DOCSEARCHbyAlgolia
Submit Docs

datavault4dbt

repository·main·Indexed 41 days ago

https://github.com/scalefreecom/datavault4dbt

A dbt package by Scalefree International GmbH providing an automated implementation of the Data Vault 2.0 methodology. It enables the creation of auditable, scalable, and standardized data warehouses using macros for Hubs, Links, Satellites (including Non-Historized, Multi-Active, and Effectivity), PIT tables, and Snapshot Control. It supports multiple platforms including BigQuery, Snowflake, Databricks, Redshift, and PostgreSQL, and is compatible with the dbt Fusion engine.

Tokens
46.1K
Snippets
109
Records
183
Agent score
53%

What's inside datavault4dbt

  1. Overview of datavault4dbt features and macros

    main

    datavault4dbt provides a complete implementation of Data Vault 2.0. Key capabilities include:

    Core Entities & Macros

    • Staging Area: For hashing, prejoins, and ghost records.
    • Standard Entities: Macros for Hubs, Links, and Satellites (supporting multiple deltas).
    • Specialized Satellites: Non-Historized Satellites, Multi-Active Satellites, Effectivity Satellites, and Record Tracking Satellites.
    • Business Vault: PIT (Point-in-Time) Tables (including a cleanup hook), Snapshot Control Tables, and Snapshot Control Views.
    • Reference Data: Reference Hubs, Satellites, and Tables.

    Key Architectural Features

    • Auditability: A fully auditable solution for Data Warehousing.
    • Insert-only approach: Modern design that avoids updating existing data.
    • Incremental Loading: Optimized via high-water-mark implementation that supports entities loaded from multiple sources.
    • Business Interface: Centralized, snapshot-based interface using logarithmic logic.

  2. What is Prejoining and when to use it

    main

    Prejoining is a mechanism used to enrich source data with attributes from other database objects. It is primarily used when the source data contains a technical Key of an object rather than its Business Key.

    Within a single Stage model, you can configure the extraction of one or many columns from other objects by defining a list of dictionaries under the prejoined_columns parameter.

  3. What is an Effectivity Satellite and when to use it

    main

    An Effectivity Satellite (version 0) is used to capture whether an object (Hub) or a relationship (Link) disappears from the source system. It tracks the appearances of a specific hashkey to identify deletions.

    Key Concepts

    • Purpose: To identify and track deletions and appearances of hashkeys inside source objects.
    • Requirement: The source data delivery must always include a full load of the data. It is not compatible with delta-only data deliveries.
    • Granularity: One effectivity satellite tracks exactly one hashkey. If a Hub or Link is loaded by multiple sources, you must create one effectivity satellite for each source.
    • Materialization: It should always be materialized as an incremental table.
    • Connectivity: It is applied on top of the staging layer and is connected to either a Hub or a Link.

  4. What is a Reference Hub and how does it differ from a Standard Hub?

    main

    A Reference Hub is a specialized Data Vault hub designed to store reference data. Unlike a Standard Hub, a Reference Hub does not require a Hub Hashkey. Instead, it contains one or more unhashed reference keys.

    Key Characteristics:

    • Source Models: The source for a Reference Hub is typically a stage model.
    • Multi-source Loading: It can be loaded from multiple sources. If multiple sources are used, each source must have the same number of reference keys and must define a rsrc_static attribute.
    • Shared Features: Like standard hubs, it supports multiple updates per batch (initial loading), uses dynamic high-water-marks for performance optimization, and allows source mappings for column name deviations.

  5. Manage column limits for PostgreSQL satellites

    main

    In PostgreSQL, the default MAX_FUNCTION_ARGUMENTS setting is 100. Because datavault4dbt uses function arguments to handle satellite columns, this imposes a limit of 50 columns per satellite if the server uses default settings.

    To handle satellites with more than 50 columns, you must either:

    1. Adjust the database server settings: Increase the MAX_FUNCTION_ARGUMENTS value in your PostgreSQL configuration.
    2. Use Satellite Splits: Design your Data Vault model with more frequent satellite splits to ensure no single satellite exceeds the 50-column threshold.

  6. Multi-active hashdiff implementation on Databricks

    main

    Starting from version v2.0.0, the multi-active hashdiff implementation for Databricks utilizes the native LISTAGG function with an explicit WITHIN GROUP (ORDER BY ...) clause. This approach ensures deterministic, correctly-ordered aggregations for multi-active satellites without needing a ROW_NUMBER workaround.

    Important: This implementation requires Databricks Runtime 16.4 LTS or later (or an equivalent Databricks SQL warehouse version). Using an older runtime will result in a compilation failure with a function-not-found error.

    LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY multi_active_key)

  7. Understand the Reference Data model in Datavault4dbt

    main

    Datavault4dbt uses a decoupled architecture for reference data to allow for flexible historization, privacy compliance, and scalability. The model consists of three main components:

    1. reference Hub: The core entity that holds the reference key(s) for a reference object.
    2. reference Satellites: Attached to a reference Hub, these store descriptive data and track changes over time. You can attach multiple satellites to a single reference Hub to separate different types of descriptive attributes.
    3. reference Tables: A presentation layer that reunites a reference Hub with its associated reference Satellites. These are typically implemented as virtual views, but can be materialized as tables.

    Historization Patterns for reference Tables: When creating reference tables, you can choose from three historization strategies:

    • Store only the latest descriptive data.
    • Capture all deltas (change tracking).
    • Apply a snapshot-based pattern.

  8. Overwrite or Add columns using Derived Columns

    main

    You can control whether a transformation replaces an existing column or creates a new one by comparing the column alias (col_alias) to the source columns (src_col) used in the expression:

    • Overwriting Existing Columns: Set col_alias equal to the src_col. The original input column will be replaced by the result of the transformation. Use with caution.
    • Adding New Columns: Set col_alias to a name different from the src_col. The transformation will be appended as a new column in the stage model.

  9. Data Vault 2.0 implementation requirements

    main

    To use the datavault4dbt macros effectively, your source data must meet the following prerequisites:

    • Flat & Wide source data: Data must be available within your target database in a flattened format.
    • Load Date column: A column representing the arrival time of the data in the source storage.
    • Record Source column: A column providing metadata about the data origin (e.g., a file path in a Data Lake).

  10. Included dbt macros in datavault4dbt

    main

    The package includes specialized macros for various Data Vault components and patterns:

    • Staging Area: Handles Hashing, prejoins, and ghost records.
    • Core Entities: Hubs, Links, and Satellites (supporting multiple deltas).
    • Specialized Links & Satellites: Non-Historized Links and Satellites, and Multi-Active Satellites.
    • Tracking & Effectivity: Effectivity and Record Tracking Satellites.
    • Reference Data: Macros for Reference Data Entities.
    • Advanced Satellite Features: Virtualized End-Dating (in Satellites).
    • PIT Tables: Point-in-Time tables, including a hook for cleaning up PITs.
    • Snapshot Control: Macros for managing snapshots.

  11. Use `use_rtrim` in Hashdiff definitions

    main

    The use_rtrim: true key is an optional configuration for the Satellite Hashdiff definition within a Stage model.

    When adding new columns to a Satellite, the calculated Hashdiff will normally change even if the new columns are NULL. This causes the Satellite to treat the record as a change and insert a new row. By setting use_rtrim: true, the datavault4dbt adapter (specifically optimized for Snowflake) trims the delimiters used to represent NULL values from the right side of the hash. This allows the new Hashdiff to match the old Hashdiff if the new columns are all NULL, preventing unnecessary row insertions.

    Warning: If you add use_rtrim: true to a Satellite that was already loaded without it, the hashes will not match even for identical data, because the previous hashes contain trailing delimiters that the new calculation will trim. It is recommended to always use use_rtrim: true for newly created Satellites in Snowflake to future-proof them.

    # Inside the Stage model yaml_metadata
hashed_columns: 
    hd_account_s:
        is_hashdiff: true
        use_rtrim: true
        columns:
            - col1
            - col2
            - new_col

  12. Configure hashed_columns for Hubs, Satellites, and Links

    main

    The hashed_columns section in the stage macro configuration allows you to define different types of hash-based columns:

    1. Hub Hashkeys (HK): Defined by a list of business keys.
      • Example: hk_account_h: [account_number, account_key]
    2. Satellite Hashdiffs (HD): Defined using is_hashdiff: true and a list of columns. This calculates a hash of descriptive attributes to detect changes.
      • Example: hd_account_s: { is_hashdiff: true, columns: [name, address] }
    3. Link Hashkeys (LK): Defined by a list of business keys from the connected hubs.
      • Example: hk_account_contract_l: [account_number, account_name, contractnumber]