Skip to content

BigQuery Sensor

The BigQuerySqlSensor executes SQL queries against BigQuery and succeeds when results are truthy.

Operator Usage

from airsql.sensors import BigQuerySqlSensor

wait_for_data = BigQuerySqlSensor(
    task_id='wait_for_user_data',
    sql="SELECT COUNT(*) FROM analytics.user_events WHERE dt = '{{ ds }}'",
    conn_id='bigquery_default',
    location='us-central1',
)

Parameters

Parameter Type Required Description
task_id str Yes Unique task identifier
sql str Yes SQL query to check condition
conn_id str Yes BigQuery connection ID
location str No BigQuery location (default: 'us-central1')
retries int No Number of retries before failing (default: 1)
poke_interval int No Seconds between checks (default: 60)
timeout int No Maximum seconds to wait

Examples

Wait for Row Count

wait_for_rows = BigQuerySqlSensor(
    task_id='wait_for_min_rows',
    sql="""
        SELECT COUNT(*) >= 1000
        FROM `project.dataset.daily_data`
        WHERE date = '{{ ds }}'
    """,
    conn_id='bigquery_default',
    location='US',
)

Wait for ETL Completion

wait_for_etl = BigQuerySqlSensor(
    task_id='wait_for_etl_completion',
    sql="""
        SELECT COUNT(*) > 0
        FROM etl_status
        WHERE job_name = 'daily_import'
          AND status = 'complete'
          AND date = '{{ ds }}'
    """,
    conn_id='bigquery_default',
    retries=5,
    poke_interval=300,
)

Partition Check

wait_for_partition = BigQuerySqlSensor(
    task_id='wait_for_partition',
    sql="""
        SELECT COUNT(*) > 0
        FROM `project.dataset.events`
        WHERE DATE(event_time) = '{{ ds }}'
    """,
    conn_id='bigquery_default',
)

Data Quality Validation

validate_data = BigQuerySqlSensor(
    task_id='validate_data_quality',
    sql="""
        SELECT
            COUNT(*) > 0
            AND COUNT(CASE WHEN user_id IS NULL THEN 1 END) = 0
        FROM `project.analytics.users`
        WHERE date = '{{ ds }}'
    """,
    conn_id='bigquery_default',
)

Complex Validation Query

validate_completeness = BigQuerySqlSensor(
    task_id='validate_completeness',
    sql="""
        SELECT
            (SELECT COUNT(*) FROM source_a WHERE date = '{{ ds }}')
            =
            (SELECT COUNT(*) FROM source_b WHERE date = '{{ ds }}')
    """,
    conn_id='bigquery_default',
    location='us-central1',
)

With Retries and Timeout

from datetime import timedelta

sensor = BigQuerySqlSensor(
    task_id='robust_wait',
    sql="SELECT processed = true FROM etl_status WHERE date = '{{ ds }}'",
    conn_id='bigquery_default',
    location='US',
    retries=5,
    poke_interval=180,
    timeout=5400,
    retry_delay=timedelta(minutes=3),
)

Integration in DAG

from airflow.decorators import dag
from airsql.sensors import BigQuerySqlSensor
from airsql.transfers import BigQueryToPostgresOperator


@dag(schedule='@daily')
def bq_to_pg_pipeline():

    wait_for_source = BigQuerySqlSensor(
        task_id='wait_for_source',
        sql="""
            SELECT COUNT(*) > 0
            FROM `project.staging.data_{{ ds_nodash }}`
        """,
        conn_id='bigquery_default',
        location='US',
    )

    transfer = BigQueryToPostgresOperator(
        task_id='transfer_to_pg',
        source_project_dataset_table='project.staging.data_{{ ds_nodash }}',
        destination_table='warehouse.daily_data',
        postgres_conn_id='postgres_default',
        gcs_bucket='temp-bucket',
    )

    wait_for_source >> transfer


bq_to_pg_pipeline()

Multiple Location Support

# US multi-region
sensor_us = BigQuerySqlSensor(
    task_id='check_us',
    sql='SELECT COUNT(*) > 0 FROM `project.us_dataset.table`',
    conn_id='bigquery_default',
    location='US',
)

# EU region
sensor_eu = BigQuerySqlSensor(
    task_id='check_eu',
    sql='SELECT COUNT(*) > 0 FROM `project.eu_dataset.table`',
    conn_id='bigquery_default',
    location='EU',
)

# Specific region
sensor_regional = BigQuerySqlSensor(
    task_id='check_regional',
    sql='SELECT COUNT(*) > 0 FROM `project.dataset.table`',
    conn_id='bigquery_default',
    location='us-central1',
)