# Migrate from BigQuery > Plan and run a migration from BigQuery to MotherDuck, from inventorying projects with INFORMATION_SCHEMA to translating GoogleSQL and cutting over. This guide covers the BigQuery-specific steps of a migration: inventorying what your projects run, translating GoogleSQL and BigQuery features, and validating against BigQuery before you cut over. It follows the process in [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/), which covers sorting the inventory, grouping it into workloads, and moving one workload at a time. Read that page first. For the mechanics of copying a single table, see the [BigQuery integration page](/integrations/databases/bigquery/). :::info Prerequisites - A [MotherDuck organization](/key-tasks/managing-organizations/), with the region and plan already chosen. - In each Google Cloud project you're migrating, the BigQuery User (`roles/bigquery.user`) and BigQuery Resource Viewer (`roles/bigquery.resourceViewer`) roles to read [job history](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs), and BigQuery Metadata Viewer (`roles/bigquery.metadataViewer`) to read [table storage](https://docs.cloud.google.com/bigquery/docs/information-schema-table-storage). - The list of regions your datasets live in. `INFORMATION_SCHEMA` views are regional, so every query below runs once per region. ::: ## Inventory what BigQuery runs The queries below run in BigQuery, in GoogleSQL. Replace `region-us` with the region qualifier for your datasets, for example `region-eu` or `region-europe-west4`, and run them in each project. **What objects exist, and how big are they?** ```sql SELECT table_schema AS dataset, table_name, table_type, total_rows, total_logical_bytes, total_physical_bytes, storage_last_modified_time FROM `region-us`.INFORMATION_SCHEMA.TABLE_STORAGE WHERE NOT deleted ORDER BY total_logical_bytes DESC; ``` Daily sharded tables, such as the `events_YYYYMMDD` tables of a GA4 export, show up here as one row per day. Group them by name prefix before you count objects, or the inventory looks thousands of tables larger than it is. **Which of them does anyone read?** ```sql SELECT ref.project_id, ref.dataset_id, ref.table_id, COUNT(DISTINCT job_id) AS query_count, MAX(creation_time) AS last_read FROM `region-us`.INFORMATION_SCHEMA.JOBS, UNNEST(referenced_tables) AS ref WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) AND job_type = 'QUERY' GROUP BY ALL ORDER BY query_count DESC; ``` **Who and what connects?** ```sql SELECT user_email, COUNT(*) AS jobs, SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed, MAX(creation_time) AS last_job FROM `region-us`.INFORMATION_SCHEMA.JOBS WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY) AND (statement_type IS NULL OR statement_type != 'SCRIPT') GROUP BY ALL ORDER BY jobs DESC; ``` Service accounts show up here next to people, with addresses ending in `iam.gserviceaccount.com`. Each one is a connection you'll have to repoint later, so treat this list as your cutover checklist. The `SCRIPT` filter drops the parent row of multi-statement queries, which repeats the totals of its child jobs. **What shape is the compute?** ```sql SELECT TIMESTAMP_TRUNC(creation_time, HOUR) AS hour, reservation_id, SUM(total_slot_ms) / (1000 * 60 * 60) AS slot_hours, SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed FROM `region-us`.INFORMATION_SCHEMA.JOBS WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY) AND job_type = 'QUERY' AND (statement_type IS NULL OR statement_type != 'SCRIPT') GROUP BY ALL ORDER BY hour; ``` Slot usage that comes in two spikes a day is a batch pipeline. Usage spread evenly through business hours is serving dashboards. The two need different Duckling sizing on the other side, so it's worth knowing which is which before you size anything. A `NULL` `reservation_id` means the job ran on demand. This query attributes a job's slots to the hour it started; use `INFORMATION_SCHEMA.JOBS_TIMELINE` when you need slot usage per second. **What runs that isn't a query?** ```sql SELECT routine_schema AS dataset, routine_name, routine_type, external_language FROM `region-us`.INFORMATION_SCHEMA.ROUTINES ORDER BY routine_type, dataset; ``` Stored procedures, JavaScript UDFs, and remote functions all need rewriting, so count them early. In the [`ROUTINES` view](https://docs.cloud.google.com/bigquery/docs/information-schema-routines), `external_language` is `JAVASCRIPT` for JavaScript UDFs and `NULL` for SQL routines. Scheduled queries and Data Transfer Service jobs aren't in `INFORMATION_SCHEMA` at all. List them per region with [`bq ls --transfer_config`](https://docs.cloud.google.com/bigquery/docs/working-with-transfers), and add `--filter=dataSourceIds:scheduled_query` to see only scheduled queries: ```bash > bq ls --transfer_config --transfer_location=us ``` :::note The [`INFORMATION_SCHEMA.JOBS` view](https://docs.cloud.google.com/bigquery/docs/information-schema-jobs) retains 180 days of history. Pull it once at the start of the migration and refresh before cutover, so a quarterly job doesn't fall out of the window before you've seen it. ::: :::warning A regional `INFORMATION_SCHEMA` view only returns datasets in that region, with no error and no hint that other regions exist. If a dataset you expected is missing, query the other regions before you conclude it's unused. ::: With the inventory in hand, [sort it into four buckets](/key-tasks/data-warehousing/migrating-your-data-warehouse/#sort-the-inventory-into-four-buckets) and [group it into workloads](/key-tasks/data-warehousing/migrating-your-data-warehouse/#group-whats-left-into-independent-workloads). ### Ingestion that only writes to BigQuery Google's own exports land in BigQuery and nowhere else: the GA4 and Firebase exports, Google Ads and other Data Transfer Service connectors, and the Cloud Billing export. You can't repoint them at MotherDuck, so plan to keep a BigQuery project as their landing zone and copy from it on a schedule. The [GA4 BigQuery ingest Flight recipe](/cookbook/flight-ga4-bigquery-ingest/) does this for GA4 with an overlap for late-arriving events, and the [BigQuery ingest Flight recipe](/cookbook/flight-bigquery-ingest/) does it for any date-partitioned table. Reading out of BigQuery isn't free. The `bigquery` extension reads through the BigQuery Storage Read API, which BigQuery bills by bytes read, and data leaving Google Cloud for MotherDuck's AWS region is billed as network egress. For a large one-time copy, export the tables to Google Cloud Storage as Parquet with an extract job and read them from there with a [GCS secret](/integrations/cloud-storage/google-cloud-storage/), so you pay egress once and can rerun the load without reading BigQuery again. ## Three habits to unlearn BigQuery and MotherDuck organize data and compute differently, and the mistakes below come from carrying the BigQuery model across unchanged. Keep them in mind when you [set up the infrastructure](/key-tasks/data-warehousing/migrating-your-data-warehouse/#set-up-the-infrastructure-for-migration). **A dataset is not a schema, and a project is not a database.** BigQuery names a table `project.dataset.table` and MotherDuck names it `database.schema.table`, so mapping one project to one database looks natural. It's usually wrong. In BigQuery the dataset is the unit of access control. In MotherDuck the database is: it's what you share, and it takes one writer at a time. Map each dataset with its own audience or its own writing pipeline to its own database. Datasets written by the same pipeline stage and read by the same people can become schemas inside one database. **A database belongs to whoever created it, not to the project.** In BigQuery, a dataset sits in the project and anyone with a matching IAM role can query it. In MotherDuck it belongs to the user or service account that created it, and the rest of the organization can't see it until you share it. A colleague querying an unshared database gets `Catalog Error: schema "x" does not exist`, which reads like a typo rather than a permissions problem. Create production databases with a service account, because ownership can't be transferred, and publish them with a [share](/key-tasks/sharing-data/): ```sql CREATE SHARE IF NOT EXISTS models FROM models (ACCESS RESTRICTED); GRANT READ ON SHARE models TO ROLE explorer; ``` Granting to `explorer` reaches every preset role, because they're concentric. Grant to a [custom role](/concepts/roles-and-access-control/#custom-roles) instead when only one team should see it. **Concurrency comes from more databases, not a bigger slot pool.** BigQuery runs every job in a project against a shared pool of slots, so 20 pipelines writing to one dataset at once is unremarkable. In MotherDuck each database takes one writer at a time and compute belongs to the account that runs the query. A pipeline fanning 20 concurrent writers at a single database (Dagster, Airflow, and Dataflow jobs all do this) needs a database per pipeline, or writes serialized into one. For the same reason, dev and production dbt targets must never point at the same database. :::tip Budget time after the port, not only for it. A direct translation of GoogleSQL runs on MotherDuck, but it may not run efficiently. See [Tune a migrated BigQuery workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/tuning-a-migrated-bigquery-workload/) for the workload patterns that need optimization. ::: ## Translate the SQL dialect Most GoogleSQL runs in DuckDB after a handful of mechanical changes. The ones below either fail loudly or, worse, run and return a different answer. | GoogleSQL | In MotherDuck | |---|---| | `` `project.dataset.table` `` | `database.schema.table`. Backticks aren't identifier quotes in DuckDB; use double quotes where a name needs quoting. | | `WHERE status = "paid"` | GoogleSQL accepts double-quoted strings. DuckDB reads `"paid"` as a column name and fails with `Referenced column "paid" not found`. Use single quotes for every string literal. | | Case-sensitive table names | BigQuery table names are case-sensitive unless the dataset is set otherwise, so `Orders` and `orders` can coexist. DuckDB resolves names case-insensitively, and the two collide on load. | | `TIMESTAMP` and `DATETIME` | The names swap meaning. A BigQuery `TIMESTAMP` is an absolute point in time, which is DuckDB's `TIMESTAMPTZ`. A BigQuery `DATETIME` has no timezone, which is DuckDB's `TIMESTAMP`. A DDL translation that keeps the type name changes what the column means. | | `CURRENT_DATE()` and other timezone defaults | BigQuery evaluates these in UTC unless the project or organization sets `default_time_zone`. DuckDB evaluates them in the client's `TimeZone` setting, so the same model can return a different date from a laptop than from a scheduled job. Pass the timezone explicitly in the models. | | `NUMERIC` | Maps to `DECIMAL(38,9)`, which DuckDB stores as a 128-bit integer: bigger on disk and slower to compute, hash, and join on. Use a narrower `DECIMAL`, such as `DECIMAL(18,2)` for money, where the values fit. | | `BIGNUMERIC` | No equivalent. DuckDB decimals stop at 38 digits. Store as `DOUBLE` if you can accept the precision, or as `VARCHAR` if you can't. | | `ARRAY` and `STRUCT` | `LIST` and `STRUCT`. BigQuery array access is 0-based with `OFFSET(n)` and 1-based with `ORDINAL(n)`. DuckDB lists are 1-based, so `arr[OFFSET(0)]` becomes `arr[1]`. Out-of-range access returns `NULL`, like `SAFE_OFFSET`. | | `UNNEST(arr) WITH OFFSET AS i` | DuckDB has no `WITH OFFSET`. Select `unnest(arr)` next to `generate_subscripts(arr, 1)`, which numbers from 1. | | `GEOGRAPHY` | `GEOMETRY` from the [`spatial` extension](/concepts/duckdb-extensions/). BigQuery measures on a sphere and returns meters; `ST_Distance` in DuckDB is planar, in coordinate units. Use `ST_Distance_Sphere` for meters. | | `SELECT * EXCEPT (col)` | `SELECT * EXCLUDE (col)`. `* REPLACE` works as-is. | | `SAFE_CAST` | `TRY_CAST`. | | `SAFE_DIVIDE(a, b)` | `a / NULLIF(b, 0)`. Plain division by zero returns `inf` in DuckDB rather than an error or `NULL`, so a missed `SAFE_DIVIDE` produces infinite values rather than failing. | | `DATE_DIFF(end, start, DAY)` | `date_diff('day', start, end)`. The part moves to the front and the dates swap order. Getting it wrong flips the sign of every result. | | `DATE_TRUNC(d, MONTH)` | `date_trunc('month', d)`. On a `DATE` input DuckDB returns a `TIMESTAMP`, so cast back to `DATE` where the model expects one. | | `FORMAT_DATE(fmt, d)` and `PARSE_DATE(fmt, s)` | `strftime(d, fmt)` and `strptime(s, fmt)::DATE`. The arguments swap order. | | `REGEXP_CONTAINS` and `REGEXP_EXTRACT` | `regexp_matches` and `regexp_extract`. BigQuery's `REGEXP_EXTRACT` returns the capture group when the pattern has one; DuckDB's returns the whole match unless you pass the group index as the third argument. | | `_PARTITIONTIME` and `_PARTITIONDATE` | Pseudo-columns aren't part of `SELECT *`, so they don't come across in a copy. Select them into a real column during the load if any model filters on them. | | Wildcard tables and `_TABLE_SUFFIX` | Load the shards into one table with a date column and sort by it. If the shards stay in object storage, a glob in `read_parquet` with [Hive partitioning](/key-tasks/cloud-storage/s3-import-best-practices/#use-hive-style-partitioning-for-large-datasets) does the same job. | ## Translate the BigQuery-specific features | BigQuery | In MotherDuck | |---|---| | Partitioning and clustering | DuckDB has neither. Sorting on insert does the same job, because row-group statistics let the scan skip ranges that can't match. See [sort tables by common filter columns](/key-tasks/query-performance/#sort-tables-by-common-filter-columns). `require_partition_filter` has no equivalent. | | Time travel and fail-safe | [Database snapshots](/concepts/snapshots/) and [UNDROP](/concepts/data-recovery/) cover point-in-time restore. Retention is set by plan rather than per dataset, and only the Business plan can change `snapshot_retention_days`, so check the window you land on matches the time travel window your datasets assumed. | | Table snapshots and clones | [Zero-copy clones](/sql-reference/motherduck-sql-reference/create-database/) with `CREATE DATABASE ... FROM`. Clones work on whole databases, not single tables. | | Materialized views | No equivalent. Build a dbt table or incremental model and refresh it on a schedule. | | Scheduled queries | A [Flight](/concepts/flights/) on a cron schedule, or an external orchestrator for pipelines with cross-system dependencies. | | Scripting and stored procedures | DuckDB has no procedural SQL: no `DECLARE`, loops, or `EXECUTE IMMEDIATE`. Move the logic to Python in a [Flight](/key-tasks/flights/), which runs any PyPI package next to your data. | | SQL UDFs | [`CREATE MACRO`](/sql-reference/duckdb-sql-reference/duckdb-statements/create-macro/), including table macros for table functions. | | JavaScript UDFs and remote functions | No equivalent in SQL. Rewrite them in SQL where the logic allows, or run them as Python in a Flight. | | BigQuery ML | No equivalent for training models in SQL. Train in a Flight. For LLM calls such as `AI.GENERATE`, use [`prompt()`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/). | | Dataform | dbt with dbt-duckdb. SQLX `config` blocks map to dbt model configs, and `${ref()}` becomes `{{ ref() }}`. | | Authorized views and authorized datasets | A [share](/key-tasks/sharing-data/) per audience, narrowed with [table-level security](/key-tasks/sharing-data/table-level-security/). See [roles and access control](/concepts/roles-and-access-control/). | | BigQuery sharing listings and public datasets | No equivalent. Anything you consume through BigQuery sharing has to be re-sourced from the provider directly, or copied on a schedule from a BigQuery project you keep for that feed. Inventory these during assessment: they're nobody's model, so they're easy to miss until something breaks. | | Data Studio (formerly Looker Studio) | Connects to MotherDuck through the Postgres endpoint. See [Data Studio with MotherDuck](/integrations/bi-tools/looker/data-studio/). | | Slots, reservations, and BI Engine | Compute is per account, not per pool. Size each service account's [Duckling](/about-motherduck/billing/duckling-sizes/) for the job it runs, and use [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for concurrent dashboard traffic. | ### Governance features without an equivalent Four BigQuery governance features have no MotherDuck counterpart: **row-level access policies**, **column-level access control with policy tags**, **dynamic data masking**, and **VPC Service Controls**. Table- and column-level IAM grants have no direct equivalent either. Access in MotherDuck is granted per Share rather than per object. [Table-level security](/key-tasks/sharing-data/table-level-security/) narrows a Share to named tables and views with an include pattern, on the Business and Enterprise plans, so the usual replacement for a row or column policy is a shape change rather than a setting: publish a Share per audience from a source that already excludes what that audience shouldn't see, or maintain a masked copy of the source database and share that. [Environment management](/key-tasks/data-warehousing/environment-management/) describes the masked-copy pattern for regulated data. Row-level security and column-level permissions on Shares are on our roadmap, so check where they've got to before you design around their absence. :::warning Surface this during assessment, not at cutover. Where policy tags or row access policies back a compliance commitment, the replacement is a data-modelling change with a review attached, and that's weeks of lead time rather than a configuration change on the day. ::: ## Validate against BigQuery before you cut over Run the aggregate comparison and anti-join from [Validate before you cut over](/key-tasks/data-warehousing/migrating-your-data-warehouse/#validate-before-you-cut-over). Three BigQuery-specific differences cause most mismatches: - Checksums built on `FARM_FINGERPRINT`, such as `BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(t)))`, don't carry across. DuckDB's `hash()` is a different function, so the two sides disagree even when the data is identical. - A `TIMESTAMP` column loaded as DuckDB `TIMESTAMP` instead of `TIMESTAMPTZ`, or a `CURRENT_DATE()` that now runs in a non-UTC timezone, lands rows on a different date near midnight. - A `NUMERIC` column narrowed to a smaller `DECIMAL` scale rounds differently from BigQuery's 9 decimal places. Check these and the source watermark on both sides before you go looking for a logic bug. ## Cut over and decommission Follow the [cutover steps](/key-tasks/data-warehousing/migrating-your-data-warehouse/#cut-over-and-decommission). On the BigQuery side: - Freeze the workload by disabling the scheduled queries, transfer configs, and Dataform workflows that write to it. - On demand, spend on compute stops when the queries stop. On a capacity edition, lower the reservation's baseline and autoscaling maximum once nothing runs against it. - Storage keeps billing until you delete the tables. Drop them only after the parallel window closes and a final `INFORMATION_SCHEMA.JOBS` read shows nothing referencing them. ## Rethink what BigQuery shaped Once a workload runs on MotherDuck, some of its shape was dictated by BigQuery's pricing rather than by the problem: - Daily sharded tables and mandatory partition filters exist to limit bytes billed. One sorted table is simpler and as fast. - Deeply nested schemas, such as the GA4 export's `event_params`, were cheap to store and query in place. Flatten the parts you use into typed columns once. - Scheduled queries and Dataform workflows can become dbt models run by a [scheduled Flight](/key-tasks/flights/scheduling-and-runs/). - BI Engine reservations and extract-based dashboards bought speed for BI tools. [Read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) and [Dives](/key-tasks/dives/) cover the same need. Hold these until after the workload is validated. Changing the logic and the platform in the same step means a mismatch has two possible causes. ## Related - [Tune a migrated BigQuery workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/tuning-a-migrated-bigquery-workload/) for what to fix once the SQL runs - [BigQuery integration](/integrations/databases/bigquery/) for the table copy mechanics - [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest/) - [Incrementally ingest GA4 BigQuery exports into MotherDuck](/cookbook/flight-ga4-bigquery-ingest/) - [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/) for the source-independent process - [Environment management](/key-tasks/data-warehousing/environment-management/) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fdata-warehousing%2Fmigrating-your-data-warehouse%2Fmigrating-from-bigquery%2F&page_title=Migrate%20from%20BigQuery&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.