Extraction
Extraction is a trust boundary. Everything that crosses it must be listed, verified, logged, and failure-intolerant.
The configuration IS the contract, and the contract is the audit trail.
Extraction is where the pipeline reaches into the real world — into shared folders, USB sticks, vendor exports, manually-curated Excel workbooks — and pulls bytes into the analytical pipeline. Everything downstream rests on the assumption that this layer answers one question correctly: which exact file was read, and what was its content?
This guide describes the extraction contract: the pipeline.yml file, the six source types, the zero-transform rule, the folder-of-dated-members model, and the jin inspect CLI.
The Seven Principles
Section titled “The Seven Principles”These are stated as rules, not preferences. They apply together.
1. Deterministic, not heuristic
Section titled “1. Deterministic, not heuristic”No globs. No guesswork. Every source is pinned to a folder at a well-defined DLZ-relative path, and the members of the folder are named YYYY-MM-DD[ optional tag].xlsx so the pipeline knows which delivery is which without reading file contents.
2. Explicit, not conventional
Section titled “2. Explicit, not conventional”Reading pipeline.yml tells you the complete universe of what jin make will ever touch on the way in. No hidden knowledge inside Python scripts. Every folder is listed, by hand, with its declared purpose and its expected shape.
3. Purpose-bound
Section titled “3. Purpose-bound”Every entry declares why the folder is being read — in the config, in plain language, reviewable by a person who has never seen the code. Six months from now, anybody reading the config knows exactly what the series is for without having to dig.
4. Content-pinned, not just path-pinned
Section titled “4. Content-pinned, not just path-pinned”Two different hash mechanisms live at this layer, and keeping them apart is the point of the principle.
The declared pin — is this the file the contract names? A file-shaped source declares source.sha256 in pipeline.yml. Phase 0 of every jin make compares it to the bytes on disk before anything reads them, and a mismatch refuses the build. This is the authenticity half: it attests that what crossed the boundary is what was agreed.
The logged hash — has it changed since last time? Every extracted member’s observed SHA-256 is recorded in afs/state/extract_log.jsonl. On subsequent runs the new observation is compared to the logged one; unchanged members are skipped, changed members re-extract and mark downstream stale. This is the incrementality half.
They are not substitutes. Comparing against the last observed hash tells you whether a file moved; it can never tell you whether it was the right file to begin with — if the first extraction read the wrong delivery, every run afterwards reports “unchanged” and agrees with itself forever. Only the declared pin breaks that loop, because only the declared pin has an outside opinion.
This page claimed source.sha256 was retired from 2026-04 until 2026-11-03, while docs/design/extractor_discipline.md went on specifying it as the trust boundary and numetrix.rmc went on declaring twenty-two of them. The retirement was true of the folder-as-series shape — a folder of dated members has no single hash to declare — and was written as though it were true of every shape. For the file shape it was never true, and the gap it opened is described under Verifying the declared pin below.
5. Anomalies are errors, not warnings
Section titled “5. Anomalies are errors, not warnings”At this layer, the list of conditions that must cause a hard failure is long and unforgiving:
- A declared
source.sha256that does not match the file on disk - Folder missing at the declared path
- Folder present but empty (no
YYYY-MM-DD.xlsxmembers) - Expected sheet not found in a workbook member
- Required column(s) missing from the header row
- Member produces fewer rows than
min_rows - Extractor script exits non-zero
No warnings users can ignore. No “continue anyway” flags. No silent fallbacks.
6. Zero-transform
Section titled “6. Zero-transform”Extractors are dumb. They open the workbook, parse the declared structure, and emit CSV rows that map one-to-one to the workbook content. No filtering, no calculations, no “smart” handling of missing values, no unit conversions, no date normalization, no deduplication.
Anything that looks like decision-making about the data belongs in Gen 01 canonicalisation or Gen 02 enrichment — never in Gen 00 extraction.
7. Fully traceable, per run
Section titled “7. Fully traceable, per run”Every extraction run appends to afs/state/extract_log.jsonl — git-tracked, one JSON record per (entry, member) tuple. Over time the log becomes a queryable history: “when did we last extract the 2026-02-17 delivery of e1_material_master? what hash? how long did it take?” All answerable by reading a file.
Source Taxonomy
Section titled “Source Taxonomy”Not all ingress is the same. The extraction framework declares what kind of thing each source is, because different kinds need different governance.
| Type | Example | Characteristics | Special governance |
|---|---|---|---|
system_export | OPALE e1_material_master/, SAP MM AUFK | Machine-generated, reproducible, schema-stable | Automated verification |
expert_curation | SME-authored e00_ou_structure/ with priority flags | Human-authored, judgmental, schema-volatile | author, reviewed_by, justification recommended |
reference_data | ICD-10 codes, ATC, MIGEL, FX rates | External authority, rarely changes | publisher, license, upstream_url recommended |
pipeline_config | Severity thresholds, exclusion lists | Instructions to the pipeline | author, reviewed_by, impact_statement, affects recommended |
snapshot_series | Point-in-time state dumps on a schedule | Periodic captures, one member per interval | Interval + retention convention |
api_feed | HL7 FHIR, REST webhook | Real-time or scheduled pull from a live system | Future phase |
db_extract | Read-only query against an operational DB | Direct pull, pinned query, connection reference | Future phase |
Why the taxonomy matters: a finding derived from a system export and a finding derived from a hand-curated spreadsheet have different epistemic weight. The source type travels with the data all the way to the Observation layer and becomes part of the validation surface.
jin inspect --check verifies that every entry’s source.type is in the allowlist and warns on missing / unknown types.
The DLZ Layout — One Folder Per Entity
Section titled “The DLZ Layout — One Folder Per Entity”Each entity lives in its own folder; each file inside is a dated member of that entity’s series. The folder name is the entity id.
<dlz_root>/<pack>/<tenant>/<source_system>/ xlsx/ <entity_folder>/ YYYY-MM-DD[ optional tag].xlsx ← dated member YYYY-MM-DD[ optional tag].xlsx … archive/xlsx/… ← sibling, frozen historyConcrete example (hrcentral.vai on OPALE):
jinflow-datalandingzone/hrcentral/vai/opale/ xlsx/ e00_ou_structure/ 2026-04-06.xlsx e1_material_master/ 2025-03-25.xlsx 2026-02-17.xlsx ← two members, different content shapes e3e4_billing/ 2026-02-17.xlsx e5_material_movements/ 2026-02-17.xlsx e6_suppliers/ 2026-02-17.xlsx h1h4_reference/ 2026-02-17.xlsx archive/xlsx/… ← older deliveries, frozenFilename convention
Section titled “Filename convention”Member filenames must start with a strict YYYY-MM-DD prefix. Everything after the date is a human-readable tag the system ignores:
| filename | member date | note |
|---|---|---|
2025-03-14.xlsx | 2025-03-14 | classic |
2025-03-14 new version from operator.xlsx | 2025-03-14 | ✓ suffix ignored |
2025-03-14_draft.xlsx | 2025-03-14 | ✓ |
20250314.xlsx | — | ✗ no dashes, not a date prefix |
Operators often add context to filenames (“2026-02-17 corrected prices.xlsx”). The system respects that without demanding rigid clerical hygiene.
See DLZ Layout and the Three Generations for the full architecture and how the DLZ feeds Gen 00 / Gen 01 / Gen 02.
The pipeline.yml Shape
Section titled “The pipeline.yml Shape”Lives at afs/scripts/pipeline.yml in the tenant AFS — one config per tenant, fully self-contained, no pack-level inheritance.
# ── Three scripts that power `jin make` ────────────────────────────generate: generate_synthetic_data.py # (optional) synthetic data generatorcanonicalise: canonicalise_csvs.py # Gen 00 → Gen 01: column renames, derivationsenrich: enrich_csvs.py # Gen 01 → Gen 02: lineage + backfills
hooks: {}
# ── Extraction — one entry per DLZ folder ──────────────────────────# Each folder is a series; every YYYY-MM-DD.xlsx inside it is a dated# member. The extractor discovers members, extracts each (zero-transform),# and carries the date forward as `as_of_date` in bronze.
extract_entries: - id: e00_ou_structure # = folder name under xlsx/ purpose: "ABT department structure with priority flags and ABT→KST mapping" source: type: expert_curation path: "opale/xlsx/e00_ou_structure/" sheet: "Tabelle1" header_row: 6 expected: required_columns: ["ABT Code", "ABT Name", "ABT Kat.", "Prio"] min_rows: 100 particularities: - "Column E (Prio) uses German boolean: JA = priority, empty = not" - "Rows 0-5 are metadata/description, data starts at row 7"
- id: e1_material_master purpose: "Material master — articles, prices, quantities" source: type: system_export path: "opale/xlsx/e1_material_master/" all_sheets: true # read every sheet in every member header_row: 3 # row 0=empty, 1=title, 2=empty, 3=headers expected: min_rows: 1000 # per-member minimum
- id: h1h4_reference purpose: "Reference tables — H1 packaging, H2 classifications, H3 cost centres, H4 service mandates" source: type: system_export path: "opale/xlsx/h1h4_reference/" all_sheets: true header_row: 0 expected: min_rows: 30Field reference
Section titled “Field reference”| Field | Required | Purpose |
|---|---|---|
id | yes | Stable identifier for the log, CLI, and Gen 00 output folder. Conventionally equal to the DLZ folder name. Unique within the tenant. |
purpose | recommended | One-line human description. Rendered by jin inspect --contract. |
source.type | yes | One of the seven source taxonomy types (see above). Checked against an allowlist. |
source.path | yes | DLZ-relative path. A folder (every YYYY-MM-DD*.xlsx inside is a member of the series) or a single file (a delivery that arrives under a fixed name, e.g. SAP IS-H). No globs, no wildcards, no absolute paths. |
source.sha256 | yes, file shape | Full 64-char SHA-256 of a file-shaped source. Verified at the top of Phase 0 on every build; a mismatch refuses the build. Not declarable for a folder-of-members series — those are hashed per member. |
sheet | one-of | Name of a single sheet to read (mutually exclusive with all_sheets). |
all_sheets | one-of | true to read every sheet in every member (the common case for multi-sheet workbooks). |
header_row | yes | 0-based row index where the column headers live. Applied to every sheet unless overridden per-sheet. |
expected.required_columns | recommended | Column names that must appear in the header row. Failure = hard error. |
expected.min_rows | recommended | Minimum data-row count per member. Failure = hard error. |
particularities | recommended | Free-form notes rendered by jin inspect --contract — for humans, not enforced programmatically. |
Retired since Stage 2 (2026-04) for the folder-as-series shape: output:, purpose.delivered_by, purpose.delivered_at, source.sha256, source.size_bytes, script:. Output paths derive mechanically from (id, member date, sheet); delivery attribution belongs in the DLZ commit history; a series’ member hashes are recorded in the extract log on first sight rather than declared, because no single hash describes a folder.
source.sha256 is not retired for a file-shaped source — there it is the only thing that pins the delivery, and it is verified on every build. The blanket wording above stood on this page from 2026-04 to 2026-11-03 and was wrong for every extract: entry in the fleet.
The Extract Log
Section titled “The Extract Log”afs/state/extract_log.jsonl — append-only, git-tracked, one JSON record per (entry, member) extraction, plus one event: "verify" record per declared pin checked. Every record captures:
- Entry id, member date, declared folder path, observed SHA-256, file size, mtime
- For a
verifyrecord:declared_sha256andobserved_sha256side by side withhash_match, so the log answers “did this build honour the contract?” and not merely “what did it see?” - Sheets extracted, per-sheet row counts, output files written with their hashes
- Outcome (ok / error) with full error details on failure
- Start, end, duration, engine version, git commit
The log is git-tracked: every build run produces new log lines that become part of the AFS commit. Over a year of daily builds, the file accumulates a few thousand lines — human-readable, git-diffable, queryable with jq, Python, or DuckDB’s read_json_auto.
Extraction as Phase 0 of Make
Section titled “Extraction as Phase 0 of Make”Extraction is the first phase of every build:
jin make # Phase 0 (verify pins → extract) → canonicalise → enrich → dbtjin make --skip-extract # Skip re-extraction when you know sources haven't changedjin make --extract-only # Run Phase 0 only, then stopVerifying the declared pin
Section titled “Verifying the declared pin”Phase 0 opens by comparing every declared source.sha256 against the file on disk — before the extraction loop runs and before the pre_sync hooks that consume the DLZ inbox. A trust boundary is checked on the way in; attesting to it afterwards is not the same thing.
Three outcomes, kept distinct because the next action differs for each:
| Outcome | Code | What happens |
|---|---|---|
| verified | — | observed equals declared; logged, build proceeds |
| mismatch | E025 | the build refuses. Either the bytes moved or the contract is stale — re-pin pipeline.yml as a reviewable change, or find out why |
| unverifiable | W015 | a pin is declared but the file it names is absent (or no DLZ is configured). Nothing was checked, so this is never reported as a pass |
| unpinned | W016 | a file-shaped source declares no hash, so nothing pins what crossed |
A folder-of-members series and an out-of-band (api_feed / URL) source are neither pinned nor unpinned — neither has a single declarable hash, and counting them as unpinned would produce a warning nobody could ever clear.
--deny=W015 or --deny=W016 promotes a warning to fatal; -W promotes everything. There is deliberately no flag to skip verification — the only use for one would be to walk past a real mismatch, and the documented remedy for that is to re-pin, which goes through review and leaves a trace in git.
This gate landed 2026-11-03. Before it, the declared pin was compared to the file by exactly one code path — jin inspect --check, which is opt-in, read-only and absent from CI. jin make hashed every file it touched and compared each one only against its own previous observation, so a build consumed rmc’s twenty-two pinned IS-H files on every run without once consulting the contract, and went green either way. The check costs 0.18 s for 210 MB; it was never skipped for speed, it was never wired.
━━━ Phase 0: Verify declared pins ━━━ ✓ ish_mara_material_master: sha256 ca1ec430b7172e60… (2,272,245 bytes) … 22/22 declared pins verifiedIncremental detection
Section titled “Incremental detection”- For each entry, walk the DLZ folder and discover
YYYY-MM-DD*.xlsxmembers. - For each member, hash the file and compare to the last successful entry in
extract_log.jsonl. - Matching hash → skip (log line:
skipped: unchanged). Different hash or first sighting → extract. - After extraction, compare Gen 00 output hashes to the previous run’s; if they actually changed, mark Gen 01 / 02 / dbt stale.
A build against an unchanged DLZ skips extraction entirely — fast, no I/O. A build after a new delivery re-extracts only the affected entries and marks the downstream layers stale.
Inspecting the Contract — jin inspect
Section titled “Inspecting the Contract — jin inspect”jin make does extraction; jin inspect sees extraction. No overlap — inspect is strictly read-only and never runs the extractors.
jin inspect # show every entry with folder status + discovered membersjin inspect --verbose # full detail: sha256 prefixes, sizes, particularitiesjin inspect --contract # render the full contract (purpose, source type, sheets, particularities)jin inspect --check # verify folder presence + source.type allowlist; exit 1 on violationsjin inspect <entry_id> # filter to one specific entryjin inspect pack.tenant # override the active tenantDefault output — folder status per entry
Section titled “Default output — folder status per entry”jin inspect lists every declared entry and enumerates the dated members the extractor would see right now:
rmc — 6 extract entries─────────────────────────────────────────────────────────────────
e1_material_master [system_export] Purpose: Material master — articles, prices, quantities Folder: opale/xlsx/e1_material_master/ ✓ 2 members 2025-03-25 9,370,838 bytes 2026-02-17 32,885,622 bytes Sheets: all sheets (header row 3) Status: READY
e3e4_billing [system_export] … Status: READYStatus values:
| Status | Meaning |
|---|---|
READY | Folder present; at least one dated member discovered. Build will proceed. |
EMPTY | Folder present; no YYYY-MM-DD.xlsx members. --check fails. |
MISSING | Folder absent at declared path. --check fails. |
UNKNOWN | DLZ root not configured for this tenant. |
--contract — delivery specification
Section titled “--contract — delivery specification”jin inspect --contract is the tool to reach for when you want to audit a tenant’s ingress surface in one go. It renders the full DLZ tree, every entry’s purpose, source type, expected sheets, particularities, and required columns — the complete story, config-only, no disk I/O beyond the DLZ walk.
--check — pre-flight verification
Section titled “--check — pre-flight verification”jin inspect --check walks every entry, verifies folder presence, compares every declared source.sha256 against the file on disk, and runs the structure contract checks (source.type in allowlist, required_columns present when specified, min_rows respected per member). CI-friendly: exit 0 if clean, exit 1 if any entry fails. Does NOT extract.
inspect --check and Phase 0’s gate now ask the same question of the same declarations; the difference is only that one is a tool you reach for and the other cannot be avoided.
Scope — Pack vs Tenant
Section titled “Scope — Pack vs Tenant”Extractor scripts are pack-level assets. They know how to parse a given kind of workbook — OPALE sheet conventions, SAP MM AUFK exports, winery scale-weigh exports. They live in the domain pack and are copied into each tenant’s afs/scripts/ at jin init <pack>.<tenant> time.
The pipeline.yml that tells the scripts what to do is tenant-specific. Two tenants in the same pack, using the same source system, at the same point in time, still have different pipeline.yml files — different deliveries, different auxiliary files, different file versions. There is no pack-level pipeline.yml, no inheritance, no cascade. Each tenant’s config is fully self-contained.
Boilerplate at this layer is readable. Inheritance at this layer is dangerous.
Related
Section titled “Related”- DLZ Layout and the Three Generations — how Gen 00 / Gen 01 / Gen 02 carry data from xlsx to the dbt contract
- Pipeline Overview — how extract, bronze, silver, and gold fit together
- Make Guide — the build pipeline; extraction is Phase 0
- Tenants Guide — tenant layout and AFS structure