search

ScalefreeCOM / datavault4dbt

Scalefree's dbt package for a Data Vault 2.0 implementation congruent to the original Data Vault 2.0 definition by Dan Linstedt including the Staging Area, DV2.0 main entities, PITs and Snapshot Tables.
https://www.scalefree.com/
Apache License 2.0
136 stars 25 forks source link

Control snapshot v0: is_daily flag not correctly set, when daily snapshot timestamp isn't 00:00:00 #156

Open tta-scalefree opened 7 months ago

tta-scalefree commented 7 months ago

In the generated code for control_snapshot_v0, the Is_daily flag is currently not correctly set to reflect the user's input for daily_snapshot_time - If this parameter is set to any timestamp other than 00:00:00.

Current macro: CASE WHEN EXTRACT(MINUTE FROM sdts) = 0 AND EXTRACT(SECOND FROM sdts) = 0 AND EXTRACT(HOUR FROM sdts) = 0 THEN TRUE ELSE FALSE END AS is_daily

Meaning, is_daily flag is only true, if daily snapshot definition is 00:00:00.

Proposal to change the macro code to parse user definition of daily snapshot: CASE WHEN EXTRACT(HOUR FROM sdts) = EXTRACT(HOUR FROM TO_TIME('{{ daily_snapshot_time }}')) AND EXTRACT(MINUTE FROM sdts) = EXTRACT(MINUTE FROM TO_TIME('{{ daily_snapshot_time }}')) AND EXTRACT(SECOND FROM sdts) = EXTRACT(SECOND FROM TO_TIME('{{ daily_snapshot_time }}')) THEN TRUE ELSE FALSE END AS is_daily

tkirschke commented 3 months ago

Next ToDo: Implement and test changes for all other environments

tkirschke commented 3 months ago

Consider: is_hourly & is_daily should be removed. Our macro does not allow hourly snapshots.