Control Flow

Control flow statements let you conditionally include SQL and generate repetitive patterns. They use the {* *} syntax.

If Statements

Basic If

SELECT
    id,
    name,
    {* if target.name == 'prod' *}
    email,
    phone,
    {* endif *}
    created_at
FROM users

In dev: SELECT id, name, created_at FROM usersIn prod: SELECT id, name, email, phone, created_at FROM users

If-Else

SELECT *
FROM 
    {* if target.name == 'prod' *}
    production.customers
    {* else *}
    dev.sample_customers
    {* endif *}

If-Elif-Else

SELECT *
FROM events
WHERE 1=1
    {* if env.get('PERIOD') == 'day' *}
    AND date = CURRENT_DATE
    {* elif env.get('PERIOD') == 'week' *}
    AND date >= CURRENT_DATE - INTERVAL '7 days'
    {* elif env.get('PERIOD') == 'month' *}
    AND date >= CURRENT_DATE - INTERVAL '30 days'
    {* else *}
    AND date >= '2024-01-01'
    {* endif *}

Nested Conditions

{* if target.name == 'prod' *}
    {* if config.include_pii *}
    SELECT id, name, email, phone
    {* else *}
    SELECT id, name
    {* endif *}
{* else *}
SELECT id, name, 'redacted' as email
{* endif *}
FROM users

For Loops

Basic Loop

SELECT
    date,
    {* for metric in ['revenue', 'orders', 'users'] *}
    SUM({{ metric }}) as total_{{ metric }},
    {* endfor *}
FROM daily_metrics
GROUP BY date

Output:

SELECT
    date,
    SUM(revenue) as total_revenue,
    SUM(orders) as total_orders,
    SUM(users) as total_users,
FROM daily_metrics
GROUP BY date

Loop with Index

SELECT
    {* for i, col in enumerate(['id', 'name', 'email']) *}
    {{ col }}{{ '' if i == 2 else ',' }}
    {* endfor *}
FROM users

Output:

SELECT
    id,
    name,
    email
FROM users

Loop over Dictionary

{* for status_code, status_name in [('P', 'Pending'), ('A', 'Active'), ('C', 'Closed')] *}
WHEN status = '{{ status_code }}' THEN '{{ status_name }}'
{* endfor *}

Nested Loops

{* for table in ['orders', 'customers', 'products'] *}
    {* for agg in ['COUNT', 'SUM', 'AVG'] *}
-- {{ agg }} for {{ table }}
    {* endfor *}
{* endfor *}

Loop Helpers

Handling Trailing Commas

A common challenge is avoiding trailing commas. Several approaches:

Approach 1: Check index

SELECT
    {* for i, col in enumerate(columns) *}
    {{ col }}{{ '' if i == len(columns) - 1 else ',' }}
    {* endfor *}
FROM table

Approach 2: Join after building list

SELECT
    {{ ', '.join(columns) }}
FROM table

Approach 3: Leading commas

SELECT
    id
    {* for col in extra_columns *}
    , {{ col }}
    {* endfor *}
FROM table

Filtering in Loops

{* for col in columns if col != 'internal_id' *}
{{ col }},
{* endfor *}

Loop with Else

The else block runs if the loop never executes:

{* for col in columns *}
{{ col }},
{* else *}
*  -- No columns specified, select all
{* endfor *}

Combining Control Flow

If Inside For

SELECT
    {* for col in columns *}
        {* if col.type == 'pii' and target.name != 'prod' *}
    'REDACTED' as {{ col.name }},
        {* else *}
    {{ col.name }},
        {* endif *}
    {* endfor *}
FROM users

For Inside If

{* if include_metrics *}
SELECT
    date,
    {* for metric in metrics *}
    SUM({{ metric }}) as {{ metric }},
    {* endfor *}
FROM events
GROUP BY date
{* else *}
SELECT date FROM events GROUP BY date
{* endif *}

Whitespace Control

Control flow statements produce whitespace. Manage it with careful formatting:

Problem: Extra Blank Lines

SELECT
    {* for col in ['a', 'b'] *}
    {{ col }},
    {* endfor *}
FROM t

Produces:

SELECT
    
    a,
    
    b,
    
FROM t

Solution: Compact Formatting

SELECT
    {* for col in ['a', 'b'] *}{{ col }},{* endfor *}
FROM t

Or use a macro for complex cases.

Practical Patterns

Dynamic Column Selection

/*---
name: user_report
---*/

{%- set columns = ['id', 'name'] -%}
{%- if target.name == 'prod' -%}
    {%- set columns = columns + ['email', 'phone'] -%}
{%- endif -%}

SELECT {{ ', '.join(columns) }}
FROM users

UNION ALL Generation

{* for year in [2022, 2023, 2024] *}
SELECT *, {{ year }} as year FROM events_{{ year }}
{* if year != 2024 *}
UNION ALL
{* endif *}
{* endfor *}

Pivot Table

SELECT
    customer_id,
    {* for status in ['pending', 'shipped', 'delivered'] *}
    SUM(CASE WHEN status = '{{ status }}' THEN 1 ELSE 0 END) as {{ status }}_count,
    {* endfor *}
FROM orders
GROUP BY customer_id

Dynamic WHERE Clauses

SELECT *
FROM events
WHERE 1=1
{* if env.get('USER_ID') *}
    AND user_id = {{ env.USER_ID }}
{* endif *}
{* if env.get('START_DATE') *}
    AND date >= '{{ env.START_DATE }}'
{* endif *}
{* if env.get('END_DATE') *}
    AND date <= '{{ env.END_DATE }}'
{* endif *}

Multi-Table Join

SELECT
    base.*
    {* for t in join_tables *}
    , {{ t.alias }}.*
    {* endfor *}
FROM base_table base
{* for t in join_tables *}
LEFT JOIN {{ t.name }} {{ t.alias }} ON base.id = {{ t.alias }}.base_id
{* endfor *}

Best Practices

1. Prefer Macros for Complex Logic

-- Instead of complex inline control flow:
{* if this *}
    {* for that *}
        {* if other *}
        ...
        {* endif *}
    {* endfor *}
{* endif *}

-- Use a macro:
{{ utils.build_select(columns, conditions) }}

2. Keep Conditionals Readable

-- Good: Clear intent
{* if target.name == 'prod' *}

-- Avoid: Complex boolean expressions
{* if (target.name == 'prod' or env.get('FORCE_PROD')) and not config.debug *}

3. Document Complex Templates

/*---
name: dynamic_report
meta:
  template_notes: |
    This template generates different columns based on target:
    - dev: basic columns only
    - prod: includes PII columns
---*/

4. Test Template Output

Use the rendered SQL in logs to verify templates work as expected.

Next Steps

• Expressions - Expression syntax
• Global Variables - Available globals
• Macros - Extract complex logic