Snowflake to BigQuery Migration — ELT Platform on Google Cloud

By Amar Kumar

A proposed Snowflake to BigQuery migration for an ELT-heavy analytics platform would rebuild streaming and batch ingestion on Google Cloud, port Snowflake-native SQL (Streams, Tasks, dynamic tables, stored procedures) into dbt incremental models, replace cron with Cloud Composer, run a historical backfill and parallel reconciliation, then cut over BI, reverse-ETL, and AI pipelines — with the legacy Snowflake stack and managed ELT tool decommissioned.

Proposed outcome: A reconciled BigQuery platform with Kafka Sink and CDC ingestion, dbt transforms, Composer orchestration, residency-aware IAM controls, GCP cost visibility, and a freeze / final-delta / cutover executed with minimal downtime.

Scenario

This brief describes a proposed solution — not a delivered engagement. It maps a recurring warehouse migration pattern: an analytics platform that grew on Snowflake must standardize on BigQuery for cost, GCP-native integrations, and residency controls, while production continues until cutover.

• Organization: Data platform / analytics engineering team with BI, reverse-ETL, and AI insight pipelines downstream of the warehouse
• Current stack: Streaming and batch into Snowflake; transforms as Snowflake SQL plus Python and shell; cron-based scheduling; managed ELT tool for some source loads
• Target stack: BigQuery datasets with partition/cluster design; Kafka BigQuery Sink on existing Kubernetes; Airbyte OSS or Datastream CDC; Cloud Composer (Airflow) or Scheduler + Workflows; native BI connectors
• Owner profile: Hands-on data engineer working with a Data Architect / PM and internal DE; AI-assisted bulk SQL translation is part of the workflow
• Constraints: Google Cloud data-residency boundaries; all sampling must pass PII-redaction and secret-scanning before leaving the environment
• Timeline: Roughly six months foundation through cutover and decommission, dual-running with production Snowflake until freeze

Problem

Moving warehouses is not a dump-and-load. Snowflake-native constructs, cron orchestration, and a managed ELT dependency create a multi-track migration where ingestion, transforms, security, and cutover each need an explicit design — not a one-shot dialect rewrite.

• No 1:1 equivalents — Streams + Tasks CDC, dynamic tables, zero-copy clones, and JavaScript stored procedures do not translate cleanly; they need BigQuery patterns or Cloud Run jobs
• Cron without a DAG graph — dependency-blind schedules, weak retries, and painful backfills make parallel-run validation and freeze windows risky
• Managed ELT lock-in — the current tool may lack clean BigQuery destinations, residency options, or cost transparency once the warehouse moves
• Security remapping — Snowflake RBAC and row-access policies must become IAM, authorized views, row-level access policies, and policy-tag column masking
• Cost model change — Snowflake credits vs BigQuery on-demand / Editions slots; without partitioning, clustering, and reservation planning, “cheaper warehouse” never materializes
• Silent cutover drift — large historical backfills and consumer repointing without row-count / aggregate reconciliation produce BI and AI pipelines that look green but disagree with source of truth
• PII / residency — sampling and debug exports that leave the GCP boundary without redaction create compliance exposure during migration

Requirements

Functional

• Transformation migration — port incremental models formerly driven by Streams, Tasks, stored procedures, and dynamic tables into BigQuery / dbt
• SQL translation — BigQuery Migration Service for bulk dialect conversion; manual fixes for VARIANT ↔ JSON/STRUCT, QUALIFY, and non-translatable constructs
• Re-architecture — map clones, JS procedures, and usage-metadata jobs to native BigQuery SQL, scheduled queries, or Cloud Run Python jobs
• Ingestion rebuild — Kafka Connect BigQuery Sink (Storage Write API) on existing Kubernetes; replace managed ELT with Airbyte OSS or Datastream CDC; switch BI/event sources to native BigQuery destinations
• Foundation & security — datasets, regions (residency), partitioning, clustering; IAM; row-level access policies; column-level controls / authorized views
• Orchestration — Cloud Composer DAGs (preferred) or Cloud Scheduler + Workflows replacing cron — dependencies, retries, backfills, alerting
• Historical migration — unload to GCS, load via BigQuery jobs / Data Transfer Service with partition and cluster keys applied on land
• Validation & cutover — parallel run, reconcile, repoint consumers, freeze / final-delta / cutover, decommission Snowflake and managed ELT

Non-functional

• Residency & PII — dataset locations honor boundaries; samples redacted and secret-scanned before any external use
• Idempotency — loads and incremental models safe to re-run; MERGE / insert-overwrite patterns documented
• Cost control — partition pruning and clustering on hot tables; slot reservation plan where scan volume justifies Editions
• Observability — DAG SLAs, ingestion lag, reconciliation metrics, and GCP billing dashboards from day one of parallel run
• Cutover RTO — freeze window sized for final delta + consumer smoke tests; documented rollback to Snowflake until freeze closes
• Workload Identity — service accounts for Composer, Sink, CDC, and Cloud Run — no long-lived keys in repos

Architecture

Four tracks run in parallel until cutover: ingestion (Kafka Sink + CDC → BigQuery raw), transform (dbt incremental → curated/marts), orchestration (Composer DAGs), and migration control (historical GCS loads + reconciliation against Snowflake).

flowchart TB classDef src fill:#f8fafc,stroke:#475569,color:#334155 classDef ingest fill:#ede9fe,stroke:#7c3aed,color:#5b21b6 classDef bq fill:#dbeafe,stroke:#2563eb,color:#1e3a8a classDef orch fill:#fef3c7,stroke:#d97706,color:#92400e classDef legacy fill:#fee2e2,stroke:#dc2626,color:#991b1b classDef cons fill:#ccfbf1,stroke:#0d9488,color:#115e59 KAFKA["Kafka topics\nstream events"]:::src OLTP["OLTP / SaaS\nsources"]:::src SF["Snowflake\nlegacy warehouse"]:::legacy SINK["Kafka BigQuery Sink\nStorage Write API"]:::ingest CDC["Airbyte / Datastream\nCDC · batch ELT"]:::ingest GCS["GCS staging\nunload · backfill"]:::ingest RAW["BigQuery raw\npartitioned · clustered"]:::bq DBT["dbt incremental\nstaging · core · marts"]:::bq CUR["BigQuery curated\nIAM · RLS · policy tags"]:::bq COMP["Cloud Composer\nAirflow DAGs"]:::orch CRUN["Cloud Run\nnon-SQL jobs"]:::orch BI["BI tools\nnative BQ connector"]:::cons RETL["Reverse ETL"]:::cons AI["AI insights\npipelines"]:::cons KAFKA --> SINK OLTP --> CDC SINK --> RAW CDC --> RAW SF --> GCS GCS --> RAW RAW --> DBT DBT --> CUR COMP --> DBT COMP --> CDC COMP --> CRUN CUR --> BI CUR --> RETL CUR --> AI SF -.->|"parallel reconcile"| CUR

Target architecture — rebuilt ingestion into BigQuery raw, dbt transforms to curated marts, Composer orchestration, and parallel reconciliation against Snowflake until cutover

sequenceDiagram autonumber participant Src as Sources participant SF as Snowflake participant BQ as BigQuery participant Rec as Reconcile suite participant Cons as BI · reverse-ETL · AI Src->>SF: production writes continue Src->>BQ: dual-write / rebuilt ingestion SF->>BQ: historical unload via GCS loop nightly parallel run BQ->>Rec: row counts · key aggregates SF->>Rec: same metrics Rec-->>Rec: diff report · alert on breach end Note over Src,Cons: freeze window Src--xSF: pause non-critical writers SF->>BQ: final delta sync Rec->>Rec: sign-off checklist Cons->>BQ: repoint connections Note over SF: decommission ELT + Snowflake

Cutover sequence — dual-run with reconciliation, freeze, final delta, consumer repoint, then decommission of Snowflake and the managed ELT tool

End-to-end flow

Six-month lifecycle — foundation and ingestion first, transforms and Composer next, historical backfill and parallel run, then freeze and cutover

Recommended stack

Recommendation: standardize the warehouse on BigQuery with dbt for transforms, Kafka Connect BigQuery Sink for streaming, Airbyte OSS or Datastream for CDC/batch replacement of the managed ELT tool, and Cloud Composer for orchestration — using the BigQuery Migration Service for bulk SQL assist and Cloud Run only where SQL cannot express prior JS/procedure logic.

Why dbt over BigQuery scheduled queries alone? An ELT-heavy estate needs incremental materializations, ref graphs, tests, and PR review. Scheduled queries work for a handful of jobs; they do not replace a transformation layer that grew as Streams, Tasks, and dynamic tables.

Why Composer over Scheduler + Workflows? Composer is heavier to operate but matches cron-replacement reality: multi-hop dependencies, backfills during parallel run, and SLA alerts. Scheduler + Workflows is a fallback when the DAG surface is intentionally small.

Why Airbyte or Datastream instead of keeping the managed ELT tool? Cutover success includes decommissioning that tool. Open-source or GCP-native CDC with BigQuery destinations removes a second vendor boundary and aligns residency and IAM with the rest of the GCP estate.

Illustrative dbt incremental pattern replacing a Snowflake Stream + Task CDC merge:

{{ config(
  materialized='incremental',
  incremental_strategy='merge',
  unique_key='event_id',
  partition_by={'field': 'event_date', 'data_type': 'date'},
  cluster_by=['account_id']
) }}

select
  event_id,
  account_id,
  event_ts,
  date(event_ts) as event_date,
  payload
from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_ts > (
  select coalesce(max(event_ts), timestamp('1970-01-01'))
  from {{ this }}
)
{% endif %}

Component design

1 — Inventory & translation factory

• Input: Snowflake object catalog (tables, views, Streams, Tasks, procedures, dynamic tables, scripts)
• Output: Migration inventory tagged auto-translate / manual-SQL / Cloud-Run rewrite; Migration Service batch jobs + review queue
• QA gate: every production Task or dynamic table mapped to an owner, SLA, and dbt model or job ID before Phase 3 exit

2 — BigQuery foundation & security

• Input: Residency map, consumer roles, PII column inventory
• Output: Dataset layout (raw / staging / curated / marts), partition/cluster standards, IAM bindings, row-level policies, policy tags, authorized views
• QA gate: negative access tests — analyst role cannot select tagged PII; residency violation blocked by dataset location

3 — Streaming ingestion (Kafka Sink)

• Input: Existing Kafka topics and schemas; GKE Helm values
• Output: BigQuery Sink connectors writing via Storage Write API into partitioned raw tables; DLQ topic for poison messages
• QA gate: lag < agreed SLA; null-rate and schema-drift alerts; dual-read sample vs Snowflake stream for N days

4 — CDC / batch ELT replacement

• Input: Source systems currently loaded by the managed ELT tool
• Output: Airbyte or Datastream connections to BigQuery; sync cadence documented in Composer
• QA gate: source row counts and primary-key coverage match prior ELT within threshold before that connector is retired

5 — dbt transformation layer

• Input: Translated SQL, macro library, source freshness tests
• Output: staging → intermediate → marts project; incremental models for former Stream/Task paths; docs and contracts
• QA gate: dbt tests green; mart-level aggregates within reconciliation thresholds vs Snowflake

6 — Cloud Run re-architecture jobs

• Input: JS stored procedures, shell jobs, usage-metadata scripts with no SQL equivalent
• Output: Containerized Python jobs on Cloud Run, triggered by Composer; structured logs to Cloud Logging
• QA gate: unit tests on fixtures; idempotent re-run; secrets only via Secret Manager

7 — Orchestration (Composer)

• Input: Legacy cron inventory and dependency notes
• Output: DAG packs with sensors, retries, SLA misses → alerting; backfill operators for parallel-run catch-up
• QA gate: dry-run in Composer staging; no production DAG without on-call runbook link in docstring

8 — Backfill & reconciliation

Implementation plan

Reporting & ops

Ops cadence would include a daily parallel-run reconcile email (diffs, lag, failed DAGs), a weekly migration standup with the Architect / PM (inventory burn-down, cutover blockers), and hypercare war-room coverage for 1–2 weeks post-cutover. On-call pages for DAG SLA misses, Sink lag, and reconcile threshold breaches — not for every dbt warning.

Proposed deliverables

Following the phased plan, a build would ship these artifacts:

• Snowflake object inventory with migration-path tags and owner map
• BigQuery foundation — datasets, regions, partition/cluster standards, IAM / RLS / policy tags / authorized views (Terraform preferred)
• Kafka BigQuery Sink deployment on existing Kubernetes with DLQ and lag dashboards
• Airbyte OSS or Datastream CDC connections replacing the managed ELT tool
• dbt project — staging, intermediate, and mart models with tests, docs, and incremental macros
• SQL translation report — Migration Service coverage vs manual / Cloud Run rewrites
• Cloud Composer DAG pack replacing cron, with retries, backfills, and alert wiring
• Cloud Run jobs for non-SQL procedure and shell logic
• Historical unload/load runbooks, GCS manifests, and partition/cluster DDL applied on land
• Parallel-run reconciliation suite and cutover checklist (freeze / final delta / repoint / rollback)
• Cost model — on-demand vs Editions recommendation — and GCP billing dashboard
• Decommission plan for Snowflake and the managed ELT tool, plus hypercare handoff notes

Effort estimate

Indicative effort for a six-month, hands-on IC engagement (assumes existing Kafka/Kubernetes footprint, Architect/PM partnership, and AI-assisted bulk SQL translation):

At 30+ hrs/week, delivery would span roughly 22–26 weeks foundation through hypercare. Milestone gates — Sink healthy, dbt marts reconciling, Composer green, backfill complete, cutover signed — keep dual-run cost bounded and make rollback decisions explicit.

Glossary