Data Breadcrumbs in the Common Integration Layer

What we’re solving, and exactly how to do it with dual SCD2, PIT/exposure views, and dbt on Databricks

When we debug odd results, we don’t just need dataset-level lineage — we need a row-level story: which source snapshots fed the row and which logical steps shaped it (select, derive, filter, aggregate, union, pivot).

Our integration layer is already structured around these transformation stages, as outlined in Modeling Data Transformations in Logical Steps. Data Breadcrumbs make that story portable at the row level — cheap to store, trivial to query, and aligned with our bi-temporal SCD2 and PIT/exposure patterns.

The problem we want to solve

1. Row-level provenance is opaque.
   We can trace datasets and jobs, but it’s hard to answer, for this exact row, “which snapshots and which steps produced it?”
2. Equivalence after changes is expensive to prove.
   When we refactor or optimize (new filters, new unions, engine migration) we need to show we used the same upstream snapshots or — if different — pinpoint what changed.
3. Impact analysis is slow.
   A new reference-data release should be traceable to all affected rows with one filter, not a detective story through logs and UIs.

What we’re aiming for

A portable, row-level trace that travels with every integration row and tells us:

• the functional snapshots used (e.g., crm.customers@2025-09-30#2),
• the logical steps applied (filter:is_active=1, union:crm+erp, pivot:month),
• the build context (dbt run id, code version, timestamp).

Deterministic equivalence checks across refactors or migrations.

Fast impact analysis (“show rows influenced by refdata.country@v2025-10-01”).

How this fits our architecture

Bi-temporal SCD2 (dual timelines).
We maintain functional and technical timelines so we can replay exact historical states. How we operationalize that — incremental processing and platform-optimized SQL — is covered in ⚙️ Advanced Patterns: Incremental Loads, SCD2, PIT, and Platform-Optimized SQL.

PIT / Exposure views.
We resolve the correct versions via point-in-time logic and present 1:1 joins in views, not as newly persisted history. Surrogate keys are the linchpin; we explain why and how in Adding Surrogate Keys to Dual SCD2 Tables to Enable PIT Tables in a Data Platform.

Persist derived results; join 1:1 in views.
Our integration layer stores derived datasets/entities (the results of filter/union/aggregate/pivot), while 1:1 joins remain in views — the separation of concerns we outline in Modeling Data Transformations in Logical Steps.

Data Breadcrumbs align with all of the above: they don’t replace historization or lineage; they summarize the chosen snapshots and the material steps at the row level.

The Trace Capsule (what we store on every integration table)

We add a single column, trace, to each integration table. It’s a compact STRUCT with four parts:

trace = STRUCT<
src_set ARRAY, – dataset@snapshot tags (functional), e.g. “crm.customers@2025-09-30#2”
ops ARRAY, – ordered logical steps: “select:id,email”,”filter:is_active=1”,”union:crm+erp”
prov_hash STRING, – sha256 of sorted, de-duplicated src_set (deterministic input fingerprint)
build STRUCT

Design principles

• Portable: one column travels through exposure views into consumption.
• Bounded & deterministic: tiny footprint; order-independent hashing for equivalence checks.
• PII-free: metadata only (ids, snapshots, step markers, versions).
• Replayable: run_id (dbt invocation), code_ref (git SHA), version (semver), ts (UTC).

How we populate it (dbt + Databricks, step-by-step)

We keep a tight vocabulary for material steps that change grain or meaning:

• select:<cols>
• filter:<predicate>
• derive:<short expr>
• aggregate:by=<keys> fn=<list>
• union:<label>
• pivot:<desc>
• join:<ref> (mostly in exposure views)

1) Seed from the PIT/exposure choice

Where we select a functional snapshot (reporting date + delivery), we seed:

• src_set = ["system.schema.table@<reporting_date>#<delivery>", …]
• ops = []
• prov_hash = sha256(sorted(src_set))
• build = { run_id, code_ref, version, ts }

2) Extend at each logical step

• Filter → append filter:<predicate>
• Derive → append derive:<expr>
• Aggregate → append aggregate:by=<keys> fn=<list>
• Union → merge src_set from both branches and append union:<label>
• Pivot → append pivot:<desc>

The column remains a single STRUCT; we never explode it.

3) Let it flow through exposure views

Exposure views perform 1:1 joins across integration tables. When combining traces:

• src_set = distinct(sort(left.src_set ∪ right.src_set))
• ops = left.ops + right.ops + ["join:<name>"]
• Recompute prov_hash from the merged src_set.

Concrete example (trimmed for clarity)

– CTE: pick exact functional snapshots we need (via PIT/exposure)
with src as (
select
s.*,
named_struct(
‘src_set’, array(‘crm.customers@2025-09-30#2’),
‘ops’, array(),
‘prov_hash’, sha2(‘crm.customers@2025-09-30#2’, 256),
‘build’, named_struct(‘run_id’,’{{ invocation_id }}’,’code_ref’,’{{ var(“git_sha”,”dev”) }}’,’version’,’{{ var(“app_version”,”0.0.0”) }}’,’ts’,current_timestamp())
) as trace
from {{ ref(‘src_crm_customers_at_snapshot’) }} s
),
sel as (
select customer_id, email,
– select step
named_struct(‘src_set’, trace.src_set,
‘ops’, array_concat(trace.ops, array(‘select:customer_id,email’)),
‘prov_hash’, trace.prov_hash,
‘build’, trace.build) as trace
from src
),
filt as (
select customer_id, email,
– filter step
named_struct(‘src_set’, trace.src_set,
‘ops’, array_concat(trace.ops, array(‘filter:email is not null and is_active=1’)),
‘prov_hash’, trace.prov_hash,
‘build’, trace.build) as trace
from sel
where email is not null and is_active = 1
),
drv as (
select customer_id,
lower(trim(email)) as email_std,
– derive step
named_struct(‘src_set’, trace.src_set,
‘ops’, array_concat(trace.ops, array(‘derive:email_std=lower(trim(email))’)),
‘prov_hash’, trace.prov_hash,
‘build’, trace.build) as trace
from filt
)
select customer_id, email_std, trace from drv

In production we wrap these STRUCT edits in small dbt macros for consistency and readability.

How we use breadcrumbs day-to-day

Impact analysis

“Which rows used _refdata.country@v2025-10-01_?”

select *
from integration.int_customer
where array_contains(trace.src_set, ‘refdata.country@v2025-10-01#1’);

Equivalence after refactor/migration

“Did we use the same upstream snapshots?”

select count(*) as rows_same_inputs
from integration.int_orders
where trace.prov_hash = ‘a7f…’; – expected fingerprint for the set of snapshots

Root-cause analysis

“What steps shaped this row?”

select trace.ops, trace.build
from integration.int_customer
where business_key = ‘…’;

Operating model

Testing

• Assert trace exists and trace.build.run_id is not null for every integration model.
• Optionally, assert array_length(trace.src_set) > 0.

Governance & ACLs

• Treat trace as metadata (no PII).
• Apply same (or stricter) ACLs as the owning table.

Performance

• One STRUCT column compresses well in Delta and flows through views with negligible overhead.

Observability

• Use dbt’s invocation_id as build.run_id.
• Provide git SHA and app version via environment variables for auditability.

Rollout plan (small, safe increments)

1. Pilot on one high-value integration table; seed from the PIT/exposure snapshot we already use and mark each logical step.
2. Publish two saved queries: Provenance search (by src_set) and Equivalence check (by prov_hash).
3. Stamp the pattern across remaining integration models via macros.
4. Merge traces in exposure views that join 1:1; let the capsule flow to consumption.

Afterword — How our prov_hash works (and why)

• What: A SHA-256 hash of the sorted, de-duplicated src_set (the list of dataset@snapshot tags).
• Why: Proves two builds used the same upstream snapshots, regardless of order or union branch.
• Databricks SQL / dbt:
• sha2(concat_ws('|', array_sort(array_distinct(trace.src_set))), 256)
• Changes when: any snapshot or dataset in src_set changes (added/removed/replaced).
• Doesn’t change when: we reorder sources or only tweak downstream filters/derives (those are tracked in ops, not in prov_hash).
• Collision risk: Practically negligible with SHA-256 for our short strings. If desired, store the canonical joined string next to the hash.

Concepts at a glance (how this fits together)

Bi-temporal SCD2 (dual timelines).
We keep functional time (reporting date + delivery) and technical time (loads/corrections), enabling exact reproduction and late-arrival handling.

PIT / Exposure views.
PIT resolves the correct SCD2 version(s) for a business cut. Exposure views assemble 1:1 data without persisting new history. With breadcrumbs, each exposure row carries a trace capsule end-to-end.

Materialize (derived results) vs 1:1 views.
We persist only transformations that change grain/meaning (filters, unions, conforming, aggregates, pivots).
We do not persist historized sources in integration.
We join 1:1 in views. The trace lives on the materialized result and flows through the views.

Surrogate keys in SCD2.
Stable, meaningless identifiers for SCD2 versions. They enable fast PIT access and consistent joins. Pair them with breadcrumbs: keys stitch facts to dimensions; the trace explains where the version came from.

Quick reference (copy/paste)

Stable **prov_hash**

select sha2(concat_ws(‘|’, array_sort(array_distinct(trace.src_set))), 256) as prov_hash
from integration.int_customer;

Find rows influenced by a snapshot

select *
from integration.int_customer
where array_contains(trace.src_set, ‘refdata.country@v2025-10-01#1’);

Compare inputs across two runs (equivalence)

select trace.prov_hash, count(*) as rows
from integration.int_orders
where trace.build.run_id in (‘RUN_A’,’RUN_B’)
group by trace.prov_hash;

📚 Further reading

• Modeling Data Transformations in Logical Steps
• Adding Surrogate Keys to Dual SCD2 Tables to Enable PIT Tables in a Data Platform
• ⚙️ Advanced Patterns: Incremental Loads, SCD2, PIT, and Platform-Optimized SQL

Bottom line: Our dual SCD2 + PIT/exposure design already gives us version-correct data. Data Breadcrumbs add the missing row-level narrative — a tiny, deterministic capsule that makes provenance obvious, equivalence verifiable, and impact analysis fast — without changing how we store data or moving away from our “persist derived, join in views” principle.