UW Markdown → data lake

UW Markdown is the backbone of a CRE data lake, not the lake itself. The standard contributes the three things a lake cannot retrofit later — canonical facts (one deterministic JSON shape per deal, whatever the authoring surface), stable identity (a semantic digest that survives re-encoding), and verifiable trust (receipts that attest outputs follow from inputs) — and stays out of everything a lake vendor already does well (storage, orchestration, query engines).

This guide shows the full pipeline with nothing but the published uwmd CLI and DuckDB. Every command and query below was run before it was written down. The same CSVs load into Snowflake, ClickHouse, BigQuery, or Pandas unchanged — DuckDB is just the shortest path to a working demo.

1. From deals to bundles

Each .uwx.md (or .uw.md Lite) deal converts to a UW CSV bundle (UW_CSV_BUNDLE.md) — a deterministic ZIP of typed CSV files plus a manifest:

npx @uwmd/cli convert deal.uwx.md --to uw-csv-bundle --output lake/deal-001/deal.uw.csv.zip

Extracted, every bundle has the same layout:

deal-001/bundle/
├── manifest.json            # versions, semantic digest, file inventory, view descriptors
├── frontmatter.csv          # deal-level fields as typed value rows
├── blocks.csv               # section inventory
├── block_values.csv         # ★ one row per JSON fact — the fact table
├── prose.csv                # narrative text
├── document.csv             # document-level structure
└── views/                   # wide, spreadsheet-shaped projections (lossy, fidelity: "view")
    ├── deal_summary.csv
    ├── operating_statement.csv
    └── …                    # rent_roll, debt, valuation, sources_uses when those sections exist

Two different fidelities, on purpose:

• block_values.csv is lossless. Columns block_ref, scope, pointer, json_type, value_json: one row per fact, JSON Pointer addressing, canonical JSON values. Empty containers, nulls vs. absence, array order, and unsafe keys all survive. scope separates content from _meta provenance and block annotations. (block_ref is local to the encoding — use the deal's digest plus the pointer as the durable key, never block_ref alone.)
• The views/ files are lossy conveniences — deliberately flat for spreadsheet users and quick joins. They declare fidelity: "view" in the manifest and are never reconstruction inputs.

2. Querying the lake

Point DuckDB at a directory of extracted bundles. Three query layers fall out of the layout.

The catalog — one row per deal from the deal_summary view:

SELECT deal_id, asset_class, deal_stage, city
FROM read_csv('lake/*/bundle/views/deal_summary.csv',
              union_by_name = true, filename = true);

union_by_name matters: views may gain columns across format versions, and deals authored at different versions coexist in one lake.

The facts — every stated NOI-model number across every deal:

SELECT regexp_extract(replace(filename, chr(92), '/'), '([^/]+)/bundle', 1) AS deal,
       pointer, value_json
FROM read_csv('lake/*/bundle/block_values.csv',
              union_by_name = true, filename = true)
WHERE block_ref = '/sections/noi_model'
  AND scope = 'content'
  AND json_type = 'number';

(The replace(filename, chr(92), '/') keeps the deal-name extraction portable across Windows and POSIX paths.)

Rates are fractions, not percents (0.0551 = 5.51%) everywhere in UW Markdown — apply display formatting in the BI layer, not in ingestion.

The identity — semantic digests from the manifests:

SELECT regexp_extract(replace(filename, chr(92), '/'), '([^/]+)/bundle', 1) AS deal,
       semantic_digest
FROM read_json('lake/*/bundle/manifest.json', filename = true);

The digest is computed over the canonical envelope, so it is the natural idempotency key for ingestion: re-encoding an unchanged deal produces a byte-identical bundle with the same digest, and your loader can skip it. A changed digest means the deal's semantic content actually changed.

3. Joining in trust

A lake of numbers is only as good as its provenance. Issue a receipt per deal:

npx @uwmd/cli receipt issue deal.uwx.md --output lake/deal-001/deal.receipt.json

A receipt (UW_RECEIPT.md) attests that the deal's computed outputs follow from its stated inputs under a named calc pack — it does not attest that the inputs are true. Its subject.digest is the same semantic digest the bundle manifest carries, which makes the trust join one equality:

SELECT s.deal_id,
       r.subject.digest = m.semantic_digest AS receipt_matches_bundle,
       r.policy.validation.errors           AS validation_errors,
       r.computation.pack                   AS pack
FROM read_json('lake/deal-001/deal.receipt.json')       r,
     read_json('lake/deal-001/bundle/manifest.json')    m,
     read_csv('lake/deal-001/bundle/views/deal_summary.csv') s;

Now every row in the lake traces to a digest, and every digest to a receipt with a validation verdict and a named, versioned calc pack. That chain — not the CSV files — is what distinguishes this from generic ETL: two independent implementations of the standard produce the same digests and the same computed outputs, so the lake's numbers are reproducible claims, not spreadsheet exhaust.

4. Scaling up

The loop over deals is deliberately boring — any orchestrator can run uwmd convert and uwmd receipt issue per file. For the whole-corpus case, @uwmd/batch walks a directory, validates every deal, and emits both a deal-level catalog (uwmd-collection.json + CSV, with semantic digests) and — with --facts — the corpus fact table directly:

npx @uwmd/batch deals --out lake-out --facts

lake-out/uwmd-facts.jsonl is one line per JSON fact per deal: the same normative block_values rows as §2's fact table, prefixed with path, deal_id, asset_class, semantic_digest, and the validation verdict — so the DuckDB side collapses to:

SELECT deal_id, pointer, value_json
FROM read_json('lake-out/uwmd-facts.jsonl')
WHERE block_ref = '/sections/noi_model' AND scope = 'content'
  AND pointer = '/net_operating_income' AND valid;

Files that cannot produce an envelope are listed in uwmd-facts-manifest.json under deals_skipped; deals that parse but fail validation stay in the table with valid: false. A fact table never silently drops a deal.

5. A PostgreSQL reference loader

DuckDB over JSONL is the zero-setup path. When the destination is a PostgreSQL-style warehouse, @uwmd/lake (RFC 0049; on npm from release 3.0.0, or build it from source) turns the same canonical outputs into idempotent SQL:

import {
  postgresLakeSchemaStatements, lakeInputFromEnvelope, planLakeLoad, executeLakeLoad,
} from '@uwmd/lake';

// One command per call: a client given a values array uses the extended query
// protocol, which carries exactly one statement. `postgresLakeSchema()` returns
// the same DDL as a single script, for piping to psql.
for (const statement of postgresLakeSchemaStatements('uwmd_lake')) await client.query(statement, []);
const { document, facts } = await lakeInputFromEnvelope(envelope, { path, deal_id, valid });
await executeLakeLoad(await planLakeLoad({ documents: [document], facts, schema: 'uwmd_lake' }), client);

It plans { sql, params } pairs and never opens a connection itself, so no database driver enters any @uwmd/* dependency tree. Six tables (uw_documents, uw_facts, uw_receipts, uw_packages, uw_package_members, uw_source_evidence) each keep the raw canonical JSON in jsonb and typed shadow columns beside it — value_number, value_text, value_boolean, value_date — so a filter can use an index while the JSONB column stays the answer of record.

Identity follows §3's chain rather than the filesystem: a document is keyed by its semantic digest, a fact by (semantic_digest, block_ref, scope, pointer), a package member by its byte digest, a receipt by the canonical hash of the receipt JSON. Every statement is INSERT … ON CONFLICT … DO UPDATE, so a re-load is a no-op and an edited deal arrives as a new row instead of erasing the old one.

readBatchFactJSONL and readBlockValuesCSV load §4's JSONL and §1's CSV bundle directly, so nothing above needs to be regenerated to reach PostgreSQL.

One shape to expect in uw_facts: a fact whose json_type is object or array has a NULL value_json, because the canonical fact table represents a container by its flattened children rather than by a repeated blob. Filter on value_json IS NOT NULL when you want only the leaves.

The whole conformance corpus has been loaded against PostgreSQL 18 — 382 documents, 24,380 facts — twice, for the same row counts. See the load record.

What the standard will not grow: a storage contract, warehouse-specific loaders, or aggregate-math semantics in the lake layer. Aggregates that need to be verifiable belong in the document layer (portfolio rollups, RFC 0021 §6; portfolio sidecars, protocol §XV) where a verifier can recompute them — what lands in your warehouse is a projection, and projections are the host's domain.