Help understanding sqlmesh scheduler

[thulio]

I've been playing with sqlmesh for the past few days, but I'm not sure if I understood how the scheduler works.

Given a project with the following configuration:

# config.yaml
gateways:
  local:
    connection:
      type: bigquery
      project: my-project
      priority: batch

    state_connection:
      type: duckdb
      database: state.db

default_gateway: local

physical_schema_override:
  thulio: thulio

model_defaults:
  dialect: bigquery

This project has a single model (my-project.thulio.transactions_incremental) and an external model (my-project.other_dataset.upstream_model):

MODEL (
  name `my-project`.thulio.transactions_incremental,
  kind INCREMENTAL_BY_UNIQUE_KEY (
    unique_key id,
    lookback 1
  ),
  cron '45 * * * *',
  start '2023-11-14 00:00:00'
);

SELECT
  *
FROM `my-project`.other_dataset.upstream_model
WHERE
  updated_at >= @start_ts
QUALIFY
  row_number() OVER (PARTITION BY id ORDER BY updated_at DESC) = 1

I've already applied sqlmesh plan prod and made no modifications to the model. When running sqlmesh run, I expected that @start_ts would be replaced with a timestamp with the same minutes as the cron parameter, but that is not what I've observed. For example, when executing sqlmesh run on, let's say, "2023-11-14 17:57:00 UTC", sqlmesh replaced @start_ts with "2023-11-14T15:00:00+00:00", but I expected it to be "2023-11-14T15:45:00+00:00". Is this really the correct behavior?

[tobymao]

yes, this is intended behavior. the cron is used for triggering, but the actual intervals used in your queries is floored to the 'interval_unit', which in your case is every hour because your job runs hourly (at every minute 45).

if you want your interval unit to be at the 15 minute grain, you can set interval unit to be "quarter_hour"

https://sqlmesh.readthedocs.io/en/stable/concepts/models/overview/#interval_unit

or, if you're satisfied with hourly interval units, you can adjust accordingly.

feel free to join our slack if you have more questions https://tobikodata.com/slack