Possible bug: SQL models interpolate time macros incorrectly

[florianm]

Problem

I have either misconfigured my SQLMesh project, or SQL models interpolate macro variables incorrectly.

The docs on predefined macro variables say

execution - The timestamp of when the execution started

I would expect that the execution_* family of macro variables are interpolated as the time of the current execution.
#3704 vaguely sounds like this could/should be possible?
Or are the macro variables only accessible from the environment present inside an SQL audit?

I can reproduce that in an SQL model

• the DDL interpolates @execution_ts as 1970-01-01 00:00:00,
• the SQL body interpolates @execution_ts as 2025-06-30 00:00:00, which is the correct date but misses the time component.
• The postfixes (ts, tstz, et al.) seem to work fine!
• The signal can access the ExecutionContext, from which I can reconstruct the last execution time. Source here

Details

Config

Source - a plain DuckDB and DuckLake config.

from sqlmesh.core.config import (
    Config,
    DuckDBConnectionConfig,
    GatewayConfig,
    ModelDefaultsConfig,
)
from sqlmesh.core.config.connection import DuckDBAttachOptions

config = Config(
    gateways={
        "dev": GatewayConfig(
            connection=DuckDBConnectionConfig(
                catalogs={
                    "peoplewa": DuckDBAttachOptions(
                        type="ducklake",
                        path="dev_metadata.ducklake",
                        data_path="data/ducklake",
                        # Optional: encrypted=True, data_inlining_row_limit=10,
                    ),
                },
                extensions=["ducklake"],
            ),
            # Use a separate DuckDB file for state tracking
            state_connection=DuckDBConnectionConfig(
                database="dev_state.db",
                type="duckdb",
            ),
        ),
    },
    default_gateway="dev",
    model_defaults=ModelDefaultsConfig(
        dialect="duckdb",
        start="2024-01-01",
    ),
)

SQL model

The following SQL model reads a CSV file from my seeds folder into my DuckLake.

MODEL (
  name bronze.seed_data,
  kind FULL,
  grain "id",
  cron '*/5 * * * *',
  signals [
    ext_file_updated(execution_ts := @execution_ts,
    file_path := 'seeds/seed_data.csv',
    cron_str := '*/5 * * * *'
    )
  ]
);

SELECT
  *, @execution_ts as "execution_ts"
FROM read_csv('seeds/seed_data.csv', delim = ',', header = true)

Signal

A custom signal (source) reconstructs from the current execution time and the cron schedule when the model could have run last and compares the file's modification time to infer whether the file was updated after the last possible run.

Output

Rendering macros in the SQL statement gets at least the date right, but is missing the time:

/workspaces/sqlmesh-python-models (main) $ uv run sqlmesh render bronze.seed_data
SELECT *,
  '2025-06-30 00:00:00' AS "execution_ts",
  '2025-06-30 00:00:00+00:00' AS "execution_tstz"
FROM READ_CSV('seeds/seed_data.csv', "delim" = ',', "header" = TRUE) AS "_q_0"

The DDL renders the macro to a timestamp but does not seem to have access to the execution context and therefore defaults to the epoch start:. From my signal's debug output:

Got execution time 1970-01-01 00:00:00
The last run before '1970-01-01 00:00:00' based on cron schedule '*/5 * * * *' was 1969-12-31 23:55:00
Checking if file 'seeds/seed_data.csv' was updated (2025-06-22 11:57:42.994376) after last run (1969-12-31 23:55:00): True

I'm not privvy to internals on macros, but the kapa.ai bot thinks

• If @​execution_ts is rendered as '1970-01-01 00:00:00+00:00', it typically means the macro variable is not being set by the runtime for that context. This can happen if:
• The model has not run for the current interval.
• There is a misconfiguration in the model's scheduling or execution context.
• The macro is being referenced outside of a valid execution context.

I would expect that both DDL of SQL models (and ideally Python models too!) as well as the model body would be able to access the execution context. If that is not the case, a mention / workaround / warning in the docs would be very helpful.

MVE

Launch a codespace on https://github.com/florianm/sqlmesh-python-models
See the README for a long form of observations.
Run just pd to run the sqlmesh plan for the dev environment. The console output should match the output above.

What I haven't tried yet: Next I will hook my custom signal into the vanilla SQLMesh example models. This should shake out configuration and DuckLake issues.

Edit: I can access the ExecutionContext from inside the signal and fish out a suitable timestamp from there. Will update the MVE.