DEV Community: Steven Blough

Row and Field-Level Data Provenance: Why It's Worth the Pain (and Where the Pain Is)

Steven Blough — Fri, 31 Jul 2026 15:37:17 +0000

Most "data lineage" you've seen answers a schema question: table B comes from table A, or column B.total comes from columns A.price and A.qty. That's genuinely useful, and tools like OpenLineage do it well. But notice what it doesn't tell you: it says which columns can influence an output. It never says which values actually did.

That gap is the whole subject of this post. I built a small, self-contained reference pipeline that captures provenance at the row and field level — "the value in this destination row, this field, was computed from these specific source (row, field) pairs" — and I want to walk through two things: why you'd ever want provenance at that granularity, and why it's genuinely hard once you commit to it.

Repo (dbt-core + DuckDB, no server, no cloud, runs on a clean checkout): https://github.com/stevenblough/row-level-prov

The one distinction everything follows from

Here's the sentence the entire project turns on:

Column-level lineage is a schema-sized, static fact you can derive from code. Value-level provenance is a data-sized, dynamic fact you must capture at execution.

Put it in complexity terms and the consequences become obvious:

Column lineage is O(schema). It scales with how many columns you have. You can compute it by parsing SQL, offline, without ever looking at a single row.
Value provenance is O(rows × fan-in). It scales with your data volume times how many source values feed each output value. It does not exist anywhere until the query runs, and it can only be captured there, piggybacked on the query that actually produced the values.

You cannot "reconstruct" value provenance later by re-querying the sources — the moment the source changes, you'd reconstruct a different answer than what really happened. That single exponent change (schema → rows × fan-in) is why value-level provenance has an entire class of problems that column lineage never faces.

Why bother? The reasons for this level of granularity

Granularity is expensive, so it has to earn its place. Here's where row/field provenance pays for itself:

1. Answering "why is this number wrong?" in one hop.
A daily site summary reads 4.2 when a domain expert insists it should be 4.1. With table or column lineage you learn the summary came from the readings table — now go find the needle yourself. With value provenance you get the exact set of contributing readings, each addressable, each with its own status. Debugging goes from an investigation to a lookup.

2. Corrections and their blast radius.
A sensor re-submits a reading with a corrected value. Which downstream aggregates are now stale? Column lineage says "anything computed from readings" — technically true, uselessly broad. Value provenance says these three daily summaries and no others, because it knows which specific rows fed which specific outputs.

3. Compliance and the right to explanation.
"Show me every source value that fed this figure in the regulatory report" is a per-value question. So is GDPR-style "where did this person's data flow?" These are not answerable at column granularity.

4. Trust as a first-class, visible property.
This one is underrated. The most dangerous failure in a provenance system isn't a missing trace — it's a confident, plausible, wrong trace. If a trace can silently be wrong, it's worse than no trace at all, because people act on it. So every node and edge carries a verification_status, and any trace that crosses an unverified or flagged edge says so rather than returning a silently confident answer. That converts undetected-wrongness into visible-uncertainty — which is the single most valuable property if anyone downstream is making decisions on your lineage.

The challenges (this is the interesting part)

Committing to value-level provenance signs you up for a specific set of hard problems. Here are the ones that shaped the build.

1. Storage blowup — the defining scaling problem

A daily summary over ~1,440 readings, times millions of summaries, is billions of edges. Do the naive thing — one edge per contributor — and your provenance store dwarfs the data it describes by orders of magnitude. This is the problem column-level tools never hit, and it's a direct consequence of the rows × fan-in exponent.

The insight that rescues it: contributing sets are usually not arbitrary. They're the result of a predicate — WHERE site_id = X AND reading_date = Y — over a partition. So you store the predicate, not the enumeration: O(1) instead of O(n). In the reference build the aggregate's contributors are predicate-encoded — one edge carrying the equality and a checksum — and the individual member rows are only materialized on demand when someone actually runs a trace query.

Concretely, instead of ~1,440 edges the aggregate emits one predicate edge per output field (distilled from the mart model — the real thing is a LIST(STRUCT(...)) dbt shreds into the PROV tables):

-- write side: one O(1) reference instead of O(n) enumeration
{
  'target_field':   'avg_temp_c',
  'source_dataset': 'int_calibrated_readings',
  'set_encoding':   'predicate',
  'set_definition': {
      'source_dataset': 'int_calibrated_readings',
      'predicate': { 'site_id':      site_id,
                     'reading_date': reading_date,
                     'as_of_txn':    <this run's transaction instant> }
  },
  'set_cardinality': card,   -- how many rows it stood for
  'set_checksum':    ck      -- md5 over the ACTUAL members, at execution:
}                            --   md5(string_agg(row_id, ',' order by row_id))

That set_checksum is the crux. But — and this is the subtle part — a predicate is only sound if the data it ranges over is immutable or versioned. If the underlying partition changes after you recorded the predicate, re-evaluating it at trace time gives a different set than was actually used. Compression stopped being a mere optimization and became a correctness-critical component.

So expansion is as-of aware: it pins the predicate to the transaction-time snapshot it was captured under, and re-checksums what it reconstructs (distilled from prov/_common.py):

def expand_predicate(con, set_definition):
    spec      = json.loads(set_definition)
    dataset   = spec["source_dataset"]
    predicate = dict(spec["predicate"])
    as_of     = predicate.pop("as_of_txn", None)     # the captured snapshot

    where = " AND ".join(f'"{k}" = ?' for k in predicate)
    sql   = f"SELECT row_id FROM {dataset} r"
    if as_of is not None:
        # keep only rows whose bitemporal belief interval contains the coordinate,
        # so a stored predicate reproduces its EXACT captured membership even
        # after the source has grown.
        sql += (" JOIN prov_bitemporal b ON b.entity_id = r.row_id"
                " AND b.txn_from <= ? AND (b.txn_to IS NULL OR b.txn_to > ?)")
    members = [r[0] for r in con.execute(sql + f" WHERE {where}", ...).fetchall()]

    checksum = md5(",".join(sorted(members)))         # SAME expression as capture
    return sorted(members), len(members), checksum

At trace time you compare that recomputed checksum against the stored set_checksum. A mismatch means the set drifted — so you get a detected error, not a silently wrong trace. Two invariants make this honest: the checksum is computed from execution-time members (never from the predicate's own re-expansion — that would let a wrong predicate confirm itself), and drift is detected rather than assumed away.

2. Capture correctness — and "who verifies the verifier?"

There are two completely different ways capture can fail:

Total silence — no event emitted at all. This is an absence, and you catch it by measuring completeness against an expectation (a root registry / orphan sweep).
Partial or wrong capture — an event is emitted, but the mapping is wrong. This is a present-but-false record, and it is the more dangerous class, because it produces confident false traces.

A system that only checks "did an event show up?" catches silence and is blind to wrong mappings. So you need at least one detector rooted in an independent oracle — ground truth the capture layer never touches. In the build, one detector reconciles event counts against raw row counts (pure cardinality, shares no code with the capture logic), and another re-derives each model's row-correlation rule from the compiled SQL and diffs it against what was emitted. That second one catches the nastiest fault: an edge re-pointed at the wrong source row while keeping the count correct — invisible to every count-based check.

The honest residual: a bug that is common-mode to both the capture layer and its oracle is undetectable. That's the argument for anchoring at least one detector in something as dumb and independent as row counts. And the capture layer certifies nothing about itself — capture can only ever write unverified; it is structurally incapable of writing verified. Only the separate detector pass promotes. A capture path that certifies its own output is "a report written by the suspect."

3. Identity — the discipline no schema can enforce

Every edge needs a stable anchor on both ends, and it cannot be the business primary key (PKs are reused, composite, sometimes absent, and — critically — the same PK is legitimately re-submitted with new content during a correction, which is exactly when you need to tell versions apart). So identity is a surrogate row_id minted at ingestion, independent of content and business key, and preserved unmodified across every storage boundary.

That preservation is a discipline, not something the schema can enforce. Any transform that joins, unions, or repartitions and quietly re-mints the id silently severs the chain. The only defense is an id-preservation test that catches it after the fact. And when a transform genuinely does merge many rows into one (an N:1 aggregate), it must mint a new id — so "preserve the id" and "mint a new id" both have to be deliberate, reviewable choices, never accidents.

4. Temporal correctness — corrections, late arrivals, bitemporality

Real data isn't loaded once. Corrections arrive (same key, new value, newer timestamp). Late arrivals show up (a reading older than what you've already processed). Duplicates get redelivered. Each needs distinct handling — a late arrival is tagged, not dropped and not overwritten; a correction creates a new version that supersedes the old one — and the hard part is propagation: a corrected reading has to trigger re-aggregation of exactly the summaries it touched, and the new summary has to supersede the old. Under-scope that and you get either over-recomputation (correct but expensive) or missed recomputation (cheap but wrong).

Underneath, corrections and late arrivals are both bitemporal: there are two time axes — when the event happened (valid time) and when the system learned it (transaction time). A famous edge case makes the axes unavoidable: a zero-delta correction, where a value is re-submitted unchanged. Membership doesn't move, the rounded average doesn't move — every value-based check sees nothing. But it's a distinct transaction-time version, so with a proper bitemporal model it's still distinguishable. The reference build carries valid and transaction time as first-class coordinates so you can ask "what did we believe as-of last Tuesday?" and get the answer that was true then, not the answer that's true now.

5. Immutability vs. the right to erasure

The provenance store is append-only and immutable — that's what makes it trustworthy and what makes predicate encoding sound. But immutability collides head-on with GDPR Article 17 erasure. If a captured value is personal data, "delete this person's data" and "never mutate the log" are in direct conflict.

The cleanest reconciliation is reference, don't copy: the graph stores row/field ids and addresses, not the values themselves. Delete the underlying value and the edges survive but dangle — the trace structure is intact, the PII is gone. (Crypto-shredding and tombstoning are the other options, each with its own regulatory trade-offs.) This isn't a storage trick; it's a governance decision about what the graph is even allowed to contain, and it's a strong argument for an address-only graph.

What the reference build actually proves

I want to be precise about scope, because "provenance" invites scope creep. The build is deliberately small — dbt-core + DuckDB + one SQL parser, no Docker, no server, no network at run time — and it exists to demonstrate exactly one thing:

Value-level provenance can be captured faithfully at execution time, and gaps in capture are detectable rather than silent.

Concretely, it runs over two genuine dbt loads (so incremental propagation and corrections are exercised for real, not sidestepped by a single rebuild), captures provenance for ~20k readings across a 1:1 → M:N join → N:1 aggregate DAG, encodes the high-fan-in aggregate as predicates, and runs five independent detectors — completeness, cardinality reconciliation, temporal supersession, a compiled-SQL diff audit, and a bitemporal soundness check. There's a fault-injection suite that deliberately breaks capture in seven ways and asserts the right detector catches each one (and, honestly, asserts which faults the count-based detectors stay blind to — the blind spots are real and named, not hidden).

The W3C PROV data model (Entity / Activity / Agent) is the backbone, which keeps "the transformation run" cleanly separate from "its output" — a distinction that sounds pedantic until you try to model supersession without it and everything tangles.

If any of this is relevant to your own pipelines — or you just want to see how far you can push value-level lineage in a few hundred lines of dbt and Python — the whole thing is here, and make demo runs the full chain green on a clean checkout:

👉 https://github.com/stevenblough/row-level-prov

The two design docs in the repo go much deeper than this post: one is the concrete build spec, the other maps the full problem space — including the parts deliberately left for later (nested/array identity, cross-engine capture, cold archival). Provenance is a rabbit hole; this is a map of it with one vertical slice actually dug.

Agile or Winging It

Steven Blough — Wed, 25 Mar 2026 09:17:43 +0000

Hey Agile Folks, We Need to Talk About Planning

I've been in and around software teams long enough to watch Agile go from a genuinely good idea to something that gets used as a hall pass for not thinking things through. And I think it's worth having an honest conversation about it, because I've seen it from both sides of the fence.

The Problem Nobody Wants to Say Out Loud

Here's what I keep running into. A team says they're "doing Agile," and what they really mean is they skipped the part where you sit down and figure out what you're actually building and why. No requirements. No discovery. No real conversation with the people who'll use the thing. Just, "Let's start sprinting and we'll figure it out."

A 2024 study by Engprax and J.L. Partners surveyed 600 software engineers in the US and UK and found that projects with documented requirements before development began were 50% more likely to succeed. Projects with clear requirements were 97% more likely to succeed. That's not a rounding error. That's a wake-up call.

The Agile Manifesto says to value "responding to change over following a plan." But folks, read that again. It says over, not instead of. You still need a plan. You just hold it loosely. Somewhere along the way, a whole lot of teams heard "don't plan" and ran with it, and honestly, it shows.

I've watched teams burn months of budget building the wrong thing because nobody stopped to ask a few basic questions up front. As one practitioner put it, if you skip upfront planning, you might not learn enough about what you're taking on until you're mid-stream and expectations are already set. By then you're in trouble and everybody knows it.

But Hold On, It's Not That Simple

Now, before the Agile coaches come for me, let me be fair. There's good reason the industry moved this direction.

The Standish Group has studied over 50,000 IT projects across 25 years. Their 2020 CHAOS report found that Agile projects are roughly three times more likely to succeed than traditional Waterfall projects, and Waterfall projects are twice as likely to fail outright. That's a mountain of data, and you can't just wave it away.

A meta-analysis of 25 peer-reviewed studies found that iterative approaches deliver 25 to 28 percent faster time-to-market than linear processes. The key insight was that iterative methods don't skip planning. They distribute it throughout the work instead of front-loading it all into a phase that's outdated by the time you finish writing it up.

And that's the real point. The original Agile thinkers were obsessed with design quality. Robert Martin's foundational Agile book was mostly about SOLID principles, design patterns, and test-driven development. Martin Fowler is synonymous with thoughtful software architecture. These folks weren't saying "don't think." They were saying "don't pretend you can think of everything before you start."

Where I Come Down

The research and my own experience both point to the same place. You need "just enough" upfront work to establish a shared understanding of what you're building, who it's for, and what the big risks are. Domain modeling, architecture decisions, basic user research. Then you iterate from there.

The problem isn't Agile. The problem is lazy Agile. It's using the framework as cover for skipping the hard work of understanding your problem space. "We don't need a test team because we're Agile" isn't a principle. It's a budget cut wearing a hoodie.

Good Agile teams plan. They just plan differently. They plan enough to set a direction, then they learn and adjust as they go. Bad Agile teams don't plan at all and call it a methodology.

If your team can't answer "what problem are we solving and for whom" before the first sprint starts, you're not being Agile. You're just winging it.

Note: The Engprax study was conducted to promote a competing methodology and should be read with that context in mind.

Sources:

Engprax/J.L. Partners survey (2024)
Standish Group CHAOS Reports (1994-2020)
meta-analysis of 25 peer-reviewed studies on iterative vs. linear change management (Rietze et al., 2022)
Digital.ai State of Agile Reports

Build with Intention: The Case for Synthetic Data

Steven Blough — Thu, 19 Mar 2026 11:55:39 +0000

Every development team eventually confronts the same problem: the data needed to build and test a system is either unavailable, sensitive, incomplete, or all three. The instinctive solution is to copy some production data, sanitize the obvious fields, and move on. It is understandable. It is also, in most cases, the wrong answer. A more deliberate approach, one grounded in understanding the business domain and constructing data from first principles, produces better software and fewer surprises in production.

This is the argument for synthetic data as a primary development and testing strategy, with replicated production data reserved for the specific, bounded purpose it actually serves: final verification that nothing was missed.

The Foundation: Understanding Precedes Generation

There is a persistent myth that synthetic data is technically difficult or that it requires specialized AI tooling before teams can get started. This misunderstands what synthetic data actually demands. The hard part is not the generation; it is the understanding that must precede it.

If an engineering team can articulate, precisely, what their system does with data: what a valid order looks like, what distinguishes an active customer from a lapsed one, what the lifecycle of a financial transaction is from initiation to settlement, then creating synthetic records that represent those concepts is straightforward. The domain knowledge is the hard work. The data generation follows naturally from it.

"Understanding the source data, its characteristics, distributions, and dependencies, is the essential prerequisite to generating synthetic data that maintains the statistical properties necessary for valid testing."

— Tonic.ai, Guide to Synthetic Test Data Generation

This observation has a useful corollary: if a team struggles to define what valid synthetic data should look like, that struggle is a signal. It reveals gaps in the team's understanding of the domain, gaps that will produce bugs regardless of the testing approach used. Forcing that conversation early, during data design, is itself a quality-improvement activity.

Rule-based synthesis (generating records according to explicit business rules, ranges, and relationships) is the natural starting point. For most transactional business domains, it is also sufficient. More sophisticated statistical or ML-based generation adds value later, when fidelity to complex distributions matters. But the entry point is simply: know your domain, codify its rules, generate records that obey them.

Cause and Effect: Designing Tests That Actually Test

The most powerful characteristic of synthetic data is one that is rarely discussed: it enables genuine experimental design. When you construct data rather than inherit it, you control the inputs precisely. That control is the precondition for meaningful testing.

Consider what it means to test a refund processing workflow. With production-derived data, you find refunds that happened to exist in the snapshot. You test against the outcomes that were already embedded in that data. With synthetic data, you construct the exact scenario you intend to test: a refund requested after the return window closes, a partial refund on an order with a promotional discount, a refund on a subscription that has already renewed. Each scenario has a known input and an expected output. The test is a controlled experiment, not an archaeological dig.

Scenario Coverage

This matters enormously for edge cases and failure paths. Production data snapshots reflect what has already happened: the distribution of events that occurred in the past under real conditions. They systematically under-represent rare events: the unusual transaction pattern, the concurrent write conflict, the workflow state that only arises when three conditions coincide. These are precisely the cases that cause production incidents.

Synthetic data inverts this. You can generate exactly as many rare-event scenarios as you need, not because they happen frequently in production, but because you have decided they are important to test. The frequency of a scenario in a test suite should reflect its risk and complexity, not its historical prevalence in production data.

The Problem with Production Snapshots

A replicated subset of production data feels safe because it is real. That intuition is worth examining. Real data carries real distributions, real constraints, and real history. But those properties are exactly what makes it a poor primary testing tool, not a good one.

What production snapshots miss

The most significant limitation is coverage of critical business events. A snapshot taken on any given day captures a cross-section of normal operations. Month-end close behavior, fiscal year rollovers, promotional surge processing, first-time-purchaser workflows, and account reactivation paths: these events happen infrequently and are statistically unlikely to be well-represented in any particular snapshot. Yet these are precisely the flows that accumulate the most technical debt and surface the most defects.

A related problem is that production data encodes past behavior, not future requirements. When a business adds a new product category, changes its pricing model, or enters a new market, the production snapshot contains no records of these new patterns. The team is forced to either hand-craft a handful of test records (ad hoc and not versioned) or test with data that does not reflect the scenarios the new code must handle.

Finally, production data is bounded by its own scale. A subset that represents normal daily volume tells you little about behavior under peak load, bulk import operations, or the long-tail of a large customer's data set. The snapshot is, by definition, a sample; and samples are not the right tool for performance and scalability testing.

"Replicated production data systematically under-represents the rare, high-risk events most likely to cause production incidents: the precise scenarios a test suite most needs to cover."

— Engineering Practice Observation

Scalability: Data on Demand

One of synthetic data's practical advantages is deceptively simple: you can have as much of it as you need, in exactly the shape you need, when you need it. This sounds obvious, but its implications run deep through a development workflow.

Performance testing requires volumes that no sanitized production subset can provide. Load simulations, database query optimization, and index strategy validation all depend on data at production scale or beyond. Synthetic data generators can produce millions of statistically coherent records in minutes. The same specification that generates a thousand records for a developer's local environment generates ten million for a stress test, with no new configuration and no waiting for a DBA to provision a larger snapshot.

This scalability also applies along a different axis: time. Development teams can generate fresh data sets for every test run, eliminating the accumulated drift that affects shared test databases. Tests that depend on specific data states do not corrupt the environment for subsequent runs. Every pipeline starts clean. The feedback loop tightens because the test environment is deterministic.

For teams working in CI/CD pipelines, this is not a convenience; it is an architectural requirement. A test environment that depends on a pre-provisioned, manually refreshed copy of production data is a bottleneck with a scheduled maintenance window. A test environment backed by a data generation specification is a pipeline input that scales horizontally.

The Recommended Pipeline: Synthetic First, Production Last

This is not an argument for abandoning production data in testing entirely. It is an argument for placing it correctly in the pipeline: at the end, as final verification, rather than at the beginning, as the primary test environment.

Phase 1: Domain Modeling & Spec
Phase 2: Synthetic Data Generation
Phase 3: Scenario & Edge Case Testing
Phase 4: Scale & Performance Runs
Phase 5: Final Verification with Masked Production Data

The recommended workflow proceeds as follows. Development and unit testing use fully synthetic data, generated from domain specifications. Integration and system testing use synthetic data sized and shaped to the scenario requirements. Performance and load testing use synthetic data at production scale or beyond. Only the final pre-release verification stage (the stage intended to confirm that the real-world data distribution does not contain surprises the synthetic specification missed) uses a masked or anonymized production subset.

This final verification stage is legitimate and important. No domain specification is perfect. Production data will occasionally reveal a pattern or relationship that the synthetic specification did not capture: an unusual character encoding in a legacy customer record, a historical data migration artifact, an edge case in a third-party data feed. The production subset exists to catch those cases, not to serve as the primary testing substrate for all the cases that were already well-understood.

Compliance as a benefit, not an afterthought

This pipeline structure also resolves the compliance problem that haunts every team that relies on production data for development. Synthetic data does not contain personally identifiable information, protected health records, financial account details, or any other regulated data class, because it was never derived from real individuals. Development environments built on synthetic data satisfy GDPR, HIPAA, PCI-DSS, and similar frameworks structurally, without depending on masking pipelines that require ongoing maintenance and audit. The compliance posture is embedded in the architecture.

Tooling: A Mature Ecosystem

Teams considering this approach will find the tooling landscape well-developed. The choice of tool depends on data type, scale requirements, and team preferences:

Tonic.ai — Structured and unstructured synthesis; strong referential integrity preservation for relational databases
Gretel.ai — Developer-first APIs for generation, transformation, and privacy at scale
MOSTLY AI — Privacy-preserving synthesis with fairness controls; strong for regulated data sharing
Synthetic Data Vault (SDV) — Open-source Python ecosystem for tabular, relational, and time-series data; widely used in enterprise and academia
K2View — Enterprise-grade entity-based synthesis combined with test data management and masking
Mockaroo — Lightweight, schema-driven generation for smaller-scale development use cases; accessible free tier

For teams with primarily tabular, rule-governed data (the common case in transactional business systems), open-source options like SDV or direct programmatic generation using libraries like Python's Faker and factory_boy are often sufficient to start. The investment in domain specification pays dividends regardless of the generation mechanism used.

Comparison: Approaches at a Glance

Criterion	Synthetic Data	Production Subset	Hand-crafted
Privacy & compliance	✓ Structural	△ Requires masking	✓ If no real data
Edge case coverage	✓ By design	✗ Historically rare	△ Labor-intensive
Scalability	✓ On demand	△ Snapshot-bounded	✗ Not practical
Controlled cause & effect	✓ Precise	✗ Inherited state	✓ Precise
Real-world distribution	△ Approximated	✓ Actual	✗ Minimal
CI/CD integration	✓ Native	✗ Refresh bottleneck	△ Fragile
New feature coverage	✓ Specifiable	✗ Not yet in data	△ Manual effort

A Closing Argument

The teams that do this well share a common trait: they have invested in understanding their domain deeply enough to specify it. That investment is not a cost unique to synthetic data; it is a cost that any team building reliable software must eventually pay. The difference is whether that investment happens at the beginning of development, where it shapes the design, or at the end, after a production incident forces a reckoning.

Synthetic data does not replace production data. It replaces the false confidence that production snapshots provide: the feeling that testing against "real" data means testing against reality. A snapshot of the past, stripped of its most sensitive content and frozen at a point in time, is not reality. It is an artifact. The team that understands its domain well enough to construct the right data from scratch understands its system better than the team that inherited a database dump.

Build with intention. Verify against reality. In that order.

Sources: Gartner Market Guide for Data Masking · MIT Sloan Management Review · Perforce 2025 State of Synthetic Data Report · Tonic.ai · Accutive Security · Netguru · Enov8