The cheapest day in a data model’s life is the day it merges.
Every day after that costs something: build minutes every night, an engineer’s morning whenever someone renames an upstream column, and trust, because every near-duplicate table is one more an analyst has to second-guess. Coding agents made the merge cheaper still. One will write the model and its tests in less time than the meeting where someone asked for it.
I think the speed is mostly good news, but it changes the ratio. Writing a model used to take real effort. Now it’s nearly free while everything after the merge costs what it always did, so upkeep is most of the bill, and almost no team tracks that bill per model. I’d rather count models read than models merged.
So I’d put three checks on a model: two before it ships, and one every week for as long as it exists.
No decider, no merge
No new model merges without a short decision brief: what decision this number feeds, who makes it, how often they look, and what they’d do differently if the number moved. The brief also names an owner and a number to reconcile against. Only then does anyone sketch the grain for an agent to build.
The brief lives under meta in the model’s YAML, so it travels with the code and lands in manifest.json for the ledger. CI can reject a new model without one.
# models/marts/pricing/_pricing.yml (an invented model)
version: 2
models:
- name: fct_churned_accounts_by_plan
description: "Accounts active at the start of each month and how many of them churned, by plan and region."
config:
meta:
owner: "pricing-analytics"
decision: "Whether to change the annual-plan discount at the monthly pricing review"
decider: "Head of pricing"
read_cadence_days: 31
if_it_moves: "Annual-plan churn above 4% two months running puts the discount back on the agenda"
trusted_number: "Monthly churned-account total in the billing system's churn report"
columns:
- name: churn_month
data_tests: [not_null]
- name: plan
data_tests: [not_null]
It takes five minutes, and I think it keeps most speculative models from being built at all. “Someone might want this by region” can’t fill in the decision field. If nobody can name a decider, the model is a guess.
Set read_cadence_days to how often the decider really looks. The ledger holds the model to it.
Reconcile against a number someone already trusts
not_null and unique will happily pass on a table that’s wrong where it matters. Join accounts to billing contacts before aggregating, and every account with two contacts gets counted twice. The grain is still unique and nothing is null. Agent-written SQL hides this well: the query runs and the numbers look fine.
Before a model publishes, it has to agree with something finance or the source system already reports, like a close total or a source table’s row count. That’s the test that catches a fan-out join, and the tolerance lives in it, where a reviewer can argue with it.
-- tests/reconcile_churn_to_billing.sql
-- Returns a row for every billed month the model misses or is more than 0.5% off.
with model_totals as (
select churn_month, sum(churned_accounts) as churned
from {{ ref('fct_churned_accounts_by_plan') }}
group by churn_month
)
select
b.report_month,
b.churned_accounts as billing_churned,
m.churned as model_churned
from {{ source('billing', 'monthly_churn_report') }} as b
left join model_totals as m
on m.churn_month = b.report_month
where b.report_month >= '2024-01-01' -- the model's first month, fixed so dropping it still fails
and (m.churned is null
or abs(m.churned - b.churned_accounts) > 0.005 * b.churned_accounts)
Starting from billing means a month the model drops entirely fails, and the current month, which billing hasn’t closed, is left alone. The fixed start date skips months from before the model existed.
No trusted number counts the same as no decider. The model might be fine, but it shouldn’t feed a decision yet.
A ledger of who reads what
Every major warehouse logs who ran each query, and most record which tables it touched. The usage ledger is a weekly job that counts those reads per model, by type of reader.
The split matters more than the totals. The dbt user reads every upstream model every night, so if its reads count, nothing ever looks idle. People count, and so do other service accounts, like the BI tool or an export job. A staging model with live children is in use even if nobody queries it, so the ledger only judges leaves, the models nothing else depends on.
Here it is against Snowflake’s ACCOUNT_USAGE.ACCESS_HISTORY.
-- Usage ledger: reads per dbt model over the last 180 days (Snowflake).
-- ACCESS_HISTORY needs Enterprise Edition and can lag by up to three hours.
-- ops.dbt_models is upserted from manifest.json on every deploy, one row per model:
-- unique_id, relation_name (DB.SCHEMA.NAME), owner, read_cadence_days,
-- child_count (models and exposures that depend on it, from child_map, tests excluded),
-- first_deployed (from git history for existing models, then kept from the first deploy)
WITH models AS (
SELECT
d.*,
COALESCE(d.read_cadence_days, 90) + 14 AS allowed_days -- cadence plus two weeks of grace
FROM ops.dbt_models AS d
WHERE d.relation_name IS NOT NULL -- ephemeral models have no table
),
reads AS (
SELECT
ah.query_id,
ah.user_name,
ah.query_start_time,
UPPER(obj.value:"objectName"::string) AS relation_name
FROM snowflake.account_usage.access_history AS ah,
LATERAL FLATTEN(input => ah.direct_objects_accessed) AS obj
WHERE ah.query_start_time >= DATEADD(day, -180, CURRENT_TIMESTAMP())
-- no objectDomain filter, so dynamic tables count; the final join drops non-dbt objects
),
classified AS (
SELECT
r.*,
CASE
WHEN r.user_name IN ('DBT_PROD', 'DBT_CI') THEN 'build' -- the users that run dbt
WHEN u.type IN ('SERVICE', 'LEGACY_SERVICE') THEN 'service' -- BI tools, exports
ELSE 'human' -- TYPE stays NULL until an admin sets it on a user
END AS reader_kind
FROM reads AS r
LEFT JOIN snowflake.account_usage.users AS u
ON u.name = r.user_name
AND u.deleted_on IS NULL
),
per_model AS (
SELECT
relation_name,
COUNT(DISTINCT IFF(reader_kind = 'human', query_id, NULL)) AS human_reads,
COUNT(DISTINCT IFF(reader_kind = 'human', user_name, NULL)) AS human_readers,
COUNT(DISTINCT IFF(reader_kind = 'service', query_id, NULL)) AS service_reads,
MAX(IFF(reader_kind <> 'build', query_start_time, NULL)) AS last_read
FROM classified
GROUP BY relation_name
)
SELECT
m.unique_id,
m.owner,
m.read_cadence_days,
m.child_count,
COALESCE(p.human_reads, 0) AS human_reads_180d,
COALESCE(p.human_readers, 0) AS human_readers_180d,
COALESCE(p.service_reads, 0) AS service_reads_180d,
p.last_read,
DATEDIFF(day, p.last_read, CURRENT_TIMESTAMP()) AS days_since_read,
CASE
WHEN m.child_count > 0 THEN FALSE -- models or exposures depend on it
WHEN DATEDIFF(day, m.first_deployed, CURRENT_TIMESTAMP()) <= m.allowed_days
THEN NULL -- too new to judge
WHEN p.last_read IS NOT NULL
THEN DATEDIFF(day, p.last_read, CURRENT_TIMESTAMP()) > m.allowed_days
WHEN m.allowed_days > 180 THEN NULL -- cadence longer than the window
ELSE TRUE
END AS past_cadence
FROM models AS m
LEFT JOIN per_model AS p
ON p.relation_name = UPPER(REPLACE(m.relation_name, '"', ''))
ORDER BY past_cadence DESC NULLS LAST, days_since_read DESC NULLS FIRST;
On BigQuery, start from the region-qualified INFORMATION_SCHEMA.JOBS view, unnest referenced_tables, and split readers on user_email, since service accounts end in gserviceaccount.com. Cached queries there don’t list their tables, so a dashboard served from cache looks quieter than it is. That view keeps 180 days, so I’d use the same window on Snowflake, which keeps a year.
An annual model can’t be judged inside 180 days, so the query returns NULL. For those, I’d keep each run’s last-read dates somewhere that outlives the warehouse’s history.
A read isn’t a decision, though. A BI tool refreshing an extract every morning reads the model whether or not anyone opens the dashboard, so the ledger calls it healthy. For models behind dashboards, I’d add the BI tool’s own view counts. That makes the ledger a floor: zero reads means a leaf model is unused, and plenty of reads means it might be. The brief covers the gap, because its decider should be able to say what they did the last time the number moved.
The board pack a 90-day rule would delete
A flat rule looks like enough: retire anything nobody has read in 90 days. It would flag the model behind the quarterly board pack days before the board meets, and the one behind the annual audit too.
That’s why the brief carries a cadence. The ledger judges each model against its own read_cadence_days plus two weeks of grace. Models without a brief get 90 days, and a new model gets one full cadence before it’s judged. Backfill first_deployed for existing models from the commit that added each SQL file, or they all look brand new on the first run.
A flagged model’s owner gets the read counts and two weeks to claim it. If nobody does, the model gets a deprecation_date a month out and a matching table comment that analysts browsing the warehouse see. Then it’s dropped. The SQL stays in git, so most drops are easy to undo. An incremental model whose source data has aged out isn’t, so copy that table somewhere cold first.
The first run, and the export it broke
None of this happened. The team and its numbers are made up.
Six analytics engineers own 420 dbt models, about half written with an agent. The first run judges the 230 leaves and flags 96. A flat 90-day rule would have flagged four more, which feed the quarterly board pack and were last read 98 days ago, inside their 92-day cadence plus grace.
Seventy of the 96 are retired, and the other 26 get briefs from owners willing to defend them. Dropping those 70 turns 40 upstream models into leaves, and later runs retire 38 of them. That’s 108 models gone in about three months, and the nightly build falls from 95 minutes to 62.
Six weeks in, the weekly partner export fails. It read one of the retired models through a service account, and the first ledger had thrown out every service account along with the dbt user. The deprecation comment had sat on that table for a month, and a service account doesn’t read comments. From then on, every service account except the dbt users counts as a reader, and the export is declared as a dbt exposure, which the ledger counts as a child.