Skip to content
Tutorials Center
My learning

Topics

Lesson 32 of 37 ยท Pipelines and Integrations

Scheduled Queries and Routines

Automate BigQuery with scheduled queries and parameters, then package logic into SQL and JavaScript UDFs, stored procedures and table functions.

Analysis becomes a pipeline when it runs without you. BigQuery has two features for that: scheduled queries, which run a statement on a timetable, and routines, which package reusable logic as functions and procedures. Together they let you build a small, dependable ELT system entirely in SQL: refresh a summary table every night, then call a procedure that validates the result.

Scheduling uses the BigQuery Data Transfer Service and needs a real project, so those parts are read-only. The runnable examples model the logic of a daily refresh and a reusable function with SQL and Python.

Scheduled queries

In the console, write a query, choose Schedule, set a frequency such as "every day 02:00 UTC" or a cron-like custom schedule, and pick a destination. You can also create one from the command line:

Terminal
bq mk --transfer_config \
  --target_dataset=shop_reports \
  --display_name='Daily revenue' \
  --data_source=scheduled_query \
  --schedule='every 24 hours' \
  --params='{
    "query": "SELECT @run_date AS day, SUM(total) AS revenue FROM shop.orders WHERE order_date = @run_date",
    "destination_table_name_template": "daily_revenue_{run_date}",
    "write_disposition": "WRITE_TRUNCATE",
    "partitioning_field": ""
  }'

Things to know:

  • Two special parameters are available: @run_date (a DATE) and @run_time (a TIMESTAMP), the logical time of the run in UTC. Use them instead of CURRENT_DATE() so a backfill for a past day computes the right answer.
  • The destination table name can include a template like to create one table per day, or you can write into one partitioned table with WRITE_TRUNCATE on the partition so reruns stay idempotent.
  • A scheduled query runs as the user who created it, or as a service account if you choose one. Prefer a service account for production.
  • Failures send notifications (email or Pub/Sub). Check the run history in the transfers page, and set up alerts.
  • Scheduled queries can run DDL and DML too, not only SELECT, for example a daily MERGE.
  • Add maximum_bytes_billed to avoid a runaway job (see controlling query cost).

A daily refresh pattern

The safest recurring job rebuilds exactly one day, so it can be repeated and backfilled:

SQL
DELETE FROM shop_reports.daily_revenue WHERE day = @run_date;

INSERT INTO shop_reports.daily_revenue (day, country, revenue, orders)
SELECT @run_date, country, SUM(total), COUNT(*)
FROM shop.orders
WHERE order_date = @run_date
GROUP BY country;

You can model that delete-then-insert idempotency with SQLite: running the same day twice leaves one set of rows.

SQL An idempotent daily refresh

Press Run (or Ctrl + Enter) to see the result here.

The table has one row per country, even though the job ran twice.

User-defined functions

A UDF is a function you define once and call in queries. A SQL UDF is the simplest:

SQL
CREATE OR REPLACE FUNCTION shop.margin_pct(price NUMERIC, cost NUMERIC)
RETURNS NUMERIC
AS (ROUND(SAFE_DIVIDE(price - cost, price) * 100, 1));

SELECT product, shop.margin_pct(price, cost) AS margin
FROM shop.products;

A JavaScript UDF handles logic that is awkward in SQL, such as parsing strings:

SQL
CREATE TEMP FUNCTION slugify(s STRING)
RETURNS STRING
LANGUAGE js AS r"""
  return s.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '');
""";

SELECT slugify('Hello, BigQuery World!') AS slug;

TEMP functions live for one query or script, while a function in a dataset is permanent and shareable. Prefer built-in functions and SQL UDFs when possible, because JavaScript UDFs run slower and have memory limits.

Python The same logic as a reusable function

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Stored procedures and scripting

A stored procedure bundles several statements with variables, control flow and transactions. BigQuery scripts use DECLARE, SET, IF, LOOP, FOR ... IN and BEGIN ... EXCEPTION:

SQL
CREATE OR REPLACE PROCEDURE shop_reports.refresh_day(run_day DATE)
BEGIN
  DECLARE rows_loaded INT64;

  DELETE FROM shop_reports.daily_revenue WHERE day = run_day;

  INSERT INTO shop_reports.daily_revenue
  SELECT run_day, country, SUM(total), COUNT(*)
  FROM shop.orders
  WHERE order_date = run_day
  GROUP BY country;

  SET rows_loaded = (SELECT COUNT(*) FROM shop_reports.daily_revenue WHERE day = run_day);
  IF rows_loaded = 0 THEN
    RAISE USING MESSAGE = FORMAT('No orders for %t', run_day);
  END IF;
END;

CALL shop_reports.refresh_day(DATE '2026-03-02');

A scheduled query can simply run CALL shop_reports.refresh_day(@run_date);, which keeps the logic in one version-controlled place. Procedures can also run a multi-statement transaction with BEGIN TRANSACTION; ... COMMIT TRANSACTION;, so a failure rolls back everything.

Table functions

A table function returns a table and takes parameters, like a parameterized view:

SQL
CREATE OR REPLACE TABLE FUNCTION shop.orders_on(day DATE)
AS (SELECT * FROM shop.orders WHERE order_date = day);

SELECT * FROM shop.orders_on(DATE '2026-03-02');

Because the partition filter is a parameter, the function prunes properly and users cannot forget it.

Beyond schedules

Automation options

Scheduled queries SQL on a timetable
Routines UDFs, procedures, table functions
Orchestrators Composer, Airflow, Workflows
Dataform SQL models with dependencies

Start simple; grow when jobs depend on each other.

1 / 2

When one job must wait for another, or you need retries and branching, move to an orchestrator or Dataform (see streaming, Dataform and connected tools).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why use @run_date instead of CURRENT_DATE() in a scheduled query?

    @run_date is the logical run date, so reruns and backfills are deterministic.
  2. 2. What makes a daily refresh idempotent?

    Rewriting exactly one day's data means a retry leaves the same result.
  3. 3. Which routine returns a table and takes parameters?

    A table function behaves like a parameterized view.
  4. 4. When might you choose a JavaScript UDF over a SQL UDF?

    JavaScript UDFs handle complex string logic but are slower, so use them only when needed.

Summary

  • Scheduled queries run SQL on a timetable with @run_date and @run_time parameters, ideally as a service account.
  • Design jobs to rebuild one day so retries and backfills are safe.
  • SQL and JavaScript UDFs, stored procedures and table functions package reusable logic.
  • A scheduled query can just CALL a procedure.
  • Orchestrators and Dataform take over when jobs have dependencies.

Next: The Python Client Library