Build BigQuery scheduled query configurations with parameterized queries and notification settings.
Build BigQuery scheduled query configurations with parameterized queries, schedules, destination tables, and notification settings.
Required Fields
displayNamedataSourceIddestinationDatasetIdparams.queryscheduleOutput will appear here...The builder requires displayName, dataSourceId, destinationDatasetId, params.query, and schedule to resolve before accepting the config, the fields BigQuery Data Transfer Service needs to know what SQL to run, where results land, and how often, write disposition and notification settings are validated as well-formed but their correctness for your specific idempotency and alerting needs is on you.
Build a BigQuery scheduled query configuration: a parameterized SQL query, a destination table naming template, write disposition, and a schedule expressed either as a human-readable phrase ("every day 06:00") or cron syntax. The @run_date parameter is substituted automatically per run, letting one query definition process each day's partition without hardcoding a date, and write_disposition WRITE_TRUNCATE versus WRITE_APPEND is a real behavioral fork: truncate replaces the destination table's contents on every run (safe for idempotent daily rebuilds) while append accumulates rows (correct for incremental loads, dangerous if a run is accidentally replayed).
A finance team's daily revenue rollup query stops updating for six days before anyone notices the dashboard looks stale, and investigating shows the scheduled query has been failing silently every run because the service account it executes as had its key rotated as part of an unrelated security cleanup, with nobody realizing enableFailureEmail had never been turned on. They use the builder to rebuild the schedule with a fresh service account reference, explicitly enable emailPreferences.enableFailureEmail this time, and backfill the six missing days by manually triggering runs with WRITE_TRUNCATE so each day's re-run cleanly replaces rather than duplicates that day's figures.
A scheduled query silently failing due to a revoked or deleted service account produces no obvious signal unless failure email or Pub/Sub notification is explicitly enabled, always enable at least one notification channel, the default is easy to leave off during initial setup and forget.
WRITE_APPEND without a corresponding dedup step downstream is a latent double-counting bug waiting for the first accidental manual re-run or backfill, default to WRITE_TRUNCATE against a partitioned destination unless you have a specific incremental-load reason not to.
encryptionConfiguration's kmsKeyName location must match the destination dataset's location, a scheduled query targeting a US multi-region dataset with a key from a specific region like us-central1 fails at schedule-creation time, not silently at run time.
You get duplicate rows for that period, since append mode has no built-in idempotency, it just adds whatever the query returns to whatever's already in the destination table. For any query that might reasonably be re-run or backfilled, WRITE_TRUNCATE against a date-partitioned destination table is the safer default; use append only when you're certain re-runs won't happen or you've built deduplication downstream.
The serviceAccountName specified in the config, not the user who created or owns the schedule. If that service account's key is revoked, the account is deleted, or its permissions on the source tables change, the scheduled query starts failing silently unless enableFailureEmail (or a Pub/Sub notification) is configured to actually surface the failure.
It reflects the logical date of each individual run, not the creation date, that's what makes one query definition usable indefinitely for a recurring daily job. For a schedule set to "every day 06:00", @run_date advances by one day on each successive execution, letting the same WHERE DATE(order_timestamp) = @run_date clause correctly scope each day's run to that day's data.
Was this tool helpful?
Disclaimer: This tool runs entirely in your browser. No data is sent to our servers. Always verify outputs before using them in production. AWS, Azure, and GCP are trademarks of their respective owners.