Pass parameters to SQL query when using PostgresOperator in Airflow

This article is a part of my "100 data engineering tutorials in 100 days" challenge. (45/100)

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:

    params={"some_value":"'the value I want'"},

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 }} 

Subscribe to the newsletter and join the free email course.

Remember to share on social media!
If you like this text, please share it on Facebook/Twitter/LinkedIn/Reddit or other social media.

If you want to contact me, send me a message on LinkedIn or Twitter.

Would you like to have a call and talk? Please schedule a meeting using this link.

Bartosz Mikulski
Bartosz Mikulski * MLOps Engineer / data engineer * conference speaker * co-founder of Software Craft Poznan & Poznan Scala User Group

Subscribe to the newsletter and get access to my free email course on building trustworthy data pipelines.