Migrating a dbt Pipeline from PostgreSQL to BigQuery: What Changes and What Doesn’t

2026-07-12 · Source: Data Engineering on Medium · Field: Technology & Digital — Data Science & Analytics, Cloud Computing & IT Infrastructure · Depth: Intermediate, short

Summary

A technical account details the migration of a dbt pipeline, originally built on local PostgreSQL for NYC Taxi and Olist datasets, to Google BigQuery. This transition addresses PostgreSQL's limitations in production, such as lack of team accessibility, scalable partitioning, and managed infrastructure. Key changes involved adapting SQL dialect, specifically "CAST" syntax and "EXTRACT(DAYOFWEEK)" functions, and reconfiguring "profiles.yml" for BigQuery's OAuth and service account authentication. New implementations included partitioning by "pickup_datetime" and clustering by "trip_id" for cost optimization, alongside a 3-day lookback window for incremental models. Furthermore, a CI/CD pipeline was established using GitHub Actions, automating 18 dbt tests on every push to the main branch, secured by a dedicated service account with minimal permissions. The core dbt model structure, business logic, and incremental model concepts remained warehouse-agnostic.

Key takeaway

For Data Engineers migrating dbt pipelines to cloud data warehouses like BigQuery, you must account for significant SQL dialect differences and authentication changes beyond just "profiles.yml". Implement BigQuery's native partitioning and clustering on relevant columns, such as "pickup_datetime" and "trip_id", to optimize query costs. Additionally, establish a robust CI/CD pipeline with GitHub Actions and dedicated service accounts to automate data quality checks and ensure production readiness.

Key insights

Migrating dbt pipelines to cloud warehouses requires adapting SQL dialects, authentication, and implementing cloud-native optimizations.

Principles

• BigQuery charges by data scanned, not query time.
• CI/CD automates data quality gates.
• dbt models can be warehouse-agnostic by design.

Method

Migrate dbt by adapting SQL dialect, configuring "profiles.yml" for cloud authentication, implementing partitioning/clustering, and setting up CI/CD with service accounts and GitHub Secrets.

In practice

• Use "CAST(column AS TYPE)" for BigQuery type casting.
• Partition by date and cluster by ID for BigQuery cost savings.
• Implement a 3-day lookback for incremental models.

Topics

• dbt
• BigQuery
• PostgreSQL
• Data Migration
• CI/CD
• GitHub Actions
• Data Engineering

Code references

• NajmaFahmi/nyc-taxi-dbt-pipeline

Best for: Data Engineer, MLOps Engineer

Related on AIssential

• Migrating GA4 Data from BigQuery to S3/Redshift — Migrating GA4 data from BigQuery to AWS requires a "push" architecture to avoid high cross-cloud egress and NAT Gateway costs.
• 4 YAML Files Instead of PySpark: How We Let Analysts Build Data Pipelines Without Engineers — Shifting to a declarative, SQL-centric data platform empowers analysts and drastically reduces pipeline delivery times.
• Switching from PostgreSQL to ClickHouse for Improved Performance and Scalability — Migrating from PostgreSQL to ClickHouse significantly scales high-volume caching systems by leveraging column-oriented architecture and sparse indexes.
• Branching databases like code: a CI/CD pattern for Lakebase, in production at Glaspoort — Branching OLTP databases directly from production and treating migrations as truth enables robust, fast CI/CD.
• Chatting with your Data: Conversational Analytics in BigQuery — Enterprise-grade conversational analytics requires robust governance to ensure accuracy and cost control over large datasets.
• Data News — dbt Coalesce 2025 — The dbt Labs-Fivetran merger signals a shift towards enterprise-focused, open-standard data infrastructure, potentially alienating open-source advocates.

See Counsel's argued verdicts on the open AI decisions leaders are weighing →

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Data Engineering on Medium.