# Continuous queries in BigQuery

> A continuous query processes rows as they land in a table and writes results to another table, Pub/Sub, Bigtable or Spanner. It needs an Enterprise reservation, supports a subset of SQL, can emit duplicates, and stops after 2 days on a user account or 150 on a service account.

Source: https://docs.querylab.io/bigquery/continuous-queries/

Updated: 2026-10-06

A normal query reads a table once and stops. A continuous query keeps running: as new rows are written to its source table, it processes them and sends the results on within seconds. It turns BigQuery into an event processor: enrich incoming events with SQL or a model, filter for alerts, and push the results to Pub/Sub, Bigtable, Spanner or another table, all without a separate streaming system.

## What it looks like

A continuous query is an `INSERT ... SELECT` or an `EXPORT DATA` statement, started with the continuous flag:

```sql
EXPORT DATA OPTIONS (
  format = 'CLOUD_PUBSUB',
  uri = 'https://pubsub.googleapis.com/projects/myproject/topics/large-orders'
) AS
SELECT TO_JSON_STRING(STRUCT(order_id, customer_id, amount)) AS message
FROM APPENDS(TABLE mydataset.orders, CURRENT_TIMESTAMP() - INTERVAL 10 MINUTE)
WHERE amount > 1000;
```

```sh
bq query --use_legacy_sql=false --continuous=true \
  --connection_property=service_account=pipeline@myproject.iam.gserviceaccount.com \
  "$(cat large_orders.sql)"
```

In the console, switch the query editor to continuous query mode. `APPENDS` with a start time tells the query where to begin. The start must be within the table's time travel window (seven days by default), and you leave out `APPENDS`' end time so the query never stops. Rows are picked up however they arrive: streaming, load jobs, `INSERT` statements or other continuous queries.

## What you can write to

- Another BigQuery table, with `INSERT INTO ... SELECT`.
- Pub/Sub, Bigtable or Spanner, with `EXPORT DATA`. Exporting to Pub/Sub requires running the query as a service account.

Anything else, like Cloud Storage files or `MERGE` into a table, isn't supported.

## What SQL is allowed

Continuous queries support filters, projections, stateless functions (casts, string, date and JSON functions), `UNNEST`, and a few generative AI and ML functions such as `AI.GENERATE`, `ML.UNDERSTAND_TEXT` and `ML.TRANSLATE`. Joins, aggregations over time windows and `UNION ALL` are supported as stateful operations, in Preview.

Not allowed:

- `DISTINCT`, `EXISTS`, set operators other than `UNION ALL`, `PIVOT`, `UNPIVOT`, `TABLESAMPLE`, recursive CTEs, window function calls and user-defined functions
- DML other than `INSERT`, DDL, scripts
- Reading external tables, wildcard tables, `INFORMATION_SCHEMA`, materialized views, CDC upsert tables, or tables with row- or column-level security

You can't change the SQL of a running continuous query. Stop it and start a new one, from the point the old one stopped (use `APPENDS` with that time).

## Reservations and cost

Continuous queries don't run on on-demand pricing. They need an Enterprise or Enterprise Plus [reservation](https://docs.querylab.io/bigquery/reservations/) with an assignment of job type `CONTINUOUS`:

- that reservation can't hold assignments of other job types,
- it is limited to 500 slots unless Google raises it,
- BigQuery admits a new continuous query only if 10 slots are available for it, even though an idle query uses about 1 slot,
- slot autoscaling is supported, and an idle continuous query keeps the reservation from scaling to zero.

So the cost is the slot time of the reservation for as long as the queries run, plus the destination's own costs (Pub/Sub messages, Bigtable or Spanner writes). See [On-demand vs capacity pricing](https://docs.querylab.io/bigquery/on-demand-vs-capacity-pricing/).

## How long it runs

- A continuous query started with your user account stops after 2 days, when its access token expires. Use a service account for anything long-lived.
- With a service account it can run for up to 150 days, then fails. Plan to restart it, starting from the last processed time.
- If it falls more than 48 hours behind the incoming data, it fails.

## Duplicates

Google states that temporary issues can make a continuous query reprocess some data, so its output can contain duplicates. Make the receiver idempotent: include an event ID in each message and deduplicate on it, or write to a table and deduplicate when you read.

## Continuous queries vs the alternatives

| Need                                              | Use                                                  |
| ------------------------------------------------- | ---------------------------------------------------- |
| React to each new row within seconds              | a continuous query                                   |
| Recompute a result every hour or day              | a [scheduled query](https://docs.querylab.io/bigquery/scheduled-queries/)    |
| A fast, automatically fresh aggregate for queries | a [materialized view](https://docs.querylab.io/bigquery/materialized-views/) |

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to continuous queries](https://cloud.google.com/bigquery/docs/continuous-queries-introduction)

- [Create continuous queries](https://cloud.google.com/bigquery/docs/continuous-queries)