# Incremental Features


Making a feature incremental improves performance and reduces costs by processing only new data instead of recomputing everything from scratch. 

See [Incremental Features Overview]({{< ref "profiles/concepts/incremental-features/_index.md" >}}) for more information.

## Quick decision guide

| Can you express your logic as... | Use this approach | Read more |
|----------------------------------|-------------------|-----------|
| A single composable function (`SUM`, `MIN`, `MAX`, `COUNT`) | **Single Incremental Entity Var** | [Implementation Guide]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}}) |
| Combine multiple composable aggregations (for example, `AVG()`=`SUM()`/`COUNT()`) | **Express goal entity var as a composition of multiple Incremental Entity Vars** | [Implementation Guide]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}}) |
| Window functions, PIVOTs, or advanced logic | **Define an incremental SQL model and define entity vars (without merge) on top** | [Implementation Guide]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/" >}}) |

Follow this decision tree if you're unsure which approach to use:

{{< image src="images/profiles/incremental-features/incremental-patterns-decision-tree.webp" alt="Incremental features decision tree" >}}

### Understand composable functions

**Composable functions** can be computed incrementally by combining results from different time periods:
- `SUM`, `MIN`, `MAX`, `COUNT` are composable - they can merge results across checkpoints
- `AVG` is **NOT** composable (you cannot average two averages correctly without knowing the counts)

**Compound composable functions** can be expressed as combinations of simple composable functions:
- `AVG(x) = SUM(x) / COUNT(x)` - requires two composable components
- `WEIGHTED_AVG(x, w) = SUM(x * w) / SUM(w)` - requires two composable components

This distinction matters because composable functions can use simple merge logic, while non-composable functions like `AVG` require compound aggregations.

## Simple aggregations

**Best for:** Direct aggregations using a single composable `merge` function

### When to use {#when-to-use-simple-aggregations}

- Computing totals, counts, min/max values  
- Using `SUM`, `MIN`, `MAX`, or `COUNT`
- Straightforward merge operations (for example, sum of previous + new, min/max of previous and new)

### Why start here {#why-start-with-simple-aggregations}

- Minimal configuration (just add a `merge` property)  
- Predictable behavior  
- Best performance  
- Works across all warehouses

### Use cases {#use-cases-simple-aggregations}

| Use Case | Function |
|----------|----------|
| Total lifetime value | `SUM` |
| First purchase date | `MIN` |
| Last login time | `MAX` |
| Total event count | `COUNT` |

### Quick example {#quick-example-simple-aggregations}

```yaml
- entity_var:
    name: total_purchases
    select: SUM({{orders.value}})
    merge: SUM({{rowset.total_purchases}})  # ← Key: merge mirrors select
    from: inputs/orders
```

### Detailed reference {#detailed-reference-simple-aggregations}

See [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}}) for implementation and detailed examples.

## Compound aggregations

**Best for:** Metrics combining multiple simple aggregations

### When to use {#when-to-use-compound-aggregations}

- Need to calculate averages (sum ÷ count)
- Combining multiple aggregations
- Array operations (union, distinct, sort)

### How it works {#how-it-works-compound-aggregations}

This approach breaks down complex metrics into simple components, then combines them:

```yaml
# Components
- entity_var:
    name: order_value_sum
    select: SUM({{orders.value}})
    merge: SUM({{rowset.order_value_sum}})

- entity_var:
    name: order_count
    select: COUNT(*)
    merge: SUM({{rowset.order_count}})

# Combination
- entity_var:
    name: avg_order_value
    select: "{{user.order_value_sum}} / NULLIF({{user.order_count}}, 0)"
```

### Use cases {#use-cases-compound-aggregations}

| Use case | Components |
|----------|------------------|
| Average order value | sum + count |
| Conversion rate | purchase_count + session_count |
| List of visited countries | array aggregation + distinct + sort |

### Detailed reference {#detailed-reference-compound-aggregations}

See [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}}) for detailed examples and implementation.

## Incremental SQL models

**Best for:** Stateful processing and advanced transformations

### When to use {#when-to-use-incremental-sql-models}

Use this approach only when entity vars cannot express your logic. For example:

- Tracking activity windows (MAU/DAU with rolling dates)
- Maintaining running balances or state
- Multi-step transformations with conditional logic
- Complex business rules requiring temporary tables

### How it works {#how-it-works-incremental-sql-models}

Reference previous model state and merge with new data:

```sql
-- Previous state
{{#with this.DeRef()}}
  SELECT * FROM {{this}}
{{/with}}

UNION ALL

-- New incremental data
SELECT * FROM new_data
```

### Use cases {#use-cases-incremental-sql-models}

| Use case | Description |
|----------|----------------------|
| Monthly Active Users (MAU) | Rolling 30-day window with date-based filtering |
| Wallet balances | Running balance with credits/debits |
| Multi-step ETL | Conditional logic with intermediate calculations |

### Detailed reference {#detailed-reference-incremental-sql-models}

See [Incremental SQL Models]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/" >}}) for detailed examples and implementation.

## Quick reference

| Pattern | Complexity | Use when |
|---------|-----------|-------------|
| [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}}) | ⭐ Easiest | Single aggregation function |
| [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}}) | ⭐⭐ Moderate | Multiple aggregations combined |
| [Incremental SQL Models]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/_index.md" >}}) | ⭐⭐⭐ Advanced | Stateful or complex logic |

{{< tip >}}
Start with simple aggregations first. If your use case doesn't fit, move to compound aggregations. Use incremental SQL models only when simple and compound aggregations cannot express your requirements.
{{< /tip >}}

## See more

- [How to Make Features Incremental]({{< ref "profiles/dev-docs/incremental-features/make-features-incremental.md" >}}): Feature-level migration guide
- [Entity Vars]({{< ref "profiles/dev-docs/profiles-yaml/var-groups/entity-var.md" >}})
- [Features]({{< ref "profiles/concepts/features.md" >}})
- [Checkpoints and Baselines]({{< ref "profiles/concepts/incremental-features/checkpoints-and-baselines.md" >}})