Git-like branching for analytical databases. Attach isolated what-if scenarios as catalogs, edit them with ordinary SQL on copy-on-write delta storage, branch, diff, and merge them back.
Installing and Loading
INSTALL anofox_scenario FROM community;
LOAD anofox_scenario;
Example
-- History as recorded
CREATE TABLE sales_plan (region VARCHAR PRIMARY KEY, units INTEGER);
INSERT INTO sales_plan VALUES ('EMEA', 1000), ('AMER', 2000), ('APAC', 500);
-- Branch a what-if world: two statements, then it's just SQL
CALL scenario_create('aggressive_q4', 'push APAC hard');
ATTACH 'aggressive_q4' AS q4 (TYPE scenario);
UPDATE q4.sales_plan SET units = 1500 WHERE region = 'APAC';
DELETE FROM q4.sales_plan WHERE region = 'EMEA';
-- Both worlds coexist; the base is never written
SELECT 'base' AS world, * FROM sales_plan
UNION ALL
SELECT 'scenario', * FROM q4.sales_plan ORDER BY world, region;
-- Audit the changes, then promote them
SELECT * FROM scenario_diff('aggressive_q4', 'sales_plan');
SELECT * FROM scenario_merge('aggressive_q4');
About anofox_scenario
anofox_scenario brings git-like branching to DuckDB databases. A scenario is
an attached catalog (ATTACH 'name' AS s (TYPE scenario)) over your existing
tables: reads merge the base with the scenario's copy-on-write delta on the
fly, and INSERT / UPDATE / DELETE / MERGE INTO / ON CONFLICT (all with
RETURNING) land in the delta — the base tables are never modified.
Three isolation tiers: a live overlay (default), mode := 'materialized'
point-in-time copies, and DuckLake bases pinned to creation time via
AT (TIMESTAMP) for zero-copy snapshot isolation. Scenarios branch from each
other (from_scenario :=), inheriting changes, declared row identities, and
snapshot pins.
A streaming diff engine (scenario_diff, scenario_diff_summary) audits any
world against its origin or another world, and
scenario_merge(name, on_conflict := 'abort'|'ours'|'theirs') promotes a
scenario into its base atomically — with true 3-way drift conflicts when a
creation snapshot exists. Views rebind inside scenarios, all base schemas
are mirrored, and tables without a primary key work either via
key_columns := declared identity or bag semantics with multiplicity
tracking.
Scenario worlds compose with table-name-driven extensions: for example
ts_forecast_by('s.demand', ...) (anofox_forecast) forecasts a what-if
world directly, no copies needed.
The extension collects anonymous usage telemetry (an envelope-only
extension_loaded event; never table names, keys, or SQL). Disable with
SET anofox_scenario_telemetry_enabled = false or
DATAZOO_DISABLE_TELEMETRY=1; see TELEMETRY.md in the repository.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| scenario_create | table | Register a scenario. 'materialized' mode copies every base table, the default 'delta' mode stores only what changed; from_scenario branches off an existing scenario; base uses another attached catalog; key_columns declares row identity for tables without a primary key. | NULL | [CALL scenario_create('optimistic');] |
| scenario_create | table | Register a scenario. 'materialized' mode copies every base table, the default 'delta' mode stores only what changed; from_scenario branches off an existing scenario; base uses another attached catalog; key_columns declares row identity for tables without a primary key. | NULL | [CALL scenario_create('price_increase', 'Analyzing 10% price increase impact');] |
| scenario_diff | table | Diff a scenario against its origin, returning the primary key columns plus change_type ('added', 'removed' or 'modified'), column_name, old_value and new_value. | NULL | [SELECT * FROM scenario_diff('price_increase', 'products');] |
| scenario_diff | table | Diff any two sides against each other – 'main' or any scenario name – where old_value comes from side a and new_value from side b. | NULL | [SELECT * FROM scenario_diff('main', 'price_increase', 'products');] |
| scenario_diff_summary | table | Summarise a scenario's changes per table as rows_added, rows_modified and rows_removed. | NULL | [SELECT * FROM scenario_diff_summary('price_increase');] |
| scenario_drop | table | Remove a scenario and its delta or materialized tables. Refuses while the scenario is attached or while branches of it still exist. | NULL | [CALL scenario_drop('price_increase_eu');] |
| scenario_freeze | table | Reject writes to a scenario while leaving reads working; a frozen materialized scenario is a snapshot. | NULL | [CALL scenario_freeze('q2_approved');] |
| scenario_list | table | List every registered scenario as (scenario_id, name, mode, frozen, parent, created_at, description). | NULL | [SELECT * FROM scenario_list();] |
| scenario_merge | table | Apply a scenario's changes back to its base tables. | NULL | [SELECT * FROM scenario_merge('price_increase', on_conflict := 'abort');] |
| scenario_merge_preview | table | Show the actions a merge would take as (table_name, key, action, conflict). Streaming, with no side effects. | NULL | [SELECT * FROM scenario_merge_preview('price_increase');] |
| scenario_migrate | table | Migrate a legacy v0.1 scenario database into the v2 layout. One-way. | NULL | [SELECT * FROM scenario_migrate();] |
| scenario_refresh | table | Create delta tables for base tables that were added after the scenario itself was created. | NULL | [SELECT * FROM scenario_refresh('price_increase');] |
| scenario_unfreeze | table | Allow writes to a scenario again, reversing scenario_freeze. | NULL | [CALL scenario_unfreeze('q2_approved');] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases |
|---|---|---|---|---|
| anofox_scenario_telemetry_enabled | Enable or disable anonymous usage telemetry for anofox_scenario | BOOLEAN | GLOBAL | [] |
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] |