Templated SQL with Jinja

This post is part of a series of bite-sized tutorials that I have been publishing on my blog.

Problem Statement

In data warehousing, we often encounter repetitive processes that can benefit from templating. This is a simple example of creating a COPY INTO statement using some JSON.

{# jinja_template.j2 #}
COPY INTO {{ table.target_name }}
SELECT
{% for col in table.columns %}
{{ col.name }} as {{ col.alias }} {% if not loop.last %},{% endif %}
{% endfor %}
FROM {{ table.s3_stage }}

// config.json
{
    "table": {
        "target_name": "transactions.app_payments",
        "columns": [
            {
               "name": "date",
               "alias": "payment_date"
            },
            {
               "name": "price",
               "alias": "app_price"
            },
            {
               "name": "ts",
               "alias": "purchase_ts"
             }
        ],
        "s3_stage": "s3://import_bucket/purchase_date/"
    }
}

And now for our python driver program:

# main.py
from jinja2 import Template
import json
json_config = json.loads(open("config.json").read())
template = Template(open("jinja_template.j2").read())
rendered_sql = template.render(json_config)
print(rendered_sql)

Here’s an interactive repl to play with:

Try this out yourself on repl.it!