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.pyfrom 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!

Python everything!

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store