Lineage answers what the audit log cannot: not who read this number, but where it came from. It is genuinely good, and it is a subset — both halves of that sentence have to reach the client.
Unity Catalog captures lineage automatically for work done on the platform, at two granularities. system.access.table_lineage answers 'what fed this table and which job wrote it'. system.access.column_lineage narrows it to a single column, which is what an auditor asking about a specific number actually needs.
Why
Two questions arrive repeatedly and one graph answers both. From the business: 'the revenue figure in this report is wrong — where does it come from?' From the auditor: 'demonstrate that this number derives from the source system, and show what happens to it on the way.' Without lineage both are answered by an engineer reading code, which takes hours and is as accurate as their memory.
Impact analysis is the second query above — the same graph walked source-to-target. Before altering silver.sales_order_line, the set of downstream consumers is a fact rather than an estimate, which is what turns 'we think nothing depends on this' into a change somebody is willing to make.
How
sqlWhere does gold.fct_order_line.amount_eur come from?
SELECT DISTINCT
source_table_full_name,
source_column_name,
entity_type,
entity_id
FROM system.access.column_lineage
WHERE target_table_full_name = 'dwh_prod.gold.fct_order_line'
AND target_column_name = 'amount_eur'
AND event_date >= current_date() - INTERVAL 90 DAYS
ORDER BY source_table_full_name;
The expected result is three sources — quantity and unit_price from silver.sales_order_line, plus the rate column from silver.fx_rate — which is business rule 2 read back out of the platform. A fourth source, or a missing one, means the implementation and the rule have diverged, found without anyone reading the transformation code.
Table lineage answers the other direction, and it is the query to run before touching a silver schema. Same table, read from source to target instead of target to source:
sqlImpact analysis — what consumes silver.sales_order_line?
SELECT target_table_full_name AS downstream_table,
entity_type,
count(*) AS runs,
max(event_date) AS last_seen
FROM system.access.table_lineage
WHERE source_table_full_name = 'dwh_prod.silver.sales_order_line'
AND event_date >= current_date() - INTERVAL 90 DAYS
GROUP BY target_table_full_name, entity_type
ORDER BY last_seen DESC;
Do not filter out the rows where downstream_table is NULL. A NULL target means the read did not write a table — a notebook, a dashboard or an ad-hoc query consumed it — and those are consumers that break just as loudly as a pipeline. entity_type is what tells you which kind you are looking at.
Lineage stops at the edge of Databricks unless you extend it. External lineage is now generally available, and here it is the difference between a picture that starts at bronze.sap_vbap and one that starts at the SAP system and ends at the Power BI report finance actually opens. It is not automatic: you register the external system as a metadata object and declare the relationship to the Unity Catalog securable. Registering costs CREATE EXTERNAL METADATA on the metastore; declaring a relationship costs MODIFY on the object. The metastore caps you at 10,000 external metadata objects and 100,000 relationships — generous for one warehouse, worth knowing before someone scripts a per-report object.
Doing it
Answer the next 'where does this number come from' with the column-lineage query, liveRun it in front of the person asking, not a diagram drawn afterwards.
Register external metadata objects for SAP and the Power BI semantic model and declare the relationshipsHalf a day that shortens every later audit conversation. Check the result in Catalog Explorer or through the External Lineage API; a system-table query will not show it and that is not a bug in your declaration.
Run the impact query before any silver schema change and paste the result into the pull request
Compare the column lineage of amount_eur against business rule 2 once per releaseA divergence there is a correctness bug no row-count test will catch.
Embedding it
Next time the business challenges a number, hand over the keyboardLet the team run the query while the business person watches. They will not draw that diagram by hand again.
Add 'external lineage declared' to the definition of done for pipelines touching systems outside DatabricksIt is then a build step rather than a governance clean-up.
Have them do the impact analysis for a change they are afraid ofDiscovering only two things depend on a table is how a team stops fearing its own warehouse.
Defaults
Column lineage for correctness questions, table lineage for impact analysisReaching for the wrong one wastes the meeting.
External lineage declared for SAP and Power BI as part of the pipeline's definition of doneAnd verified in Catalog Explorer — never in a system-table query.
Filter on event_date in every lineage query; these tables get large and are laid out by date
Gotchas
Reading lineage as a static diagramRows are emitted per run, so a table refactored two months ago still shows its old sources inside the window — constrain event_date or you are looking at an architecture that no longer exists.
Assuming external lineage appeared because the feature is GANothing is declared until someone declares it, and a graph that stops at bronze in front of an auditor reads as 'we cannot trace to source'.
Debugging a declaration that 'did not work' because the system-table query still stops at bronzeIt did work — external lineage is never written to system.access.table_lineage or system.access.column_lineage. Look in Catalog Explorer, and expect to lose an afternoon the first time nobody tells you this.
Using lineage as the only impact analysis before a breaking changeIt covers what ran inside Unity Catalog in the window; a monthly report that has not run yet this month is not in it, and it will break.
Confusing entity_id with a job nameIt is an identifier, not a label — join it out, or hand the client a column of opaque values.
Four limits, all of which a client will eventually find. Say them first — this is the part of the route that protects you.
Retention is two regimes for one feature. The lineage system tables keep a rolling one-year window; Catalog Explorer and the lineage API retain lineage captured after 01.09.2024 indefinitely. A question spanning more than a year cannot be answered from system.access.table_lineage and can be answered in Catalog Explorer — a surprising sentence to say in a meeting, and much worse to discover mid-answer.
Both lineage tables are a subset. Records are emitted only where lineage can be inferred. Work that writes through a path rather than a table name, or runs on an engine outside Unity Catalog, produces no row. Hence the sentence that has to survive into the client's own documentation: absence of lineage is not evidence of absence of a data flow.
External lineage is declared, and then invisible to every query in this route. The limitation is documented in one flat sentence: external lineage is not recorded in the lineage system tables — neither system.access.table_lineage nor system.access.column_lineage. Declared SAP and Power BI relationships appear in Catalog Explorer and through the External Lineage API, and nowhere else. So the graph you demo and the graph you can query, join, schedule and paste into a report are two different artefacts, and it is the one reaching the source system that cannot be produced from SQL. This is the most consequential limit on the page for audit evidence: it decides what a scheduled exhibit is able to contain.
Column lineage misses literals. A column filled with an explicit constant has no source column and contributes nothing to the graph. This warehouse hits it in one guaranteed place — the unknown member from business rule 7:
Why
The commercial risk is asymmetric. Lineage demos well, so a client will over-read it — and the moment they treat the graph as complete, every downstream decision inherits an assumption you did not correct. The external-lineage split is where that costs money rather than credibility: what you showed in Catalog Explorer is not what the scheduled query can produce, and the difference surfaces when someone tries to automate the exhibit you promised.
The technical risk is the retention split. GDPR and financial-audit questions routinely span more than twelve months, and the system-table query returns rows for the part of the window it covers with nothing marking the rest as missing. A partial answer that looks complete is worse than an empty one.
How
sqlThe unknown member: every value is a literal, so this INSERT emits no column lineage
INSERT INTO gold.dim_customer (
customer_sk, customer_id, customer_name, country,
customer_segment, _tech_valid_from, _tech_is_current
)
SELECT 'UNKNOWN',
'UNKNOWN',
'Unknown customer',
'UNKNOWN',
'UNKNOWN', -- NOT 'C'. A real segment value here folds every
-- unresolved line into a segment finance reports on.
TIMESTAMP '2020-01-01 00:00:00',
true
FROM (SELECT 1) AS seed -- a WHERE clause needs a relation
WHERE NOT EXISTS (
SELECT 1 FROM gold.dim_customer WHERE customer_sk = 'UNKNOWN'
);
Every fact line that failed to resolve to a customer points at that row. Ask lineage where dim_customer.customer_name comes from and it answers 'SAP' — truthfully and incompletely, because one of its values is a hard-coded string in a load script, and that is exactly the value an auditor would most want explained.
Doing it
Write the retention split into the permissions conceptThe limit is then documented before anyone needs it rather than confessed during an audit.
For anything older than a year go to Catalog Explorer or the lineage APISay in the answer which source you used.
Split every evidence exhibit in two before promising itThe platform-internal half can be a scheduled query; the external half is a Catalog Explorer or External Lineage API export produced by hand. Price the second as manual work, because it is.
Grep the repository for literals written into gold columnsEach is a blind spot, and the unknown member is the one you already know about.
When lineage returns nothing for a table, read the code before reporting it as unusedAbsence is a prompt to look, not a conclusion.
Embedding it
Show the unknown-member blind spot liveRun the column-lineage query on dim_customer, then show the INSERT. The gap between the two lands in ninety seconds.
Have the team write the limits paragraph in their own documentation, in their own wordsA limit they can explain is one they will not oversell to their own management.
Let them try to answer a question deliberately older than a year, and be misled onceBeing taken in by the partial result is what makes the retention split memorable.
Have them declare one external relationship, then go looking for it in system.access.table_lineageFive minutes of not finding it teaches the fourth limit better than any sentence you can write.
Defaults
State limits in the same document as the findings, never in a footnote
System tables inside a year; Catalog Explorer or the API beyond itName the source you used in the answer.
System tables for the platform-internal graphCatalog Explorer or the External Lineage API for anything crossing the platform edge — and name which produced each exhibit.
Never write 'no other consumers'Write 'no other consumers appear in lineage for the last N days' — true, and still useful.
Gotchas
The one-year window returning rows for the covered part of a longer questionNothing marks the boundary, so the answer looks complete and understates the flows by however much history the question needed.
Reporting 'no downstream consumers, safe to drop' from a lineage queryAnything reading through a path, from outside the metastore, or on a cadence longer than your window is invisible — and the report that breaks is always the quarterly one.
Promising an end-to-end SAP-to-Power-BI lineage exhibit as a scheduled queryExternal lineage being GA and the relationships being declared is not enough: the declaration succeeds, Catalog Explorer shows it, and the query returns exactly what it returned before — external lineage is not written to the lineage system tables at all, so the deliverable you sold cannot be automated and somebody screenshots Catalog Explorer every quarter instead.
Assuming column lineage covers derived constantsamount_eur traces cleanly and the UNKNOWN customer row does not, so the exact case an auditor asks about is the case with no lineage.
Quoting the 01.09.2024 indefinite-retention date without checking which interface the client usesIt applies to Catalog Explorer and the API; the system tables are still a rolling year, and mixing them up in writing is a correction you have to issue later.