Scheduled queries in BigQuery

Scheduled queries run SQL on a schedule through the Data Transfer Service, with @run_time and @run_date parameters for incremental loads. They cost the same as normal queries, can fire twice when scheduled exactly on the hour, and run as their creator unless you give them a service account.

A scheduled query is a SQL statement BigQuery runs for you on a schedule: every hour, every day at 06:00, or on a custom cron-like schedule. It is the simplest way to rebuild a summary table every night or append each day’s data to a history table, without an outside scheduler. Scheduled queries are managed by the BigQuery Data Transfer Service, but they are billed and limited like ordinary queries.

Two ways to write results

A scheduled query can either be a statement that writes data itself, or a SELECT whose result BigQuery writes to a destination table you configure.

A statement that writes its own result (DML or DDL). The SQL decides what happens, and no destination settings are needed:

MERGE mydataset.daily_sales AS t
USING (
  SELECT DATE(ordered_at) AS day, store, SUM(amount) AS revenue
  FROM mydataset.orders
  WHERE DATE(ordered_at) = DATE_SUB(@run_date, INTERVAL 1 DAY)
  GROUP BY day, store
) AS s
ON t.day = s.day AND t.store = s.store
WHEN MATCHED THEN UPDATE SET revenue = s.revenue
WHEN NOT MATCHED THEN INSERT ROW;

A SELECT with a destination table. You pick the dataset, a table name and a write preference: WRITE_TRUNCATE replaces the table on each run, WRITE_APPEND adds to it. The destination must be in the same location as the data. The table name can contain a template that changes per run, such as daily_sales_{run_date} for one table per day or daily_sales${run_date} for one partition per day (the $ is a partition decorator).

Prefer the first form. A MERGE or CREATE OR REPLACE TABLE is idempotent: running it twice for the same day gives the same table, which matters for the duplicate runs described below.

@run_time and @run_date

Every run passes two parameters:

• @run_time is the TIMESTAMP the run was scheduled for, in UTC. For a query scheduled every 24 hours, consecutive values are exactly 24 hours apart, even if a run started late.
• @run_date is the DATE of @run_time, in UTC.

Use them instead of CURRENT_DATE(). They make a run reproducible: a run for March 2 processes March 2’s data even if it runs on March 3, and a backfill (below) can rerun past days with the right dates. They also make the query cacheable. To test the query by hand, pass them as query parameters: bq query --parameter=run_date:DATE:2026-03-02 .... See Query parameters.

Both are UTC. If your business day is in another time zone, convert: DATE(@run_time, 'America/New_York'). See Dates, times and time zones.

Avoid scheduling exactly on the hour

Google’s docs warn that a scheduled query that runs exactly on the hour, like 09:00, can be triggered more than once, and an INSERT would then add the same rows twice. Schedule at an odd minute, such as 08:58 or 09:03, and write queries that are safe to rerun (MERGE, CREATE OR REPLACE, WRITE_TRUNCATE on a partition) rather than plain appends.

Backfilling past dates

If source data arrived late or a run failed, open the scheduled query and choose Schedule backfill with a date range. BigQuery runs the query once for each scheduled time in the range, each with its own @run_time and @run_date. The range includes the first date and excludes the last, and it is given in UTC even though the console shows the schedule in your local time.

In Querylab.io, open Scheduled Queries from the header and click Backfill… on the schedule’s card. You pick a First day and a Last day, and both days are included. The last day can be yesterday at the latest. Dates are read in your time zone, and the dialog shows the UTC range it sends to BigQuery. Before you click Start backfill, the dialog lists each run time with the run_date that run gets, and gives a cost estimate from a dry run with @run_time and @run_date set. If the schedule appends rows to its destination table, a warning says that each backfill run adds those rows again. For an interval schedule such as every 24 hours, the run times aren’t listed. Schedules that only run on demand have no Backfill… button.

Whose credentials run it

By default a scheduled query runs as the user who created it, with that user’s permissions and quotas. If that person leaves and their account is removed, the scheduled query starts failing. For anything that matters, set a service account when you create it: the query then runs with the service account’s permissions and quotas, and its jobs are still billed to the project that holds the scheduled query. You need the Service Account User role on that service account to assign it.

Cost, priority and limits

• Scheduled queries cost the same as running the query yourself: bytes billed on on-demand, slots on capacity pricing. The Data Transfer Service adds no charge for them. Querylab.io’s per-query limit and budgets don’t cover scheduled runs (cost limits), so look at the estimate in the editor before you schedule the query.
• Runs must be at least 5 minutes apart.
• They count toward the same query quotas as manual queries, not the load-job quotas. See Quotas and limits.
• Failed runs show in the scheduled query’s run history and in INFORMATION_SCHEMA.JOBS. Set up email notifications or Pub/Sub notifications on the scheduled query to hear about failures.

Scheduled queries vs the alternatives