Query Cost Attribution: Tagging, Resource Monitors & Showback for Data Teams

query cost attribution is the single practice that turns a runaway warehouse bill into a per-team, per-pipeline, per-environment ledger you can defend in a finance review — and the workflow senior data engineers are increasingly expected to own end-to-end rather than lob over the wall to a FinOps team. The warehouse invoice arrives every month as one giant number; the useful number is the decomposition of that invoice by the team that ran the queries, the pipeline that spawned them, and the environment (prod, staging, dev) they targeted. Get the decomposition right and every subsequent conversation — budget cuts, pipeline optimisation, dashboard consolidation — becomes a data conversation instead of a political one. Get it wrong and the top ten queries hide under an "unknown" bucket that everyone points at and nobody owns.

This guide is the senior-DE walkthrough you wished existed the first time an interviewer asked "how does your showback cadence work?" or "what's the difference between chargeback and showback?" or "walk me through your resource monitor policy for a team that runs an unbounded backfill on a Sunday night." It works through the four axes every serious attribution pipeline resolves — tagging primitives (Snowflake QUERY_TAG, BigQuery labels, Databricks cluster tags), resource monitors and cluster policies with soft-notify and hard-suspend caps, direct-vs-shared spend allocation methodology, and the weekly data team billing digest pattern — and finishes with the nightly Airflow ETL + Slack + z-score anomaly-detector loop that keeps the whole system honest. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works. The primary keyword cost tagging gets probed relentlessly at senior interviews; the secondary probes on snowflake resource monitor and bigquery labels show up whenever a candidate claims warehouse experience.

When you want hands-on reps immediately after reading, drill the SQL practice library →, rehearse on the ETL practice library →, and sharpen the tuning axis with the optimization practice library →.

---

On this page

• Why attribution is a data-eng problem, not a finance problem
• Tagging — query tag, session tag, warehouse tag
• Resource monitors — hard + soft caps
• Allocation methodology + showback
• Automation + Slack / reporting patterns
• Cheat sheet — attribution recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why attribution is a data-eng problem, not a finance problem

Attribution lives where the queries live — the warehouse team owns the tag hygiene, the allocation SQL, and the showback cadence

The one-sentence invariant: query cost attribution is a data-engineering problem because the only place where a query, a team, a pipeline, and a dollar amount can be joined is the warehouse's own query history — and that join is a SQL problem, not an ERP problem. Finance can read a monthly invoice; only the data platform team can decompose it. In 2026 every major cloud warehouse — Snowflake, BigQuery, Databricks, Redshift — ships a first-class query-history table with cost, latency, user, warehouse, and (crucially) tag or label columns. The moment you make tagging mandatory upstream, the attribution pipeline is a handful of straightforward joins downstream.

The four axes interviewers actually probe.

• Tag. The primitive that carries team, pipeline, and env from the client all the way into the query history. Snowflake calls it QUERY_TAG, BigQuery calls them labels, Databricks calls them custom_tags. The senior signal is naming all three without prompting and knowing the cardinality trade-offs.
• Monitor. The resource monitor (Snowflake) or reservation (BigQuery) or budget policy (Databricks) that enforces a spend ceiling — soft at 80% (notify), hard at 100% (suspend). The junior mistake is asking "what happens when the cap is hit?"; the senior answer is "we have three actions — notify, notify + suspend, and notify + suspend immediate — with different blast radii."
• Allocation. How shared cost (queries the intern ran without a tag, storage that serves multiple teams, ingestion that feeds several pipelines) is divided. Direct allocation is trivial; shared allocation is a policy conversation.
• Showback. The report + cadence that puts the numbers back in front of the team that ran the queries. Showback is report only (a Slack digest, a Notion page, a Grafana dashboard); chargeback is actual billing (a chargeback to the team's ledger). Most orgs run showback; only mature FinOps orgs run chargeback.

Why "just look at the invoice" fails.

• The invoice is an aggregate. Snowflake bills you by credit-hour by warehouse; BigQuery bills you by TiB scanned by project; Databricks bills you by DBU by cluster. None of those axes match "team X ran pipeline Y in environment Z." Without tag propagation, the invoice is uninterpretable at the team level.
• The invoice arrives late. Cloud invoices arrive at the start of the next month, by which point the runaway backfill has been going for four weeks. A working attribution pipeline reports daily or better — the invoice is a reconciliation checkpoint, not an operational tool.
• The invoice doesn't split shared cost. The default warehouse (COMPUTE_WH, default, all-purpose) is shared across every team that hasn't spun up their own. Its cost is invisible on the invoice at the team level; only in-warehouse SQL over query_history × tag → team reveals who ran what.
• The invoice doesn't tell you about drift. A pipeline that scanned 200 GB last month and 3 TB this month looks like a $$100 vs $200$$ number on the invoice. In the query history, it's a 15× drift with a specific commit hash and a specific dbt model — actionable only from the warehouse side.

The 2026 reality — every warehouse ships attribution primitives.

• Snowflake. ALTER SESSION SET QUERY_TAG = 'team=fin,pipeline=elt,env=prod'; plus SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for cost columns (credits_used_cloud_services, total_elapsed_time, warehouse_size). Object tags on databases, schemas, tables let you tag storage too.
• BigQuery. SET @@query_label = 'team:fin,pipeline:elt,env:prod'; (or the labels field on jobs) plus INFORMATION_SCHEMA.JOBS_BY_PROJECT with total_bytes_billed, total_slot_ms, and the labels column. Dataset labels + table labels flow into every job that touches them.
• Databricks. Cluster custom_tags propagate to the billing system-usage table system.billing.usage; job-level tags flow into system.billing.usage.custom_tags. The usage_metadata column carries cluster_id, job_id, workspace_id.
• Redshift + others. Redshift has query_group and workload management (WLM) queues; Databricks-serverless has SKU-level tagging; Trino has X-Trino-Client-Tags.

What interviewers listen for.

• Do you name three distinct tag dimensions — team, pipeline, environment — without prompting? — senior signal.
• Do you describe the four axes (tag / monitor / allocation / showback) and explain which one your last project owned? — required framing.
• Do you push back on "finance handles cost" with the "join lives in the warehouse" argument? — required answer.
• Do you distinguish showback (report) from chargeback (bill) without conflating them? — required.

Worked example — the runaway-Sunday-backfill scenario

Detailed explanation. The classic attribution failure: a Sunday-night backfill runs on the shared COMPUTE_WH under a shared service account. It scans 40 TB, burns 800 credits, and shows up on Monday's Slack digest as a $3200 spike against the "unknown" bucket. Two teams both had backfill work over the weekend and both point at the other. Walk an interviewer through what tagging + monitor + allocation would have caught before the credit meter rolled over.

• The scenario. Weekend backfill on shared warehouse, no tag, no monitor, no per-team ceiling.
• The Monday reality. $3200 spike, no owner, finance escalates to the VP of engineering.
• The fix path. Mandatory QUERY_TAG at the session start, a per-team resource monitor with 80% notify, a nightly showback digest that surfaces trailing 24h spend by team.

Question. Given a Snowflake account with a shared COMPUTE_WH and two teams (fin, ml) that both run weekend backfills, design the tagging + monitor + allocation policy that would have caught the $3200 spike within 15 minutes rather than 36 hours.

Input.

Component	Before	After
Warehouse	Shared COMPUTE_WH	Shared COMPUTE_WH, per-team monitors on top
Tag policy	None	Session-level QUERY_TAG required by dbt macro
Monitor	None on shared WH	Per-team monitor: 400 credits/day, 80% notify, 100% suspend
Allocation	Aggregate	Direct via tag + proportional for un-tagged
Showback	Monthly invoice review	Nightly Slack digest

Code.

-- 1. Enforce QUERY_TAG at session start (dbt on-run-start macro).
ALTER SESSION SET QUERY_TAG = '{"team":"fin","pipeline":"weekly_backfill","env":"prod","run_id":"2026-07-06-abc123"}';

-- 2. Per-team resource monitor with soft + hard caps.
CREATE OR REPLACE RESOURCE MONITOR rm_fin_daily
  WITH CREDIT_QUOTA         = 400
       FREQUENCY            = DAILY
       START_TIMESTAMP      = IMMEDIATELY
  TRIGGERS ON  80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

ALTER WAREHOUSE COMPUTE_WH SET RESOURCE_MONITOR = rm_fin_daily;

-- 3. Nightly attribution ETL — collapse query_history to per-team spend.
CREATE OR REPLACE TABLE analytics.cost.team_cost_daily AS
SELECT DATE(start_time)                                              AS run_date,
       COALESCE(TRY_PARSE_JSON(query_tag):team::STRING, 'unknown')   AS team,
       COALESCE(TRY_PARSE_JSON(query_tag):pipeline::STRING, 'ad_hoc') AS pipeline,
       COALESCE(TRY_PARSE_JSON(query_tag):env::STRING, 'unknown')    AS env,
       SUM(credits_used_cloud_services)                              AS credits,
       COUNT(*)                                                      AS query_count
FROM   snowflake.account_usage.query_history
WHERE  start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP)
GROUP  BY 1, 2, 3, 4;

Step-by-step explanation.

1. The dbt on-run-start hook sets QUERY_TAG to a JSON blob carrying team, pipeline, env, and a run_id. Every query that follows in the session inherits the tag — no per-query effort, no drift. The JSON shape lets the downstream ETL parse fields with a single TRY_PARSE_JSON call.
2. The resource monitor rm_fin_daily sets a 400-credit-per-day quota specifically for the fin team. At 80% (320 credits) it notifies the owner; at 100% (400 credits) it both notifies again and suspends the warehouse. The suspend action is aggressive by design — it stops the meter but does not kill running queries mid-flight (Snowflake gives them a grace period).
3. Attaching the monitor to COMPUTE_WH gives the shared warehouse a per-team ceiling. To split the ceiling by team properly, most teams either (a) spin up per-team warehouses each with its own monitor, or (b) rely on the tag-based ETL to attribute after the fact. The example uses (a) for the enforcement side, (b) for the reporting side — they complement each other.
4. The nightly ETL parses the JSON QUERY_TAG, groups by (team, pipeline, env), and sums credits. The COALESCE fallbacks to 'unknown' guarantee every row is attributed — the "unknown" bucket becomes a tag-hygiene KPI, not a black hole.
5. Under the new policy, the Sunday-night backfill either (a) never runs untagged because the dbt macro enforces the tag, or (b) hits the 80% notify at 4 PM Sunday, giving the on-call four hours of warning before the 100% suspend fires at 8 PM. The Monday spike is impossible.

Output.

Metric	Before (no attribution)	After (attribution + monitor)
Time to identify spending team	36 hours (finance review)	15 minutes (Slack digest)
Un-tagged fraction of spend	100%	< 3% (measured drift)
Max per-team daily spend	Unbounded	400 credits (hard cap)
Sunday backfill outcome	$3200 spike, no owner	Auto-suspended at 400 credits

Rule of thumb. Attribution starts with the tag and ends with the monitor. Both are cheap; missing either one is a 3 AM incident waiting to happen. Never bill a team for cost you cannot join to their queries in SQL.

Worked example — allocating shared cost across three teams

Detailed explanation. A second classic: three teams (fin, ml, ops) all use a shared ingestion pipeline that lands data every hour. The ingestion queries do not carry a per-team tag because they serve everyone. The cost is $8000 per month. Simple direct allocation fails — the queries have no team on them. The mature answer is proportional allocation weighted by downstream usage: split the $8000 in proportion to each team's read volume against the ingested tables.

• The problem. $8000 monthly shared-ingestion cost, no per-team tag on ingestion queries.
• The naive fix. Split three ways evenly ($2667 each). Feels fair, doesn't match reality — the ml team reads 10× as much as ops.
• The mature fix. Weight by downstream read volume from the tagged consumer queries.

Question. Given a shared_ingestion cost of $8000 and per-team read volumes of fin=200 TB, ml=800 TB, ops=50 TB against the ingested tables, compute the proportional allocation and show the SQL that does it automatically every month.

Input.

Team	Read TB against shared tables
fin	200
ml	800
ops	50

Code.

-- 1. Per-team read volume against the shared ingestion tables (from tagged consumer queries).
WITH team_reads AS (
  SELECT COALESCE(TRY_PARSE_JSON(query_tag):team::STRING, 'unknown') AS team,
         SUM(bytes_scanned) / POWER(1024, 4)                          AS tb_read
  FROM   snowflake.account_usage.query_history
  WHERE  start_time >= DATE_TRUNC('month', CURRENT_DATE)
    AND  ARRAY_CONTAINS('SHARED_INGEST.EVENTS'::VARIANT, TRY_PARSE_JSON(direct_objects_accessed))
  GROUP  BY 1
),
total_reads AS (
  SELECT SUM(tb_read) AS total_tb FROM team_reads
),
shared_cost AS (
  SELECT 8000.00 AS usd
),
weighted AS (
  SELECT t.team,
         t.tb_read,
         t.tb_read / r.total_tb                    AS weight,
         t.tb_read / r.total_tb * s.usd            AS allocated_usd
  FROM   team_reads t
  CROSS  JOIN total_reads r
  CROSS  JOIN shared_cost s
)
SELECT team, ROUND(tb_read, 1) AS tb_read, ROUND(weight, 3) AS weight, ROUND(allocated_usd, 2) AS allocated_usd
FROM   weighted
ORDER  BY allocated_usd DESC;

Step-by-step explanation.

1. team_reads joins the tagged consumer queries in query_history to the shared ingestion tables via direct_objects_accessed (a Snowflake-native JSON column that lists every object touched by the query). This turns "who read the ingested data" into an SQL question with a numeric answer.
2. The bytes_scanned sum divided by 2^40 gives TB read per team for the current month. The join is exact — un-tagged reads fall into 'unknown' and are reported separately so the tag-hygiene team can chase them.
3. total_reads is a single-row CTE that sums the per-team TB. shared_cost is the $8000 monthly ingestion invoice, hard-coded here but usually pulled from a cost.invoices reconciliation table.
4. weighted computes each team's weight as team_tb / total_tb and multiplies by the shared cost to get the allocated dollars. CROSS JOIN broadcasts the single-row CTEs so every team row gets the total and the invoice.
5. The output is ordered by allocated USD descending — ml gets the biggest slice ($6095), fin gets $1524, ops gets $381. The allocation is defensible because it maps to actual read volume; the ml team owns the story on why they read 10× as much and can decide whether to optimise or accept the bill.

Output.

team	tb_read	weight	allocated_usd
ml	800.0	0.762	6095.24
fin	200.0	0.190	1523.81
ops	50.0	0.048	380.95

Rule of thumb. For shared cost, weight by the axis that most closely correlates with consumption — read TB for ingestion, active DAU for a shared UI, storage GB for shared storage. Even-split is easy but wrong; the mature answer is a defensible weighted allocation.

Worked example — the tag hygiene score-card

Detailed explanation. Attribution only works when tagging works. The most useful operational metric is the tag hygiene score — the fraction of spend attributable to a real (team, pipeline, env) triple as opposed to the 'unknown' fallback. A hygiene score above 95% is healthy; below 90% means the ETL is producing untrustworthy showback. Build the scorecard and put it on the same Slack digest as the per-team spend so tag hygiene is a first-class KPI.

• The metric. hygiene_pct = tagged_credits / total_credits, computed daily.
• The threshold. 95% healthy, 90% warning, <85% page the tag-owner rotation.
• The remediation. Trace un-tagged spend by user + warehouse to identify the offending client.

Question. Write a Snowflake query that computes the tag hygiene score for the trailing 7 days, broken down by warehouse and top-10 users, so the tag-owner rotation can chase the biggest offenders first.

Input.

Setup	Value
Query history source	snowflake.account_usage.query_history
Tag column	query_tag (JSON)
Window	Trailing 7 days
Report cadence	Daily

Code.

WITH tagged AS (
  SELECT DATE(start_time)                                          AS run_date,
         warehouse_name,
         user_name,
         credits_used_cloud_services                                AS credits,
         CASE
           WHEN query_tag IS NULL OR query_tag = '' THEN 'untagged'
           WHEN TRY_PARSE_JSON(query_tag):team IS NULL THEN 'missing_team'
           WHEN TRY_PARSE_JSON(query_tag):pipeline IS NULL THEN 'missing_pipeline'
           WHEN TRY_PARSE_JSON(query_tag):env IS NULL THEN 'missing_env'
           ELSE 'ok'
         END                                                        AS tag_status
  FROM   snowflake.account_usage.query_history
  WHERE  start_time >= DATEADD(day, -7, CURRENT_DATE)
),
by_warehouse AS (
  SELECT warehouse_name,
         SUM(CASE WHEN tag_status = 'ok' THEN credits ELSE 0 END) / NULLIF(SUM(credits), 0) AS hygiene_pct,
         SUM(credits)                                                                       AS total_credits,
         SUM(CASE WHEN tag_status != 'ok' THEN credits ELSE 0 END)                          AS untagged_credits
  FROM   tagged
  GROUP  BY warehouse_name
),
top_offenders AS (
  SELECT user_name,
         warehouse_name,
         SUM(credits)                                            AS untagged_credits,
         MODE(tag_status)                                        AS most_common_defect
  FROM   tagged
  WHERE  tag_status != 'ok'
  GROUP  BY 1, 2
  ORDER  BY untagged_credits DESC
  LIMIT  10
)
SELECT 'warehouse'         AS report_kind, warehouse_name AS entity, NULL AS user_name,
       ROUND(hygiene_pct * 100, 1) AS hygiene_pct, total_credits, untagged_credits, NULL AS defect
FROM   by_warehouse
UNION ALL
SELECT 'top_offender'      AS report_kind, warehouse_name AS entity, user_name,
       NULL, NULL, untagged_credits, most_common_defect
FROM   top_offenders;

Step-by-step explanation.

1. The tagged CTE labels every query with a tag_status from a five-value enum: untagged, missing_team, missing_pipeline, missing_env, or ok. This granularity matters — "no tag at all" and "tag missing env" require different fixes.
2. by_warehouse computes the hygiene percentage per warehouse. The formula uses NULLIF(SUM(credits), 0) to avoid divide-by-zero for a quiet warehouse. Below 95% surfaces immediately; a warehouse-level view catches the "our staging warehouse is 60% untagged" pattern before it drifts into prod.
3. top_offenders ranks the top-10 (user, warehouse) pairs by untagged credits and reports the most common defect. MODE(tag_status) returns the most-frequent failure mode — usually missing_pipeline because someone shipped a client that sets team+env but forgot pipeline.
4. The UNION ALL combines both views into a single result set the digest can render. In practice the two halves become two Slack blocks — a "hygiene by warehouse" table and a "top-10 offenders" table.
5. The remediation loop: the tag-owner rotation gets paged when any warehouse drops below 90%; they read the top-10 offenders, identify the client library or user, ship a fix, and the number returns to green within 24 hours.

Output.

report_kind	entity	user_name	hygiene_pct	total_credits	untagged_credits	defect
warehouse	COMPUTE_WH	—	97.2	1400.0	39.2	—
warehouse	ML_WH	—	88.4	900.0	104.4	—
warehouse	STAGING_WH	—	62.1	250.0	94.8	—
top_offender	STAGING_WH	data_intern	—	—	82.0	untagged
top_offender	ML_WH	ml_service_acct	—	—	61.2	missing_pipeline

Rule of thumb. Ship the hygiene score in the same digest as the spend numbers; treat below-90% as a page-worthy incident. Un-tagged spend erodes attribution faster than any other single failure mode.

Senior interview question on framing attribution as a data-engineering problem

A senior interviewer often opens with: "You inherit a Snowflake account that spends $600k / month, has no query tags, no resource monitors, and finance is asking for a per-team breakdown by end of quarter. Walk me through your 90-day plan — what you build first, what you measure, and what you push back on."

Solution Using the four-axes 90-day rollout

-- Phase 1 (day 1-14) — Tag enforcement + hygiene baseline
CREATE OR REPLACE PROCEDURE public.set_pipecode_tag(team STRING, pipeline STRING, env STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
  EXECUTE IMMEDIATE
    'ALTER SESSION SET QUERY_TAG = ''{"team":"' || team || '","pipeline":"' || pipeline || '","env":"' || env || '"}''';
  RETURN 'ok';
END;
$$;

-- dbt on-run-start hook:
--   CALL public.set_pipecode_tag('{{ var("team") }}', '{{ target.name }}', '{{ env_var("DBT_ENV") }}');

-- Phase 2 (day 15-30) — Resource monitors per team + shared
CREATE OR REPLACE RESOURCE MONITOR rm_fin_monthly
  WITH CREDIT_QUOTA = 5000 FREQUENCY = MONTHLY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY SUSPEND;

CREATE OR REPLACE RESOURCE MONITOR rm_ml_monthly
  WITH CREDIT_QUOTA = 12000 FREQUENCY = MONTHLY
  TRIGGERS ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY SUSPEND;

CREATE WAREHOUSE fin_wh WITH WAREHOUSE_SIZE = MEDIUM RESOURCE_MONITOR = rm_fin_monthly;
CREATE WAREHOUSE ml_wh  WITH WAREHOUSE_SIZE = LARGE  RESOURCE_MONITOR = rm_ml_monthly;

-- Phase 3 (day 31-60) — Nightly attribution ETL → team_cost_daily
CREATE OR REPLACE TABLE analytics.cost.team_cost_daily AS
SELECT DATE(start_time)                                          AS run_date,
       COALESCE(TRY_PARSE_JSON(query_tag):team::STRING, 'shared') AS team,
       COALESCE(TRY_PARSE_JSON(query_tag):pipeline::STRING, 'shared') AS pipeline,
       COALESCE(TRY_PARSE_JSON(query_tag):env::STRING, 'unknown') AS env,
       SUM(credits_used_cloud_services)                          AS credits,
       COUNT(*)                                                  AS query_count
FROM   snowflake.account_usage.query_history
WHERE  start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP)
GROUP  BY 1, 2, 3, 4;

-- Phase 4 (day 61-90) — Weekly showback digest to Slack

Step-by-step trace.

Phase	Days	Deliverable	KPI
1. Tag enforcement	1-14	dbt macro + Airflow op enforces QUERY_TAG	Hygiene 40% → 92%
2. Resource monitors	15-30	Per-team WH + monitor	0 → 4 monitors live
3. Nightly ETL	31-60	team_cost_daily table + Airflow DAG	Daily spend by team ± $50
4. Weekly showback	61-90	Slack digest + Grafana dashboard	1 digest/week + on-call runbook

After the 90-day rollout, the account has hygiene at 95%+, four per-team monitors with 80%/100% triggers, a nightly attribution table with sub-$50 error against the monthly invoice, and a weekly Slack digest that surfaces the top-5 teams and top-10 costly queries. Finance no longer chases the team; the team chases the number.

Output:

Metric	Day 0	Day 90
Tag hygiene	0%	95%
Resource monitors	0	4 (one per team + shared)
Attribution latency	monthly (invoice)	nightly (ETL)
Showback cadence	never	weekly (Slack)
Finance escalations	~4/month	0/month

Why this works — concept by concept:

• Tag first, monitor second, ETL third, showback fourth — the ordering is deliberate. Without tags the monitor has nothing to enforce per-team; without the ETL the monitor's notifications are point-in-time, not trend; without showback the team never sees the numbers. Skipping any step turns the whole system into decoration.
• Enforcement at the client, reporting at the warehouse — the dbt macro forces the tag at the session start (enforcement); the nightly ETL reads query_history (reporting). Two different surfaces, one shared vocabulary.
• Monitor triggers are notify → notify + suspend — the double-notify pattern (once at 80%, once at 100%) gives the team both an early warning and a hard stop. Missing the 80% notify is how a runaway becomes a 3 AM page.
• Attribution table as a shared artefact — team_cost_daily is the canonical join target. Every downstream (Grafana, Slack, chargeback CSV) reads from it, so the semantics of "team cost" only need to be defined once.
• Cost — the whole rollout is O(1) senior-engineer-quarters and returns O(monthly-invoice) in avoided over-spend. The showback digest itself costs pennies to run; the ETL cost is under 5 credits per day even on a Snowflake M-warehouse.

SQL
Topic — sql
SQL attribution and cost-decomposition problems

Practice →

Optimization Topic — optimization Optimization problems on warehouse spend tuning

Practice →

---

2. Tagging — query tag, session tag, warehouse tag

Three tag surfaces, three propagation stories — QUERY_TAG, labels, and object tags all encode team × pipeline × env

The mental model in one line: every warehouse ships (a) a session/query-level tag primitive that flows into query history, (b) an object-level tag primitive that flows into storage and lineage, and (c) a warehouse-level tag primitive that flows into compute cost — a mature attribution pipeline uses all three because none of them alone covers the whole surface. Snowflake, BigQuery, and Databricks have their own names for these three surfaces; the interview probe is knowing the mapping.

The four axes for tagging.

• Cardinality. How many distinct (team, pipeline, env) triples exist in the org? Under ~1000 is easy; ~10000 needs a controlled vocabulary; above ~100000 needs a two-tier tag (team + narrow-cardinality-id + JSON blob for the rest).
• Enforcement. Where in the stack is the tag set? Client-side (dbt macro, Airflow operator, SDK) is preferred over server-side (a warehouse policy that rejects untagged queries) because client-side sets the tag before the query runs; server-side only catches the drift after the fact.
• Propagation. Does the tag survive across a stored procedure, a task, a materialised view refresh, a share, a data-clean-room query? Snowflake object tags propagate through most surfaces; QUERY_TAG only lives inside the session.
• Retention. How long is the tag visible in query history? Snowflake ACCOUNT_USAGE.QUERY_HISTORY retains for 1 year; BigQuery INFORMATION_SCHEMA.JOBS_BY_PROJECT retains for 180 days by default. Beyond that you need your own tag warehouse.

Snowflake tagging primitives — three concentric layers.

• QUERY_TAG. A per-session string (typically a JSON blob) set via ALTER SESSION SET QUERY_TAG = '...'. Flows into SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY.query_tag. This is the primary attribution axis.
• Object tags. CREATE TAG cost_center; ALTER TABLE X SET TAG cost_center = 'fin';. Object tags flow into SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES and let you attribute storage separately from compute.
• Session parameters. ALTER USER svc_dbt SET DEFAULT_WAREHOUSE = FIN_WH; gives a user-scope default. Combined with QUERY_TAG set in the JDBC/ODBC connection string, you get per-user default tagging without touching client code.

BigQuery tagging primitives — labels + query labels.

• Job labels. Set via the labels field on a job (SDK) or via SET @@query_label = 'team:fin,pipeline:elt,env:prod';. Flows into INFORMATION_SCHEMA.JOBS_BY_PROJECT.labels.
• Dataset + table labels. bq update --set_label team:fin project:dataset — flows into the dataset metadata and cascades into every job that touches the labelled resource.
• Reservation labels. Slot reservations can carry their own labels; joins to jobs via reservation_id propagate the reservation label into per-team cost.

Databricks tagging primitives — cluster + job + workspace.

• Cluster custom_tags. Set at cluster creation; flows into system.billing.usage.custom_tags. Best for long-running interactive clusters.
• Job tags. Set at job creation (jobs.create API); flows into system.billing.usage.custom_tags along with the job_id. Best for scheduled jobs.
• Workspace-level default tags. Applied automatically to every cluster and job in a workspace — the safety-net tag for anything that forgot to set its own.

Common interview probes on tagging.

• "What's the JSON structure of your QUERY_TAG?" — expected answer names team, pipeline, env, and probably a run_id for traceability.
• "How do you enforce tagging on ad-hoc queries?" — dbt macro + Airflow op + a Grafana panel showing hygiene by user.
• "BigQuery labels vs bytes-scanned attribution — when do you use each?" — labels for team; bytes-scanned for the shared-storage weighted-allocation cross-check.
• "What breaks the QUERY_TAG propagation chain?" — Snowflake tasks that don't inherit session state, shares to external accounts, calls to COPY INTO that bypass session context.

Worked example — a JSON QUERY_TAG schema for a 500-tenant SaaS

Detailed explanation. A SaaS with 500 customer tenants wants team, pipeline, env, and tenant-id on every query. The naive schema puts all four fields into a flat JSON blob but the tenant-id explodes the cardinality — 500 tenants × 20 pipelines × 3 environments × 8 teams ≈ 240k distinct tags. The mature schema keeps the high-cardinality field (tenant-id) as a nested value that the reporting layer can pivot on, while the low-cardinality fields (team, pipeline, env) stay flat.

• The naive schema. {"team":"fin","pipeline":"elt","env":"prod","tenant":"acme"}.
• The cardinality problem. 240k distinct strings — hard to aggregate without pre-parsing.
• The mature schema. {"team":"fin","pipeline":"elt","env":"prod","ctx":{"tenant":"acme","run_id":"..."}}.

Question. Design a QUERY_TAG schema for the SaaS above, write the setter and the parser, and show the aggregation SQL that groups by team+pipeline+env while surfacing top-10 tenants inside each group.

Input.

Field	Cardinality	In flat schema?
team	8	yes
pipeline	20	yes
env	3	yes
tenant	500	no — nested in ctx
run_id	∞	no — nested in ctx

Code.

-- 1. Setter — nested-JSON QUERY_TAG.
CREATE OR REPLACE PROCEDURE public.set_tag(team STRING, pipeline STRING, env STRING, tenant STRING, run_id STRING)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  tag STRING;
BEGIN
  tag := OBJECT_CONSTRUCT(
           'team', team,
           'pipeline', pipeline,
           'env', env,
           'ctx', OBJECT_CONSTRUCT('tenant', tenant, 'run_id', run_id)
         )::STRING;
  EXECUTE IMMEDIATE 'ALTER SESSION SET QUERY_TAG = ''' || tag || '''';
  RETURN tag;
END;
$$;

-- 2. Parser — aggregation SQL.
WITH parsed AS (
  SELECT DATE(start_time)                                            AS run_date,
         TRY_PARSE_JSON(query_tag):team::STRING                       AS team,
         TRY_PARSE_JSON(query_tag):pipeline::STRING                   AS pipeline,
         TRY_PARSE_JSON(query_tag):env::STRING                        AS env,
         TRY_PARSE_JSON(query_tag):ctx.tenant::STRING                 AS tenant,
         credits_used_cloud_services                                  AS credits
  FROM   snowflake.account_usage.query_history
  WHERE  start_time >= DATE_TRUNC('day', CURRENT_TIMESTAMP)
),
grouped AS (
  SELECT run_date, team, pipeline, env, SUM(credits) AS credits
  FROM   parsed
  GROUP  BY 1, 2, 3, 4
),
top_tenants_per_group AS (
  SELECT team, pipeline, env, tenant, SUM(credits) AS tenant_credits,
         ROW_NUMBER() OVER (PARTITION BY team, pipeline, env ORDER BY SUM(credits) DESC) AS rn
  FROM   parsed
  GROUP  BY 1, 2, 3, 4
)
SELECT g.team, g.pipeline, g.env, g.credits,
       LISTAGG(t.tenant || '=' || ROUND(t.tenant_credits, 2)::STRING, ', ')
         WITHIN GROUP (ORDER BY t.tenant_credits DESC) AS top10_tenants
FROM   grouped g
LEFT   JOIN top_tenants_per_group t
  ON   g.team = t.team AND g.pipeline = t.pipeline AND g.env = t.env AND t.rn <= 10
GROUP  BY g.team, g.pipeline, g.env, g.credits
ORDER  BY g.credits DESC;

Step-by-step explanation.

1. The setter builds a nested OBJECT_CONSTRUCT where the low-cardinality fields (team, pipeline, env) live at the top level and the high-cardinality fields (tenant, run_id) live inside ctx. Snowflake's JSON parser reads either layer with the same : path syntax, so downstream consumers pay no complexity tax.
2. The parser projects each field into its own column via TRY_PARSE_JSON(query_tag):path::TYPE. TRY_PARSE_JSON returns NULL instead of failing on malformed tags — a mandatory choice, since old queries in the retention window predate the schema change and would otherwise error the whole ETL.
3. grouped gives the classic (team, pipeline, env) → credits aggregation. The result set is small — bounded by teams × pipelines × envs = 480 rows even in the worst case. Fits in a Slack digest table.
4. top_tenants_per_group computes the top-10 tenants inside each (team, pipeline, env) group using ROW_NUMBER(). Without the row-number filter, the tenant cardinality would blow the digest apart; with it, each group gets a bounded 10-item breakdown.
5. The final SELECT joins the two and uses LISTAGG to compress the top-10 tenants into one string per group. The digest shows "team=ml pipeline=training env=prod → 1450 credits (acme=420, globex=310, umbrella=200, ...)". Readable and complete.

Output.

team	pipeline	env	credits	top10_tenants
ml	training	prod	1450.0	acme=420.0, globex=310.0, umbrella=200.0, ...
fin	elt	prod	820.0	acme=180.0, contoso=130.0, ...
ops	monitoring	prod	210.0	(shared=210.0)

Rule of thumb. Keep the low-cardinality axes flat; nest the high-cardinality axes. The digest reads the flat fields; the drill-down reads the nested fields. Never mix the two layers into one flat blob.

Worked example — BigQuery labels for a multi-project org

Detailed explanation. A BigQuery org runs 12 projects (one per team) with a shared central data lake in central-lake. Every job that reads from central-lake must carry team:X labels so the central bill can be attributed back to the reader. The org uses the SET @@query_label GoogleSQL statement in dbt's on-run-start and pushes the label into every job that follows.

• The problem. Cross-project reads to central-lake have no default team label — the caller's project label doesn't help because it's central-lake regardless of caller.
• The fix. Session-level @@query_label set at the caller side; the label rides on the job into INFORMATION_SCHEMA.JOBS_BY_PROJECT and is queryable by the central-lake attribution ETL.

Question. Write the dbt hook that sets @@query_label, then write the BigQuery SQL that computes per-team bytes-billed against central-lake for the trailing 30 days.

Input.

Component	Value
Shared project	central-lake
Labelled jobs source	central-lake.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
Label key	team
Trailing window	30 days

Code.

-- 1. dbt on-run-start hook (dbt-bigquery + GoogleSQL).
SET @@query_label = CONCAT('team:', @@dataset_project_id, ',pipeline:', @@dataset_id, ',env:prod');

-- 2. Central attribution SQL — trailing 30 days by team.
WITH jobs AS (
  SELECT job_id,
         creation_time,
         total_bytes_billed / POWER(1024, 4) AS tib_billed,
         (SELECT value FROM UNNEST(labels) WHERE key = 'team')     AS team,
         (SELECT value FROM UNNEST(labels) WHERE key = 'pipeline') AS pipeline,
         (SELECT value FROM UNNEST(labels) WHERE key = 'env')      AS env
  FROM   `central-lake.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    AND  statement_type = 'SELECT'
    AND  state = 'DONE'
)
SELECT COALESCE(team, 'unknown')                              AS team,
       COALESCE(pipeline, 'ad_hoc')                           AS pipeline,
       COALESCE(env, 'unknown')                               AS env,
       ROUND(SUM(tib_billed), 3)                              AS tib_billed,
       ROUND(SUM(tib_billed) * 6.25, 2)                       AS usd_on_demand,   -- $6.25/TiB
       COUNT(*)                                               AS job_count
FROM   jobs
GROUP  BY team, pipeline, env
ORDER  BY tib_billed DESC;

Step-by-step explanation.

1. The SET @@query_label statement is scoped to the current script/session in GoogleSQL. The CONCAT builds the label from dbt macro variables so each job gets a per-team, per-pipeline label without hard-coding. env:prod is hard-coded here; a real hook reads it from an environment variable.
2. The attribution query queries INFORMATION_SCHEMA.JOBS_BY_PROJECT from the central project. This is deliberate: labels ride on the job, not the resource, so the query has to run in the project where the job was executed. For cross-project reads, the caller's project holds the job record.
3. The UNNEST(labels) subquery pattern extracts each label by key. labels is a REPEATED RECORD(key STRING, value STRING) — you can't dot into it directly. The three subqueries yield team, pipeline, env respectively.
4. total_bytes_billed / POWER(1024, 4) converts bytes to tebibytes (TiB) because BigQuery on-demand pricing is per-TiB. Multiplying by $6.25 (the US-region on-demand rate) gives dollars. Flat-rate customers substitute their slot-cost model here.
5. The output rolls up by (team, pipeline, env). The COALESCE fallbacks catch untagged jobs into an unknown bucket. In practice, the tag-hygiene KPI is 1 - unknown_tib / total_tib — anything below 95% pages the tag-owner rotation.

Output.

team	pipeline	env	tib_billed	usd_on_demand	job_count
ml	training	prod	42.500	265.63	1240
fin	elt	prod	18.200	113.75	3480
unknown	ad_hoc	unknown	3.900	24.38	82
ops	monitoring	prod	1.100	6.88	5600

Rule of thumb. BigQuery labels are the primary attribution axis; the bytes_scanned axis is the fallback for cross-checking shared storage. Enforce labels at the session start with SET @@query_label, never leave attribution to per-job label parameters that clients might skip.

Worked example — Databricks cluster tags that propagate into system.billing.usage

Detailed explanation. Databricks bills DBUs (Databricks Units) per cluster per hour. The custom_tags map on a cluster propagates into system.billing.usage.custom_tags — a system-schema table that's queryable across the whole workspace. A senior DE ships a Terraform module that creates clusters with mandatory team, pipeline, env custom tags, and a Databricks SQL attribution view that groups DBU spend by tag.

• The propagation path. cluster.custom_tags → system.billing.usage.custom_tags (Delta table).
• The attribution unit. DBU-hours per (team, pipeline, env).
• The enforcement. Terraform module + a workspace-level cluster policy that rejects clusters without the three tags.

Question. Write the Terraform module (in HCL-flavoured pseudo-code inside a code block) and the Databricks SQL attribution view.

Input.

Setup	Value
Workspace	finance-prod
Mandatory tags	team, pipeline, env
Attribution unit	DBU-hours
Source table	system.billing.usage

Code.

-- Terraform (HCL) — creates a job cluster with mandatory tags.
-- resource "databricks_cluster" "fin_elt" {
--   cluster_name  = "fin-elt-nightly"
--   spark_version = "15.4.x-scala2.12"
--   node_type_id  = "i3.xlarge"
--   num_workers   = 4
--   custom_tags = {
--     team     = "fin"
--     pipeline = "elt"
--     env      = "prod"
--   }
-- }

-- Databricks SQL — attribution view against system.billing.usage.
CREATE OR REPLACE VIEW analytics.cost.team_dbu_daily AS
SELECT DATE(usage_date)                                                AS usage_date,
       COALESCE(custom_tags['team'],     'unknown')                    AS team,
       COALESCE(custom_tags['pipeline'], 'ad_hoc')                     AS pipeline,
       COALESCE(custom_tags['env'],      'unknown')                    AS env,
       sku_name,
       SUM(usage_quantity)                                             AS dbu_hours,
       SUM(usage_quantity * list_prices.pricing.default)               AS list_usd
FROM   system.billing.usage       u
JOIN   system.billing.list_prices lp
  ON   u.cloud    = lp.cloud
  AND  u.sku_name = lp.sku_name
  AND  u.usage_date BETWEEN lp.price_start_time AND COALESCE(lp.price_end_time, CURRENT_DATE)
WHERE  u.usage_date >= CURRENT_DATE - INTERVAL 30 DAYS
GROUP  BY 1, 2, 3, 4, 5;

-- Rollup — top-5 teams by trailing-30d DBU list dollars.
SELECT team,
       ROUND(SUM(dbu_hours), 1) AS dbu_hours,
       ROUND(SUM(list_usd), 2)  AS list_usd
FROM   analytics.cost.team_dbu_daily
GROUP  BY team
ORDER  BY list_usd DESC
LIMIT  5;

Step-by-step explanation.

1. The Terraform cluster resource sets custom_tags at creation. Any job or interactive session that uses the cluster inherits the tags automatically. Cluster policies (workspace-level) enforce that the three keys are non-empty — clusters without them fail to launch.
2. system.billing.usage is Databricks' system-schema table exposing DBU consumption at row-per-(cluster, sku, day) granularity. The custom_tags column is a MAP<STRING, STRING> carrying whatever the cluster was tagged with.
3. Joining to system.billing.list_prices converts DBUs into dollars using the effective list price at the time of consumption (the BETWEEN on price_start_time / price_end_time handles rate changes). This gives list-rate dollars; committed-use discounts and enterprise agreements are applied off-line.
4. The COALESCE fallbacks funnel un-tagged rows into a shared bucket. In practice these should be near-zero if the cluster policy enforces the tags at creation.
5. The final SELECT is the digest rollup — top-5 teams by list dollars over trailing 30 days. It's the same shape as the Snowflake and BigQuery equivalents, deliberately, so the same Slack digest template renders any warehouse.

Output.

team	dbu_hours	list_usd
ml	2450.5	1470.30
fin	820.0	492.00
ops	210.0	126.00
unknown	40.5	24.30
shared	12.0	7.20

Rule of thumb. In Databricks, the enforcement layer is cluster policy + Terraform module, not runtime SQL. Get the tag on at cluster creation and the billing table takes care of the rest. Reverse-engineering tags from job history after the fact is a losing battle.

Senior interview question on tag design + propagation

A senior interviewer might ask: "Your org has Snowflake for analytics, BigQuery for machine learning, and Databricks for streaming. Design a unified tag schema that survives all three warehouses, works for the 500-tenant SaaS you already run, and produces a single canonical team_cost_daily table across them all."

Solution Using a unified (team, pipeline, env, ctx) schema with per-warehouse setters + one central ETL

-- 1. Canonical schema — same shape everywhere.
--    JSON: {"team":"fin","pipeline":"elt","env":"prod","ctx":{"tenant":"acme","run_id":"..."}}

-- 2. Snowflake setter (dbt on-run-start).
ALTER SESSION SET QUERY_TAG = OBJECT_CONSTRUCT(
  'team', '{{ var("team") }}',
  'pipeline', '{{ target.name }}',
  'env', '{{ env_var("PC_ENV") }}',
  'ctx', OBJECT_CONSTRUCT('run_id', '{{ invocation_id }}')
)::STRING;

-- 3. BigQuery setter (dbt on-run-start).
SET @@query_label = CONCAT(
  'team:', '{{ var("team") }}',
  ',pipeline:', '{{ target.name }}',
  ',env:', '{{ env_var("PC_ENV") }}'
);

-- 4. Databricks setter (cluster + job custom_tags).
--    Set at Terraform time; propagates automatically.

-- 5. Central attribution ETL — unions all three warehouses into one `team_cost_daily`.
CREATE OR REPLACE TABLE analytics.cost.team_cost_daily AS
WITH sf AS (
  SELECT DATE(start_time)                                        AS run_date,
         'snowflake'                                             AS warehouse,
         TRY_PARSE_JSON(query_tag):team::STRING                   AS team,
         TRY_PARSE_JSON(query_tag):pipeline::STRING               AS pipeline,
         TRY_PARSE_JSON(query_tag):env::STRING                    AS env,
         SUM(credits_used_cloud_services) * 3.0                   AS usd
  FROM   snowflake.account_usage.query_history
  WHERE  start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP)
  GROUP  BY 1, 2, 3, 4, 5
),
bq AS (
  SELECT DATE(creation_time)                                              AS run_date,
         'bigquery'                                                       AS warehouse,
         (SELECT value FROM UNNEST(labels) WHERE key = 'team')             AS team,
         (SELECT value FROM UNNEST(labels) WHERE key = 'pipeline')         AS pipeline,
         (SELECT value FROM UNNEST(labels) WHERE key = 'env')              AS env,
         SUM(total_bytes_billed) / POWER(1024, 4) * 6.25                  AS usd
  FROM   `central-lake.region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT`
  WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  GROUP  BY 1, 2, 3, 4, 5
),
dbx AS (
  SELECT usage_date                                                       AS run_date,
         'databricks'                                                     AS warehouse,
         custom_tags['team']                                              AS team,
         custom_tags['pipeline']                                          AS pipeline,
         custom_tags['env']                                               AS env,
         SUM(usage_quantity * list_prices.pricing.default)                AS usd
  FROM   system.billing.usage       u
  JOIN   system.billing.list_prices lp USING (cloud, sku_name)
  WHERE  usage_date = CURRENT_DATE - INTERVAL 1 DAY
  GROUP  BY 1, 2, 3, 4, 5
)
SELECT * FROM sf
UNION ALL SELECT * FROM bq
UNION ALL SELECT * FROM dbx;

Step-by-step trace.

Warehouse	Setter	Propagation table	Cost column	Unit
Snowflake	ALTER SESSION SET QUERY_TAG = OBJECT_CONSTRUCT(...)	SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY	credits_used_cloud_services	credits × $3
BigQuery	SET @@query_label = 'team:...,pipeline:...'	INFORMATION_SCHEMA.JOBS_BY_PROJECT	total_bytes_billed	TiB × $6.25
Databricks	Terraform custom_tags at cluster/job creation	system.billing.usage	usage_quantity	DBU × list_price

After the rollout, one central Airflow DAG runs the three-warehouse UNION into team_cost_daily nightly. The Slack digest reads from team_cost_daily regardless of which warehouse produced the cost. Finance sees one number per team; the DE team sees the per-warehouse breakdown when they drill in.

Output:

Surface	Result
Canonical schema	one (team, pipeline, env, ctx) shape across all three warehouses
Enforcement	dbt macro (SF, BQ) + Terraform (DBX)
Nightly ETL	1 DAG, 3 sources, 1 target team_cost_daily
Slack digest	1 template, all warehouses
Finance breakdown	per-team USD, per-warehouse split available on drill-in

Why this works — concept by concept:

• One schema across warehouses — the (team, pipeline, env, ctx) shape is portable. Every warehouse encodes it slightly differently (JSON, comma-separated, MAP), but the fields are the same. The central ETL flattens the encodings.
• Setters at the client, not the server — each warehouse has a client-side setter (dbt hook, Terraform module) rather than a server-side rewrite rule. Client-side is preferred because it sets the tag before the query runs, so cancelled/failed queries still carry the tag for post-mortem.
• One central table, warehouse-labelled rows — team_cost_daily has a warehouse column so downstream views can filter by warehouse or roll them up. The union preserves the per-warehouse breakdown without forcing the digest to run three separate queries.
• USD as the common unit — each warehouse has a different native unit (credits, TiB, DBU-hours). Converting to USD in the CTE keeps the union type-clean and lets the digest speak dollars, which is what stakeholders understand.
• Cost — 3 setters × O(warehouse) = 3 pieces of client-side glue; 1 ETL × O(daily) = pennies per day; 1 digest × O(weekly) = one Slack message. The whole architecture is O(1) in operational complexity, O(warehouses) in tagging glue.

SQL
Topic — sql
SQL tagging and JSON-extraction problems

Practice →

ETL Topic — etl ETL problems on multi-source cost unions

Practice →

---

3. Resource monitors — hard + soft caps

snowflake resource monitor, BigQuery reservations, and Databricks budget policies all share one pattern — soft-notify + hard-suspend

The mental model in one line: a production resource-monitor policy has at least two triggers per team — a soft notify at 60–80% of quota (early warning) and a hard suspend at 100% (final stop) — and never a single trigger, because a single 100% trigger gives the team zero warning before their queries start failing. The three major warehouses implement the same pattern with different vocabulary: Snowflake calls them resource monitors, BigQuery calls them reservation assignments and BI Engine quotas, Databricks calls them budget policies + cluster policies.

The four axes for resource monitors.

• Cadence. Daily, weekly, monthly — daily for volatile experimental teams, monthly for stable production teams, weekly for staging.
• Scope. Warehouse-level, account-level, tag-level. Snowflake monitors attach to warehouses; BigQuery reservations attach to projects; Databricks budget policies attach to workspaces or tag combinations.
• Trigger actions. Notify (email / webhook), suspend (stop the warehouse gracefully), suspend immediate (kill in-flight queries). The ordering matters — always notify before suspend, always suspend before suspend-immediate.
• Escalation. Who receives the notify at 60%, 80%, 100%? The pattern: team owner at 60%, team + DE-oncall at 80%, team + DE + FinOps at 100%. Prevents blindsiding a team with a suspend they never saw coming.

Snowflake resource monitors — the canonical primitive.

• Scope. Attach to one or more warehouses via ALTER WAREHOUSE X SET RESOURCE_MONITOR = rm_Y;. One warehouse can carry only one monitor; one monitor can cover many warehouses.
• Quota. CREDIT_QUOTA = N credits per FREQUENCY = DAILY | WEEKLY | MONTHLY | YEARLY | NEVER. NEVER gives a rolling total; the others reset at the boundary.
• Triggers. Up to five triggers per monitor: ON <pct> PERCENT DO NOTIFY | SUSPEND | SUSPEND_IMMEDIATE. Multiple triggers can share the same percentage (e.g. NOTIFY + SUSPEND at 100%).
• Actions. NOTIFY fires an email to the account admins (subscribable via ALTER USER ... SET NOTIFY_USER = TRUE). SUSPEND prevents new queries from starting; running queries finish. SUSPEND_IMMEDIATE cancels running queries too.

BigQuery reservations + slot assignments — the analogue.

• Reservation. Fixed slot capacity (e.g. 500 slots) purchased for a project or org.
• Assignment. A (reservation, project, job_type) triple that pins a project's QUERY jobs to a specific reservation. Reservations without an assignment behave as spare capacity.
• Idle slots. By default reservations can share idle slots; setting ignore_idle_slots = true isolates a reservation from the shared pool.
• BI Engine quotas. Separate GB-hour quotas for cached BI queries.

Databricks budget policies + cluster policies — the two-layer story.

• Budget policy. A workspace-level dollar budget per (team, tag combination). Alerts + optional prevention (block new jobs).
• Cluster policy. A schema over spark_conf, custom_tags, node_type_id, num_workers. Enforces cluster size caps + mandatory tag keys.
• Serverless SKU caps. For serverless SQL warehouses, DBU caps + auto-stop policies bound spend at the warehouse level.

Common interview probes on resource monitors.

• "What's the difference between SUSPEND and SUSPEND_IMMEDIATE?" — the first lets running queries finish; the second kills them mid-flight.
• "What cadence do you use for a research team vs a production ELT?" — daily monitor for research (volatile), monthly for ELT (predictable).
• "Who gets the 80% notify?" — team owner + DE-oncall, not FinOps yet; FinOps enters at 100%.
• "Can one monitor cover multiple warehouses?" — yes, Snowflake supports many-to-one; BigQuery reservations are similar with assignments.

Worked example — monthly team budget with tiered notify + suspend

Detailed explanation. The fin team has a monthly credit budget of 5000. The policy: notify at 60% (owner), notify at 80% (owner + DE-oncall), notify at 100% (owner + DE + FinOps), suspend at 100%. This gives the team two soft-warning points before the hard stop, so the suspend at 100% is never a surprise.

• The quota. 5000 credits/month.
• The triggers. 60% notify, 80% notify, 100% notify + suspend.
• The escalation. Owner → owner+DE → owner+DE+FinOps.

Question. Write the Snowflake DDL that creates this monitor, attach it to the FIN_WH warehouse, and add the subscription commands that route the notifications to the right people.

Input.

Parameter	Value
Monthly quota	5000 credits
Frequency	Monthly
Warehouse	FIN_WH
Triggers	60% notify, 80% notify, 100% notify + suspend

Code.

-- 1. Create the resource monitor.
CREATE OR REPLACE RESOURCE MONITOR rm_fin_monthly
  WITH CREDIT_QUOTA         = 5000
       FREQUENCY            = MONTHLY
       START_TIMESTAMP      = IMMEDIATELY
  TRIGGERS ON  60 PERCENT DO NOTIFY
           ON  80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

-- 2. Attach the monitor to the warehouse.
ALTER WAREHOUSE fin_wh SET RESOURCE_MONITOR = rm_fin_monthly;

-- 3. Subscribe the notification recipients.
--    Snowflake notifications go to users with NOTIFY_USER = TRUE
--    who have the ACCOUNTADMIN role AND are subscribed to the monitor.
ALTER USER fin_owner        SET EMAIL = '[e-mail]';
ALTER USER fin_owner        SET NOTIFY_USER = TRUE;

ALTER USER de_oncall_rota   SET EMAIL = '[e-mail]';
ALTER USER de_oncall_rota   SET NOTIFY_USER = TRUE;

ALTER USER finops_pager     SET EMAIL = '[e-mail]';
ALTER USER finops_pager     SET NOTIFY_USER = TRUE;

-- 4. Verify.
SHOW RESOURCE MONITORS LIKE 'rm_fin_monthly';

Step-by-step explanation.

1. CREATE OR REPLACE RESOURCE MONITOR builds the monitor with CREDIT_QUOTA = 5000 and FREQUENCY = MONTHLY. The START_TIMESTAMP = IMMEDIATELY says "start now, not at the next calendar month" — pick this for the first rollout, then SCHEDULED from the next month onwards.
2. The four TRIGGERS fire in order: 60% notify (owner heads-up), 80% notify (add DE-oncall), 100% notify (add FinOps), 100% suspend (stop new queries). Splitting the 100% action into two lines is deliberate — the notify line runs, then the suspend line runs; you can leave one out for a soft-only monitor.
3. ALTER WAREHOUSE fin_wh SET RESOURCE_MONITOR = rm_fin_monthly binds the warehouse to the monitor. Only ACCOUNTADMIN can execute this; delegate carefully. One warehouse can have exactly one monitor; add another monitor at the account level for the account-wide safety net.
4. Subscribing the notification path uses NOTIFY_USER = TRUE on each recipient's Snowflake user. This is Snowflake-native email; production teams usually pair it with a Webhook (via Snowflake alert integrations) that pings PagerDuty or Slack.
5. SHOW RESOURCE MONITORS LIKE 'rm_fin_monthly' returns the current credit usage against the quota — useful for the digest ETL to fold monitor state into the same table as spend.

Output.

Trigger	Threshold	Action	Recipient set
T1	60% (3000 credits)	NOTIFY	fin_owner
T2	80% (4000 credits)	NOTIFY	fin_owner, de_oncall_rota
T3	100% (5000 credits)	NOTIFY	fin_owner, de_oncall_rota, finops_pager
T4	100% (5000 credits)	SUSPEND	(warehouse stops new queries)

Rule of thumb. Always ship at least two notify triggers before the suspend. The 60% + 80% + 100% pattern is the industry default for a reason — it gives teams two escalating warnings before their queries stop.

Worked example — BigQuery reservation with per-team assignments

Detailed explanation. A BigQuery org buys a 1000-slot reservation. Two teams (ml, fin) share it — ml gets 700 slots baseline, fin gets 300 slots baseline, with idle-slot sharing so bursty workloads can borrow. The team assignments enforce the baseline share; idle-slot sharing gives elasticity when the other team is quiet.

• The reservation. 1000 slots.
• The teams. ml (baseline 700) + fin (baseline 300).
• The elasticity. Idle slots shareable across teams.

Question. Write the bq CLI commands (as SQL-style block for clarity) that create the reservation, split it into two sub-reservations, and assign each project to its team's sub-reservation.

Input.

Component	Value
Total slots	1000
ml sub-reservation	700 slots (project ml-analytics)
fin sub-reservation	300 slots (project fin-analytics)
Region	us

Code.

-- 1. Create the top-level reservation (1000 slots).
--    bq mk --project_id=finance-reservations --reservation \
--          --location=US --slots=1000 --slot_capacity=1000 main

-- 2. Split into two sub-reservations.
--    bq mk --project_id=finance-reservations --reservation \
--          --location=US --slots=700 --parent=projects/finance-reservations/locations/US/reservations/main ml-team
--    bq mk --project_id=finance-reservations --reservation \
--          --location=US --slots=300 --parent=projects/finance-reservations/locations/US/reservations/main fin-team

-- 3. Assign projects to sub-reservations.
--    bq mk --reservation_assignment \
--          --project_id=finance-reservations --location=US \
--          --reservation_id=ml-team \
--          --assignee_type=PROJECT --assignee_id=ml-analytics \
--          --job_type=QUERY
--
--    bq mk --reservation_assignment \
--          --project_id=finance-reservations --location=US \
--          --reservation_id=fin-team \
--          --assignee_type=PROJECT --assignee_id=fin-analytics \
--          --job_type=QUERY

-- 4. Attribute per-team consumption via reservation_id.
WITH per_res AS (
  SELECT reservation_id,
         SUM(total_slot_ms) / 3600000.0 AS slot_hours,
         COUNT(*)                        AS job_count
  FROM   `finance-reservations.region-us.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION`
  WHERE  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  GROUP  BY reservation_id
)
SELECT CASE
         WHEN reservation_id LIKE '%/ml-team'  THEN 'ml'
         WHEN reservation_id LIKE '%/fin-team' THEN 'fin'
         ELSE 'unassigned'
       END                                    AS team,
       ROUND(slot_hours, 1)                   AS slot_hours,
       job_count
FROM   per_res
ORDER  BY slot_hours DESC;

Step-by-step explanation.

1. The top-level main reservation (--slots=1000) is the parent capacity. All sub-reservations draw from this pool. This is the fixed capacity the org pays for; slot-hours consumed outside the reservation fall back to on-demand pricing.
2. Two sub-reservations (ml-team, fin-team) carve 700 + 300 out of the parent. Each carries its own idle-slot-sharing policy — with sharing on (default), a quiet fin-team gives its idle slots to a busy ml-team; with sharing off, each team gets exactly its baseline.
3. Reservation assignments pin each project to a sub-reservation. ml-analytics runs its QUERY jobs against ml-team; fin-analytics runs against fin-team. Different job types (PIPELINE, ML_EXTERNAL_MODEL_CREATION) can be assigned separately.
4. The attribution SQL reads INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION (org-scoped) and groups by reservation_id. The CASE maps the fully-qualified reservation resource name back to a friendly team label.
5. The team column is derived from the reservation, not the labels — so this SQL works even if the client forgot to set @@query_label. It's the belt-and-braces attribution pattern: labels first, reservation second, project third.

Output.

team	slot_hours	job_count
ml	4200.5	8200
fin	1180.0	12400
unassigned	40.0	320

Rule of thumb. BigQuery reservations are how you enforce the team split; labels are how you report the team split. Ship both — the reservation is the hard cap; the label is the observability.

Worked example — Databricks cluster policy that enforces mandatory tags

Detailed explanation. A Databricks workspace ships a cluster policy that (a) enforces mandatory team, pipeline, env custom tags, (b) caps num_workers to bound cost, and (c) forces auto-termination on interactive clusters. Any cluster that violates the policy fails to launch — this is the enforcement layer that pairs with the reporting layer from section 2.

• Enforcement layer. Cluster policy JSON.
• What it enforces. Mandatory tag keys, max worker count, auto-terminate.
• The pairing. Cluster policy stops mis-tagged clusters at creation; system.billing.usage reports the tagged spend downstream.

Question. Write the Databricks cluster policy JSON and a SQL query against system.billing.usage that flags any spend from clusters missing a mandatory tag (to catch policy drift on older clusters that predate the policy).

Input.

Setup	Value
Workspace	finance-prod
Mandatory tag keys	team, pipeline, env
Max workers	8
Auto-terminate	60 minutes

Code.

-- 1. Cluster policy JSON — attach to workspace.
-- {
--   "custom_tags.team":     {"type": "regex", "pattern": "^(fin|ml|ops)$"},
--   "custom_tags.pipeline": {"type": "regex", "pattern": "^[a-z0-9_-]+$"},
--   "custom_tags.env":      {"type": "regex", "pattern": "^(prod|staging|dev)$"},
--   "num_workers":          {"type": "range", "maxValue": 8},
--   "autotermination_minutes": {"type": "range", "maxValue": 60, "defaultValue": 30},
--   "spark_conf.spark.databricks.cluster.profile": {"type": "fixed", "value": "singleNode", "hidden": false}
-- }

-- 2. Drift-detection SQL — surface spend where required tags are missing.
WITH tagged AS (
  SELECT usage_date,
         cluster_id,
         custom_tags['team']     AS team,
         custom_tags['pipeline'] AS pipeline,
         custom_tags['env']      AS env,
         SUM(usage_quantity)     AS dbu
  FROM   system.billing.usage
  WHERE  usage_date >= CURRENT_DATE - INTERVAL 7 DAYS
  GROUP  BY 1, 2, 3, 4, 5
)
SELECT usage_date,
       cluster_id,
       team, pipeline, env,
       ROUND(dbu, 2) AS dbu,
       CASE
         WHEN team     IS NULL THEN 'missing_team'
         WHEN pipeline IS NULL THEN 'missing_pipeline'
         WHEN env      IS NULL THEN 'missing_env'
         WHEN team NOT IN ('fin','ml','ops') THEN 'invalid_team'
         WHEN env  NOT IN ('prod','staging','dev') THEN 'invalid_env'
         ELSE 'ok'
       END AS defect
FROM   tagged
WHERE  team     IS NULL
   OR  pipeline IS NULL
   OR  env      IS NULL
   OR  team NOT IN ('fin','ml','ops')
   OR  env  NOT IN ('prod','staging','dev')
ORDER  BY dbu DESC
LIMIT  20;

Step-by-step explanation.

1. The cluster policy JSON uses Databricks' policy-schema language: regex types enforce a value pattern (team ∈ {fin, ml, ops}), range types enforce numeric bounds (max workers = 8), and fixed types nail a value. When someone tries to create a cluster that violates any rule, the create API returns an error.
2. The policy rejects new mis-tagged clusters. It does not retroactively fix older clusters. The drift-detection SQL is the belt-and-braces check for the historical tail.
3. The tagged CTE flattens custom_tags into three columns per row + a DBU sum per (day, cluster). Groups by cluster so a repeat-offender is one row, not one per query.
4. The final SELECT filters to only defective rows and adds a defect label. missing_team beats invalid_team in the enum ordering because a null value is a stronger signal (client didn't try) than a wrong value (client tried, got it wrong).
5. The LIMIT 20 bounds the digest; larger orgs bump this or send the full list to a Notion table. The DBU column lets the tag-owner rotation chase the most expensive offenders first — a Pareto approach that hits 80% of drift with 20% of the work.

Output.

usage_date	cluster_id	team	pipeline	env	dbu	defect
2026-07-04	0621-legacy-1	NULL	NULL	NULL	84.20	missing_team
2026-07-05	0621-legacy-2	ml	NULL	prod	42.10	missing_pipeline
2026-07-06	0705-adhoc-3	consulting	analytics	prod	18.50	invalid_team

Rule of thumb. Cluster policies enforce forward; drift-detection SQL cleans up backward. Ship both. The policy alone leaves the historical tail un-tagged; the SQL alone lets bad clusters keep launching.

Senior interview question on resource-monitor policy design

A senior interviewer might ask: "Design a resource-monitor policy for a Snowflake account with three teams (fin at $10k/month baseline, ml at $25k/month baseline, ops at $3k/month baseline) plus a shared COMPUTE_WH that everyone falls back to. Walk me through the monitor topology, the trigger thresholds, and how you handle a temporary quota bump for a quarterly-close backfill."

Solution Using per-team monitors + a shared account-level safety-net monitor + a temporary override procedure

-- 1. Per-team monthly monitors (assuming $3 per credit list price).
CREATE OR REPLACE RESOURCE MONITOR rm_fin_monthly
  WITH CREDIT_QUOTA = 3333 FREQUENCY = MONTHLY   -- $10000 / $3
  TRIGGERS ON 60 PERCENT DO NOTIFY
           ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

CREATE OR REPLACE RESOURCE MONITOR rm_ml_monthly
  WITH CREDIT_QUOTA = 8333 FREQUENCY = MONTHLY   -- $25000 / $3
  TRIGGERS ON 60 PERCENT DO NOTIFY
           ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

CREATE OR REPLACE RESOURCE MONITOR rm_ops_monthly
  WITH CREDIT_QUOTA = 1000 FREQUENCY = MONTHLY   -- $3000 / $3
  TRIGGERS ON 60 PERCENT DO NOTIFY
           ON 80 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY
           ON 100 PERCENT DO SUSPEND;

-- 2. Account-level safety net (catches un-attached warehouses).
CREATE OR REPLACE RESOURCE MONITOR rm_account_safety
  WITH CREDIT_QUOTA = 25000 FREQUENCY = MONTHLY  -- $75000 / $3
  TRIGGERS ON 90 PERCENT DO NOTIFY
           ON 100 PERCENT DO NOTIFY;             -- notify only; never auto-suspend the whole account

ALTER ACCOUNT SET RESOURCE_MONITOR = rm_account_safety;

-- 3. Attach team monitors to team warehouses.
ALTER WAREHOUSE fin_wh SET RESOURCE_MONITOR = rm_fin_monthly;
ALTER WAREHOUSE ml_wh  SET RESOURCE_MONITOR = rm_ml_monthly;
ALTER WAREHOUSE ops_wh SET RESOURCE_MONITOR = rm_ops_monthly;

-- 4. Temporary quota-bump procedure.
CREATE OR REPLACE PROCEDURE public.temp_quota_bump(monitor STRING, extra_credits NUMBER, expires_at TIMESTAMP)
RETURNS STRING
LANGUAGE SQL
AS
$$
DECLARE
  new_quota NUMBER;
BEGIN
  SELECT credit_quota + :extra_credits
  INTO   new_quota
  FROM   snowflake.account_usage.resource_monitors
  WHERE  name = :monitor;

  EXECUTE IMMEDIATE 'ALTER RESOURCE MONITOR ' || :monitor
                 || ' SET CREDIT_QUOTA = ' || :new_quota;

  INSERT INTO analytics.cost.quota_overrides (monitor, extra, expires_at, granted_by, granted_at)
  VALUES (:monitor, :extra_credits, :expires_at, CURRENT_USER(), CURRENT_TIMESTAMP);

  RETURN 'quota bumped to ' || :new_quota;
END;
$$;

-- Usage: CALL public.temp_quota_bump('rm_fin_monthly', 2000, DATEADD(day, 7, CURRENT_DATE));

Step-by-step trace.

Layer	Monitor	Quota (credits/mo)	Suspend?	Purpose
Team	rm_fin_monthly	3333 (≈$10k)	yes	Hard cap for fin
Team	rm_ml_monthly	8333 (≈$25k)	yes	Hard cap for ml
Team	rm_ops_monthly	1000 (≈$3k)	yes	Hard cap for ops
Account	rm_account_safety	25000 (≈$75k)	no (notify only)	Catches un-attached warehouses
Override	temp_quota_bump SP	+N credits, expires	—	Quarterly-close bumps

After the rollout, each team has its own monitor with the same 60/80/100 trigger ladder; the account has a notify-only safety net at 90/100% that catches new warehouses spun up without an explicit monitor attachment; a stored procedure records every temporary quota bump in an audit table (quota_overrides) so FinOps can trace who bumped what and when.

Output:

Metric	Result
Number of monitors	3 team + 1 account = 4
Suspend action scope	per-team warehouse only; never account-wide
Quarterly-close override	audited stored proc; auto-expiry via digest
Time to detect a runaway team	< 15 min (60% notify)
Blast radius on suspend	one team's warehouse; other teams unaffected

Why this works — concept by concept:

• Team monitors, not account monitors, for the suspend action — suspending the account kills everyone. Suspending one team's warehouse kills only the offender. Always scope the suspend action to the smallest unit that matches the accountability boundary.
• Account safety net is notify-only — a rogue warehouse spun up outside the team pattern still shows up in the account monitor; the notify catches it fast, but the account-level suspend would take out everyone, so we never suspend at the account.
• 60/80/100 trigger ladder — three notifies before suspend gives the team two soft warnings + a final alert before their queries stop. Missing any tier is how the "we didn't know we were going over" incident happens.
• Audited override procedure — temporary bumps get logged with (who, what, when, expires). FinOps can reconcile against the invoice and see every bump; nobody can quietly raise the quota without leaving a trail.
• Cost — 4 monitors × O(1) config; 1 stored procedure × O(1) glue. Runtime cost of enforcement is zero — monitors run inside Snowflake's control plane, not against warehouse credits. The audit table is tens of rows per year.

SQL
Topic — sql
SQL quota, threshold, and monitor problems

Practice →

Optimization Topic — optimization Optimization problems on spend-cap tuning

Practice →

---

4. Allocation methodology + showback

Direct where tagged, proportional where shared — spend allocation is a policy choice, not a technical one

The mental model in one line: allocation splits into two branches — direct (queries with a per-team tag go straight to that team) and shared (queries without a per-team tag get split across teams by a weighting rule) — and the interesting engineering conversation is entirely about the shared branch and the weighting rule, because direct is trivial. The rule can be uniform (equal split), proportional (by downstream usage), or storage-weighted (by TB stored); each is defensible in a different context.

The four axes for allocation.

• Direct vs shared. Direct is queries with a team tag → straight to that team. Shared is queries without → split by rule. The mature attribution report shows both columns so the team can see how much of their bill is direct vs shared.
• Weighting rule. Uniform (1/N per team), proportional (by read TB, active DAU, storage GB), storage-weighted (by TB stored). Uniform is politically fair but wrong; proportional is defensible but requires a proxy metric; storage-weighted is best for shared storage cost specifically.
• Cadence. Weekly for volatile ops teams, monthly for stable production teams; some orgs run daily digests but monthly reconciliation against the invoice.
• Showback vs chargeback. Showback = report only (Slack digest, Notion page, Grafana dashboard). Chargeback = actual cross-team billing (a general ledger entry that debits the team's budget). Most orgs run showback; the transition to chargeback is a governance decision.

Direct allocation — the trivial branch.

• Rule. team_direct_cost = SUM(credits WHERE tag.team = 'X').
• Applies to. Every tagged query the team actually ran.
• Reporting. Rolls up in team_cost_daily as the first column.

Shared allocation — the interesting branch.

• Rule 1: Uniform. each_team_share = shared_cost / N_teams. Best when the shared cost genuinely serves everyone equally (e.g. a shared metadata catalog).
• Rule 2: Proportional by downstream usage. team_share = (team_downstream_usage / total_downstream_usage) × shared_cost. Best when a shared ingestion pipeline feeds multiple teams' downstream queries.
• Rule 3: Storage-weighted. team_share = (team_storage_gb / total_storage_gb) × shared_storage_cost. Best when the shared cost is storage rather than compute.
• Rule 4: Headcount-weighted. team_share = (team_headcount / total_headcount) × shared_cost. Best for shared tooling cost (BI licenses, etc.) — not really warehouse cost.

Showback cadence — the four common patterns.

• Daily digest. Slack message with trailing-24h spend by team + top-10 queries. High signal for volatile ops teams; noise for stable teams.
• Weekly digest. Slack + email combined; shows week-on-week delta + z-score anomaly flags. The industry standard for mature attribution pipelines.
• Monthly review. Formal doc (Notion / Google Doc) with per-team spend, budget vs actual, top-10 optimisations. Aligned with the invoice reconciliation.
• Quarterly business review. Executive summary; shown to the CFO / CTO. Uses the trailing three months of weekly digests as evidence.

Common interview probes on allocation + showback.

• "How do you allocate shared cost?" — expect a proportional-by-downstream-usage answer with a specific proxy metric.
• "Showback vs chargeback — when do you switch?" — chargeback needs governance sign-off; most orgs stop at showback.
• "What cadence do you show back at?" — weekly is the default; daily is for volatile teams; monthly is the reconciliation.
• "How do you reconcile against the invoice?" — a monthly SQL diff between the ETL-summed spend and the invoice line items, with a target of ≤ 3% drift.

Worked example — the direct + shared split for a shared warehouse

Detailed explanation. A single warehouse (COMPUTE_WH) serves both direct-tagged team queries (~$18k of the $25k monthly bill) and un-tagged shared queries (~$7k). The shared $7k needs a weighting rule. Downstream-usage-proportional makes sense because the un-tagged queries are shared ingestion feeds — each team reads from them proportionally to their own workload.

• The bill. $25k monthly on COMPUTE_WH.
• The direct. $18k tagged to specific teams.
• The shared. $7k un-tagged shared ingestion.
• The weighting. By each team's downstream read TB from the shared tables.

Question. Write the SQL that computes each team's total cost (direct + shared) with a downstream-usage-weighted allocation of the shared portion.

Input.

Team	Direct $	Downstream read TB (from shared tables)
fin	4000	100
ml	12000	500
ops	2000	50

Code.

WITH direct AS (
  SELECT team, SUM(usd) AS direct_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND  team != 'shared'
  GROUP  BY team
),
downstream AS (
  SELECT team, SUM(tb_read) AS tb_read
  FROM   analytics.cost.shared_read_daily          -- pre-computed downstream reads per team
  WHERE  run_date >= DATE_TRUNC('month', CURRENT_DATE)
  GROUP  BY team
),
shared_pool AS (
  SELECT SUM(usd) AS shared_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND  team = 'shared'
),
total_read AS (
  SELECT SUM(tb_read) AS total_tb FROM downstream
),
allocated AS (
  SELECT d.team,
         d.direct_usd,
         (dw.tb_read / t.total_tb) * s.shared_usd  AS shared_alloc_usd
  FROM   direct     d
  JOIN   downstream dw USING (team)
  CROSS  JOIN total_read  t
  CROSS  JOIN shared_pool s
)
SELECT team,
       ROUND(direct_usd, 2)                          AS direct_usd,
       ROUND(shared_alloc_usd, 2)                    AS shared_alloc_usd,
       ROUND(direct_usd + shared_alloc_usd, 2)       AS total_usd,
       ROUND(shared_alloc_usd / (direct_usd + shared_alloc_usd), 3) AS shared_pct
FROM   allocated
ORDER  BY total_usd DESC;

Step-by-step explanation.

1. direct sums the tagged spend per team for the current month, excluding the shared bucket. This is the trivial branch — each row's team column comes straight from the tag.
2. downstream sums each team's read TB against the shared ingestion tables from a pre-computed daily table shared_read_daily. The pre-computation runs in the nightly ETL because computing bytes_scanned per (team, table-pattern) across the full query history is expensive.
3. shared_pool is a single-row CTE — the total shared spend to allocate. total_read is the total downstream read TB across all teams. The two provide the numerator/denominator for the proportional rule.
4. allocated computes each team's shared allocation as (team_tb / total_tb) × shared_usd. CROSS JOIN broadcasts the single-row CTEs; because there's only one row on each side, the cross product stays small.
5. The final SELECT adds direct + shared_alloc for the total and reports shared_pct as a sanity check. A team with shared_pct > 40% probably has a mis-tagged pipeline that's leaking into shared — a follow-up to chase in the next tag-hygiene pass.

Output.

team	direct_usd	shared_alloc_usd	total_usd	shared_pct
ml	12000.00	5384.62	17384.62	0.310
fin	4000.00	1076.92	5076.92	0.212
ops	2000.00	538.46	2538.46	0.212

Rule of thumb. Report direct + shared as separate columns; never fold them into one number. The team needs to see both — the direct portion is under their control; the shared portion is a policy question.

Worked example — weekly Slack showback digest

Detailed explanation. The team ships a weekly Slack digest every Monday at 9am. It has three sections: top-5 teams by trailing-7-day spend, top-10 costly queries, and tag-hygiene score. The digest is generated by an Airflow DAG that queries team_cost_daily and posts to a Slack webhook. This is the showback leg — report only; no ledger entry.

• The cadence. Every Monday 9am.
• The sections. Top-5 teams, top-10 queries, hygiene score.
• The medium. Slack webhook (Incoming Webhook or Slack App).

Question. Write the SQL that produces the three sections' data, and the Python that posts them to Slack as a formatted message.

Input.

Setup	Value
Attribution table	analytics.cost.team_cost_daily
Query history	snowflake.account_usage.query_history
Slack webhook	SLACK_WEBHOOK_URL env var
Cadence	Weekly, Monday 09:00

Code.

-- Section 1 — top-5 teams by trailing 7-day spend.
WITH team_7d AS (
  SELECT team, SUM(usd) AS usd_7d
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date >= CURRENT_DATE - 7
  GROUP  BY team
),
team_7d_prev AS (
  SELECT team, SUM(usd) AS usd_prev_7d
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date BETWEEN CURRENT_DATE - 14 AND CURRENT_DATE - 8
  GROUP  BY team
)
SELECT t.team,
       ROUND(t.usd_7d, 2)                                                AS usd_7d,
       ROUND(p.usd_prev_7d, 2)                                           AS usd_prev_7d,
       ROUND((t.usd_7d - p.usd_prev_7d) / NULLIF(p.usd_prev_7d, 0), 3)   AS wow_delta
FROM   team_7d t
LEFT   JOIN team_7d_prev p USING (team)
ORDER  BY usd_7d DESC
LIMIT  5;

-- Section 2 — top-10 costly queries in the trailing 7 days.
SELECT LEFT(query_text, 200)                                    AS query_preview,
       user_name,
       warehouse_name,
       ROUND(credits_used_cloud_services * 3, 2)                AS usd,
       total_elapsed_time / 1000                                AS elapsed_seconds,
       TRY_PARSE_JSON(query_tag):team::STRING                    AS team
FROM   snowflake.account_usage.query_history
WHERE  start_time >= DATEADD(day, -7, CURRENT_TIMESTAMP)
ORDER  BY credits_used_cloud_services DESC
LIMIT  10;

-- Section 3 — tag hygiene score.
SELECT ROUND(SUM(CASE WHEN team IS NOT NULL AND team != 'unknown' THEN usd ELSE 0 END)
             / NULLIF(SUM(usd), 0) * 100, 1) AS hygiene_pct,
       ROUND(SUM(usd), 2)                     AS total_usd,
       ROUND(SUM(CASE WHEN team IS NULL OR team = 'unknown' THEN usd ELSE 0 END), 2) AS untagged_usd
FROM   analytics.cost.team_cost_daily
WHERE  run_date >= CURRENT_DATE - 7;

# Airflow op — post the digest to Slack.
import os, json, requests
from snowflake.connector import connect

def build_digest():
    conn = connect(...)
    teams   = conn.cursor().execute(TOP_TEAMS_SQL).fetchall()
    queries = conn.cursor().execute(TOP_QUERIES_SQL).fetchall()
    hygiene = conn.cursor().execute(HYGIENE_SQL).fetchone()

    blocks = []
    blocks.append({"type": "header", "text": {"type": "plain_text", "text": ":moneybag: Weekly cost showback"}})
    blocks.append({"type": "section", "text": {"type": "mrkdwn",
        "text": f"*Hygiene:* {hygiene[0]}% tagged  |  *Total:* ${hygiene[1]:,}  |  *Untagged:* ${hygiene[2]:,}"}})

    lines = ["*Top 5 teams (7d)*"]
    for team, usd, prev, delta in teams:
        arrow = "↑" if (delta or 0) > 0 else "↓"
        lines.append(f"• *{team}*: ${usd:,}  ({arrow} {(delta or 0)*100:+.1f}% WoW)")
    blocks.append({"type": "section", "text": {"type": "mrkdwn", "text": "\n".join(lines)}})

    lines = ["*Top 10 costly queries (7d)*"]
    for q in queries:
        lines.append(f"• *${q[3]:,}* — `{q[1]}` on `{q[2]}` — team=`{q[5] or 'unknown'}`")
    blocks.append({"type": "section", "text": {"type": "mrkdwn", "text": "\n".join(lines)}})

    return {"blocks": blocks}

def post_to_slack(payload):
    requests.post(os.environ["SLACK_WEBHOOK_URL"], json=payload).raise_for_status()

post_to_slack(build_digest())

Step-by-step explanation.

1. Section 1's SQL uses two CTEs to compute this-week vs previous-week spend per team, then joins them and computes the week-on-week delta. NULLIF guards against a divide-by-zero on a new team. LIMIT 5 bounds the digest.
2. Section 2 lists the top-10 costliest queries in the trailing 7 days. The query preview is truncated to 200 chars so the Slack message stays readable; teams can click into a Snowsight link (added in production) for the full text.
3. Section 3's hygiene SQL divides tagged spend by total spend to get a percentage. The untagged column names the drift dollar amount — a concrete number that motivates the tag-owner rotation.
4. The Python Airflow op runs each SQL, builds a Slack Blocks-formatted payload (three sections: hygiene → teams → queries), and POSTs to the webhook. The Blocks format renders as a rich message with headers and bullets rather than a wall of text.
5. Scheduling the DAG with a Monday-9am cron gives the digest at the start of the week when team leads are catching up. Adjust to Sunday-evening for teams that plan on Monday morning.

Output (Slack rendering, approximated).

:moneybag: Weekly cost showback
Hygiene: 96.2% tagged  |  Total: $18,420  |  Untagged: $701

Top 5 teams (7d)
• ml: $9,800  (↑ +12.5% WoW)
• fin: $4,200  (↓ -3.1% WoW)
• ops: $1,300  (↑ +0.8% WoW)
• platform: $1,120  (↑ +2.4% WoW)
• data-ci: $700  (↑ +8.6% WoW)

Top 10 costly queries (7d)
• $420 — ml_service_acct on ML_WH — team=ml
• $310 — fin_analyst on FIN_WH — team=fin
...

Rule of thumb. Ship the digest as three sections in one message, not three separate messages. Team leads read one message; they will not chase three notifications. WoW deltas and hygiene KPIs go in the same view as the raw numbers.

Worked example — the monthly invoice reconciliation

Detailed explanation. The showback digest is derived from team_cost_daily, which is derived from QUERY_HISTORY. The monthly invoice is Snowflake's authoritative bill. The two should match to within 3%; larger drift signals either a tag-hygiene issue or an off-warehouse cost the ETL missed (storage, serverless services, external functions). The reconciliation SQL runs on the first Monday of each month against the previous month's data.

• The invoice. Loaded into analytics.cost.invoices via a manual CSV upload from the billing portal.
• The ETL total. Sum of team_cost_daily for the month.
• The target drift. ≤ 3%.

Question. Write the reconciliation SQL and the alert threshold that catches a drift beyond the target.

Input.

Component	Value
Invoice table	analytics.cost.invoices(month, line_item, usd)
ETL total	SUM(usd) from team_cost_daily for the month
Target drift	≤ 3%

Code.

WITH etl AS (
  SELECT DATE_TRUNC('month', run_date) AS month, SUM(usd) AS etl_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH')
    AND  run_date  < DATE_TRUNC('month', CURRENT_DATE)
  GROUP  BY 1
),
inv AS (
  SELECT month, SUM(usd) AS invoice_usd
  FROM   analytics.cost.invoices
  WHERE  month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH')
  GROUP  BY 1
),
delta AS (
  SELECT e.month,
         e.etl_usd,
         i.invoice_usd,
         i.invoice_usd - e.etl_usd                              AS drift_usd,
         (i.invoice_usd - e.etl_usd) / NULLIF(i.invoice_usd, 0) AS drift_pct
  FROM   etl e
  JOIN   inv i USING (month)
)
SELECT month,
       ROUND(etl_usd, 2)                                        AS etl_usd,
       ROUND(invoice_usd, 2)                                    AS invoice_usd,
       ROUND(drift_usd, 2)                                      AS drift_usd,
       ROUND(drift_pct * 100, 2)                                AS drift_pct,
       CASE
         WHEN ABS(drift_pct) > 0.10 THEN 'red — page FinOps'
         WHEN ABS(drift_pct) > 0.03 THEN 'amber — investigate'
         ELSE 'green'
       END                                                      AS status
FROM   delta;

Step-by-step explanation.

1. etl sums team_cost_daily for the previous calendar month. The date filter >= start-of-prev-month AND < start-of-this-month is inclusive-exclusive to avoid boundary double-counting.
2. inv reads the manually-loaded invoice CSV for the same month. In practice this table is loaded once per month by FinOps from the billing portal — the reconciliation runs after that upload.
3. delta joins the two on month and computes drift_usd = invoice - etl and drift_pct = drift / invoice. Positive drift means the invoice is higher than the ETL (i.e. we missed some cost); negative drift means we over-counted (rare, usually a duplicated tag).
4. The CASE bucket assigns a status: green ≤ 3%, amber 3–10%, red > 10%. Amber is investigation-only; red pages FinOps because a > 10% drift means something structural is wrong (a whole warehouse missing from the ETL, a storage cost line item not modelled).
5. In production this SQL runs as an Airflow DAG on the first Monday of each month. The result posts to the same Slack channel as the weekly digest so the reconciliation is public — teams see the drift number and trust the ETL numbers accordingly.

Output.

month	etl_usd	invoice_usd	drift_usd	drift_pct	status
2026-06-01	24800.00	25100.00	300.00	1.20	green
2026-05-01	22300.00	24700.00	2400.00	9.72	amber — investigate
2026-04-01	18400.00	22100.00	3700.00	16.74	red — page FinOps

Rule of thumb. Publish the reconciliation drift alongside the digest. Trust is built on the drift being consistently ≤ 3%; the first time a team sees a 10% miss they lose faith in every previous digest.

Senior interview question on allocation methodology

A senior interviewer might ask: "You inherit a spreadsheet-based allocation model that splits shared cost evenly across 8 teams. Two teams complain that they read almost nothing from the shared ingestion but are getting billed the same as the two heavy readers. Design a proportional allocation model, decide the cadence, and describe the change management to switch from spreadsheet to warehouse-native."

Solution Using a proportional-by-downstream-usage model with a weekly showback + monthly reconciliation cadence

-- 1. Downstream-usage table — daily per-team bytes read from shared objects.
CREATE OR REPLACE TABLE analytics.cost.shared_read_daily AS
SELECT DATE(start_time)                                                AS run_date,
       COALESCE(TRY_PARSE_JSON(query_tag):team::STRING, 'unknown')      AS team,
       SUM(bytes_scanned) / POWER(1024, 4)                              AS tb_read,
       COUNT(*)                                                         AS query_count
FROM   snowflake.account_usage.access_history ah
JOIN   snowflake.account_usage.query_history  qh USING (query_id)
WHERE  ah.direct_objects_accessed LIKE '%SHARED_INGEST.%'
  AND  qh.start_time >= DATE_TRUNC('day', CURRENT_TIMESTAMP)
GROUP  BY 1, 2;

-- 2. Allocation view — direct + proportional shared.
CREATE OR REPLACE VIEW analytics.cost.team_cost_alloc AS
WITH direct AS (
  SELECT DATE_TRUNC('month', run_date) AS month, team, SUM(usd) AS direct_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  team != 'shared'
  GROUP  BY 1, 2
),
downstream AS (
  SELECT DATE_TRUNC('month', run_date) AS month, team, SUM(tb_read) AS tb_read
  FROM   analytics.cost.shared_read_daily
  GROUP  BY 1, 2
),
shared_pool AS (
  SELECT DATE_TRUNC('month', run_date) AS month, SUM(usd) AS shared_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  team = 'shared'
  GROUP  BY 1
),
totals AS (
  SELECT month, SUM(tb_read) AS total_tb FROM downstream GROUP BY month
)
SELECT d.month,
       d.team,
       ROUND(d.direct_usd, 2)                                                            AS direct_usd,
       ROUND((dw.tb_read / NULLIF(t.total_tb, 0)) * s.shared_usd, 2)                     AS shared_alloc_usd,
       ROUND(d.direct_usd + (dw.tb_read / NULLIF(t.total_tb, 0)) * s.shared_usd, 2)      AS total_usd
FROM   direct     d
LEFT   JOIN downstream  dw ON d.month = dw.month AND d.team = dw.team
LEFT   JOIN shared_pool s  ON d.month = s.month
LEFT   JOIN totals      t  ON d.month = t.month;

-- 3. Change management — shadow-mode SQL diff.
SELECT team,
       old_alloc.usd AS old_uniform_split,
       new_alloc.total_usd AS new_proportional,
       new_alloc.total_usd - old_alloc.usd AS delta
FROM   analytics.cost.legacy_uniform_alloc old_alloc
JOIN   analytics.cost.team_cost_alloc      new_alloc USING (team, month)
WHERE  month = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 MONTH');

Step-by-step trace.

Phase	Days	Deliverable	Cadence
Shadow mode	30	Run both models; publish diff	Weekly diff Slack post
Team review	15	Each team validates their new number	Weekly Q&A
Cutover	1	Legacy uniform model retired	—
Steady state	∞	Proportional model powers digest	Weekly digest + monthly reconciliation

After the change management, the two heavy-reader teams (ml, fin) see their bill go up (they were previously subsidised); the two light-reader teams (ops, platform) see their bill go down (they were previously subsidising). The diff is published for a month before cutover so the change is transparent, not a surprise.

Output:

team	old_uniform_split	new_proportional	delta
ml	3125.00	6095.24	+2970.24
fin	3125.00	1523.81	-1601.19
ops	3125.00	380.95	-2744.05
platform	3125.00	500.00	-2625.00

Why this works — concept by concept:

• Direct + shared as separate columns — the team sees the two halves of their bill. The direct half is under their control (optimise their queries); the shared half is a policy conversation (why do we share this cost?). Folding them into one number hides both levers.
• Proportional weighting by downstream reads — this maps cost to consumption, which is the fairness principle everyone agrees on in the abstract. The proxy (bytes read from shared tables) has to match the shared-cost origin (ingestion of those tables) — pick the wrong proxy and the fairness argument falls apart.
• Shadow-mode for change management — running both models in parallel for a month and publishing the diff turns the switch from a political fight into a data conversation. Teams have time to challenge their new number before the invoice ledger flips.
• Weekly showback + monthly reconciliation — the cadence layer. Weekly digest is the operational signal; monthly reconciliation against the invoice is the audit checkpoint. Both are needed; one alone is either too noisy or too slow.
• Cost — one downstream-reads table (nightly refresh), one allocation view (queried on demand), one diff SQL (weekly). O(1) operational overhead; the change management is O(1) senior-engineer-month; the fairness improvement is O(monthly-invoice) in defensibility.

SQL
Topic — sql
SQL allocation and weighted-split problems

Practice →

Aggregation Topic — aggregation Aggregation problems on proportional splits

Practice →

---

5. Automation + Slack / reporting patterns

The closed loop — Airflow computes, Slack reports, the anomaly detector flags — is what turns attribution into an operational system

The mental model in one line: a production attribution pipeline runs three cooperating processes on a fixed cadence — a nightly Airflow ETL that refreshes team_cost_daily, a weekly Slack digest that reports the numbers back, and a z-score anomaly detector that flags per-team spend excursions — and the whole thing is monitored just like any other data pipeline, with SLOs and on-call. Skip the automation layer and the attribution numbers rot on a Notion page nobody reads.

The four axes for automation.

• Cadence. Nightly ETL, weekly digest, monthly reconciliation, quarterly business review. Fixed — never "when the on-call remembers".
• Alerting. Anomaly detector (z-score or IQR) on daily per-team spend + monitor triggers + hygiene KPIs. Wired to PagerDuty for red events; Slack for amber.
• Observability. The attribution pipeline itself needs metrics — DAG runtime, row count, drift vs invoice, alert count. Meta but essential: if the attribution pipeline is untrustworthy, so is the attribution.
• Runbook. When the anomaly detector fires, the on-call has a paved path: identify the spending team, identify the offending query, contact the owner, decide whether to kill or continue.

Pattern 1 — the nightly Airflow ETL.

• DAG. One DAG, one task per warehouse source, one union task, one write to team_cost_daily.
• Idempotency. Delete-and-insert for the target day; safe to re-run.
• Backfill. Parameterised on execution_date so a range can be re-run cleanly.
• SLO. Complete by 06:00 UTC so morning digests have fresh data.

Pattern 2 — the weekly Slack digest.

• Schedule. Monday 09:00 local time.
• Format. Slack Blocks with header + three sections (hygiene, teams, queries).
• Delivery. Slack Incoming Webhook or Slack App with chat.postMessage.
• Interaction. Optional buttons — "acknowledge", "raise ticket", "drill into Snowsight".

Pattern 3 — the z-score anomaly detector.

• Baseline. Trailing 28-day mean + stdev of daily spend per team.
• Metric. z = (today - mean) / stdev.
• Threshold. z > 3 fires an alert; z > 4 pages on-call.
• Guard. Skip teams with < 14 days of history (insufficient baseline).

Pattern 4 — the monthly reconciliation.

• Cadence. First Monday of each month.
• Inputs. team_cost_daily sum + invoices sum.
• Threshold. ≤ 3% drift = green; > 10% pages FinOps.
• Output. Same Slack channel as the digest; keeps trust in the numbers public.

Common interview probes on automation.

• "What triggers your anomaly detector?" — z-score, trailing-28d baseline, threshold 3.
• "What runs where — Airflow, dbt, Snowflake tasks?" — Airflow schedules the ETL DAG; dbt owns the SQL models; Snowflake tasks fill in for lightweight refreshes.
• "How do you handle the anomaly false-positive rate?" — mask team-holiday windows, mask campaign windows, tune threshold per team from experience.
• "What's the SLO on the attribution ETL?" — complete by 06:00 UTC; alert on late runs.

Worked example — nightly Airflow DAG that refreshes team_cost_daily

Detailed explanation. The DAG runs at 04:00 UTC nightly, has three parallel PythonOperator tasks (one per warehouse source), a union task, and a QA task. If any source task fails, the DAG fails; if the union succeeds but the QA task detects a > 10% row-count regression, the DAG alerts but does not fail (soft alert).

• Schedule. 0 4 * * *.
• Sources. Snowflake, BigQuery, Databricks.
• Idempotency. Delete-and-insert for the target run_date.
• QA. Row-count regression check against trailing 28d median.

Question. Write the Airflow DAG in Python (task graph + operators) and the QA-task SQL.

Input.

Component	Value
Target table	analytics.cost.team_cost_daily
Sources	3 warehouses (SF, BQ, DBX)
Cadence	Daily 04:00 UTC
SLA	Complete by 06:00 UTC

Code.

# dags/cost_attribution.py — nightly attribution DAG.
from datetime import datetime, timedelta
from airflow import DAG
from airflow.decorators import task
from airflow.operators.python import PythonOperator

DEFAULTS = {
    "owner": "de-oncall",
    "retries": 2,
    "retry_delay": timedelta(minutes=5),
    "sla": timedelta(hours=2),
}

with DAG(
    "cost_attribution_nightly",
    default_args=DEFAULTS,
    schedule="0 4 * * *",
    start_date=datetime(2026, 1, 1),
    catchup=False,
    max_active_runs=1,
    tags=["cost", "attribution"],
) as dag:

    @task
    def extract_snowflake(ds):
        # SELECT ... FROM snowflake.account_usage.query_history WHERE start_time = ds
        # write to analytics.cost.stg_sf_cost_daily
        ...

    @task
    def extract_bigquery(ds):
        # SELECT ... FROM `...INFORMATION_SCHEMA.JOBS_BY_PROJECT` WHERE creation_time = ds
        # write to analytics.cost.stg_bq_cost_daily
        ...

    @task
    def extract_databricks(ds):
        # SELECT ... FROM system.billing.usage WHERE usage_date = ds
        # write to analytics.cost.stg_dbx_cost_daily
        ...

    @task
    def union_all(ds):
        # DELETE FROM team_cost_daily WHERE run_date = ds;
        # INSERT INTO team_cost_daily
        #   SELECT * FROM stg_sf_cost_daily WHERE run_date = ds
        #   UNION ALL SELECT * FROM stg_bq_cost_daily WHERE run_date = ds
        #   UNION ALL SELECT * FROM stg_dbx_cost_daily WHERE run_date = ds;
        ...

    @task(retries=0)  # soft alert; do not fail the DAG on row-count regression
    def qa_row_count(ds):
        # See SQL below; raise soft alert if today's row count < 0.8 × median(trailing_28d).
        ...

    sf  = extract_snowflake("{{ ds }}")
    bq  = extract_bigquery("{{ ds }}")
    dbx = extract_databricks("{{ ds }}")
    u   = union_all("{{ ds }}")
    qa  = qa_row_count("{{ ds }}")

    [sf, bq, dbx] >> u >> qa

-- QA task — row-count regression check.
WITH today AS (
  SELECT COUNT(*) AS n
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date = :ds
),
baseline AS (
  SELECT MEDIAN(daily_n) AS med_n
  FROM  (SELECT run_date, COUNT(*) AS daily_n
         FROM   analytics.cost.team_cost_daily
         WHERE  run_date BETWEEN DATEADD(day, -28, DATE(:ds)) AND DATEADD(day, -1, DATE(:ds))
         GROUP  BY run_date) t
)
SELECT t.n                                                        AS today_n,
       b.med_n                                                    AS median_28d_n,
       CASE
         WHEN t.n < 0.8 * b.med_n THEN 'RED — row-count regression'
         WHEN t.n < 0.9 * b.med_n THEN 'AMBER — investigate'
         ELSE 'GREEN'
       END                                                        AS qa_status
FROM   today t
CROSS  JOIN baseline b;

Step-by-step explanation.

1. The DAG has three parallel extract tasks — one per warehouse — followed by a union task and a QA task. Airflow's >> operator wires the graph; the [sf, bq, dbx] >> u >> qa line says "the three extracts fan into the union, which then flows into QA."
2. Each extract task writes to a staging table (stg_sf_cost_daily, etc.) partitioned by run_date. Staging isolates the warehouse-specific quirks (JSON parsing, unit conversion) from the union step.
3. union_all deletes the target day from team_cost_daily first, then inserts the union of the three staging tables. Delete-and-insert makes the task idempotent — safe to re-run on a backfill or a recovery.
4. qa_row_count runs the regression check SQL. retries=0 means the QA failure does not retry (nothing will change on re-run); the task exits with a soft alert rather than failing the DAG, so a temporarily-quiet Sunday doesn't page the on-call.
5. The sla=timedelta(hours=2) at the DAG level enforces the 06:00 UTC completion target. Airflow emits an SLA-miss email if the DAG hasn't completed by 06:00; that email routes to de-oncall.

Output (Airflow UI + QA task output).

Task	Duration	Status	Rows written
extract_snowflake	42s	success	320
extract_bigquery	68s	success	180
extract_databricks	55s	success	42
union_all	12s	success	542
qa_row_count	3s	success (GREEN)	today=542, median=510

Rule of thumb. Extract tasks in parallel, union in one, QA at the end. Make the QA task soft-alert (retries=0, no fail) so a legitimate quiet day doesn't page the on-call; make the extract tasks retry-aggressive (2–3 retries) so a transient warehouse blip doesn't fail the whole DAG.

Worked example — z-score anomaly detector on per-team daily spend

Detailed explanation. The anomaly detector runs after the ETL each morning. It reads the last 29 days of team_cost_daily, computes the trailing-28-day mean and stdev for each team, and flags today's spend if (today - mean) / stdev > 3. Teams with < 14 days of history are skipped (baseline too noisy). Flags post to Slack; z > 4 also pages via PagerDuty.

• Baseline window. Trailing 28 days (excluding today).
• Metric. z-score of today's spend vs baseline.
• Thresholds. z > 3 amber (Slack); z > 4 red (PagerDuty).
• Guard. Skip teams with n_days < 14.

Question. Write the SQL that computes per-team z-scores and the Python that raises Slack alerts for z > 3 and PagerDuty for z > 4.

Input.

Setup	Value
Source table	analytics.cost.team_cost_daily
Baseline window	28 days
Amber threshold	z > 3
Red threshold	z > 4
Min history	14 days

Code.

WITH baseline AS (
  SELECT team,
         AVG(usd)                                                      AS mean_usd,
         STDDEV_SAMP(usd)                                              AS stdev_usd,
         COUNT(*)                                                      AS n_days
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date BETWEEN CURRENT_DATE - 29 AND CURRENT_DATE - 1
  GROUP  BY team
),
today AS (
  SELECT team, SUM(usd) AS today_usd
  FROM   analytics.cost.team_cost_daily
  WHERE  run_date = CURRENT_DATE - 1                -- ETL fills yesterday, checked this morning
  GROUP  BY team
),
scored AS (
  SELECT t.team,
         ROUND(t.today_usd, 2)                                          AS today_usd,
         ROUND(b.mean_usd, 2)                                           AS mean_usd,
         ROUND(b.stdev_usd, 2)                                          AS stdev_usd,
         CASE
           WHEN b.stdev_usd IS NULL OR b.stdev_usd = 0 THEN NULL
           ELSE ROUND((t.today_usd - b.mean_usd) / b.stdev_usd, 2)
         END                                                            AS z_score,
         b.n_days
  FROM   today t
  JOIN   baseline b USING (team)
  WHERE  b.n_days >= 14                                                 -- guard against thin baseline
)
SELECT team, today_usd, mean_usd, stdev_usd, z_score,
       CASE
         WHEN z_score > 4 THEN 'RED — page'
         WHEN z_score > 3 THEN 'AMBER — slack'
         ELSE 'green'
       END AS severity
FROM   scored
WHERE  z_score > 3
ORDER  BY z_score DESC;

# Anomaly-detector task — read scored rows, dispatch alerts.
import os, requests
from snowflake.connector import connect

def dispatch():
    conn = connect(...)
    rows = conn.cursor().execute(Z_SQL).fetchall()

    for team, today_usd, mean_usd, stdev_usd, z_score, severity in rows:
        text = (f":rotating_light: Cost anomaly on *{team}* — today ${today_usd:,} "
                f"vs 28d mean ${mean_usd:,} (σ=${stdev_usd:,}) → z={z_score}")
        if severity.startswith("RED"):
            requests.post(os.environ["PAGERDUTY_ROUTING_URL"], json={
                "routing_key": os.environ["PAGERDUTY_KEY"],
                "event_action": "trigger",
                "payload": {"summary": text, "severity": "critical", "source": "cost-attribution"},
            })
        requests.post(os.environ["SLACK_WEBHOOK_URL"], json={"text": text})

Step-by-step explanation.

1. baseline groups the trailing 28 days (excluding today) by team and computes mean + sample stdev + day count. STDDEV_SAMP uses the Bessel-corrected n-1 divisor which is more appropriate for a small sample than population stdev.
2. today sums today's spend per team from the same table. The ETL just filled today's row; the anomaly detector runs immediately after.
3. scored joins the two and computes z = (today - mean) / stdev. The CASE guards against stdev = 0 (a perfectly-flat team) which would otherwise divide by zero; those teams get a NULL z-score and fall out of the alert filter.
4. The WHERE b.n_days >= 14 guard skips teams with a thin baseline. A team that only started tagging 5 days ago has an unstable mean/stdev; the alert would be pure noise. 14 days is a defensible minimum; some teams tune this to 21.
5. The Python dispatcher posts every z > 3 to Slack and additionally fires PagerDuty for z > 4. The dual-channel pattern ensures the on-call knows about the red events even if they're not watching Slack.

Output.

team	today_usd	mean_usd	stdev_usd	z_score	severity
ml	2800.00	1200.00	350.00	4.57	RED — page
fin	620.00	400.00	60.00	3.67	AMBER — slack
ops	190.00	150.00	25.00	1.60	green

Rule of thumb. Use z-score, not absolute-dollar thresholds — a $500 spike is huge for ops and nothing for ml. The z-score normalises across teams and adapts as the baseline shifts. Threshold 3 for Slack, 4 for pager; anything lower is noise.

Worked example — Slack digest with interactive buttons

Detailed explanation. The weekly digest is more useful with interactive buttons — "acknowledge", "raise ticket", "drill into Snowsight". Slack's Block Kit supports button actions that POST to a webhook when clicked; the webhook records the action in an analytics.cost.acknowledgements table. Now the digest is not just a report but a workflow.

• The interaction. Buttons attached to each team's row.
• The endpoint. A small Slack event handler (Flask / FastAPI) or a Slack Workflow.
• The persistence. acknowledgements(team, action, actor, ts).

Question. Write the Slack Block payload with buttons and the FastAPI endpoint that receives the button click.

Input.

Component	Value
Slack Block Kit version	latest (2026)
Endpoint	POST /slack/actions
Persistence	analytics.cost.acknowledgements

Code.

# Slack Blocks payload with buttons.
blocks = []
blocks.append({"type": "header", "text": {"type": "plain_text", "text": "Weekly cost showback"}})
for team, usd, prev, delta in top_teams:
    blocks.append({
        "type": "section",
        "text": {"type": "mrkdwn", "text": f"*{team}* — ${usd:,} ({(delta or 0)*100:+.1f}% WoW)"},
        "accessory": {
            "type": "overflow",
            "action_id": f"cost_actions:{team}",
            "options": [
                {"text": {"type": "plain_text", "text": "Acknowledge"},          "value": f"ack:{team}"},
                {"text": {"type": "plain_text", "text": "Raise ticket"},         "value": f"ticket:{team}"},
                {"text": {"type": "plain_text", "text": "Drill in Snowsight"},   "value": f"drill:{team}"},
            ],
        },
    })

# FastAPI endpoint — receive button click, persist, respond.
from fastapi import FastAPI, Form, HTTPException
from datetime import datetime
import json
from snowflake.connector import connect

app = FastAPI()

@app.post("/slack/actions")
async def slack_actions(payload: str = Form(...)):
    data = json.loads(payload)
    # Slack signs every payload — verify signature before trusting it (omitted for brevity).
    action = data["actions"][0]["selected_option"]["value"]
    kind, team = action.split(":", 1)
    actor = data["user"]["username"]

    conn = connect(...)
    conn.cursor().execute(
        "INSERT INTO analytics.cost.acknowledgements (team, action, actor, ts) VALUES (%s, %s, %s, %s)",
        (team, kind, actor, datetime.utcnow()),
    )

    return {"response_type": "ephemeral",
            "text": f":white_check_mark: recorded *{kind}* on *{team}* by *{actor}*"}

-- Weekly rollup — which teams acknowledged, which teams silent.
SELECT team,
       MAX(CASE WHEN action = 'ack'    THEN ts END) AS last_ack_ts,
       MAX(CASE WHEN action = 'ticket' THEN ts END) AS last_ticket_ts,
       COUNT(DISTINCT actor)                        AS distinct_actors
FROM   analytics.cost.acknowledgements
WHERE  ts >= CURRENT_DATE - 7
GROUP  BY team
ORDER  BY last_ack_ts DESC NULLS LAST;

Step-by-step explanation.

1. The Slack Block payload uses the overflow accessory to attach a three-item dropdown to each team's row. action_id and value carry the (kind, team) pair so the endpoint knows what was clicked.
2. When a user clicks a button, Slack POSTs to the configured endpoint with a JSON payload (as a form field). The endpoint parses it, extracts the action + team + actor, and persists a row.
3. The FastAPI endpoint's signature verification (elided for brevity) is mandatory — without it, anyone can forge Slack messages. Verify the X-Slack-Signature header against the shared signing secret.
4. The INSERT INTO acknowledgements writes one row per click. In production this table has an index on (team, ts) for the rollup query and a TTL of 90 days.
5. The rollup SQL surfaces "who acknowledged their number this week" — a compliance-style report that FinOps runs monthly. Teams that never acknowledge get a follow-up conversation about the digest usefulness.

Output.

team	last_ack_ts	last_ticket_ts	distinct_actors
ml	2026-07-06 10:15	2026-07-06 10:16	2
fin	2026-07-06 09:32	NULL	1
ops	NULL	NULL	0

Rule of thumb. Interactive buttons turn a broadcast into a workflow. Ship them once you have a stable weekly digest; skip them if the digest itself is still churning. The acknowledgement KPI (distinct-actor-per-team-per-week) is the honest measure of whether attribution has cultural traction.

Senior interview question on end-to-end automation

A senior interviewer might ask: "Design the end-to-end attribution automation for a Snowflake account — the DAG shape, the SLOs, the alerting, and how the whole thing gets monitored. What are the top-3 failure modes and how do you catch them?"

Solution Using a nightly ETL + weekly digest + z-score detector + meta-observability

-- 1. Nightly attribution DAG (see previous example).
-- 2. Weekly digest DAG — Monday 09:00 UTC.
-- 3. Z-score anomaly detector — 07:00 UTC after ETL.
-- 4. Monthly reconciliation — first Monday of the month.
-- 5. Meta-observability — attribution pipeline is itself instrumented.

-- Meta-observability table — pipeline health metrics.
CREATE OR REPLACE TABLE analytics.cost.attribution_pipeline_health AS
SELECT CURRENT_DATE                                                                        AS run_date,
       (SELECT MAX(end_time) FROM analytics.cost.dag_run_log
        WHERE  dag_id = 'cost_attribution_nightly' AND state = 'success'
          AND  execution_date = CURRENT_DATE - 1)                                          AS last_success_ts,
       (SELECT COUNT(*) FROM analytics.cost.team_cost_daily
        WHERE  run_date = CURRENT_DATE - 1)                                                AS rows_yesterday,
       (SELECT MEDIAN(COUNT(*)) FROM analytics.cost.team_cost_daily
        WHERE  run_date BETWEEN CURRENT_DATE - 29 AND CURRENT_DATE - 2
        GROUP  BY run_date)                                                                AS median_28d_rows,
       (SELECT COUNT(*) FROM analytics.cost.anomaly_alerts
        WHERE  ts >= CURRENT_DATE - 7)                                                     AS alerts_7d;

-- Top-3 failure modes — each has a dedicated check.
-- FM1: DAG missed SLA (didn't finish by 06:00 UTC).
SELECT 'FM1' AS failure_mode,
       CASE WHEN last_success_ts IS NULL OR last_success_ts > CURRENT_TIMESTAMP - INTERVAL '30 hours'
            THEN 'PAGE' ELSE 'ok' END AS status
FROM   analytics.cost.attribution_pipeline_health;

-- FM2: Row count regression (< 80% of trailing median).
SELECT 'FM2' AS failure_mode,
       CASE WHEN rows_yesterday < 0.8 * median_28d_rows THEN 'AMBER' ELSE 'ok' END AS status
FROM   analytics.cost.attribution_pipeline_health;

-- FM3: Anomaly alert storm (> 20 alerts in 7 days signals threshold drift).
SELECT 'FM3' AS failure_mode,
       CASE WHEN alerts_7d > 20 THEN 'AMBER — tune thresholds' ELSE 'ok' END AS status
FROM   analytics.cost.attribution_pipeline_health;

Step-by-step trace.

Cadence	Task	SLO	Alert path
Nightly 04:00	ETL DAG	complete by 06:00 UTC	SLA miss → email de-oncall
Daily 07:00	z-score detector	5-min runtime	Slack (z > 3), PagerDuty (z > 4)
Weekly Mon 09:00	Digest DAG	5-min runtime	Slack channel
Monthly 1st Mon	Reconciliation	complete same day	Slack + FinOps ticket if > 10% drift
Nightly	Meta-observability	O(1) rows	Slack on FM1/FM2/FM3

After the whole system is in place, the top-3 failure modes are checked automatically every morning: FM1 (ETL missed SLA) pages the on-call; FM2 (row-count regression) surfaces as an amber Slack ping; FM3 (anomaly alert storm) suggests threshold drift and prompts a tuning pass. The attribution pipeline is now itself observed like any other data pipeline, with SLOs and alerts.

Output:

Layer	Surface	Alert path
DAG success	Airflow UI + Slack	on-call email + PagerDuty on SLA miss
Row-count	attribution_pipeline_health	Slack amber
Anomaly count	anomaly_alerts	Slack + PagerDuty per event
Digest delivery	Slack channel	none (self-observable)
Reconciliation drift	Slack channel	FinOps ticket if > 10%

Why this works — concept by concept:

• Cadence-locked automation — every artefact runs on a fixed schedule. Nothing waits for a human to remember. Missing runs are alerts; on-time runs are silent — the on-call learns the pipeline is boring, which is the highest praise.
• Meta-observability — the attribution pipeline is instrumented like any other pipeline. Row counts, DAG runtime, alert volume all trend into their own dashboard. Untrustworthy attribution is worse than no attribution; the meta layer guards the trust.
• Three failure-mode checks — FM1 (SLA miss) is the pager. FM2 (row regression) is the amber. FM3 (alert storm) is the drift signal. Covering three orthogonal failure modes catches the ~90% of realistic incidents; the tail requires human judgement.
• Anomaly detector as a first-class citizen — z-score on trailing 28d is a bounded, defensible statistic. It adapts to team-level baseline; it doesn't require per-team hand-tuning; it fires on a statistical excursion, not an arbitrary dollar threshold.
• Cost — one DAG, four schedules, one meta table. Runtime cost of the whole automation layer is a handful of credits per day. The avoided cost of one missed runaway (say $3k) pays for a year of automation compute.

SQL
Topic — sql
SQL anomaly-detection and rolling-baseline problems

Practice →

ETL
Topic — etl
ETL problems on nightly attribution pipelines

Practice →

---

Cheat sheet — Attribution recipes

Snowflake QUERY_TAG template

• Set at session start. ALTER SESSION SET QUERY_TAG = '{"team":"fin","pipeline":"elt","env":"prod","ctx":{"run_id":"..."}}'; — set in dbt's on-run-start hook or in an Airflow SnowflakeOperator session_parameters argument.
• Parse in ETL. TRY_PARSE_JSON(query_tag):team::STRING — use TRY_PARSE_JSON (not PARSE_JSON) so historical un-JSON queries return NULL instead of erroring the ETL.
• Retention. SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY keeps 1 year of history; beyond that, snapshot into your own attribution table monthly.
• Object tags for storage. CREATE TAG cost_center; ALTER TABLE t SET TAG cost_center = 'fin'; — flows into SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES and lets you attribute storage cost separately.

BigQuery INFORMATION_SCHEMA.JOBS_BY_PROJECT + labels query

• Set at session start. SET @@query_label = 'team:fin,pipeline:elt,env:prod'; — the entire session inherits the label until re-set.
• Parse in ETL. (SELECT value FROM UNNEST(labels) WHERE key = 'team') — the labels column is REPEATED RECORD(key, value) so you extract each key via a correlated subquery.
• Cost columns. total_bytes_billed / POWER(1024, 4) * on_demand_rate_per_tib for on-demand pricing; total_slot_ms / 3.6e6 * slot_hour_rate for flat-rate.
• Retention. INFORMATION_SCHEMA.JOBS_BY_PROJECT keeps 180 days; snapshot regularly to a permanent attribution table.

Databricks jobs + system.billing.usage

• Set at Terraform time. custom_tags = { team = "fin", pipeline = "elt", env = "prod" } on cluster or job resource. Enforced by cluster policy.
• Parse in ETL. custom_tags['team'] — the column is MAP<STRING, STRING> so key-lookup is straightforward.
• Cost columns. usage_quantity * list_prices.pricing.default after joining system.billing.usage to system.billing.list_prices.
• Cluster policy is enforcement. Terraform module + workspace-level cluster policy JSON with regex validators on the three tag keys.

Team-cost SQL template

CREATE OR REPLACE TABLE analytics.cost.team_cost_daily AS
SELECT DATE(start_time)                                              AS run_date,
       'snowflake'                                                   AS warehouse,
       COALESCE(TRY_PARSE_JSON(query_tag):team::STRING, 'shared')     AS team,
       COALESCE(TRY_PARSE_JSON(query_tag):pipeline::STRING, 'shared') AS pipeline,
       COALESCE(TRY_PARSE_JSON(query_tag):env::STRING, 'unknown')    AS env,
       SUM(credits_used_cloud_services) * 3.0                        AS usd,
       COUNT(*)                                                      AS query_count
FROM   snowflake.account_usage.query_history
WHERE  start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP)
GROUP  BY 1, 2, 3, 4, 5;

Slack digest webhook payload

• Endpoint. https://hooks.slack.com/services/... from Slack Incoming Webhook.
• Format. Slack Blocks — header + section blocks with mrkdwn text; use overflow accessories for buttons.
• Cadence. Weekly Monday 09:00 local time; nightly digest only if the team is high-velocity.
• Content. Three sections — hygiene KPI, top-5 teams (with WoW delta), top-10 costly queries.
• Interaction. Optional — button → FastAPI endpoint → acknowledgements table for compliance tracking.

Frequently asked questions

What is query cost attribution and why does it matter for data teams?

Query cost attribution is the practice of joining every warehouse query to the team, pipeline, and environment that ran it — turning a lump-sum monthly invoice into a per-owner, per-workload ledger you can defend, budget against, and optimise. It matters because the warehouse invoice is an aggregate; without attribution, no one can answer "who caused the 30% cost jump last month?" or "which pipeline should we optimise first?" A working attribution pipeline resolves four axes — cost tagging (QUERY_TAG, labels, custom_tags), resource monitors (soft-notify + hard-suspend caps), spend allocation (direct + proportional shared), and showback (report only) or chargeback (billed) — and produces a canonical team_cost_daily table that every downstream (Slack digest, Grafana dashboard, FinOps report) reads from. For senior data engineers, attribution is now a routinely-tested interview area because it sits at the intersection of SQL, pipeline design, cost engineering, and cross-team governance.

Snowflake QUERY_TAG or session tag — what's the right way to tag queries?

Use QUERY_TAG set as a session-level parameter, not per-query. ALTER SESSION SET QUERY_TAG = '{"team":"fin","pipeline":"elt","env":"prod","ctx":{...}}'; is the canonical setter — run it once at session start (typically in dbt's on-run-start hook, an Airflow operator session_parameters, or the JDBC connection string), and every query in the session inherits the tag. The JSON blob shape lets you carry multiple dimensions in one tag without exploding cardinality; low-cardinality axes (team, pipeline, env) live at the top level and high-cardinality context (tenant-id, run-id) lives under a nested ctx key. Parse in downstream ETL with TRY_PARSE_JSON(query_tag):team::STRING — the TRY_ form returns NULL on malformed tags rather than failing the ETL, which is essential because the query-history retention window includes queries that predate schema changes. Session-level tagging is enforced by the client (dbt macro, SDK wrapper) and audited by a nightly hygiene query that surfaces unknown spend by user and warehouse.

BigQuery labels vs bytes-scanned attribution — when do I use each?

Labels are the primary attribution axis; bytes-scanned is the fallback proxy for shared-cost weighting. Labels set via SET @@query_label = 'team:fin,pipeline:elt,env:prod' (or the labels field on a job) flow into INFORMATION_SCHEMA.JOBS_BY_PROJECT.labels and give you a direct join from job to team — this is the answer for direct allocation. total_bytes_billed (or total_slot_ms under flat-rate) is the cost axis on the same row; multiply by the effective rate ($6.25 per TiB on-demand at 2026 rates) to get dollars per (team, pipeline, env). Bytes-scanned enters when you need to allocate shared cost — an ingestion pipeline that everyone reads from but no one team owns. There you sum bytes_scanned per team against the shared tables and use the ratios as weights on the shared bill. Never rely on bytes-scanned alone for team attribution: two teams with the same read volume can have wildly different downstream cost profiles, and the label answers "who owns this" definitively while bytes-scanned only proxies it.

Showback vs chargeback — when does an org switch from one to the other?

showback is report only — a Slack digest, a Notion page, a Grafana dashboard that surfaces per-team spend without triggering any general-ledger entry. chargeback is actual cross-team billing — a monthly journal entry that debits the team's budget and credits the platform's budget. Most orgs run showback indefinitely and never move to chargeback; the switch happens when (a) finance requires teams to defend their spend against a formal budget line, (b) the platform team's own funding depends on internal cost recovery, or (c) there's a governance mandate (public sector, regulated industries, SOX-adjacent controls). Chargeback requires (i) a signed-off allocation methodology (defensible in an audit), (ii) an appeals process (teams disputing a bill), and (iii) a rolling reconciliation against the invoice with a tight drift target. Showback needs none of the above and can be shipped by a single senior DE in a quarter; chargeback usually needs a cross-functional program with FinOps, finance, and legal.

How do I allocate shared cost across multiple teams fairly?

Split shared cost with a weighting rule that maps to actual consumption of the shared resource. The three defensible weightings: uniform (equal split — best when the shared cost genuinely serves everyone equally, e.g. a shared metadata catalog); proportional by downstream usage (weight by each team's read TB or query count against the shared tables — the industry default for shared ingestion pipelines); storage-weighted (weight by each team's stored GB — best for shared storage cost specifically). Compute the weight ratio each month, multiply by the shared-pool total, and add to each team's direct cost. Publish the direct and shared portions as separate columns in team_cost_daily — the team needs to see both because the direct half is under their control (optimise their queries) while the shared half is a policy conversation (why do we share this cost?). Roll out via shadow mode — run the new proportional model in parallel with the legacy uniform model for a month, publish the diff, let teams challenge their new number, then cut over. Big-bang switches to a new allocation model are how attribution loses credibility.

What cadence should I show back cost at — daily, weekly, or monthly?

Weekly is the default for a mature attribution pipeline; daily is for volatile ops teams; monthly is the invoice reconciliation checkpoint. Weekly digest (Monday 09:00) — Slack message with hygiene KPI, top-5 teams by trailing-7-day spend + WoW delta, top-10 costly queries. High enough signal that teams take action; low enough noise that they don't tune it out. Daily digest (early morning) — only for teams with high day-to-day volatility (streaming teams, ad-hoc research); most stable production teams find daily too noisy. Monthly review (first Monday of the month) — formal doc with per-team spend vs budget, top-10 optimisations, and reconciliation against the invoice with a ≤ 3% drift target. Quarterly business review — executive summary for CFO / CTO using the trailing three months of weekly digests. Pair weekly showback with a z-score anomaly detector on daily spend for the events that can't wait for Monday — that gives you both a fixed cadence for routine attention and interrupt-driven alerts for genuine excursions.

Practice on PipeCode

• Drill the SQL practice library → for the tagging-JSON, attribution-join, and weighted-allocation problems senior interviewers love.
• Rehearse on the ETL practice library → for the nightly-attribution and multi-warehouse-union pipelines that motivate the whole system.
• Sharpen the tuning axis with the optimization practice library → for the resource-monitor, quota-sizing, and anomaly-detector problems.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the tag + monitor + allocation intuition against real graded inputs.

Lock in query-cost-attribution muscle memory

Warehouse docs explain tag primitives. PipeCode drills explain the decision — when a shared-cost split should be proportional vs uniform, when to escalate a resource monitor from notify to suspend, when a z-score anomaly is real vs a team-holiday artefact. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →