Archiving old marketing data without deleting it

Start with the numbers, because archiving conversations almost always begin with an assumption that storage is expensive. BigQuery's pricing page lists active logical storage at $0.000031507 per GiB hour, with the first 10 GiB free each month. Over a 730 hour month that is about $0.023 per GiB.

So 500 GiB of raw event history costs roughly $11 a month to store. Querying that same 500 GiB once, at $6.25 per TiB, costs about $3.05. Twelve dashboard tiles reading it twice a day cost $73 in a single day.

That ratio is the whole argument. Storage is the smallest line on a marketing warehouse invoice and query volume is the largest, so an archiving project that reduces storage and leaves query patterns alone is optimising the wrong number.

It also means the honest goal of archiving is rarely cost. It is keeping the working set small, keeping obligations about retention, and making sure nothing large is accidentally reachable by a dashboard.

BigQuery tiers automatically. Long-term storage covers any table or partition that has not been modified for 90 consecutive days, the price drops by approximately 50 percent, and there is no difference in performance, durability or availability.

Nothing moves and nothing changes. The data stays queryable at the same speed, in the same table, under the same name. There is no restore step because there was no archive step.

The detail that matters is that it applies per partition. An unpartitioned table that gets a nightly append never qualifies, because modifying the table resets the clock on the whole thing. A partitioned table gets the discount on every partition older than 90 days while today's partition is still being written.

That is a quiet argument for partitioning that has nothing to do with query cost, on top of the ones in partitioning marketing tables so queries stay cheap. Partition an archive-shaped table even if nothing queries it by date.

Beware of anything that touches old partitions on a schedule. A nightly job that rewrites the last ninety days to be safe resets the clock on ninety days of data every night, and none of it ever reaches the discounted tier. Reload the window the platform actually restates, not a comfortable round number.

The first row wins more often than anyone expects, and the reason it gets skipped is that doing nothing feels like a non-answer in a planning meeting. It is the correct answer for a warehouse under a few terabytes.

The second row is the one that actually reduces future cost, because it shrinks what a careless query can reach. Rolling three years of event-level data into daily aggregates by campaign turns hundreds of gigabytes into a few hundred megabytes, and the aggregate answers ninety percent of the questions anybody asks of old data.

The fourth row is deletion wearing a gentler name. Partitioned tables support a partition expiration that removes partitions past a given age automatically, and it is the right mechanism when a retention obligation exists. It is the wrong mechanism for saving money, because it destroys the ability to answer a question later.

Cold object storage is much cheaper per byte than warehouse storage, so at real scale the export path pays. Cloud Storage classes run from Standard through Nearline, Coldline and Archive, with minimum storage durations of 30, 90 and 365 days respectively and retrieval fees on all three.

Those minimum durations are the part people miss. An object deleted or rewritten before its minimum duration is still billed for the full period, so a monthly job that overwrites Archive-class files is paying a year of storage every month.

The bigger cost is not money. Data in Coldline is not queryable. Answering a question about 2024 becomes an export, a load, a query and a cleanup, which is half a day and a decision about where the loaded copy lives. Nobody does that for a client question, so in practice the question goes unanswered.

So export when the driver is scale or a residency obligation, and be deliberate about format. Avro or Parquet preserve types and nested structure in a way CSV cannot, which matters enormously if the point is to be able to reload it. The constraints are in getting data back out of BigQuery.

If you do export, write down the reload procedure and test it once. An archive nobody has restored is a backup nobody has tested, and the two fail for the same reasons: a schema that no longer matches, a bucket somebody tidied up, a format decision made in a hurry on a Friday.

Before choosing a mechanism, get the actual requirement written down. How long does the client need to be able to answer questions about, what has been promised to their customers about personal data, and what does the contract say happens on termination.

Those three answers rarely match. A client wants five years of trend data, their privacy commitment implies deleting identifiable records much sooner, and nobody has reconciled the two. The reconciliation is usually simple: keep aggregates for years, keep identifiable detail for months.

That split is also the cheapest design. Aggregates are tiny and permanently useful. Raw event data with identifiers is large, sensitive, and rarely queried after a quarter, so it is the obvious candidate for expiration.

Write the rule per table rather than per warehouse. Raw ad performance can live forever because it holds nothing personal. Raw form submissions probably should not. Table and dataset default expirations let you set that once rather than remembering it.

The real risk with long history is not what it costs to hold, it is what it costs when something reads all of it. A dashboard tile with no date filter over a five-year table is the entire cost problem, and it does not care whether the old partitions are on the discounted tier.

So pair any retention decision with the two controls that make history safe: require a partition filter on the large tables, and make sure nothing outside the marts layer is ever read by a dashboard. That pairing is the substance of keeping a BigQuery bill boring.

The reporting layer should hold its own history separately, pre-aggregated, small, and fast to read for a five-year trend. Then a long history is a feature rather than an exposure, and the decision about whether that layer is a view or a table is worked through in views or tables for the reporting layer.

Google's guidance on optimising storage for query performance covers the physical side of the same idea, and the practical version is one sentence: shrink what a query can touch, not what you keep.

For an operator running per-location quarterly reporting across more than fifteen clinics, five years of comparable history is genuinely valuable and costs almost nothing to hold as aggregates. Deciding that at build time rather than during a cost review is part of what a marketing data warehouse engagement is for.

Review it annually, on a date, with the client. Retention is one of the few warehouse decisions that changes for non-technical reasons, and finding out about a new obligation during an audit is a great deal more expensive than a fifteen-minute conversation once a year.