The Four Ghosts in Your Data Warehouse (And How to Evict Them)

Forgotten copies, full price.

Pull up a chair, grab some chai, and let me tell you about a quiet disaster waiting in your cloud bill. $9,847. In exactly 22 seconds. That is what running a single bad query three times cost on Google BigQuery (Google’s managed cloud data warehouse—basically a massive analytical database built to crunch terabytes of reporting data in seconds). This was documented over in Panoply’s BigQuery pricing guide. The engineer even added a LIMIT clause, thinking it would keep things small and cheap. It did not save them a single rupee. Why? Because on BigQuery’s on-demand analysis pricing model ($6.25 per TiB scanned, where a TiB or tebibyte is roughly 1,024 gigabytes), Google bills you for the sheer volume of data the storage engine reads off disk to answer your question—not the tidy ten rows printed on your screen.

That is the loud, dramatic way data warehouses burn your budget. An alert fires, your VP of Engineering gets a heart palpitation, and everyone rushes into a Slack war room. But there is another leak, my friend. It is ten times slower, completely silent, and far more lethal over a financial year.

Open your warehouse console right now and look at the total number of tables. Now check how many of those tables received an actual query from a human or an automated dashboard in the last thirty days. The difference between those two numbers is what I call a monthly charity donation to your cloud provider—made automatically in your company’s name, with zero tax write-offs, compounding silently every month.

Here is the truth: your warehouse bill is not high because raw cloud storage is expensive. Storage is ridiculously cheap. Your warehouse bill is high because forgetting is expensive. Almost every production warehouse on earth is an uncurated museum of things someone meant to clean up “next sprint.”

The four ghosts in your warehouse

Whenever an enterprise warehouse bill spirals out of control, the mess always breaks down into the exact same four culprits. Learn their names, because your billing dashboard already knows them intimately:

• Forgotten tables — What they are: one-off data pulls, abandoned machine learning experiments, and leftovers from pipelines that were migrated two quarters ago. How they get there: an engineer says, “Let me save this intermediate output table for a few days just to be safe,” moves to another project, and nobody touches it again. The fix: audit last-queried dates, verify owners, and drop them.

• Copy sprawl — What it is: tables named events_backup_before_migration, events_backup_v2, and twelve other cousins sitting in production. How they get there: fear. Before running an update or schema alter, an engineer makes a full copy of a multi-terabyte table by hand, completes the release, and forgets the duplicate. The fix: automated point-in-time snapshots with automatic expiration instead of manual copy-paste tables.

• Zombie snapshots — What they are: backup table snapshots preserved “just in case” someone asks for historical parity. How they get there: the team created snapshots without setting a time-to-live (a mandatory auto-delete date), so “just in case” ends up meaning until the end of human civilization. The fix: strict retention policies that enforce auto-deletion after a fixed number of days.

• Temp tables that never expired — What they are: staging tables and raw buffer dumps created by ETL pipelines (Extract, Transform, Load—the automated jobs that pull data from raw sources and clean it up). How they get there: the ingestion tool script creates temporary landing tables every midnight; no cleanup step was ever written into the code. The fix: setting a default table expiration at the parent dataset level.

Think about that one table sitting in your project right now—let’s call it events_v2_final_FINAL. You know the exact table I mean. It was spun up eighteen months ago during a weekend rehearsal for a big database migration. The migration went fine on Sunday night. The rehearsal table was never dropped. It has now survived two departmental reorganizations, three manager changes, and an entirely new data stack migration. It will outlive our careers, billing quietly every single billing cycle like an auto-debit gym membership for a gym that caught fire years ago.

Illustrative proportions — but the four ghosts are real in every warehouse. Your bill knows their names.

Storage is cheap. Forgetting is expensive. Every table without an expiry date is a subscription you forgot to cancel.

Step one: find out what nobody queries

You cannot clean up what you cannot see. The beautiful part is that your warehouse is not keeping secrets. It logs every single query job executed by every human and every service account. You just have to ask it. In BigQuery (and you can adapt this exact logic to Snowflake or Databricks query history logs), we can query the warehouse’s internal system metadata—specifically the INFORMATION_SCHEMA (the built-in administrative tables that store project logs, storage sizes, and historical jobs). We join our physical table storage inventory against 180 days of executed query logs:

-- Tables nobody has queried in the last 180 days.
-- Read-only. Run it before you touch anything.
WITH recent_queries AS (
  SELECT DISTINCT
    referenced_tables.project_id,
    referenced_tables.dataset_id,
    referenced_tables.table_id
  FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
  UNNEST(referenced_tables) AS referenced_tables
  WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 180 DAY)
)
SELECT
  t.table_schema,
  t.table_name,
  ROUND(t.total_logical_bytes / POW(1024, 3), 2) AS gb,
  t.creation_time
FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT t
LEFT JOIN recent_queries q
  USING (project_id, dataset_id, table_id)
WHERE q.table_id IS NULL
  AND t.total_logical_bytes > 0
ORDER BY gb DESC;

Here is what this SQL is doing in simple terms: the first block (the common table expression or temporary lookup named recent_queries) scans the last six months of query execution logs. It unrolls the list of every table touched by those queries. Then, we take our list of every physical table taking up storage space and look for ones that never appeared in the query logs at all. We convert bytes to gigabytes so humans can read it, and sort the biggest storage hogs right to the top.

One critical heads-up from the official docs: referenced_tables is only populated for query jobs that are not cache hits. A cache hit happens when someone runs the exact same query twice and BigQuery simply hands back the previously calculated result instantly without scanning data blocks again. If a table is only ever touched through cached results, it could theoretically appear unqueried here. So treat this output as an investigation list, not an automated drop script. Sort by gb DESC and start with the monsters at the top. The first time you run this, you will invariably find a handful of enormous tables that nobody has touched in years, each one somebody’s “temporary” something.

Do not go cowboy and delete anything yet. This query produces a list of suspects, not a hit list. You need a disciplined cleanup process before hitting drop.

Run this monthly. The output is a to-decommission list with owners — not a report to admire.

Cap the query side too

Storage ghosts bleed your wallet slowly over months; query ghosts nuke your budget in seconds. That $9,847 story is what happens when nothing stands between a runaway query and the bill. Google provides a built-in guardrail that almost nobody turns on: Maximum bytes billed. When you set this parameter, BigQuery estimates the scan size before running the query. If the estimated scan exceeds your cap, the engine aborts immediately and throws an error—without charging you a single cent. You can configure this in the web console under Query settings > Advanced options, or bake it directly into your command-line interface (CLI):

# A hard cap per query: fails instead of billing. Set it once in
# ~/.bigqueryrc so every ad hoc query is capped by default.
bq query --maximum_bytes_billed=1000000000 --use_legacy_sql=false \
  'SELECT word FROM `bigquery-public-data`.samples.shakespeare'

In this command, --maximum_bytes_billed=1000000000 caps the scan at 1 GB. If someone accidentally writes a query that would scan 5 TB of unstructured log data, BigQuery refuses to run it. Combine this with the --dry_run flag: running bq query --dry_run prints the exact number of bytes the query would process without executing it or billing your card. And drill this into your team: a SQL LIMIT clause is not a cost control on non-clustered tables. It trims the rows returned, not the bytes scanned. The byte cap is your shield; the dry run is your check.

The cheapest query is the one that never runs. The second cheapest is the one that fails its byte cap before it bills you.

Default expiry: the one-line fix nobody sets

Here is the funniest part about cloud data warehouses: the cloud providers already built the automated garbage collection feature you need, but the default setting is never expire. In BigQuery, a newly created table is configured to live forever unless someone deliberately types in an expiration timestamp. Think about that for a second. It is like checking into a luxury hotel where the front desk assumes your checkout date is the heat death of the universe unless you file a ticket asking to leave.

We fix this by setting one single parameter on the dataset level using Terraform (the standard infrastructure-as-code tool where teams define cloud resources in version-controlled configuration files). By setting a default expiration on the dataset container, every table created inside it inherits an automatic death date—unless an engineer explicitly overrides it in code:

# The fix: datasets expire tables by default.
# Tables must earn their immortality instead of inheriting it.
resource "google_bigquery_dataset" "analytics" {
  dataset_id = "analytics"
  # 90 days in milliseconds: unqueried tables die on their own
  default_table_expiration_ms = 7776000000
  labels = {
    owner       = "data-platform"
    cost_center = "analytics"
    tier        = "warm"
  }
}
# Hot tables opt out explicitly - the exception, visible in code review,
# instead of the invisible default it used to be.
resource "google_bigquery_table" "daily_active_users" {
  dataset_id = google_bigquery_dataset.analytics.dataset_id
  table_id   = "daily_active_users"
  # Far-future expiry: this table has a reason to live, stated in the open
  expiration_time = "2036-01-01T00:00:00Z"
  labels = {
    owner = "data-platform"
    tier  = "hot"
  }
}

Look at what this simple Terraform block changes. It shifts the psychology of your entire team. Previously, keeping a table forever was effortless, and cleaning up required effort. By setting default_table_expiration_ms to 90 days (7,776,000,000 milliseconds), neglect works in your favor. If a table is truly vital to the business—like our daily_active_users production aggregation—an engineer must explicitly declare an expiration_time in the codebase. That requires a pull request and code review. Immortality becomes an explicit architectural choice that has to be justified, not a silent default.

Keeping a table alive should be a decision, not an accident. Put the expiry in Terraform and let neglect do the cleaning.

Labels, owners, and the decommissioning checklist

Automated expiration protects your future datasets. But what about the terabytes of zombie data already sitting in your warehouse right now? If you run around typing DROP TABLE without a formal procedure, you will inevitably delete a table that feeds the Chief Financial Officer’s monthly revenue deck, and your weekend will be ruined. Every table needs a lifecycle classification, and every decommissioning action must follow a reliable checklist:

Every table gets a tier at birth. The default is not “live forever.”

• Step 1 — Confirm zero queries in 180 days using the INFORMATION_SCHEMA audit query we ran earlier. Do not rely on someone saying “I think we don’t need this.” Verify the hard logs. Owner: Data Engineering.

• Step 2 — Check downstream references across all connected systems: Looker dashboards, scheduled dbt runs, reverse-ETL tools, and batch export scripts. Search the query history for any service account attempting to read this table. Owner: Data Engineering.

• Step 3 — Snapshot or export to cold storage if regulatory compliance or internal caution demands a safety net. An export of compressed Parquet files dumped into an archival object storage tier costs pennies compared to warehouse active storage. Owner: FinOps / owner.

• Step 4 — Notify the dataset owners on Slack and email with a mandatory 14-day objection window. State clearly: if no team claims this table by date X, it will be dropped. Silence is documented consent. Owner: Table Owner.

• Step 5 — Drop the table (or apply an imminent expiration date) and log the drop in your team’s change log. Owner: Data Engineering.

• Step 6 — Verify the bill delta on your next monthly cloud invoice or billing dashboard. Watching the storage graph step down is how you validate your platform work. Owner: FinOps / owner.

Step 4 is the secret to staying sane. When you give teams a 14-day window and log it in a public Jira ticket or Slack message, a sudden “Hey, who deleted my table?” two months later is not an emergency incident. It is a polite link to the closed ticket. Clean communication transforms platform cleanup from an office political battle into standard engineering hygiene.

Make the cost visible per team

Here is an unavoidable human truth: nobody sweeps the floor if nobody knows who dropped the dirt. When your company’s data warehouse bill arrives as one single, terrifying lump sum charged to the central infrastructure budget, every application team learns the wrong lesson: warehouse storage is free, infinite, and somebody else’s problem.

You break this dynamic with clear cost attribution. Apply resource metadata labels (like owner = "marketing" and cost_center = "growth") directly to your datasets in Terraform. Then write a scheduled reporting query against TABLE_STORAGE_BY_PROJECT that calculates unqueried gigabytes grouped by team label. Post that breakdown into each engineering team’s channel on the first of every month. The moment an engineering lead sees their team listed at the top of a leaderboard for hoarding 40 terabytes of unqueried staging tables, those tables get deleted before lunch.

Track one core operational metric: dead-table gigabytes per team, trending down month over month. Do not waste time debating complex dollar estimates or dynamic pricing tiers. Gigabytes are indisputable facts. When the dead gigabyte count drops, your monthly invoice drops right behind it, without needing a single fancy presentation deck.

The playbook is simple: run the unqueried audit query, lock down query runaway risks with byte caps, turn on default dataset expiration in your code, tag every new table with a lifecycle tier at birth, follow the six-step decommissioning checklist, and make dead storage transparent to every team. None of this is rocket science. But when you put these six habits on autopilot, your cloud bill stops leaking money while you sleep.

So here's my question for you: what is the oldest "temporary" table in your warehouse, and who would notice if it expired tomorrow?

Disclaimer

• This post describes my personal experience with my own systems and accounts, in my own words. It is a first-person account of what I observed and what I do in my own work — not a statement of any company's intentions, policies, or internal decisions. Where I do not know something, the post says so.

• I am not affiliated with, endorsed by, or speaking for any company, product, or vendor named in this post. All product and company names are trademarks of their respective owners and are mentioned only to describe the experience accurately.

• Any error messages, documentation excerpts, or figures quoted here are reproduced from my own records or from the public sources linked in the post, believed accurate at the time of writing. Product behavior changes over time; verify against current official documentation before relying on any detail.

• Nothing in this post is professional, financial, legal, or security advice. Examples are illustrative — validate in your own environment before running anything in production.

• Corrections are welcome: if a company named here publishes an official statement, or a detail above is wrong, I will update this post and note the correction. The goal is an accurate public record of my experience, not a verdict on anyone.

© 2026 Honey Kiran. All rights reserved.

Honey Kiran · DevOps & Platform Engineering

I write about the unsexy work that keeps systems alive at 3am — monitoring, SRE, Terraform, and platform engineering.

Run the audit query on your warehouse this week, then send this guide to the teammate whose “temporary” backup table just celebrated its second birthday.

Examples are illustrative — validate in your own environment before running in production.