Design a Column-Level Data Lineage System at Scale

WHAT THIS TESTS: Your ability to architect a real-world metadata system, not just define a concept. The interviewer is evaluating if you can break a complex problem (lineage) into three distinct engineering challenges: collection, storage, and serving/visualization. They are testing your knowledge of modern data tooling (Spark, dbt), metadata standards (OpenLineage), and data modeling, specifically for graph-like data. This is a systems design question for data engineers.

A GOOD ANSWER COVERS: Three key areas in order. First, Collection: Acknowledge this is the hardest part. Propose a hybrid approach using an open standard like OpenLineage to capture metadata from instrumented jobs (e.g., Spark listeners, dbt run artifacts). For legacy systems, mention parsing SQL query logs from the data warehouse as a less reliable fallback. Second, Storage: Propose a graph database (e.g., Neo4j, Neptune) as the storage layer. The data model consists of nodes (representing columns, tables, jobs, dashboards) and edges (representing transformations). This naturally models the many-to-many relationships in lineage. A relational DB is a poor fit due to the need for complex recursive queries. Third, Visualization/APIs: Describe building a UI on top of the graph DB. The primary use cases are impact analysis (querying downstream dependencies) and root cause analysis (querying upstream sources). Expose this data via an API for programmatic access.

COMMON WRONG ANSWERS: A frequent mistake is focusing only on table-level lineage and ignoring the "column-level" requirement, which is an order of magnitude harder. Another red flag is suggesting a relational database without a strong justification for how you'd handle recursive traversals efficiently. The biggest mistake is hand-waving collection. An answer like "we'll just parse the SQL" is too naive; it fails to account for dynamic queries, CTEs, and transformations happening outside of SQL. It shows a lack of practical experience with the messiness of real-world data pipelines.

LIKELY FOLLOW-UPS: How do you ensure accuracy and coverage? (Answer: Reconciliation jobs that compare graph lineage against reality, and dashboards tracking what percentage of assets are covered). How does this system handle schema evolution, like a column being renamed? (Answer: Versioning nodes in the graph and creating a "renamed_from" edge). How would you scale the query layer for a graph with 50 million nodes? (Answer: Strategic indexing on key node properties like asset name, and potentially sharding the graph database).

ONE CONCRETE EXAMPLE: A column users.email in a source Postgres database is ingested by a Spark job. The job hashes it and writes it to a Parquet file in S3 as processed_users.email_hash. A dbt model then reads this file, joins it with other data, and materializes a dimension table in Snowflake called dim_users, with the column named user_email_hash. This column is then used in a "Weekly Active Users" dashboard. In our graph DB, this creates at least 4 nodes (the columns) and 3 nodes (the jobs/dashboard), connected by "reads_from" and "writes_to" edges, allowing you to trace from the dashboard all the way back to the source Postgres column.