Snapshot configuration should have created_at option

[brentleslieaware]

I have an improvement, am surprised its not on this list already!

dbt_valid_from field sometimes does not go back far enough. We have a source system that tracks create_date and modified date. We set up our snapshot with a timestamp strategy, using modified date as the "updated_at" indicator. So far so good.

However when we run the process for the first time the dbt_valid_from is set to the current value of the modified date. This makes sense, for records going forward. But when we run our fact table to join on valid records in the snapshot, based on the time the fact record was created, this can be before the modified date of the first record, therefore the fact table row is unable to join to the dimension row. This is the late arriving fact problem wearing different clothes. The work around is to do a full refresh of the fact table, ensuring you only run the timestamp matching code when the fact table already exists (i.e. in snapshot blocks). But this seems "hacky". It means you don't get a rich enough history in your dimensions in the dbt_valid_from value, that accurately represents source data. I think its far better for:

• the snapshot configuration to have an optional "created_at" timestamp column specification
• on first run, if the created_at timestamp exists, use it to inform the system of the dbt_valid_from date
• if the created_at does not exist, use current behaviour and use the updated_at column value instead