Compute Migration: BigQuery and Dataproc to Databricks and Fabric¶

A hands-on guide for data engineers migrating BigQuery SQL workloads, BigQuery ML, scheduled queries, and Dataproc Spark jobs to Databricks SQL, Fabric, and dbt.

Scope¶

This guide covers:

• BigQuery SQL to SparkSQL / T-SQL dialect conversion
• Slots to DBU/CU capacity mapping
• Materialized views to dbt materializations
• Scheduled queries to dbt + ADF triggers
• BigQuery BI Engine to Direct Lake mode
• Clustering and partitioning to Delta table optimization
• BigQuery Omni to OneLake shortcuts
• Dataproc to Databricks cluster migration

For storage migration, see Storage Migration. For Looker migration, see Analytics Migration.

BigQuery SQL to Databricks SQL / Fabric¶

Dialect conversion reference¶

The existing playbook (Section 4.3) documents the critical dialect differences. This guide expands on those with additional patterns.

Core syntax differences¶

DDL differences¶

Window function differences¶

Automated dialect conversion¶

For large codebases, manual conversion is impractical. Use a systematic approach:

1. Regex-based find-and-replace for type names (INT64 to BIGINT, FLOAT64 to DOUBLE)
2. AST-based tools (sqlglot) for complex syntax transformations
3. Manual review for QUALIFY, UNNEST, struct literals, and cross-joins

# Using sqlglot for automated conversion
import sqlglot

bq_sql = """
SELECT
  DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AS start_date,
  SAFE_CAST(revenue AS FLOAT64) AS revenue,
  ARRAY_AGG(STRUCT(product_id, quantity)) AS line_items
FROM `acme-gov.sales.orders`
WHERE REGEXP_CONTAINS(region, r'^US-')
"""

databricks_sql = sqlglot.transpile(bq_sql, read="bigquery", write="databricks")[0]
print(databricks_sql)

Slots to DBU/CU mapping¶

Conceptual mapping¶

Sizing guidance¶

There is no direct slot-to-DBU conversion formula because the architectures differ. Use this heuristic:

Recommendation: Start with the estimated size, run representative workloads for 2 weeks, then right-size based on actual DBU consumption.

Materialized views to dbt materializations¶

BigQuery materialized views auto-refresh when base tables change. The dbt equivalent depends on the refresh pattern:

Worked example: BigQuery MV to dbt incremental¶

BigQuery materialized view:

CREATE MATERIALIZED VIEW `acme-gov.finance.mv_daily_revenue`
PARTITION BY sales_date
CLUSTER BY region
AS
SELECT
  DATE(order_ts) AS sales_date,
  region,
  SUM(gross_amount) AS daily_revenue,
  COUNT(*) AS order_count
FROM `acme-gov.sales.orders`
GROUP BY 1, 2;

dbt incremental model:

-- models/gold/daily_revenue.sql
{{ config(
    materialized='incremental',
    unique_key=['sales_date', 'region'],
    incremental_strategy='merge',
    partition_by=['sales_date']
) }}

SELECT
  DATE(order_ts) AS sales_date,
  region,
  SUM(gross_amount) AS daily_revenue,
  COUNT(*) AS order_count
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE DATE(order_ts) >= DATE_SUB(CURRENT_DATE(), 3)
{% endif %}
GROUP BY 1, 2

Scheduled queries to dbt + ADF triggers¶

Migration steps¶

1. Inventory all scheduled queries from BigQuery console or INFORMATION_SCHEMA.SCHEDULED_QUERIES
2. Classify each query as simple (single table refresh), dependent (DAG chain), or cross-system
3. Convert simple queries to dbt models with Databricks Workflow schedules
4. Convert dependent chains to dbt jobs (dbt manages the DAG)
5. Convert cross-system queries to ADF pipelines calling dbt and other activities

BigQuery BI Engine to Direct Lake¶

BigQuery BI Engine is an in-memory acceleration layer that speeds up BI queries over BigQuery tables. The Azure equivalent is Power BI Direct Lake mode.

Migration: Once data is in Delta Lake on OneLake, create a Direct Lake semantic model in Power BI that points to the Delta tables. No BI Engine configuration is needed -- Direct Lake is the default mode for Fabric lakehouses.

Clustering and partitioning to Delta optimization¶

Partitioning¶

Clustering to Z-ordering¶

BigQuery clustering is automatic and maintenance-free. Delta Z-ordering requires explicit OPTIMIZE commands.

-- Run after data load or on a schedule
OPTIMIZE finance.fact_sales_daily ZORDER BY (region, product_id);

-- Enable auto-compaction for ongoing optimization
ALTER TABLE finance.fact_sales_daily SET TBLPROPERTIES (
  'delta.autoOptimize.autoCompact' = 'true',
  'delta.autoOptimize.optimizeWrite' = 'true'
);

Best practice: Schedule OPTIMIZE as a post-load step in dbt or as a Databricks Workflow task.

BigQuery Omni to OneLake shortcuts¶

BigQuery Omni allows querying data in S3 or Azure Storage from BigQuery. During migration, use OneLake shortcuts for the reverse: querying GCS data from Azure.

Dataproc to Databricks¶

Cluster migration¶

Spark compatibility¶

Dataproc uses open-source Apache Spark. Databricks uses the Databricks Runtime, which is a superset of Apache Spark with performance optimizations (Photon engine).

Compatibility notes:

• PySpark code runs on Databricks with minimal changes
• Spark SQL is compatible; some GCP-specific UDFs need porting
• Spark JAR jobs run on Databricks with the same JAR
• Hive metastore tables bridge via Unity Catalog external metastore
• GCS paths (gs://) need translation to ADLS paths (abfss://)
• GCP-specific libraries (e.g., BigQuery connector) are replaced by Databricks native connectors

Migration steps¶

1. Inventory Dataproc clusters, jobs, notebooks, and init actions
2. Map cluster configurations to Databricks cluster policies
3. Port Spark jobs -- change storage paths, remove GCP-specific operators
4. Port notebooks -- change gs:// to abfss://, update library imports
5. Test on Databricks with representative data
6. Schedule using Databricks Workflows

Validation checklist¶

After migrating compute workloads:

• All BigQuery scheduled queries have equivalent dbt models or Databricks jobs
• SQL dialect conversion tested with representative queries
• Partition and Z-order strategies applied to Delta tables
• Query performance is comparable or better (benchmark key queries)
• dbt tests pass for all migrated models
• Databricks Workflow schedules match original BigQuery schedules
• Dataproc Spark jobs run successfully on Databricks
• MLflow models registered for any BigQuery ML conversions

Last updated: 2026-04-30 Maintainers: CSA-in-a-Box core team Related: Storage Migration | ETL Migration | Analytics Migration | Migration Playbook