Scheduling Queries in BigQuery
A scheduled query is SQL that BigQuery runs for you on a timetable. This guide covers the run parameters, destination table templates checked against Google's own examples, backfills, credentials and notifications, and shows on real data why an appending statement doubles on a rerun while a MERGE does not.
GloryTecks Team
Training & Placement Faculty
Last updated: 2 October 2026
A scheduled query is the smallest pipeline on Google Cloud: a SQL statement, a timetable and a destination. No orchestrator, no servers, no code besides the SQL. For a daily summary table that is often all you need. It is also easy to get subtly wrong, because the statement will be run by a clock, rerun after failures and replayed over past dates, and it has to give the right answer every time.
What you are setting up
Scheduled queries are built on the BigQuery Data Transfer Service (Google Cloud: Data Transfer Service). The query must be GoogleSQL and can include DDL and DML, so it can create tables and modify them as well as select (Google Cloud: scheduling queries). A few properties are worth knowing before the first one:
- Schedules are in UTC. The console converts the time you enter from local time, and UTC has no daylight saving (Google Cloud: transfer configuration reference).
- The shortest interval is 5 minutes.
- They are priced as ordinary queries and run as batch query jobs.
- Avoid the top of the hour. Google's documentation warns that a query scheduled exactly on the hour might trigger more than once, which can duplicate data from an
INSERT. Schedule at 02:10, not 02:00.
The two run parameters
Every run receives two parameters:
| Parameter | Type | Meaning |
|---|---|---|
| @run_time | TIMESTAMP | The intended time of the run, in UTC |
| @run_date | DATE | The logical calendar date of the run |
"Intended" is the important word. A daily run always has a run time exactly 24 hours after the previous one, even if it actually started a few seconds late or was retried an hour later.
That is why a scheduled query should use these parameters and not CURRENT_DATE(). A statement written with CURRENT_DATE() computes something different each time it is run. A statement written with @run_date computes the same thing for a given date however late, and however often, it runs:
SELECT DATE(pickup_at) AS day, payment_type,
COUNT(*) AS trips, SUM(total_amount) AS revenue
FROM analytics.trips
WHERE DATE(pickup_at) = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY day, payment_type;Where the result goes
For a SELECT, you name a destination table and a write preference: append, or overwrite. The write happens as one atomic update when the job completes, and only if the query succeeds.
The destination name can include the run parameters, using a small template language:
{run_date}becomes the run date asYYYYMMDD.{run_time|"format"}formats the run time.{run_time-1h|"format"}shifts it first. Offsets are in hours, minutes and seconds; days are not supported, so use24h.
A $ before the date addresses a partition, as in sales${run_date}.
Templates, schedules and reruns, worked through
The script does four things. It implements the template syntax from the documentation and checks it against the seven examples Google publishes. It prints what a daily schedule produces. It plans a backfill. And it runs the actual SQL twice for the same date, as an append and as a merge, on New York taxi trips for January 2024 (NYC TLC: trip record data). The template code is a model, and the SQL is run on DuckDB after translation by the sqlglot library. Neither is a run of BigQuery.
# A model of the destination table templating used by BigQuery scheduled queries, written from the
# documented syntax: {run_date} and {run_time[+-offset]|"time_format"}. It is not BigQuery. It is
# checked against the examples in Google's documentation, then used to plan a schedule and a backfill.
import re
from datetime import datetime, timedelta
UNIT = {"h": "hours", "m": "minutes", "s": "seconds"} # days are not supported as an offset
PATTERN = re.compile(r'\{run_date\}|\{run_time(?:([+-])([\d.]+)([hms]))?\|"([^"]*)"\}')
def render(template, run_time):
def replace(match):
if match.group(0) == "{run_date}":
return run_time.strftime("%Y%m%d")
sign, amount, unit, time_format = match.groups()
shifted = run_time
if sign:
delta = timedelta(**{UNIT[unit]: float(amount)})
shifted = run_time + delta if sign == "+" else run_time - delta
return shifted.strftime(time_format)
return PATTERN.sub(replace, template)
# 1. The examples from the documentation, all for a run_time of 2018-02-15 00:01:00 UTC.
documented = [
('mytable', 'mytable'),
('mytable_{run_time|"%Y%m%d"}', 'mytable_20180215'),
('mytable_{run_time+25h|"%Y%m%d"}', 'mytable_20180216'),
('mytable_{run_time-1h|"%Y%m%d"}', 'mytable_20180214'),
('mytable_{run_time+1.5h|"%Y%m%d%H"}', 'mytable_2018021501'),
('mytable_{run_time+90m|"%Y%m%d%H"}', 'mytable_2018021501'),
('{run_time+97s|"%Y%m%d"}_mytable_{run_time+97s|"%H%M%S"}', '20180215_mytable_000237'),
]
run_time = datetime(2018, 2, 15, 0, 1, 0)
matches = sum(render(template, run_time) == expected for template, expected in documented)
print(f"documented examples reproduced: {matches} of {len(documented)}\n")
# 2. A daily schedule at 02:10 UTC that writes the previous day's data.
print("run_time (UTC) sales${run_date} sales_{run_time-24h|\"%Y%m%d\"}")
for day in (1, 2, 3):
run_time = datetime(2026, 10, day, 2, 10)
print(f'{run_time:%Y-%m-%d %H:%M} {render("sales${run_date}", run_time):22} '
+ render('sales_{run_time-24h|"%Y%m%d"}', run_time))
# 3. A backfill: the range includes the start date and excludes the end date.
start, end = datetime(2026, 9, 27), datetime(2026, 9, 30)
runs = [start + timedelta(days=n, hours=2, minutes=10) for n in range((end - start).days)]
print(f"\nbackfill from {start:%Y-%m-%d} to {end:%Y-%m-%d}: {len(runs)} runs")
for run_time in runs:
print(f" run_time {run_time:%Y-%m-%d %H:%M} @run_date {run_time:%Y-%m-%d}")
# 4. The scheduled statement itself, run for one date, rerun, and run for the next date. The SQL is
# GoogleSQL translated by sqlglot and run on DuckDB with real trip data; it is not a run of BigQuery.
import duckdb
import sqlglot
from datetime import date
con = duckdb.connect()
con.execute("""CREATE TABLE trips AS
SELECT tpep_pickup_datetime AS pickup_at, payment_type, total_amount
FROM read_parquet('../../dedata/yellow_tripdata_2024-01.parquet')""")
DAILY = """
SELECT DATE(pickup_at) AS day, payment_type, COUNT(*) AS trips, SUM(total_amount) AS revenue
FROM trips
WHERE DATE(pickup_at) = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY day, payment_type"""
APPEND = "INSERT INTO daily_sales (day, payment_type, trips, revenue)" + DAILY
MERGE = f"""
MERGE daily_sales AS target
USING ({DAILY}) AS source
ON target.day = source.day AND target.payment_type = source.payment_type
WHEN MATCHED THEN
UPDATE SET trips = source.trips, revenue = source.revenue
WHEN NOT MATCHED THEN
INSERT (day, payment_type, trips, revenue)
VALUES (source.day, source.payment_type, source.trips, source.revenue)"""
print("\nstatement run_date rows in daily_sales trips recorded")
for name, statement in [("append", APPEND), ("merge", MERGE)]:
con.execute("CREATE OR REPLACE TABLE daily_sales (day DATE, payment_type BIGINT, trips BIGINT, revenue DOUBLE)")
translated = sqlglot.transpile(statement, read="bigquery", write="duckdb")[0]
for run_date in ["2024-01-16", "2024-01-16", "2024-01-17"]:
con.execute(translated, {"run_date": date.fromisoformat(run_date)})
rows, trips = con.execute("SELECT COUNT(*), SUM(trips) FROM daily_sales").fetchone()
print(f"{name:10} {run_date} {rows:19} {trips:16,}")documented examples reproduced: 7 of 7
run_time (UTC) sales${run_date} sales_{run_time-24h|"%Y%m%d"}
2026-10-01 02:10 sales$20261001 sales_20260930
2026-10-02 02:10 sales$20261002 sales_20261001
2026-10-03 02:10 sales$20261003 sales_20261002
backfill from 2026-09-27 to 2026-09-30: 3 runs
run_time 2026-09-27 02:10 @run_date 2026-09-27
run_time 2026-09-28 02:10 @run_date 2026-09-28
run_time 2026-09-29 02:10 @run_date 2026-09-29
statement run_date rows in daily_sales trips recorded
append 2024-01-16 5 77,033
append 2024-01-16 10 154,066
append 2024-01-17 15 247,123
merge 2024-01-16 5 77,033
merge 2024-01-16 5 77,033
merge 2024-01-17 10 170,090The template model matches all seven documented examples, including the fractional offset and the 97-second one, so it can be trusted for the next two blocks.
A run just after midnight UTC writes the wrong day unless you shift it. The schedule block shows the difference between {run_date} and {run_time-24h|...}. A run at 02:10 on 2 October is processing 1 October's data. Naming the destination by the run date files it under the 2nd. Decide which convention you want and state it; mixed conventions are a common source of off-by-one-day reports.
A backfill includes its start date and excludes its end date. A range from 27 to 30 September produces three runs, for the 27th, 28th and 29th. Each receives the run date it would have had originally, which is only useful because the query uses @run_date.
Appending is not safe to repeat; merging is. This is the last block, on real data. Run for 16 January, the appending statement writes 5 rows and 77,033 trips. Run again for the same date, it writes them again: 10 rows, 154,066 trips. The MERGE gives 5 rows both times, then 10 after the next date. Nothing in the output of the appending version signals an error.
A statement that can be rerun
A scheduled query will be rerun: by a retry, by a backfill, by the duplicate trigger at the top of the hour, or by a person. Two patterns survive that.
Overwrite a partition. For a SELECT with a destination, write to one partition and choose overwrite. Each run replaces exactly its own day.
Merge on the key. For DML, use a single MERGE keyed on the grain of the table, as in the script:
MERGE reporting.daily_sales AS target
USING (
SELECT DATE(pickup_at) AS day, payment_type,
COUNT(*) AS trips, SUM(total_amount) AS revenue
FROM analytics.trips
WHERE DATE(pickup_at) = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY day, payment_type) AS source
ON target.day = source.day AND target.payment_type = source.payment_type
WHEN MATCHED THEN
UPDATE SET trips = source.trips, revenue = source.revenue
WHEN NOT MATCHED THEN
INSERT (day, payment_type, trips, revenue)
VALUES (source.day, source.payment_type, source.trips, source.revenue);With a DDL or DML statement there is no write preference to set; the statement itself decides what happens to existing rows.
Creating, and backfilling, from the command line
# A daily summary that overwrites its destination table
bq query \
--use_legacy_sql=false \
--destination_table=reporting.daily_sales_latest \
--display_name='Daily sales summary' \
--schedule='every 24 hours' \
--replace=true \
'SELECT payment_type, COUNT(*) AS trips FROM analytics.trips
WHERE DATE(pickup_at) = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY payment_type'
# The same idea as a transfer configuration, run by a service account
bq mk \
--transfer_config \
--target_dataset=reporting \
--display_name='Daily sales summary' \
--params='{"query":"SELECT 1 FROM analytics.trips","destination_table_name_template":"daily_sales_{run_date}","write_disposition":"WRITE_TRUNCATE"}' \
--data_source=scheduled_query \
--service_account_name=reporting-sa@analytics-prod.iam.gserviceaccount.com
# Rerun the schedule over past dates
bq mk \
--transfer_run \
--start_time 2026-09-27T00:00:00Z \
--end_time 2026-09-30T00:00:00Z \
projects/analytics-prod/locations/us/transferConfigs/1234a123-1234-1a23-1be9-12ab3c456de7The commands follow the documented forms; names are placeholders, and the query in the second is shortened to fit.
Whose query is it?
By default a scheduled query runs with the credentials of the person who created it. If that person loses access or leaves, the query fails. Run it as a service account instead, which also lets you grant the query only the access it needs. Assigning one requires permission to use that service account. Service accounts and workload identity explains the model.
Turn on failure notifications when you create the schedule. Email notification of failed runs is a checkbox, and a Pub/Sub topic can be named for automated handling. The command-line tool cannot configure notifications, so set them in the console.
When to move on from scheduled queries
They suit a handful of independent statements. Signs that you have outgrown them:
- One query must wait for another. There are no dependencies between scheduled queries, only clock times that you hope are far enough apart.
- A query must wait for data to arrive. A schedule cannot check that yesterday's load finished.
- There are dozens of them. Nobody can see the whole flow.
At that point use a tool that models dependencies: Dataform for SQL transformations inside BigQuery, or Airflow for workflows across services. For a repeated aggregate that should stay fresh by itself, consider materialized views first. And for loading the tables these queries read, see loading data into BigQuery.
What is the difference between run_time and run_date in a scheduled query?
How do I rerun a scheduled query for past dates?
Why did my scheduled query insert duplicate rows?
Which time zone do scheduled queries use?
Scheduling and orchestration are covered in the Data Engineering course.
Sources
- Scheduling queries (Google Cloud Documentation) — Google Cloud
- Introduction to the BigQuery Data Transfer Service (Google Cloud Documentation) — Google Cloud
- REST Resource: transferConfigs (Google Cloud Documentation) — Google Cloud
- TLC Trip Record Data (NYC Taxi and Limousine Commission) — NYC Taxi and Limousine Commission
GloryTecks Team
Training & Placement Faculty
The GloryTecks faculty team mentors learners across Data Science, AI and Data Engineering, with a focus on real-time projects and placement readiness in Hyderabad.
Want to master GCP?
Join GloryTecks’ expert-led Data Engineering training in Hyderabad with real-time projects and 100% placement support.
Related articles
BigQuery Explained: Google's Serverless Data Warehouse
What “serverless” means in BigQuery, why its bill depends on the bytes a query reads, and the three habits that keep that number small: select only needed columns, partition and cluster tables, and check a query’s cost before running it.
Materialized Views in BigQuery Explained
A materialized view stores the result of a query and keeps it current as the base table changes. This guide shows how BigQuery combines stored results with new rows, models it on 2.9 million real trips, and covers refresh settings, max_staleness, smart tuning, unsupported SQL and when a scheduled table is better.
Loading Data into BigQuery: Batch and Streaming
BigQuery can be loaded in batches from files or fed continuously through the Storage Write API. This guide compares the methods by latency, cost, limits and what a retry does, with documented commands, a model of at-least-once against exactly-once delivery, and a rule for choosing.