Design a Column-Level Data Lineage System at Scale

WHAT THIS TESTS: This question assesses your ability to design a robust metadata system. It's not about the data itself, but the data about the data. The interviewer is looking for your understanding of the modern data stack (Spark, dbt, BI tools), the tradeoffs between different metadata collection techniques (static vs. runtime), and your ability to choose the right data model and storage technology for highly interconnected, graph-like data at scale.

A GOOD ANSWER COVERS: A strong answer addresses four key components in order. First, Collection: a hybrid approach is best. Use static analysis by parsing SQL from dbt models and query logs (using libraries like sqlglot) to get broad coverage. Complement this with runtime instrumentation for key systems, like using a SparkListener to capture exact query plans, to ensure accuracy. Second, Modeling: represent lineage as a Directed Acyclic Graph (DAG). Nodes can be Column, Table, Job, Dashboard, and edges represent relationships like TRANSFORMED_INTO or GENERATED_BY. Third, Storage: use a graph database like Neo4j or AWS Neptune. Relational databases are a poor fit because tracing lineage requires recursive queries that are inefficient with SQL JOINs. A graph DB is optimized for these traversals. Fourth, Serving: build an API over the graph database to support key use cases like upstream source tracing ('Where did this data come from?') and downstream impact analysis ('What will break if I change this column?'). This API would then power a visualization front-end.

COMMON WRONG ANSWERS: A major red flag is proposing a manual solution, like documenting lineage in a wiki or spreadsheets; this is completely unscalable. Another common mistake is choosing a relational database (like Postgres) for storage without acknowledging the extreme performance challenges of deep, recursive graph traversals. Candidates also stumble by suggesting only one collection method (e.g., 'we'll just parse all SQL'), which is naive and misses lineage from non-SQL steps or dynamic queries. Finally, providing a table-level solution misses the core requirement of column-level granularity.

LIKELY FOLLOW-UPS: Expect questions like: 'How would you bootstrap this system for a data warehouse with 10,000 existing tables and jobs?' (Answer: Start by parsing historical query logs for a baseline, then prioritize instrumenting new and critical pipelines). Another is, 'How do you handle schema changes, like a dropped column?' (Answer: The collection system should detect the change, mark the lineage path as stale or broken in the graph, and potentially trigger an alert). They might also ask about the scale of metadata, which is typically 1,000-10,000x smaller than the actual data (e.g., terabytes of metadata for petabytes of data).

ONE CONCRETE EXAMPLE: To trace the origin of a dashboard metric sales_report.total_revenue, the system would query the graph database. The query would start at the total_revenue column node and traverse backwards along TRANSFORMED_INTO edges. It might find it comes from a dbt model that aggregates mart.orders.order_value. Traversing further back, it finds mart.orders.order_value is generated by a Spark job that joins staging.prices.price and staging.orders.quantity. The final trace, source_db.prices.price -> Spark Job -> mart.orders.order_value -> dbt model -> sales_report.total_revenue, is returned by the API.