Tables dbt Left Behind

Concepts covered: Materializations

dbt's picture of the warehouse is the project. Each run walks the models it parsed and creates or replaces 1 relation per model, named after the model. At the start of a run dbt may list the relations in the schemas it builds into, so it knows whether each name it is about to build already exists, but it acts only on the names its parsed nodes claim. A relation no node claims is never updated and never dropped. So every rename and every deletion in a dbt project leaves a relation behind, built on the day of the edit and untouched from then on.

5 edits and what they leave

Edit	dbt builds on the next run	Left in the warehouse
Rename `fct_orders.sql` to `fct_orders_daily.sql`	`fct_orders_daily`	`fct_orders`, frozen at its last build
Delete `int_order_payments.sql`	Nothing for it	`int_order_payments`
Point `fct_revenue` at a different schema	`fct_revenue` in the new schema	`fct_revenue` in the old one
Add `versions` to `dim_customers` with no alias	`dim_customers_v1`, `dim_customers_v2`	`dim_customers`
Change `materialized` from view to table	`fct_orders` as a table	Nothing: dbt drops the view of the same name first

The last row marks the boundary. dbt owns the name it is about to build, so a view called fct_orders gives way to a table called fct_orders. The 4 rows above it change the name or the schema, and the old name belongs to nobody. The frozen fct_orders is the dangerous case. The finance dashboard reading it renders every panel. Its monthly total on June 30 equals the total shown on March 3, the morning of the rename, because the table has held the same rows since, and no error appears anywhere.

Why dbt leaves them

The schema dbt builds into is often shared. An analyst's hand-made table, a load tool's staging copy and a table another project writes can all live beside dbt's relations, and a run that dropped everything it did not recognise would destroy them. dbt's unit of work is the node, and a deleted node has no file to run and no name to act on. The cleanup is left to the engineer, who knows which of the unclaimed names were dbt's.

Finding them

SELECT
  table_schema,
  table_name,
  table_type
FROM information_schema.tables
WHERE table_schema = 'analytics'
ORDER BY table_name

The warehouse answers with what exists. dbt ls --resource-type model --resource-type seed --resource-type snapshot answers with the nodes the project builds, seeds included because each seed loads a table of its own. dbt ls prints node names, and a relation's name can differ: an alias replaces it, so fct_orders_daily builds fct_orders, and a version without an alias adds _v and its number, so version 2 of dim_customers builds dim_customers_v2. Translate each node into its relation name before comparing. A relation no translated name matches is either one dbt left behind or a table a person created by hand inside dbt's schema. Both deserve a look, and the warehouse's last-modified metadata, where it keeps any, dates the day each orphan stopped changing.

DROP TABLE IF EXISTS analytics.fct_orders

Dropping is a statement run against the warehouse, outside dbt. Run it after the readers have moved, because a dropped table takes the dashboard from stale to broken, and a stale dashboard is at least a dashboard someone can compare against. dbt clean deletes local folders such as target and touches nothing in the warehouse.

Renaming without an orphan

{{ config(alias='fct_orders') }}

select
    order_date,
    count(*) as orders,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by
    order_date

The alias config separates the model's name in the project from the relation's name in the warehouse. fct_orders_daily.sql with alias='fct_orders' builds the relation fct_orders the dashboard already reads, so the rename is visible to every ref() and invisible to the warehouse. 2 models that would build the same relation name in the same schema stop the run at parse time with an error naming both, so an alias can never silently overwrite a neighbour. Without the alias, a rename is 3 changes in a fixed order: rename the file and build, move the dashboard to the new name, drop the old relation. Dropping first leaves the dashboard with nothing, and never dropping leaves it showing frozen numbers.

Rename the file and run, so the new relation exists beside the old one
Move every reader outside dbt to the new name and confirm it renders
List the schema and compare it with dbt ls to confirm the old name is unclaimed
Drop the old relation by hand and record the drop in the same change

Do

Treat every file rename or deletion as a warehouse change with a drop step at the end
Use alias when the relation name must hold while the model name changes
List orphans with a catalog query after each round of refactoring
Drop a relation only after confirming nothing reads it

Avoid

Expect dbt run to drop anything whose model is gone
Trust a dashboard with no errors to be reading fresh data
Drop the old relation before its readers have moved

About this lesson

This section is part of the Models: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.