Data Engineer for Snowflake to BigQuery migration

We are migrating ELT-heavy analytics platform from Snowflake to BigQuery on Google Cloud. The current stack uses streaming and batch ingestion into Snowflake, a transformation layer built on Snowflake-native SQL plus Python and shell scripting, and cron-based orchestration; downstream consumers include BI, reverse-ETL, and AI insights pipelines. The target architecture standardizes on BigQuery and potentially Cloud Composer for orchestration, rebuilt ingestion (Kafka BigQuery Sink, open-source / managed CDC in place of the current ELT tool, and native BI/warehouse connectors), and BigQuery-native security and cost controls. The work is delivered in phases over roughly six months, running in parallel with production until cutover. You will be a hands-on engineer on a small team — translating SQL and jobs, rebuilding ingestion, standing up the BigQuery foundation, and running the historical backfill and parallel-run reconciliation through to cutover. What you will do Transformation migration — port the Snowflake transformation layer (Streams + Tasks CDC, stored procedures, dynamic tables) to BigQuery — primarily incremental models. SQL translation — translate Snowflake SQL and script-based jobs into dbt models and macros, using the BigQuery Migration Service for the bulk translation plus manual fixes for what does not auto-translate. Re-architecture (as required) — re-architect constructs with no direct BigQuery equivalent (Streams, dynamic tables, zero-copy clone, JavaScript stored procedures, usage-metadata jobs) into native BigQuery patterns or Cloud Run jobs. Ingestion rebuild — move Kafka ingestion to the BigQuery Sink connector on the existing Kubernetes footprint; replace the current managed ELT tool with open-source (Airbyte OSS etc) / managed CDC; switch BI and event sources to native BigQuery destinations. BigQuery foundation & security — design datasets, regions (including data-residency boundaries), partitioning and clustering; implement IAM, row-level access policies, and column-level controls / authorized views. Orchestration (TBD) — build Cloud Composer (Airflow) DAGs — or Cloud Scheduler + Workflows — to replace legacy cron-based scheduling, with dependencies, retries, backfills, and alerting. Historical data migration — run the one-time historical backfill using unload-to-GCS loads and the BigQuery Data Transfer Service, applying the right partition/cluster design as data lands. Validation & cutover — run BigQuery and Snowflake in parallel, reconcile results, repoint downstream consumers, and execute the freeze / final-delta / cutover. Required skills These capabilities most directly determine whether the project succeeds. A strong candidate is genuinely hands-on across both the transformation and platform sides. Skill area What we need BigQuery (expert) Deep, hands-on BigQuery: Standard SQL, partitioning & clustering design, IAM, row-level access policies, policy tags / column masking, authorized views, on-demand vs. slot reservations (Editions / autoscaling), Storage Write API, load jobs, and the BigQuery Migration Service + Data Transfer Service. Snowflake (Intermediate) Practical experience with the Snowflake internals being migrated away from: Streams, Tasks, Snowpipe, stored procedures, dynamic tables, zero-copy clone, RBAC, and row-access policies. SQL dialect translation Fluent translation between Snowflake and BigQuery SQL, including semi-structured / VARIANT ↔ JSON/STRUCT handling, and the judgment to know what will not auto-translate. Data ingestion / CDC Kafka Connect (BigQuery Sink / Storage Write API), plus at least one of Airbyte, Datastream, or comparable open-source / managed CDC. Orchestration (TBD) Cloud Composer / Apache Airflow (DAG design, retries, backfills) — or Cloud Scheduler + Workflows — replacing legacy cron-based scheduling. Python Solid Python for data engineering: porting connector-based batch jobs and building Cloud Run jobs for non-SQL logic. GCP fundamentals Service accounts / Workload Identity, GCS, billing & cost modeling, and reasoning about scan-based vs. reservation pricing to actually realize the cost savings. Migration & reconciliation Track record on large-table backfills and data validation: row-count / aggregate reconciliation, delta sync, and freeze/cutover execution with minimal downtime. Preferred (nice to have) Kubernetes tooling: GKE, ArgoCD, Helm, and Terraform — the ingestion connectors deploy this way. BI tooling migration (e.g., Looker / LookML) — repointing connections and PDT strategy. Data governance and PII handling: policy tags, column masking, and preserving anonymization logic. Event-streaming and reverse-ETL platforms with native BigQuery support. Experience decommissioning a managed ELT tool. Prior end-to-end warehouse migration (Snowflake, Redshift, or Teradata → BigQuery) delivered through cutover. Engagement details Duration — 6-month contract, full-time equivalent; delivery spans foundation through cutover and decommission. Working model — Hands-on individual contributor working alongside the Data Architect / PM and the internal Data Engineering team; AI-assisted tooling (bulk SQL translation) is part of the workflow. Environment — Runs within Google Cloud, honoring data-residency requirements. All data sampling must pass PII-redaction and secret-scanning checks before leaving the environment. Definition of done — A migrated, reconciled BigQuery platform rebuilt ingestion, Cloud Composer orchestration, and the legacy Snowflake / ELT stack decommissioned — with cost visibility moved to GCP billing dashboards.