Data Engineering Interview: Design a Data Lineage System

Prompt and Applicable Context

Your company has 30,000 datasets and 200,000 pipeline runs per day across Airflow, Spark, dbt, and Kafka. Teams need to answer three questions: where a dataset or field came from, what will be affected by a proposed change, and which outputs were produced by a particular execution. Design a lineage system that captures table- and column-level dependencies, supports upstream and downstream traversal, reconstructs lineage at a past time, handles retries and failed or partial runs, enforces metadata access, and exposes whether the graph is complete enough to trust.

For the interview, assume event ingestion peaks at 500 events per second, a three-hop traversal should usually finish within 2 seconds, and detailed execution history is retained for 1 year. These are scenario inputs, not industry benchmarks. The design should explain how the limits would be measured and revised after observing real traffic.

Current 2026 data-engineering interview material directly asks candidates how they design for data lineage and calls out table, column, and job levels, metadata capture, naming, and change impact. OpenLineage 1.50.0 provides a useful factual baseline: Job, Run, and Dataset are the core entities; namespaces and names identify Jobs and Datasets; a Run uses a UUID; run events have defined lifecycle states; and facets extend the model with schema and column dependencies. The category is data because the central abilities are metadata modeling, pipeline semantics, impact analysis, and data governance.

What the Interviewer Evaluates

The first signal is whether the candidate starts with identity and semantics. A graph is unusable when the same table appears as orders, prod.orders, and a warehouse URL, or when a retry is mistaken for a new job. Stable resource keys, environment boundaries, job definitions, execution IDs, field paths, and version semantics must be established before choosing a graph database.

The second signal is whether lineage reflects execution reality. Declared lineage from source code helps before release; observed lineage from an actual run proves what was read and written. A failed run may have written temporary or partial outputs, while a successful execution status still does not prove data correctness. A strong design keeps event evidence, run state, declared edges, observed edges, and publication state distinct.

The third signal is system design depth. The candidate should cover producer integrations, a durable and idempotent ingestion path, immutable raw events, normalization, temporal graph materialization, traversal indexes, freshness and coverage metrics, backfills, and access control. Saying “put it in a graph database” skips the hardest problems.

Finally, the interviewer wants calibrated trust. Missing instrumentation must remain visible. A neat graph assembled from 60% of critical jobs is dangerous if the UI presents it as complete. Strong answers expose provenance, observation time, confidence, coverage, and gaps so users can decide whether an impact query is sufficient for a deployment decision.

Questions to Clarify Before Answering

• Which decisions must lineage support? Incident diagnosis, pre-deployment impact analysis, governance discovery, PII

propagation, and audit evidence have different freshness, history, and correctness requirements.

• What counts as a dataset? Tables, views, files, object prefixes, Kafka topics, materialized views, dashboards, and

machine-learning features need explicit granularity. Treating every file as a node may create an unusable graph.

• Do we need declared, observed, or both kinds of lineage? Declared lineage can show future changes before execution;

observed lineage can tie inputs and outputs to a concrete Run. The UI must not silently merge their meanings.

• What is the required field-level precision? Direct value derivation differs from indirect influence through a join,

filter, grouping, sort, window, or condition. Some engines expose a logical plan; others only expose table lineage.

• How are datasets and jobs identified across environments? Define namespaces, canonical names, aliases, case rules,

renames, and ownership. A display label is not a durable primary key.

• What should happen after a failed or partial run? Clarify whether partially written outputs are published, isolated,

or rolled back and whether impact analysis should include them as evidence, current truth, or both.

• How long must point-in-time history remain queryable? One year of run events does not necessarily require one year of

every expanded column edge in the low-latency serving layer.

• Which metadata is sensitive? SQL text, field names, ownership, PII tags, and dataset existence can reveal protected

information. Traversal results need the same authorization discipline as the catalog.

• What are the scale and service objectives? Confirm event rate, graph size, traversal depth, latency percentile,

recovery-point objective, and acceptable delay between a run and visible lineage.

30-Second Answer Framework

“I would model canonical Job, Run, Dataset, and Field identities, then separate declared from observed and table from column lineage. Producers emit versioned events through an authenticated, idempotent gateway backed by a durable log. Consumers retain raw evidence and build temporal upstream and downstream indexes; failed outputs remain diagnostic until publication is confirmed. Traversals are bounded by depth, time, and permission and expose provenance and gaps. I would measure lag, expected-job coverage, terminal-event completeness, unresolved identities, stale edges, and sampled path accuracy, launching table lineage for critical pipelines before adding reliable column extraction.”

Step-by-Step Deep Answer

Step 1: Define the truth model before the storage engine.

Use four kinds of records:

The namespace should come from the data source for a Dataset and the scheduler or processing system for a Job. Keep aliases in a separate mapping with validity intervals. Renaming analytics.orders to analytics.sales_orders should not silently create or merge identity based on string similarity; it needs an explicit rename or alias event.

Model declared lineage from compiled SQL, dbt manifests, or configuration separately from observed lineage emitted by a Run. Model table edges separately from field edges. A field edge records output field, input field, transformation kind, and whether the dependency is direct value derivation or indirect influence. OpenLineage's column model distinguishes direct identity, transformation, and aggregation from indirect join, group, filter, sort, window, and condition effects. That distinction matters when deciding whether changing a field's values, type, or availability affects an output.

Every edge should include validFrom, optional validTo, observedAt, producer, source event, Job version, Run ID, run status, lineage kind, and confidence or derivation method. These attributes turn an unqualified arrow into evidence that can answer “as of when?” and “according to what?”

Step 2: Capture lineage as close to execution as possible.

Use native or maintained integrations where they exist: orchestration listeners for task lifecycle, Spark logical-plan instrumentation, dbt artifacts and run results, and connectors or query history for warehouses and streaming systems. Prefer a parsed or engine-produced logical plan over regex against SQL. Dynamic SQL, macros, stored procedures, temporary objects, user-defined functions, and runtime branch selection make string parsing incomplete.

Define a versioned ingestion envelope around the lineage payload:

{
  "eventId": "producer-unique-id",
  "producer": "spark-prod-eu",
  "schemaVersion": "1.0",
  "emittedAt": "2026-07-19T00:00:00Z",
  "job": { "namespace": "spark-prod", "name": "daily_orders" },
  "runId": "53ee3770-86fa-4cb9-8c31-a09072dd88f7",
  "state": "COMPLETE",
  "inputs": [{ "namespace": "warehouse-prod", "name": "raw.orders" }],
  "outputs": [{ "namespace": "warehouse-prod", "name": "mart.daily_orders" }]
}

eventId is a requirement of this platform envelope for idempotency; do not claim it is a mandatory field in every external lineage standard. Producers retry delivery with the same ID. The gateway authenticates the producer, checks schema compatibility and size limits, attaches receipt time, and writes the event to a partitioned durable log before acknowledging it. Invalid events go to a quarantine stream with a reason, producer, and safe payload reference; they do not disappear into logs.

Partitioning by Run ID preserves a Run's local order while distributing unrelated runs. Event time can be late or skewed, so the consumer stores both emitted and received time and applies lifecycle rules. OpenLineage defines START, RUNNING, COMPLETE, ABORT, FAIL, and OTHER; terminal events must not be undone by a late START. Keep the immutable event even when it no longer changes materialized current state.

Step 3: Normalize without destroying provenance.

A normalizer converts each integration's payload into canonical identities and edge semantics. It resolves registered aliases, case conventions, environment, temporary datasets, and nested field paths. Unknown identities enter an unresolved queue rather than being guessed. The raw event, normalized record, resolver version, and any warnings stay linked so a bad mapping can be corrected and replayed.

Schema changes create versioned field definitions. Dropping and later recreating customer_id does not imply continuous field history. A schema hash or catalog version plus validity interval lets point-in-time queries select the right field. For streaming pipelines, record the topic and transformation Job at stable granularity; retain partitions and offsets as Run evidence rather than exploding every partition-offset pair into a permanent graph node.

Treat execution state carefully. COMPLETE means the Job execution concluded; it does not certify the output's business quality. A FAIL or ABORT event may still report inputs and attempted outputs. Store those observed edges for diagnosis, but only materialize current published lineage when the output publication policy is satisfied. That policy may require an atomic commit marker or a separate quality gate. Label it explicitly.

Step 4: Build an event-sourced temporal graph with fit-for-purpose indexes.

The durable log and immutable object-store archive are the recovery source. Consumers create three projections:

1. A metadata store for canonical Jobs, Datasets, Fields, schemas, aliases, owners, and access policies.
2. A temporal edge store for declared and observed lineage with validity intervals and execution provenance.
3. A Run store for lifecycle events, input/output snapshots, status, and diagnostic details.

A reproducible first-pass capacity estimate prevents the daily Run count from being confused with event throughput. At a minimum, one START and one terminal event for each of 200,000 Runs produce 400,000 events per day, about 4.6 per second on average. If a typical Run emits one START, two RUNNING updates, and one terminal event, that becomes 800,000 per day, about 9.3 per second; the 500-per-second input is therefore a burst target, not an average. With an assumed mean raw payload of 20 KB, 800,000 events require about 16 GB per day or 5.8 TB per year before compression and replication. Payload size and events per Run must be measured because column facets can change this estimate substantially.

For low-latency impact queries, maintain both downstream and upstream adjacency indexes keyed by canonical node ID and time bucket or active version. A breadth-first traversal has explicit maximum depth, node count, edge type, environment, and time boundary. The service returns partial-result indicators if a limit is reached. A graph database can implement this, but it is not mandatory; relational edge tables with suitable indexes or a key-value adjacency service may be simpler at this scale. Benchmark actual fanout and point-in-time predicates before choosing.

Column lineage can be much larger than table lineage. Store table edges in the hot projection, keep frequently queried field adjacency hot, and place older or low-use detailed edges in a compressed historical store. Do not precompute the full transitive closure: dense graphs make it expensive to update and authorize. Cache bounded query results by node, direction, depth, time, edge-kind filters, and authorization scope; invalidate them when relevant edge versions change.

Step 5: Make the query contract explicit.

The API should support:

• upstream or downstream traversal for a Dataset or Field, bounded by depth and point in time;
• impact analysis for a proposed schema or field change, with direct and indirect dependencies distinguished;
• Run lookup showing the exact inputs, outputs, Job version, lifecycle, and publication state;
• provenance on every returned edge, including declared versus observed and last observation time;
• gap markers for uninstrumented Jobs, unresolved identities, stale producers, and truncated traversal.

Authorization cannot be applied only after traversal. A hidden Dataset's name or existence may itself be sensitive. Resolve the caller's policy during expansion, omit or replace protected nodes according to governance rules, prevent degree counts from leaking hidden neighbors, and audit sensitive traversals. Cache keys include authorization scope so one user's graph is never served to another.

For the 2-second three-hop objective, measure P50, P95, and P99 by direction, depth, fanout, temporal filter, and column versus table query. A response can return a continuation token or an explicit truncation when the bounded node budget is exceeded. Quietly returning an incomplete graph is unacceptable.

Step 6: Design replay, backfill, and disaster recovery.

Consumers checkpoint durable-log offsets. Because processing is at least once, projection writes use eventId and projection version for idempotency. A normalization bug is repaired by deploying a new resolver version, rebuilding into a shadow projection from raw events, comparing counts and sampled paths, and switching readers after validation. Do not overwrite the only serving graph during a full replay.

Retain raw events for the required 1 year in immutable storage, with encryption and lifecycle policy. Snapshot canonical metadata and edge projections to reduce recovery time, but prove that snapshots plus later events reproduce the same result. Define recovery objectives, test loss of an ingestion region, and verify that producer retries do not create extra edges.

Point-in-time lineage uses edge validity and observation time, not the current graph plus a timestamp label. A query for last month must resolve the identities, schema versions, and authorized edges valid then. If a source never emitted history, return that limitation instead of inventing it.

Step 7: Measure trust as a product property.

Track at least these metrics:

Coverage needs a denominator. Compare lineage-emitting Runs against the scheduler's inventory or warehouse query history, not just the number of received events. Publish trust badges such as “observed 12 minutes ago,” “declared only,” “column lineage unavailable,” or “2 of 17 upstream Jobs uninstrumented.” Avoid one opaque confidence score that hides the failure mode.

Validate with deterministic pipeline fixtures containing identity, aggregation, join, filter, rename, retry, failure, and partial-publication cases. In production, sample recent runs and compare the engine plan, emitted event, normalized edge, and query result end to end. Reconcile node and edge counts during every projection release.

Step 8: Roll out by decision value.

Start with business-critical domains and table-level lineage. Register canonical identities and owners, instrument the highest-impact schedulers and engines, and expose freshness and coverage before promising comprehensive impact analysis. Then add column lineage for engines with reliable logical plans, pre-deployment declared lineage, history, and PII propagation.

Success is measured by decisions: the percentage of critical changes with usable pre-deployment impact reports, the percentage of incidents whose first bad upstream boundary can be identified, reduction in unresolved identities, and coverage of critical producers. Node count and a visually dense graph are not success metrics.

High-Quality Sample Answer

“I would begin by defining the decisions and identities. For this system, a Dataset or Job is identified by canonical namespace and name within an environment, a Field adds a canonical path and schema version, and a Run is one execution identified by a UUID. Aliases and renames are explicit, time-bounded mappings. I would keep declared lineage from compiled plans separate from observed lineage from executions, and table dependencies separate from field dependencies.

At collection time, maintained integrations in Airflow, Spark, dbt, and the relevant Kafka processing frameworks emit a versioned payload. Spark and SQL engines should use logical plans when possible because regex cannot reliably understand dynamic SQL, joins, macros, or runtime branches. Each platform envelope includes a producer-unique event ID, producer, schema version, Job identity, Run ID, lifecycle state, and inputs and outputs. The ingestion gateway authenticates the producer, validates the payload, and appends it to a durable log before acknowledging. Repeated delivery of the same event ID is idempotent; invalid events go to a visible quarantine queue.

The raw event is immutable. A normalizer resolves registered aliases and creates versioned Jobs, Datasets, Fields, and edges while preserving the source event and resolver version. It never guesses an unknown identity. Run state affects serving: START and RUNNING can add evidence, while COMPLETE, ABORT, and FAIL are terminal. Failed Runs remain available for diagnosis, but their attempted outputs are not promoted as current published lineage unless an independent publication marker says the data became visible. COMPLETE proves execution completion, not data correctness.

Consumers build a canonical metadata store, a temporal edge store, and a Run store. Both upstream and downstream adjacency indexes support bounded breadth-first traversal. Every edge carries validity interval, observation time, producer, Job version, Run, status, declared-or-observed kind, and direct-or-indirect field transformation. A point-in-time query selects the identities and edges valid at that time. I would not precompute a universal transitive closure because high fanout, changing versions, and authorization make it expensive and risky.

For 30,000 datasets and 200,000 Runs per day, 500 peak events per second is modest enough to start with a durable partitioned log and indexed relational or key-value projections, then benchmark graph-specific storage against real fanout. Column edges are the larger dimension, so recent and frequently queried adjacency stays hot while detailed old history can be compressed. The three-hop, 2-second goal is measured at P50, P95, and P99 by graph type and fanout. Every request has depth and node budgets and returns an explicit continuation or truncation marker.

The query service performs authorization during graph expansion. It must not leak hidden node names, existence, or neighbor counts, and its cache key includes the caller's policy scope. The response includes provenance and visible gaps: declared only, observed time, unresolved identities, stale producers, missing column lineage, and uninstrumented Jobs.

I would make the projections replayable. Raw events are retained for 1 year. Consumers checkpoint offsets and write idempotently. Resolver or schema bugs are fixed by rebuilding a shadow projection, reconciling it against the active one, testing known paths, and switching only after validation. Snapshots shorten recovery but are tested with subsequent events to prove deterministic reconstruction.

Finally, I would measure trust with expected-job coverage, terminal-event completeness, identity-resolution failures, edge freshness, critical table and column coverage, rejected events, and sampled path correctness. The denominator comes from scheduler inventories and query history. I would launch table lineage for critical finance and customer domains, publish coverage gaps, then add column and declared lineage where extraction is reliable. The system is successful when engineers can make safer changes and trace incidents with evidence, not when the graph merely contains many nodes.”

Common Mistakes

• Starting with a graph database → Storage choice does not solve identity, execution state, history, or missing

instrumentation → Define entities, evidence, lifecycle, and query contracts first.

• Using display names as primary keys → Aliases, case changes, environments, and renames split or merge nodes →

Use canonical namespace/name identities and explicit time-bounded aliases.

• Treating declared and observed lineage as identical → Compiled possibilities can differ from runtime paths →

Store the kind and provenance of every edge and let queries filter them.

• Promoting every attempted output from a failed Run → Partial files or tables become false current truth → **Keep

diagnostic evidence, but require publication semantics before activating the edge.**

• Assuming COMPLETE means correct data → Execution can finish with duplicated or invalid output → **Keep data-quality

state separate from Run lifecycle.**

• Parsing all SQL with regex → Dynamic SQL, dialects, macros, and nested expressions produce false dependencies →

Prefer engine plans and supported parsers; expose unsupported coverage.

• Deduplicating by payload hash without an event contract → Distinct progress events can share content and retries can

differ in timestamps → Require a producer-stable event ID in the ingestion envelope.

• Keeping only the current graph → Past impact and incident reconstruction become impossible → **Retain immutable

events and temporal edge versions.**

• Precomputing all transitive paths → Fanout, version changes, and authorization cause expensive invalidation → **Use

bounded traversal and targeted caching.**

• Authorizing only the final response → Hidden nodes and degree counts can leak during traversal or caching → **Apply

policy during expansion and scope cache keys.**

• Reporting received-event count as coverage → Silent producers disappear from both the events and the metric →

Compare against scheduler inventory or query history.

• Showing a complete-looking graph with unknown gaps → Users make unsafe change decisions → **Surface freshness,

provenance, unresolved identities, and missing producers in every relevant result.**

Follow-Up Questions and Responses

Follow-up 1: A failed Spark Run wrote a table partition before emitting FAIL. Should the edge appear?

Keep the Run and attempted input-output edge as observed diagnostic evidence, tagged FAIL and not published. Whether it appears in the current production graph depends on the storage commit and publication policy. If the partition became visible, show it as a failed or suspect version until rollback or validation; if the write was atomic and aborted, do not activate it. Preserving both execution evidence and publication state avoids losing forensic detail or presenting a partial output as trusted truth.

Follow-up 2: How do you detect a producer that silently stopped sending lineage?

Received-event metrics cannot detect an absent producer alone. Build an expected-Run inventory from Airflow schedules, Spark history, dbt run results, warehouse query logs, or another independent control plane. Join expected Runs to lineage events by canonical Job and Run identity within a lateness window. Alert on missing starts, missing terminal events, and falling coverage by criticality tier, while distinguishing a disabled Job from a broken integration.

Follow-up 3: How would you answer impact analysis before the changed Job has run?

Use declared lineage extracted from the proposed compiled plan or manifest and compare it with the active definition. Traverse downstream from removed or changed outputs, label the result as declared pre-deployment evidence, and show where observed lineage confirms or disagrees with it. A CI gate can require owner review for affected critical assets. Do not claim the future runtime path is observed; dynamic branches may still differ after deployment.

Follow-up 4: Why not store everything in one graph database?

A single database may be acceptable after benchmarking, but Run event history, immutable replay, metadata search, and low-latency adjacency have different access patterns. Separating the durable event source from rebuildable projections protects recovery and allows each projection to evolve. Start with the fewest stores that satisfy those patterns, measure fanout and temporal-query cost, and add specialized storage only when evidence justifies the operational cost.

Follow-up 5: Column lineage multiplies the edge count by hundreds. What do you degrade first?

Protect correctness and critical decisions. Keep table lineage and recent column lineage for high-criticality domains hot, move old detailed edges to compressed history, and compute less-used field paths asynchronously. Enforce traversal budgets and return explicit partial status. Do not silently replace column answers with table guesses. Track column coverage by engine and domain so the degradation remains measurable and reversible.