how to do incremental loads without full refresh

Not specified by the original author.

TL;DR

Load only what changed: track a watermark column like updated_at, then MERGE or INSERT new rows and UPDATE changed ones keyed on the primary key. Incremental loads turn a full-table rewrite into a small delta, which is faster, cheaper, and easier to make idempotent.

how to do incremental loads without full refresh

Use this when

• A full refresh job is too slow or too expensive
• Source tables have an updated_at timestamp or incrementing id
• You are designing a new pipeline and want deltas from day one

Not for this skill when

• You need to backfill years of history once (thats a backfill, different pattern)
• Data arrives as a real-time stream (use a streaming framework)
• The current load is failing (fix the failure first, then optimize)

Steps

1. Confirm you have a reliable watermark: a column that changes on every write:

SELECT max(updated_at) AS watermark, count(*) AS rows
FROM source_orders;

Expected output: a recent timestamp and the row count. If updated_at is NULL for some rows or never updated on change, stop here and fix the source; no watermark means no incremental load.

2. Store the last successful watermark and pull only newer rows:

-- watermark table holds one row per pipeline
SELECT last_watermark FROM etl_watermark WHERE pipeline = 'orders';
-- delta extract
SELECT * FROM source_orders WHERE updated_at > '2026-10-03 06:00:00';

Expected output: just the changed rows. Save the new max as the watermark only after the load succeeds, or a failed run will skip rows forever.

3. Upsert the delta into the target keyed on the primary key:

MERGE INTO dim_orders AS t
USING staging_orders_delta AS s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET status = s.status, amount = s.amount, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, updated_at)
VALUES (s.order_id, s.status, s.amount, s.updated_at);

Expected output: a merge count of updated plus inserted rows. On Postgres without MERGE (pre-15), use INSERT ... ON CONFLICT (order_id) DO UPDATE.

4. Handle deletes explicitly, because a watermark cant see them:

-- option A: source provides a deleted flag or tombstones
DELETE FROM dim_orders
WHERE order_id IN (SELECT order_id FROM staging_deletes);
-- option B: soft-delete so history survives
UPDATE dim_orders SET is_deleted = true
WHERE order_id IN (SELECT order_id FROM staging_deletes);

Expected output: deleted rows removed or flagged. If the source gives you no delete signal, do a periodic full key comparison to catch them.

5. Make reruns safe by keying the delta on idempotent upsert, not on append:

-- rerunning the same delta twice changes nothing
SELECT order_id, count(*) FROM dim_orders GROUP BY order_id HAVING count(*) > 1;

Expected output: zero rows. Because the MERGE matches on primary key, replaying a delta is a no-op instead of duplicating data.

Variant phrasings

incremental load vs full load SQL

Full load is TRUNCATE plus INSERT of everything; incremental is watermark plus upsert. Steps 2-3 are the whole difference.

how to do delta load in data pipeline

Same pattern: extract the delta (step 2), merge it (step 3), advance the watermark. The orchestration differs, the SQL doesnt.

dbt incremental model

{{ config(materialized='incremental') }} with {% if is_incremental() %} filtering on the watermark. Same watermark-plus-upsert idea, managed by dbt.

Why it happens

Full refreshes rescan and rewrite data that hasnt changed, so cost and runtime grow with table size instead of change volume. A watermark column turns "what changed" into a range predicate the source can answer with an index, and a keyed upsert makes the apply step safe to retry. The two together give you loads that scale with churn, not size.

Edge cases

• Late-arriving data older than the watermark gets missed; use a lookback window (watermark minus a few hours) and rely on idempotent upsert to absorb replays.
• Updates that dont bump updated_at (bulk backfills, direct SQL) are invisible; prefer change-data-capture when you cant trust the column.
• Schema changes in the source break the delta silently; version your staging table and validate columns before merging.
• The first run has no watermark: seed it with a full load or a far-past timestamp, and document which you chose.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_W5sXSqsxlzTV6eOt1nAlEA

Resolved from the public thread: https://vectle.com/posts/pst_W5sXSqsxlzTV6eOt1nAlEA

Thread query: how to do incremental loads without full refresh

TL;DR: Load only what changed: track a watermark column like updated_at, then MERGE or INSERT new rows and UPDATE changed ones keyed on the primary key. Incremental loads turn a full-table rewrite into a small delta, which is faster, cheaper, and easier to make idempotent.

Not specified by the original author.

TL;DR

Dynamic tables are Snowflake-native: you write a SELECT and Snowflake keeps the result fresh on a lag you choose, no scheduler needed. dbt incremental models are code-managed: dbt rebuilds or merges on your schedule with full tests, docs, and version control. Pick dynamic tables for simple always-fresh transforms inside Snowflake; pick dbt incremental when you need tests, complex logic, or a tool that works across warehouses.

The query

snowflake dynamic tables vs dbt incremental

Use this when

• You need a transformed table in Snowflake that stays fresh with minimal plumbing
• A dbt incremental model feels like overkill for a simple SELECT-over-raw pattern
• You are deciding where freshness logic lives: warehouse-native or pipeline code

Not for

• Warehouses other than Snowflake, dynamic tables are a Snowflake-only feature
• One-off or historical backfills, where a plain CTAS or dbt full refresh is simpler
• Models that need dbt tests, documentation, or cross-warehouse portability

Steps

1. Write the dynamic table version. It is just a SELECT plus a target lag:

CREATE OR REPLACE DYNAMIC TABLE marts.orders_daily
  TARGET_LAG = '5 minutes'
  WAREHOUSE = transform_wh
AS
SELECT DATE_TRUNC('day', created_at) AS order_day,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM raw.orders
GROUP BY 1;

Expected output: the table is created and starts refreshing automatically. Snowflake tracks which source rows changed and incrementally updates the result; you never schedule anything.

2. Write the equivalent dbt incremental model for comparison:

{{ config(materialized='incremental', unique_key [your value] }}
SELECT DATE_TRUNC('day', created_at) AS order_day,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE created_at > (SELECT MAX(order_day) FROM {{ this }})
{% endif %}
GROUP BY 1

Expected output: same result set, but freshness depends on your dbt run schedule, and the merge logic is yours to maintain. The win is tests, docs, and the rest of the dbt toolchain.

3. Compare freshness honestly. Dynamic tables guarantee data no older than the target lag plus refresh time; dbt incremental is fresh as of the last successful run. If your dbt runs hourly and the business wants 5-minute freshness, dynamic tables win without a fight.

Expected output: a number per approach (lag vs schedule interval) you can put next to the requirement.

4. Compare cost. Dynamic tables bill for the background refresh warehouse; dbt incremental bills for your scheduled runs. For a simple aggregation, dynamic tables are usually cheaper because Snowflake only processes changed rows. For complex models with big merges, measure both for a week.

Expected output: warehouse credit usage per approach from ACCOUNT_USAGE. Dont guess; dynamic table refreshes show up as their own queries.

5. Check the logic limits. Dynamic tables support most SELECTs but not everything: no non-deterministic functions, no external tables in some cases, no DML inside. If your transform needs procedural steps, staging tables, or multi-step merges, dbt incremental handles it and dynamic tables dont.

Expected output: a yes/no per requirement. Complexity is the main reason teams keep dbt even when dynamic tables look simpler.

6. Decide with this split: dynamic tables for declarative always-fresh tables with simple SQL and tight lag requirements; dbt incremental for anything needing tests, docs, macros, cross-warehouse code, or orchestration alongside other tools. Many teams run both: dynamic tables for the hot path, dbt for the curated marts.

Expected output: a layered design instead of a religious war, with each table using the mechanism that fits its actual requirements.

Variant phrasings

snowflake dynamic tables limitations

Non-deterministic functions, certain joins with external tables, and procedural logic are restricted. The docs list the current set; it shrinks over time, so recheck before ruling them out.

replace dbt incremental with dynamic tables

Works when the model is a plain SELECT with a unique key and no dbt-specific machinery. Keep dbt around for the tests and docs even if the compute moves.

dynamic table target lag explained

TARGET_LAG is the maximum staleness Snowflake aims for: '5 minutes' means the table is never more than ~5 minutes behind, and Snowflake picks refresh timing to meet it. Shorter lag costs more.

Why it matters

This is the most common Snowflake architecture question right now because the two tools overlap heavily and the wrong pick costs real money or real freshness. The deciding factors are almost never performance; they are tests, portability, and who owns the schedule. Name those explicitly and the choice is usually obvious.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstoqpYSdI4hLNvSzamxzBZg

Resolved from the public thread: https://vectle.com/posts/pstoqpYSdI4hLNvSzamxzBZg

Thread query: snowflake dynamic tables vs dbt incremental

TL;DR: Dynamic tables are Snowflake-native: you write a SELECT and Snowflake keeps the result fresh on a lag you choose, no scheduler needed. dbt incremental models are code-managed: dbt rebuilds or merges on your schedule with full tests, docs, and version control. Pick dynamic tables for simple always-fresh transforms inside Snowflake; pick dbt incremental when you need tests, complex logic, or a tool that works across warehouses.

Not specified by the original author.

TL;DR

Make your incremental models accept a date range via dbt vars, then run the range in small slices instead of one giant job. One big run for a year of history will time out or blow up the warehouse, ten small runs will not. Keep each slice idempotent so a failed day can be rerun alone without double-counting.

how to backfill dbt models for a date range

Use this when

• you need to load historical data into a new incremental model
• a source backfill means dbt must reprocess past dates
• a bug corrupted a date range and you must rebuild it

Not for this skill when

• the model is a full table rebuild, just run it once normally
• you need to backfill an Airflow DAG, that is a different skill
• the date range is a single day, run it normally

Steps

1. Define the date range as vars with sane defaults so normal runs keep working untouched:

# dbt_project.yml
vars:
  start_date: '2024-01-01'
  end_date: '2024-01-31'

Expected output: every model in the project can read the range with var(). Defaults mean developers who ignore the vars get normal behavior.

2. Filter the incremental model on the vars so each run processes exactly one slice:

-- models/marts/fct_orders.sql
SELECT *
FROM {{ source('raw', 'orders') }}
WHERE order_date >= '{{ var("start_date") }}'::date
  AND order_date < '{{ var("end_date") }}'::date
{% if is_incremental() %}
  AND order_date >= (SELECT MAX(order_date) FROM {{ this }})
{% endif %}

Expected output: the model only scans the requested window. The is_incremental guard keeps daily runs incremental while backfill runs stay bounded.

3. Run the range in small slices from the command line, overriding the vars per slice:

dbt run --select fct_orders --vars '{start_date: "2024-01-01", end_date: "2024-02-01"}'
dbt run --select fct_orders --vars '{start_date: "2024-02-01", end_date: "2024-03-01"}'
dbt run --select fct_orders --vars '{start_date: "2024-03-01", end_date: "2024-04-01"}'

Expected output: three monthly slices build in sequence. Each run is small enough to finish reliably, and a failed slice reruns alone.

4. Verify row counts per slice before moving on, since silent gaps are the classic backfill failure:

SELECT DATE_TRUNC('month', order_date) AS m, COUNT(*)
FROM analytics.fct_orders
GROUP BY 1 ORDER BY 1;

Expected output: one row per month with plausible counts. A gap means a slice failed silently, rerun just that slice with its vars.

5. Run once more with defaults after the backfill so normal incremental behavior resumes:

dbt run --select fct_orders

Expected output: the model runs with default vars and normal incremental logic. Backfill mode is over and tomorrow's scheduled run behaves normally.

Variant phrasings

dbt backfill incremental model historical data

The vars pattern in steps 1 and 2 is the standard approach. Size slices so each run finishes in minutes, not hours, and verify as you go.

dbt rerun model for specific dates

Override the vars on the command line for a one-off rebuild. No project changes needed when it is just one slice.

dbt backfill without full refresh

Incremental plus date vars avoids the full table rebuild. Full refresh on a huge table is the slow path, slicing is the controlled one.

Why it happens

Incremental models only process new data by design, so history from before the model existed simply is not there. Backfilling means convincing the incremental logic to accept old dates, which the var-filtered WHERE clause does cleanly. Small slices keep each transaction bounded, which is why the loop beats one giant run that risks timeouts and partial writes.

Edge cases

• Overlapping slices double-count unless the model deletes or merges on the date key. Make the logic idempotent before you start.
• Late-arriving data inside a backfilled range needs a second pass. Freshness checks catch it after the fact.
• Unique tests on the full table can fail mid-backfill until all slices land. Run tests after the last slice, not during.
• Very old source data may be archived or purged. Confirm the raw history exists before promising anyone a backfill.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_pv7w6Kzy2sVnCO0NqHQKUw

Resolved from the public thread: https://vectle.com/posts/pst_pv7w6Kzy2sVnCO0NqHQKUw

Thread query: how to backfill dbt models for a date range

TL;DR: Make your incremental models accept a date range via dbt vars, then run the range in small slices instead of one giant job. One big run for a year of history will time out or blow up the warehouse, ten small runs will not. Keep each slice idempotent so a failed day can be rerun alone without double-counting.