Materialized datasets¶

A materialized dataset has kind = "query" in its [dataset.source] block. Instead of reading parquet or Delta files, DataPress runs your SQL against other registered datasets, collects the result into memory (or writes it to a storage backend), and serves it exactly like a file-backed dataset.

Joins, aggregations, window functions — any SELECT the engine supports.
Automatic dependency ordering at boot: dependencies are built before dependents, and independent datasets build concurrently.
Non-blocking startup: the HTTP listener is ready immediately; every dataset build runs in the background.
Interval-based scheduled refresh and upstream-triggered cascade.
Optional storage-backed lazy residency for results that are too large for RAM.

Minimal example¶

[[dataset]]
name = "accidents"

  [dataset.source]
  kind     = "parquet"
  location = "data/accidents.parquet"

[[dataset]]
name = "state_daily_severity"

  [dataset.source]
  kind       = "query"
  sql        = """
    SELECT state, CAST(start_time AS DATE) AS day, AVG(severity) AS avg_sev
    FROM accidents
    GROUP BY state, day
  """
  depends_on = ["accidents"]   # required: must list every dataset the SQL references

on_start = "eager"             # "eager" (default) | "lazy" | "skip"

The state_daily_severity dataset is built from the accidents dataset. On /query and /count it behaves identically to a parquet dataset — no query-path branching.

`[dataset.source]` — `kind = "query"`¶

Field	Required	Default	Notes
`kind`	yes	—	Must be `"query"`.
`sql`	yes	—	A single read-only `SELECT` (or `WITH … SELECT`). The same denylist as `POST /api/v1/sql` applies: no file functions, no DDL, no DML.
`depends_on`	yes	—	Array of dataset names referenced by the SQL. Exact-match both ways: every name in `depends_on` must appear in the SQL, and every table reference in the SQL must be listed.

depends_on is validated at startup: unknown names, missing names, and self-references are all startup errors.

Cross-dataset joins¶

A query dataset may reference any combination of registered datasets — including other query datasets (chained materialization). The SQL is validated with the same read-only validator as POST /api/v1/sql.

[[dataset]]
name = "regions"

  [dataset.source]
  kind     = "parquet"
  location = "data/regions.parquet"

[[dataset]]
name = "accidents_enriched"

  [dataset.source]
  kind       = "query"
  sql        = "SELECT a.*, r.timezone FROM accidents a JOIN regions r ON a.state = r.state"
  depends_on = ["accidents", "regions"]

`on_start` — startup policy¶

on_start is a top-level field on [[dataset]] (not inside [dataset.source]). It controls how the dataset is treated during startup.

Value	Behaviour
`eager`	(default) Built in the background at boot. `/readyz` waits for it.
`lazy`	Registered as `pending`; built on the first incoming query. The triggering request waits for the build to finish. Not gated by `/readyz`.
`skip`	Registered as `pending`; built only when `POST /api/v1/datasets/{name}/reload` is called. Not gated by `/readyz`. Queries return `503` with `Retry-After: 2` until it is built.

on_start is valid on any dataset kind (parquet, delta, query).

Non-blocking startup¶

The HTTP listener binds and serves before any dataset build begins. All datasets start in pending state; eager datasets are then built in background tasks.

A request to a pending or building dataset returns:

HTTP 503 Service Unavailable
Retry-After: 2
Content-Type: application/json

{"error":"dataset 'state_daily_severity' is not yet ready","state":"building"}

Gate your orchestrator (Kubernetes, ECS, HAProxy health check) on /readyz rather than a single /query call.

`/readyz` behaviour¶

/readyz semantics are controlled by [server.startup] readiness:

Mode	When `/readyz` returns `200`
`all`	(default) Every `eager` dataset has published successfully. A failed `eager` dataset keeps `/readyz` at `503`.
`any`	At least one `eager` dataset has published.

lazy and skip datasets never gate readiness — they start in pending without blocking the ready signal.

`[server.startup]` — startup concurrency¶

[server.startup]
max_concurrent = 4        # independent datasets built concurrently; default 4
readiness      = "all"    # "all" | "any"; default "all"

Field	Default	Notes
`max_concurrent`	`4`	Maximum datasets built in parallel during startup. Dependencies are always ordered before dependents regardless of this cap.
`readiness`	`"all"`	See above.

`[dataset.refresh]` — scheduled refresh¶

Add a [dataset.refresh] block to schedule periodic re-materialization. Only valid on kind = "query" datasets; setting it on a file-backed dataset is a startup error.

[[dataset]]
name = "state_daily_severity"

  [dataset.source]
  kind       = "query"
  sql        = "..."
  depends_on = ["accidents"]

  [dataset.refresh]
  interval           = "15m"    # humantime: "30s", "15m", "2h", "1d"
  on_upstream_reload = true     # re-build when "accidents" publishes
  timeout            = "10m"    # build timeout; default "10m"
  jitter             = true     # ±10% uniform jitter; default true
  debounce           = "5s"     # cascade debounce window; default "5s"

Field	Default	Notes
`interval`	(unset)	Polling interval. Absent = no scheduled refresh.
`on_upstream_reload`	`false`	Re-build when any dependency publishes a new generation.
`timeout`	`"10m"`	Per-build timeout. The build is cancelled on expiry; the previous generation stays live.
`jitter`	`true`	Apply ±10% uniform jitter to every scheduled fire to avoid thundering herds.
`debounce`	`"5s"`	When `on_upstream_reload = true`, multiple upstream publishes within this window coalesce into one refresh.

Concurrency rules¶

Scheduled refresh and manual POST .../reload share the same per-dataset mutex. A concurrent manual reload coalesces the scheduled tick: the tick is skipped and the next fire is scheduled from now + interval.
The global semaphore in [server.refresh] governs how many dataset builds may run simultaneously across all scheduled refreshes.

[server.refresh]
max_concurrent = 1    # default 1

Failure backoff¶

On consecutive build failures, the scheduler backs off exponentially: base_interval × 2^min(failures, 3), capped at 8 × base_interval. The counter resets on success or on a coalesced-tick.

Snapshot consistency¶

Every materialization captures a snapshot of each dependency's published state once, before planning. Mid-build upstreams do not bleed into the result:

DataFusion: one Arc clone of each dependency's DatasetState is taken before the query is planned. Readers of the old generation keep their captured arcs; the new generation is published atomically via ArcSwap.
DuckDB: the CREATE OR REPLACE TABLE … AS SELECT runs inside a single transaction; DuckDB's MVCC provides a consistent snapshot.

Keep-last-good: a failed build never publishes. The previously published generation stays live and queryable.

`[dataset.materialize]` — residency¶

By default, materialized results live in memory. Use [dataset.materialize] to control this.

[dataset.materialize]
residency      = "auto"      # "auto" (default) | "memory" | "lazy"
sort_by        = ["state"]   # ORDER BY applied when writing parquet files
reuse_on_start = false       # reuse an existing generation on restart

Field	Default	Notes
`residency`	`"auto"`	See below.
`sort_by`	`[]`	Column names. Applied as `ORDER BY` so parquet row-group min/max stats enable effective predicate pruning.
`reuse_on_start`	`false`	On boot, find the newest complete generation whose SQL-hash and schema-hash match current config and register it without rebuilding. Default `false` — predictable freshness.

Residency modes¶

Mode	Behaviour
`auto`	(default) In memory unless the build crosses `[server.storage] force_lazy_above_mb`; then automatically demoted to storage. With no `[server.storage]` configured, behaves as `memory` with a startup WARN.
`memory`	Always in RAM. Crossing the threshold logs a WARN but does not demote.
`lazy`	Always written to the storage backend; served lazily from parquet. Requires `[server.storage]`.

lazy residency disables the DataFusion equality index: combining an explicit [dataset.index] block with residency = "lazy" is a startup error. The auto-index (mode = "auto") is silently skipped for demoted generations, with a WARN logged.

`[server.storage]` — storage backend¶

Configure a server-level storage backend to enable lazy residency or auto-demotion for query datasets.

[server.storage]
backend            = "local"                            # "local" | "s3"
root               = "/var/lib/datapress/materialized"  # local path or "s3://bucket/prefix"
force_lazy_above_mb = 512                               # auto-demotion threshold; default 512

Local storage¶

[server.storage]
backend = "local"
root    = "/var/lib/datapress/materialized"
force_lazy_above_mb = 512

S3 / S3-compatible storage¶

[server.storage]
backend = "s3"
root    = "s3://my-bucket/datapress"

  [server.storage.s3]
  region              = "eu-west-1"
  endpoint            = "http://localhost:9000"    # omit for AWS; MinIO / R2 / etc.
  access_key_id_env   = "DATAPRESS_STORAGE_KEY_ID" # env-var NAME, not the value
  secret_access_key_env = "DATAPRESS_STORAGE_SECRET"
  addressing_style    = "path"    # "virtual" (default) | "path" (MinIO)
  allow_http          = true      # required for http:// endpoints

Field	Default	Notes
`backend`	`"local"`	`"local"` or `"s3"`.
`root`	(required)	Local path or `s3://bucket/prefix`.
`force_lazy_above_mb`	`512`	Auto-demotion threshold in MiB. `0` disables auto-demotion (only explicit `lazy` datasets use storage).
`s3.region`	(unset)	AWS region. Omit to use `AWS_REGION` or the SDK default.
`s3.endpoint`	(unset)	Custom endpoint (MinIO, R2, Wasabi…).
`s3.access_key_id_env`	(unset)	Name of the env var holding the access key ID. Both env vars must be set together, or both omitted (provider chain).
`s3.secret_access_key_env`	(unset)	Name of the env var holding the secret key.
`s3.addressing_style`	`"virtual"`	`"virtual"` or `"path"`. MinIO requires `"path"`.
`s3.allow_http`	`false`	Required for `http://` endpoints.

Inline credentials are rejected

Inline secret values directly in TOML (e.g. access_key_id = "AKI…") are a startup error in [server.storage.s3]. Use env-var indirection only.

Generation layout¶

Each published generation is written to:

<root>/<dataset-name>/<generation-ulid>/data-*.parquet
<root>/<dataset-name>/<generation-ulid>/manifest.json

manifest.json is written last — a generation without a manifest is treated as incomplete and GC'd at boot. The manifest records the SQL hash, schema hash, row count, byte size, file list, and creation timestamp.

N-2 retention: on each publish, all generations older than the previous one are deleted. In-flight readers of the prior generation keep their file handles until they finish; the swap is atomic at the index level (ArcSwap / view replace), not at the filesystem level.

reuse_on_start = true checks the sql-hash and schema-hash in the most recent complete manifest against the current config before deciding whether to skip a rebuild.

Cascade refresh¶

When on_upstream_reload = true, a new generation in any upstream dependency triggers a refresh of this dataset. The cascade follows topological order across the DAG: a dataset is not refreshed until all its dependencies in the same wave have published.

A diamond (d depends on b and c, both depending on a) refreshes d exactly once per settled wave, governed by the debounce window.

Cascade refreshes use the same Phase 3 scheduler machinery (global semaphore, per-dataset mutex, timeout, coalescing). A cascade triggered by a failed upstream build does not fire — nothing was published.

Schema and query paths¶

A query dataset exposes the same routes as a file-backed dataset:

Route	Notes
`GET /api/v1/datasets/{name}/schema`	Schema inferred from the materialized result; one sample row.
`POST /api/v1/datasets/{name}/query`	Filter / project / sort / paginate.
`POST /api/v1/datasets/{name}/count`	Total or filtered row count.
`POST /api/v1/datasets/{name}/reload`	Re-execute the SQL and publish (manual refresh).
`GET /api/v1/datasets/{name}/status`	State, residency, refresh observability fields.

The equality index (DataFusion) is built from the materialized result, so point-lookup predicates work exactly as on a parquet dataset — unless residency = "lazy", which has no eq-index (see above).

Complete config reference¶

All new keys introduced by materialized datasets:

# ---------- per dataset ----------

[[dataset]]
name    = "my_query_dataset"
on_start = "eager"          # "eager" | "lazy" | "skip"; default "eager"

  [dataset.source]
  kind       = "query"
  sql        = "SELECT ..."
  depends_on = ["dep1", "dep2"]

  [dataset.refresh]
  interval           = "15m"   # absent = no scheduled refresh
  on_upstream_reload = false   # default false
  timeout            = "10m"   # default "10m"
  jitter             = true    # default true
  debounce           = "5s"    # default "5s"

  [dataset.materialize]
  residency      = "auto"   # "auto" | "memory" | "lazy"; default "auto"
  sort_by        = []        # default []
  reuse_on_start = false     # default false

# ---------- server ----------

[server.startup]
max_concurrent = 4     # default 4
readiness      = "all" # "all" | "any"; default "all"

[server.refresh]
max_concurrent = 1     # default 1

[server.storage]
backend             = "local"   # "local" | "s3"
root                = "/var/lib/datapress/materialized"
force_lazy_above_mb = 512       # default 512

  [server.storage.s3]           # required iff backend = "s3"
  region                = "us-east-1"
  endpoint              = ""
  access_key_id_env     = "DATAPRESS_STORAGE_KEY_ID"
  secret_access_key_env = "DATAPRESS_STORAGE_SECRET"
  addressing_style      = "virtual"  # "virtual" | "path"
  allow_http            = false