Databricks interview questions: System tables: who spent that?

Say this first: system.billing.usage is a Delta table you query with ordinary SQL, and custom tags on the workloads are what turn a DBU number into a team name.

Why: The usage dashboards are built on system tables, so they only slice the bill along the dimensions Databricks chose. system.billing.usage records every workload: which SKU ran, for how many DBUs, which workspace, and the tags attached to the cluster, warehouse, or job. Joining it to system.billing.list_prices on the SKU and the time window turns DBUs into an estimated dollar figure. Grouping by usage.custom_tags is the line that answers 'which team'. Without tags the table still tells you what ran, but not who owns it, and that is the gap the dashboard cannot close either.

SELECT u.custom_tags.team AS team, SUM(u.usage_quantity * p.pricing.default) AS usd
FROM system.billing.usage u
JOIN system.billing.list_prices p
  ON u.sku_name = p.sku_name
 AND u.usage_start_time BETWEEN p.price_start_time AND COALESCE(p.price_end_time, NOW())
GROUP BY team
ORDER BY usd DESC

Common mistake: Treating the usage dashboard as the source of truth and concluding the bill cannot be attributed.

Follow-up they will ask: What do you do about the rows where the team tag is null?

Say this first: System schemas collect from the moment they are enabled. There is no backfill of history from before that.

Why: Each system schema has to be turned on per metastore, and several are off by default. Once enabled, rows start landing for new activity only. A query over system.billing.usage filtered to last quarter returns whatever has accumulated since you flipped the switch, which might be a day. The cloud provider's own billing export is the only record of the months before that, and it will not have your Databricks tags on it. The practical move is to enable every schema you expect to need on day one of a workspace, then tell stakeholders the attribution clock starts now.

Common mistake: Assuming system tables contain the workspace's full history because the UI shows older jobs.

Follow-up they will ask: The job UI shows runs from three months ago. Why can't the system table?

Say this first: Anything created without a custom tag — an all-purpose cluster someone spun up, a warehouse, a job from the UI — lands as unattributed usage.

Why: usage.custom_tags only contains tags that were set on the compute or the job when it ran. A cluster created by clicking in the UI, a SQL warehouse with no tags, and a job defined before the tagging standard existed all bill to null. Serverless and system workloads such as predictive optimization bill under their own SKU and often have no team tag unless a budget policy assigns one. The fix is a default tag enforced where compute is created: a budget policy, a cluster policy that requires a team tag, and asset bundles that set the tag in YAML so a UI click is no longer the path. Then query the null-tag remainder weekly, because that query is the audit.

SELECT sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE custom_tags.team IS NULL
  AND usage_date >= current_date() - 7
GROUP BY sku_name
ORDER BY dbus DESC

Common mistake: Tagging new jobs and assuming the existing clusters inherit the standard.

Follow-up they will ask: Where do you enforce the tag so a UI-created cluster cannot skip it?

Say this first: system.query.history has the statement text, duration, and bytes scanned, so you can rank the warehouse's queries by what they actually cost to run.

Why: Warehouse size explains the rate, not the cause. system.query.history records each statement against a SQL warehouse: the text, the user, the warehouse id, total_duration_ms, and the bytes read. Sorting a single warehouse by duration or bytes over the week usually surfaces one dashboard query or a SELECT * that started scanning far more after a table grew. Cross-check the query profile for that statement to see whether it was a cache miss, a join explosion, or a full scan. Billing usage confirms the warehouse's DBU total; query history tells you which statement to fix. Filter on the start time or you will scan the whole history table.

SELECT user_name, total_duration_ms, read_bytes, statement_text
FROM system.query.history
WHERE compute.warehouse_id = :id
  AND start_time >= current_timestamp() - INTERVAL 7 DAYS
ORDER BY total_duration_ms DESC
LIMIT 20

Senior depth: Unchanged SQL gets more expensive when the table underneath grows, loses clustering, or stops hitting the result cache because a non-deterministic function was added. Bytes scanned in query history is the column that distinguishes those from a warehouse that was simply busy.

Common mistake: Resizing the warehouse because the bill went up, without looking at which query changed.

Follow-up they will ask: The top query's text has not changed. What else could have made it slower?

Say this first: system.access.table_lineage and system.access.column_lineage record reads and writes observed between tables, so you can walk what depends on that column.

Why: Unity Catalog records lineage as queries actually run. table_lineage connects a source table to a target table for each observed read, with the workspace, the user, and the time. column_lineage goes one level deeper and names the source and target columns, which is what you need for a drop-column question. A column with no downstream lineage for the last 90 days is a much safer drop than one feeding three gold tables. The limit is that lineage only exists for workloads that ran through Unity Catalog after the schema was enabled, so a job that has not run recently, or that reads the table from outside Databricks, will not appear.

Senior depth: Lineage follows Spark and SQL plans that Unity Catalog can see. A collect() into pandas, a read through a raw cloud path, or a job that last ran before lineage was enabled all fall outside it, so an empty result is 'not observed', not 'unused'.

Common mistake: Treating an empty lineage result as proof that nothing depends on the column.

Follow-up they will ask: A notebook reads the column into a pandas DataFrame and writes a CSV. Does lineage catch it?

Say this first: System tables grow without your layout choices, and a query that does not filter the usage date scans the full retained history.

Why: You do not OPTIMIZE system tables and you do not choose their clustering. They are retained for a long window and appended continuously, so an unfiltered scan reads every workspace and every day the metastore has kept. usage_date and usage_start_time are the columns that prune. The same applies to system.access.audit and system.query.history, which get large faster than billing does. There is also ingestion lag: the last hour or two may not be there yet, so a query for 'right now' both scans a lot and still misses the newest rows. Materialise a daily aggregate of your own, filtered and tagged, if this query runs on a schedule.

SELECT usage_date, sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date >= current_date() - 14
GROUP BY usage_date, sku_name

Senior depth: Incrementally merge the last few usage_date partitions into your own gold table. That table you can cluster, tag, and retain on your own terms, and the system table goes back to being a source rather than a dashboard.

Common mistake: Writing SELECT * FROM system.billing.usage and blaming Databricks for the scan.

Follow-up they will ask: How would you keep a daily cost mart fresh without re-scanning history?

Say this first: system.access.audit records the grant action, the principal who made it, and the object — if the audit schema was enabled before Tuesday.

Why: system.access.audit is the account's audit log as a table: logins, permission changes, share access, and job actions, with the user, the request, and the target. A GRANT shows up as an action name on the table's securable, with the identity that performed it. The catch is the same as billing: the schema must have been enabled at the time, and audit rows arrive with a delay. It is also a wide, chatty table, so filter on event_date and the action name before you scan it. For shared data, recipient reads are audited too, which is the record a file drop never gives you.

Senior depth: The identity on the audit row is the answer. A human email means someone clicked. A service principal application id means a pipeline did it, which is what you want in production and what you should alert on when it appears somewhere unexpected.

Common mistake: Expecting the audit table to reconstruct grants made before the schema was enabled.

Follow-up they will ask: How do you tell a grant made in the UI from one made by a CI service principal?

Say this first: A required team tag enforced by cluster policy and budget policy, every system schema enabled, and a owned cost mart — because none of this can be reconstructed later.

Why: Attribution is a decision you make before the spend, not a query you write after it. Cluster policies and budget policies that reject untagged compute close the UI path. Asset bundles set the tag in code so jobs cannot drift. Enabling billing, access, query, and lakeflow schemas on each metastore starts the clock, and waiting a quarter means that quarter is unauditable. A small daily job that aggregates usage by tag into a table you own survives retention limits and gives finance a stable contract. The organisational part is harder than the SQL: someone has to own the tag taxonomy, and a tag that means three different things across units is as useless as a null.

Senior depth: Put the allowed values in the cluster policy itself so an unknown team name fails creation. A wiki list and a policy that only checks the tag is non-empty will be full of typos within a month, and typos split one team's spend across spellings.

Common mistake: Leaving tagging as a convention in a wiki and planning to clean it up at year end.

Follow-up they will ask: Who is allowed to create a new tag value, and where is that list enforced?