Zero-Downtime Table Materialization in dbt

dbt’s standard table materialization builds a temp table, drops the existing one, then renames the temp into place. This works for most models. It breaks down for production-critical tables in two specific ways.

The availability gap. There’s a brief window between DROP and RENAME where the table doesn’t exist. If a dashboard query or a scheduled extract hits that window, it fails with a “table not found” error. For internal models, nobody notices. For the table feeding a client-facing dashboard, it’s a support ticket.

The validation gap. There’s no check before replacement. If your model SQL returns zero rows because a source table is empty or a date filter is wrong, the standard materialization replaces a million-row table with an empty one. You don’t find out until someone notices missing data downstream.

A zero-downtime materialization solves both: it builds to a temp name, validates the result, then swaps via rename. The old table stays queryable until the new one is confirmed ready.

The Pattern

The six-step structure stays the same, but the main SQL step builds into a temp relation instead of the target, and a validation step sits between build and swap.

Create macros/materializations/zero_downtime_table.sql:

{%- materialization zero_downtime_table, adapter='bigquery' -%}

  {# Configuration #}
  {% set min_row_count = config.get('min_row_count', 1) %}

  {# Setup relations #}
  {% set target_relation = this.incorporate(type='table') %}
  {% set existing_relation = load_cached_relation(this) %}
  {% set temp_relation = make_temp_relation(target_relation) %}
  {% set backup_relation = make_backup_relation(target_relation) %}

  {# Handle type mismatch (existing is a view, not a table) #}
  {% if existing_relation is not none and existing_relation.type != 'table' %}
    {% do adapter.drop_relation(existing_relation) %}
    {% set existing_relation = none %}
  {% endif %}

  {{ run_hooks(pre_hooks) }}

  {# Build new table under a temp name. Old table stays queryable. #}
  {% call statement('main') %}
    {{ create_table_as(false, temp_relation, sql) }}
  {% endcall %}

  {# Validate before swapping #}
  {% call statement('validate', fetch_result=True) %}
    SELECT COUNT(*) AS row_count FROM {{ temp_relation }}
  {% endcall %}

  {% set row_count = load_result('validate')['data'][0][0] %}

  {% if row_count < min_row_count %}
    {% do adapter.drop_relation(temp_relation) %}
    {{ exceptions.raise_compiler_error(
      "Validation failed: " ~ row_count ~ " rows, expected at least "
      ~ min_row_count ~ ". Old table preserved."
    ) }}
  {% endif %}

  {# Swap: rename existing to backup, then temp to target #}
  {% if existing_relation is not none %}
    {% do adapter.rename_relation(existing_relation, backup_relation) %}
  {% endif %}

  {% do adapter.rename_relation(temp_relation, target_relation) %}

  {# Drop backup after successful swap #}
  {% if existing_relation is not none %}
    {% do adapter.drop_relation(backup_relation) %}
  {% endif %}

  {{ run_hooks(post_hooks) }}

  {% set grant_config = config.get('grants') %}
  {% do apply_grants(target_relation, grant_config) %}
  {% do persist_docs(target_relation, model) %}

  {% do adapter.commit() %}
  {{ return({'relations': [target_relation]}) }}

{%- endmaterialization -%}

How the Swap Works

The sequence matters. The old table gets renamed to a backup name before the new one takes its place. If the build or validation fails, the old table stays untouched — the error fires before any rename happens. The backup only gets dropped after the swap succeeds.

The window where neither old nor new table exists under the target name is the time it takes to execute two BigQuery metadata operations: rename old to backup, rename temp to target. That’s typically under a second, compared to the potentially minutes-long gap during a standard table rebuild.

BigQuery doesn’t support atomic table swaps like Snowflake’s ALTER TABLE ... SWAP WITH, so this window can’t be eliminated entirely. But it’s small enough that most concurrent queries won’t hit it. For truly zero-gap requirements, you’d need a different approach (views as abstraction layers, for instance), but for practical purposes this pattern eliminates the problem.

Using It in a Model

{{ config(
    materialized='zero_downtime_table',
    min_row_count=1000
) }}

SELECT
    user_id,
    email,
    account_status,
    created_at
FROM {{ ref('int__users_joined') }}
WHERE account_status != 'deleted'

If the model returns fewer than 1000 rows, dbt raises an error and the old table stays in place. The logs tell you exactly what happened: “Validation failed: 42 rows, expected at least 1000. Old table preserved.” No guessing.

Comparing Against the Previous Build

A static row count threshold catches empty tables, but a relative check catches more subtle problems. You can compare the new table against the existing one to flag unexpected drops in volume:

{# Add this after the min_row_count check #}
{% set max_drop_pct = config.get('max_row_count_drop_pct', 50) %}

{% if existing_relation is not none and max_drop_pct is not none %}
  {% call statement('old_count', fetch_result=True) %}
    SELECT COUNT(*) FROM {{ existing_relation }}
  {% endcall %}
  {% set old_count = load_result('old_count')['data'][0][0] %}

  {% if old_count > 0 %}
    {% set drop_pct = ((old_count - row_count) * 100 / old_count) | int %}
    {% if drop_pct > max_drop_pct %}
      {% do adapter.drop_relation(temp_relation) %}
      {{ exceptions.raise_compiler_error(
        "Row count dropped " ~ drop_pct ~ "% (" ~ old_count ~ " to " ~ row_count
        ~ "), threshold is " ~ max_drop_pct ~ "%. Old table preserved."
      ) }}
    {% endif %}
  {% endif %}
{% endif %}

This catches the scenario where a source table goes partially empty or a filter removes too much data. The model fails safely rather than pushing incomplete data downstream.

The two validation layers complement each other:

• Absolute minimum (min_row_count): catches total failures — empty source tables, broken joins that produce zero rows, misconfigured filters.
• Relative drop (max_row_count_drop_pct): catches partial failures — a source that lost half its data, a new WHERE clause that’s too aggressive, a partition that went missing upstream.

For tables with naturally volatile row counts (seasonal data, campaign-driven traffic), set max_row_count_drop_pct to none in model config to disable the relative check while keeping the absolute minimum.

When This Pattern Fits

Use zero-downtime materialization when:

• The table feeds client-facing dashboards or external consumers where “table not found” errors create support tickets
• The data is critical enough that replacing it with an empty or dramatically smaller table would cause real damage
• You have a reasonable expectation of what the table’s row count should be

For internal development models or tables that only get queried by downstream dbt models during a run (not concurrent queries), the standard table materialization is fine. The cost impact of the extra COUNT query is negligible compared to the cost of rebuilding the table itself.

This is the simpler of the two common custom materializations and a good starting point for learning the structure. Once you’re comfortable with the swap-and-validate pattern, the secured table pattern adds more post-build operations while following the same principles.