Design column-level data lineage from source to dashboard

WHAT THIS TESTS: This question tests whether you understand metadata systems at scale, specifically how to trace individual columns through heterogeneous transformation engines. Interviewers want to see that you distinguish between coarse table-level lineage and fine-grained column-level lineage, and that you recognize the difference between static SQL parsing and dynamic runtime instrumentation. They also care about your ability to choose storage and query patterns for graph data, handle schema evolution, and design for both data engineers investigating pipeline failures and compliance auditors requiring provenance reports.

A GOOD ANSWER COVERS: First, collection: describe AST-based extractors that hook into Spark's Catalyst optimizer or dbt's compilation phase to capture column mappings without relying on brittle regex. Mention that you need both static analysis for SQL and optional runtime hooks for dynamic code. Second, storage: explain why a graph database like Neo4j, JanusGraph, or Amazon Neptune fits better than relational stores for billions of column nodes and directed edges, and note that you would partition by dataset or time to control write throughput. Third, serving: propose a query API that supports upstream root-cause tracing and downstream impact analysis, plus a DAG visualization that handles ten-thousand-node subgraphs via lazy expansion. Fourth, scale and reliability: mention asynchronous event streaming through Kafka, idempotent writes, and periodic reconciliation jobs to heal gaps from failed extractions.

COMMON WRONG ANSWERS: A red flag is suggesting manual documentation or spreadsheet tracking for a system meant to operate at scale. Another is proposing to parse SQL with string matching instead of ASTs, which breaks on nested CTEs and dialect variations. Storing lineage in a single relational table is also a mistake because recursive joins for column paths become prohibitively expensive beyond millions of rows. Finally, ignoring the difference between table-level and column-level lineage shows shallow understanding of the domain.

LIKELY FOLLOW-UPS: The interviewer may ask how you handle dynamic SQL generated at runtime, how you version lineage graphs when schemas change, or how you ensure sub-second query latency for a dashboard with ten thousand columns. They might also probe how you integrate with existing data catalogs like DataHub or Collibra, or how you detect and resolve cycles in the lineage graph.

ONE CONCRETE EXAMPLE: Imagine a source column named customer_id in a Postgres table. A dbt model casts it to string and renames it user_id. A downstream Spark job joins it with a clickstream table on user_id to produce a final aggregate. Your extractor captures the Postgres column as a node, the dbt transformation as an edge with a cast operation, and the Spark join as a multi-parent edge. When the dashboard shows anomalous nulls in the aggregate, a user clicks the column and traverses the graph backward in milliseconds to discover the nulls originated in a specific Postgres shard ingested last Tuesday.