Skip to content

Macro variables

The most common use case for macros is variable substitution. For example, you might have a SQL query that filters by date in the WHERE clause.

Instead of manually changing the date each time the model is run, you can use a macro variable to make the date dynamic. With the dynamic approach, the date changes automatically based on when the query is run.

Consider this query that filters for rows where column my_date is after '2023-01-01':

1
2
3
SELECT *
FROM table
WHERE my_date > '2023-01-01'

To make this query's date dynamic you could use the predefined SQLMesh macro variable @execution_ds:

1
2
3
SELECT *
FROM table
WHERE my_date > @execution_ds

The @ symbol tells SQLMesh that @execution_ds is a macro variable that requires substitution before the SQL is executed.

The macro variable @execution_ds is predefined, so its value will be automatically set by SQLMesh based on when the execution started. If the model was executed on February 1, 2023 the rendered query would be:

1
2
3
SELECT *
FROM table
WHERE my_date > '2023-02-01'

This example used one of SQLMesh's predefined variables, but you can also define your own macro variables.

We describe SQLMesh's predefined variables below; user-defined macro variables are discussed in the SQLMesh macros and Jinja macros pages.

Predefined Variables

SQLMesh comes with predefined variables that can be used in your queries. They are automatically set by the SQLMesh runtime.

Most predefined variables are related to time and use a combination of prefixes (start, end, execution) and postfixes (date, ds, ts, epoch, millis). They are described in the next section; other predefined variables are discussed in the following section.

Temporal variables

SQLMesh uses the python datetime module for handling dates and times. It uses the standard Unix epoch start of 1970-01-01. All predefined variables with a time component use the UTC time zone.

Prefixes:

  • start - The inclusive starting interval of a model run.
  • end - The inclusive end interval of a model run.
  • execution - The timestamp of when the execution started.

Postfixes:

  • date - A python date object that converts into a native SQL Date.
  • ds - A date string with the format: '%Y-%m-%d'
  • ts - An ISO 8601 datetime formatted string: '%Y-%m-%d %H:%M:%S'.
  • tstz - An ISO 8601 datetime formatted string with timezone: '%Y-%m-%d %H:%M:%S%z'.
  • epoch - An integer representing seconds since Unix epoch.
  • millis - An integer representing milliseconds since Unix epoch.

All predefined temporal macro variables:

  • date

    • @start_date
    • @end_date
    • @execution_date
  • datetime

    • @start_dt
    • @end_dt
    • @execution_dt
  • ds

    • @start_ds
    • @end_ds
    • @execution_ds
  • ts

    • @start_ts
    • @end_ts
    • @execution_ts
  • tstz

    • @start_tstz
    • @end_tstz
    • @execution_tstz
  • epoch

    • @start_epoch
    • @end_epoch
    • @execution_epoch
  • millis

    • @start_millis
    • @end_millis
    • @execution_millis

Runtime variables

SQLMesh provides two other predefined variables used to modify model behavior based on information available at runtime.

  • @runtime_stage - A string value that denotes the current stage of the SQLMesh runtime. It can take one of the following values:
    • 'loading' - The project is currently being loaded into SQLMesh's runtime context.
    • 'creating' - The model tables are being created.
    • 'evaluating' - The models' logic is being evaluated.
    • 'testing' - The models' logic is being evaluated in the context of a unit test.
  • @gateway - A string value that represents the name of the selected gateway.

Audit-only variables

Some predefined variables are only supported in SQLMesh audit definitions.

The {{ this_model }} Jinja macro variable may be used in model definitions for the rare cases when SQLGlot cannot fully parse a statement and you need to reference the model's underlying physical table directly. We recommend against using it unless absolutely required.