Enterprise Data Warehouse Migration to BigQuery

Problem

A fashion retailer with 1,200 stores in 40 countries runs an on-premises SQL Server warehouse. It holds 60 TB, 2,800 tables and 1,900 SSIS packages, and a Hadoop cluster sits beside it. The nightly batch takes nine hours and often misses the 07:00 deadline. Hardware support ends in 14 months. The board has approved a move to BigQuery. 600 Power BI reports and 40 downstream systems depend on the warehouse, and month-end finance close cannot slip by a day. The data team has 16 people, half of them SQL Server specialists new to the cloud.

Approach

The key constraint: operations never stop during the move. A big-bang cutover is impossible, so the migration goes table by table in dual-run mode. The legacy platform stays the reference. The new platform produces the same tables from the same cut-off point. A reconciliation service compares row counts, sums and hashes every night. No table switches until the difference is zero for two closed accounting periods.

Architecture

• Capture and load: Debezium change data capture through Kafka to Cloud Storage as Avro. History is bulk-loaded once per wave.
• Layers: Dataform keeps staging, core (Data Vault 2.0) and dimensional marts as versioned SQL in Git, with assertions and CI/CD only.
• Legacy packages: SSIS is not auto-translated. A dependency graph is extracted, dead and duplicate packages are dropped, and flows are split into waves by business domain.
• Control plane: dependency graph, wave planner, reconciliation service and a per-table cutover switch.
• Consumers: old table names live on as BigQuery views. A reverse sync to SQL Server serves T-SQL consumers for a transition period.

Decisions and trade-offs

• Dual run by waves, not a big bang. With 2,800 tables and 40 consumers, a weekend cutover has no way back. Month-end close cannot absorb one wrong number. Each table must show zero difference over two closed periods before it moves, and rollback is a flag. The cost is running two platforms. A period with a difference extends that wave by one more period.
• One owner per month-end close. Before each period opens, the cutover switch records which platform owns the close. Only the owner publishes. The other result is a shadow dataset that only the data team can read, and the finance mart moves as one block. The cost: finance may ask for a parallel close first, which adds a period.
• Data Vault 2.0 core, not a direct dimensional model. ERP, POS and e-commerce systems keep changing during the migration. A source change touches only a satellite, so marts and 600 Power BI models stay stable, and reconciliation stays traceable per source. Users only see dimensional marts. The cost is more tables.
• Prove equality, never patch the result. Both sides hash the same canonical string with SHA-256. A difference is classified, such as time zone, rounding, NULL handling, collation or late data, and the rule goes into the Dataform model. If the old system turns out to be wrong, finance decides which number stands, and the decision is written down.
• FinOps from the first sprint. A fixed slot reservation with an autoscale ceiling, no on-demand for users, and a byte cap on every query: 1 TB for users, 10 TB for pipelines. A query over the cap never starts and creates no bill. Labels feed a monthly chargeback report. The cost: users must narrow their queries, and DirectQuery reports push against the cap.
• SQL skills first, no automatic SSIS translation. The kata assumes about 30% of 1,900 packages are dead or duplicated. I extract the dependency graph, drop them, and write one Dataform model per flow. That halves the work and teaches the team the SQL. Java stays in a three-person streaming core. The cost is a manual rewrite.

What I would revisit

• T-SQL consumers. Which of the 15 really need T-SQL, and for how long? That sets the life of the compatibility database and the scope of the reverse sync.
• Finance acceptance. Will finance accept the new platform as close owner from wave five, or ask for a parallel close first? That can stretch a wave by one period.
• Point-of-sale sources. Debezium needs change capture per database. Older SQL Server versions in 40 countries may need a log-shipping replica in between.
• Hadoop jobs. If they hold Spark or Java logic, not only SQL, wave one needs a rewrite to Beam or BigQuery SQL.

Outcome notes (design assumptions, not measurements)

This is a design exercise, not a completed migration. The output is a four-page diagram set, a one-page whiteboard version and a 16-page Turkish PDF. The three-hour batch target is a requirement, not a result.

Sources

• Architecture PDF (Turkish, 16 pages).
• draw.io diagrams (English labels): four-page set and a one-page 30-minute version.