Migrating from BigQuery

Moving off BigQuery doesn't have to be a big-bang copy. Thunderduck can query BigQuery in place on day one, so you move tables one at a time, starting with the ones that cost you the most.

1. Attach BigQuery and query it in place: nothing is copied yet.
2. Copy the tables you want to own into Iceberg on your lake.
3. Keep them current with a scheduled pipeline.
4. Repoint dashboards and jobs, then retire the BigQuery copy.

1. Attach and query in place

Register each dataset as a BigQuery catalog, one catalog per dataset, and query it like any other table:

SELECT customer_id, sum(amount) AS revenue
FROM   bq_sales.sales.orders
WHERE  order_date >= DATE '2026-01-01'
GROUP  BY 1;

Filters on constants and on current_date arithmetic, plus GROUP BY / ORDER BY / LIMIT on a single BigQuery table, are pushed down and run inside BigQuery, so only the result crosses the wire. A filter that depends on another table, or uses now(), stays local and the BigQuery table is scanned in full. The exact rules are in Getting data in.

At this stage everything already works: joins with your Postgres, files and Iceberg tables, roles, schedules, the API and AI assistants.

2. Copy tables into Iceberg

Small and medium tables: one statement

CREATE TABLE lake.default.customers AS
SELECT * FROM bq_sales.sales.customers;

Large tables: export to Parquet, then load

For large tables, CREATE TABLE … AS SELECT and INSERT … SELECT straight from BigQuery are not reliable. Let BigQuery write Parquet files to Cloud Storage in parallel, and load those instead.

a. Export from BigQuery. Run this in BigQuery. The * is required, since BigQuery writes at most 1 GB per file:

EXPORT DATA OPTIONS (
  uri         = 'gs://acme-bq-export/orders/*.parquet',
  format      = 'PARQUET',
  compression = 'SNAPPY',
  overwrite   = true
) AS
SELECT
  * EXCEPT (attributes, total_precise, location),
  TO_JSON_STRING(attributes)     AS attributes,     -- JSON: Parquet export doesn't support it
  CAST(total_precise AS STRING)  AS total_precise,  -- BIGNUMERIC: beyond DuckDB's DECIMAL(38)
  ST_ASTEXT(location)            AS location        -- GEOGRAPHY: export as WKT text
FROM `acme-prod.sales.orders`;

Three BigQuery types need converting in the export query:

Everything else maps directly: NUMERIC becomes DECIMAL(38,9), TIMESTAMP is UTC, DATETIME becomes a timestamp without time zone, and STRUCT / ARRAY stay nested.

For very large tables, run one export per date range into its own folder (gs://acme-bq-export/orders/month=2025-01/*.parquet, and so on). Each folder becomes one load batch below.

b. Register the bucket as a GCS catalog (HMAC keys), for example bq_export. Files are then addressed by catalog name, not by gs:// URL.

c. Create the empty table from the files' own schema. Taking the schema from the Parquet files rather than from BigQuery guarantees the load below never hits a type mismatch:

CREATE TABLE lake.default.orders AS
SELECT * FROM read_parquet('bq_export/orders/*.parquet') LIMIT 0;

d. Load in batches, one INSERT per folder:

INSERT INTO lake.default.orders
SELECT * FROM read_parquet('bq_export/orders/month=2025-01/*.parquet')
ORDER  BY customer_id;

Each statement runs as its own job, sized from its own row estimate, so batches stay comfortably within a tier, and a failed batch is simply re-run. Sort on the column you filter or join by most: Iceberg reads skip files using min/max statistics, so sorted data reads much less.

e. Compact. Many inserts leave many small files. Run Compact on the table from the LakeOps page once the load is done.

Querying former JSON columns

attributes arrives as text. Iceberg has no JSON type, so keep it as VARCHAR; DuckDB's JSON functions work on it directly:

SELECT json_extract_string(attributes, '$.channel') AS channel,
       json_extract(attributes, '$.items')         AS items
FROM   lake.default.orders
WHERE  json_extract_string(attributes, '$.country') = 'TR';

Transfer cost

If your lake is on AWS, reading Parquet from Cloud Storage is internet egress from Google's side, billed per GB by Google. For multi-terabyte migrations it can be cheaper to copy the export to S3 first (for example with Google's Storage Transfer Service) and read it from an S3 catalog.

3. Keep the copy current

Once a table lives in Iceberg, append new rows on a schedule. Pull only recent rows from BigQuery: a current_date filter is pushed down, so each run reads a small slice, not the whole table.

• For SQL you already have, use a scheduled job:

  INSERT INTO lake.default.orders
  SELECT * FROM bq_sales.sales.orders
  WHERE  order_date = current_date - 1;

• For anything with more than one step, or where you want history, tests and freshness alerts, use a pipeline: dbt incremental models in your own Git repository, run on a schedule.

4. Repoint and retire

• Dashboards: connect BI tools through JDBC or SQLAlchemy and swap bq_sales.sales.orders for lake.default.orders.
• Scheduled queries: recreate them as scheduled jobs.
• Retire: once nothing reads the BigQuery table, drop the BigQuery catalog, or keep it read-only for history.