Fix GCP BigQuery Scheduled Query Errors

When working with GCP BigQuery, you may encounter a configuration error that prevents your data pipeline or messaging system from working. This guide explains the most common mistake with scheduled query and shows the exact fix.

A Common Mistake

Creating a scheduled query without specifying a destination table, causing the query to run but discard results.

The incorrect command:

bq query --use_legacy_sql=false 'SELECT DATE(created_at) as day, COUNT(*) FROM events GROUP BY day'
# Scheduled daily but no destination

Error output:

Query runs daily but results are discarded.
Scheduled queries without a destination table produce no output. The user expects a daily report but nothing is stored. The query cost is incurred for no benefit.

The Correct Approach

The right way to configure scheduled query in GCP BigQuery:

bq mk --transfer_config --target_dataset=my_dataset --display_name='Daily events count' --schedule='every 24 hours' --params='{"query":"SELECT DATE(created_at) as day, COUNT(*) as cnt FROM events GROUP BY day","destination_table_name_template":"daily_events","write_disposition":"WRITE_TRUNCATE"}'

Successful result:

Transfer config created.
Query runs daily and writes to my_dataset.daily_events.
SELECT * FROM my_dataset.daily_events ORDER BY day DESC
Returns yesterday's data. Results persist in the destination table.

How to Prevent This

Always set a destination table for scheduled queries. Use WRITE_TRUNCATE for complete refreshes, WRITE_APPEND for incremental. Use partitioning on the destination table. Set notification channels for failures. Scheduled queries count as a transfer config in BigQuery. Use the transfer_config API or bq mk --transfer_config.

FAQ

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro. Secure your cloud with DodaTech.