Reference22 Reference · 1 of 3

System tables

Databricks writes operational metadata into read-only Delta tables under the system catalog. They are the evidence base for cost, access, lineage and performance questions — no agent to install, no export to build.

Stand 29.07.2026

The system tables a warehouse team actually uses — with the two columns that decide whether you can build on one

Table	One row is	Answers	Status	Free retention
`system.billing.usage`	a metered usage record	where the money goes, per workspace, SKU and — if you tagged things — per domain	GA	365 days
`system.billing.list_prices`	a price for a SKU on one cloud over a validity interval	converting DBUs into currency without hardcoding a rate	GA	365 days
`system.access.audit`	an audited action	who read, changed or granted what, and when	Public Preview	365 days
`system.access.table_lineage`	a read/write between a table and a job or query	what feeds this table, and what breaks if it changes	GA	365 days
`system.access.column_lineage`	a column-level read/write	which source column a PII column derives from	GA	365 days
`system.query.history`	a completed query	which queries are expensive, and which are merely frequent	Public Preview	365 days
`system.compute.clusters`	a version of a cluster definition — a new row each time it changes	what compute exists and how it is configured	GA	365 days
`system.compute.warehouses`	a snapshot of a SQL warehouse's properties, written whenever they change	size, scaling limits and `auto_stop_minutes` as they were on the day in question — the compute sweep's before/after baseline	GA	365 days
`system.compute.warehouse_events`	a SQL warehouse state change	whether auto-stop is doing its job	GA	365 days
`system.compute.node_timeline`	one minute of utilisation for one node	whether a cluster is idle, starved or simply the wrong size	GA	90 days
`system.lakeflow.jobs`	a version of a job definition (SCD2 — a new row is emitted whenever a row changes)	the inventory of what is scheduled, once you take the newest row per `job_id`	GA	365 days
`system.lakeflow.job_run_timeline`	a period within a job run — several rows per run, because a run lasting more than an hour is recorded over multiple rows	success, duration and the run-duration trend, once you aggregate to `run_id`	GA	365 days
`system.lakeflow.pipelines`	a version of a pipeline definition (SCD2 — the same trap as `jobs`)	which pipelines exist, and what one looked like before the edit	Public Preview	365 days
`system.lakeflow.pipeline_update_timeline`	a period within a pipeline update — `update_id`, `result_state` (COMPLETED, FAILED, CANCELED), `full_refresh_selection`	did the update finish, and was it a full refresh — as an ordinary table anyone with SELECT can read, rather than through `event_log()`	Public Preview	365 days
`system.storage.predictive_optimization_operations_history`	one OPTIMIZE, VACUUM or ANALYZE that predictive optimization ran for you	what maintenance is already automatic — and what your scheduled job is now paying to duplicate	Public Preview	180 days
`system.data_quality_monitoring.table_results`	an anomaly-detection result for one table	which tables are stale, shrinking, or failing the checks nobody wrote by hand	Public Preview	indefinite
`system.information_schema.*`	a catalog object as it is now	schema drift, grants, constraints, comments	GA	current state, no history

Three limits worth stating before you promise anything

Schemas must be enabled per metastore before they appear. Whether enabling backfills is documented nowhere, in either direction — see system-schema-retroactivity in the register — so enable on day one rather than find out the hard way, and do not tell a client their history is definitively gone without reproducing it first. Retention is not uniform: 365 days for most, 180 for predictive-optimization operations history, 90 for compute.node_timeline. A baseline you promise to re-run at handover has to fit inside the shortest window it touches, and a year-on-year comparison out of node_timeline does not exist. And the lineage tables keep only a rolling one year, while Catalog Explorer and the lineage API retain indefinitely after 01.09.2024 — an audit question spanning more than a year is answered by the API, not by the system table.

#Reading system tables without being misled by them

What

System tables are the cheapest evidence available on this platform: read-only, already populated, and queryable with plain SQL. The whole audit library next door is built on nothing else. The skill is not writing the query — it is knowing what a missing row means.

Why

The failure mode is subtle and expensive: reading absence as evidence. A system table returning no rows can mean the thing did not happen, or that the schema was enabled last month, or that lineage could not be inferred for that operation, or that the retention window rolled past it.

Report those as four different findings, because a client hears "there is no evidence anyone accessed the PII table" as "nobody accessed it". Those are not the same sentence, and only one of them is defensible.

How

sql

Cost per domain — the query that starts most engagements

SELECT
    u.custom_tags['domain']       AS domain,
    u.custom_tags['environment']  AS environment,
    ROUND(SUM(u.usage_quantity * p.pricing.effective_list.default), 2) AS cost_usd,
    count_if(p.sku_name IS NULL)  AS unpriced_rows   -- must be 0, or cost_usd is short
FROM system.billing.usage AS u
LEFT JOIN system.billing.list_prices AS p
       ON  u.cloud    = p.cloud          -- AWS | AZURE | GCP. Without this predicate a
                                         -- SKU priced on two clouds joins twice and the
                                         -- cost is DOUBLED, plausibly.
      AND u.sku_name = p.sku_name
      AND u.usage_end_time >= p.price_start_time
      AND (p.price_end_time IS NULL OR u.usage_end_time < p.price_end_time)
WHERE u.usage_date >= current_date() - INTERVAL 30 DAYS
GROUP BY ALL
ORDER BY cost_usd DESC;

Three details in that query are the difference between a number and a defensible number. pricing.effective_list.default — not pricing.default — is the key that resolves list and promotional price into the effective list price the cost is actually computed from. u.cloud = p.cloud stops a SKU that is priced on more than one cloud from fanning out and multiplying the total. And unpriced_rows exists because the join is a LEFT JOIN: an unmatched SKU contributes usage_quantity * NULL, SUM skips it, and the row costs nothing at all. Without that counter the query commits the exact failure the rest of this topic warns about — it reads absence as evidence, silently, in its own headline figure.

Run it on an untagged account and every row says NULL. That is not a broken query — it is the finding, and it is the argument for the tag taxonomy in the storage and cost route.

Two tables in the list above change what you can promise a rota. system.lakeflow.pipeline_update_timeline carries pipeline outcomes — update_id, result_state, full_refresh_selection — as an ordinary system table any rota member with SELECT can read, which is the way past the event_log() table-valued function being reachable only by someone who can already see the pipeline. system.compute.warehouses gives the warehouse configuration as it stood on a given day, so a compute recommendation can be evidenced against what was actually running rather than against what the console shows today. Both are Public Preview: use them, and write that down before a runbook depends on them.

Doing it

Establish the earliest row you can actually see before quoting any historical numberSELECT MIN(usage_date) FROM system.billing.usage, and the equivalent on every other schema you cite.
Join usage to list_prices on cloud, sku_name AND the validity intervalRead pricing.effective_list.default — prices change, SKUs are priced per cloud, and hardcoding a rate is wrong twice over.
Carry an unmatched-row counter beside every priced sumA missing price then shows up as a number instead of as a silently smaller total.
Check the retention of each table you build a recurring report onA quarterly comparison out of node_timeline is impossible at 90 days, and nobody discovers that until the second quarter.
State the window on every figure you report'EUR 4,200' means nothing; 'EUR 4,200 over the last 30 days' means something.
Treat an empty result as a finding to explain, never as a clean bill of health

Embedding it

Have the team run the cost query on their own account before you show them yoursThe NULL domain column makes the tagging argument better than any slide.
Ask 'what would make this query return zero rows?' about every check they writeIt is the single most useful habit for anyone reading operational data.
Have someone delete the u.cloud = p.cloud predicate on a multi-cloud accountWatch the total change without a single error. A cost figure that moves silently teaches join discipline in one sitting.
Hand over the queries as a repository they own, not as a documentThe ones they can re-run are the ones that survive.

Defaults

Enable the system schemas on day oneThe tables are free to hold, and waiting cannot make the history better.
Never hardcode a DBU priceJoin list_prices on cloud, SKU and the validity interval, and read effective_list.default.
Every priced sum ships with its unmatched-row count in the same result
Every reported figure carries its time window and the date it was run
Audit data is itself sensitive; grant it deliberately, as covered in the audit and lineage route

Gotchas

Quoting a historical figure from a schema enabled last weekWhether enabling backfills is undocumented (register key system-schema-retroactivity), so the period behind the number may be shorter than the client hears — and short in the flattering direction, because the missing history is missing spend. Report MIN(usage_date) beside the figure and the ambiguity disappears.
Telling a client their pre-enablement history is definitively goneThat is asserted all over the internet and confirmed by no first-party page; if it matters to the engagement, reproduce it on their metastore before you put it in a document.
Joining usage to list_prices on sku_name alonelist_prices carries a row per SKU per cloud (AWS, AZURE, GCP), so a SKU priced on more than one cloud fans out and the cost is multiplied by the number of matching clouds — a total that is too high, looks plausible, and nobody re-derives.
Reading pricing.default instead of pricing.effective_list.defaulteffective_list is the key that resolves list and promotional price into the price the cost is calculated from; the sibling keys are not what the invoice was computed against.
LEFT JOIN to list_prices with no unmatched-row counterAn unpriced SKU contributes usage_quantity * NULL, SUM skips it, and that usage costs zero for the rest of the engagement — absence read as evidence, inside the query that warns about it.
Counting rows in system.lakeflow.job_run_timeline as runsOne row is a period within a run, and a run longer than an hour is written over several rows, so run counts scale with duration and the slowest job also looks like the busiest. Aggregate to run_id first.
Joining system.lakeflow.jobs or system.lakeflow.pipelines without picking a versionBoth are SCD2 — a new row on every edit — so a plain join multiplies every run by the number of times the definition was ever changed, and the team that tunes its jobs most appears to run them most.
Reading an empty lineage result as 'nothing depends on this table'Both lineage tables are a subset: rows are emitted only when lineage can be inferred, so absence is not evidence of absence. Dropping a table on that basis is how a report dies silently.
Column lineage misses inserts that write explicit literal valuesA column populated by a constant appears to have no upstream at all — exactly the case you most want to spot in a PII trace.
Assuming system tables are consistent across cloudsAvailability and column sets differ between AWS, Azure and GCP; check on the client's cloud before promising a query works.

Depends on