BigQuery Table Expiration Breaking dbt Models: Find & Fix It

BigQuery table expiration silently deletes tables that your dbt models depend on — and unless you are actively monitoring for it, the first sign of a problem is a failed dbt run in production, often hours after the table vanished. Here is how to detect every at-risk dependency in your project before it costs you a broken pipeline.

Table expiration in BigQuery is a legitimate cost-control mechanism. Set a TTL on a dataset or table and BigQuery will auto-delete it after the configured window — no prompts, no warnings, no ceremony. The problem is that this feature interacts badly with dbt in ways that are genuinely non-obvious. Intermediate tables materialised as table during a previous run, ad-hoc staging tables created outside of dbt, or raw source tables loaded by an ingestion tool — any of them can have an expiration timestamp set, either deliberately or accidentally via a dataset-level default expiration that nobody remembers configuring.

A pattern we see repeatedly in early-stage fintech and e-commerce companies: a new dataset is created during a prototype phase with a dataset-level default table expiration of 24 or 72 hours — just enough to keep costs down while the team is experimenting. Six months later, production dbt models are reading from tables in that same dataset. Nobody has noticed the TTL. The dataset-level expiration is still in place, quietly stamping every new table that lands there. Eventually a dbt model materialises a large intermediate table, it expires overnight, and the downstream mart fails the next morning with a "Table not found" error. By that point, the table is gone — and so is your Monday morning executive report.

Why BigQuery Table Expiration Is Invisible by Default

BigQuery does not emit a native alerting event when a table expires. There is no Pub/Sub notification, no Cloud Monitoring metric, no email. The deletion is silent. If you are not querying INFORMATION_SCHEMA proactively, you will not know a table has an expiration set until it has already disappeared.

The situation is compounded by two dbt-specific factors. First, dbt's ref() and source() functions resolve table names at compile time, not at runtime. If a referenced table has been deleted, the compile step may still succeed — the error surfaces only when BigQuery actually tries to read it during execution. Second, dbt has no native awareness of BigQuery table expiration metadata. It does not read TTL values. It does not warn you that a table it is about to write to has a 48-hour expiration. A common issue that arises is the increased size of tables in BigQuery due to repeated transformations, which can significantly inflate costs — but the inverse is also true: tables disappearing on a schedule is equally disruptive and far less visible.

The risk is highest in three scenarios:

• Raw source tables loaded by ingestion tools (Fivetran, Airbyte, custom pipelines) into a dataset that has a dataset-level default expiration inherited from its creation settings
• dbt intermediate models materialised as table in a dataset where someone has set a default expiration — the table gets recreated each run, but if the run fails or is skipped, the previous materialisation expires before the next run
• Ad-hoc reference tables — lookup files, seed overrides, or manually loaded CSVes — that have individual table-level expiration timestamps set and are referenced by source() declarations

---

📺 Watch: Module 14 Mastering SCDs Type 1 & Type 2 for Historical Data in dbt

---

How to Find Tables With Expiration Set in Your BigQuery Project

The detection query runs against INFORMATION_SCHEMA.TABLES and takes under ten seconds on a typical project. It returns every table in a dataset that has a non-null expiration timestamp, along with how many hours remain before deletion.

SELECT
  table_schema AS dataset,
  table_name,
  table_type,
  TIMESTAMP_MILLIS(CAST(option_value AS INT64)) AS expires_at,
  TIMESTAMP_DIFF(
    TIMESTAMP_MILLIS(CAST(option_value AS INT64)),
    CURRENT_TIMESTAMP(),
    HOUR
  ) AS hours_remaining
FROM
  `your_project`.`your_dataset`.INFORMATION_SCHEMA.TABLES
JOIN
  `your_project`.`your_dataset`.INFORMATION_SCHEMA.TABLE_OPTIONS
  USING (table_catalog, table_schema, table_name)
WHERE
  option_name = 'expiration_timestamp'
  AND TIMESTAMP_MILLIS(CAST(option_value AS INT64)) > CURRENT_TIMESTAMP()
ORDER BY
  expires_at ASC;

Repeat this for each dataset your dbt project reads from. If you want to check for dataset-level default expirations — which stamp every new table written into that dataset — query INFORMATION_SCHEMA.SCHEMATA_OPTIONS instead:

SELECT
  schema_name AS dataset,
  option_name,
  option_value
FROM
  `your_project`.`region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE
  option_name = 'default_table_expiration_ms'
ORDER BY
  schema_name;

Any dataset returned by this second query is actively stamping every table written to it with a TTL. If your dbt models materialise into those datasets, they are on a countdown timer.

To cross-reference which dbt models are writing into affected datasets, compare the results against your dbt ls output filtered by materialisation:

dbt ls --select config.materialized:table --output json | grep '"schema"'

This gives you the list of models materialised as tables — map them against the datasets returned by the SCHEMATA_OPTIONS query to find your highest-risk models.

---

Checking one dataset manually is fine. Checking every dataset across a multi-environment dbt project is not. fintel-scan is a free open-source CLI (MIT licensed) that automates this check and fourteen others locally, without a warehouse connection: uvx fintel-scan.

---

How to Find Expired Tables That Have Already Broken Your Lineage

If you suspect a table has already expired and want to confirm what was lost, query job history for "Table not found" errors in the recent window:

SELECT
  creation_time,
  user_email,
  job_id,
  error_result.reason AS error_reason,
  error_result.message AS error_message,
  query
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result.reason = 'notFound'
  AND job_type = 'QUERY'
ORDER BY
  creation_time DESC
LIMIT 50;

The error_message field will include the full table reference that was not found. Match that back to your dbt DAG to understand which models failed and what the downstream impact was.

For source tables specifically — tables declared in your sources.yml — you can also check whether any sources have a loaded_at_field configured and cross-reference the last ingest timestamp against the expiration window. If a source table expired before its next scheduled load, it may have been recreated by the ingestion tool — but any dbt run that landed in the gap will have failed silently if alerting was not in place.

A fintech client we worked with had exactly this failure mode in their transaction data pipeline. A raw events table loaded by a custom ingestion job had a 48-hour expiration set on it from an early-prototype dataset configuration that nobody had revisited. The ingestion job ran every 24 hours — fine in normal operation. But when the ingestion job failed one evening and was not retried until the following morning, the table expired in the gap. The dbt run that fired at 6 AM hit a "Table not found" error. The downstream settlement report did not populate. The operations team spent two hours diagnosing what had happened before the root cause was identified. The fix took ten minutes — the damage to operational trust took considerably longer to repair.

How to Fix Table Expiration Issues in dbt Projects

There are three layers of fix, and you need all three.

1. Remove expiration from production datasets

For any dataset that production dbt models write to or read from, remove the default table expiration. In the BigQuery console, navigate to the dataset, click Edit Dataset, and set "Default table expiration" to "Never". Via SQL:

ALTER SCHEMA `your_project`.your_dataset
SET OPTIONS (
  default_table_expiration_ms = NULL
);

Do the same for any individual tables that have table-level expiration set, identified by the first detection query above:

ALTER TABLE `your_project.your_dataset.your_table`
SET OPTIONS (
  expiration_timestamp = NULL
);

2. Separate prototype and production datasets explicitly

The root cause in most cases is that prototype-era settings bleed into production because datasets were never properly separated. Enforce a naming convention — _dev, _staging, _prod suffixes — and apply dataset-level expirations only to dev and staging tiers. Production datasets should have NULL expiration at the dataset level, enforced via Terraform or whatever IaC tooling manages your GCP environment. A 2024 survey found that 57% of analytics engineers see data quality as one of their biggest day-to-day challenges — configuration drift from prototype to production is one of the most common and least visible contributors.

3. Add expiration monitoring to your data observability layer

Schedule the detection query from the first section to run daily and pipe results into your alerting channel. A simple Cloud Scheduler job triggering a BigQuery scheduled query that writes results to a monitoring table — with a Looker or Holistics alert firing when hours_remaining < 48 for any table referenced by a dbt model — gives you advance warning before the deletion hits production.

The scheduled query approach is particularly effective for source tables owned by ingestion tools, where you cannot directly control the TTL but can at least be alerted when one appears. Scheduling key monitoring queries to run daily and piping the results into a dashboard or alerting system is a straightforward pattern that most teams underuse.

If you are using dbt Cloud, add a post-run job hook that queries INFORMATION_SCHEMA.TABLE_OPTIONS for the datasets written to during the run and fails the job if any output table has an expiration set within the next 72 hours. This makes the problem visible at deployment time rather than at deletion time.

How to Prevent This Recurring

Once you have cleared existing expirations from production datasets, the prevention layer has three components:

• IaC enforcement — manage all production dataset configurations via Terraform. Set default_table_expiration_ms = null explicitly in your resource definitions so any accidental re-application of an expiration is caught in a PR review before it reaches the environment.
• dbt project checks — add a custom dbt macro or a CI step that queries INFORMATION_SCHEMA.TABLE_OPTIONS post-run and raises an error if any model output has an expiration timestamp. This is a five-minute addition to a GitHub Actions workflow.
• Source freshness + existence checks — extend your sources.yml freshness configuration to also validate that source tables exist before the dbt run fires. Tools like Elementary and custom pre-hooks can make this native to your dbt workflow.

The investment is small. The cost of not doing it is a broken pipeline at the worst possible moment — typically during a period-end close, a board reporting cycle, or a high-volume transaction day when operational visibility matters most.

For reference, also see our posts on related pipeline reliability patterns: Late-Arriving Data in dbt BigQuery: Fix Silent Metric Corruption and dbt Sources With No Freshness Config: Find Them in BigQuery — both cover adjacent failure modes that compound the impact of expiration-driven outages.

Frequently Asked Questions

Q: How do I check if a BigQuery table has an expiration set?

A: Query INFORMATION_SCHEMA.TABLE_OPTIONS for the dataset containing the table, filtering where option_name = 'expiration_timestamp'. This returns the expiry timestamp for any table in the dataset that has one set. You can also view it in the BigQuery console under the table's Details tab.

Q: Can BigQuery table expiration delete a table that my dbt model is currently writing to?

A: Yes — if a dbt model materialises a table in a dataset with a dataset-level default expiration, the newly written table inherits that expiration immediately. The table will be deleted after the configured TTL regardless of whether it is actively used by downstream models.

Q: How do I remove a default table expiration from a BigQuery dataset?

A: Use ALTER SCHEMA your_dataset SET OPTIONS (default_table_expiration_ms = NULL) in BigQuery SQL. Alternatively, navigate to the dataset in the BigQuery console, click "Edit dataset", and set the default table expiration to "Never". Neither operation affects existing table-level expirations — those must be cleared individually.

Q: Will dbt warn me if a source table has an expiration timestamp set?

A: No. dbt has no native awareness of BigQuery table expiration metadata. Source freshness checks verify that data was loaded within a configured window, but they do not read or validate TTL values. You need to monitor expiration separately via INFORMATION_SCHEMA queries or a dedicated observability tool.

Q: What is the difference between a dataset-level default expiration and a table-level expiration in BigQuery?

A: A dataset-level default expiration (default_table_expiration_ms) is applied automatically to every new table created in that dataset — existing tables are not affected when you set it. A table-level expiration is set on an individual table and overrides the dataset default. Both cause silent auto-deletion when the TTL expires.