The most common use case for macros is variable substitution. For example, you might have a SQL query that filters by date in the
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':
To make this query's date dynamic you could use the predefined SQLMesh macro variable
@ 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:
This example used one of SQLMesh's predefined variables, but you can also define your own macro variables.
SQLMesh comes with predefined variables that can be used in your queries. They are automatically set by the SQLMesh runtime.
These variables are related to time and comprise a combination of prefixes (start, end, execution) and postfixes (date, ds, ts, epoch, millis).
- 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.
- 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'.
- epoch - An integer representing seconds since Unix epoch.
- millis - An integer representing milliseconds since Unix epoch.
All predefined macro variables: