Scheduled queries and transfers

Tuesday's session, on October 27, starts with a failure. At 04:10 Aisha's phone showed an alert: the weekly promotions summary had not refreshed. The cause took five minutes to find. The scheduled query that builds the table had been created two years ago by an analyst who has since left Birchline, and when her account was suspended, the query lost the credentials it ran with. Aisha uses the incident to walk Mateo through the simplest orchestration tools in Google Cloud, and the habits that keep them from breaking quietly.

Scheduled queries

A scheduled query is a BigQuery query that runs on a timetable. It is the simplest way to automate SQL: no extra service, no code to deploy, and no charge beyond the query itself. Behind the scenes, scheduled queries are run by the BigQuery Data Transfer Service, which is why they appear on the transfers page of the console and share its run history.

A scheduled query has a few settings that matter. The schedule says when it runs, written in a simple form such as 'every day 07:30' or 'every mon 09:00'. The destination says where results go: a table, with a write preference to overwrite it or append to it, or no destination at all for a query such as a MERGE or a CREATE TABLE statement that writes for itself. Notifications can send an email to the owner on failure or publish a message to a Pub/Sub topic for each run. And the credentials decide whose permissions the query runs with.

That last setting caused this morning's failure. A scheduled query created in the console runs as the person who created it unless a service account is chosen. When that person leaves, the query fails. Birchline's rule since this morning is that every scheduled query in a production project runs as a service account named for its pipeline, with only the roles that pipeline needs, and lists a team, not a person, as its contact.

# Create the nightly curated build as a scheduled query that runs as a service account
bq query --use_legacy_sql=false \
  --display_name="nightly curated sales" \
  --schedule="every day 07:30" \
  --service_account_name=sq-curated-sales@bl-data-prod.iam.gserviceaccount.com \
  "CALL curated.build_sales(DATE_SUB(@run_date, INTERVAL 1 DAY))"

Run-time parameters

Scheduled queries receive two parameters on every run. @run_time is the moment the run was scheduled for, as a TIMESTAMP in UTC, and @run_date is that moment's date. Using them, rather than CURRENT_DATE(), makes a scheduled query safe to rerun: a manual rerun of Tuesday's run still processes Monday's data. Destination table names can also use the run date, so a query can write to one partition per day by naming the destination with a partition decorator built from the run date.

The parameters are in UTC, and that matters in Canada and the United States. A query scheduled for 07:30 UTC runs at 03:30 in Columbus during daylight saving time, but at 02:30 after the clocks change. In 2026 that change happens on Sunday, November 1, in both countries. Mateo checks what this does to Birchline's schedule.

# Local start time of a fixed 07:30 UTC schedule, before and after the clocks change
schedule_utc = 7 * 60 + 30            # minutes after midnight UTC
offsets = {"October 31 (daylight time)": -4, "November 2 (standard time)": -5}
till_files_due = 2 * 60               # 02:00 local

for day, hours in offsets.items():
    local = schedule_utc + hours * 60
    h, m = divmod(local, 60)
    margin = local - till_files_due
    print(f"{day}: starts at {h:02d}:{m:02d} local, {margin} minutes after the till files are due")

October 31 (daylight time): starts at 03:30 local, 90 minutes after the till files are due
November 2 (standard time): starts at 02:30 local, 30 minutes after the till files are due

The margin drops from 90 minutes to 30. That is still positive, but a store whose file arrives late now has less time to catch up before the build. Aisha's preference is to remove the guess altogether, which the next lessons cover: start the build when the files have arrived, not when the clock says they probably have.

The BigQuery Data Transfer Service

The BigQuery Data Transfer Service (DTS) loads data into BigQuery on a schedule from a list of supported sources. They include Cloud Storage, other clouds' object storage such as Amazon S3 and Azure Blob Storage, Google's own advertising and marketing products such as Google Ads and Campaign Manager, YouTube channel reports, and migrations from warehouses such as Teradata and Amazon Redshift. Each transfer is a configuration with a source, a destination dataset, a schedule and credentials, and each run is recorded with its status and logs.

Birchline's marketing team has used a Google Ads transfer since GCP-220. It runs every morning, loads the previous day's campaign statistics, and needs no code. When a campaign account was added in September, Aisha used the transfer's backfill option to load the earlier months for the new account: a backfill runs the transfer for a range of past dates, one run per day. The same feature lets a team recover from a few days of failed runs without writing anything.

Storage Transfer Service and Cloud Scheduler

Two other services are often confused with DTS. Storage Transfer Service moves files, not table rows: from another cloud, from a URL list, between buckets, or from an on-premises file system through agents. GCP-220 used it for the 40 TB photo library. It can run once or on a schedule, and it can delete files from the source after copying, which is useful for landing areas.

Cloud Scheduler is a managed cron service. A job has a schedule in standard cron syntax, a time zone, and a target: an HTTP endpoint, a Pub/Sub topic or an App Engine app. It retries failed calls according to the job's retry settings. On its own it does no data work, but it is a common way to start other things on a timetable, such as a Workflows execution, a Cloud Run job or a Dataform workflow. Unlike scheduled queries, a Cloud Scheduler job lets you choose the time zone directly, so a job set to 03:30 America/New_York keeps running at 03:30 local time when the clocks change.

# Start a Workflows execution every day at 03:30 local time
gcloud scheduler jobs create http start-nightly-sales \
  --location=us-east5 \
  --schedule="30 3 * * *" \
  --time-zone="America/New_York" \
  --uri="https://workflowexecutions.googleapis.com/v1/projects/bl-data-prod/locations/us-east5/workflows/nightly-sales/executions" \
  --oauth-service-account-email=wf-nightly-sales@bl-data-prod.iam.gserviceaccount.com

Writing scheduled SQL that is safe to rerun

A scheduled query that appends rows doubles them when it is rerun. Aisha's standard pattern for a daily table has two parts. First, the table is partitioned by date. Second, each run replaces only its own day: either by writing to that day's partition with an overwrite preference, or by running a MERGE that matches on a key and updates or inserts. Both make the run idempotent. A third option, DELETE for the day followed by INSERT, also works if both statements run in one multi-statement transaction, so a failure between them does not leave the day empty.

-- Rebuild one day of curated.sales inside a transaction
DECLARE d DATE DEFAULT DATE_SUB(@run_date, INTERVAL 1 DAY);
BEGIN TRANSACTION;
DELETE FROM curated.sales WHERE sale_date = d;
INSERT INTO curated.sales
SELECT * FROM staging.sales_lines WHERE sale_date = d;
COMMIT TRANSACTION;

Watching scheduled work

Scheduled queries and transfers fail quietly unless someone is told. Every transfer configuration, including every scheduled query, keeps a run history that shows each run's scheduled time, status, duration and error message, and each run writes entries to Cloud Logging. Aisha's checklist for any new schedule has three items: turn on failure notifications to the team's address rather than a person's, publish run notifications to a Pub/Sub topic that the team's alerting reads, and record the schedule, its owner and its expected finish time on the pipeline's wiki page.

A failed run is not the only problem worth catching. A run can succeed while doing nothing useful: a transfer from Cloud Storage that finds no new files, or a scheduled query whose source table was empty because an upstream load was late. Both show a green tick. Aisha adds a small check after the important ones, a query that counts the rows loaded for the run date and raises an error with the ERROR() function if the count is zero or far below normal. Lesson 5 turns that idea into proper freshness and volume alerts.

Finally, schedules need housekeeping. Each quarter, the team lists every scheduled query and transfer in the production projects, with its owner, credentials and last successful run. Anything owned by a departed employee is moved to a service account, anything that has failed for weeks without anyone noticing is either fixed or deleted, and anything nobody can explain is paused for a month before removal. The promotions summary that failed this morning would have been caught by that review.