How to Schedule Jobs in BigQuery

Galaxy Glossary

How do I schedule recurring SQL jobs in BigQuery?

BigQuery scheduled jobs let you run SQL statements automatically on a fixed timetable, eliminating manual refreshes.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why schedule jobs in BigQuery?

Automating reports, refreshes, and exports saves time and prevents missed updates. Scheduled queries run in Google Cloud, use service-account billing, and integrate with Stackdriver for alerts.

What is the exact CREATE SCHEDULE syntax?

CREATE SCHEDULE wraps a normal SELECT, INSERT, or DML statement in a job definition.Options control frequency, windowing, destination tables, and alerting.

Minimum viable syntax

CREATE SCHEDULE my_project.my_dataset.daily_sales OPTIONS(schedule="every 24 hours") AS SELECT * FROM ...;

Full option list

Key options: schedule (cron/interval), time_zone, destination_table_name_template, write_disposition, start_time, end_time, enabled, and notification_pubsub_topic.

How do you schedule a daily sales summary?

Create a schedule that runs nightly, truncates the destination, and inserts aggregated sales totals into sales_summary.

How do you monitor and manage scheduled queries?

Use the BigQuery UI "Scheduled Queries" tab, the bq ls --transfer_config CLI, or Cloud Monitoring metrics.Pause, resume, backfill, or delete schedules as needed.

Best practices for scheduling jobs

Version-control SQL, parameterize dates with @run_date, use UTC or a fixed time_zone, set write_disposition explicitly, and send Pub/Sub alerts on failures.

Common mistakes and fixes

Omitting write_disposition: default is APPEND, which bloats tables. Set WRITE_TRUNCATE or MERGE when you need fresh data.

Forgetting time_zone: schedules default to UTC. Specify your locale to avoid 24-hour drift.

FAQs

Can I trigger backfills?

Yes.In the UI choose "Backfill" or run bq mk --transfer_config --run-backfill to re-run for past dates.

Does CREATE SCHEDULE support DML?

Absolutely. Any single SQL statement—including INSERT, MERGE, or CALL—can be wrapped in a schedule.

How do I rotate service accounts?

Edit the schedule and change the service account or update IAM roles; jobs start using the new credentials immediately.

Why How to Schedule Jobs in BigQuery is important

How to Schedule Jobs in BigQuery Example Usage


-- Nightly summary loaded into sales_summary_YYYYMMDD
CREATE SCHEDULE my_proj.analytics.daily_sales_summary
OPTIONS (
  schedule = '0 3 * * *',        -- 03:00 every day
  time_zone = 'UTC',
  destination_table_name_template = 'sales_summary_${run_date}',
  write_disposition = 'WRITE_TRUNCATE'
) AS
SELECT
  DATE(o.order_date)   AS sale_date,
  COUNT(o.id)          AS orders,
  SUM(oi.quantity)     AS items_sold,
  SUM(oi.quantity*p.price) AS revenue
FROM `my_proj.ecom.Orders` o
JOIN `my_proj.ecom.OrderItems` oi ON oi.order_id = o.id
JOIN `my_proj.ecom.Products` p     ON p.id = oi.product_id
WHERE DATE(o.order_date)=DATE_SUB(@run_date,INTERVAL 1 DAY)
GROUP BY sale_date;

How to Schedule Jobs in BigQuery Syntax


CREATE SCHEDULE project_id.dataset_id.schedule_name
OPTIONS (
  schedule = 'every 24 hours',
  time_zone = 'America/New_York',
  destination_table_name_template = 'daily_sales_${run_date}',
  write_disposition = 'WRITE_TRUNCATE',
  enabled = true,
  notification_pubsub_topic = 'projects/my_project/topics/bq_schedule_alerts'
)
AS
SELECT
  o.id AS order_id,
  o.order_date,
  c.id AS customer_id,
  SUM(oi.quantity * p.price) AS order_total
FROM `project.dataset.Orders` o
JOIN `project.dataset.OrderItems` oi ON o.id = oi.order_id
JOIN `project.dataset.Products` p ON oi.product_id = p.id
JOIN `project.dataset.Customers` c ON o.customer_id = c.id
WHERE DATE(o.order_date) = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY order_id, order_date, customer_id;

Common Mistakes

Using the default APPEND write_disposition, which keeps piling data and inflates storage bills. Fix: set write_disposition='WRITE_TRUNCATE' or MERGE logic.
Leaving the schedule in UTC when stakeholders expect local midnight runs. Fix: specify time_zone='America/New_York' (or your locale).