When dbt’s Scan Filter Hides the Row You Need to Update

A pipeline team receives a historical correction for a key that already exists in the target. The dbt run can succeed while the old value remains and a second row appears. The likely boundary is not source admission: a destination-side predicate has removed the historical target row from the MERGE ON condition. The optimization has hidden the row that the correction needed to match. This playbook tests one invariant: scan reduction is acceptable only when every admitted correction can still see the target row it is meant to replace. The evidence is a controlled Snowflake lab with two clean target rows, one correction, a frozen clock, and paired runs that differ only by the destination predicate. The decisions are accept, remove, hold, or rebuild. Documented behavior, predicted results, and observations are kept separate; no production incident or executed benchmark is claimed. The scope is dbt Core with dbt-snowflake, incremental_strategy=merge, and a standard Snowflake table.

The dbt Configure incremental models guide explains that incremental models transform all selected rows on the first build and only selected source rows on later runs. With merge, a configured unique_key lets new information replace a matching existing row rather than append blindly. dbt also documents incremental_predicates as a scan-limiting optimization. Its seven-day example is a configuration example, not evidence that seven days covers your correction domain.

models:
  - name: fct_ledger_lab
    config:
      materialized: incremental
      unique_key: id
      incremental_strategy: merge
      incremental_predicates:
        - "DBT_INTERNAL_DEST.event_at > dateadd(day, -7, to_date('2026-09-21'))"

That predicate becomes an additional condition in the dbt merge match, alongside the key equality. Snowflake's MERGE command applies matched actions only when the ON expression matches; an incoming row that does not match follows the WHEN NOT MATCHED branch. The date filter therefore changes correctness, not merely scan volume. In this lab we hold source rows constant and change only the destination predicate.

The review proceeds in four stages: pin the environment and fixtures; prove that the correction enters the source batch; capture the executed MERGE from dbt.log or Snowflake query history; and compare clean control and optimized targets. Uniqueness, row-level reconciliation, and totals become promotion gates. If evidence is missing, the result is hold. If duplicates already exist, changing configuration is containment only; recovery must reconstruct the approved state and revalidate downstream outputs.

The result is a narrow acceptance playbook, not a default recommendation for every incremental model. The article provides executable lab SQL, predicted outcomes, failure signals, and a decision matrix. Until the proposed lab is actually run and its emitted SQL is captured, every result below is labeled expected rather than observed.

Define the update that must remain possible

Our scenario is simple: a historical record needs correction. In other words, an existing row in the target table (with a given business key) should be updated by a new incoming row. Invariant: if a source row has the same business key as one in the target, then after the dbt run the target row must reflect the new data (and only one row per key exists). An incremental predicate on the target must not make that existing row invisible to the MERGE’s ON clause. If it does, the MERGE will consider the incoming row as “not matched” and insert it instead, creating duplicates of the key.

Let’s introduce the four possible outcomes for this invariant:

•         Accept: The predicate is safe. The merge always matches and updates the target row correctly. We keep the predicate optimization.

•         Remove: The predicate is unsafe. We disable it (e.g. by deleting it from config) to avoid hidden rows.

•         Hold: We cannot confirm one way or the other (e.g. missing evidence, or the scenario is too complex). We pause and gather more logs/tests.

•         Rebuild: If duplicates were already introduced, we may need to rebuild the table from source to restore correctness before proceeding.

The dbt unique_key configuration expresses row identity for matching; it is not a database constraint and does not prove uniqueness. Snowflake's constraint overview states that PRIMARY KEY and UNIQUE constraints on standard tables are optional and not enforced, while NOT NULL and CHECK constraints are enforced. The acceptance gate must therefore query source and target grain directly, not infer safety from configuration or DDL.

Use the Refonte article on dbt and Snowflake incremental modeling background for broader architecture and materialization context. Here the review is narrower: a correction may enter the source batch correctly and still fail to match because the target predicate hides its historical row. The source owner defines valid correction age; the model owner proves match coverage.

Pin the model, adapter and execution environment

Pin the lab to dbt Core 1.12.5 and dbt-snowflake 1.12.1, both available before the September 21, 2026 research cutoff. Use a Snowflake standard table in database DBT_PREDICATE_LAB, schema ACCEPTANCE, and a dedicated XSMALL warehouse named WH_DBT_PREDICATE_LAB. Record the role, region, session parameters, model Git revision, and dbt --version output. Freeze lab_as_of as DATE '2026-09-21'; do not depend on the wall clock. The lab uses DATE columns for event_at and updated_at, integer amount_units, and isolated targets that can be dropped without affecting production.

Our model configuration (either in dbt_project.yml or the model’s config block) looks like this:

models:
  acceptance:
    +materialized: incremental
    +unique_key: id
    +incremental_strategy: merge
    +incremental_predicates:
      - "DBT_INTERNAL_DEST.event_at > dateadd(day, -7, to_date('2026-09-21'))"

The id column is the non-null business key. The event_at date drives the destination window, while updated_at drives source admission. These are independent questions: a correction can be recently modified and therefore selected from the source while still retaining an old event date that hides its target row.

•         Source filter: inside is_incremental(), use updated_at >= (select max(updated_at) from {{ this }}). This selects new and corrected source rows.

•         Target filter: DBT_INTERNAL_DEST.event_at > dateadd(day, -7, to_date('2026-09-21')). This limits existing rows that are eligible to match.

At run time, dbt templates the materialization into a MERGE statement. Capture the exact SQL from dbt.log or Snowflake query history; dbt compile alone may show the model SELECT without the final materialization DML. Inspect the MERGE ON clause to see exactly how unique_key and incremental_predicates appear. Record the dbt invocation ID, warehouse query ID, execution timestamp, and effective configuration so a reviewer can trace the evidence.

Keep every test relation in the isolated ACCEPTANCE schema and treat input fixtures as immutable. Because dbt configuration can be inherited, inspect manifest.json or an equivalent resolved-artifact view to confirm that materialized='incremental', unique_key='id', incremental_strategy='merge', and the intended predicate are effective for this model revision.

The proposed manifest now pins versions, warehouse, schema, model revision, configuration, and lab clock. It is still incomplete evidence until an executed run supplies the run ID, query ID, configuration digest, emitted MERGE text, and result rows.

Separate incoming-row selection from target matching

It’s crucial to distinguish two different filters in an incremental model:

•         Source filter: selects which rows to process on this incremental run. Typically expressed with is_incremental() in the model SQL. For example, WHERE updated_at >= (select max(updated_at) from {{this}}) means “give me only the new or changed rows since the last run.”

•         Target predicate: restricts which existing rows are eligible to match and update. It appears in the MERGE’s ON condition as a predicate on DBT_INTERNAL_DEST (the alias for the existing target table).

Meanwhile, the unique_key defines equality between source and target. A helpful table summarizes:

The source filter (like using is_incremental()) ensures the incoming batch has only the new data we expect. In our lab, we’ll verify that the correction row indeed satisfies the source condition, so it appears in DBT_INTERNAL_SOURCE. Then separately, the target predicate (the 7-day filter above) will hide old rows if their event_at is too old. It’s the interplay of unique key equality and the target predicate in the ON clause that decides whether an existing row is considered “matched” or not.

Before the MERGE, prove that the historical correction is present in DBT_INTERNAL_SOURCE and that the existing target row falls outside the destination predicate. This separation is central to the dbt incremental models guide: source filtering controls what is processed, while the destination predicate controls which old rows can match. Only after source admission is established should target invisibility be investigated.

Now we have configuration and context ready. Let’s build our counterexample.

Construct a counterexample with no input duplicates

We construct minimal seed data: two existing rows in the target table and then a single incoming correction. The keys (id) are unique and non-null. We freeze all dates relative to our lab date (2026-09-21) so tests are deterministic.

Seed the old and recent target rows

We put two rows into the target table. One is old (outside the 7-day window), one is recent. For concreteness, let’s say:

•         Old row (id=1): event_at = 2026-09-01, updated_at = 2026-09-01, amount_units = 100.

•         Recent row (id=2): event_at = 2026-09-18, updated_at = 2026-09-18, amount_units = 50.

Here, 2026-09-01 is 20 days before our lab date; 2026-09-18 is 3 days before. The 7-day cutoff from Sep 21 would be Sep 14. Thus id=1’s event_at is older than 7 days (and will fail the predicate > Sep 14), whereas id=2’s event_at is within the window. Both keys are unique and no fields are null.

In the disposable lab schema, initialize the clean target:

CREATE OR REPLACE TABLE target_table (
  id INTEGER NOT NULL,
  event_at DATE NOT NULL,
  updated_at DATE NOT NULL,
  amount_units INTEGER NOT NULL
);

INSERT INTO target_table (id, event_at, updated_at, amount_units) VALUES
  (1, TO_DATE('2026-09-01'), TO_DATE('2026-09-01'), 100),
  (2, TO_DATE('2026-09-18'), TO_DATE('2026-09-18'), 50);

These are our starting state. (In practice, we’d use dbt seed or manual INSERTs as needed.)

The fixture declares id, event_at, updated_at, and amount_units as NOT NULL. Do not treat a declared key as protection: Snowflake standard-table uniqueness constraints are not enforced. The clean starting grain is established by the seed and verified by a query.

Admit one correction for the old key

Now we craft a single incoming row for the old key (id=1), simulating a late correction. We keep the old event date (2026-09-01) but update the value and give it a current update timestamp:

•         Incoming row: (id=1, event_at=2026-09-01, updated_at=2026-09-21, amount_units=120).

The correction has updated_at=2026-09-21, which is greater than the current target maximum of 2026-09-18, so it passes the proposed source-selection condition. Its amount changes from 100 to 120, and the batch contains one row only.

CREATE OR REPLACE TEMP TABLE incoming_batch AS
SELECT
  1 AS id,
  TO_DATE('2026-09-01') AS event_at,
  TO_DATE('2026-09-21') AS updated_at,
  120 AS amount_units;

This row matches the old target row by id, and we expect the old amount_units (100) to become 120 after the merge. Because event_at is unchanged (and stale), we want to see if the target predicate will erroneously hide that row.

The independently expected control state is two rows: id=1 at 120 and id=2 at 50, with total amount_units of 170. The predicted flawed state is three rows: the old id=1 at 100, id=2 at 50, and a second id=1 at 120, with total 270. The paired run must start from separate clean targets and use the same incoming_batch.

Store that expected row ledger outside the model SQL before execution. Otherwise the test risks deriving its expected result from the same logic under review.

Inspect the merge the adapter actually executes

Run dbt in the disposable schema, then capture the executed statement from dbt.log or Snowflake query history. The dbt incremental predicate documentation shows the standard DBT_INTERNAL_DEST and DBT_INTERNAL_SOURCE aliases, but its merge illustration is abbreviated. Do not present that illustration as executable Snowflake SQL. Approval requires the statement associated with the actual dbt invocation, together with run ID, query ID, package versions, model revision, and configuration digest.

MERGE INTO target_table AS DBT_INTERNAL_DEST
USING incoming_batch AS DBT_INTERNAL_SOURCE
ON DBT_INTERNAL_DEST.id = DBT_INTERNAL_SOURCE.id
AND DBT_INTERNAL_DEST.event_at > DATEADD(
  day, -7, TO_DATE('2026-09-21')
)
WHEN MATCHED THEN UPDATE SET
  event_at = DBT_INTERNAL_SOURCE.event_at,
  updated_at = DBT_INTERNAL_SOURCE.updated_at,
  amount_units = DBT_INTERNAL_SOURCE.amount_units
WHEN NOT MATCHED THEN INSERT (
  id, event_at, updated_at, amount_units
) VALUES (
  DBT_INTERNAL_SOURCE.id,
  DBT_INTERNAL_SOURCE.event_at,
  DBT_INTERNAL_SOURCE.updated_at,
  DBT_INTERNAL_SOURCE.amount_units
);

This is an executable lab MERGE that mirrors the expected key equality and destination predicate; it is not presented as captured adapter output. Compare it with the emitted statement before drawing a conclusion. The proof obligation is that the unique-key equality and every DBT_INTERNAL_DEST predicate are located in ON exactly as expected.

Capture a compact evidence record beside the complete SQL:

run_id=<dbt-invocation-id>
query_id=<snowflake-query-id>
dbt_core=1.12.5
dbt_snowflake=1.12.1
model_revision=<git-sha>
lab_as_of=2026-09-21
config_sha256=<digest>

If the executed SQL differs from the expected shape, stop. Do not infer behavior from the model SELECT or from compile output alone; the materialization DML is the artifact under review.

Confirm that the dbt unique_key is resolved as id and that the incoming and target grains are both unique and non-null. The configuration enables matching behavior, but it does not enforce those invariants.

With fixtures and expected SQL shape defined, execute two isolated targets. Until query IDs and result rows are attached, the comparison in the next section remains a prediction.

Compare unfiltered and optimized target matches

The proposed experiment uses two targets cloned from the same two-row seed and one immutable incoming_batch:

• Control, no destination predicate: the ON condition contains only id equality. The expected result is an update of id=1 from 100 to 120, leaving two rows and a total of 170.

• Optimized, seven-day destination predicate: id=1 has event_at=2026-09-01, which is not greater than the frozen cutoff of 2026-09-14. The historical target row is excluded from ON. Under Snowflake MERGE semantics, the single incoming row follows WHEN NOT MATCHED and is inserted beside the retained historical row. The expected result is three rows and a total of 270.

•         id=1 with amount 100 (old row),

•         id=2 with amount 50,

•         id=1 with amount 120 (new inserted duplicate key).

The comparison table records predicted results, not observations. After each run, save the complete row set, count, total, query ID, and target reset evidence.

Snowflake's WHEN NOT MATCHED behavior applies when a source row has no target match under ON. In this fixture the source batch contains one row. The duplicate key is predicted because the historical target row is excluded from ON, so the one source row is inserted beside it. This is different from Snowflake's separate duplicate-source example and must not be described as an input-duplicate failure.

Verify row-level and aggregate state with:

SELECT id, event_at, updated_at, amount_units
FROM target_table
ORDER BY id, amount_units;

SELECT
  COUNT(*) AS row_count,
  SUM(amount_units) AS total_amount_units
FROM target_table;

The expected optimized output is (1,100), (1,120), and (2,50). If the actual result differs, hold the decision and inspect the emitted SQL, target reset, source batch, and session settings. Do not edit the expected ledger after seeing the outcome.

The predicted optimized total is 270, compared with 170 for the control. These are synthetic expected values. Only captured result sets can convert the prediction into an observation.

This clean case is not designed to trigger a nondeterministic-merge error: the source contains one row and the target begins unique. The intended failure is an unmatched insert. If the run errors, hold and investigate rather than forcing the result into the expected narrative.

A successful dbt exit code is not acceptance evidence. Accept only when the control and optimized policies produce the same approved row set, key cardinality, and total for every fixture in the correction domain.

If either run produces another outcome, check the effective predicate, source batch, transaction isolation, target reset, date/session settings, and emitted SQL. A discrepancy is evidence to investigate, not a reason to retrofit the expected values.

Our next step is to explore edge cases and ensure we understand the boundary behaviors precisely, so our tests don’t miss anything.

Test the predicate boundary and replay behavior

The core fixture predicts that a strict greater-than predicate hides an old row. Extend the lab one assumption at a time so boundary, null, future-date, and replay behavior cannot be confused with the original clean-key defect.

•         How the predicate handles equality at the boundary,

•         What happens with nulls or future dates,

•         What re-running the same batch does.

This will give a complete picture and help us generalize rules (for example, is the predicate effectively >= or >?).

Check equality, nulls and future values separately

Add a target row exactly at the frozen cutoff, event_at=DATE '2026-09-14'. The expression event_at > DATE '2026-09-14' is false at equality, so that row is outside the candidate match set.

•         Row with (id=3, event_at=2026-09-14, updated_at=2026-09-14, amount_units = X). This is exactly 7 days old.

An admitted correction for the boundary key is therefore predicted to insert rather than update under the strict predicate. If policy intends to include the boundary, the operator must change to >=, and that change requires its own fixture and review.

Test nulls separately. In the pinned fixture event_at is NOT NULL, but legacy or alternate sources might violate that contract. A comparison with NULL evaluates to unknown, which does not satisfy the predicate and therefore hides the target row. Fail a non-null gate before attributing that case to the clean historical-correction counterexample.

Test future event dates separately as well. A future value satisfies the target predicate, but predicate visibility does not make the timestamp valid. The boundary test records whether the row can match; a separate data contract decides whether future events are accepted, corrected, or quarantined.

Replay an identical admitted correction

Finally, we ask: what if we run the same incoming correction again on the already-merged table? This checks idempotency and potential cascading bugs. Consider both versions:

• Replay on a corrupted target: stop. Once duplicate keys exist, the original clean-key fixture no longer applies. Do not use a retry as a repair procedure. Preserve the table, query IDs, emitted SQL, and affected keys, then reconstruct the approved state in a separate relation.

• Replay from a clean target: reset to the two-row seed and run the identical batch twice under each policy. The control should remain at two rows and total 170. Under the flawed destination predicate, the first run is predicted to create the duplicate; the second run is a separate corruption behavior that must be measured, not assumed. The acceptance gate already fails when first-run cardinality changes.

The Snowflake nondeterministic-merge rules describe specific multiple-source match conditions; they do not prove the replay result for an already-corrupt target with duplicate business keys. Stop at the first invariant breach and repair from canonical history.

Boundary tests define the exact comparison policy. Replay tests prove that a clean accepted policy is idempotent. Neither test justifies continuing normal merges against a target whose key invariant is already broken.

Turn uniqueness and reconciliation into executable gates

To make this process reliable, we convert the above reasoning into concrete tests that can be run after each dbt run. These act as quality gates in CI or deployment. Key gates include:

•         Source uniqueness: Ensure the unique_key is unique within the incoming batch. For our example, run a query like:

    SELECT id, COUNT(*) AS cnt
FROM incoming_batch
GROUP BY id
HAVING COUNT(*) > 1;

The source query must return zero rows, and a separate check must return zero rows for id IS NULL. A failure is an input-grain defect that must be investigated separately from destination-predicate match coverage.

•         Target uniqueness: After the merge, assert that no duplicate unique_key exists in the target. For example:

    SELECT id, COUNT(*) AS cnt
FROM target_table
GROUP BY id
HAVING COUNT(*) > 1;

The target query must also return zero rows. This is where data-quality contracts and ownership become operational: the source owner defines the business key and admissible corrections, the model owner preserves one row per key, and the downstream reviewer blocks promotion when either invariant fails.

•         Row-level reconciliation: If we have a small canonical source of truth (e.g. a mini reference table for this unit test), we can join it against the target and check for mismatches. For instance, if we know the correct amounts after the merge (id=1 → 120, id=2 → 50), a query like:

    SELECT t.id
FROM target_table t
JOIN reference_table r ON t.id = r.id
WHERE t.amount_units != r.amount_units;

should yield no rows. This checks that each row in the target matches the expected. In larger scenarios we might compare totals or hashes of partitions.

•         Aggregate checks: Independently compute totals. For example,

    SELECT COUNT(*) AS cnt, SUM(amount_units) AS total_amount
FROM target_table;

and compare to a pre-computed correct value (in our cases, 2 rows and sum=170). A mismatch signals a problem.

Implement these assertions as dbt data tests or SQL gates in the isolated workflow. A post-merge test can block promotion, but it cannot retroactively prevent the intermediate target from being damaged. Preserve the failed relation and its evidence before cleanup.

Ownership must be explicit. The source owner attests to input grain and correction policy. The model owner supplies emitted SQL, target-grain tests, and reconciliation. The downstream reviewer decides whether dependent outputs may advance.

By turning the invariant (“every correction matches exactly one row”) into automated checks, we make “match coverage” part of our quality contract. No more blind faith in optimization.

A uniqueness or reconciliation failure blocks promotion. Removing the predicate is the model decision when match coverage is unsafe; rebuilding or targeted reconstruction is the data decision when the target is already corrupt.

Establish when scan reduction is actually safe

The counterexample predicts that a seven-day target predicate is unsafe when an admitted correction can reference an older event date. Any scan-reducing predicate is acceptable only when every valid correction either remains inside the candidate target set or is routed through a separately controlled historical-correction path.

A sufficient condition is a documented and enforced bound N such that every admitted correction targets a row whose predicate column remains within N days of lab_as_of. Evidence can include a contractual rule, source-system enforcement, and historical exception analysis. A percentile or 'almost always' statement is not a correctness bound.

Do not prove a target event_at window by looking only at recent updated_at values. The correction in this lab has a recent updated_at and an old event_at. The evidence must evaluate the same target column used by DBT_INTERNAL_DEST and must cover the full admitted correction population.

Three defensible designs exist: no destination predicate; a predicate backed by a proven correction bound; or a separate historical-correction job that reads approved canonical history and uses unrestricted matching for affected keys. A periodic full refresh is a recovery mechanism, not proof that routine merges are correct between refreshes.

Importantly, changing “7 days” to a longer window (e.g. 30 days) is not inherently correct unless you can justify it. It just delays the breakage. There is no magic fix unless you know the true bounds of late data. We should clearly state: a target-side filter is only safe if its window fully covers all possible updates. That might come from SLA, data contracts, or business logic. If you cannot assert that, the only fully safe state is no target predicate (which just means reading the whole table, slower but correct).

This safety rule is an engineering inference from two documented facts: dbt accepts user-supplied incremental predicates and does not validate their SQL, while Snowflake selects matched and unmatched actions from MERGE ON. Scan reduction is therefore acceptable only after match coverage is proven for the admitted correction domain. Measure performance after that gate, never in place of it.

Repair the data, not just the model configuration

If our tests found that the predicate was unsafe (as in our example), we must repair the data before moving forward. Simply removing the predicate from the model will prevent new duplicates, but it does not fix the duplicates already created in the target. Therefore, the repair procedure has two parts: fixing the target table, and reconciling downstream outputs that may have read the bad data.

Rebuild from an approved source of truth

Build a candidate relation in an isolated schema from approved history and a pinned transformation revision. dbt's full-refresh behavior can rebuild an incremental model, but a production full refresh may drop and recreate the relation and may include downstream models when selected with a graph operator. Use it only after permissions, source completeness, cost, dependency impact, and rollback are approved.

If a full rebuild is not approved, reconstruct only a reviewed set of affected keys while retaining verified unaffected rows. The replacement rows must come from an approved canonical relation, not from choosing one duplicate by timestamp. A disposable-lab pattern is:

CREATE OR REPLACE TEMP TABLE affected_keys (id INTEGER NOT NULL);
INSERT INTO affected_keys VALUES (1);

CREATE OR REPLACE TABLE target_table_candidate AS
SELECT t.id, t.event_at, t.updated_at, t.amount_units
FROM target_table AS t
LEFT JOIN affected_keys AS k USING (id)
WHERE k.id IS NULL
UNION ALL
SELECT c.id, c.event_at, c.updated_at, c.amount_units
FROM approved_canonical_source AS c
JOIN affected_keys AS k USING (id);

Before using that pattern, prove that approved_canonical_source contains exactly one non-null row for every affected key and that every affected key is represented. If canonical history is incomplete, stop; do not guess which duplicate to keep.

Do not overwrite the production target yet. Run uniqueness, non-null, row-level, and aggregate checks against target_table_candidate. Only after review may the owner perform a bounded rename or swap under the organization's change procedure.

Record the affected-key query, source snapshot or ledger version, transformation revision, candidate relation, reviewer, approval time, and rollback relation. That evidence is part of recovery, not optional documentation.

Reconcile before downstream promotion

Finally, consider any downstream tables or analyses that already consumed the faulty target data. If reports or models ran with the duplicated key, they might have incorrect aggregations. We should reconcile those too. A pragmatic approach: rerun any downstream models that depend on this target once the target is fixed. If that’s not possible (e.g. very late stage), at least document the discrepancy.

For example, if a summary metric assumed one row per id, it likely double-counted id=1. We should recompute such aggregates from the fixed target and compare. A simple way:

WITH before_state AS (
  SELECT id, COUNT(*) AS row_count, SUM(amount_units) AS total_units
  FROM old_target_table
  GROUP BY id
),
after_state AS (
  SELECT id, COUNT(*) AS row_count, SUM(amount_units) AS total_units
  FROM target_table_candidate
  GROUP BY id
)
SELECT
  COALESCE(b.id, a.id) AS id,
  b.row_count AS before_rows,
  a.row_count AS after_rows,
  b.total_units AS before_units,
  a.total_units AS after_units
FROM before_state AS b
FULL OUTER JOIN after_state AS a USING (id)
WHERE COALESCE(b.row_count, -1) <> COALESCE(a.row_count, -1)
   OR COALESCE(b.total_units, -1) <> COALESCE(a.total_units, -1);

See where they differ. Recoveries may involve backfilling reports or alerts. The key point is: stop any pipeline promotion until all dependent outputs are validated against the repaired data. This might mean rolling back a scheduled report or blocking a deployment.

We should also plan to prevent reintroduction. Once we understand the fix, we must adjust our data contracts or monitoring so that future cases of this kind (if any) are caught. For instance, if we assumed 7-day cutoff but corrections arrived 20 days late, maybe adjust the source process or set up an alert if any correction arrives older than expected.

Apply the same approval, rollback, and evidence discipline described in Refonte's guidance on warehouse governance and controlled maintenance. Full refreshes, swaps, and downstream backfills must follow the organization's permissions and change windows.

In short: fixing the model config is not enough; we must fix the data. Use the source history and domain rules to rebuild the correct state, then verify it with tests before proceeding.

Measure performance only after correctness passes

Once the data is correct and the predicate is either accepted or removed, teams often want to quantify the performance impact of the change. It’s important to do this methodically, without compromising correctness. Here’s how to approach it:

• Comparable runs: Use the same warehouse size (e.g. XSMALL), same versions, and ideally the same data volume. For example, run with predicate off and on, each on a fresh copy of the target (so that caching or clustering is comparable). Collect both Snowflake query IDs.

• Use query history: retrieve the paired MERGE statements through Snowflake QUERY_HISTORY. Record query_id, query_text, warehouse_size, query_tag, total_elapsed_time, bytes_scanned, rows_produced, compilation_time, execution_time, and queue times. Treat per-query warehouse-credit attribution separately; do not invent it from fields that QUERY_HISTORY does not provide.

• Repeat comparable trials: reset to clean, equivalent targets and hold source batch, warehouse size, session parameters, role, model revision, and clustering state constant. Publish raw trials and a robust summary; do not hide cache or warehouse-resume effects inside one average.

• Focus on correctness metrics first: Critically, if any correctness test from above still fails with the predicate on, then performance is moot. We might measure performance only in the acceptance case. Indeed, if we decide to remove the predicate for correctness, performance numbers with the wrong predicate are irrelevant.

• Correlate run evidence: connect dbt invocation IDs, Snowflake query IDs, configuration digests, and assertion results. This is the practical value of operational evidence across runs: a performance claim can be traced back to the exact correctness-approved execution.

No performance result is reported here because the lab has not been executed. When you run it, publish comparable query IDs and raw measurements, not a headline percentage. A lower scan is relevant only if row-level, key-cardinality, and aggregate gates remain identical to the approved control.

Correctness is the admission gate. If any invariant fails, the faster query is rejected and the logic is repaired before performance is discussed.

Package regression fixtures with the model change

To ensure this scenario never regresses, we add a new automated test (or fixture) to the project. This includes:

•         Input fixtures: tiny source tables/files. For example, a CSV or seed SQL table with the initial two rows (id=1 old, id=2 recent) and a separate one with the incoming correction. These should live in version control as test data.

•         Expected outputs: A file or SQL query that defines the expected target table after running with/without predicate. For example, in the test we assert that “final target has rows (1,120) and (2,50) only.” We can encode this as a dbt data test or a simple SQL assertion.

•         Assertion SQL: As described in section 8, e.g. uniqueness tests and sum checks. We include the SQL queries that should return no rows (or specific values) when things are correct.

•         dbt configuration & run record: Record exactly which dbt version and configuration was used for the test. For example, in the test we could log dbt --version output or record the run_results.json. This ensures that if something changes in dbt or the adapter, we can revisit the test context.

•         Captured evidence: save the complete emitted MERGE statement, run ID, query ID, resolved configuration, package versions, and result assertions. Do not store a shortened statement with omitted ON conditions.

Package two isolated scenarios: the control must pass, and the optimized case must produce the same approved state before the predicate can be accepted. During defect reproduction, the optimized fixture is expected to fail the uniqueness gate; after repair, the regression suite should encode the approved policy rather than ship a permanently failing production test.

Keep fixtures in a dedicated schema and run only the model and assertions required for this boundary. Pin dbt Core, dbt-snowflake, Python, model revision, warehouse settings, and lab_as_of. Retain the historical-row correction whenever the destination predicate changes.

Treat the fixture as part of the broader data engineering testing and pipeline foundations. Reviewers should receive inputs, expected outputs, assertion SQL, emitted DML, and cleanup steps in one change set.

With these tests in place, any change to this model (or to the dbt runtime) must still pass them. It becomes a first-class part of our continuous integration, not a one-off investigation.

Choose accept, remove, hold or rebuild

At this point we have all the evidence: test results, query logs, and data comparisons. We can fill in the decision matrix:

For this synthetic fixture, the predicted optimized outcome falls under Remove. If that outcome is reproduced against an existing target, the data-repair decision becomes Rebuild or a controlled targeted reconstruction. Because this document does not include executed run evidence, the decision for a real model remains Hold until the paired run and reconciliation are captured.

The matrix is an engineering proof obligation, not a universal vendor recommendation. A predicate can be accepted for a defined correction domain when match coverage is proven; it must be removed when the same admitted batch produces a different approved state.

A test failure does not automatically require a full rebuild. Remove or repair the predicate to stop new damage, then choose targeted reconstruction or full rebuild according to affected-key scope, source completeness, dependencies, cost, permissions, and rollback confidence. If canonical history is insufficient, hold rather than improvise.

No one said “this is a dbt or Snowflake bug”; this is our infrastructure at work. If, however, we suspected a bug in dbt’s handling of predicates (unlikely given the docs), we’d raise it after ruling out data issues. For now, the evidence points to “predicate hides rows” being intended behavior (by construction), not a bug.

Make match coverage part of analytics engineering practice

Make destination-predicate review a standard change gate. For every model, name the correction domain, prove source admission, inspect the emitted ON condition, run the historical-row fixture, and attach key-level reconciliation before approving scan reduction.

These review habits draw on the ingestion, transformation, storage, and governance foundations described on the Refonte Learning Data Engineering Program page. Review the published curriculum and its stated three-month, 12-14-hours-per-week format to assess whether it matches your learning goals.

Never accept a destination predicate because a run succeeded or a declared key looks unique. Accept it only when every admitted correction can still match exactly one existing row, the optimized output equals the approved control, and the evidence can be reproduced.