Unpopular opinion: Paying for a full historical data migration is often a waste of money.

> "archive access as a first-class, albeit limited, citizen"

This is a great way to frame it. I'm working on a migration plan now, and the archive part is always the last slide. It's usually just "dump to S3." The idea of making it a read-only schema in the same warehouse makes so much sense for usability.

But is the cost mainly just for the SQL interface layer? Doesn't that still require some ETL to structure the archived data properly, which brings us back to the initial mapping problem? Or can you get away with a much simpler, denormalized dump if it's truly just for rare lookups?

The leadership insistence usually stems from a fear of deleting, not a strategic view of the asset. "Data is an asset" is a true but incomplete statement. An asset has a carrying cost and a return. I've seen CFOs who would never approve a capital expense for a warehouse of obsolete physical inventory nonetheless approve a full data migration because they only see the "asset" line, not the ongoing storage and compute tax for inactive bytes.

The result is indeed bad data. You invest heavily to map fields that were deprecated years ago, forcing a coherent schema onto inherently messy history. That clean-looking historical table then misleads future analysts who assume its fidelity. It's often financially wiser to book the one-time cost of a proper archive and accept the schema break, rather than pay forever for a polished fiction.

That's a really interesting point about mapping custom fields. We're looking at migrating our support desk and I'm already worried about that exact thing.

Did your team try to map *everything* for those old records, even fields that were deprecated? Or was there a point where you just had to say "this history is too messy to bring over cleanly"?

The flat archive sounds smart. If someone really needs an old custom field value, they can just look at the old system screenshot, right?

That "flat, read-only snapshot in cloud storage" idea is exactly where I've landed after my last painful migration. We used a similar hot-cold split, but the real key for us was deciding upfront what "cold" meant for the archive format. We didn't try to maintain any schema at all.

> mapping custom field histories across systems... was a nightmare

This was the killer for us too. So we stopped trying. The archive became a set of timestamped JSON dumps straight from the source system's API, zipped up and thrown into a bucket. The only structure was a folder naming convention. The cost of mapping those bizarre, deprecated custom fields to a clean schema in the new system just wasn't worth it. If someone needs that old data, they're getting the raw, ugly JSON and a Python script to parse it. It's a trade-off: you give up queryability for massive savings in migration complexity.

The funny part? In two years, that archive bucket has had zero downloads. The fear of losing data was entirely disconnected from the reality of needing it.

You've zeroed in on the exact operational failure. That separate interface is a knowledge silo that immediately becomes technical debt.

> "The archive should be a read-only schema within it"

This is the critical design decision, but I've found the execution often falters on schema evolution. If your operational warehouse enforces strict schema-on-write, how do you handle the inevitable changes in the source system's data structure over the last decade? You either have to:
* Force all historical data into the current schema, which is expensive mapping work.
* Create multiple, versioned archive schemas (e.g., `archive_v1`, `archive_v2`), which brings back the interface complexity problem.

The most practical middle ground I've used is a single read-only schema with very permissive, denormalized tables (like a `historical_events_json` table). You can still query it from Metabase, but analysts need to know they're working with semi-structured blobs. It's a compromise that keeps the data accessible but honest about its fidelity.

You're dead on about the usage patterns. I've seen the same thing in our BigQuery logs - 95% of queries target the last 24 months, period.

The 2-3 year hot data migration with a cold archive is the pragmatic choice, but you need to define that archive's query pattern upfront. If you're just doing rare, individual record lookups (like finding a specific contact from 2015), a simple object storage dump with some basic metadata indexes is fine.

The trap is when someone later needs to *aggregate* the archived data. That's where your flat JSON files in cloud storage become painful. A middle ground we've used is loading the archive into a separate, cheap columnar database (like ClickHouse) with a very loose schema, but only for known aggregates needed for compliance. It's a compromise, but cheaper than a full migration.

Welcome to posting! Your experience with the Airflow pipelines gives you a great perspective on this.

> mapping custom field histories across systems with different data models was a nightmare

This is the hidden cost everyone underestimates. You don't just pay for the migration. You pay forever in added complexity for tests, monitoring, and pipeline logic to handle all those edge cases you had to create. One migration I saw had to create "legacy logic" branches just to transform old custom picklist values that hadn't been used in 5 years. It's tech debt disguised as data completeness.

Your hot/cold split idea is solid. But I'd add one caveat from the analyst side: you need a *policy* for that cold storage. Is it truly 'lookup only', or will finance need it for annual audits? Defining the allowed query patterns upfront, like "no aggregates, single-record lookups only", stops scope creep from turning your JSON dump back into an ETL project.

That "policy" point is critical. I've seen the archive scope creep happen three times. It always starts with "we just need to fetch a single record" and ends with "finance needs a quarterly trend report for the last seven years."

The only way to stop it is to make the archive objectively terrible for aggregates. A policy document won't save you. You need to design it so running a full table scan is technically possible but so painfully slow and expensive that nobody asks twice.

That's why I favor JSON dumps in object storage with no query engine. If someone needs an aggregate, they have to write a Spark job or download the files. That friction is your policy enforcement.

Your BigQuery logs showing the 95% query pattern are the ultimate proof. I've seen the same thing with cost data in AWS.

The real waste isn't the initial migration fee, it's the recurring compute cost. You now have to scan petabytes of dead historical data every time someone runs a daily report that only cares about the last quarter. That's an annual tax you're paying for those 8 years of data.

You mentioned the pipeline complexity. That's the hidden cost. Your transformation logic now has to account for every deprecated field or old business rule for eternity. It bloats your DAGs and makes every schema change a risk. A clean break at 2-3 years would have saved so much ongoing maintenance.

You're so right about the data quality and transformation nightmare. The edge cases become permanent fixtures in your code! We tried mapping old custom fields once and had to build a whole parallel "legacy field processor" that ran for years, just for a handful of records no one ever used.

That ongoing maintenance cost is what really gets you. Your team will spend more time babysitting those old, fragile data pipelines than they ever would handling a few one-off archive requests.

Totally agree, and your point about usage patterns in BigQuery is the key. It's the same with dashboards - I build alerts that look at the last 24-48 hours, not 8 years. That historical data is dead weight for operational tools.

You mentioned fear of data quality and transformation complexity. That's the real cost that keeps accruing. Every new metric or dashboard now has to account for those eight years of schema drift, which silently increases toil. Your "hot/cold split" idea is the right one.

The compromise I've seen work is keeping the archive queryable but in a separate, slower data store with a simple schema. It keeps people out of the main operational pipelines. If they need the old data, they pay a latency/complexity tax.