System design interview

Data Lineage Tracking

MediumLocked55 min read

Column-level lineage from source to dashboard for impact analysis and compliance; DataHub/OpenMetadata.

system-designlineagecataloggovernance

Start here

The problem in plain words

A data engineer decides that user_id is a confusing name and renames it. Within the hour, twelve dashboards stop working, and three teams are asking who broke their numbers. Nobody knew those dashboards depended on that column.

Data does not stay in one place. A value starts in a raw table, passes through cleaned tables and models, and ends up in a field on a dashboard. Each hop creates a dependency. Lineage is the map of those hops: where a piece of data came from and where it goes.

With a lineage map you can answer two kinds of question quickly. Before a change: what will break? After a problem: where did this wrong number come from? It is also how a company proves where personal data flows.

Words you will meet

Lineage
The recorded path of data from its source, through each transformation, to where it is used.
Upstream and downstream
Upstream is where data came from. Downstream is what depends on it.
Table-level lineage
Which tables feed which tables.
Column-level lineage
Which column feeds which column. More precise, and more work to collect.
Impact analysis
Asking "if I change this, what breaks?" and getting an answer from the lineage map.
Data catalog
A searchable directory of datasets with owners, descriptions and lineage. DataHub and OpenMetadata are examples.
OpenLineage
An open standard for tools to report what data they read and write, so lineage can be collected automatically.
dbt manifest
A file dbt produces describing every model and its dependencies.
PII
Personally identifiable information. Lineage shows which tables it flows into.
A rename that quietly breaks twelve dashboards
renamebreakData engineerrenames user_idSource tablecolumn changed12 dashboardsbreak
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The engineer renames the column, believing it is only used in one place.
  2. 2.Models and dashboards downstream still use the old name, and twelve of them fail. Nobody had a map to warn them.

The numbers, as things you can picture

  • Hundreds of models and jobsToo many to remember by hand. Dependencies must be collected automatically.
  • Business intelligence tools includedThe map must reach the dashboards, since that is where breakage is noticed.

What the interviewer wants

Design how lineage is collected and shown, so that engineers can see the impact of a change and auditors can follow personal data.

Interview room

Design column-level lineage source→dashboard for impact analysis and audits; discuss DataHub/OpenMetadata.

Basics

Lineage is a graph of datasets and jobs. Table-level vs column-level. Wiki lineage rots. OpenLineage emitters feed catalogs. Impact analysis and provenance are the killer apps.

Prompt

Automate capture, answer impact/provenance, catalog UX, freshness of lineage, include BI.

Good

Emitters, column edges on tier-0, owners, BI connectors, honest notebook gaps.

Clarify/scope

Stack coverage, column-level priority, existing catalog, PII tags.

Scoring lens: Data Lineage Tracking

Interviewers reward incident-first teaching, a clear control loop, and named owners. Tool logos without that loop score poorly.

Weak vs strong answers on Data Lineage Tracking

Weak: list products. Strong: tell the failure story, define the mechanism, draw the path, state rollout and monitors.

Scope control

Say what is out of scope for v1. Come back only if follow-ups demand it. This is a seniority signal for Data Lineage Tracking.

Clarifying then committing

Ask a few high-use questions about SLAs, owners, and scale, then sketch. Endless questions without a diagram look evasive.

Impact ticket template

When changing a column, the catalog should generate: downstream models, dashboards, owners, last lineage emit time, and suggested notification list. Engineers paste that into the change ticket.

Emitter SRE

Lineage is only as fresh as emitters. Alert when a tier-0 job has not emitted OpenLineage in N hours. A stale graph is worse than no graph because it creates false confidence.

BI inclusion

If Looker/Tableau/Power BI datasets are missing, impact analysis lies. Budget connectors for the top BI tool first.

Interview framing detail 1

For lb-sd-27, spend the first minutes making the problem concrete: who gets hurt when this fails, what SLA is implied, and what is explicitly out of scope. Then teach the prerequisite concept before proposing boxes. A candidate who names vendors first usually loses the plot. Write two clarifying questions on the board and answer them with assumptions if the interviewer shrugs. Keep the first diagram small enough to redraw when a follow-up changes a constraint.