How to Choose a Platform When Migrating Your Data Warehouse: Pain Points, PoC and TCO

Enterprise analytics grew for years inside the on-prem enterprise data warehouse: a Kimball-style model, ETL packages, maybe cubes. As the batch window shrank, the heavy jobs drifted to Hadoop or Spark. The result is a typical hybrid: two places, two numbers, two operations teams. Moving to the cloud is not about lifting this picture as it is. It is about building one source of truth, a cost you can predict and an operation you can sustain.

This article looks at three paths: the classic cloud data warehouse (BigQuery, Snowflake), the data lake or lakehouse (Databricks, Delta, Iceberg), and hybrid layers. Which one fits your workload does not become clear without a proof of concept (PoC) and a total cost of ownership (TCO) calculation.

The target does not have to be the cloud. A new-generation on-prem platform, an appliance for example, is also a candidate. Put cloud and on-prem options through the same scenarios and the same scorecard. Deciding “we are going to the cloud” before the PoC narrows your options from the start.

Decision path in short: on-prem → list the pain points → PoC with 2–3 candidates on the same scenario → 3-year TCO → reference architecture. The fastest path and the lowest budget are often different answers; look at both on the same scorecard.

Pain points first, product second

Vendor presentations usually start with the product. In the field, the work starts with the pain point.

The nine items below are examples. In your environment some will drop out and others will be added. What matters is writing the list down and tying every row to a response. Several responses mention an ADR, an architecture decision record: a short, dated note of what was decided and why.

Area	Pain point	What it tells you	Response
Architecture	Run times keep growing with data volume	You are approaching a scaling limit	A scalable architecture; measure with growing volumes in the PoC
Architecture	Layers are tangled together	One change touches everything	A layered model; owners and names recorded in an ADR
Architecture	The same number differs from place to place	Two places, two numbers	A single source of truth; a shared Core model
Operations	Batch no longer fits the night	A bigger cluster only buys time; the root cause stays	Incremental loads; split the job into parts
Operations	Hardware or software is near end of support	Renewal is mandatory and expensive	A platform decision instead of a renewal; PoC and TCO
Operations	The old system stays open	The dual-run calendar eats your time	A closing checklist, recorded as an ADR
Governance	Errors are found after the report goes out	Trust is broken	Assertion and test gates in the pipeline
Governance	Changes cannot be traced	There is no history of code or data	A code repository; versioned data
Governance	No glossary, catalog or quality tooling	Nobody knows who holds what, where	Catalog, lineage, quality gates

A pain point is not one sentence; it is a list. What matters is that every row has a response: which layer, which decision, which PoC measurement closes the item. An item without a response is still there after you move.

What eats the calendar is rarely a failing job. It is the old system left open “just to be sure”. The closing checklist is short: one date field, one grain, a deviation threshold, a signature. Moving to BigQuery without that signature produces a second copy; dumping everything into a lake produces uncontrolled growth.

Three target architectures

Cloud data warehouse (SQL-first): modeled tables, nightly batch. BigQuery + Dataform, Snowflake + dbt.

Lakehouse: raw and processed data in files; Spark, ML. Delta or Iceberg, Databricks, Dataproc.

Hybrid (medallion): bronze in the lake, gold in the warehouse. The realistic target for most large enterprise migrations.

When leaving on-prem, the question is not “warehouse or lake”. It is three questions: is most of the transformation SQL or Spark, is the latency overnight or minutes, and which team holds the skills?

Typical flow: on-prem enterprise data warehouse (change data capture or batch) → Landing → Lake (bronze/silver) → Gold warehouse (BigQuery, marts) → BI. Spark and ML workloads can stay in the lake; reporting sits in gold.

Layered warehouse and medallion

“We are moving to BigQuery” often really means moving a layered model. Medallion is a separate vocabulary. Mix the two up and the PoC runs long and the copies multiply.

Layered warehouse: Landing → Staging → Core → Data mart

• Landing zone: raw intake; replay and audit.
• Staging: cleaning, joining, light transformation.
• Core: modeled enterprise data, shared dimensions, the single source of truth; closed to consumers.
• Data mart: the consumption grain of one business unit; BI is fed from here.

Why keep Core separate from the marts? A mart holds a wide, business-unit-specific table where stock, sales and price sit side by side. In Core, stock, price and sales live in separate tables at the same grain, and Core is not exposed. This is the approach I recommend; in some architectures the silver layer is also consumed directly. Whichever you choose, write it down in an ADR. The payoff: when only prices change, the whole structure does not re-run. Only the related datasets and the marts that depend on them are processed. You gain speed and cost.

Advantages: the Kimball vocabulary of the existing warehouse is preserved; it fits nightly batch; partitions and retention give you FinOps (cloud cost control) per layer; Dataform or dbt bring versioning and assertions.

Medallion: Bronze → Silver → Gold

• Bronze: append-only raw data (lake or object storage).
• Silver: deduplicated, conformed, quality-checked.
• Gold: the consumption layer (the data mart role): BI grain, KPIs, mart tables.

Advantages: semi-raw data and Spark/ML stay in bronze and silver; you get a cheap archive plus a quality gate in silver; gold can overlap with BigQuery gold datasets.

Mapping the two vocabularies

Is gold the data mart? In role, yes. The layer that feeds reports in medallion does the same job as the data mart in a layered warehouse. Core is different: it is integration and the single source of truth. In a hybrid it usually maps to the shared models across silver and gold.

Roughly:

• Landing ≈ Bronze
• Staging ≈ Silver
• Core ≈ Silver/Gold integration (shared dimensions, conformed facts)
• Data mart ≈ Gold (consumption)

Dataset names can differ; the goal is a shared vocabulary. More layers bring discipline; without discipline they only produce more expensive copies. Add a line to the PoC scorecard: “Are layer owners and names recorded in an ADR?”

PoC: the fastest learning at the smallest risk

The purpose of a PoC is not a purchase slide. It is to eliminate the wrong path early.

1. Choose scenarios (at most three). You do not need to move everything. Pick the worst statements of the worst job: the heaviest nightly job, the most contested report, or the integration that creates the most copies. Cover different profiles such as bulk insert, update, complex select and window functions. Run each at small, medium and large data volumes; the ranking of candidates can change with volume.
2. Same input, same output. Write down the grain and the acceptance criteria: row counts, totals, a sample join.
3. Two or three candidates, same duration (two weeks). For example BigQuery + Dataform, Snowflake + dbt, and a Databricks lakehouse.
4. One scorecard. Development time, PoC environment cost, query latency, number of operational moving parts, team fit, security and governance. Also: is the flow clear, is there a cost ceiling, who owns operations, is lineage in place? Add a line for who you will talk to when something breaks (see below).
5. A decision, not a go-live. The winning pattern becomes the reference architecture; the rest follows in waves.

Ideas that shine in a PoC and fall apart in production usually fail for the same reasons: an unclear data flow, no cost ceiling, no owner, traceability added late.

Performance and cost eliminate; the counterpart decides

In a PoC, candidates are mostly eliminated on the balance of performance and cost. The question that settles the final choice is often a different one: when something goes wrong, who do we talk to? When a query falls apart in production or the bill grows unexpectedly, a counterpart you can reach in the same time zone and the same language directly reduces operational risk. That is why a provider’s local office, support team and partner network should carry real weight in the decision table.

Turn this into explicit scorecard lines: local office, support channel and response time, local partner ecosystem, consultant quality. Otherwise the decision stays a “feeling of trust” in the meeting room.

Three lessons from the PoC

1. The ranking can change with data volume. A candidate that is fast on small data can fall behind at large volume. Run the PoC at small, medium and large volumes; a result from a single volume is not enough for a decision.
2. Reading files from outside is not the same as keeping the table inside. A setup where data sits as files and is read externally looks easy, but query time and cost change. Measure both models in the PoC.
3. Single-user tests mislead. Run the same queries under parallel load too, and calculate cost for a realistic daily load rather than per query.

A short list of options

Profile	Usually fits	Watch out
SQL, nightly batch, many reports	BigQuery or Snowflake + Dataform or dbt	Partitions and slots; TCO can swell
Spark, ML, semi-raw data	Lakehouse	Without gold, the lake falls apart
Already settled on Google Cloud	BigQuery-centered hybrid	Multi-cloud doubles operations
Microsoft 365, Fabric, Power BI	Fabric / OneLake	If you have SSAS cubes, the semantic layer can be preserved
Streaming (events, sub-minute latency)	Pub/Sub + Dataflow, or Flink	Landing append or direct to silver/gold; same Core as batch

“BigQuery or Snowflake” is often a single row in this table. The real split is whether you need a lakehouse and where you already sit in the cloud.

TCO: the lowest budget does not fit in one line

Calculate at least three years, with these items:

• License and capacity: the existing warehouse and Hadoop baseline. If an on-prem target is also on the table, new hardware, licenses and disaster recovery go into the same table; in the cloud, consumption takes their place.
• Cloud consumption: storage, slots, pipelines, egress.
• Migration: dual-run, testing, training.
• Operations: platform team, vendor.
• Risk: delayed reports, quality, regulation. Put it on the scorecard, even if only as a ranking.

If dual-run and the migration project are forgotten in year one, “the cloud is cheap” misleads. A PoC scales the bill to production volume; TCO asks when the old license can be closed.

The fastest migration looks like lift-and-shift; the lowest TCO often needs some remodeling. The PoC shows where the two overlap.

Going to production: two systems together, closing with a signature

Once the platform is chosen, the question becomes when you can switch the old system off. The order I recommend:

1. Run both systems together. Feed the old and the new platform from the same source.
2. Run a gap analysis. Compare the same reports and tables on both sides, list every deviation and close them one by one.
3. Open user acceptance testing (UAT) to the business units. The people who use the numbers verify their own reports on the new system.
4. Retire the old system once you are fully sure. Only from that point does the new system become the single source of truth.

Dual-run is a cost item in the TCO, and it gets more expensive the longer it lasts. So write the closing criteria up front: a deviation threshold, the business units that give UAT approval, and a date. The timeline can stretch; the closing condition should not be vague.

Decision order

1. Latency: overnight or minutes?
2. Source: files, change data capture or events?
3. Transformation: SQL models or Spark?
4. Correctness: does the flow stop when a rule breaks?
5. Counterpart: who do you reach when something goes wrong?
6. Money: the three-year TCO.

Once the order is written down, you get a short list; the PoC picks the winner; the TCO gets finance approval.

A Google Cloud example map

The same logic can be built on other clouds; Google Cloud is the example here.

• Nightly batch, SQL, layered warehouse: Datastream or batch load → Landing → Staging → Core → Data mart (BigQuery datasets); Dataform + Composer + assertions.

• Streaming: Pub/Sub → Dataflow (Beam) → BigQuery. There are two paths:

  • A. Landing append: the raw event is written to landing, and silver/Core is processed by the nightly batch. Audit and replay are easy; latency is about one batch.
  • B. Direct to silver/gold: after validation and deduplication in Beam, the event is written at the target grain. This path is for sub-minute decisions, and a schema contract and quality checks are a must.

  Both paths join the Core model produced by the nightly batch. If the stream opens its own mart, you are back to two numbers.

• Governance: Dataplex + Dataform assertions + Cloud Build.

If there is a lot of Spark in production, Dataproc stays for a while. Work that is SQL by nature stays in SQL; the lake takes over feeding and archiving.

From on-prem to BigQuery, or to the lake first?

In most organizations the practical answer is hybrid. Running the same scenario once on both layers during the PoC ends the ideology.

Three questions to close with: Can you list your pain points and write a response to each? Did you measure in the PoC? Did you show year three in the TCO?

---

The views in this article are my own.