The Cutover Playbook: Staged Waves, Dry-Runs, and Catching the Legacy 2nd-Writer Before It Eats Your Weekend

Cutting over from BigQuery to Databricks is a staged sequence of waves, not a single weekend event. Treating it as one weekend is how you book three weekends and a lot of pager noise — and how the four classes of incident that never appear in a migration RFP all surface at once.

This post covers the wave structure, the legacy second-writer trap, the bootstrap discovery gotcha, the dedup-loser audit, the federation decision, and the Unity Catalog Service Principal permissions gap. The technical patterns here are drawn from the founder’s prior-role migration work at scale.

The wave plan

Cut over by domain, not by individual job. Domains are the natural blast radius — finance jobs talk to other finance jobs; growth jobs talk to other growth jobs; cross-domain dependencies are rarer than you think. Pick the smallest domain first and let the rest of the platform watch it bake for a week.

The wave plan below is drawn from the shape of migrations at this scale; specific findings are illustrative rather than from a single named engagement.

That’s around 480 distinct jobs touched over four months. (About 300 left to decommission individually because they were already dead in some sense — stubs, duplicates, dashboards nobody opens. The catalog often grows the migration to 1.5x the original scope; this is normal.)

Stretching the schedule is the right call because each wave surfaces failure modes the previous wave didn’t. The Wave A to Wave D gap on a migration at this scale typically adds five or more new translator passes and roughly halves the manual fix count by the time the larger wave ships.

The legacy second-writer trap

The single most common operational failure in a cutover is the legacy second-writer.

The story goes like this. You translate a job, validate that the Databricks side produces the same output as the BigQuery side, deploy the Databricks job, cut over the downstream BI tool to read from the new target, and disable the BigQuery scheduled query that used to write the source table.

Then you go to bed.

At 03:00 your monitoring lights up. The target table has wrong data. You wake up, dig in, and discover that there is another BigQuery scheduled query that also writes to that table — a “second writer” — that you didn’t know about, that nobody on the current team remembers, that was set up two years ago by someone who has since left. While the Databricks job was writing correctly, the legacy second-writer was overwriting it with stale data three hours later. The founder has seen this pattern across multiple prior migrations. It is not a rare bug.

The detection method is simple and you must run it before every wave:

-- BigQuery: find all scheduled queries that write to a given table
SELECT
  config.display_name,
  config.destination_dataset_id,
  config.params,
  config.schedule
FROM `region-us`.INFORMATION_SCHEMA.SCHEDULED_QUERIES sq
WHERE
  REGEXP_CONTAINS(
    JSON_EXTRACT_SCALAR(config.params, '$.query'),
    r'INTO\s+`?my_dataset\.my_target_table`?'
  )
  OR config.destination_dataset_id LIKE '%my_dataset%'

The list returned should be exactly one job per target table. If it’s two, you have a legacy second-writer. Disable both BigQuery jobs before you enable the Databricks one.

In one prior-engagement migration this query surfaced three legacy second-writers: one in the brand-merchants wave, one in the Wave B cutover, and one in Wave 2. The Wave B discovery was particularly painful because the legacy second-writer wrote to one specific column of a wide table with a MERGE statement, so the symptom was “one column is stale, everything else is fine.” Each one would have caused a weekend outage without the pre-wave check.

The bootstrap discovery gotcha

Your translated query passes dry-run validation in Databricks (EXPLAIN-style check that says “yes, this is valid SQL”). You enable it in production. It immediately fails with [TABLE_OR_VIEW_NOT_FOUND].

The cause: the dry-run validation in Databricks SQL does not check whether the target table (the one you’re writing to) exists. It only checks the query parses. If your INSERT INTO target_schema.target_table references a target schema or table that doesn’t exist yet in Unity Catalog (Databricks’ table governance and metadata layer, which centralizes table grants and discovery), the dry-run is green, and the prod run breaks.

This is bootstrap discovery. It is a one-line fix and it has to be in your wave playbook:

-- Before unpausing any translated job, bootstrap the target table:
CREATE SCHEMA IF NOT EXISTS my_catalog.target_schema;
CREATE TABLE IF NOT EXISTS my_catalog.target_schema.target_table (
  -- Schema derived from the BigQuery source's INFORMATION_SCHEMA.COLUMNS
);

Every translated job needs a pre-cutover bootstrap step. Generate the CREATE TABLE IF NOT EXISTS statements from the source schema, run them as part of the wave’s preparation phase, then validate that all targets exist before enabling any writers. On a prior engagement, bootstrapping at the wave-prep phase took a wave from 4 job failures to 0. It is the cheapest five-minute fix in the playbook.

The dedup-loser audit

Before every wave, run a dedup audit. The question it answers: does any pair of jobs in this wave write to the same target table?

The answer is “yes” surprisingly often. Roughly 15-20% of mid-market BigQuery environments have duplicate writers that have accumulated over years of feature additions. Two teams added similar pipelines for similar reports; a third team added a third pipeline when the first two seemed broken; the result is three writers producing slightly different outputs to slightly different versions of the same target table.

When you migrate, you have to pick one to keep — the “winner” — and pause the others — the “losers.” In a prior engagement, the dedup pass identified roughly 50 losers across 45 duplicate groups (illustrative). Roughly a third of those groups required manual review where the differences in output were enough that a stakeholder had to decide which was canonical; the other two-thirds were straightforward “pause the older one” calls.

The detection query — Databricks side, since the catalog often has more cross-job visibility than BigQuery’s INFORMATION_SCHEMA:

-- Databricks: find pairs of jobs writing to the same destination
SELECT
  destination_table,
  COUNT(DISTINCT job_id) AS writer_count,
  COLLECT_LIST(job_name) AS writer_jobs
FROM job_inventory  -- your team's job catalog
WHERE destination_table IS NOT NULL
GROUP BY destination_table
HAVING writer_count > 1
ORDER BY writer_count DESC;

If the writer count is two or more, pause every writer but the canonical one before the wave. If there is no canonical, flag for stakeholder review and skip the cutover for that table.

The federation-vs-mirror decision

For every BigQuery table your migration depends on, you have three choices:

1. Mirror it. Build a Databricks table that’s refreshed from the BigQuery source (CDC, full daily refresh, or hourly). The Databricks job reads the mirror. Eventually you cut off the BigQuery source.
2. Federate it. Use Databricks’ BigQuery federation (Lakehouse Federation) to query the BigQuery table directly from Databricks without copying the data. The Databricks job reads through the federation connector. You never copy the data; you read live.
3. Skip it. Identify the table as out-of-scope for migration and document that the workflow that depends on it stays on BigQuery permanently.

Federation is tempting because it’s the path of least short-term resistance. Migrations where the team federated 90% of source tables out of habit tend to spend the following year migrating them properly when the federation latency turns out to be unacceptable for the production workload.

The rule:

In a prior engagement, the catalog showed around 90 unique federation references across around 20 jobs (illustrative). The validation pass classified roughly 63 as HIGH-confidence removable (mirror exists, fresh, row-counts match), 18 as needs-validation (mirror stale or row counts diverge), and 13 as keep-federation (no mirror equivalent yet). The keep-federation group typically becomes the next sprint’s work — build a mirror, then revisit.

Also worth knowing: Databricks’ BigQuery federation does not work on Classic Pro SQL Warehouses. It requires Serverless SQL or a Spark cluster. If your validation warehouse is Classic Pro, federation SELECT COUNT(*) queries return [UNSUPPORTED_DATA_SOURCE]. This surfaces as “zero rows” for every federation table — a warehouse-class incompatibility, not a missing table.

The Unity Catalog Service Principal trap

When you stand up a Databricks workspace and need it to write to Google Sheets (very common for finance pipelines that publish to a CFO sheet), the Databricks side uses a Service Principal (SP) — Databricks’ equivalent of a service account. The Service Principal authenticates to Google with a JSON key.

The gotcha: the JSON key authenticates the Service Account (SA, Google Cloud’s identity for non-human callers), not the Service Principal. The Service Account needs Editor permission on the target sheet, not Viewer. The SA commonly arrives with Viewer access, the sheet write fails silently (or with an obscure permission error in the Databricks job log), and the finance team doesn’t realize the new sheet is stale for a few days.

The diagnostic: before running any cutover on a sheet-writing pipeline, manually open the sheet, click Share, and verify the SA email (SA_NAME@PROJECT_ID.iam.gserviceaccount.com) is listed as Editor. If it’s not, add it. If you can’t add it because the sheet is owned by someone who left the company, you need a re-share (or a re-creation of the sheet) before the cutover happens.

On a prior engagement, this exact permission gap stalled a finance reconciliation cutover at “Stage 0” until the Databricks secret was re-pointed to the correct service account’s JSON key. Tiny problem, large blast radius.

The post-cutover soak

After every wave, leave both writers running in parallel for 24-72 hours. Compare the outputs. Specifically:

• Row count diff per partition (target: under 0.1% drift over 24 hours)
• Aggregate value diff (SUM of key measures: GMV, revenue, count of records — target: identical within rounding)
• Schema drift (any column types or names that diverged during translation)
• Downstream success rate (BI tool dashboards, scheduled emails, third-party exports)

A simple diff script running hourly during the soak and posting to a dedicated Slack channel covers most of this.

If any of the checks fail twice in a row, roll back: un-pause the BigQuery writer, pause the Databricks writer, page the on-call. If the soak passes for 72 hours, the cutover is declared live and the BigQuery writer is disabled permanently — not deleted. Disable for 30 days, then delete, so a rollback is still possible.

After cutover: federation cleanup and DLT recovery

Two follow-on workstreams that almost always appear:

Federation cleanup. After the wave’s writers move, review the federation connector that fed the new mirror. Twelve months post-migration, federation queries can still be firing from forgotten dbt models — paying BigQuery scan costs for a table that has a Databricks-native mirror sitting next to it. Periodic audit.

DLT recovery. Delta Live Tables (DLT) is Databricks’ managed declarative streaming framework — you write SQL that describes how data flows, DLT manages the streaming, retry, schema evolution, and SLA. It is genuinely good for the right workload. It is also vulnerable to a specific failure: MYSQL_BINLOG_FILE_DELETED on realtime resume after a MySQL source rolls its binary log files faster than DLT can read them. Default MySQL binlog retention is 24 hours; on a busy database, this is not enough headroom for the streaming pipeline to recover from a stop. Raise binlog_expire_logs_seconds to 604800 (seven days) before going live with DLT against any MySQL source.

Summary

BigQuery to Databricks migration is a six-to-nine month operational engagement, not a vendor swap. Most of the work is not translation — it is staged waves, parallel-run validation, and disciplined federation/mirror decisions. The translator covered in Part 2 handles roughly 85% of the SQL work; the cutover playbook in this post handles the rest.