Skip to main content

Reference

A Reference Table in the context of Data Vault (and data warehousing in general) is used to store relatively static lookup or classification data. While not a core Data Vault 2.0 entity type like Hubs, Links, or Satellites, Reference tables play a crucial supporting role. They provide descriptive context to codes or keys found in other tables, making the data more understandable and usable for reporting and analysis.

Key Characteristics:​

  • Lookup Data: Stores codes, descriptions, categories, types, statuses, etc. (e.g., country codes and names, order status descriptions, product category names).
  • Relatively Static: Data in reference tables changes infrequently.
  • Enhances Readability: Provides human-readable descriptions for codes used in operational systems or other Data Vault tables.
  • Simple Structure: Typically has a simple key (the code) and one or more descriptive attributes.

Role in Data Warehousing:​

Reference tables are essential for enriching data and supporting business intelligence. They allow users to see meaningful descriptions instead of cryptic codes in their reports and dashboards. In a Data Vault context, they can be used to provide context to data loaded into Satellites or other informational marts built on top of the Raw Vault.

Simple Reference Table Example:​

This example defines a Reference table REF_ORDER_STATUS to store descriptions for order status codes.

name: 'REF_SAPSE_GENERAL_T003T'
entity_type: 'reference'
enable_refresh: true
enable_history: false

ordering_columns:
skip_hashdiff_comparison: false
concatenate_business_keys: true

target_business_key_columns:
  - 'REF_SAPSE_GENERAL_T003T_BK'

entity_source:
  (SAP_SE, T003T):                  # Source: (Schema, Table)
    source_filter:                  # Optional filter
    use_source_cdc_flag: true
    source_system_configuration_urn: 'urn:s2v:source_setting:SAP'
    business_key_mapping:
     - REF_SAPSE_GENERAL_T003T_BK:  # Business key column name
        - 'BLART'                   # Corresponding column in T003T table
        - 'MANDT'                   # Corresponding column in T003T table
        - 'SPRAS'                   # Corresponding column in T003T table

historized_columns:                 # Attributes whose history is tracked
  - 'BLART'
  - 'MANDT'
  - 'SPRAS'
  - 'LTEXT'

non_historized_columns: []          # Attributes whose history is NOT tracked (current value stored)
  • For a detailed step-by-step guide on building a Reference, please refer to the How to build a Reference? tutorial.
  • For a comprehensive guide on all available properties and detailed explanations for defining a Reference, please refer to the Reference.