Unique keys are not optional in analytical incrementals

An incremental model needs a stable answer to one question: which existing output row represents the same analytical fact as this source result?

That answer is the unique key. Without it, the model can append, replace partitions, or guess. It can’t perform a reliable row-level merge.

Derive the key from grain

State the grain before choosing columns.

• one order uses the analytical order identifier
• one order line usually uses order plus line identity
• one customer per day uses customer plus business date
• one account-product state uses account plus product, and perhaps another boundary such as market or contract

The source primary key may match. It may also be scoped to a tenant, recycled after migration, or split across several records at the analytical grain. The model owns the composite key it needs.

A useful key is stable under ordinary corrections. If changing an email address or status produces a new key, the hash included evidence rather than identity. If two distinct facts share a key, the grain is broader than the model admits.

Validate the source result before merging

A merge source must produce at most one row per target key. Dataform’s uniqueKey tells the incremental action how to match rows. It doesn’t repair duplicate source results.

Assert uniqueness on the final source query or deduplicate through an explicit business rule before the merge. Never rely on incidental query order or any_value() to hide unresolved identity.

select
  order_id,
  line_id,
  count(*) as row_count
from ${ref('order_lines_current')}
group by order_id, line_id
having count(*) > 1;

When duplicates occur, preserve enough evidence to classify them. They may be source retries, overlapping extraction batches, genuinely distinct entities, or a missing key component. “Take the latest” is valid only when latest is a defined source contract.

Make null semantics explicit

A key component that can be null needs a deliberate interpretation.

SQL equality doesn’t treat two nulls as equal, and string concatenation can collapse nulls and empty strings into the same synthetic key. Either reject null key components, map an explicit domain value, or use a canonical encoding that preserves type and null state.

Don’t hash ambiguous concatenated strings. Structure the fields, include boundaries or typed serialization, and keep the unhashed components in the table for debugging. Compactness is useful. Opacity isn’t.

Surrogate keys don’t create identity

A generated row number or random UUID makes rows unique at insertion time. It doesn’t tell a later run which row should be updated.

A deterministic hash of the real business key can be a useful surrogate for joins and storage. Its correctness still depends on the underlying grain and canonical encoding. If the source identity changes legitimately, decide whether that means a new fact, a key migration, or an update connected through another durable identifier.

The surrogate implements the identity decision. It doesn’t make the decision.

A key is necessary but not sufficient

The model also needs to know which existing keys should be reconsidered. A perfect key can’t update a row that never enters the incremental source query.

Late child records, source corrections, dimension changes, and deletions require explicit change detection. The key answers where. The affected set answers when.

Deletion deserves special treatment. When a source row disappears, the model still needs the old target key to delete or deactivate it. Current source state alone may not contain that evidence.

If there’s no stable key, change the materialization

Some analytical outputs don’t have durable row identity. A result may be a temporary aggregation whose membership changes broadly, or the source may lack enough information to distinguish facts.

Use partition replacement, a full rebuild, or remodel the output. Don’t force a synthetic merge key because incremental execution looks more efficient. A wrong key makes the model cheaply wrong, which isn’t much of an optimization.

Test key transitions

Test more than duplicate-free first load. Verify corrections keep the same key, distinct facts remain distinct, nulls fail or map intentionally, replays converge, and key migrations follow the declared policy.

Compare the incremental result with a full build at the same source state. If they disagree, the identity or affected-set contract is incomplete.

A model has earned row-level incrementality when its grain makes the key obvious, the source is unique at that key, and every supported change can find the same target row again. Until then, use a materialization whose correctness doesn’t depend on identity the data can’t supply.