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

These are stated as rules, not preferences. They apply together.

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

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

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

Every member’s SHA-256 is recorded in afs/state/extract_log.jsonl the first time it is extracted. On subsequent runs, the observed hash is compared to the logged hash. Unchanged members are skipped; changed members re-extract and mark downstream stale.

5. Anomalies are errors, not warnings

At this layer, the list of conditions that must cause a hard failure is long and unforgiving:

• Folder missing at the declared path
• Folder present but empty (no YYYY-MM-DD.xlsx members)
• 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

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

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

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

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 history

Concrete 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, frozen

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

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 generator
canonicalise: canonicalise_csvs.py         # Gen 00 → Gen 01: column renames, derivations
enrich: 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: 30

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 folder path. Every YYYY-MM-DD*.xlsx inside is a member of the series. No globs, no wildcards, no absolute paths.
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): 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; hashes are recorded in the extract log on first sight, not declared.

---

The Extract Log

afs/state/extract_log.jsonl — append-only, git-tracked, one JSON record per (entry, member) extraction. Every record captures:

• Entry id, member date, declared folder path, observed SHA-256, file size, mtime
• 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

Extraction is the first phase of every build:

jin make                  # Phase 0 (extract) → canonicalise → enrich → dbt
jin make --skip-extract   # Skip Phase 0 when you know sources haven't changed
jin make --extract-only   # Run Phase 0 only, then stop

Incremental detection

1. For each entry, walk the DLZ folder and discover YYYY-MM-DD*.xlsx members.
2. For each member, hash the file and compare to the last successful entry in extract_log.jsonl.
3. Matching hash → skip (log line: skipped: unchanged). Different hash or first sighting → extract.
4. 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

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 members
jin inspect --verbose             # full detail: sha256 prefixes, sizes, particularities
jin inspect --contract            # render the full contract (purpose, source type, sheets, particularities)
jin inspect --check               # verify folder presence + source.type allowlist; exit 1 on violations
jin inspect <entry_id>            # filter to one specific entry
jin inspect --tenant pack.tenant  # override the active tenant

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:      READY

Status 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

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

jin inspect --check walks every entry, verifies folder presence, 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.

---

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 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

• 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