GitHub user meel-ayush edited a discussion: Pre-flight SQL query syntax 
validation and partition guard in Common SQL provider

Check PR: #76828 for implementation.

Hey everyone,

Wanted to share an idea and see what people think before putting together any 
code.

Right now in `providers/common/sql`, `SQLExecuteQueryOperator` sends whatever 
SQL string it receives straight to the database hook. If there's a syntax typo 
or a query accidentally misses a partition filter on a large table, the task 
only fails after opening the connection and waking up the warehouse (which on 
Snowflake/BigQuery takes time and costs money).

While static SQL files are usually caught in CI using linters like `sqlfluff`, 
dynamic queries (where filters or table names are injected via Jinja/XComs) 
only get rendered at task runtime and slip through.

I was looking at `sqlglot` recently (pure Python, MIT license, zero C 
dependencies). Since Jinja templates are already rendered by the time 
`execute()` runs, we could optionally run a fast client-side check (< 2ms) 
before creating the DB connection:
1. Catch syntax errors for the target dialect locally so tasks fail fast.
2. Optionally check if expected partition columns (e.g. `ds`) are present in 
the `WHERE` clause to prevent accidental full-table scans.

To avoid bloating `common.sql`, this could be an optional extra (like 
`apache-airflow-providers-common-sql[validator]`). If `sqlglot` isn't installed 
or the check isn't enabled on the operator, behavior stays 100% unchanged.

A couple of open questions:
1. Does the community feel this belongs in `common.sql`, or is this something 
users should just handle in custom wrapper operators?
2. Has anyone experimented with `sqlglot` across Airflow's supported dialects 
(Snowflake, BigQuery, Postgres, etc.)?

Looking forward to hear thoughts from maintainers and anyone running heavy 
warehouse workloads.

GitHub link: https://github.com/apache/airflow/discussions/76373

----
This is an automatically sent email for [email protected].
To unsubscribe, please send an email to: [email protected]

Reply via email to