Cheatsheet

---

Why this is a high-leverage skill

Once you learn Jinja, you unlock capabilities across the entire stack of a Data Engineer:

1. dbt: You can write macros (functions for SQL).

2. Airflow/Kestra: You can build dynamic pipelines that don't need manual updates.

3. Ansible: If you ever touch infrastructure-as-code, Ansible also uses Jinja.

4. Superset/Preset: Even BI tools use Jinja to inject dynamic date filters into SQL queries.

---

Cheatsheet

This focuses on the patterns you will actually use in your day-to-day work with tools like dbt (SQL generation) and Kestra (Workflow orchestration).

I. The 30-Second Syntax Refresher

Syntax	Name	What it does	Example
{{ ... }}	Variable	Prints a value.	SELECT * FROM {{ table_name }}
{% ... %}	Block	Logic (Loops, If/Else).	{% if is_incremental() %} ... {% endif %}
{# ... #}	Comment	Comments (won't render).	{# TODO: Fix this join #}
|	Pipe	Applies a filter.	{{ my_string | upper }}

II. Top 5 Jinja Patterns for Data Engineers

1. The "For Loop" Pivot (dbt)

The Problem: You have a table of transactions and you want to pivot the rows into columns (e.g., revenue by year), but you don't want to write 20 CASE WHEN statements manually.

The Jinja Solution:

{% set years = [2020, 2021, 2022, 2023, 2024] %}

SELECT
  customer_id,
  {% for year in years %}
  -- This loop generates one line of SQL for every year in the list
  SUM(CASE WHEN order_year = {{ year }} THEN amount ELSE 0 END) as total_{{ year }}
  {% if not loop.last %},{% endif %} -- Adds a comma unless it's the last item
  {% endfor %}
FROM {{ ref('orders') }}
GROUP BY 1

2. Dynamic Dates & Filenames (Kestra)

The Problem: You are saving a file to S3/GCS. If you hardcode the filename, the next run will overwrite it. You need unique, time-based names.

The Jinja Solution: Kestra provides execution context variables (like execution.startDate) that you can format using Java date patterns.

id: save_to_s3
type: io.kestra.plugin.aws.s3.Upload
from: "{{ outputs.extract_data.uri }}"
# Dynamically names the file: sales_2023-10-27.csv
key: "raw/sales/sales_{{ execution.startDate | date('yyyy-MM-dd') }}.csv"
bucket: "my-data-lake"

3. Environment Logic (dbt)

The Problem: You want to test your code on a small subset of data in "Development" to save money/time, but run on the full dataset in "Production."

The Jinja Solution:

SELECT *
FROM {{ ref('heavy_event_logs') }}

-- If I am running this on my laptop (dev), only look at the last 3 days
{% if target.name == 'dev' %}
WHERE created_at > current_date - 3
{% endif %}

4. Handling Missing Inputs (General)

The Problem: A variable might be missing or null, which causes your pipeline to crash.

The Jinja Solution: Use the | default() filter to set a fallback value.

# If 'batch_size' isn't provided in the inputs, default to 1000
batch_size: "{{ inputs.batch_size | default(1000, true) }}"

5. Passing Data Between Tasks (Kestra)

The Problem: Task A downloads a file; Task B needs to read that specific file.

The Jinja Solution: You reference the outputs of previous tasks.

- id: download_file
  type: io.kestra.plugin.fs.http.Download
  uri: "https://api.example.com/data.csv"

- id: load_to_bigquery
  type: io.kestra.plugin.gcp.bigquery.Load
  # Jinja grabs the location of the file downloaded in the previous step
  from: "{{ outputs.download_file.uri }}"
  destinationTable: "my_project.dataset.table"

---

III. Pro-Tip: Whitespace Control

You will often see a minus sign inside the brackets: {%- or -%}.

• Without -: Jinja leaves a blank line in your compiled code where the logic was.

• With -: Jinja strips the whitespace.

Example:

{# Standard #}
{% if true %}
  SELECT 1
{% endif %}
-- Output:
-- (blank line)
--   SELECT 1
-- (blank line)

{# With Whitespace Control #}
{%- if true -%}
SELECT 1
{%- endif -%}
-- Output:
-- SELECT 1

---