Datakey caches

A datakey cache lets your own orchestrator precompute expensive features into a warehouse table, register that table with FeatureMesh, and have later queries read the stored values instead of recomputing them.

This tutorial builds one end to end, on a small daily-partitioned model:

• customers: a dimension. Each ds partition holds every customer, with their data as of that day.
• orders: a fact. Each ds partition holds the orders of that day.
• cache_per_country: the cache. Each ds partition holds the revenue and order count per country for that day.

You will build one partition with a hybrid query , register it, check that queries read it, see that another date still computes, and rebuild a partition after its source changed.

Who does what

FeatureMesh does not schedule or build anything. The work splits in two:

A datakey identifies what a value means: the feature's logic, the key it is computed for, and the populations it reads, with their parameters. It does not record when the source tables were read. So the partition date must be part of the query, not left to whenever the build happens to run. This tutorial does that with one CONST, named DS.

The contract

These rules say which queries share a datakey, and what a registered cache is allowed to stand in for. The spelling below is what EXPLAIN (FIELDS (DATA_KEYS)) shows in keyset_bindings[].params and const_values.

The type is part of the value. A keyset parameter and a CONST enter the datakey as the SQL literal of their declared type.

2 and '2' are two datakeys. An array or a row parameter is a literal, on the same path as a scalar.

EVAL_CONST() is the value it returns. EVAL_CONST(DS AS DATE) with DS := DATE '2026-10-07' contributes the same literal as writing that date, so the two share a datakey. A source that still names the constant, as in ON SELF[ds] = EVAL_CONST(DS AS DATE), keeps that typed literal in const_values. EVAL_CONST(CURRENT_TIMESTAMP()) contributes the timestamp captured for this compilation. A later compilation has another datakey.

The cache column is read as the feature's type. FeatureMesh casts the column to the type declared on the feature. The warehouse type of that column is an assumption: validate states it, with a check you run on the warehouse, and the query still compiles. A DECIMAL column read as BIGINT is cast, so 1.5 becomes 2. A cast that fails fails the query. The same kind of assumption on a mapped table is Data contracts .

A cache leaves the query as it is. A query that fails to compile still fails when a cache is registered. A VARIANT that replaces an external source still fails. A VARIANT or a MACRO is cached from the formula it expands to, so the stored value is that result. Each FEATUREQL(...) island in one SQL statement has its own datakeys: two islands can read the same source, and both stay cacheable.

A registered row is the declaration you wrote. FeatureMesh serves that row as registered. Freshness, completeness and uniqueness of the key are yours to keep. A missing key reads as NULL. A duplicate key repeats rows. Step 6 shows a stale partition and the rebuild.

1. The data and the features

Two source tables and an empty cache table, all partitioned by ds:

CREATE SCHEMA IF NOT EXISTS docs_cache;
--
DROP TABLE IF EXISTS docs_cache.customers;
--
CREATE TABLE docs_cache.customers AS SELECT * FROM (VALUES
    (DATE '2026-10-07', 1, 'Ann', 'FR'),
    (DATE '2026-10-07', 2, 'Bob', 'FR'),
    (DATE '2026-10-07', 3, 'Cyd', 'ES'),
    (DATE '2026-10-07', 4, 'Dee', 'IT')
) AS t(ds, customer_id, customer_name, country);
--
DROP TABLE IF EXISTS docs_cache.orders;
--
CREATE TABLE docs_cache.orders AS SELECT * FROM (VALUES
    (DATE '2026-10-07', 101, 1, 20),
    (DATE '2026-10-07', 102, 2, 15),
    (DATE '2026-10-07', 103, 3, 30)
) AS t(ds, order_id, customer_id, amount);
--
DROP TABLE IF EXISTS docs_cache.cache_per_country;
--
CREATE TABLE docs_cache.cache_per_country (ds DATE, country VARCHAR, country_revenue BIGINT, country_orders BIGINT);
--
SELECT CAST(COUNT() AS INTEGER) AS Count FROM docs_cache.orders;

One CONST, DS, selects the partition everywhere:

• Each source keeps only the rows of one partition, with ON SELF[ds] = EVAL_CONST(DS AS DATE). EVAL_CONST() reads the DS of whoever queries the feature. The CONST in CREATE FEATURES only checks that the formula resolves.
• KS_COUNTRIES is the population of countries present in the customers partition of a date, given as the :ds parameter.
• KS_ORDERS is the population of orders in the orders partition of a date.

ORDER_COUNTRY joins each order to the country of its customer with RELATED(). COUNTRY_REVENUE and COUNTRY_ORDERS then aggregate the orders per country:

CREATE OR REPLACE FEATURES IN FM.DOCS.CACHE AS
CONST
    DS := DATE '2026-10-07'
PERSIST
    CUSTOMERS := ENTITY(),
    ORDERS := ENTITY(),
    COUNTRIES := ENTITY(),
    CUSTOMER_ID := INPUT(BIGINT#CUSTOMERS),
    ORDER_ID := INPUT(BIGINT#ORDERS),
    COUNTRY := INPUT(VARCHAR#COUNTRIES),
    KS_COUNTRIES := KEYSET(COUNTRIES, 'SELECT DISTINCT country FROM docs_cache.customers WHERE ds = :ds'),
    KS_ORDERS := KEYSET(ORDERS, 'SELECT order_id FROM docs_cache.orders WHERE ds = :ds'),
    TABLES.CUSTOMERS := EXTERNAL_COLUMNS(
        customer_id BIGINT#CUSTOMERS BIND TO CUSTOMER_ID,
        ds DATE,
        customer_name VARCHAR,
        country VARCHAR#COUNTRIES
        FROM TABLE(docs_cache.customers)
        ON SELF[ds] = EVAL_CONST(DS AS DATE)
    ),
    TABLES.ORDERS := EXTERNAL_COLUMNS(
        order_id BIGINT#ORDERS BIND TO ORDER_ID,
        ds DATE,
        customer_id BIGINT#CUSTOMERS,
        amount BIGINT
        FROM TABLE(docs_cache.orders)
        ON SELF[ds] = EVAL_CONST(DS AS DATE)
    ),
    CUSTOMER_COUNTRY := TABLES.CUSTOMERS[country],
    ORDER_AMOUNT := TABLES.ORDERS[amount],
    ORDER_COUNTRY := TABLES.ORDERS[customer_id].RELATED(CUSTOMER_COUNTRY),
    COUNTRY_REVENUE := COUNTRY.RELATED(SUM(ORDER_AMOUNT) GROUP BY ORDER_COUNTRY),
    COUNTRY_ORDERS := COUNTRY.RELATED(COUNT() GROUP BY ORDER_COUNTRY),
;

The query the cache will serve fixes the partition date once, in CONST. Both keysets and both sources read it:

CONST DS := DATE '2026-10-07'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
;

Every value is computed from the source tables. Italy is in the result with no revenue, because the population is the countries of the customers partition, not the countries that have orders.

2. Build a partition with a hybrid query

The orchestrator fills one partition with the same FeatureQL query, wrapped in FEATUREQL(...) inside an INSERT. PARTITION_DATE := @eval_as_literal(DS) returns the CONST as the first column, so the date is written in one place only:

/* SQL */
INSERT INTO docs_cache.cache_per_country
SELECT * FROM FEATUREQL(
    /* NO-CACHE */
    CONST
        DS := DATE '2026-10-07'
    RETURN
        PARTITION_DATE := @eval_as_literal(DS),
        COUNTRY,
        COUNTRY_REVENUE,
        COUNTRY_ORDERS
    FROM FM.DOCS.CACHE
    FOR
        COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
        NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
);
--
/* SQL */
SELECT * FROM docs_cache.cache_per_country ORDER BY ds, country;

Four things make a cache table correct:

• One row per key of the keyset. The keyset is KS_COUNTRIES, so Italy is there, with no orders. A key missing from the table reads as NULL.
• The backend that will read it. Build the table on the warehouse your queries run on. A DuckDB cache serves DuckDB queries only.
• Same table, same key, same partition for the features you want to read together. They then cost a single join.
• /* NO-CACHE */ inside the island. A build must compute from the source tables. Without the tag, an island reads a registered cache like any other query. Step 6 shows what that does to a rebuild. The tag must be inside FEATUREQL(...): at the top of the SQL statement it has no effect.

The island's columns are named after the features ("FM.DOCS.CACHE.COUNTRY_REVENUE"). INSERT … SELECT * into a table you created maps them by position. If you create the table from the query instead, alias them to plain names with SELECT "FM.DOCS.CACHE.COUNTRY" AS country, ….

3. Read the datakeys from the build

The response of the INSERT carries the datakey of every column the island returned. For a hybrid query, they are under featureql_queries, one entry per island, in query_outputs_data_keys:

client translate
{"query": "/* SQL */ INSERT INTO docs_cache.cache_per_country SELECT * FROM FEATUREQL(/* NO-CACHE */ CONST DS := DATE '2026-10-07' RETURN PARTITION_DATE := @eval_as_literal(DS), COUNTRY, COUNTRY_REVENUE, COUNTRY_ORDERS FROM FM.DOCS.CACHE FOR COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)), NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS)));"}

Each entry has name, data_key, reason, key_input and cache_ids (the sorted list of cache rows read for that column; empty when none were). PARTITION_DATE has no datakey: it is a constant, and reason says so.

So the job that builds a partition already holds the datakeys to register. It needs no second query. For a plain FeatureQL query (no /* SQL */), the same list is at the top level of the response metadata, as query_outputs_data_keys.

To see what a datakey depends on, ask for EXPLAIN (FIELDS (DATA_KEYS)) on the query:

EXPLAIN (FIELDS (DATA_KEYS))
CONST
    DS := DATE '2026-10-07'
RETURN
    COUNTRY,
    COUNTRY_REVENUE,
    COUNTRY_ORDERS
FROM FM.DOCS.CACHE
FOR
    COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
    NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
;

A datakey is built from:

• the feature's semantic hash, so a change of logic gives a new datakey;
• the key input (COUNTRY), which becomes the key column of the cache table;
• every keyset binding the value reads, including the NESTED order population, with its parameters as the typed literals in the contract above;
• the CONST values the value reads, in that same spelling. An EVAL_CONST() that has already been evaluated matches a written literal of that value.

Here the partition date reaches the datakey through both keyset parameters, and through the DS that the two sources read. Each date has its own datakey.

A value can be cached only when FeatureMesh can say exactly which population it was computed for. When it cannot, data_key is null and reason says why:

4. Register the partition

CREATE CACHES declares which column holds which datakey. One parenthesized row per feature, as many rows as you like per statement:

CREATE CACHES
    (DATAKEY = 'dk1:c8986232b759412405ee4e883d35e7428a74abcf0d185a26a7b4d600e057edc7', BACKEND = 'duckdb',
     TABLE_NAME = 'docs_cache.cache_per_country', KEY_COLUMN = 'country', VALUE_COLUMN = 'country_revenue',
     FILTER_COLUMN = 'ds', FILTER_VALUE = DATE '2026-10-07'),
    (DATAKEY = 'dk1:b18c634af48844b33ecdfc36d38254094b08a5a19c87c954e725d21c51214581', BACKEND = 'duckdb',
     TABLE_NAME = 'docs_cache.cache_per_country', KEY_COLUMN = 'country', VALUE_COLUMN = 'country_orders',
     FILTER_COLUMN = 'ds', FILTER_VALUE = DATE '2026-10-07')
;

KEY_COLUMN, VALUE_COLUMN and FILTER_COLUMN may be SQL keywords, such as select.

The datakey says which value the column holds. The filter says where in the table it is. Use the same date for both.

The returned ID is a hash of the row's content. Registering the same row again returns EXISTS, so the orchestrator can retry a run safely.

5. One date is cached, another is not

Run the original query under EXPLAIN (FIELDS (CACHE_PLAN)):

EXPLAIN (FIELDS (CACHE_PLAN))
CONST
    DS := DATE '2026-10-07'
RETURN
    COUNTRY,
    COUNTRY_REVENUE,
    COUNTRY_ORDERS
FROM FM.DOCS.CACHE
FOR
    COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
    NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
;

The plan counts work units: each source table and each RELATED() scope. A cache adds one join per group of rows sharing a table, key column and partition. FeatureMesh uses a group only when it removes more work units than that join costs. Here one join replaces five work units: neither orders nor customers is read any more.

Each candidate row gets a decision:

The next day, the 2026-10-08 partitions land. Bob moved from France to Spain, and two new orders came in:

INSERT INTO docs_cache.customers VALUES
    (DATE '2026-10-08', 1, 'Ann', 'FR'),
    (DATE '2026-10-08', 2, 'Bob', 'ES'),
    (DATE '2026-10-08', 3, 'Cyd', 'ES'),
    (DATE '2026-10-08', 4, 'Dee', 'IT');
--
INSERT INTO docs_cache.orders VALUES
    (DATE '2026-10-08', 104, 2, 40),
    (DATE '2026-10-08', 105, 4, 10);
--
SELECT CAST(COUNT() AS INTEGER) AS Count FROM docs_cache.orders;

The 2026-10-07 query still reads its cache, and the answer is still right. The new orders are in another partition, and on 2026-10-07 Bob was in France:

CONST DS := DATE '2026-10-07'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
;

A query for 2026-10-08 reads other populations and other partitions, so it has other datakeys. No row is registered for them:

EXPLAIN (FIELDS (CACHE_PLAN))
CONST
    DS := DATE '2026-10-08'
RETURN
    COUNTRY,
    COUNTRY_REVENUE,
    COUNTRY_ORDERS
FROM FM.DOCS.CACHE
FOR
    COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
    NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
;

So it computes from the source tables. Bob's order now counts for Spain:

CONST DS := DATE '2026-10-08'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
;

Nothing ever serves a value computed for another date. To cache 2026-10-08 too, repeat steps 2 to 4 with DS := DATE '2026-10-08'.

6. Recompute a cached date from the source

A cache row is the declaration from the contract: the value column is cast to the feature's type, and the table is served as registered. Suppose a late order lands in the 2026-10-07 partition after its cache was built:

INSERT INTO docs_cache.orders VALUES (DATE '2026-10-07', 106, 1, 5);
--
SELECT CAST(COUNT() AS INTEGER) AS Count FROM docs_cache.orders WHERE ds = DATE '2026-10-07';

The 2026-10-07 query still reads the cache, so France still shows the old numbers:

CONST DS := DATE '2026-10-07'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
;

The /* NO-CACHE */ tag forces the computation from the source tables. Use it to check a cached partition against its source:

CONST DS := DATE '2026-10-07'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
    -- unrecognized comments
    /* NO-CACHE */
;

The plan shows that the registered rows were not even looked up:

EXPLAIN (FIELDS (CACHE_PLAN))
/* NO-CACHE */
CONST
    DS := DATE '2026-10-07'
RETURN
    COUNTRY,
    COUNTRY_REVENUE,
    COUNTRY_ORDERS
FROM FM.DOCS.CACHE
FOR
    COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
    NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
;

The two answers differ, so the partition must be rebuilt: delete it, then insert it again. This is where /* NO-CACHE */ inside the island matters. The partition is registered, so without the tag the island would read the cache table it is filling. After the DELETE that table is empty for this date, and the rebuild would write NULL in every row.

/* SQL */
DROP TABLE IF EXISTS docs_cache.cache_per_country_fm_keep;
--
/* SQL */
CREATE TABLE docs_cache.cache_per_country_fm_keep AS SELECT * FROM docs_cache.cache_per_country WHERE NOT (ds = DATE '2026-10-07');
--
/* SQL */
DROP TABLE docs_cache.cache_per_country;
--
/* SQL */
CREATE TABLE docs_cache.cache_per_country AS SELECT * FROM docs_cache.cache_per_country_fm_keep;
--
/* SQL */
DROP TABLE docs_cache.cache_per_country_fm_keep;
--
/* SQL */
INSERT INTO docs_cache.cache_per_country
SELECT * FROM FEATUREQL(
    /* NO-CACHE */
    CONST
        DS := DATE '2026-10-07'
    RETURN
        PARTITION_DATE := @eval_as_literal(DS),
        COUNTRY,
        COUNTRY_REVENUE,
        COUNTRY_ORDERS
    FROM FM.DOCS.CACHE
    FOR
        COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
        NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
);
--
/* SQL */
SELECT * FROM docs_cache.cache_per_country ORDER BY ds, country;

Nothing changed in the logic, the date or the populations, so the datakeys are the same and the rows are already registered:

CREATE CACHES
    (DATAKEY = 'dk1:c8986232b759412405ee4e883d35e7428a74abcf0d185a26a7b4d600e057edc7', BACKEND = 'duckdb',
     TABLE_NAME = 'docs_cache.cache_per_country', KEY_COLUMN = 'country', VALUE_COLUMN = 'country_revenue',
     FILTER_COLUMN = 'ds', FILTER_VALUE = DATE '2026-10-07'),
    (DATAKEY = 'dk1:b18c634af48844b33ecdfc36d38254094b08a5a19c87c954e725d21c51214581', BACKEND = 'duckdb',
     TABLE_NAME = 'docs_cache.cache_per_country', KEY_COLUMN = 'country', VALUE_COLUMN = 'country_orders',
     FILTER_COLUMN = 'ds', FILTER_VALUE = DATE '2026-10-07')
;

The query reads the rebuilt partition:

CONST DS := DATE '2026-10-07'
RETURN
    country,
    country_revenue,
    country_orders
FROM FM.DOCS.CACHE
FOR
    country := @BIND_KEYSET(ks_countries, ROW(ds AS ds)),
    NESTED order_id := @BIND_KEYSET(ks_orders, ROW(ds AS ds))
ORDER BY country
;

7. List and drop

SHOW CACHES lists the registered rows:

SHOW CACHES (COLUMNS (VALUE_COLUMN, FILTER_VALUE))
WHERE TABLE_NAME = 'docs_cache.cache_per_country'
ORDER BY FILTER_VALUE, VALUE_COLUMN
;

Old partitions are dropped for retention, not correctness. Drop them once nobody queries those dates:

DROP CACHES 'c1:f4ce3b1db3a0e0c5', 'c1:d502ace5fbd04132';

Understand what the system does

When a query does not read the cache you expect, check these in order:

1. Is the value cacheable? EXPLAIN (FIELDS (DATA_KEYS)) on the query. A null data_key comes with a reason (step 3).
2. Does a row match? The datakey of the query must equal the registered DATAKEY exactly. Another date, another keyset, other keyset parameters or another NESTED population each give another datakey. Compare the datakey with SHOW CACHES (COLUMNS (DATAKEY, BACKEND)).
3. Is it the right backend? Only rows for the backend the query runs on are candidates.
4. What did the planner decide? EXPLAIN (FIELDS (CACHE_PLAN)) lists every candidate with its decision and saving. With no candidate, skipped_reason says why: NO-CACHE-TAG, BACKEND-WITHOUT-CACHES, NOTHING-CACHEABLE or NO-REGISTERED-ROW.
5. What did the query return? cache_ids in the output metadata lists every cache row read for that column. An empty list means the column was computed.

CREATE FEATURES, CREATE VIEW (a persisted view stores computed SQL) and prepared statements never read a cache. A FEATUREQL(...) island does, unless it carries /* NO-CACHE */.

Advice

• Put the partition date in the query. Pass one CONST to the keyset parameters and to the ON clause of the sources, so the datakey changes with it. A cache built without it is a snapshot that FeatureMesh will serve for every later query, because it trusts the registration.
• Read dimensions at the partition date, not their latest state. A lookup on the latest partition of a dimension gives a different answer once the dimension is updated, under the same datakey.
• Always build with /* NO-CACHE */ inside the island. A build that reads its own cache copies old values, or NULL after a DELETE.
• Rebuild a partition when its source changes. The datakey covers the populations and the dates, not the rows in the source tables. If late orders land in a partition already built, rebuild it. Its rows keep the same datakeys and the same ids.
• Cache aggregates and joins, not plain columns. A column read from a source table costs one join either way, so it is never served from a cache (NO-SAVING).
• Group features by key and partition. Everything keyed by country for the same ds belongs in one table: one join serves all of it.
• Keep the build and the registration in one job. Register only after the INSERT succeeded.
• Write one row per key. The cache table declares the grain of its key. A duplicate key fans out. See the mental model , section 12.
• Register one row per datakey. A second table or partition registered for the same datakey by mistake is a candidate too, and only one of them is read.

Annex: a script that rebuilds the cache for some dates

This script repeats steps 2 to 4 for each date it is given. The FeatureQL is exactly what you ran above, with the date filled in. Run it for a new date, or again for a date whose source changed.

from datetime import date
from featuremesh import BatchClient

client = BatchClient()

TABLE = "docs_cache.cache_per_country"
KEY_COLUMN = "country"
# Feature returned by the island -> column of the cache table that stores it.
VALUE_COLUMNS = {
    "FM.DOCS.CACHE.COUNTRY_REVENUE": "country_revenue",
    "FM.DOCS.CACHE.COUNTRY_ORDERS": "country_orders",
}

def island(ds: date) -> str:
    # NO-CACHE: a build always computes from the source tables.
    return f"""
    /* NO-CACHE */
    CONST
        DS := DATE '{ds}'
    RETURN
        PARTITION_DATE := @eval_as_literal(DS),
        COUNTRY,
        COUNTRY_REVENUE,
        COUNTRY_ORDERS
    FROM FM.DOCS.CACHE
    FOR
        COUNTRY := @BIND_KEYSET(KS_COUNTRIES, ROW(ds := DS)),
        NESTED ORDER_ID := @BIND_KEYSET(KS_ORDERS, ROW(ds := DS))
    """

def rebuild(ds: date) -> None:
    # 1. Empty the partition, so that a re-run does not duplicate keys.
    client.query(f"/* SQL */ DELETE FROM {TABLE} WHERE ds = DATE '{ds}';")

    # 2. Build the partition from the source tables.
    build = client.query(f"/* SQL */ INSERT INTO {TABLE} SELECT * FROM FEATUREQL({island(ds)});")

    # 3. Read the datakeys from the response of the build: one entry per island.
    outputs = build.to_dict()["full_response"]["data"]["featureql_queries"][0]["query_outputs_data_keys"]
    data_keys = {output["name"]: output["data_key"] for output in outputs}

    missing = [name for name in VALUE_COLUMNS if not data_keys.get(name)]
    if missing:
        raise RuntimeError(f"No datakey for {missing} on {ds}: these values cannot be cached.")

    # 4. Register the partition. Rows already registered return EXISTS.
    rows = ",\n".join(
        f"(DATAKEY = '{data_keys[name]}', BACKEND = 'duckdb', TABLE_NAME = '{TABLE}', "
        f"KEY_COLUMN = '{KEY_COLUMN}', VALUE_COLUMN = '{column}', "
        f"FILTER_COLUMN = 'ds', FILTER_VALUE = DATE '{ds}')"
        for name, column in VALUE_COLUMNS.items()
    )
    client.query(f"CREATE CACHES {rows};")

if __name__ == "__main__":
    for ds in [date(2026, 10, 7), date(2026, 10, 8)]:
        rebuild(ds)

Step 3 runs on every build on purpose. If someone changes ORDER_COUNTRY, the datakey of COUNTRY_REVENUE changes and every old row stops matching. Queries compute until the next build registers the new datakey. Nothing serves a value computed with the old logic. Rows with old datakeys are never read: list them with SHOW CACHES and drop them with DROP CACHES.

Between step 1 and step 2, the partition is empty but still registered, so a query for that date reads NULL. If readers run during rebuilds, write the new rows to a staging table first and swap the partition in one transaction.

Next

For the SQL side of the build, see hybrid queries . For the populations a datakey depends on, see @BIND_KEYSET() and RELATED() . For CONST and @eval_as_literal(), see metaprogramming .