mz-demo-data

Live operational demo data, generated entirely in SQL

This skill builds continuously-updating, realistic synthetic data inside a running Materialize instance — no Kafka, no external load generator, no seed scripts. Everything is plain views over mz_now().

The technique comes from this blog post. Use the catalog at the bottom for an existing domain; use the rubric to design new ones.

Quickstart

psql -p 6875 -h localhost -U materialize -f assets/scaffold.sql
psql -p 6875 -h localhost -U materialize -f assets/common/people.sql
psql -p 6875 -h localhost -U materialize -f assets/domains/auctions.sql

Everything lands in its own materialize_demo schema, so nothing in the user's existing schemas is touched. Every object is created IF NOT EXISTS, so re-running any file is safe, but it also means re-running an edited file keeps the old definitions. To rebuild, run assets/teardown.sql (it drops the schema) and load again.

Then in a psql session:

SET search_path = materialize_demo;
COPY (SUBSCRIBE (SELECT COUNT(*) FROM auctions) WITH (progress = true)) TO STDOUT;

You should see a heartbeat tick once per second. The window is backfilled on load, so the count starts at retention/tick (default 86,400) and stays there as both ends of the window advance.

To change the window or tick rate, set them before \i. They only take effect on a fresh load; if the scaffold is already there, run assets/teardown.sql first.

\set retention '6 hours'
\set tick '1 second'
\i assets/scaffold.sql

To tear everything down: psql -f assets/teardown.sql.

First contact: walking a fresh user from zero to demo

Read this section first if you're an agent and a user shows up wanting a demo. The skill is meant to feel guided, not like a SQL reference. Drive the user through these turns:

Turn 1 — establish the connection

Ask once if it isn't already in context:

What's the connection? A psql command, a MATERIALIZE_URL, or host/port/user/db?

Accept any of: a connection string, env var, or the four flags. Don't proceed until you can run psql -c 'SELECT mz_version();' and get a row back. If they say "localhost defaults", use psql -p 6875 -h localhost -U materialize -d materialize.

Turn 2 — pick a domain

If the user named one ("show me a banking demo"), check the catalog at the bottom of this file. If it matches, jump to Turn 5 with that file.

If they have a domain in mind that isn't in the catalog ("I work in logistics", "we do telecom billing", "give me healthcare"), continue to Turn 3 — we'll build a new domain together. Do not force-fit their domain onto one in the catalog; that's worse than a fresh one.

If they don't have a domain in mind, suggest 2–3 from the catalog with a one-line hook each and let them pick. Banking is the strongest correctness story; zoo is there to show that a domain can be anything, not just orders and sensors.

Turn 3 — propose the model in plain language

Not SQL yet. Show the user the entities and relationships in English, plus the invariant you'd bake in. Format:

Here's what I'd build:

<TopEntity> (one per moment) — fields: a, b, c

<ChildEntity> (3–5 per top-level) — fields: x, y

<Lookup> (static, N rows) — purpose

Invariant baked in: <one sentence — what must always hold>

Joins to people? <yes/no, and why>

Sound right, or different entities?

Wait for their reaction. Don't write SQL until they sign off. They will usually correct one of: an entity name, a missing field, the invariant, or the cardinality. Cheap to iterate here, expensive later.

Turn 4 — show the byte budget

Once entities are confirmed, show the byte allocation explicitly:

Per top-level row, 16 random bytes:
[0..2]  id              24-bit
[3]     person_id       mod 256, FK to people
[4]     kind            mod N
[5]     n_children      1..K
[6]     time offset     for due_at
[7..]   free
Children re-hash (parent.random || child_index) for their own bytes.

This is the last cheap-to-change step. If the user wants higher cardinality in some field, swap bytes here, not later.

Turn 5 — write and load

Copy assets/domains/_template.sql to assets/domains/<their>.sql and fill it in following the byte budget. Then:

# Every file is IF NOT EXISTS — safe to re-run.
psql ... -f assets/scaffold.sql
psql ... -f assets/common/people.sql    # only if domain joins people
psql ... -f assets/domains/<their>.sql

If you are iterating on the domain file, re-running it will not replace views that already exist. Drop the domain's objects first (DROP VIEW materialize_demo.<name>_core CASCADE takes its whole chain), or run assets/teardown.sql and load everything again.

Turn 6 — prove it works, hand over the wheel

Show the user three queries, after SET search_path = materialize_demo;:

Heartbeat — confirms data is flowing:

COPY (SUBSCRIBE (SELECT COUNT(*) FROM <top_table>) WITH (progress = true)) TO STDOUT;

Invariant — confirms correctness; should return the expected fixed value (usually 0).
One "cool" query — the demo payoff. For aggregations, SELECT ... GROUP BY ...; for cross-domain, a join through people.

Then offer: "Want to layer another domain on top? You'll see joins through the shared people table stay live too."

Things to not do during first contact

Don't dump the catalog up front. The user came with a domain or wants guidance — give them one path, not a menu.
Don't ship SQL without the plain-language proposal first.
Don't skip the byte budget. It's where misalignments hide (e.g., two fields sharing byte 5 by accident).
Don't claim "it's done" until the heartbeat ticks and the invariant query returns its expected value against THEIR Materialize instance.
Don't add columns the user didn't ask for. The template's free bytes exist; leave them free unless the user names a use.

The two-layer model

Every demo built with this skill has exactly two layers:

Scaffold (stable). The moments view — a sliding window of timestamps — and the random view — MD5(moment) producing 16 deterministic bytes per moment. Do not edit per-domain. Always loaded first; the same scaffold serves every domain. Lives in assets/scaffold.sql.
Domain (creative). Views that turn random bytes into entities, relationships, and aggregates. One file per domain in assets/domains/. Domains can compose: load multiple and they cross-join naturally via the shared people table.

Knowing where the line is matters. If you find yourself wanting to edit the scaffold from a domain file, you almost certainly want to add another view on top instead.

The four design rules for a domain

Every domain in the catalog follows these four rules. Apply them when designing a new one.

1. PKs are derived deterministically from `moment`

A row's primary key is a function of its moment's random bytes:

get_byte(random, 0) + get_byte(random, 1) * 256 + get_byte(random, 2) * 65536  AS id

Why: the same moment always yields the same id, so re-derivation is stable even though the underlying view is a sliding window. PKs naturally vanish from the system when their moment falls out of retention.

2. FKs come from re-derivation, not declared constraints

A child row is generated by re-hashing the parent's random bytes:

WITH expanded AS (
    SELECT id AS parent_id, ...
           digest(random::text || generate_series(1, n_children)::text, 'md5') AS random
    FROM parent_core
)
SELECT ... FROM expanded;

Why: when the parent's moment falls out of retention, every child derived from it vanishes simultaneously. Referential integrity for free, no FOREIGN KEY declarations needed. This is the key insight that makes the whole approach work.

3. Distributions are byte-mask choices

Cardinality is controlled by which bytes you read:

Pattern	Cardinality
`get_byte(random, 0)`	256
`get_byte(random, 0) + get_byte(random, 1) * 256`	65,536
`mod(get_byte(random, 0), 5)`	5
`get_byte(random, 0) < 80`	~31% boolean

Why: explicit and predictable. A 256-account banking demo uses one byte mod 64 for from-account; a 16M-id auction marketplace uses three bytes. Pick the cardinality you need.

4. Evolution is `moment + interval`

Time-relative fields are derived from the moment plus a random offset:

moment + (get_byte(random, 6)::text || ' minutes')::interval AS end_time

Why: the field is monotone in the moment, so downstream views can filter on it sensibly. Combined with rule #1, this gives you a temporal lifecycle (start, end, expiry) tied to the entity's identity.

Invariant-by-construction patterns

The strongest demos rely on invariants that cannot be violated because of how the data is constructed. Two patterns:

Sum-to-zero (double entry). Each event emits two child rows with opposite signs (banking.sql). SUM(ledger_entries.amount) = 0 holds at every consistent timestamp, regardless of concurrent transaction volume. This is the headline Materialize correctness demo and it's almost impossible to fake with off-the-shelf data.

Count matches declared fanout. A parent row declares n_items, and uses generate_series(1, n_items) to emit children. COUNT(children) = SUM(parent.n_items) is invariant (ecommerce.sql). State it in aggregate; the per-parent form breaks on id collisions (next section).

When designing a new domain, look for an invariant of this form. A demo without one is weaker — it shows speed but not correctness.

Invariants vs. id collisions

The blog's construction derives primary keys from 24 bits of MD5 entropy (get_byte(random, 0..2) packed into an int). At default settings — 86,400 moments in retention — the birthday paradox guarantees roughly 230–500 id collisions in steady state. This is intentional and expected. The blog accepts it; this skill keeps it for fidelity to the blog and to keep parent ids in a tractable range.

Collisions affect which invariant shapes you can honestly claim:

Survives collisions (claim freely):

Aggregate equalities. COUNT(children) = SUM(n_children over all parents).
Sum-to-zero. SUM(ledger_entries.amount) = 0.
Subset / FK by re-derivation. every child.parent_id has matching parent — re-derivation guarantees this even when multiple parents share an id (the child joins to some row with that id).
Distribution-shape claims. ~10% of page_views are /checkout.

Does NOT survive collisions (don't claim, or restate as aggregate):

Per-parent fanout equality. for every parent, COUNT(children with this id) = parent.n_children. Two parents sharing an id will sum their children, breaking the equality.
Per-id uniqueness. every id appears exactly once.
Per-row temporal monotonicity that depends on id grouping.

When the user asks "what's the invariant," default to the aggregate form unless the construction makes per-id claims structurally true (e.g. double-entry, where each transaction emits exactly two entries no matter what id arithmetic does).

If a demo requires unique ids (e.g. a customer-facing dashboard that treats id as a key), the fix is to either widen the id space (use 4+ bytes), use EXTRACT(EPOCH FROM moment)::bigint as the id (collision-free by construction), or accept ~0.5% noise as a feature ("real data is messy"). All three are reasonable; the catalog uses the blog's 24-bit form for fidelity.

Adapting to a new domain

When walking a user through this, follow the First contact protocol above — propose-then-confirm, don't write SQL upfront. The mechanical checklist is below.

To add assets/domains/<yours>.sql, work through these steps in order:

List the entities. Top-level (one per moment), child rows (per parent via fanout), shared lookups (static).
Pick the byte budget for each top-level entity. Random gives you 16 bytes per moment. Allocate bytes to fields by cardinality: id (3 bytes), FK to people (1 byte mod 256), category (1 byte mod N), timing offsets (1 byte), free for amount/value (2–3 bytes).
Decide which existing shared tables you reference. Today: people. If you reference people, your domain joins the cross-domain "same person appears in multiple data products" story automatically.
Identify the invariant. What sum, count, or equality must hold by construction? Bake it in via fanout or two-legged emission, not as a CHECK constraint.
Copy the template. cp assets/domains/_template.sql assets/domains/<yours>.sql and fill in the TODOs. The template already encodes the standard _core view → public MV → child MV → aggregate VIEW pattern, with every object in materialize_demo and created IF NOT EXISTS. Keep that structure.
Add two validation queries at the bottom: a SUBSCRIBE heartbeat and an invariant query that should always return 0 (or a fixed value).

Validation

For every domain, two queries:

-- Heartbeat: confirms the domain is live and producing rows.
COPY (SUBSCRIBE (SELECT COUNT(*) FROM <table>) WITH (progress = true)) TO STDOUT;

-- Invariant: should return a fixed value (usually 0).
SELECT ... ;

If the heartbeat ticks but the invariant drifts, the byte budget is misaligned — likely two entities sharing the same byte for different purposes. Re-allocate.

The catalog

File	Domain	Highlights	Joins `people`?
`domains/auctions.sql`	Auctions & bids	Marketplace; lifecycle (end_time); winning-bid demo	No
`domains/ecommerce.sql`	Orders, line items, totals	Multi-row child fanout; `order_totals` aggregate-as-view	Yes
`domains/banking.sql`	Accounts, double-entry txns	`SUM(balances) = 0` invariant. Strongest correctness demo.	Yes
`domains/iot.sql`	Devices, readings, alerts	High cardinality; threshold alerts; per-site rollup	No
`domains/clickstream.sql`	Sessions, page views, funnel	Funnel analytics; conversion rate by channel	Yes
`domains/zoo.sql`	Zoo visits, ratings, shipments	Three invariants at once, richest domain. Proof that a domain needn't be boring: front-of-house ratings correlate with back-of-house consultations by construction.	Yes

Loading more than one ecommerce/banking/clickstream domain at once gives you the cross-product demo: "show me Person 042's orders, their transactions, and their browsing session — all live, all consistent."

File map

skills/mz-demo-data/
├── SKILL.md                  ← you are here
├── README.md                 short human intro
└── assets/
    ├── scaffold.sql          moments + random; do not edit per-domain
    ├── teardown.sql          drops the materialize_demo schema
    ├── common/
    │   └── people.sql        256-identity pool, shared across domains
    └── domains/
        ├── _template.sql         ← copy this to start a new domain
        ├── auctions.sql
        ├── ecommerce.sql
        ├── banking.sql
        ├── iot.sql
        ├── clickstream.sql
        └── zoo.sql