# Templates

> Jinja in SQL, paths and options: target, profile(), columns(), dates and timezones.

SQL files, output paths, destination options and template values all render through the same Jinja environment ([minijinja](https://github.com/mitsuhiko/minijinja)). Beyond your own macros in `macros/` and packages, every template sees:

Name

What it is

`var('name', default)`

A variable: `--var`, then the schedule, Set, report, folders, project.

`env_var('NAME', default)`

An environment variable.

`run.*`

The run: `report`, `set`, `target`, `profile`, `source_type`, `schedule`, `date`, `now`, `timezone`.

`target.*`

The Binding’s source connection: its target’s fields (below).

`profile('name').*`

Any profile’s active target (below).

`run_query(sql)`

Rows from the report’s own connection.

`columns(rel)`

A relation’s columns (below).

`ref('file')` / `lookup('name')`

Another `.sql` file as a subquery; a lookup’s rows.

`date()`, `datetime()`, `period()`, `month_of()`, …

Calendar values (below).

`dispatch('macro', 'package')`

A package macro’s per-database variant.

`raise_error('message')`

Stop rendering with this error, e.g. from a macro that checks its arguments.

## Names from profiles: `target` and `profile()`

Build names from the connection instead of hard-coding them:

```sql
select * from {{ target.catalog }}.{{ target.schema }}.orders   -- client_a_catalog.sales_dev.orders
```

-   `target.name` is the active target (`dev`, `prod`, or `--target`), `target.type` the plugin type, `target.profile` the profile’s name, and every other field of the target is there by its own name. Fields are read after `env_var()` has been applied.
-   `profile('reports_s3').bucket` reads another profile’s active target the same way. It looks in `sources:` and `destinations:`; when both have the name, say which: `profile('shared', role='destination')`.
-   **Secrets can’t be read.** A field whose value comes from a `DRE_SECRET_*` variable, or that the plugin’s `describe` marks secret, is an error to read, so a password never reaches compiled SQL, logs or a file path. When the plugin isn’t installed to ask, fields named like `password`, `secret`, `token`, `key` or `credential` count as secret.

Keep the naming rule in one place with your own macro:

```sql
{# macros/names.sql #}{% macro crm(t) %}{{ var('client') }}_catalog.crm_{{ target.name }}.{{ t }}{% endmacro %}
select * from {{ crm('orders') }}
```

## Columns: `columns(rel)`

`columns('orders')` returns the relation’s columns in `select *` order, each with `name` and `type` (the Arrow type, e.g. `Int64`, `Utf8`, `Date32`):

```sql
select {% for c in columns('orders') if c.name != '_etl_ts' %}{{ c.name }}{% if not loop.last %}, {% endif %}{% endfor %}from orders
```

`rel` is anything that can follow `from`: a table, a fully qualified name, a temp table made by an earlier query, or `ref('file')`. DRE asks the database with `select * from <rel> as _dre_cols where 1=0`, once per relation in each file it renders. Like `run_query()`, it connects only when a template calls it, so `dre compile` connects for reports that use it. Packages build on it: `dre_utils.star()` is one.

`dre run` renders each query just before running it, so a template sees what the queries before it made. `dre compile`, `--dry-run` and `dre validate` run nothing, so there a temp table made by an earlier query doesn’t exist yet: `columns()` or `run_query()` on it fails there, and works in `dre run`. `dre validate` reports such a report as a warning (it can only be checked by `dre run`) and still checks everything else.

## Conditions, loops and Python-style methods

`{% if %}` / `{% elif %}` / `{% else %}` and `{% for %}` work in SQL, and inside the string values of YAML (a `path:`, a subject). Loops have `loop.index`, `loop.first`, `loop.last`, `loop.length`, `for ... else`, `range()` and `{% for x in xs if cond %}`. The YAML structure itself (keys, list items) is plain YAML: Jinja only renders values.

Common Python string and mapping methods work as they do in Jinja2: `'a,b'.split(',')`, `s.startswith('a')`, `s.strip()`, `s.replace(a, b)`, `d.get('k', default)`, `d.keys()`, `d.values()`, `d.items()`.

### Lists and dicts you can change: `list()` and `dict()`

A `[]` or `{}` literal, and a `var()` value, can’t change once made. To build one up in a loop, start from `list()` or `dict()` (optionally from existing values: `list(var('regions'))`):

```sql
{% set cols = list() %}{% for c in columns('orders') if c.name != '_etl_ts' %}  {% set _ = cols.append(c.name) %}{% endfor %}select {{ cols | join(', ') }} from orders
```

-   Lists: `append`, `extend`, `insert`, `pop`, `remove`, `clear`, `sort(reverse=true)`, `reverse`, `index`, `count`, `copy`. Dicts: `update`, `get`, `setdefault`, `pop`, `clear`, `keys`, `values`, `items`, `copy`.
-   They behave like Python’s: `{% set b = a %}` is the same list (use `a.copy()` for another); a dict keeps the order keys were added in; `l[-1]` is the last item.
-   Write `{% set _ = items.append(x) %}`: the method returns nothing, and there is no `{% do %}`. Calling a changing method on a `[]` literal or a `var()` value is an error that says so.
-   They last for one render only; nothing carries over between queries, Sets or runs.
-   For a counter or a flag, `namespace()` also works: `{% set ns = namespace(n=0) %}` then `{% set ns.n = ns.n + 1 %}`.

## String literals and Databricks

Databricks SQL doesn’t read `''` inside a string literal as an escaped quote: `'O''Brien'` is two adjacent literals that Databricks joins into `OBrien`. It reads backslashes as escapes instead: `'O\'Brien'`. Postgres and DuckDB are the other way round. A macro that writes values into SQL as literals should `dispatch()` a Databricks variant:

```sql
{% macro literal(v) %}{{ dispatch('literal', 'my_macros')(v) }}{% endmacro %}{% macro default__literal(v) %}'{{ v | replace("'", "''") }}'{% endmacro %}{% macro databricks__literal(v) %}'{{ v | replace("\\", "\\\\") | replace("'", "\\'") }}'{% endmacro %}
```

Lookups (`ref('countries')`) are inlined in a form every engine reads the same way.

## Dates and times

`run.date` is a date, not a string. It’s `DRE_RUN_DATE` when set, else the date of `DRE_RUN_AT` in the run’s timezone when that’s set, else today in the run’s timezone. Everything below is worked out when the template renders, so the compiled SQL holds plain literals, and a rerun with the same `DRE_RUN_DATE` renders the same SQL.

```sql
where txn_date between '{{ run.date.prev_month.start.date }}' and '{{ run.date.prev_month.end.date }}'-- where txn_date between '2026-08-01' and '2026-08-31'
```

### Timezone

The run’s timezone decides what “today” is and what midnight means. It’s **UTC** unless you set one, nearest first:

1.  `--timezone Australia/Sydney`
2.  `DRE_TIMEZONE`
3.  `timezone:` on the `schedules.yml` entry, or its shared timing’s (with `--schedule`)
4.  `timezone:` in the report’s YAML
5.  `+timezone:` in folder config
6.  `timezone:` in `dre_project.yml`

Names are IANA timezone names (`Europe/London`, `America/New_York`, `UTC`). `run.timezone` renders the one in use, and `run_results.json` and the JSON events record it.

### Dates

A date renders as `YYYY-MM-DD`.

Parts

`year`, `month`, `day`, `quarter`, `week`, `week_year`, `weekday` (1 = Monday … 7), `day_of_year`, `days_in_month`

Neighbours

`prev_day`, `next_day`, `add(days=, weeks=, months=, years=)` (negative to go back; 31 Jan + 1 month is 28/29 Feb)

Boundaries

`week_start`, `week_end`, `month_start`, `month_end`, `quarter_start`, `quarter_end`, `year_start`, `year_end`

Periods

`this_week`, `prev_week`, `next_week`, `this_month`, `prev_month`, `next_month`, `this_quarter`, `prev_quarter`, `next_quarter`, `this_year`, `prev_year`, `next_year`, `as_period()` (the day itself)

Formats

`iso`, `yyyymmdd`, `ddmmyyyy`, `yyyy`, `mm`, `dd`, `format('%d/%m/%Y')` (strftime)

As time

`start`, `end` (its first and last instant), `unix`, `unix_ms` (its midnight)

### Periods

A period is a run of whole days: a week, a month, a quarter, a year, or any range.

`start`, `end`

Its first and last instant: `2026-08-01 00:00:00`, `2026-08-31 23:59:59.999999`.

`start.date`, `end.date`

Its first and last day: `2026-08-01`, `2026-08-31`. Also `first_day`, `last_day`.

`next`, `prev`

The adjacent period of the same kind (`prev_month.prev` is two months back).

`days`

How many days it has.

Filter a `date` column with the days and a `timestamp` column with the instants:

```sql
where txn_date between '{{ p.start.date }}' and '{{ p.end.date }}'where txn_ts   between '{{ p.start }}'      and '{{ p.end }}'where txn_ts   >=      '{{ p.start }}'      and txn_ts < '{{ p.next.start }}'
```

The last form is the safest for timestamps: some databases keep fewer than 6 decimal places and round `23:59:59.999999` up to the next day.

### Datetimes

A datetime is an instant, shown in a timezone. It renders as `YYYY-MM-DD HH:MM:SS`, with `.ffffff` only when there’s a fraction.

Parts

`date`, `time`, `year`, `month`, `day`, `hour`, `minute`, `second`, `timezone`, `offset`

Other zones

`utc`, `tz('Europe/London')`: the same instant, shown elsewhere

Formats

`iso` (with offset: `2026-08-01T00:00:00+10:00`), `unix`, `unix_ms`, `format('%H:%M')`

Moving

`add(days=, weeks=, months=, years=, hours=, minutes=, seconds=)`

`run.now` is the instant the run started, unless `DRE_RUN_AT` pins it to the instant the run was scheduled for. Without `DRE_RUN_AT` it isn’t reproducible, and `DRE_RUN_DATE` doesn’t change it.

`run.scheduled_at` is that `DRE_RUN_AT` instant, shown in the run’s timezone, and `none` when `DRE_RUN_AT` isn’t set. It tells two firings on the same day apart:

```yaml
path: "out/intraday-{{ run.scheduled_at.format('%Y%m%d-%H%M') if run.scheduled_at else run.date.yyyymmdd }}.csv"
```

A day’s first instant depends on the zone: with `timezone: Australia/Sydney`, `run.date.start.utc` is 13:00 or 14:00 the previous day, and `run.date.unix` is Sydney’s midnight. Use `.utc` when the table stores UTC timestamps but the report is about Sydney days.

### Building dates

`date('2026-03-15')`, `date(2026, 3, 15)`

A date.

`'2026-03-15' | as_date`

A date from a string var (`YYYY-MM-DD` or `YYYYMMDD`).

`datetime('2026-03-15 10:30:00')`, `| as_datetime`

A datetime, in the run’s zone unless the string has an offset (`...+10:00`, `...Z`).

`month_of(2026, 2)`, `quarter_of(2026, 1)`, `year_of(2026)`

A period.

`week_of(2026, 12)`

Week 12 of 2026, in the project’s week numbering.

`date_range('2026-01-05', '2026-01-18')`

Any run of days; its `next` is the following run of the same length.

### Named periods: `period()`

`period(name)` is a period relative to `run.date`, so a schedule var can pick a report’s range:

schedules.yml

```yaml
- {name: flash_daily, report: sales, cron: "0 7 * * *", vars: {period: yesterday}}- {name: close_monthly, report: sales, cron: "0 6 1 * *", vars: {period: last_month}}
```

```sql
{% set p = period(var('period')) %}where sale_date between '{{ p.start.date }}' and '{{ p.end.date }}'
```

Name

Period

`today`, `yesterday`

That one day.

`this_week`, `last_week`

The whole week (see `week_start`).

`this_month`, `last_month`, `this_quarter`, `last_quarter`, `this_year`, `last_year`

The whole month, quarter or year.

`mtd`, `qtd`, `ytd`

From the start of the month, quarter or year through `run.date` itself.

`last_n_days`

The `n` days before `run.date`: `period('last_n_days', n=7)`.

`last_n_months`

The `n` whole months before `run.date`’s month.

`as_of=` counts from another date: `period('last_month', as_of=var('as_at'))`.

### Weeks

dre\_project.yml

```yaml
week_start: monday      # or sunday: moves week_start, week_end and the week periodsweek_numbering: iso     # or us
```

-   `iso` (default): week 1 holds the year’s first Thursday, so the first days of January can belong to the previous year’s week 52 or 53. `week_year` gives that year.
-   `us`: week 1 holds 1 January.

[Edit page](https://github.com/get-dre/dre/edit/master/docs/templates.md)

[Previous  
Orchestration recipe](https://getdre.com/docs/orchestration/)[Next  
Lookups](https://getdre.com/docs/lookups/)