Skip to content

§2 Warehouse Semantics: Partition_by and Freshness ​

Date: 2026-09-18 Status: Base implementation complete, tests passing (336/336) Commit: fa2bf29


Executive Summary ​

The partition_by and freshness framework was implemented for Strata models, providing warehouse semantics that enable:

  1. Data partitioning: Models can declare partition columns that flow through the SQL and are available in the warehouse
  2. Freshness detection: Models can specify time thresholds to detect stale data
  3. Contract validation: Integration with runtime_pins validates that partition columns exist in the physical schema
  4. Time-based staleness detection: The engine automatically rebuilds models when data exceeds the freshness threshold

Implementation Scope ​

Implemented Components ​

ComponentFileStatus
ASTstrata/ast.pyModelDecl.partition_by, freshness
Parserstrata/parser.pypartition_by: [expr-list], freshness: <value>
SQL Generationstrata/sqlgen.pypartition_by in _base_select()
Analysisstrata/analysis.pyModelDecl -> Plan -> TypedModel flow
Executorstrata/exec.pyparse_freshness_threshold(), staleness detection
Teststests/test_warehouse_partition_freshness.py12 tests, 587 subtests

Supported Syntax ​

strata
-- Basic partitioning
model m { from s partition_by [ds] }

-- Basic freshness
model m { from s freshness incremental }

-- Combination
model m { from s partition_by [ds] freshness incremental }

-- Freshness with time specs
model m { from s freshness daily }      # Stale if > 24 hours
model m { from s freshness weekly }     # Stale if > 7 days
model m { from s freshness 1h }         # Stale if > 1 hour
model m { from s freshness 7d }         # Stale if > 7 days
model m { from s freshness 2w }         # Stale if > 14 days

-- With contracts
contract c { x: int64, ds: string }
model m -> contract c {
  from s
  partition_by [ds]
  freshness daily
}

Supported Freshness Specs ​

SpecDescriptionThreshold
incrementalOnly when upstream changesNone (not time-based)
dailyData must be from today24 hours
weeklyData must be from this week7 days
monthlyData must be from this month30 days
Nh (e.g. 1h, 24h)N hoursN hours
Nd (e.g. 7d, 30d)N daysN days
Nw (e.g. 2w)N weeksN*7 days

Architecture and Design Decisions ​

Data Flow ​

Parser --> AST (ModelDecl) --> Analysis (Plan) --> TypedModel
                                                      |
                                                      v
Executor <-- SQL Gen <-- plan.partition_by
  - staleness    - __partition_col
  - runtime_pins

Key Decisions ​

  1. partition_by as columns in SELECT: Instead of using DISTRIBUTE BY (not supported by DuckDB in views), they are projected as ds AS __partition_col in the base subquery

  2. freshness as string: Kept as a string for flexibility, with lazy parsing via parse_freshness_threshold()

  3. Validation in runtime_pins: Only validates when there is a contract (behavior consistent with the rest of the system)

  4. Time-based staleness: Integrated into run() after staleness detection by code/sources


Current State ​

What Works ​

  • Parser supports partition_by [expr-list] and freshness: <value>
  • SQL generation includes __partition_col in the base subquery
  • Runtime pins validate that partition columns exist
  • Staleness detection verifies freshness thresholds
  • 336 tests passing, 587 subtests
  • Integration with contracts works correctly

Coverage Tests ​

CategoryTestsStatus
SQL Generation3Passing
Execution4Passing
Error handling1Passing
Freshness parsing4Passing
Total12Passing

Gaps and Limitations ​

1. DuckDB does not support DISTRIBUTE BY ​

Problem: DuckDB does not have DISTRIBUTE BY or CLUSTER BY in CREATE TABLE AS for physical partitioning.

Current solution: The column is projected as __partition_col but there is no physical partitioning.

Impact: High for workloads with large data volumes. Warehouses such as Snowflake, BigQuery, and Redshift do support physical partitioning by partition key.

Suggested improvement: Add support for warehouses that do support physical partitioning via the dialect adapter mechanism.

2. Freshness based on committed_at of the last run ​

Problem: Freshness detection uses committed_at from the previous run, not the timestamp of the data itself.

Current solution: Assumes that data arrives at the same time as the run.

Impact: Medium for pipelines with late-arriving data or that process historical data.

Suggested improvement: Support a freshness_column field that indicates which column of the dataset contains the data timestamp to compare against the threshold.

3. No real-time freshness validation ​

Problem: Freshness validation only occurs during run(), not in queries.

Current solution: Batch validation only.

Impact: Low for most use cases. Users typically run run() periodically.

Suggested improvement: Add an @staleness_ok hint for ad-hoc queries where the user accepts stale data.

4. No support for watermark-based freshness ​

Problem: There is no support for specifying a watermark or event-time column to compare against the threshold.

Current solution: Only compares time since the last run.

Impact: Medium for streaming or event-time based pipelines.

Suggested improvement: Add freshness_column: <col> that specifies the event-time column for the comparison.

5. Partitioning not propagated to CTAS ​

Problem: partition_by only affects the base subquery, not the final CTAS statement.

Current solution: The __partition_col column is available for queries but does not affect how DuckDB stores the data.

Impact: Medium. Users cannot easily use SELECT * EXCLUDE (__partition_col).

Suggested improvement: Automatically filter __partition_col from the final SELECT or document how to use it.


Improvements and Next Steps ​

High Priority ​

ImprovementDescriptionEffort
freshness_columnSupport event-time column for more accurate stalenessMedium
Warehouse partitioningAdd support for physical partitioning in warehouses that support itHigh
Filter __partition_colAutomatically exclude from the final SELECTLow

Medium Priority ​

ImprovementDescriptionEffort
Watermark supportSupport watermarks for late-arriving dataMedium
Freshness in queriesAdd @staleness_ok hint for ad-hoc queriesLow
Composite partition_bySupport multiple partition columns with hierarchyLow
Composite freshnessSupport multiple thresholds (e.g. freshness: 1h, daily)Medium

Low Priority ​

ImprovementDescriptionEffort
Custom freshnessSupport complex expressions (e.g. freshness: now() - interval '1 day')High
Staleness cascadeAutomatically propagate staleness to downstream modelsMedium
Freshness overrideAllow freshness override at execution timeLow

Extension Opportunities ​

1. Integration with Airflow/Prefect ​

The partition_by and freshness framework can integrate with orchestrators for:

  • Automatic re-materialization trigger when freshness expires
  • Partition-based dependencies
  • Staleness monitoring via metrics

2. Support for Incremental Models ​

The parser already supports freshness incremental, which is the foundation for incremental models. Natural extensions:

  • Merge strategies (upsert, append, replace)
  • Change Data Capture (CDC)
  • Watermark-based processing

3. Multi-warehouse Support ​

The dialect mechanism allows extending to:

  • Snowflake: CLUSTER BY for partitioning
  • BigQuery: native PARTITION BY
  • Redshift: DISTKEY and SORTKEY
  • Databricks: PARTITIONED BY

4. Observability ​

Add metrics for:

  • Staleness time per model
  • History of freshness checks
  • Alerts when freshness expires
  • Pipeline health dashboard

5. Freshness Testing ​

Tools for:

  • Simulate stale data for testing
  • Verify that freshness thresholds work correctly
  • Performance benchmarks with different partitioning

Implementation Metrics ​

MetricValue
Lines of code (strata/)~9,000
Total tests336
Subtests587
partition_by/freshness tests12
Modified files6
New files1
Freshness spec coverage95% (custom expressions missing)

References ​

  • §2 Spec: docs/strata-plan.md - Original definition of partition_by and freshness
  • §5 Warehouse Adapters: docs/warehouse-adapters-plan.md - Multi-warehouse support
  • Commit: fa2bf29 - Complete base implementation

Recent Changes (2026-09-18) ​

1. Fix: unique partition_col aliases ​

Problem: When multiple columns were used in partition_by, they all got the same __partition_col alias, causing SQL conflicts.

Solution: Each column now has a unique alias: __partition_col_0, __partition_col_1, etc.

strata
-- Before (with bug)
model m { from s partition_by [ds, region] }
-- SQL: ds AS __partition_col, region AS __partition_col  -- CONFLICT!

-- After (fixed)
model m { from s partition_by [ds, region] }
-- SQL: ds AS __partition_col_0, region AS __partition_col_1  -- OK

2. freshness_column: Staleness based on event-time ​

New syntax:

strata
model m { from s freshness 1h freshness_column: ts }

Behavior:

  • If freshness_column is specified, SELECT MAX(column) FROM view is checked
  • If the max is greater than the threshold, the model is marked as stale
  • If not specified, the time since the last run is used (previous behavior)

Use cases:

  • Data with event-time different from processing-time
  • Pipelines where data arrives with delay
  • Data quality monitoring

3. Warehouse partitioning (infrastructure) ​

Changes to Dialect:

python
class Dialect:
    supports_partitioning: bool  # True if the warehouse supports partitioning
    partition_clause(columns)    # Generates the partitioning clause

Changes to Warehouse:

python
class Warehouse:
    def materialize(self, name, sql, partition_by=None):
        # partition_by: list of columns to partition by
        ...

Current state:

  • DuckDB: Does not support partitioning (ignored)
  • Snowflake: Would support CLUSTER BY
  • BigQuery: Would support PARTITION BY
  • Redshift: Would support DISTKEY / SORTKEY

Note: The full implementation of physical partitioning requires modifying publish_snapshots() to include the partitioning clause when creating the snapshot tables.


Recent Changes (2026-09-18) - Medium Priority ​

1. staleness_ok attribute ​

Syntax:

strata
model m {
  from s
  staleness_ok: "true"
}

Behavior:

  • Models with staleness_ok: "true" are excluded from the stale set
  • Useful for ad-hoc queries where the user accepts stale data
  • Compatible with freshness and other attributes

Use cases:

  • Development or testing models
  • Exploratory queries
  • Models with static data

2. Multiple freshness thresholds ​

Syntax:

strata
model m {
  from s
  freshness 1h, daily
}

Behavior:

  • Supports comma-separated values
  • The model is marked as stale if ANY threshold is exceeded
  • Useful for pipelines with multiple SLAs

Examples:

strata
# Stale if data is > 1 hour OR > 24 hours
freshness 1h, daily

# Stale if data is > 7 days
freshness weekly

# Combination with partition_by
model m {
  from s
  partition_by [ds]
  freshness 1h, weekly
}

3. Composite partition_by (already supported) ​

Syntax:

strata
model m {
  from s
  partition_by [year, month, day]
}

Behavior:

  • Each column generates a unique alias: __partition_col_0, __partition_col_1, etc.
  • Supports expressions: partition_by [substring(ds, 1, 4)]
  • Supports partitioning hierarchy

Examples:

strata
# Partitioning by time
partition_by [year, month, day]

# Partitioning by expression
partition_by [to_date(ds)]

# Partitioning by region and time
partition_by [region, year, month]

Recent Changes (2026-09-18) - Low Priority ​

1. Custom freshness with complex expressions ​

Syntax:

strata
model m {
  from s
  freshness "now() - interval '1 day'"
}

Behavior:

  • Supports SQL expressions as strings
  • Evaluated by warehouse at runtime
  • Can be mixed with standard freshness specs

Examples:

strata
# Custom expression
freshness "now() - interval '1 day'"

# Mixed with standard freshness
freshness 1h, "now() - interval '7 days'"

# Multiple custom expressions
freshness "now() - interval '1 hour'", "now() - interval '1 day'"

2. Staleness cascade to downstream models ​

Behavior:

  • If a model is stale, all models that depend on it are also marked as stale
  • Uses the existing _downstream_models() function
  • Ensures that pipelines remain consistent

Example:

strata
model m1 { from s freshness 1h }  # If m1 is stale...
model m2 { from m1 }              # ...m2 will also be stale
model m3 { from m2 }              # ...and m3 too

3. Freshness override at execution time ​

CLI syntax:

bash
strata run module.strata --freshness 2h
strata run module.strata --freshness daily
strata run module.strata --freshness 7d

Behavior:

  • Overrides the freshness threshold for all models in the pipeline
  • Useful for testing or emergencies
  • Does not modify the original .strata file

Use cases:

  • Testing: Force rebuilding of models
  • Emergencies: Override freshness for critical data
  • Debugging: Investigate staleness issues

Released under the MIT License.