Materialization 101

Choose the right pattern for the job

Build idempotent merge logic

1. Stage events in a temporary table keyed by primary key and log position.
2. Deduplicate by picking the highest ts_ms (or change version) per key.
3. Merge updates and inserts using the latest event per key.
4. Apply deletes as tombstones that remove or flag the row.
5. Record the processed offset so restarts can resume safely.
6. Publish metrics: rows inserted, updated, deleted, skipped duplicates.

When duplicates land, the last change wins rule keeps results stable. Do not rely on arrival order; rely on source timestamps or change versions.

Capture merge SQL in version control and tag it with the connector version it expects. This makes rollbacks trivial when envelope changes introduce new columns.

SQL templates you can adapt

MERGE INTO analytics.orders tgt
USING staging.orders_src src
  ON tgt.order_id = src.order_id
WHEN MATCHED AND src.op = 'd' THEN DELETE
WHEN MATCHED THEN UPDATE SET
  amount = src.amount,
  status = src.status,
  updated_at = src.ts_ms
WHEN NOT MATCHED AND src.op IN ('c', 'u') THEN
  INSERT (order_id, amount, status, updated_at)
  VALUES (src.order_id, src.amount, src.status, src.ts_ms);

CREATE OR REPLACE TABLE analytics.orders AS
SELECT AS VALUE latest.*
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY ts_ms DESC) AS rnk
  FROM staging.orders_src
)
WHERE rnk = 1 AND op != 'd';

{{
  config(
    materialized='incremental',
    unique_key='order_id'
  )
}}

with ranked as (
  select *,
         row_number() over (
           partition by order_id
           order by ts_ms desc
         ) as rnk
  from {{ source('cdc', 'orders') }}
)

select * except(rnk)
from ranked
where rnk = 1 and op != 'd';

Keep history when you need it

• Add valid_from and valid_to columns around each change.
• Close the previous version when a new update arrives.
• Expose current rows via a view that filters on valid_to IS NULL.

This pattern powers SCD Type 2 tables without losing the benefits of CDC freshness.

Handle late-arriving and out-of-order events

• Track a watermark timestamp in your staging table and ignore events older than your replay budget.
• Store raw envelopes for N days so you can reprocess with updated merge logic.
• Document how to reconcile when the source database is manually patched (backfills, deletes).

Late arrivals are usually replayed by ops teams—make the procedure idempotent so you can run it twice without fear.

Operational guardrails

Combine these runbooks with automated smoke tests that upsert and delete a synthetic record daily. The job should fail if materialization drifts.

Data quality checks before publishing

1. Compare row counts between source and target using time-bounded windows.
2. Verify key uniqueness and detect primary-key churn with a duplicate audit query.
3. Validate soft deletes by ensuring tombstones propagate to downstream marts within SLA.

Embed these checks in orchestration (Airflow, Dagster, dbt) so they run on every deploy and after infrastructure maintenance.

Verification playbooks

Treat verification as part of the deployment pipeline—pair SQL reviews with a matching verification checklist so no merge ships without evidence that the materialized view still behaves.

Materialization readiness scorecard

Align the data platform, analytics, and ops teams on what “ready for production” means before turning on the CDC materialization job.

Further resources

• Event envelope for envelope field semantics feeding your merges.
• Offsets & replays to keep staging tables consistent during rewinds.
• Observability basics to monitor merge throughput, freshness, and failure rates.