1 post
A table model rebuilds everything on every run. That's simple and correct, and it's the right default until the table gets big. An incremental model processes only new or changed rows and merges them into what's already there. It's faster and cheaper, and it's also where most dbt bugs live. The minimal correct version {% raw %} sql {{ config( materialized = 'incremental', uniquekey = 'claimid', incrementalstrategy = 'merge', onschemachange = 'appendnewcolumns' ) }} select claimid, policyid, claimamount, status, updatedat from {{ ref('stgclaims') }} {% if isincremental() %} where updatedat ( select dateadd('day', -3, max(updatedat)) from {{ this }} ) {% endif %} {% endraw %} What each piece does: - uniquekey tells dbt how to match incoming rows to existing ones. Without it, the merge becomes a plain append and reruns create duplicates. - incrementalstrategy = 'merge' is the default on Snowflake. It's written out here so nobody has to wonder. - isincremental() is false on the first run and on --full-refresh, so the filter only applies once the table exists. - The 3-day lookback catches late-arriving rows. Filtering on exactly max(updatedat) silently drops any record that lands after a later one has already been loaded. Things that bite 1. Duplicate keys in the source. If stgclaims has two rows for one claimid, the merge errors out. Deduplicate in staging with qualify rownumber() over (partition by claimid order by updatedat desc) = 1. 2. Logic changes don't backfill themselves. Change a calculation and only new rows get it. Run dbt run --select mymodel --full-refresh after changing business logic. 3. Schema changes. The default onschemachange is ignore, so new columns just don't appear. appendnewcolumns or syncallcolumns is usually what you want. 4. Hard deletes are invisible. Rows deleted upstream stay in your table forever. Use a soft-delete flag from the source, or a snapshot. Test it like you mean it yaml models: - name: fctclaims columns: - name: claimid tests: - unique - notnull A unique test on the uniquekey is the cheapest insurance you can buy for an incremental model. It fails the build the first time a duplicate slips through. When to go incremental Stay on table until a full rebuild is noticeably slow or expensive. Incremental models trade simplicity for speed, so make the trade only when you need to. Try it locally with DuckDB You don't need a Snowflake account to see the behaviour. dbt-duckdb runs the same Jinja and the same merge strategy on your laptop. bash pip install dbt-duckdb mkdir -p dbtdemo/models && cd dbtdemo curl -o models/fctevents.sql https://www.yashank.site/files/demos/fctevents.sql Add a two-line dbtproject.yml (name and profile) and a profiles.yml with type: duckdb and path: demo.duckdb. Create a rawevents table, run dbt run, then update 10 rows and insert 200 new ones upstream and run again: Open target/compiled/.../fctevents.sql after the second run and you'll see the where updatedat (select max(updatedat) - interval 3 day ...) filter that isincremental() switched on. When you move to Snowflake, only the profile changes.