Write strategies and SCD2

A write strategy defines how a batch of extracted rows is applied to the destination table. It only matters for delta and iceberg output; CSV and Parquet always produce new files.

Resolution order

data_retrieval:
  write_strategy: merge          # optional global default for every table
  databases:
    - name: SalesDb
      sync_mode: ct
      tables:
        - name: dbo.*
          all_columns: true
        - name: dbo.Customer
          all_columns: true
          write_strategy: scd2   # per-table override

1. write_strategy on the table entry, when present.
2. data_retrieval.write_strategy, when present.
3. merge, the behaviour the agent has always had.

Both settings are optional. A configuration that never mentions write_strategy behaves exactly as before the setting existed: each table is upserted by primary key. Setting the global value to scd2 turns on history tracking for every table in every database at once, while individual tables can still opt back to merge or another strategy.

Strategies

Strategy names are case-insensitive. Unknown values fail validation at startup and in --config-check.

SCD Type 2

write_strategy: scd2 keeps every version of every row rather than overwriting it. Three metadata columns are added to the Delta or Iceberg table:

Per run, for each primary key in the batch:

• New key: one row is inserted with _scd_is_current = true and _scd_valid_to = NULL.
• Changed values: the current version is closed (_scd_valid_to set to the batch timestamp, _scd_is_current = false) and a new current version is inserted.
• Unchanged values: nothing is written, so a full extract that returns identical rows does not inflate history.
• Deleted in source (CT/CDC delete, or missing from a full snapshot): the current version is closed and no new version is inserted. The key has no current row until it reappears.

All versions written in one run share one _scd_valid_from timestamp, which makes it easy to reconstruct the state of the table after a given run.

Requirements and fallback

• Output format must be delta or iceberg.
• The table must have a primary key (or primary_key_columns declared on the entry). If scd2 is requested for a table without one, the agent logs a warning and uses merge for that table. This keeps a global write_strategy: scd2 safe for databases that contain heaps or keyless tables.
• Column-level masks are applied before change detection, so masked values are compared consistently between runs.
• Existing tables cannot be switched to scd2 in place. If the target table exists without the _scd_* columns the table is reported as failed with a message asking for a one-time reload. Rename or remove the target table (and, for Iceberg, the catalog entry) and let the agent recreate it with a full load; every row of that first load becomes the initial current version.

Querying SCD2 tables

Current state:

SELECT * FROM sales_customer WHERE _scd_is_current = true;

As of a point in time:

SELECT * FROM sales_customer
WHERE _scd_valid_from <= '2026-08-01 00:00:00'
  AND (_scd_valid_to IS NULL OR _scd_valid_to > '2026-08-01 00:00:00');

Keys deleted in the source have no current row:

SELECT CustomerID FROM sales_customer
GROUP BY CustomerID
HAVING SUM(CASE WHEN _scd_is_current THEN 1 ELSE 0 END) = 0;

Choosing a strategy

Write strategies interact with Schema drift handling in the same way: the drift policy is evaluated on every Delta or Iceberg write regardless of strategy.