untagged PII columns; production objects owned by a named person
system.information_schema.*, system.access.audit
Cost. Can spend be attributed to a domain and an environment at all? An unattributable bill cannot be argued about — only feared.
Why
The order is deliberate. Cost, compute and jobs are where the money is and where the fix is configuration, so the sweep can pay for the engagement before anyone argues about the data model. Storage and governance are last in the sweep and first in the risk register: neither produces a number finance cares about, and both produce the finding that ends an engagement badly if it surfaces in month five.
How
sqlIs the bill attributable? — tag taxonomy from the storage & cost route
SELECT
SUM(usage_quantity) AS dbus,
ROUND(100 * SUM(CASE WHEN custom_tags['domain'] IS NULL
THEN usage_quantity ELSE 0 END)
/ NULLIF(SUM(usage_quantity), 0), 1) AS pct_no_domain,
ROUND(100 * SUM(CASE WHEN custom_tags['environment'] IS NULL
THEN usage_quantity ELSE 0 END)
/ NULLIF(SUM(usage_quantity), 0), 1) AS pct_no_environment
FROM system.billing.usage
-- 30 complete days, ending two days back so ingestion latency cannot clip the
-- right edge. current_date() - 31 .. - 2 is 30 days inclusive.
WHERE usage_date BETWEEN current_date() - INTERVAL 31 DAYS
AND current_date() - INTERVAL 2 DAYS
-- usage_quantity is additive only WITHIN one unit. The same table also bills
-- STORAGE_SPACE in storage units, private networking in hours, plus NETWORK_BYTE,
-- API_OPERATION and TOKEN rows. Without this filter the "dbus" total is DBUs plus
-- gigabyte-months plus network hours, and since those rows carry no compute tags
-- they inflate both percentages. Drop the filter and GROUP BY usage_unit if you
-- want the other units — but never sum across them.
AND usage_unit = 'DBU';
-- No record_type filter, on purpose. A RETRACTION row carries a NEGATIVE
-- usage_quantity that cancels its original, so summing all record types NETS to
-- the corrected figure — and the retraction repeats the original custom_tags, so
-- the share nets correctly too. Filtering to ORIGINAL returns the PRE-correction
-- number, which is the restatement error this route warns about. Filter on
-- record_type only where you are COUNTING ROWS rather than summing quantities.
A double-digit pct_no_domainover DBU rows is the bad answer, and it is unfixable backwards: tags attach to compute when it starts, so untagged history stays untagged. You are measuring how much of last quarter can never be explained. Report the non-DBU units — storage, networking, predictive optimization — as their own lines rather than folding them in: they are largely untaggable by construction, and mixing them into the DBU denominator manufactures an untagged share the client cannot act on.
Compute. All-purpose clusters with auto-termination unset or generous, warehouses whose idle tail between the last query and the stop event is a large share of running time, clusters whose owner has left. A few long-lived all-purpose clusters carrying a disproportionate share of the DBUs above is usually the largest recoverable number in the sweep, and it needs no code change.
Jobs. Three bad answers, in increasing order of usefulness:
Repeat failures still consuming DBUs
Nobody reads the alerts.
Job tasks pinned to all-purpose compute
The job was built in a notebook and never promoted. Read it from system.lakeflow.job_task_run_timeline, whose compute_ids carry the compute actually used — job_tasks is an SCD2 table of task definitions and has no compute column at all.
A p50 duration drifting upward over 30 days
The one people miss: it never fails, appears in no alert, and it is what breaks the SLA next quarter.
Storage. Check system.storage.predictive_optimization_operations_history for recent OPTIMIZE and VACUUM on the gold tables, then check whether a scheduled maintenance job also exists. Neither running means small files accumulating and query times drifting up with no event to point at. Both running is rarer and worse: a hand-written OPTIMIZE competing with predictive optimization pays twice for the same work and produces conflicting commits.
Governance. Three questions, in this order: are the personal-data columns tagged at all, is dwh_prod owned by a group rather than a named individual, and does system.access.audit show anyone actually reading those columns. The first is the one that gates everything else, so it is the one to run.
sqlAre the personal-data columns on dim_customer classified?
SELECT c.column_name, t.tag_name, t.tag_value
FROM system.information_schema.columns AS c
LEFT JOIN system.information_schema.column_tags AS t
ON t.catalog_name = c.table_catalog
AND t.schema_name = c.table_schema
AND t.table_name = c.table_name
AND t.column_name = c.column_name
WHERE c.table_catalog = 'dwh_prod'
AND c.table_schema = 'gold'
AND c.table_name = 'dim_customer'
AND c.column_name IN ('email', 'phone', 'street')
ORDER BY c.column_name;
Three rows with a NULL tag_name is the bad answer, and it is bad specifically: with no tags there is nothing for a tag-driven ABAC policy to attach to, so every access decision stays a per-table grant someone has to remember to write. A production catalog owned by an individual is one leaving date away from an object nobody can alter.
Doing it
Join usage to system.billing.list_prices for a dollar figure, and label it 'at list' every time it appears
Split job DBUs by compute type before rankingAn expensive job on the right compute and a cheap one on all-purpose need opposite responses.
Pull p50 and p90 duration per job over 30 days, not only failuresThe trend is the finding; the failures are already known.
Run one access-review query over the tagged columns for the last 30 days even if the answer is nobodyThat null result is the baseline.
Embedding it
Ask them to guess the untagged percentage before you run itThe guess is always low, and the gap turns the tag taxonomy from a chore into a priority.
Have the engineer who owns the nightly job find its compute type themselves, in front of everyoneNobody builds the next job on all-purpose compute after that.
Ask who owns dwh_prod before you run the query, then run itRoom answer and catalog answer differ often enough to be worth the theatre.
Send them the classification route when they propose hashing the email column to close the findingCorrecting it in the room does not work — pseudonymisation is a rule they have to arrive at.
Defaults
Dollars at list price, labelled, unless the client hands you their contract
Rank by recoverable DBUs, not total DBUs — the biggest line is often correct spend
One maintenance mechanism per table: predictive optimization or a scheduled job, never both
PII tags derived from the data model, so classification and model are one statement
Gotchas
Summing usage_quantity without pinning usage_unitsystem.billing.usage meters storage, networking, API operations and tokens through the same column as DBUs, so the total is DBUs plus gigabyte-months plus network hours — and those rows carry no compute tags, so the untagged share you put on the first slide is an artefact of the missing filter rather than a tagging problem. You present a manufactured number and lose the area.
Looking for the wrong-compute answer in system.lakeflow.job_tasksIt is an SCD2 table of task definitions — task_key, depends_on_keys, timeout_seconds, health_rules — with no compute in it, so the join returns nothing and reads as 'no jobs on all-purpose'. Compute identity is on job_task_run_timeline.
Reading an idle warehouse as pure wastePart of that tail is a deliberate warm window; check who queries it first, or the complaint after your change is a dashboard that now takes a minute to wake up.
Chasing failure counts and ignoring duration driftA job that went from four minutes to eleven has never failed, and it is the one that pages someone at 03:00 next quarter.
Answering 'is predictive optimization on?' at the wrong level, in either directionIt is on by default for accounts created from 11.11.2024, the rollout to older accounts was due to finish in August 2026 — i.e. during this sweep — and it is settable at account, catalog, schema and table level. So the account answer is not the catalog answer and neither is the table answer, and a note written last quarter is already wrong. Read it per table on the gold tables before you either write a maintenance job or tell them theirs is redundant.
Presenting the ownership finding as a security problem in front of the person who is the ownerIt is an operational risk about leaving dates; framing it otherwise buys an enemy on the platform team in week one.