# How to Make Features Incremental


This guide walks you through converting features to incremental mode in Profiles.

## Prerequisites

Before making a feature incremental, make sure that:

- Your input sources are append-only and have reliable `occurred_at_col` timestamps
- The feature can be expressed using [supported incremental patterns]({{< ref "profiles/dev-docs/incremental-features/_index.md" >}})
- You understand how [Checkpoints and Baselines]({{< ref "profiles/concepts/incremental-features/checkpoints-and-baselines.md" >}}) work in Profiles

{{< warning >}}
**Not all features can be incremental.** 

Features that require scanning all historical data (such as median or percentile calculations) **cannot** use incremental computation. For such cases, consider using [Timegrains]({{< ref "profiles/concepts/timegrains.md" >}}) and [bundling]({{< ref "profiles/dev-docs/optimizations.md" >}}).
{{< /warning >}}

## Step 1: Configure input contracts

Before making features incremental, you must configure all input sources and SQL models with the `is_append_only` contract. This applies to inputs and SQL models (which can also be ephemeral).

### Verify append-only behavior

Before marking an input as append-only, verify that it truly only appends new rows and never updates or deletes existing ones:

1. **Check business semantics**: Event/fact tables (page views, purchases, clicks) are typically append-only. Dimension tables (user profiles, account settings) that update rows in place are **not** append-only.
2. **Run a warehouse query**: Check for duplicate primary keys that would indicate updates:

   ```sql
   SELECT entity_id_column, COUNT(*)
   FROM your_input_table
   GROUP BY entity_id_column
   HAVING COUNT(*) > 1;
   ```

{{< tip >}}
Inspect results to distinguish genuine duplicate events from row updates.
{{< /tip >}}

3. **Check downstream usage**: If a downstream model selects the latest attribute value (like current subscription status), the input likely receives updates and should **not** be marked append-only.

{{< warning >}}
**When in doubt, treat an input as not append-only.**

Incorrectly marking a mutable input as `is_append_only: true` leads to incorrect incremental results that are difficult to diagnose.
{{< /warning >}}

### Input example

Add the following to each verified append-only input in your `inputs.yaml`:

```yaml
inputs:
  - name: orders
    table: PROD.ANALYTICS.ORDERS
    contract:
      is_append_only: true
      is_event_stream: true
    occurred_at_col: order_timestamp
```

### SQL model example

You can also create append-only SQL models that filter or transform inputs. Add this to your `sql_models.yaml`:

```yaml
models:
  - name: purchase_events
    model_type: sql_template
    model_spec:
      occurred_at_col: timestamp
      materialization:
        output_type: table
      single_sql: |
        {% with events = this.DeRef("inputs/events") %}
          SELECT * FROM {{ events }}
          WHERE event_name = 'purchase'
        {% endwith %}
      contract:
        is_append_only: true
        is_event_stream: true
```

This SQL model filters events to only purchases and is marked as `is_append_only: true`, making it usable as an incremental source.

### Ephemeral SQL models

Ephemeral SQL models (`output_type: ephemeral`) that sit on top of append-only inputs can also be marked as `is_append_only: true`. This is useful for filtering or transforming inputs without materializing intermediate tables. 

Only mark an ephemeral model as append-only if:

- All upstream inputs are append-only
- The model only filters, renames, or transforms columns (no joins with mutable dimension tables)
- The output is itself append-only (no aggregations that change with updates)

{{< warning >}}
All inputs and SQL models used by incremental features must have `contract: is_append_only: true`. SQL models can also be ephemeral. Without this configuration, the project will throw an error when you try to run incremental features.
{{< /warning >}}

### Complete example: Simple incremental feature

The following example shows a complete setup for a simple incremental feature that tracks total purchase value:

**Input configuration** (`inputs.yaml`):

```yaml
inputs:
  - name: orders
    table: PROD.ANALYTICS.ORDERS
    contract:
      is_append_only: true
      is_event_stream: true
    occurred_at_col: order_timestamp
```

**Entity var** (`profiles.yaml`):

```yaml
- entity_var:
    name: total_purchases
    select: SUM({{orders.value}})
    merge: SUM({{rowset.total_purchases}})
    from: inputs/orders
    description: Total purchase value across all orders
```

This example shows:

- Input configured with `contract: is_append_only: true`
- Entity var using `SUM` aggregation with `merge: SUM({{rowset.total_purchases}})`
- Profiles automatically handles incremental computation

## Step 2: Identify the feature pattern

Determine which incremental pattern your feature matches:

- **Simple aggregations**: `MIN`, `MAX`, `SUM`, `COUNT` - See [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}})
- **Compound aggregations**: `AVG`, `MIN_BY`/`MAX_BY`, arrays, booleans - See [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}})
- **Window function replacements**: `LAST_VALUE` -> `MAX_BY`, `FIRST_VALUE` -> `MIN_BY` - See [Replacing window functions]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md#replacing-window-functions" >}})
- **Complex patterns**: Stateful processing, running totals, activity windows - See [Incremental SQL Models]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/_index.md" >}})

See [Incremental Features]({{< ref "profiles/dev-docs/incremental-features/_index.md" >}}) for a pattern selection guide and detailed examples.

## Step 3: Add the `merge` property

Add a `merge:` property to your `entity_var` definition in your `profiles.yaml` file. This property defines how to combine previous feature values with new data.

### Simple aggregators

For `MIN`, `MAX`, `SUM`, `COUNT`, and similar functions:

```yaml
- entity_var:
    name: first_event_time
    select: MIN({{events.timestamp}})
    merge: MIN({{rowset.first_event_time}})
    from: inputs/events
    description: Earliest timestamp of any event
```

### Average using component features

For `AVG`, break it into sum and count components. Define three separate `entity_var` entries at the same level in your `profiles.yaml`:

```yaml
- entity_var:
    name: sum_of_scores
    select: SUM({{game_events.score}})
    merge: SUM({{rowset.sum_of_scores}})
    from: inputs/game_events
    is_feature: false
    description: Sum of scores (used for average calculation)

- entity_var:
    name: count_of_scores
    select: COUNT({{game_events.score}})
    merge: SUM({{rowset.count_of_scores}})
    from: inputs/game_events
    is_feature: false
    description: Count of scores (used for average calculation)

- entity_var:
    name: avg_score
    select: "CASE WHEN {{user.count_of_scores}} > 0 THEN {{user.sum_of_scores}} / {{user.count_of_scores}} ELSE 0 END"
    description: Average score across all games
```

{{< info >}}
**Features referencing other features**

When a feature references another feature (like `{{user.sum_of_scores}}`), it doesn't need a `merge:` clause if it's just performing a calculation on incrementally maintained features. The referenced features (`sum_of_scores` and `count_of_scores`) maintain their incremental state, and the calculation (`avg_score`) is computed on-the-fly from those values.
{{< /info >}}

### Use incremental sources

You can also make features incremental by pointing to an incremental source (such as a [delta model]({{< ref "profiles/dev-docs/incremental-features/use-checkpoints.md#delta-models" >}}) or incremental input window). Even if the `entity_var` SQL runs a full aggregation, the scan volume stays small because the source is already delta-restricted:

```yaml
- entity_var:
    name: recent_purchases
    select: SUM({{recent_orders.value}})
    from: inputs/orders/incr_delta_tbl
    description: Purchases from recent data only
```

## Step 4: Define merge logic

The `merge:` clause must match the aggregation function in `select:`. Common patterns:

| Select function | Merge function |
|----------------|----------------|
| `MIN(...)` | `MIN({{rowset.feature_name}})` |
| `MAX(...)` | `MAX({{rowset.feature_name}})` |
| `SUM(...)` | `SUM({{rowset.feature_name}})` |
| `COUNT(...)` | `SUM({{rowset.feature_name}})` |
| `MAX_BY(val, ord)` | `MAX_BY({{rowset.feature}}, {{rowset.feature_by_param}})` + [helper var]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md#min_by--max_by-with-ordering-helper" >}}) |
| `MIN_BY(val, ord)` | `MIN_BY({{rowset.feature}}, {{rowset.feature_by_param}})` + [helper var]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md#min_by--max_by-with-ordering-helper" >}}) |
| `ARRAY_AGG(...)` | `ARRAY_UNION_AGG({{rowset.feature_name}})` |
| `BOOLOR_AGG(...)` | `BOOLOR_AGG({{rowset.feature_name}})` |

For complete `merge` syntax, see [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}}) and [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}}).

### Which `merge` function should I use?

The `merge` function must match your aggregation type. For `COUNT`, always use `SUM` in the `merge` property (summing counts, not counting counts). See the table above for the correct merge function for each aggregation type.

## Step 5: Use `merge_where` when needed

For complex merges, use `merge_where:` to restrict merging to only changed rows. Define separate `entity_var` entries at the same level in your `profiles.yaml`:

```yaml
- entity_var:
    name: timestamp
    select: MAX({{game_events.timestamp}})
    merge: MAX({{rowset.timestamp}})
    from: inputs/game_events
    where: {{game_events.event_type}} = "BREAK"
    is_feature: false
    description: Timestamp of the most recent break event (used for score lookup)

- entity_var:
    name: score_at_last_break
    select: any_value({{game_events.score}})
    where: {{game_events.timestamp}} = {{user.timestamp}}
    merge: any_value({{game_events.score}})
    merge_where: {{rowset.timestamp}} = {{user.timestamp}}
    from: inputs/game_events
    description: Score at the most recent break event
```

The `timestamp` entity var is marked with `is_feature: false` since it's an intermediate calculation used by `score_at_last_break`.

{{< tip >}}
Mark helper entity vars with `is_feature: false` when they're intermediate calculations used by other features. This prevents them from appearing as features in your output while still allowing them to be referenced by other entity vars.
{{< /tip >}}

## When merge is not needed

Not every entity var requires a `merge` clause to participate in an incremental project:

- **Derived-only vars** (no `from` clause) that reference other entity vars are recomputed from their incrementally maintained dependencies. Since they don't read from input sources directly, they don't need their own merge logic:

  ```yaml
  - entity_var:
      name: days_since_last_seen
      select: "DATEDIFF(day, {{user.last_seen}}, {{ end_time_sql }})"
      description: Days since the user was last seen
  ```

- **Vars from window-bounded incremental tables** that read from an [Incremental SQL Model]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/_index.md" >}}) maintaining a sliding window. The SQL model handles state incrementally, and the entity var recomputes from the bounded window each run:

  ```yaml
  - entity_var:
      name: active_days_in_past_7_days
      select: count(distinct active_day)
      from: models/recent_active_days
      where: active_day >= DATEADD(day, -7, {{ end_time_sql }})
      description: Days active in the last 7 days
  ```

## Step 6: Test your implementation

1. Run `pb run` to verify the feature computes correctly
2. Compare incremental results with a full refresh to ensure accuracy
3. Monitor run times to verify improvement

{{< warning >}}
The first run performs a full refresh to establish the baseline checkpoint. Subsequent runs use incremental computation.
{{< /warning >}}

### Verifying incremental behavior

To verify your feature is running incrementally:

- **Check run times**: Incremental runs should be faster than full refresh runs (after the first run)
- **Compare results**: Run with incremental mode and compare results with a full refresh to ensure they match
- **Monitor checkpoint usage**: Verify that checkpoints are being created and used in subsequent runs

## Troubleshooting

| Issue | Solution |
|-------|----------|
| Feature values are incorrect | <ul><li>Verify that your `merge:` clause correctly matches the aggregation logic</li><li>Ensure that your input sources are append-only or have reliable timestamps</li><li>Check that you are not missing edge cases in merge logic</li><li>Compare incremental results with a full refresh to identify discrepancies</li></ul> |
| Performance not improving | <ul><li>Verify that your input sources are properly configured for incremental processing</li><li>Check that delta models are being used correctly</li><li>Ensure your data warehouse supports efficient timestamp filtering</li><li>Monitor checkpoint creation and usage</li></ul> |
| Merge errors | <ul><li>Verify that all aggregators in `select:` have corresponding merge logic</li><li>Check that merge functions match the aggregation type</li><li>Ensure `merge_where:` conditions are correct if used</li><li>Review [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}}) and [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}}) for correct syntax</li></ul> |
| The first run is slow | This is expected. The first run always performs a full refresh to establish the baseline checkpoint. Subsequent runs use incremental computation and should be faster. |

## See more

- Explore [Incremental Features]({{< ref "profiles/dev-docs/incremental-features/_index.md" >}}) for pattern selection guide
- Learn about [Simple Aggregations]({{< ref "profiles/dev-docs/incremental-features/simple-aggregations.md" >}})
- Learn about [Compound Aggregations]({{< ref "profiles/dev-docs/incremental-features/compound-aggregations.md" >}})
- Learn about [Incremental SQL Models]({{< ref "profiles/dev-docs/incremental-features/incremental-sql-models/_index.md" >}})
- Learn about [Checkpoints and Baselines]({{< ref "profiles/concepts/incremental-features/checkpoints-and-baselines.md" >}})