BigQuery Logical vs Physical Storage in dbt: The Silent Bill

Running dbt on BigQuery without ever looking at your storage billing model is one of the most common ways early-stage data teams leak money quietly. The fix is a one-line dataset update — but only once you know which datasets to target. This post shows you the exact diagnostic query to find them in under five minutes.

BigQuery offers two storage billing models: logical (the default) and physical. For stable, infrequently updated tables they are roughly comparable. But for high-churn dbt models — staging layers, incremental models rebuilt frequently, intermediate tables replaced on every run — the difference can be substantial. The reason is time travel: BigQuery retains up to seven days of table history, and under logical billing that retention is included in your storage price. Under physical billing, time travel and fail-safe are charged separately, at compressed storage rates. When your tables change often, logical billing inflates your bill with the cost of storing all those historical versions.

A pattern we see repeatedly in early-stage dbt projects: the team audits query compute costs meticulously — labelling jobs, checking INFORMATION_SCHEMA.JOBS, reviewing slot usage — but never once opens a storage breakdown. The compute line looks fine; the storage line has been quietly compounding for months.

Why Does BigQuery's Storage Model Matter for dbt Projects?

BigQuery's two storage models are set at the dataset level, not the table level, and logical storage is enabled for all datasets by default.

Under logical storage, you pay for the uncompressed logical size of your data. Time travel storage (up to seven days of table history) and fail-safe storage (an additional seven days) are bundled into that rate at no additional per-line charge. Under physical storage, you pay for actual compressed bytes on disk — which is almost always smaller — but time travel and fail-safe are metered separately at the physical rate.

Physical storage is cheaper than logical storage in BigQuery, except in cases where compression is not beneficial, or where time travel and fail-safe costs outweigh the savings — and this most commonly happens on datasets where data changes frequently. Dataform and dbt doing ELT transforms are among the biggest culprits for this pattern.

This is the crux of the problem. A dbt staging model rebuilt every hour accumulates 168 incremental time-travel snapshots across a week. Under logical billing, every one of those historical versions contributes to your storage charge. The base table itself might be 10 GB — but the effective billed size, once time travel is factored in, can be several multiples of that.

Logical storage pricing automatically includes retention for time travel and fail-safe storage for the same data. Physical storage, by contrast, does not include either time travel or fail-safe by default — these are charged separately once enabled. Logical storage is the default for all BigQuery datasets.

The result: teams on logical billing with high-churn dbt models pay a hidden premium. And because the line item says "storage" rather than "compute", it tends to escape the usual optimisation scrutiny.

---

📺 Watch: How does BigQuery store data?

---

How to Find Datasets Where Physical Storage Would Be Cheaper

The diagnostic below queries INFORMATION_SCHEMA.SCHEMATA_OPTIONS to identify which datasets are still on logical billing, then joins to INFORMATION_SCHEMA.__TABLES__ to surface the tables with the highest logical storage footprint. Run this in your BigQuery project console:

-- Step 1: Find datasets still on logical (default) billing
SELECT
  schema_name AS dataset_id,
  option_name,
  option_value
FROM
  `region-eu`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
  -- replace region-eu with your region, e.g. region-us
WHERE
  option_name = 'storage_billing_model'
ORDER BY
  schema_name;

If option_value is LOGICAL or the row is absent entirely (logical is the default, so many datasets simply won't have this row), that dataset is on logical billing. Now identify which of those datasets carry the most time-travel weight — i.e., high-churn tables with large logical footprints:

-- Step 2: Surface the heaviest tables by logical size
-- Run once per dataset; replace YOUR_DATASET
SELECT
  table_id,
  ROUND(size_bytes / POW(1024, 3), 2)        AS size_gb_logical,
  ROUND(num_rows / 1000000, 2)               AS millions_of_rows,
  DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date,
  DATE(TIMESTAMP_MILLIS(creation_time))      AS created_date,
  DATE_DIFF(
    CURRENT_DATE(),
    DATE(TIMESTAMP_MILLIS(last_modified_time)),
    DAY
  )                                          AS days_since_last_change
FROM
  `YOUR_PROJECT.YOUR_DATASET.__TABLES__`
ORDER BY
  size_bytes DESC
LIMIT 30;

What you are looking for: large tables (size_gb_logical > 5 GB) with days_since_last_change consistently low (tables rebuilt daily or more frequently). These are your prime candidates for the physical storage model, because their time travel overhead is continuously replenished.

For a quick estimate of potential savings on a specific dataset, query the Storage API directly:

-- Step 3: Compare logical vs physical billing for a dataset
SELECT
  project_id,
  dataset_id,
  ROUND(SUM(total_logical_bytes)   / POW(1024, 3), 2) AS total_logical_gb,
  ROUND(SUM(total_physical_bytes)  / POW(1024, 3), 2) AS total_physical_gb,
  ROUND(
    (SUM(total_logical_bytes) - SUM(total_physical_bytes))
    / POW(1024, 3), 2
  )                                                    AS difference_gb,
  ROUND(
    100 * (1 - SAFE_DIVIDE(
      SUM(total_physical_bytes),
      SUM(total_logical_bytes)
    )), 1
  )                                                    AS physical_saving_pct
FROM
  `region-eu`.INFORMATION_SCHEMA.TABLE_STORAGE
  -- replace region-eu with your actual region
WHERE
  dataset_id = 'YOUR_DATASET'
GROUP BY
  1, 2
ORDER BY
  difference_gb DESC;

If physical_saving_pct is above 30–40%, physical storage will almost certainly be cheaper even after you add back time travel charges. If it is below 15%, logical is probably fine — compression gains are marginal and do not offset the unbundled time travel cost.

Running that query dataset by dataset is manageable for a small project. Checking every dataset across a multi-team dbt project is not — fintel-scan is a free, open-source CLI (MIT licence) that runs this check and fourteen others locally without a live warehouse connection: uvx fintel-scan.

How to Fix It: Switch Datasets to Physical Storage

Once you have identified the datasets where physical is likely cheaper, switching is a single DDL statement:

-- Switch a dataset to physical storage billing
ALTER SCHEMA `YOUR_PROJECT.YOUR_DATASET`
SET OPTIONS (
  storage_billing_model = 'PHYSICAL'
);

A few important caveats before you run this in production:

You can only switch billing models once every 14 days per dataset. The storage billing model can be changed at the dataset level — unlike the compute billing model, which is set at the project level — but you can only switch between models once every 14 days. Test on a non-critical dataset first.

Time travel configuration matters. Under physical billing, you are now paying for time travel by the compressed byte. If you do not need seven days of history on a staging table that is rebuilt hourly, reduce it:

-- Reduce time travel to 2 days on a high-churn dataset
ALTER SCHEMA `YOUR_PROJECT.YOUR_DATASET`
SET OPTIONS (
  max_time_travel_hours = 48
);

For dbt staging layers — stg_* models that are rebuilt from source on every run — 48 hours of time travel is almost always enough. You will rarely need to recover a staging table from five days ago when you can simply rerun the pipeline.

Clones and snapshots still use copy-on-write storage efficiently under both models. Both clones and snapshots leverage copy-on-write behaviour, making them nearly free at creation — charges only kick in when the base table is modified or deleted and the clone or snapshot becomes the only reference to specific data blocks. If your team uses table clones for dev/test environments, physical billing does not change that calculus, but because BigQuery storage is column-based, small changes to the data in a base table can result in large increases in storage cost for a clone — in some cases you may be charged the full storage amount for the clone. Monitor clone growth after switching.

For more on how to control access and cost across datasets in a multi-project dbt setup, see our post on dbt Multi-Project Architecture in BigQuery: Split It Right.

How to Build This Into Your dbt Governance Process

Detecting the problem once is not enough. Storage billing model drift — new datasets created by engineers who do not know the default is suboptimal — is a recurring issue in teams that grow quickly.

The right long-term fix is to make physical storage the default for new datasets in high-churn environments, and to audit regularly. Here is a lightweight approach that fits inside a dbt project:

1. Add a post-hook macro that alerts on new logical datasets. Run the Step 1 query above as part of your CI pipeline. If any dataset other than a known exception appears on logical billing, fail the check with a warning. This surfaces the issue at PR time rather than invoice time.

2. Document the storage billing model in your dbt project YAML. Add a meta block to each source and mart dataset declaration:

sources:
  - name: payments_raw
    database: your_project
    schema: payments_raw
    meta:
      storage_billing_model: physical
      time_travel_hours: 48
      last_audited: "2026-08-01"

This is documentation, not enforcement — but it makes the intent explicit and makes audits faster.

3. Schedule a weekly storage breakdown query against INFORMATION_SCHEMA.TABLE_STORAGE. Pipe it into a simple Holistics or Looker dashboard, plotted by dataset over time. Sudden increases in total_logical_bytes that outpace row growth are a reliable signal that time travel accumulation is spiking — usually because a model has started doing full-refresh runs more frequently than intended.

In our work with a Series A fintech, this exact pattern caused storage costs to triple over two quarters. The team had switched several high-volume tables to full-refresh after a join bug — a sensible short-term fix — but never reverted, and never looked at the storage tab. A 20-minute audit and two dataset-level DDL changes cut their monthly storage bill by roughly 35%.

For a deeper look at how dbt query labels can help you attribute both compute and storage costs by team and model, see our guide on dbt Query Labels in BigQuery: Fix Your Cost Attribution (2026).

Frequently Asked Questions

Q: When should I use physical storage instead of logical storage in BigQuery?

A: Use physical storage for datasets where tables are updated frequently — particularly dbt staging, incremental, and intermediate models rebuilt daily or more often. Physical billing charges compressed bytes plus time travel and fail-safe separately, which is cheaper when tables churn often. For stable, rarely updated reference tables, logical billing is simpler and often comparable in cost.

Q: Does switching to physical storage in BigQuery affect my dbt models?

A: No. The storage billing model is a dataset-level billing configuration only — it has no effect on query behaviour, schema, or dbt model logic. Your transforms, tests, and materialisation strategies all work identically. The only change is how Google calculates your monthly storage charge.

Q: How do I find out which BigQuery billing model my datasets are using?

A: Query INFORMATION_SCHEMA.SCHEMATA_OPTIONS filtering on option_name = 'storage_billing_model'. Datasets absent from the result, or with option_value = 'LOGICAL', are on the default logical model. You can compare logical vs physical byte sizes for a dataset using INFORMATION_SCHEMA.TABLE_STORAGE.

Q: How much can switching to physical storage save on BigQuery?

A: Savings vary by workload. For datasets with a high proportion of frequently modified tables, the physical rate on compressed bytes can be 40–70% smaller than the logical rate — before accounting for time travel. Once you add back unbundled time travel costs, net savings are typically 20–40% on high-churn datasets. Stable datasets will see little or no benefit.

Q: Can I reduce BigQuery time travel storage costs without switching billing models?

A: Yes. You can reduce the time travel window from the default seven days to as few as two days at the dataset level using ALTER SCHEMA ... SET OPTIONS (max_time_travel_hours = 48). Under logical billing this directly reduces the historical storage billed. This is a useful first step for staging datasets even if you do not switch billing models, though switching to physical plus reducing time travel typically yields the largest combined saving.

Storage cost in BigQuery is the line item most dbt teams under-optimise — it lacks the drama of a query that scans a terabyte, but it compounds every single day. At Fintel Analytics, we audit storage billing models as a standard part of every BigQuery engagement — identifying which datasets are on the wrong model, what time travel windows are appropriate for each materialisation strategy, and building the governance checks that stop the problem recurring. If your BigQuery bill has been growing faster than your data volume, that gap is worth investigating before it becomes a budget conversation.