Skip to main content

Snapshot vs Incremental Model

Two of the most frequently confused concepts in dbt are Snapshots and Incremental Models.

Both of them look at a source table, find "new" or "updated" rows based on a timestamp, and insert those rows into a target table to save compute time.

So, what is the difference? And when should a Data Engineer use one over the other?

Topics Covered​

  • The Core Difference (History vs State)
  • When to use an Incremental Model
  • When to use a Snapshot
  • Summary Cheat Sheet

1. The Core Difference​

Incremental Models (State Updates)​

An incremental model is designed to process Event Data (data that never changes, like a log file) or to Overwrite state. If an incremental model uses the merge strategy, and it detects that Arjun's address changed from Mumbai to Delhi, it will delete the Mumbai row and insert the Delhi row. The history is lost. It only cares about the current state.

Snapshots (Historical Tracking)​

A snapshot is designed to process Mutable Dimensions (data that changes, like user profiles). If a snapshot detects that Arjun's address changed from Mumbai to Delhi, it will keep the Mumbai row (marking it as expired) and insert a second row for Delhi. The history is perfectly preserved.

2. When to use an Incremental Model​

Use Case: You are processing millions of UPI transactions per day for a bank in Hyderabad. Table Type: Fact Table (fct_upi_transactions)

Once a UPI transaction occurs, it is immutable. A completed payment of 500 INR will never suddenly change to 600 INR tomorrow.

You should build this as an Incremental Model using the append strategy. Every hour, dbt simply takes the new rows from the last hour and blindly appends them to the massive fact table.

If you tried to build this as a Snapshot, dbt would waste massive amounts of compute power trying to compare the millions of rows to see if any of them "changed". We already know they didn't!

3. When to use a Snapshot​

Use Case: You have a small merchants table (e.g., 50,000 rows). Sometimes merchants upgrade their subscription from "Basic" to "Premium". The application database simply overwrites the column. Table Type: Dimension Table (snp_merchants)

If you want to run a report answering, "How much revenue did Basic merchants generate last year?", you have a problem. If a merchant is Premium today, all of their historical transactions will accidentally be attributed to the Premium tier, ruining your financial report.

You must build this as a Snapshot. dbt will track every single subscription change, creating a Slowly Changing Dimension (SCD Type 2). Your financial model can now join the transaction date to the exact dbt_valid_from and dbt_valid_to window to get the mathematically perfect answer.

4. Summary Cheat Sheet​

FeatureIncremental ModelSnapshot
Primary GoalSave compute costs on massive datasets.Preserve historical changes (SCD Type 2).
Target Table TypeFact Tables (Event Logs, Transactions)Dimension Tables (Users, Products, Accounts)
File Extension.sql (in the models/ folder).sql (in the snapshots/ folder)
Execution Commanddbt rundbt snapshot
Handles Updates?Overwrites the old row (merge).Keeps the old row, inserts a new one.
PerformanceExtremely Fast (Appends data)Slower (Requires complex Joins to detect changes)

Rule of Thumb: If the data represents an action that happened at a specific point in time (a click, a purchase, a login), use an Incremental Model. If the data represents a person, place, or thing whose attributes change over time (a user, a warehouse, a subscription tier), use a Snapshot.