# BigQuery
> Load data from Google BigQuery into MotherDuck using the duckdb-bigquery community extension.
MotherDuck works great as both a replacement and a sidecar to BigQuery: copy the tables you need with a few lines of SQL and query them fast and cost-effectively.

## Choose an execution environment

This integration runs the `bigquery` community extension in a local DuckDB
process. Use it from the DuckDB CLI or a DuckDB SDK for interactive work.

MotherDuck cloud SQL, including read-only MCP queries, cannot install or load
community extensions. For scheduled ingestion that runs on MotherDuck, use a
[Flight](/concepts/flights/) and load the extension in the Flight's in-process
DuckDB runtime. See [Where code runs](/concepts/execution-environments/) for
the boundary, and [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest/)
for the scheduled pattern. If you're moving a whole workload off BigQuery rather
than copying a table, start with [Migrate from BigQuery](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/),
which covers inventory, dialect translation, and cutover.

To load data from BigQuery into MotherDuck, use the [`duckdb-bigquery` community extension](https://github.com/hafenkran/duckdb-bigquery). It reads through the BigQuery Storage Read API with parallel streams, filter pushdown, and Arrow compression — and loads results straight into DuckDB or MotherDuck without any glue code.

## Prerequisites

- DuckDB installed (using the CLI or Python).
- Access to a GCP project with BigQuery enabled.
- Valid Google Cloud credentials, provided through one of:
  - the `GOOGLE_APPLICATION_CREDENTIALS` environment variable, or
  - `gcloud auth application-default login`.

Minimum required IAM roles:

- `BigQuery Data Viewer`
- `BigQuery Job User`

## Loading data from BigQuery into MotherDuck

The following examples use the [DuckDB CLI](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx), but you can use any local DuckDB client.

### Install and load the extension

```sql
INSTALL bigquery FROM community;
LOAD bigquery;
```

### Attach a BigQuery project

To read data from your project, attach it like you would attach a DuckDB database:

```sql
ATTACH 'project=my-gcp-project' AS bq (TYPE bigquery, READ_ONLY);
```

To read from a public dataset, use the following syntax:

```sql
ATTACH 'project=bigquery-public-data dataset=pypi billing_project=my-gcp-project'
AS bq_public (TYPE bigquery, READ_ONLY);
```

### Query a table

Once attached, you can query BigQuery tables directly using standard SQL syntax:

```sql
SELECT * FROM bq.dataset_name.table_name LIMIT 10;
```

Behind the scenes, this uses `bigquery_scan`. The extension also exposes two functions you can call directly:

**`bigquery_scan`** — for direct reads from a single table:

```sql
SELECT * FROM bigquery_scan('my_gcp_project.my_dataset.my_table');
```

**`bigquery_query`** — for custom [GoogleSQL](https://cloud.google.com/bigquery/docs/introduction-sql), including views and external tables that the Storage Read API can't access on its own:

```sql
SELECT * FROM bigquery_query(
  'my_gcp_project',
  'SELECT * FROM `my_gcp_project.my_dataset.my_table` WHERE column = "value"'
);
```

Both functions share the same Arrow scan engine. For very large reads, you can enable parallel read streams by relaxing DuckDB's default ordering guarantee:

```sql
SET preserve_insertion_order = FALSE;
```

### Load data into MotherDuck

Verify the `motherduck_token` environment variable is set, then attach MotherDuck:

```sql
ATTACH 'md:';
```

Use `CREATE TABLE ... AS` to create a new table, or `INSERT INTO ... SELECT` to append data to an existing one:

```sql
CREATE DATABASE IF NOT EXISTS pypi_playground;
USE pypi_playground;

CREATE TABLE IF NOT EXISTS duckdb_sample AS
SELECT *
FROM bq_public.pypi.file_downloads
WHERE project = 'duckdb'
AND timestamp = TIMESTAMP '2025-05-26 00:00:00'
LIMIT 100;
```

## Reload a date range without duplicates

`INSERT INTO ... SELECT` appends, so rerunning a load after a failure or to pick up late-arriving rows duplicates the range. For a date-partitioned source, delete the target rows for the range and insert the same range from BigQuery inside one transaction. Repeating the range then replaces it instead of adding to it, and a failure between the two statements rolls back to the previous state:

```sql
USE my_db;

BEGIN TRANSACTION;

-- Clear exactly the range you're about to re-read
DELETE FROM main.events
WHERE event_date BETWEEN DATE '2025-05-01' AND DATE '2025-05-03';

-- Push the same range to BigQuery so it prunes partitions and bills fewer bytes
INSERT INTO main.events
SELECT *
FROM bigquery_scan(
    'my-gcp-project.analytics.events',
    filter := 'event_date BETWEEN "2025-05-01" AND "2025-05-03"'
);

COMMIT;
```

The `DELETE` predicate and the BigQuery filter must select the same rows. If the read filters on an extra column and the `DELETE` doesn't, each rerun removes rows it never puts back. Use `bigquery_query` in place of `bigquery_scan` when the range comes from a view or needs GoogleSQL, and keep the same filter in its `WHERE` clause.

:::warning
A BigQuery `TIMESTAMP` is an absolute instant, but the extension loads it as DuckDB `TIMESTAMP` without a timezone, so the stored value is UTC wall-clock time. If the range column is derived from a timestamp with `DATE(ts)` in a non-UTC session, rows near midnight fall into a different day on each side. Compare ranges on a stored `DATE` column, or derive the day in UTC on both sides.
:::

Before you point downstream queries at the copy, run the same aggregate on both sides for each loaded range and compare:

```sql
SELECT
    event_date,
    count(*) AS event_rows
FROM main.events
WHERE event_date BETWEEN DATE '2025-05-01' AND DATE '2025-05-03'
GROUP BY event_date
ORDER BY event_date;
```

A mismatch usually comes from a filter that doesn't match on both sides, rows that arrived in BigQuery after the load, or the timezone shift above. [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest/) runs this pattern on a schedule with a watermark and an overlap window for late data.

## Load on a schedule with a Flight

A [Flight](/concepts/flights) is Python that MotherDuck schedules and runs next to your data. Use one instead of running the copy by hand when the load should repeat on a cron, retry on a transient failure, and keep a run history.

Because community extensions load in a DuckDB client rather than on MotherDuck's server-side runtime, the Flight installs `bigquery` in its own in-process DuckDB first, then loads `motherduck` and attaches:

```python
import duckdb

def main():
    con = duckdb.connect()
    con.execute("INSTALL bigquery FROM community; LOAD bigquery;")
    con.execute("LOAD motherduck;")
    con.execute("ATTACH 'md:';")
    con.execute("ATTACH 'project=my-gcp-project' AS bq (TYPE bigquery, READ_ONLY)")
    con.execute(
        "CREATE OR REPLACE TABLE my_db.main.events AS "
        "SELECT * FROM bq.analytics.events"
    )

if __name__ == "__main__":
    main()
```

Load `bigquery` before `motherduck`, as on [DuckDB extensions in MotherDuck](/concepts/duckdb-extensions), so the BigQuery scan stays in the local client instead of being routed to MotherDuck.

The service-account JSON is a credential, so keep it in a [Flight secret](/sql-reference/motherduck-sql-reference/create-secret#flight-secrets) rather than in the Flight's source or config. Each `PARAMS` key is injected into the run as an environment variable, which the Flight writes to a file and points `GOOGLE_APPLICATION_CREDENTIALS` at:

```sql
CREATE SECRET gcp_creds IN MOTHERDUCK (
    TYPE FLIGHTS,
    PARAMS MAP {
        'GOOGLE_APPLICATION_CREDENTIALS_JSON': '<service_account_json>'
    }
);
```

Create the Flight with [`MD_CREATE_FLIGHT`](/sql-reference/motherduck-sql-reference/flights/md-create-flight), passing the Python above as `source_code`, `flight_secret_names := ['gcp_creds']`, and a `schedule_cron` for the cadence you want. [Incrementally ingest BigQuery into MotherDuck](/cookbook/flight-bigquery-ingest) is a ready-made version that handles the credentials file, watermarks, and backfill windows.


---

## 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=%2Fintegrations%2Fdatabases%2Fbigquery%2F&page_title=BigQuery&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.