DV hub methodology question about load_date

Data Vault 2.0
1

Hi everyone,

I have an important question regarding the load_date in Data Vault hubs.

In my implementation, hubs are created using a standard macro with an insert-only incremental approach, meaning that existing records are never updated.

{% macro build_hub(
    source_model,
    business_key,
    hub_hash_key,
    record_source
) %}

WITH source_data AS (
    SELECT
        {{ dv_hash([business_key], hub_hash_key) }},
        {{ business_key }},
        DOP AS load_date,
        '{{ record_source }}' AS record_source
    FROM {{ source_model }}
    WHERE DOP IS NOT NULL
      AND {{ business_key }} IS NOT NULL
)

SELECT DISTINCT
    {{ hub_hash_key }},
    {{ business_key }},
    load_date,
    record_source
FROM source_data

{% if is_incremental() %}
WHERE {{ hub_hash_key }} NOT IN (
    SELECT {{ hub_hash_key }} FROM {{ this }}
)
{% endif %}
{% endmacro %}

The load_date is currently populated from the source column DOP (Date of Operation). However, DOP can change when additional operations are performed on the same business entity.

After a few days, I noticed that I have records in the hub with the same entity_hash_key, entity_id, and source_system, but with different load_date values.

My questions are:

  1. Is this changing DOP information something that should instead be stored in satellites?
  2. Should I use MIN(DOP) when loading the hub so that only the earliest date is stored and never changed for the same hash key, business key, and source system?
  3. More generally, what is considered the best practice for handling load_date in Data Vault hubs when the source operational date can change over time?

Any guidance would be greatly appreciated.

2

Hi there Julia,

Data vault structures are designed to be bitemporal, so we’re tracking two timelines. The applied timeline for seeing when your source system made changes, and the auditability timeline for tracking when the warehouse gained access to the information from source.

These are good for different purposes but in hubs you only need to concern yourself with when your warehouse gained access to this information, let your satellites track the source systems applied time.

In terms of what to do with your dbt code, yes apply your minimum function to avoid dupes but use something like current_timestamp() for your load_date fields instead of DOP. That field should be used as your applied date in your satellites.

Shout if you have any further questions,

Frankie

1 Like