Snapshot Control v0
This macro creates a snapshot table to control snapshot-based tables like PITs and Bridges. The snapshot table will hold daily snapshots starting at a specific start_date and has a configurable daytime. Usual application would involve creating one snapshot table per Data Vault environment, and therefore creating one dbt model using this macro. The model needs to be scheduled daily, with a execution time that matches your desired snapshot time, since the macro automatically inserts all snapshots until the current day, no matter which time it is, and a snapshot for 08:00:00 should be calculated at that time. So if the snapshot table is configured to have a ‘daily_snapshot_time’ of ’07:00:00′, all snapshots in the table will have the timestamp ’07:00:00′. Therefore you need to schedule the building of the snapshot table also to ’07:00:00′.
In addition to the actual snapshot-datetimestamp (sdts), the macro generates the following columns:
Column | Data Type | Explanation |
---|---|---|
replacement_sdts | timestamp | Allows users to replace a sdts with another one, without having to update the actual sdts column. By default this column is filled with the regular sdts. |
caption | string | Allows users to title their snapshots. Examples would be something like: ‘Christmas 2022’, or ‘End-of-year report 2021’. By default this is filled with ‘Snapshot {sdts}’, holding the respective sdts. |
is_hourly | boolean | Captures if the time of a sdts is on an exact hours, meaning minutes=0 and seconds=0. All sdts created by this macro are daily and therefore always hourly, but this column enables future inserts of custom, user-defined sdts. |
is_daily | boolean | Captures if the time of a sdts is on exactly midnight, meaning hours=0, minutes=0 and seconds=0. This depends on your desired daily_snapshot_time, but is not used by the downstream macros, and just generates additional metadata for potential future use. |
is_weekly | boolean | Captures if the day of the week of a sdts is monday. |
is_monthly | boolean | Captures if a sdts is the first day of a month. |
is_end_of_month | boolean | Captures if a sdts is the last day of a month. |
is_quarterly | boolean | Captures if a sdts is the first day of a quarter. |
is_yearly | boolean | Captures if a sdts is the first day of a year |
is_end_of_year | boolean | Captures if a sdts is the last day of a year |
comment | string | Allows users to write custom comments for each sdts. By default this column is set to NULL. |
Parameters | Data Type | Required | Default Value | Explanation |
---|---|---|---|---|
start_date | date | mandatory | – | Defines the earliest date that should be available inside the snapshot_table. The format of this date must be ‘YYYY-MM-DD’. |
daily_snapshot_time | time | mandatory | – | Defines the time that your daily snapshots should have. Usually this is either something right before daily business starts, or after daily business is over. Needs to be in the format ‘hh:mm:ss’. |
sdts_alias | string | optional | datavault4dbt. sdts_alias | Defines the name of the snapshot date timestamp column inside the snapshot_table. |
Example 1
{{ config(materialized='incremental') }}
{%- set yaml_metadata -%}
start_date: '2015-01-01'
daily_snapshot_time: '07:30:00'
{%- endset -%}
{%- set metadata_dict = fromyaml(yaml_metadata) -%}
{{ datavault4dbt.control_snap_v0(start_date=metadata_dict.get('start_date'),
daily_snapshot_time=metadata_dict.get('daily_snapshot_time')) }}
Description
- start_date: The parameter start_date has to be in the format ‘YYYY-MM-DD’. With the start_date set to ‘2015-01-01’, the snapshot table would hold daily snapshots beginning at 2015.
- daily_snapshot_time: ’07:30:00′ The snapshots inside this table would all have the time ’07:30:00′. Must be set in the format ‘hh:mm:ss’.