skip to content

Why does a lineage graph miss some real consumers of a dataset, and how do you find the ones it cannot see?

level: seniorimportance: should knowfreq 40%

answer

  1. lineage only sees instrumented tools
  2. ad hoc queries, notebooks, spreadsheets
  3. exports, dynamic SQL, service accounts
  4. read logs fill the gap
  5. treat the graph as a lower bound

basics

~20 s

Lineage sees only tools that emit or expose it, so ad hoc queries, notebooks, spreadsheet exports, dynamic SQL and external copies go missing. Warehouse query and access logs, plus owners, reveal readers the graph cannot see.

solid answer

~40 s

A lineage graph is built from **what it can observe**: jobs that emit lineage, SQL that can be parsed, and tools with an integration. Real consumers fall outside that: **ad hoc queries and notebooks**, **spreadsheet and file exports**, **dynamic or generated SQL** a parser cannot resolve, **stored procedures**, **service accounts of other systems** that read the table directly, and **copies** that left the platform. So I treat the downstream graph as a **lower bound**. To find the rest I read the warehouse's **query and access logs** for the dataset over a representative window — including month-end and quarter-end — group readers by user or service account, and ask the owners of unknown readers. For risky changes I also watch the dataset after announcing it, because the logs catch readers that arrived after my window.

go deeper

for a junior

Know that lineage only covers tools that report it, and that ad hoc queries and exports are commonly missing.

for a middle

Explain how access or query logs reveal readers and why the observation window matters.

for a senior

Plan the full consumer discovery for a risky change, including service-account resolution and post-announcement monitoring.

for a principal

Set platform rules such as per-system identities, governed extracts and a coverage metric that shrink blind spots over time.

## Where lineage comes from A lineage graph is assembled from **observations**: - jobs and engines that **emit** lineage events when they run, - **SQL parsing** of known transformation code, - **integrations** with dashboards and other tools that expose what they read. Whatever does not emit, cannot be parsed, or has no integration is **invisible**. The graph is accurate about what it saw, and silent about the rest. ## Typical blind spots | Consumer | Why lineage misses it | |---|---| | Ad hoc queries in a SQL editor | interactive queries are not part of any job | | Notebooks | code runs outside the orchestrated pipeline | | Spreadsheet or CSV exports | the copy lives outside the platform | | Dynamic or generated SQL | table names are built at run time, so a parser cannot resolve them | | Stored procedures and views chains | logic hidden inside the database may not be parsed | | Other systems' service accounts | an application reads the table directly | | Reverse-ETL or sync jobs to other tools | the destination is outside the graph | ## Finding the invisible consumers 1. **Read the access logs.** Warehouses and query engines log who ran which query against which tables. Aggregating reads of the dataset by user or service account over a window gives the real reader list. 2. **Choose the window deliberately.** Monthly and quarterly jobs only appear if the window spans a month-end or quarter-end. 3. **Resolve identities.** Map service accounts to owning teams; a shared account hides several consumers behind one name. 4. **Ask the owners.** For each unknown reader, ask what they use and whether they keep a copy. 5. **Watch after announcing.** A deprecation notice plus continued monitoring of reads catches consumers that appeared after your window. ## Improving coverage over time - Give each consuming system its **own service account**, so logs name it. - Prefer **governed extracts** over ad hoc exports, so copies are recorded. - Feed **query-log-derived edges** into the lineage graph, marked as observed reads, so the graph stops being blind to interactive consumers. - Track **coverage**: the share of a dataset's readers in the logs that also appear in the lineage graph. ## Why interviewers ask it Senior candidates are expected to know that **a clean lineage graph is not a complete one**. Changes break consumers nobody knew about, and privacy questions ("where did this data go?") are answered wrongly if the answer stops at the edge of the graph. The strong answer treats lineage as a lower bound and names the log-based method that closes the gap.

  • Many readers use one shared service account. How do you untangle them?
    Look at the query text, client application names and source hosts recorded in the logs, which often differ per consumer, and ask the account's owner which systems use it. Long term, split the account so each consuming system has its own identity.
  • How would you measure lineage coverage for a critical dataset?
    Compare the distinct readers found in the access logs over a period with the consumers present in the lineage graph. The share of log readers the graph already knows is the coverage figure; the rest is the work list.

saying these in an interview costs you the question

  • Treating the downstream lineage graph as the complete list of consumers
  • Using a one-week log window and missing month-end jobs
  • Ignoring exports and copies that left the platform
  • Assuming one service account means one consumer