Alright, let me tell you about my little adventure with dbt snapshots. I kept running into this issue where data in my main tables would change, you know, things like customer addresses or product categories. And then someone would ask, “Hey, what was customer X’s address back in January?” and I’d just stare blankly. Trying to figure that out retroactively was a real pain.
I remembered hearing about something in dbt for tracking history, called snapshots. Sounded promising, maybe like it could solve my “what did this row look like before?” problem. So, I decided to give it a whirl.
Getting Started
First thing, I had to actually create a snapshot file. dbt wants these things in a specific `snapshots` directory in the project. Easy enough. I made a new SQL file in there, let’s say `snap_*`.
Inside the file, I started with the `{% snapshot %}` Jinja tags. It felt a bit like writing a model, but different. I needed to tell it a few key things:
- Which table I wanted to snapshot (the `ref` or `source` function).
- How to identify a unique row (the `unique_key`). This was important; I used the customer ID.
- How dbt should know if a row has changed. I went with the `timestamp` strategy first, using an `updated_at` column I already had in my source table. There’s also a `check` strategy where you list columns to watch, but the timestamp felt simpler for my case.
- Where the final snapshot table should live (`target_schema`).
So the top part of my file looked something like this, wrapped in those curly braces and percent signs:
config(target_schema='snapshots', unique_key='customer_id', strategy='timestamp', updated_at='updated_at')
Then below that, just a simple `select from ref(‘dim_customers’)` or whatever my source table was.
Running It and Seeing What Happens
With the file saved, I hopped into my terminal. The command was pretty straightforward: `dbt snapshot`. I ran it, holding my breath a little.
It worked! No errors on the first try, surprisingly. So, I went straight to my data warehouse to check out the new table it supposedly created in the `snapshots` schema.
And there it was: `snap_customers`. When I looked inside, I saw all the columns from my original `dim_customers` table, plus a few extra ones dbt added automatically: dbt_valid_from and dbt_valid_to. Cool.
Initially, all the rows had a `dbt_valid_from` timestamp from when I ran the snapshot, and `dbt_valid_to` was null. This basically meant these were the ‘current’ versions of each customer record.
Testing the Change Tracking
The real test was seeing if it caught changes. So, I manually updated the address for one customer in my source `dim_customers` table and also made sure to update its `updated_at` timestamp. Then, I ran `dbt snapshot` again.
This time, when I checked the `snap_customers` table, something neat happened:
- The original row for that customer now had a timestamp in the `dbt_valid_to` column. It wasn’t the current record anymore.
- A brand new row appeared for that same customer ID. This new row had the updated address, a new `dbt_valid_from` timestamp (from the second snapshot run), and its `dbt_valid_to` was null again.
It actually worked! It kept the old record and marked when it stopped being valid, and then added the new record showing the current state. Exactly what I needed – a history of changes!
Final Thoughts
Setting up dbt snapshots was honestly less hassle than I expected. Figuring out the config options (`strategy`, `unique_key`, etc.) was the main part, but once that was done, running `dbt snapshot` just handled the logic. It automatically creates those `dbt_valid_from` and `dbt_valid_to` columns and manages the history.
Now, when someone asks what a record looked like in the past, I can actually query the snapshot table and find the row that was valid during that time period. It’s made tracking those slowly changing dimensions way, way easier than digging through logs or trying to reconstruct history manually. Definitely a handy tool in the dbt toolkit.
