PostgresOperator allows us to use a SQL file as the query. However, when we do that, the standard way of passing template parameters no longer works.
For example, if I have the following SQL query:
SELECT column_a, column_b FROM table_name WHERE column_a = {{ some_value }}
Airflow will not automatically pass the some_value variable as the parameter.
In this situation, I have to use the parameters attribute of the PostgresOperator:
PostgresOperator(
task_id='postgres_task',
postgres_conn_id='postgress_sophi',
params={"some_value":"'the value I want'"},
sql='query.sql',
dag=dag)
Note that, I had to wrap the value into single quotes! Because in Postgres double quotes are used to reference a columns, tables, or functions. String literals, on the other hand, are inside single quotes.
In the query.sql file, I have to change the template variable to params.some.value:
SELECT column_a, column_b FROM table_name WHERE column_a = {{ params.some_value }}
Want to build AI systems that actually work?
Download my expert-crafted GenAI Transformation Guide for Data Teams and discover how to properly measure AI performance, set up guardrails, and continuously improve your AI solutions like the pros.