BigQuery storage costs: logical vs. physical billing

For years, BigQuery storage was the line item nobody opened. Logical (uncompressed) billing at roughly two cents per GiB-month made a 10 TB warehouse a few hundred dollars. Physical billing changed the arithmetic: highly compressed, clustered, nested data can be several times smaller on disk than its logical size. The same 10 TB logical dataset might be 2 TB physical, which at physical rates is a different invoice, or a missed discount if you are still on logical.

The reverse is also true. Poorly compressing data, such as already-compressed file loads or some numeric-heavy schemas, can make physical billing more expensive than logical. Google lets you set the storage billing model per dataset, and the wrong default left in place after a migration is one of the cleaner FinOps wins because it requires no query rewrite.

-- Price both models from catalog bytes, then switch only
-- where physical (or logical) is clearly cheaper.
ALTER SCHEMA `proj.analytics`
SET OPTIONS (storage_billing_model = 'PHYSICAL');

-- Revert if compression is poor:
-- SET OPTIONS (storage_billing_model = 'LOGICAL');

Time travel and fail-safe live on the physical side of the ledger even when you think in logical bytes. Heavy DML (MERGE/UPDATE/DELETE/TRUNCATE) inflates physical bytes that logical billing never showed you. A dataset that looks 4x compressed on active bytes can still lose a physical-billing compare if the fact table gets rewritten every night.

Long-term rates kick in after 90 days unaltered, per partition. A snapshot nobody modifies gets cheaper without any action, and keeps billing forever. Long-term is not archival to GCS; it is a discount on bytes everyone forgot about.

The 10 GiB monthly free tier is project-level noise once you have a real warehouse, so it is not a reason to skip the dataset-level compare. A 10 TB logical dataset at $0.023/GiB is about $230/month before anyone has discussed compute. Ten of those is a real line item, and switching the ones that compress well is a meeting, not a rewrite.

Physical vs. logical is not a global default to pick for the whole org. Compression ratios differ by dataset: raw JSON events, numeric finance marts, and already-gzipped loads all compress differently. Compare per dataset, switch per dataset, and document why.

SlashPeak uses public list prices: active logical $0.023/GiB-month, long-term logical $0.016, active physical $0.04, long-term physical $0.02, with a 10 GiB monthly free tier at the project level. Long-term rates apply to partitions unaltered for 90 days.

The compare is not 'physical is cheaper.' It is: for this dataset's actual logical and physical bytes, which model costs less at list. A dataset that compresses 4× favors physical even at the higher per-GiB rate. A dataset that barely compresses favors logical. You cannot see this from the billing SKU alone without joining TABLE_STORAGE / PARTITIONS metadata.

Work the 10 TB logical / 2 TB physical case. Active logical: 10,240 GiB × $0.023 ≈ $236. Active physical: 2,048 GiB × $0.04 ≈ $82. Physical wins by about $154/month on that dataset with no SQL change. If physical were 9 TB on the same 10 TB logical, physical would be 9,216 × $0.04 ≈ $369 vs. $236 logical, and you would stay on logical instead.

Time-travel physical bytes are why heavy-DML datasets surprise you. If fail-safe plus time travel add 3 TB physical on a '2 TB' table, you are paying physical rates on 5 TB. Count those catalog fields, not just active_physical_bytes; a dataset with high MERGE counts in 30 days needs that extra column in the spreadsheet.

Switch thresholds should be boring. If physical is only 2% cheaper, it is not worth switching, since the next DML-heavy week can flip it. SlashPeak wants physical materially cheaper (on the order of 15% or more) before recommending PHYSICAL, and the reverse before recommending LOGICAL, plus a $1 floor so toy datasets do not generate findings. Under 10 GiB, it skips the dataset entirely.

Every warehouse accumulates snapshots, 'temp_' experiments that escaped, and deprecated marts that are still referenced by one forgotten dashboard. BigQuery will happily keep them at active logical rates until 90 days of no modification, and even then you keep paying long-term rates forever.

The useful question is not 'what is large?' It is 'what is large and unused?' Unused means no query jobs referenced the table in the scan window. That is a metadata join: storage size plus last_modified_time plus referenced_tables from jobs. Deleting or archiving to Cloud Storage / BigQuery Omni is a product decision; knowing which tables are candidates is not.

SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 1) AS gib,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), last_modified_time, DAY)
    AS days_since_modified
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_logical_bytes > 100 * POW(1024, 3)
ORDER BY total_logical_bytes DESC;

Sub-finding A is abandoned: >100 GiB, last modified >90 days ago, zero references in the job window. That is delete-or-archive after you confirm with the last owner. Sub-finding B is long-term eligible: still queried, but unmodified 90+ days, still sitting in active-rate bytes. Writes stopped; reads did not. You might snapshot it, or you might just wait for long-term rates. Severity is lower because someone still needs it.

last_modified_time moves when you load, DML, or copy. A nightly MERGE that rewrites a partition keeps the table 'fresh' even if the business stopped caring. Abandoned is about jobs not referencing it, not about a timestamp you can game with a no-op load. Use both signals.

Attribution still matters. A stale 2 TB table last touched by a departed contractor's user account is a different conversation from one queried weekly by Looker. Do not drop the Looker one because a storage rule said 'large.' The unused join is the whole finding.

A 2 TB table with neither PARTITION BY nor CLUSTER BY is an accident of chronology. It started at 8 GB. It was loaded daily. Nobody scheduled the rewrite. Every query now reads more than it should, and every MERGE rewrites more than it should. Partitioning after the fact is a project; clustering on the columns already used in WHERE/JOIN is often the faster first step.

CREATE TABLE `proj.analytics.events`
PARTITION BY event_date
CLUSTER BY account_id, event_name
AS
SELECT * FROM `proj.analytics.events_heap`;

-- Or cluster in place when a rewrite is too large:
-- ALTER TABLE ... SET OPTIONS isn't enough for CLUSTER BY;
-- use CREATE OR REPLACE / a copy job in a maintenance window.

SlashPeak's large-unpartitioned-table rule is deliberately advisory and excluded from the total estimated monthly waste. Inventing a dollar figure would require assuming a partition column and a typical date range. The finding exists to put the table on a design review, alongside clustering recommendations that are grounded in actual filter columns from jobs.

Thresholds that keep this from becoming noise: on the order of 100 GiB before it is worth a finding, a handful of real jobs in the window so you do not flag a forgotten dump, and high severity when you are past 1 TiB. Tiny heaps are not a layout emergency.

If the table has a temporal column and jobs do not filter it, you also have UNPARTITIONED_DATE_SCAN with a dollar figure. Layout advisory is for tables that are actively queried, large, and still have no PARTITION BY or CLUSTER BY, including ones whose filters are not dates at all.

Clustering-only rewrite is valid when access is selective on account_id and not time-ranged. Partition-only is valid when every expensive job is a date range. Both is the usual fact-table end state. Neither is valid as a Friday-night surprise on a 20 TB table without a backfill plan.

Storage work causes less drama than killing a dashboard, which makes it a good first SlashPeak sprint, as long as you do not flip billing models on a dataset you have not priced.

Price logical vs. physical per dataset with time-travel bytes included. Switch only where the gap is obvious, wait a billing cycle, and keep in mind that reverting is a single ALTER SCHEMA.

For stale tables: confirm with owners first, drop second. Snapshot to GCS if legal wants a hold, and do not cluster a table nobody queries.

For large active tables with no layout: pick a partition column from filters you already see, or cluster first. Schedule the copy, then add require_partition_filter if it is a fact table.

Re-scan after each dataset billing change. Storage findings should move even if compute findings do not; if they do not move, the wrong dataset was switched, or the price check used stale list prices.

None of this requires reading row data. Dataset billing model, partition DDL, CLUSTER BY, partition-level storage bytes, and last_modified_time are all INFORMATION_SCHEMA. That is the access model SlashPeak is built around: one read-only custom role with metadata permissions, never dataViewer on your tables.

If a vendor needs to sample table contents to tell you a dataset is on the wrong storage model, they are solving a metadata problem with a data-access request. The storage line on the invoice is already in the catalog.

TABLE_STORAGE and PARTITIONS can be large catalogs, but they are still metadata. You do not need SELECT on events to know it is 4 TiB logical and 0.9 TiB physical; you need permission to read the catalog in the table's region.

Scanner jobs should exclude themselves from 'referenced' sets, or a stale table gets marked as used because the audit itself read INFORMATION_SCHEMA. That is an easy mistake in homegrown notebooks. SlashPeak excludes its own user_email on purpose.

The same scan that prices storage runs the query-waste rules. Storage-only tools miss that a 2 TB table with no layout is also a full-scan risk. Compute-only tools miss that physical rates are being paid on data that does not compress. Both lines belong on the same invoice.

Dataset analytics: 10,240 GiB active logical, 2,048 GiB active physical, little DML, modest time-travel physical bytes. Logical bill is about $236/month, physical about $82/month. Switching to PHYSICAL saves roughly $154/month, about $1,850 a year: not a huge number by itself, but larger than most routine cleanup chores.

SELECT
  10240 * 0.023 AS logical_usd,   -- ~$236
  2048 * 0.04 AS physical_usd,    -- ~$82
  10240 * 0.023 - 2048 * 0.04 AS delta_usd;  -- ~$154

Same dataset, but nightly MERGE rewrites keep 4,000 GiB extra physical in time travel. Physical bill becomes (2,048 + 4,000) × $0.04 ≈ $242, while logical stays at about $236. Now physical loses. Stay on LOGICAL, or fix the MERGE so it stops rewriting the whole table, which is a compute finding that also fixes storage.

-- 2 TiB active physical + 4 TiB time travel
SELECT
  10240 * 0.023 AS logical_usd,
  (2048 + 4096) * 0.04 AS physical_with_tt_usd;

Add a stale 2,048 GiB logical snapshot in the same project, unmodified 120 days, zero job references. At active logical $0.023 that is ~$47/month if it has not flipped to long-term, or ~$33 at $0.016 if it has. Dropping it is $33–47/month and a cleaner catalog. Clustering it is a waste of a maintenance window.

Add a 2 TB heap, no partition, no cluster, queried 40 times a day. Storage billing is a rounding error next to the compute. Layout advisory has no $/mo because the save depends on event_date vs account_id. Do not pretend the storage optimizer will fix that table. Put it on the partition post's week-one list.

Ten datasets like the first paragraph is ~$1.5k/month. That is a real finding and still usually smaller than one unpruned 4 TB table. Rank storage against compute. Do storage in the same sprint as a rest day between SQL rewrites, not instead of the SQL rewrites.

TABLE_STORAGE (region-qualified) gives logical and physical bytes, time-travel, fail-safe, last_modified_time. That is the price compare's numerator. Do not use TABLES.row_count × a guessed row size.

SELECT
  table_schema,
  table_name,
  ROUND(total_logical_bytes / POW(1024, 3), 1) AS logical_gib,
  ROUND(total_physical_bytes / POW(1024, 3), 1) AS physical_gib,
  ROUND(SAFE_DIVIDE(total_logical_bytes, NULLIF(total_physical_bytes, 0)), 2)
    AS compression_x,
  last_modified_time
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE
WHERE total_logical_bytes > 10 * POW(1024, 3)
ORDER BY total_logical_bytes DESC;

SCHEMATA_OPTIONS or dataset metadata has storage_billing_model. If it is missing, you are on the default (logical, historically). ALTER SCHEMA is the switch.

SELECT
  schema_name,
  option_name,
  option_value
FROM `region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS
WHERE option_name = 'storage_billing_model';

PARTITIONS lets you see which partitions are long-term eligible and whether one table is the whole dataset. A dataset-level switch triggered by one 8 TB table still changes the model for every table in that dataset.

Join referenced_tables from JOBS to TABLE_STORAGE to split unused vs queried. Filter last_modified_time older than 90 days and active bytes above 100 GiB. That is the stale list.

DDL from TABLES tells you PARTITION BY / CLUSTER BY. Large bytes, jobs present, neither clause: layout advisory. Do not estimate dollars without picking a column.

Switching a dataset with heavy DML and fat time travel to PHYSICAL just because active bytes compress 4x is risky without pricing the extra physical columns first. If the compare is close, leave the billing model alone.

Switching every dataset in the org on the same afternoon is how a wrong global flip becomes a support ticket with finance. Switch one, wait for the billing export, then continue.

Dropping a 'stale' table that is actually the source of a weekly Sheets extract is a real risk if the job window missed that day: 30 days of JOBS should catch a weekly job, but 7 days might not. When in doubt, ask the last user_email on the finding before dropping anything.

Partitioning a high-ingest streaming table on a high-cardinality timestamp at microsecond grain creates a partition storm. Daily partitions, or ingestion-time plus a clustered business key, are the usual sensible choices.

Adding CLUSTER BY on columns that are not in any filter just to make the advisory go away gains nothing; empty cluster slots are fine.

Legal holds, finance snapshots, and 'we might need this for the audit' tables are people problems. The finding's job is to name the dollars; the drop is a named human's decision.

dbt snapshots and backup models create timestamped copies that nobody expires. strategy='check' snapshots on a 2 TB dim will grow a second 2 TB. Add a retention policy or a downstream expire. Snapshot SQL that SELECT * the source also copies width you do not need.

Looker PDTs persist explores into tables. If the explore is a star, the PDT is a starred copy stored on top of the original. Point PDTs at contract views, and drop PDTs that no dashboard has hit in 90 days; Looker will rebuild one if someone still needs it.

Connected Sheets extracts materialize into BigQuery or into the sheet. The BigQuery side is another table in TABLE_STORAGE. Name them, expire them, do not let sheets_dataconnector_ jobs create anonymous heaps in analytics.

CLONE and COPY jobs for 'just in case' are how prod_events_backup_2024 stays at active rates into 2026. If you needed it, it would have a job. If you need a hold, put it in GCS nearline and delete the BQ copy.

Incremental dbt models that full-refresh weekly recreate bytes and reset long-term eligibility. A table that should have been cheap long-term storage stays on active rates because you rewrote it for fun. Full refresh is a storage event, not just a compute event.

Day 1: dump TABLE_STORAGE + dataset billing model. Price logical vs physical per dataset including time travel. Rank stale tables >100 GiB with no references. List heaps >100 GiB with no PARTITION/CLUSTER.

Day 2: switch one dataset where physical (or logical) wins by a clear margin. Tell platform. Do not switch the heavy-MERGE dataset yet.

Day 3: owner-confirm the top stale table. Snapshot to GCS if needed, then drop. Do not cluster it.

Day 4: design review for the largest active table with no layout: partition column from real filters, or cluster-only. Schedule the copy, and avoid running it Friday at 4pm unless you have done this before.

Days 5 through 7: re-scan. Storage optimizer $/mo should move on the switched dataset, the stale list should shrink by one, and layout advisory stays open until the copy finishes, which is expected.