§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:
- Data partitioning: Models can declare partition columns that flow through the SQL and are available in the warehouse
- Freshness detection: Models can specify time thresholds to detect stale data
- Contract validation: Integration with
runtime_pinsvalidates that partition columns exist in the physical schema - Time-based staleness detection: The engine automatically rebuilds models when data exceeds the freshness threshold
Implementation Scope
Implemented Components
| Component | File | Status |
|---|---|---|
| AST | strata/ast.py | ModelDecl.partition_by, freshness |
| Parser | strata/parser.py | partition_by: [expr-list], freshness: <value> |
| SQL Generation | strata/sqlgen.py | partition_by in _base_select() |
| Analysis | strata/analysis.py | ModelDecl -> Plan -> TypedModel flow |
| Executor | strata/exec.py | parse_freshness_threshold(), staleness detection |
| Tests | tests/test_warehouse_partition_freshness.py | 12 tests, 587 subtests |
Supported Syntax
-- 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
| Spec | Description | Threshold |
|---|---|---|
incremental | Only when upstream changes | None (not time-based) |
daily | Data must be from today | 24 hours |
weekly | Data must be from this week | 7 days |
monthly | Data must be from this month | 30 days |
Nh (e.g. 1h, 24h) | N hours | N hours |
Nd (e.g. 7d, 30d) | N days | N days |
Nw (e.g. 2w) | N weeks | N*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_pinsKey Decisions
partition_by as columns in SELECT: Instead of using
DISTRIBUTE BY(not supported by DuckDB in views), they are projected asds AS __partition_colin the base subqueryfreshness as string: Kept as a string for flexibility, with lazy parsing via
parse_freshness_threshold()Validation in runtime_pins: Only validates when there is a contract (behavior consistent with the rest of the system)
Time-based staleness: Integrated into
run()after staleness detection by code/sources
Current State
What Works
- Parser supports
partition_by [expr-list]andfreshness: <value> - SQL generation includes
__partition_colin 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
| Category | Tests | Status |
|---|---|---|
| SQL Generation | 3 | Passing |
| Execution | 4 | Passing |
| Error handling | 1 | Passing |
| Freshness parsing | 4 | Passing |
| Total | 12 | Passing |
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
| Improvement | Description | Effort |
|---|---|---|
freshness_column | Support event-time column for more accurate staleness | Medium |
| Warehouse partitioning | Add support for physical partitioning in warehouses that support it | High |
Filter __partition_col | Automatically exclude from the final SELECT | Low |
Medium Priority
| Improvement | Description | Effort |
|---|---|---|
| Watermark support | Support watermarks for late-arriving data | Medium |
| Freshness in queries | Add @staleness_ok hint for ad-hoc queries | Low |
Composite partition_by | Support multiple partition columns with hierarchy | Low |
Composite freshness | Support multiple thresholds (e.g. freshness: 1h, daily) | Medium |
Low Priority
| Improvement | Description | Effort |
|---|---|---|
| Custom freshness | Support complex expressions (e.g. freshness: now() - interval '1 day') | High |
| Staleness cascade | Automatically propagate staleness to downstream models | Medium |
| Freshness override | Allow freshness override at execution time | Low |
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 BYfor partitioning - BigQuery: native
PARTITION BY - Redshift:
DISTKEYandSORTKEY - 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
| Metric | Value |
|---|---|
| Lines of code (strata/) | ~9,000 |
| Total tests | 336 |
| Subtests | 587 |
| partition_by/freshness tests | 12 |
| Modified files | 6 |
| New files | 1 |
| Freshness spec coverage | 95% (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.
-- 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 -- OK2. freshness_column: Staleness based on event-time
New syntax:
model m { from s freshness 1h freshness_column: ts }Behavior:
- If
freshness_columnis specified,SELECT MAX(column) FROM viewis 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:
class Dialect:
supports_partitioning: bool # True if the warehouse supports partitioning
partition_clause(columns) # Generates the partitioning clauseChanges to Warehouse:
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:
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
freshnessand other attributes
Use cases:
- Development or testing models
- Exploratory queries
- Models with static data
2. Multiple freshness thresholds
Syntax:
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:
# 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:
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:
# 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:
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:
# 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:
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 too3. Freshness override at execution time
CLI syntax:
strata run module.strata --freshness 2h
strata run module.strata --freshness daily
strata run module.strata --freshness 7dBehavior:
- 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