How to check files in Snowflake stage using Airflow

Question

I have some files in S3. How can I implement an Airflow sensor that checks whether a file is present in Snowflake stage, and trigger a task if it is? I understand that there's S3KeySensor, however I don't have access to the S3 bucket.

Answer 1

You can use an SqlSensor in combination with the LIST statement:

wait_for_file = SqlSensor(
    task_id="wait_for_file",
    conn_id="snowflake_conn",
    sql="LIST @mystage/path1;",
    fail_on_empty=False,
    poke_interval=60,
    mode="reschedule",
    timeout=86400,  # 24h
)