Moving a 60 TB Warehouse to BigQuery Without Missing a Single Month-End Close

A warehouse migration is rarely hard because of the volume. It is hard because the business does not stop while you move it. Here the nightly batch already misses its deadline, the hardware runs out of support in 14 months, and month-end close tolerates no wrong number. There is no weekend in which 2,800 tables and 40 downstream systems can be moved and, if needed, moved back.

This is a fictional scenario. It does not describe any real company’s systems.

The problem

In this scenario I am the Principal Data Architect at a fashion retailer with 1,200 stores in 40 countries and an e-commerce channel. The warehouse is an on-premises SQL Server (60 TB, 2,800 tables, 1,900 SQL Server Integration Services, or SSIS, packages) plus a 12-node Hadoop cluster for the heaviest jobs. The nightly batch takes 9 hours and misses the 07:00 store deadline. The hardware is out of support in 14 months, the license renewal costs more than the cloud budget, and the board has approved BigQuery.

Around the warehouse, 2,500 users rely on 600 Power BI reports, and 40 downstream systems read its tables directly. The data team is 16 people, half of them SQL Server specialists who have never used the cloud.

The eight requirements:

• R1. Migrate 60 TB and 1,900 packages in 12 months with no reporting outage; month-end close runs on the old or the new platform, never on both with different numbers.
• R2. Nightly batch under 3 hours; the 07:00 store deadline is met every day, during and after migration.
• R3. Every migrated table is proven equal to the legacy table (row counts, sums, hashes) for at least 2 closed accounting periods before switch-off.
• R4. Predictable cost: a monthly budget per domain, no unbounded query, a chargeback report for finance.
• R5. The 40 downstream systems keep working; some need a SQL Server-compatible interface for a transition period.
• R6. A layered warehouse (landing, staging, core, mart) with versioned SQL, CI/CD and automated quality checks; no hand-run scripts in production.
• R7. GDPR: pseudonymized customer data in analytical layers, EU residency, retention per table, an audit of who queried personal data.
• R8. SQL Server specialists productive within 3 months; no external consultants after month 6.

The constraint that shapes everything

One sentence decides the architecture: the business does not stop during the migration, so month-end close and the 07:00 store report must work every day, and that makes a big-bang cutover impossible. This forces a table-by-table, dual-run migration in which the legacy platform keeps running as the reference and nothing is switched until equality has been proven over two closed periods. The second constraint is cost: FinOps, the discipline of managing cloud spend, is an output of the first sprint, not a report added at the end.

The design at a glance

Read the design from left to right, the way I would draw it on a whiteboard.

On the left, the sources, Hadoop and the legacy warehouse stay untouched, and the legacy warehouse remains the reference until it is retired. Two paths leave them. Debezium streams changes through Kafka as Avro, with a log sequence number (LSN) watermark per table, and a bulk load moves the 60 TB once per wave into an immutable Cloud Storage landing bucket in the EU.

In the middle, BigQuery holds landing, pseudonymized staging, a Data Vault 2.0 core, dimensional marts and a legacy view schema with the old table names, all built by Dataform and Composer.

Underneath sits the migration control plane. It owns the SSIS dependency graph, the wave planner, the reconciliation of counts, sums and hashes on both sides, and the cutover switch with a close-owner flag per period. A compatibility service reverse-syncs to an on-prem SQL Server compat database for the 15 consumers that still need T-SQL.

Solution overview

Every component follows from the dual-run constraint.

Ingestion

The sources stay where they are. Debezium on Kafka Connect reads the SQL Server change data capture (CDC) tables, snapshots first, then streams, with an LSN watermark per table; that watermark is the line that both the nightly run and the reconciliation read up to. Kafka sinks hourly Avro files into the landing bucket per table and day. Historical data moves once per wave by bcp and distcp over a 10 Gbps Interconnect, about two days for the 60 TB.

Transformation: Dataform

The transformation layer is Dataform. A model is a SELECT and an assertion is a SELECT, so the eight SQL Server specialists keep writing SQL after a six-week program on BigQuery SQL, partitioning, clustering and Git. Java lives only in the three-person core team that runs Kafka and Debezium. Every model carries assertions, every change is a reviewed pull request, and production runs only from main through CI/CD.

Core: Data Vault 2.0

The core layer is Data Vault 2.0 rather than a direct dimensional model. The ERP, POS and e-commerce sources keep changing for twelve months. In a vault, a source change touches only a satellite; the marts and the 600 Power BI models are untouched, and reconciliation stays traceable per source.

Orchestration and the migration control plane

Cloud Composer (managed Airflow) runs the nightly DAG per domain and the wave DAGs. The migration control plane is a set of Quarkus services on Cloud Run with Cloud SQL for state and evidence: a dependency graph service that parses the 1,900 SSIS packages, a wave planner, a reconciliation service and a cutover switch per table. I rejected commercial SSIS translation because it carries every package’s technical debt. I expect the graph to find about 30 percent dead or duplicate packages; the remaining 1,300 flows become roughly 600 Dataform models, written per flow, not per package.

Consumers keep their old names

A legacy view schema in BigQuery exposes every old table name over the marts, so Power BI reports change their connection string, never their model. For the 15 consumers that need T-SQL, a nightly reverse sync feeds the existing, already licensed SQL Server compat database. Usage of both is logged per consumer; a table with no reader for 30 days is renamed, then dropped two periods later. Retirement is decided by data, not by a date on a slide.

How the guarantees are kept

Every table passes through five stops: legacy run, new run, reconciliation, cutover decision, retirement. Each guarantee below names its mechanism.

No reporting outage: the dual-run

The SSIS batch runs unchanged every night and is the reference. The new run reads the same business day from the same 02:00 cut, and the legacy comparison query filters on the same LSN or cut timestamp the CDC watermark used, so both sides compare the same slice. A change that reaches BigQuery late lands in tomorrow’s partition flagged as late data, never silently merged into a closed period. Loads are idempotent: MERGE by business key plus load timestamp into the satellites, partition-level replace for the marts, so a rerun yields the same table.

Proven equality: the reconciliation service

Every night, for every table in dual-run, both sides compute the row count, the sum of every numeric column and a SHA-256 hash of the canonical row per partition (ISO UTC dates, fixed-scale decimals, NULL as a literal; HASHBYTES on SQL Server, SHA256 on BigQuery). A difference is classified as timezone, rounding, NULL semantics, collation, late data or a real bug. Each class has a written rule, and the rule goes into the Dataform transform, never into the result. Evidence is stored per table and period, and finance signs it off for the finance mart. No cutover before zero diff over two closed periods.

One close owner: a flag

Before a period opens, the cutover switch records a single close-owner platform per table group. Only the owner publishes: legacy views and Power BI connection strings point to it, and the other platform’s output is a shadow dataset that nobody outside the data team can read. Rollback is one flag, and both connection strings are kept for two periods. If the platforms disagree on a month-end night, the owner’s number is published, the diff is fixed in the transform, and that period does not count as zero diff.

The 07:00 deadline: capacity and a watermark alarm

The cut is 02:00, the Dataform DAG finishes by 04:30, and the earliest store deadline is 05:00 CET. The pilot measured 220 slot-hours for 180 models. At 600 models that is about 730 slot-hours, which 500 slots clear in about 1.5 hours; I plan for 2.5. The slot reservation guarantees capacity at 02:00; month-end gets an autoscale cap of a further 500 slots, with finance first. CDC lag per table raises an alarm 30 minutes before the cut; a late table runs with its last watermark and is marked stale, which the report banner shows. The Hadoop jobs move in wave 1, so the legacy batch shrinks while it still runs.

Predictable cost: reservations and byte caps

One project per domain carries a monthly budget. The 500-slot baseline on a one-year commitment is a fixed monthly cost, known before the first query runs. On-demand is off for users, and every job carries a maximum_bytes_billed of 1 TB for users and 10 TB for pipelines. A query above the cap does not run, and a query that does not run does not bill. Labels on every job feed the monthly chargeback report to finance.

Deployment, security, regulation

Projects and network

On the Google Cloud side there is one organization folder in the EU multi-region, with a project per function and domain: ingest (Kafka, Debezium, the landing bucket), core (BigQuery layers, Dataform, Composer, reservations), four mart projects for finance, merchandising, supply and e-commerce with their own budgets and slot shares, and migration (the control plane, Cloud SQL, Keycloak). The project is the cost and access boundary. Everything sits inside one VPC Service Controls perimeter with no public BigQuery endpoint. The on-prem zone lives only for the transition: CDC-enabled sources, the legacy warehouse as reference, Hadoop until month 6, the compat database until month 12.

Identity and access

Identity is IAM groups per domain, with Keycloak OIDC for the cockpit mapped to the same groups; there are no user-level grants. Personal-data columns carry policy tags from staging onward; only the pseudonymization job and the data protection officer (DPO) group read raw columns in landing. Org Policy enforces EU locations, no public IPs and customer-managed encryption keys (CMEK).

GDPR

For GDPR, the data minimization principle in Article 5 is met by pseudonymizing customer identifiers at staging with a per-subject key in Cloud KMS; raw identifiers exist only in landing and are deleted by a 30-day lifecycle. Article 17 erasure is key destruction plus landing expiry: the customer satellite is purged, the marts are rebuilt from core partition by partition, and evidence is kept. Article 30 records of processing stay with the DPO, and BigQuery Data Access audit logs answer who queried which personal-data column and when. Retention is a partition expiry per table in the Dataform configuration: landing 30 days, staging 90 days, core by legal class, marts by domain policy.

Monitoring and resilience

One dashboard shows the reconciliation diff, batch end time against 07:00, bytes per domain against budget, CDC lag and legacy-view usage per consumer. Resilience rests on nightly snapshots before each wave load, 7-day time travel, a replayable landing zone and a legacy platform that stays warm until retirement. A bad night on BigQuery during dual-run costs nothing, because the owner publishes.

What I would be asked in the interview

1. Why not a big-bang cutover over one weekend? There is no rollback path for 2,800 tables and 40 consumers, and the close does not tolerate a single wrong number. A wave-by-wave dual-run proves every table over two closed periods and makes rollback a flag change.
2. What if the reconciliation diff never reaches zero? The legacy table lives on as close owner. Most diffs come from timezone, rounding, NULL semantics and collation; each gets a rule in the transform and is proven again. A genuine legacy bug is decided with finance and documented; the result is never patched.
3. A user runs SELECT * on a 60 TB table; what does the bill look like? Nothing happens. User queries carry a 1 TB maximum_bytes_billed, so the query is rejected before it starts and produces no charge.

Open points

• Which 15 of the 40 consumers genuinely need T-SQL, and for how long? That decides the life of the compat database and the reverse sync.
• Does finance accept the new platform as close owner from wave 5, or does it want a parallel close first? That can extend a wave by one period.
• SQL Server versions and CDC permissions on the POS feeds in 40 countries: Debezium needs CDC per database; older versions may need a log-shipping replica.

The verdict

In one line: legacy stays the reference, every table is proven equal with counts, sums and SHA-256 hashes over two closed periods, exactly one platform owns each month-end close, and cost is capped before the first query runs.

Which part of your last migration would have failed the two-closed-periods test?

---

Architecture Katas, part 1 of 16. This series works through fictional, interview-style design problems the way I would on a whiteboard. Next in the series: Omnichannel Order and Inventory Orchestration.