[Epic] Snapshots as a First Class Citizen

[gshank]

An epic to improve the usability and accuracy of snapshots.

P0 - must

Preview Give feedback

1. Snapshot config doesn't work in schema.yml #4000
   snapshots user docs +2
   
2. Remove IntermediateSnapshotNode class #10326
   snapshots +1
3. [Investigation] Explore options for providing upgrade or migration paths (for snapshots) #10325
   snapshots +1
   
4. [Feature] Snapshots should respect generate schema/database name macro #10301
   enhancement user docs +2
   
5. Warn if updated_at field for snapshot is not same datatype as what's returned in snapshot_get_time() #10234
   snapshots user docs +2
   
6. Allow customizing names of metadata fields in snapshots #10185
   snapshots user docs +2
   
7. [Feature] No more jinja block for snapshots - new snapshot design ideas #10246
   enhancement snapshots user docs +3
   
8. Allow custom date for dbt_valid_to in snapshots #10187
   snapshots user docs +2
   
9. new record in snapshot to track deleted state (is_deleted column, zombie records) #10235
   snapshots user docs +2
   
10. [Bug] When a custom dbt_valid_to_current is configured, it doesn't update #10921
    bug pre-release snapshots wontfix +4
    

P1 - should

Preview Give feedback

1. [Feature] Allow snapshots to have unique_key as a list #9992
   enhancement snapshots user docs +3
2. [Feature] Support --empty flag for snapshots #10372
   empty enhancement paper_cut snapshots unit tests +5
   
3. New snapshot config to validate uniqueness before merge #10236
   snapshots user docs +2
4. [CT-2699] [Feature] "interactive" compile should include the compiled code of a snapshot #7867
   enhancement help_wanted snapshots +3
5. [CT-2869] Add new snapshot records when source removes a field #8207
   Refinement enhancement snapshots +3
6. [CT-2701] Snapshot dbt_updated_at not updated #7869
   behavior_change_flag snapshots user docs +3
7. [spike+] option to generate dbt_scd_id as an integer column instead of a string for performance improvements #10300
   enhancement snapshots +2
   

p2 - could

Preview Give feedback

1. [Feature] exclude option for check_cols snapshot #10438
   enhancement snapshots +2
2. [CT-2280] Tracking changes in the non-chronological insertions of data in dbt snapshots (check strategy) #7138
   enhancement snapshots +2
3. create a snapshot on sources that already have some history #10320
   snapshots stale +2

revisit, refine, prioritize

Preview Give feedback

1. dbt snapshots to handle append tables #3878
   enhancement snapshots +2
2. [CT-137] [Bug] Snapshot job creates duplicate valid records for unique_key during 2 colliding runs #4661
   bug snapshots +2
3. Incorrect identifiers in snapshot schema test when overriding the ref() macro #2848
   bug +1
4. [CT-2492] [Bug] Update snapshot logic to use dbt.hash instead of hardcoded md5 dbt-adapters#82
   Stale enhancement snapshots +3
5. [CT-3252] [Feature] Add a way to refresh Snapshots in the database #8885
   enhancement snapshots wontfix +3

Base adapter

Preview Give feedback

1. Add hard_deletes config and new_record Option for Snapshots dbt-adapters#317
   cla:yes +1
2. Enable setting current value of dbt_valid_to dbt-adapters#321
   cla:yes +1
3. Snapshot column names dbt-adapters#290
   cla:yes +1
4. Allow use of multiple column unique keys in snapshots dbt-adapters#326
   cla:yes +1

Concrete adapters

Preview Give feedback

1. hard_deletes config and new_record option for snapshots dbt-postgres#180
   Skip Changelog cla:yes +2
2. hard_deletes config and new_record option for snapshots dbt-redshift#964
   Skip Changelog cla:yes +2
3. hard_deletes config and new_record option for snapshots dbt-snowflake#1271
   Skip Changelog cla:yes +2
4. hard_deletes config and new_record option for snapshots dbt-bigquery#1428
   Skip Changelog cla:yes +2
5. hard_deletes config and new_record option for snapshots dbt-spark#1154
   Skip Changelog cla:yes +2
6. Enable setting dbt_valid_to snapshot column to new setting dbt_valid_to_current dbt-postgres#152
   cla:yes +1
7. Enable setting dbt_valid_to snapshot column to new setting dbt_valid_to_current dbt-spark#1113
   cla:yes +1
8. Allow configuring snapshot table column names dbt-postgres#145
   cla:yes +1
9. Allow configuring snapshot column names dbt-spark#1097
   cla:yes +1

Discussions

Problems & (Potential) Solutions for Snapshots
Toggling values when snapshotting a table with a key violation

[danielefrigo]

another interesting customization could be the ability to configure the value for dbt_valid_to then opening a new record.
dbt default is null, but in many cases I found the need to have a far away timestamp (e.g. '2999-12-31').

[graciegoheen]

@danielefrigo Yes! This is something we'd like to do - issue here -> #10187

[jeremyyeo]

Snapshots currently don't support contracts. Would be great have snapshots support contracts/constraints just as models do.

[graciegoheen]

Thanks @jeremyyeo - would you be down to open up a new issue for that feature request?

[graciegoheen]

Closing this EPIC out since we've closed the issues slated for the 1.9 release - will open up a new EPIC if we pick up more of these in the future