Skip to content

PostgreSQL Sensor

The PostgresSqlSensor executes SQL queries against PostgreSQL and succeeds when results are truthy.

Operator Usage

from airsql.sensors import PostgresSqlSensor

wait_for_data = PostgresSqlSensor(
    task_id='wait_for_processing',
    sql="SELECT COUNT(*) FROM processing_queue WHERE status = 'complete'",
    conn_id='postgres_default',
    retries=2,
)

Parameters

Parameter Type Required Description
task_id str Yes Unique task identifier
sql str Yes SQL query to check condition
conn_id str Yes PostgreSQL connection ID
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 = PostgresSqlSensor(
    task_id='wait_for_min_rows',
    sql="""
        SELECT COUNT(*) >= 1000
        FROM daily_data
        WHERE date = '{{ ds }}'
    """,
    conn_id='postgres_default',
)

Wait for Parent Task

wait_for_parent = PostgresSqlSensor(
    task_id='wait_for_upstream',
    sql="""
        SELECT EXISTS (
            SELECT 1 FROM job_status
            WHERE job_name = 'parent_job'
              AND status = 'success'
              AND run_date = '{{ ds }}'
        )
    """,
    conn_id='postgres_default',
    poke_interval=30,
)

Wait for Processing Completion

wait_for_queue = PostgresSqlSensor(
    task_id='wait_for_queue_empty',
    sql='SELECT COUNT(*) = 0 FROM processing_queue',
    conn_id='postgres_default',
    retries=10,
    poke_interval=60,
)

Data Quality Check

check_no_nulls = PostgresSqlSensor(
    task_id='check_no_nulls',
    sql="""
        SELECT COUNT(*) = 0
        FROM staging_data
        WHERE id IS NULL OR created_at IS NULL
    """,
    conn_id='postgres_default',
)

Complex Validation

validate_data = PostgresSqlSensor(
    task_id='validate_data',
    sql="""
        SELECT
            COUNT(*) > 0
            AND COUNT(CASE WHEN user_id IS NULL THEN 1 END) = 0
            AND COUNT(CASE WHEN email IS NULL THEN 1 END) = 0
        FROM users
        WHERE created_at >= '{{ ds }}'
    """,
    conn_id='postgres_default',
    timeout=3600,
)

With Retries and Timeout

from datetime import timedelta

sensor = PostgresSqlSensor(
    task_id='robust_wait',
    sql="SELECT processed = true FROM etl_status WHERE date = '{{ ds }}'",
    conn_id='postgres_default',
    retries=5,
    poke_interval=120,
    timeout=7200,
    retry_delay=timedelta(minutes=2),
)

Integration in DAG

from airflow.decorators import dag
from airsql.sensors import PostgresSqlSensor
from airsql import sql, Table


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

    wait_for_source = PostgresSqlSensor(
        task_id='wait_for_source',
        sql="SELECT COUNT(*) > 0 FROM raw_data WHERE date = '{{ ds }}'",
        conn_id='postgres_default',
    )

    @sql.query(output_table=Table('postgres_conn', 'warehouse.processed'))
    def process():
        return "SELECT * FROM raw_data WHERE date = '{{ ds }}'"

    wait_for_source >> process()


pipeline()