business-driven-versioning

name

business-driven-versioning

description

Source prerequisite adapter for engines that cannot natively provide versioned reads or CDC. Derives Snapshot and Delta from business-level partition semantics instead. Use when the engine lacks time-travel, changelog tables, or CDC. CRITICAL PREREQUISITE: partitions already consumed by a previous REFRESH must be immutable — if historical partitions can be modified after consumption, this skill cannot be used. Must complete a structured interview with the user before activating.

Business-Driven Versioning

Purpose

incremental-computation requires four source prerequisites for each table: Snapshot, Delta, Version, and Schema. Some engines (e.g., plain Hive, flat object storage, simple JDBC sources) cannot provide versioned reads or row-level change streams natively.

This skill derives equivalent primitives from business-level partition semantics — the idea that partition structure, retention policy, and write patterns already encode what changed between two refreshes.

This skill is a source prerequisite adapter. It replaces iceberg-versioned-reads (or any other engine-native skill) when the engine cannot supply CDC or time-travel.

Fundamental assumptions: This skill derives Delta by computing the set difference between the current partition set and the previously consumed partition set. This is only equivalent to the true row-level delta if and only if:

1. Every partition consumed by a previous REFRESH is never modified afterward.
2. The partition column has a total order (e.g., DATE, integer sequence) and new partitions always arrive with values strictly greater than all previously consumed partitions — no partition can be inserted "between" two already-consumed ones (no backfill into past partition slots).

If either assumption is violated, the derived delta will be wrong and results will diverge from full recompute without any error.

When to Activate

Activate this skill only when all four conditions hold:

1. The user is requesting an incremental rewrite (CREATE or REFRESH), AND
2. The engine cannot provide at least one of: snapshot at a past version, delta/changelog, or a monotonic version identifier, AND
3. Consumed partitions are immutable: every partition processed by a previous REFRESH will never be modified, overwritten, or deleted afterward, AND
4. Partition column is totally ordered and monotonically increasing: the partition column has a comparable total order (e.g., DATE, integer), and new data always arrives in partitions with values strictly greater than all previously consumed ones — no backfill into already-consumed partition slots.

If condition 3 or 4 cannot be guaranteed, do not use this skill regardless of conditions 1 and 2. Fall back to engine CDC or full-refresh.md.

Do not activate speculatively. If the engine supports native versioning (Iceberg snapshots, Delta Lake history, Hudi timeline, Kafka offsets, etc.), use the engine-native skill instead.

Mandatory Interview

Before deriving any source prerequisite, the agent MUST complete the full interview below. Do not skip questions or assume answers. Incomplete answers about boundary conditions make the equivalence unsound, which silently corrupts incremental results.

Quick Disqualification Check

Before starting the interview, ask the user these two yes/no questions. A single "yes" disqualifies business-driven versioning for that source table.

#	Question	If YES
Q1	Can rows within an already-consumed partition be individually updated or deleted (not full-partition overwrite)?	Cannot use any business-driven pattern → engine CDC or full-refresh.md
Q2	Can a partition that was already consumed by a previous REFRESH be silently corrected (rows added/changed) without you knowing?	append-only-partition and sliding-window are invalid → fall back to engine CDC or full-refresh.md

If both answers are "no", proceed with the interview below.

Work through each section in order. If the user cannot answer a question, explain why it matters and ask them to clarify or make the equivalence assumption explicit.

---

Interview Checklist

Section A — Write Pattern

Ask the user to describe exactly how data arrives in the source table:

• A1. Partition column(s): What column(s) partition the table, and what do their values represent? (e.g., dt DATE, hour INT, region STRING)
  • Follow-up: Is this column totally ordered (i.e., any two values are comparable)? Can it be a categorical string like region or country? If so, this skill cannot apply — fall back to engine CDC or full-refresh.md.
  • Follow-up: Can a new partition ever arrive with a value less than or equal to the most recently consumed partition value (e.g., backfill into a past date slot)? If yes, that backfilled partition will be permanently missed — fall back to engine CDC or full-refresh.md.
• A2. Write mode per partition: For each partition, once written, can it be modified? Options:
  • Append-only: rows are only ever added to a partition, never deleted or updated.
  • Full overwrite: the entire partition is replaced atomically (INSERT OVERWRITE).
  • Partial update: rows within a partition can be individually updated or deleted.
• A3. Concurrent writes: Can multiple partitions be written simultaneously? Does a REFRESH need to handle mid-write partitions?

Section B — Partition Lifecycle

• B1. New partition trigger: What causes a new partition to appear? (e.g., scheduled batch job, event-time arrival, manual load)
• B2. Partition retention / expiry: Are old partitions ever dropped or archived? If yes, what is the retention rule (e.g., keep last 90 days)?
• B3. Late data / backfill: Can a partition that already existed be re-delivered with more rows? Under what conditions?

Section C — Window / Scope Definition (for sliding window queries)

Only required if the query has a time-scoped WHERE clause (e.g., WHERE dt >= today - 7):

• C1. Window size: How many partitions (or what time range) does the query always cover?
• C2. Window slide: How much does the window advance per REFRESH? (e.g., +1 day each day)
• C3. Window boundary stability: Are the boundaries always aligned to partition boundaries? (e.g., always full days, never partial days)
• C4. Edge case — window shrink: Can the window ever shrink or reset? (e.g., backfill scenario, reprocessing)

Section D — Row Identity

• D1. Primary key within a partition: What column(s) uniquely identify a row within one partition?
• D2. Primary key across partitions: Do the same key values (from D1) appear in multiple partitions?
  • No (globally unique): e.g., order_id is globally unique — row identity key = D1 columns only.
  • Yes (partition-scoped): e.g., a per-day auto-increment sale_id resets each day — row identity key = partition_col + D1 columns.

Derivation rule:

D1 uniqueness scope	Row identity key
Globally unique across all partitions	D1 columns (partition_col NOT needed)
Unique only within one partition	partition_col + D1 columns
Cannot determine	Ask the user; if still unclear → fall back to full-refresh.md

This matters because the row identity key is used as the state table PK and the DELETE key in REFRESH. If partition-scoped keys are used without including partition_col, two rows from different partitions sharing the same D1 value will collide — one will be silently overwritten or incorrectly deleted.

For sliding window queries in particular: even if D1 is globally unique, the same row's __weight contribution can change as it enters and leaves the window. The row identity key of the output (e.g., GROUP BY columns for aggregation) is separate from the source row identity key — derive both independently.

Section E — Soundness Gates

The agent MUST explicitly verify all of the following before declaring the equivalence valid:

Gate	Condition	If violated
E1 Atomicity	Each partition is written atomically (no partial partitions visible during REFRESH)	Must add a completeness check (see patterns/append-only-partition.md)
E2 Immutability	Partitions consumed in a previous REFRESH are never modified afterward	Equivalence is unsound for those partitions — fall back to engine CDC or full-refresh.md
E3 No partial-row updates	Write mode is either append-only or full-overwrite per partition, never partial row-level update	Partial updates within a partition cannot be expressed as partition-level delta without row comparison
E4 Deterministic boundary	The set of partitions that enter / leave the window each REFRESH is determinable from the profile alone, without querying the source system	If nondeterministic (e.g., partition appears at unpredictable times), add a discovery step (see patterns)
E5 No in-window overwrites	Partitions inside the current window are not overwritten after they enter the window	This assumption is violated → fall back to engine CDC or full-refresh.md

If any gate cannot be verified, state this explicitly to the user and recommend either fixing the upstream pipeline or falling back to full-refresh.md.

---

Pattern Library

After completing the interview, map the user's scenario to one or more patterns. Each pattern defines how to implement the four source prerequisites.

Pattern	When to Use
patterns/sliding-window.md	Query has a fixed-size time window (e.g., last N days); partitions enter and leave the window on each REFRESH
patterns/append-only-partition.md	No window; new partitions are only ever added; historical partitions are immutable
patterns/partition-expiry.md	Old partitions are dropped on a retention schedule; expiry must be treated as bulk delete

Patterns can be combined (e.g., append-only + partition-expiry).

Profile Extensions

This skill extends the standard incremental-computation profile with the following fields under a business_versioning key.

Single-source example (sliding-window)

{
  "business_versioning": {
    "pattern": "sliding-window",
    "partition_col": "dt",
    "last_window_start": "2024-01-01",
    "last_window_end":   "2024-01-07",
    "last_partition":    "2024-01-07",
    "window_size": 7,
    "window_slide": 1,
    "soundness_gates_verified": ["E1", "E2", "E3", "E4"],
    "assumptions": [
      "dt partitions are written atomically by the upstream batch job.",
      "dt partitions older than last_window_start are never modified after consumption.",
      "dt is a DATE column with a strict total order; no partition can be backfilled with a date <= last_partition."
    ]
  }
}

Single-source example (append-only-partition)

{
  "business_versioning": {
    "pattern": "append-only-partition",
    "partition_col": "dt",
    "last_partition": "2024-01-07",
    "soundness_gates_verified": ["E1", "E2", "E3", "E4"],
    "assumptions": [
      "dt partitions are written atomically and never modified after initial write.",
      "dt is a DATE column; new partitions always arrive with dt > last_partition (no backfill)."
    ]
  }
}

Multi-source example (two append-only partitioned tables)

When a query joins or aggregates across multiple partitioned sources, each source gets its own versioning entry under its table name:

{
  "business_versioning": {
    "orders": {
      "pattern": "append-only-partition",
      "partition_col": "order_dt",
      "last_partition": "2024-01-07",
      "soundness_gates_verified": ["E1", "E2", "E3", "E4"],
      "assumptions": [
        "order_dt partitions are written atomically and never modified.",
        "order_dt is a DATE column; new partitions always arrive with order_dt > last_partition."
      ]
    },
    "payments": {
      "pattern": "append-only-partition",
      "partition_col": "pay_dt",
      "last_partition": "2024-01-07",
      "soundness_gates_verified": ["E1", "E2", "E3", "E4"],
      "assumptions": [
        "pay_dt partitions are written atomically and never modified.",
        "pay_dt is a DATE column; new partitions always arrive with pay_dt > last_partition."
      ]
    }
  }
}

Field reference

Field	Required for	Description
pattern	all	Pattern name: sliding-window, append-only-partition, partition-expiry
partition_col	all	The partition column name
last_partition	all	Most recent partition value consumed by the last successful REFRESH
last_window_start	sliding-window	Inclusive lower bound of the last consumed window
last_window_end	sliding-window	Inclusive upper bound of the last consumed window
window_size	sliding-window	Number of partitions in the window
window_slide	sliding-window	How much the window advances per REFRESH
soundness_gates_verified	all	Gates explicitly confirmed with the user (E1–E5)
assumptions	all	Conditions assumed but not verified — must include immutability and monotonic-ordering statements

• last_window_start / last_window_end / window_size / window_slide are only relevant for sliding-window and must be omitted for other patterns.
• last_partition replaces source_versions[table] for business-driven sources; set source_versions[table] = "business-driven" as a sentinel.
• The assumptions array must always include an explicit statement that (a) consumed partitions are immutable and (b) the partition column is totally ordered with no backfill possible.

Integration with incremental-computation

When incremental-computation asks for source prerequisites, this skill answers:

Prerequisite	This Skill's Answer
Version	The last_window_end (or last_partition) value stored in the profile
Schema	Query the table's metadata directly (e.g., DESCRIBE TABLE, SHOW COLUMNS)
Snapshot(V)	SELECT * FROM table WHERE <partition_col> IN (<partitions in window at version V>)
Delta(V1, V2)	Derived from partition set difference — see the applicable pattern for exact logic

The __weight mapping follows the same convention as all other source skills: +1 for entering rows, -1 for leaving rows.

Limitations

This skill makes correctness guarantees only under the verified soundness gates. If upstream data pipelines violate any gate after the fact (e.g., a historical partition is silently corrected), the incremental result will diverge from full recompute without any error. The agent should communicate this risk clearly to the user and recommend periodic full-recompute verification.