Snapshot tables for historical tracking in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a snapshot table in dbt?

A snapshot table in dbt is a special table that records changes in source data over time, allowing you to track historical versions of records.

beginner

Why do we use snapshot tables for historical tracking?

We use snapshot tables to keep a history of data changes, so we can see how data looked at different points in time, which helps in auditing and analysis.

intermediate

What are the two main strategies for snapshots in dbt?

The two main strategies are 'timestamp' and 'check'. 'Timestamp' tracks changes based on a timestamp column, while 'check' compares specified columns for changes.

intermediate

How does the 'check' strategy detect changes in a snapshot?

The 'check' strategy compares specified columns in the source data to detect any changes. If any column value changes, dbt records a new version in the snapshot table.

beginner

What columns does dbt add automatically to a snapshot table?

dbt adds columns like 'dbt_valid_from' and 'dbt_valid_to' to mark the time range when a record version was valid, helping track the history clearly.

What is the main purpose of a snapshot table in dbt?

A To create backups of the database

B To track historical changes in data over time

C To delete old data automatically

D To speed up query performance

Snapshot tables are designed to keep historical versions of data to track changes over time.

Which snapshot strategy in dbt uses a timestamp column to detect changes?

A Check

B Incremental

C Merge

D Timestamp

The 'timestamp' strategy uses a timestamp column to identify when data has changed.

What does the 'check' strategy compare to detect changes?

A Only timestamp columns

B Only primary key columns

C All specified columns for any difference

D Row counts

The 'check' strategy compares specified columns to find any changes in their values.

Which columns does dbt add to snapshot tables to track validity periods?

A dbt_valid_from and dbt_valid_to

B created_at and updated_at

C start_date and end_date

D valid_from and valid_until

dbt adds 'dbt_valid_from' and 'dbt_valid_to' columns to mark when each record version is valid.

When should you use snapshot tables in your data project?

A When you want to track how data changes over time

B When you want to delete old data

C When you want to speed up data loading

D When you want to create indexes

Snapshot tables are useful to keep a history of data changes for analysis or auditing.

Explain what a snapshot table is and why it is useful for historical tracking in data projects.

Think about how you can see past versions of data.

Describe the difference between the 'timestamp' and 'check' snapshot strategies in dbt.

Consider how each detects changes in data.