Time Interval Materialization

The time_interval materialization strategy partitions a model by a time column at a fixed granularity: hour, day, month, or year. A run targets named partitions instead of appending past a watermark. Three things follow from that. Re-running the same partition is idempotent. A late-arriving row is picked up on the next run of its partition. A backfill walks the range in order.

Reach for this strategy when:

Your model aggregates by date and you need per-day rebuilds
You want cost and row counts reported per partition

A transformation incremental model is the other option. It loads the rows newer than the target’s watermark and needs no partition column. See Incremental.

TOML reference

name = "fct_daily_orders"
depends_on = ["stg_orders"]

[[sources]]
catalog = "warehouse"
schema = "raw"
table = "stg_orders"

[strategy]
type = "time_interval"
time_column = "order_date"        # column on the model output
granularity = "day"               # hour | day | month | year
lookback = 0                      # optional: recompute the previous N partitions
batch_size = 1                    # optional: combine N partitions per SQL statement
first_partition = "2024-01-01"    # required if you use --missing

[target]
catalog = "warehouse"
schema = "marts"
table = "fct_daily_orders"

Field	Type	Default	Meaning
`time_column`	string	required	Column on the model output that holds the partition value. The compiler validates it exists, has a date/timestamp type (when known), is non-nullable, and passes SQL identifier validation.
`granularity`	enum	required	One of `hour`, `day`, `month`, `year`. Determines the canonical partition key format.
`lookback`	u32	`0`	Recompute the previous N partitions on each run, in addition to whichever partitions the CLI selected. The standard pattern for late-arriving data without re-running the whole table.
`batch_size`	NonZeroU32	`1`	Combine N consecutive partitions into a single SQL statement when backfilling. `1` is atomic per partition (recommended). `>1` trades atomicity for backfill throughput.
`first_partition`	string	none	Lower bound for `--missing` discovery, in canonical key format (e.g. `"2024-01-01"` for daily). Required when using `--missing`; otherwise optional.

SQL placeholders

The model SQL must reference both @start_date and @end_date. Rocky substitutes them at compile time per partition with quoted timestamp literals ('2026-04-07 00:00:00'):

SELECT
    DATE_TRUNC('day', order_at) AS order_date,
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS revenue
FROM stg_orders
WHERE order_at >= @start_date
  AND order_at <  @end_date
GROUP BY 1, 2

Each window is half-open. A row at exactly @start_date is in the window. A row at exactly @end_date belongs to the next partition. This matches Spark, BigQuery, and Postgres tsrange conventions.

  granularity = "day"

   04-06 00:00      04-07 00:00      04-08 00:00      04-09 00:00
        │                │                │                │
   ─────┼────────────────┼────────────────┼────────────────┼─────► time
        │  partition     │  partition     │  partition     │
        │  2026-04-06    │  2026-04-07    │  2026-04-08    │
        └────────────────┴────────────────┴────────────────┘
                         ▲
                         └── @end_date of partition 2026-04-06,
                             and @start_date of partition 2026-04-07

   running partition 2026-04-07 substitutes:
       @start_date → '2026-04-07 00:00:00'   a row here is included
       @end_date   → '2026-04-08 00:00:00'   a row here is excluded

The same rule applies at every grain. For granularity = "hour", partition key 2026-04-07T13 substitutes:

@start_date → '2026-04-07 13:00:00'
@end_date → '2026-04-07 14:00:00'

The compiler emits E024 if either placeholder does not filter the rows. A partition window must have both bounds. Each placeholder must appear in a WHERE, HAVING or QUALIFY clause, or in the ON of an inner join. A placeholder in a comment, in a longer string or only in the SELECT list does not count. The compiler also emits E024 when it cannot parse the SQL.

The filter must reach every row the model emits:

Set operations. Each UNION / UNION ALL branch needs its own filter, or one filter must wrap the whole union. INTERSECT needs one filtered side. EXCEPT needs a filtered left side.
CTEs and subqueries. A filter inside a CTE or derived table counts only when the output reads it. A filter in a CTE nothing reads counts for nothing. So does a filter inside a scalar subquery in the SELECT list.
Joins. An inner or semi join is bounded when either side is. A LEFT join is bounded only by its left side, a RIGHT join by its right side. A FULL OUTER join needs both sides bounded.
Date spines. A table function such as GENERATE_SERIES(@start_date, @end_date, INTERVAL 1 DAY) does not count on its own. Its end is inclusive in most warehouses, so it emits a row that belongs to the next partition. Add WHERE d >= @start_date AND d < @end_date on the spine column.

Partition key format

Canonical ISO format, derived from granularity. These keys appear in the state store, the --partition / --from / --to CLI flags, and the JSON output:

Granularity	Format	Example
`hour`	`%Y-%m-%dT%H`	`2026-04-07T13`
`day`	`%Y-%m-%d`	`2026-04-07`
`month`	`%Y-%m`	`2026-04`
`year`	`%Y`	`2026`

Rocky restricts the format on purpose. It rejects 2026-04-7, which drops the zero padding, even though chrono would parse it.

CLI flags

Note: the canonical, auditable form is rocky plan followed by rocky apply <plan-id>. The rocky run single-step alias fuses plan + apply into one invocation for local iteration and automation. Every partition-selection flag below is accepted on both rocky plan and rocky run.

rocky run accepts seven new flags for selecting and modifying which partitions to compute. The selection flags are mutually exclusive (clap enforces it):

Flag	Behavior
`--partition KEY`	Run exactly one partition by canonical key. Errors if the format doesn’t match the model’s grain.
`--from FROM --to TO`	Run a closed inclusive range. Both bounds must align to the grain.
`--latest`	Run the partition containing `now()` (UTC). Default for `time_interval` models when no other selection flag is given.
`--missing`	Compute the diff between expected partitions (`first_partition` → `now()`) and what’s recorded as `Computed` in the `PARTITIONS` state-store table; run only the gaps. Errors if `first_partition` is unset.
`--lookback N`	Recompute the previous N partitions in addition to the selected ones. CLI override beats the model’s TOML `lookback`.
`--parallel N`	Run N partitions concurrently (default 4; pass `--parallel 1` for serial). Driven by `futures::stream::buffer_unordered` so the per-partition futures are polled in the same task — no spawn, no `Send` constraint. Warehouse-query parallelism only: state writes serialize through redb’s single-writer lock. Caveat: DuckDB’s adapter holds a connection mutex and runs `execute_statement` synchronously, so partitions always run serially against DuckDB regardless of `--parallel`. Snowflake and Databricks (REST-based async I/O) parallelize up to N as expected.

# Run today's partition
rocky run --filter client=acme

# Run a specific historical partition
rocky run --filter client=acme --partition 2026-04-07

# Backfill a closed range
rocky run --filter client=acme --from 2026-04-01 --to 2026-04-07

# Find and run gaps in the state store
rocky run --filter client=acme --missing

# Re-run today + the previous 3 days (for late-arriving data)
rocky run --filter client=acme --lookback 3

Per-warehouse SQL

Each adapter implements insert_overwrite_partition in its own way. Each one returns a Vec<String>, so the runtime can issue more than one statement. Atomicity holds per partition. If a statement fails mid-batch, the runtime issues ROLLBACK and marks the partition Failed in the state store.

Before it runs any partition, Rocky checks that the target table exists. If not, it creates it by running the model SQL over an empty window. The empty window means the model reads no upstream rows. A model with an ungrouped aggregate still writes one row; every other shape writes none. The table’s columns come from that query. The partitions then run as below.

Databricks (Delta Lake)

Single statement using Delta’s atomic REPLACE WHERE:

INSERT INTO `warehouse`.`marts`.`fct_daily_orders`
REPLACE WHERE order_date >= '2026-04-07 00:00:00' AND order_date < '2026-04-08 00:00:00'
SELECT
    DATE_TRUNC('day', order_at) AS order_date,
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS revenue
FROM `warehouse`.`raw`.`stg_orders`
WHERE order_at >= '2026-04-07 00:00:00'
  AND order_at <  '2026-04-08 00:00:00'
GROUP BY 1, 2

Works on un-partitioned tables too; REPLACE WHERE doesn’t require physical partitioning.

Snowflake

Four-statement vec via explicit transaction. Snowflake’s REST API runs one statement per call by default, so the runtime issues each as a separate POST /api/v2/statements:

BEGIN;
DELETE FROM "warehouse"."marts"."fct_daily_orders"
WHERE order_date >= '2026-04-07 00:00:00' AND order_date < '2026-04-08 00:00:00';
INSERT INTO "warehouse"."marts"."fct_daily_orders"
SELECT ... ;
COMMIT;

DuckDB

Same shape as Snowflake; keeps the abstraction symmetric across warehouses:

BEGIN;
DELETE FROM "marts"."fct_daily_orders"
WHERE order_date >= '2026-04-07 00:00:00' AND order_date < '2026-04-08 00:00:00';
INSERT INTO "marts"."fct_daily_orders"
SELECT ... ;
COMMIT;

BigQuery

One statement: a BEGIN TRANSACTION / COMMIT TRANSACTION script joining the delete and the insert into a single job. BigQuery’s REST API is stateless. Each jobs.query call is its own session. Separate BEGIN and COMMIT statements fail with “Transaction control statements are supported only in scripts or sessions.” One script keeps the delete and the insert atomic:

BEGIN TRANSACTION;
DELETE FROM `warehouse`.`marts`.`fct_daily_orders`
WHERE order_date >= '2026-04-07 00:00:00' AND order_date < '2026-04-08 00:00:00';
INSERT INTO `warehouse`.`marts`.`fct_daily_orders`
SELECT ... ;
COMMIT TRANSACTION

State store

Per-partition state lives in its own PARTITIONS table in the redb state store (<models>/.rocky-state.redb by default). There is one row per (model_name, partition_key):

PartitionRecord {
    model_name: String,
    partition_key: String,
    status: PartitionStatus,   // Computed | Failed | InProgress | Interrupted
    computed_at: DateTime<Utc>,
    row_count: u64,
    duration_ms: u64,
    run_id: String,
    checksum: Option<u64>,
}

--missing reads this table to compute the gap between expected and actual. RUN_HISTORY stays the source of truth for whole-run success. PARTITIONS is the source of truth for per-partition success. The run_id field cross-references the two when you investigate a failure.

Timezone

v1 is UTC-only. The time_column is interpreted as UTC and --latest resolves to “the partition containing chrono::Utc::now()”. If your data lives in a non-UTC timezone, convert in your model SQL or accept UTC partition boundaries. A timezone field on the strategy config is a v2 candidate.

JSON output

After a time_interval run, rocky run --output json includes per-partition materialization entries and a per-model summary:

{
  "materializations": [
    {
      "asset_key": ["warehouse", "marts", "fct_daily_orders"],
      "duration_ms": 1234,
      "metadata": { "strategy": "time_interval", "watermark": null },
      "partition": {
        "key": "2026-04-07",
        "start": "2026-04-07T00:00:00Z",
        "end": "2026-04-08T00:00:00Z",
        "batched_with": []
      }
    }
  ],
  "partition_summaries": [
    {
      "model": "fct_daily_orders",
      "partitions_planned": 1,
      "partitions_succeeded": 1,
      "partitions_failed": 0
    }
  ]
}

The dagster-rocky integration reads this through the autogenerated Pydantic PartitionInfo and PartitionSummary models. It translates the strategy discriminator into a Dagster DailyPartitionsDefinition, or Hourly, Monthly, or TimeWindow for year. See the dagster partitions guide for the full mapping.

Comparison with `incremental`

Both strategies process only part of the data on each run. They differ in what decides the part:

	`incremental`	`time_interval`
Unit of work	Rows newer than the target’s `MAX(<watermark>)`	One partition window per statement
SQL marker	`@incremental_filter`	`@start_date` and `@end_date`
Late data	`lookback` with `unique_key`	`lookback` partitions
Backfill a range	`rocky run --full-refresh` rebuilds all	`--from` / `--to`
State	Read from the target	`PARTITIONS` table in the state store

Pick time_interval when the output is grouped by date and a day must be recomputed as a whole. Pick incremental when new or changed rows can be loaded on their own. See Incremental.

Limitations (v1)

The following are deferred:

Rocky DSL placeholder syntax — @start_date / @end_date are recognized in .sql files only. The .rocky parser will gain @var syntax in v1.1.
Native partitions on PostgreSQL and Redshift — both adapters run time_interval as DELETE then INSERT in one transaction. A declarative-partition (child-table truncate) route is a follow-up.
Sub-day granularities below hour — belongs in streaming systems.
Multi-column partitions — single time column only in v1.
Partition column transformations — time_column must be a real output column, not an expression.
--batch-size CLI flag — TOML-only in v1; promote to CLI when there’s demand.