PATTERN Cited by 1 source

Jinja-templated SQL generation

Pattern

Use Jinja templates to parameterize SQL metric definitions so that a single core definition generates correct queries across multiple time granularities, dimension combinations, and date ranges — without duplicating SQL blocks.

Mechanism

A Jinja SQL template declares the metric's core logic with template variables for the parts that vary per invocation:

SELECT DATE_TRUNC('{{time_granularity}}', {{time_attribute}}) AS {{time_granularity}},
       {{select_dimensions(dimensions)}},
       {{select_metrics(metrics, include_ratio_parts)}}
  FROM rides_table
 WHERE {{time_attribute}} BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
 GROUP BY ...

The Python access layer resolves the template with caller-specified parameters (time_granularity='week', dimension_names=['region'], etc.) and emits executable SQL.

(Source: sources/2026-06-10-lyft-metric-semantic-layer)

Why Jinja specifically

Lyft chose Jinja over other templating frameworks because: - Lower learning curve — Python-native, widely known - DRY enforcement — macros and includes prevent redundant SQL across definitions - Familiar to data teams — already used in dbt, Airflow templates, etc.

Tradeoffs

Pro	Con
Single definition covers all granularities	Template complexity can grow for metrics with many conditional branches
Python-native evaluation	Not database-native — requires a generation step before execution
Readable by data analysts	Debugging template rendering errors requires understanding Jinja evaluation order

Seen in

• sources/2026-06-10-lyft-metric-semantic-layer — Lyft's MSL uses Jinja templates as the SQL generation layer