Incremental SQL Models
Beta
Learn about incremental SQL models and how they enable incremental processing for complex stateful patterns.
This guide explains incremental SQL models and how they enable incremental processing for complex stateful patterns.
Overview
Incremental SQL models are SQL models that dereference their own previous materialization to enable incremental processing. Incremental SQL models are the raw foundation of incremental features. They are general-purpose and enable any incremental SQL pattern, including advanced use cases like windowed incremental features that require complex stateful processing beyond what simple incremental entity vars can express.
Instead of recalculating from all historical data on every run, incremental SQL models reuse previously computed results and only process new data that has arrived since the last checkpoint.
What are incremental SQL models?
An incremental SQL model is a SQL-template model that:
- Uses
this.DeRef() with pre_existing=true to reference a previous materialization of itself - Merges previous state with new data instead of recalculating from scratch
- Handles both incremental processing (when baseline exists) and full refresh (when no baseline exists)
Unlike regular SQL models that process all data on each run, incremental SQL models process only new data that arrived since the last checkpoint and merge it with previous state.
How incremental SQL models work
When you define an incremental SQL model, Profiles stores previous materializations as checkpoints, identifies new data that has arrived since the baseline checkpoint, merges the previous state with new data using your SQL logic, and produces updated results that become the new checkpoint.
Mandatory requirement:
All inputs used by incremental SQL models must have contract: is_append_only: true configured in the input’s contract. Without this configuration, delta models cannot be generated and incremental processing will not work.
See Incremental SQL Models Developer Documentation for configuration details.
When to use incremental SQL models
Use incremental SQL models when:
- You want to reduce warehouse costs when processing large historical datasets (millions or billions of rows)
- New data arrives regularly (daily, hourly, or more frequently)
- The volume of new data is significantly smaller than your total dataset
- Your use case requires stateful processing that cannot be expressed with simple aggregations
Tip:
For simple aggregations like SUM, MIN, MAX, COUNT, prefer using Simple Aggregations with merge property. For averages and arrays, use Compound Aggregations.
Incremental SQL models are best for complex scenarios requiring stateful processing.
Relationship to other Profiles features
Incremental SQL models work alongside other Profiles capabilities:
- SQL Models: Incremental SQL models are specialized SQL models that dereference their own past, using the same
sql_models.yaml structure and model_spec configuration as regular SQL models. Both regular and incremental SQL models can be materialized as tables, views, or ephemeral sources. - Incremental Features: Incremental SQL models enable incremental processing for complex patterns that cannot be expressed using simple incremental entity vars.
- Checkpoints: Incremental SQL models rely on checkpoints to reference previous materializations and enable incremental processing.
Supported patterns
Incremental SQL models support two main pattern categories:
See also
Questions? Contact us by Email or on
Slack
