scheduled-queries

BigQuery Scheduled Queries

Parent Skill: /bigquery
Path: /bigquery/scheduled-queries

Purpose

Automate recurring queries, ETL pipelines, and data transformations with scheduled execution.

When to Use

Trigger when:

• Keywords: schedule, automate, daily, hourly, ETL, pipeline, cron
• User wants to: run query daily, automate reports, incremental loads

Chat commands:

/bigquery/scheduled-queries create daily aggregation at 2am
/bigquery/scheduled-queries setup hourly event summary
/bigquery/scheduled-queries automate weekly report

Requirements

---

Basic Scheduled Query

Using BigQuery Console

1. Write your query
2. Click "Schedule" button
3. Configure:
   • Name: daily_user_stats
   • Schedule: every day 02:00
   • Destination table: dataset.daily_stats
   • Write preference: WRITE_TRUNCATE or WRITE_APPEND

Using bq CLI

bq mk \
  --transfer_config \
  --data_source=scheduled_query \
  --target_dataset=my_dataset \
  --display_name='Daily User Stats' \
  --schedule='every day 02:00' \
  --params='{
    "query":"SELECT DATE(timestamp) as date, COUNT(*) as events FROM `project.dataset.events` WHERE DATE(timestamp) = CURRENT_DATE() - 1 GROUP BY date",
    "destination_table_name_template":"daily_stats",
    "write_disposition":"WRITE_APPEND"
  }'

---

Common Patterns

Pattern 1: Daily Aggregation

-- Scheduled: every day 02:00 UTC
-- Destination: dataset.daily_summary
-- Write: WRITE_APPEND

SELECT
  CURRENT_DATE() - 1 as date,
  COUNT(DISTINCT user_id) as active_users,
  COUNT(*) as total_events,
  COUNTIF(event_name = 'purchase') as purchases,
  SUM(IF(event_name = 'purchase', 
    CAST(JSON_EXTRACT_SCALAR(properties, '$.amount') AS FLOAT64), 
    0)) as revenue
FROM `project.dataset.events`
WHERE DATE(timestamp) = CURRENT_DATE() - 1
GROUP BY date;