incremental-computation

name	incremental-computation
description	Master skill for incremental computation / materialized view maintenance. Triggers on: incremental computation, incremental ETL, materialized view, delta processing, incremental refresh. Routes to algorithm skills.

Incremental Computation

Core Model

Incremental computation maintains a derived table (materialized view) by processing only the changes since the last refresh, instead of recomputing from scratch.

Two primitives:

• Snapshot(V) — all rows at version V. No __weight column. Used for initial load and recomputation.
• Delta(V1, V2) — changes between V1 and V2. Each row carries __weight: +1 = insert, -1 = delete. UPDATE = two rows (-1 old, +1 new).

Source Prerequisites

This skill pack does not assume any specific storage format or query engine. Before the first CREATE or REFRESH, the agent must confirm with the user how to obtain the following for each source table:

• Snapshot — how to read all rows at a given version (e.g., time-travel query, version-qualified SELECT, or simply reading the current state).
• Schema — how to get the column names and types of the source table.
• Delta — how to get the row-level changes between two versions, with each row carrying a weight (+1 for insert, -1 for delete). This could come from CDC, changelog tables, incremental queries, or any other mechanism.
• Version — what serves as the version identifier (snapshot ID, commit timestamp, monotonic integer, etc.) and how to query the current version.

If the agent knows the user's storage format and query engine, it can infer these from the engine's capabilities. If not, ask.

Profile

Every incrementally maintained table has a profile that stores source_versions, the original SQL, strategy, state table references, and row identity key. Before the first CREATE or REFRESH, confirm with the user where profiles are stored.

See references/profile-schema.md for the full schema and storage options.

Row Identity Key

Before choosing a strategy, derive the row identity key for this layer's output — the columns that uniquely identify one logical row. This is the prerequisite for incremental maintenance.

Every operator's REFRESH follows the same pattern: DELETE affected rows from target by row identity key, then INSERT new rows. Without a row identity key, the layer cannot identify which rows to delete, cannot define a state table PK, and cannot propagate a meaningful delta to upper layers.

No row identity key derivable → route to full-refresh.

⚠️ Assumed Uniqueness

Most data lake tables (Iceberg, Hive, etc.) have no declared PRIMARY KEY. When the agent identifies a row identity key based on column semantics (e.g., order_id looks like a PK), it MUST:

1. Explicitly state the assumption in the generated output (e.g., "Assumed unique: order_id")
2. Generate a verification query for the user to confirm (SELECT count(*) vs count(distinct ...))
3. Warn that if duplicates exist, incremental results will be incorrect

This is a correctness prerequisite, not optional. See references/row-identity-key.md for the full warning protocol.

Strategy Routing

After confirming source prerequisites and a row identity key, analyze the SQL query to select the right algorithm.

See references/strategy-routing.md for the full decision tree. Summary:

Pattern	Reference
WHERE / SELECT projection	references/filter.md
SUM / COUNT / AVG + GROUP BY	references/retractable-agg.md
MIN / MAX / GROUP_CONCAT	references/non-retractable-agg.md
AGG(DISTINCT col)	references/agg-distinct.md
Any JOIN	references/join.md
UNION ALL / UNION	references/union.md
ROW_NUMBER / RANK / LEAD / LAG OVER(...)	references/window.md
Everything else	references/full-refresh.md (last resort)

Workflow

CREATE (Initial Load)

1. Obtain snapshot of each source table at current version
2. Decompose query bottom-up into layers (see Bottom-Up Decomposition below)
3. For each layer: follow the corresponding algorithm reference, compute full result, populate target table
4. Record source_versions in profile (map of source → version consumed)

REFRESH (Incremental Update)

1. Check each source's current version against profile's source_versions
2. If all versions match → no-op, skip refresh
3. For each changed source: fetch delta(from_version, to_version)
4. Check delete_count in delta info:
   • If delete_count == 0 → positiveOnly path (skip __weight weighting, skip DELETE)
   • If delete_count > 0 → full incremental path
5. Follow the algorithm reference for this layer, generate incremental SQL
6. Execute incremental SQL
7. Update source_versions in profile

Multi-Layer REFRESH

Refresh layers bottom-up in dependency order:

• Layer N refreshes first → its version advances
• Layer N+1's to_version = Layer N's new version
• Layer N+1's from_version = its own profile's source_versions (NOT Layer N's profile)

Bottom-Up Decomposition — MANDATORY

Complexity is an argument FOR decomposition, never against it.

Decompose bottom-up (leaves → final output). At each layer boundary:

1. Create an intermediate table
2. Follow the corresponding algorithm reference for that layer's operator
3. Apply the algorithm's strategy FOR THAT LAYER ONLY

Each layer = one intermediate table. Delta propagates through the pipeline, shrinking at each stage. A 3-table JOIN with aggregation becomes:

Layer 1: join (A ⋈ B) → intermediate_ab
Layer 2: join (intermediate_ab ⋈ C) → intermediate_abc
Layer 3: retractable-agg (SUM ... GROUP BY) → final_result

Fallback

When incremental maintenance is not feasible or confidence is low, see references/fallback-guide.md for guidance on when to recommend managed solutions.

Common Mistakes

See references/common-mistakes.md — covers DELETE-before-read, missing __weight weighting, missing delta dedup, dead group cleanup, and multi-layer version propagation errors.