Skip to content

Slowly Changing Dimensions (SCD2)

SCD2 is a catalog write mode that keeps history instead of overwriting it: every write end-dates the rows whose tracked columns changed and inserts new versions alongside the ones that stayed the same. Use it for dimension tables — customers, products, accounts — where you need to know what a row looked like at a past point in time, not just what it looks like now. This page covers how the write behaves, the columns it generates, how to read history back, and the rules that protect an SCD2 table from an incompatible write.

What an SCD2 write does

Set Write Mode to scd2 on a Catalog Writer node, or pass write_mode="scd2" to write_catalog_table. Each write compares the input against the table's current rows, keyed on the business key (the same merge_keys / Key Columns field used by upsert/update/delete):

  • A business key with no current row is a new row — inserted as version 1.
  • A business key whose compared columns changed — the current row is end-dated and a new version is inserted.
  • A business key whose compared columns are unchanged is left as-is; nothing is written for it.

The first write to a table (or the first write after the table was deleted) is an initial load: every input row becomes a current version, with no comparison against anything.

Generated columns and half-open validity

Every SCD2 write adds four columns to the table, alongside the input's own columns:

Column Meaning
sk Surrogate key — one value per row version
valid_from UTC timestamp this version became current
valid_to UTC timestamp this version stopped being current, or empty while it still is
is_current true for the current version of a business key, false for a superseded one

A row's validity window is half-open: [valid_from, valid_to). The current version's valid_to is empty, not a large sentinel date — check is_current or valid_to IS NULL to find it. The generated column names can be overridden on the writer's SCD2 settings if sk/valid_from/valid_to/is_current collide with an existing column.

Deterministic surrogate keys

sk is a deterministic hash of the business key and valid_from — not a random UUID and not an auto-incrementing counter. Re-running the exact same write twice produces the exact same sk for the exact same version, which is what makes a no-op re-run genuinely a no-op: if nothing in the input changed since the last write, Flowfile detects that no business key is new or changed and skips the write entirely — no new Delta version is committed, and updated_at on the catalog table does not move.

Business keys must be string, integer, boolean, date, or datetime — floating-point and nested (list/struct) keys have no stable way to be encoded into the surrogate key and are rejected.

Change detection scope

By default, every column in the input other than the business key and the four generated columns is compared for change detection. Narrow this with Compare Columns (scd2_compare_columns in Python): only the listed columns are checked, so a change in an untracked column does not trigger a new version.

Full snapshot vs incremental

By default (Full Snapshot off), a business key that is absent from one write's input but present in an earlier write is left untouched — it stays current. Turn Full Snapshot on when each write is a complete replacement of the dimension: business keys missing from the current input are then end-dated, the same as a changed row, so the "current" set always matches the most recent full input exactly.

What the writer passes downstream

An SCD2 write is the one catalog write mode whose output differs from its input: the node emits the input's own columns followed by the four generated columns, so a downstream node can use the surrogate key of the version this run wrote — to load a fact table against the dimension it was just built from, for example, without a second read of the table. Every other write mode (including overwrite onto an SCD2 table) passes its input through unchanged, with no extra columns.

On the canvas the writer only grows an output handle once SCD2 is the selected write mode; in every other mode it stays an endpoint with nothing to connect.

The Output setting on the writer (scd2_output_mode in Python) picks which rows come out. All three choices produce the same columns, so the schema shown on the canvas holds whichever you pick:

Output Rows
All records that are inputted ("input", the default) The rows you fed in, in the same order and the same number, each carrying the generated columns of its current version — the key minted by this write for a new or changed row, the existing key for an unchanged one
All changed records ("changed") Only the row versions this write touched: every row it inserted plus every row it end-dated. A changed business key therefore appears twice — its closed old version and its new current one
All active records ("current") The table's entire current slice, including business keys that were not in this run's input

Two cases are worth knowing:

  • A skipped write (nothing new, nothing changed) still emits: input returns the full key map for the rows you fed in, current returns the whole current slice, and changed returns zero rows because nothing was touched.
  • An empty batch returns zero rows in input and changed mode, with the full column list intact, so a downstream node never sees a different schema on a run that happened to have no data. In current mode it returns the table's current slice, which an empty batch never end-dates.

input mode never invents rows: a full_snapshot close-out of a key that is absent from the input does not appear in it. Use changed mode to see close-outs.

Reading history

A Catalog Reader on an SCD2-tracked table shows a History selector (scd2_view in Python):

View Behavior
All records (default) (None on the wire) No filter — every version of every row
Active records ("active") Only current rows (valid_to empty)
Active at a point in time ("active_at", with a timestamp) The version that was current at that point in time — a half-open comparison against valid_from/valid_to

The default is all records, not active-only: a Catalog Reader pointed at an SCD2 table with History left unset returns full history. Set History to Active records explicitly whenever the downstream flow expects one row per business key — a join or aggregation over unfiltered history silently multiplies rows per key.

The tested example below writes the same two-row dimension twice, changing one row's tier on the second write, then reads both the active set and the full history:

import flowfile as ff

customers_day1 = ff.from_dict(
    {"customer_id": [1, 2], "tier": ["free", "pro"], "city": ["Amsterdam", "Berlin"]}
)
ff.write_catalog_table(
    customers_day1, "docs_customers_scd2",
    schema=ff.default_schema(), write_mode="scd2", merge_keys=["customer_id"],
)

customers_day2 = ff.from_dict(
    {"customer_id": [1, 2], "tier": ["pro", "pro"], "city": ["Amsterdam", "Berlin"]}
)
# The write hands the rows back with their surrogate key and validity window attached.
keyed_day2 = ff.write_catalog_table(
    customers_day2, "docs_customers_scd2",
    schema=ff.default_schema(), write_mode="scd2", merge_keys=["customer_id"],
)

current = ff.read_catalog_table("docs_customers_scd2", schema=ff.default_schema(), scd2_view="active")
history = ff.read_catalog_table("docs_customers_scd2", schema=ff.default_schema(), scd2_view="all")

SQL Editor and codegen

Querying an SCD2 table from the SQL Editor or via flowfile_frame's SQL context returns every version, the same as an unfiltered scd2_view. Add WHERE valid_to IS NULL (or the table's configured is_current column) yourself to get only current rows.

Protection rules

An SCD2-tracked table only accepts further scd2 writes or a plain overwrite:

  • append, upsert, update, delete against an SCD2-tracked table fail at run time with an error. These modes would insert or mutate rows without maintaining valid_from/valid_to/is_current, corrupting the version history.
  • overwrite is allowed and rebuilds the table as a normal table with the current write's data — it clears SCD2 tracking. The four generated columns are removed along with the rest of the history; a subsequent scd2 write treats the table as a fresh initial load.
  • Writing scd2 onto a table that already exists but was not created with scd2 also fails. Flowfile does not convert an existing plain table in place.

Re-initializing a table as SCD2: if you need to start SCD2 tracking on data that already has a plain table, either write to a new table name, or delete the existing table first (from the catalog browser or via the API) and let the next scd2 write perform the initial load.

Partitioning advice

New SCD2 tables are partitioned by the is-current column by default: closed versions are quarantined into files that later writes never rewrite, and both the write's own change detection and Active records reads scan only the current partition. Any partition_by columns you choose nest above it (for example region, giving region/is_current partitions), and the Partition on is_current switch (scd2_partition_on_current in the Python API) is the deliberate opt-out. All of this takes effect only on the table's initial load, since Delta partitioning is immutable afterward. Flowfile rejects partitioning by sk or valid_from (a partition_by validation error): both grow unboundedly with every write and would produce an ever-increasing number of tiny partitions.

Concurrent writers on object storage

No locking provider for S3/remote catalogs

Flowfile's Delta writes use delta-rs's optimistic concurrency control, with no external locking provider configured. On a local filesystem this is safe. On S3 (or another object-storage-backed catalog), two writers committing to the same table at the same time can conflict, and SCD2's read-then-merge write is inherently more conflict-prone than a plain append. Do not schedule two flows that write to the same SCD2 table concurrently against an object-storage catalog.