DLZ Layout and the Three Generations

The DLZ folder structure IS the contract. Each logical entity lives in its own folder; each file inside is a dated member of that entity’s series.

Three explicit generations separate zero-transform extraction, column canonicalisation, and enrichment — each writes to its own directory. The dbt contract is the last generation, not an in-place mutation of the first.

This page is the authoritative reference for how source files enter the pipeline and how they travel through to the KLS. It pairs with the Extraction Guide (which focuses on the pipeline.yml contract) and the Pipeline Overview (which focuses on Bronze → Silver → Gold).

---

The DLZ Layout

<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

Each pipeline.yml entry describes one folder. Not one file, not one delivery — a folder that holds the series. The extractor walks it on every run, discovers the members, and extracts each one.

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	✓
2025-03-14abc.xlsx	2025-03-14	✓
20250314.xlsx	—	✗ no dashes, not a date prefix

Design intent: operators add context to filenames (“2026-02-17 corrected prices.xlsx”, “2025-03-25 before year-end.xlsx”). The system respects that without demanding rigid clerical hygiene.

---

The Three Generations

Every jin make writes three distinct directories. Each generation is a pure function of its input; re-running only the enrichment step is safe, idempotent, and does not require re-extracting xlsx.

DLZ   →   build/gen00_raw/            →   build/gen01_canonical/       →   build/gen02_enriched/         →   KLS
          (zero-transform,                 (OPALE column renames,            (lineage columns, backfill,        (dbt contract)
           per-member nested:                nested by canonical entity:      taxonomy; dbt reads this)
           <entity>/<date>/)                 <canon>/<date>.csv)

Gen 00 — zero-transform (gen00_raw/)

Pure mechanical xlsx → CSV. One file per (source entity, member date, sheet). Nothing transformed. _SHEETS.json carries the safe↔original sheet-name mapping plus header_row.

gen00_raw/
  e1_material_master/
    2025-03-25/
      medikament_site_b_2022.csv
      medikament_site_b_2023.csv
      medikament_site_b_2024.csv
      Material_site_a_2022.csv
      …
      _SHEETS.json
    2026-02-17/
      medikament_site_b__full_.csv
      Material_site_a__full_.csv
      …
      _SHEETS.json

Gen 01 — canonical (gen01_canonical/)

Source-system-specific column renames, value fixes, derivations. Keyed by the canonical entity, not the source entity — bronze reads this contract and does not care which DLZ folder produced it.

gen01_canonical/
  master_medikament_site_b/2026-02-17.csv
  master_medikament_site_a/2026-02-17.csv
  master_material_site_b/2026-02-17.csv
  master_material_site_a/2026-02-17.csv
  price_history/2025-03-25.csv                 ← yearly deliveries land here
  billing_hospitalisation/2026-02-17.csv       ← (1)-continuation merged in
  billing_ambulatoire/2026-02-17.csv
  movements_medikament_site_b/2026-02-17.csv
  movements_medikament_site_a/2026-02-17.csv
  movements_material/2026-02-17.csv
  cost_centers/2026-02-17.csv
  cost_centers/2026-02-17.meta.json            ← sidecar for H4-backfill at enrich
  service_mandates/2026-02-17.csv
  material_classifications/2026-02-17.csv
  packaging_types/2026-02-17.csv
  suppliers/2026-02-17.csv
  material_sourcing/2026-02-17.csv
  e00_ou_structure/2026-04-06.csv              ← copy-through, no column mapping
  _static/
    supply_orgs.csv                            ← non-temporal reference data
    procedures.csv

The canonicalise: script in pipeline.yml (default: canonicalise_csvs.py) owns this step.

Gen 02 — enriched (gen02_enriched/)

Adds lineage columns (data_source, is_backfilled), cost-centre H4 backfill, classification L1/L2 ancestor inference, taxonomy nodes, and tenant seeds. This is the dbt contract: csv_path() in dbt macros points here.

Same nested layout as Gen 01 for dated entities, plus flat reference files (taxonomy, supply_orgs, procedures, tenant seeds).

The enrich: script in pipeline.yml (default: enrich_csvs.py) owns this step.

Why three, not two

• Debuggability: diff gen01_canonical/X/<date>.csv vs gen02_enriched/X/<date>.csv to see exactly what enrichment added.
• Idempotency: a corrupt gen02/? Delete it and re-run enrichment from a clean gen01/. No re-extraction needed.
• Honest boundaries: no phase reaches into another’s output.

---

Canonical Entity Naming

By Gen 01 there are zero references to source-system artifacts — no E1, E3, E4, E5, H1–H4, no _raw_, no vendor names. Canonical names say what the data is, not where it came from. If SAP or Navision tomorrow feed the same entity, bronze does not change.

semantic prefix	meaning
master_*	material master catalogue (what exists today)
movements_*	stock movements (what moved where)
billing_*	financial event records
price_history	temporal price series (yearly or daily deliveries)
—	already semantic: packaging_types, cost_centers, service_mandates, material_classifications, suppliers, material_sourcing, supply_orgs, procedures, taxonomy_*

---

Excel-Size Splits — the (N) Continuation Merger

Vendors occasionally split a logical entity across sheets when the Excel row limit bites — hospitalisation plus hospitalisation(1). The CsvWorkbook layer detects this generically:

• Sheet names matching <base>(N) where N is a digit, and <base> also exists, are continuations.
• Structural compatibility is enforced: primary’s header-row width must equal continuation’s first-data-row width. Mismatch → hard error, no silent merge.
• Continuations disappear from the public sheet list. Downstream code sees one logical sheet.
• Row iteration chains: primary data rows first, then each continuation from row 0 (continuations have no header — they are row-count-split resumes, not repeats).

Works for any future foo(1), foo(2) without per-entity code.

---

The Two Time Dimensions

Two orthogonal time columns travel together on any temporal attribute:

column	where it comes from	what it means
as_of_date	filename path (<canonical>/YYYY-MM-DD.csv)	when we learned — delivery provenance
price_valid_year (or valid_from / valid_until)	CSV column, possibly derived from a sheet name (medikament_site_b_2022.csv → 2022)	when the attribute was real

These must never be collapsed. as_of_date answers “what did we know, when?”. The valid-* columns answer “what was the truth in the real world?”. A later delivery can restate a prior year’s price; both rows survive in bronze.

Today’s convention for yearly OPALE deliveries: valid_from = as_of_date. This is an exercise, not a final answer. If future deliveries carry explicit effective dates per attribute, valid_from comes from those.

---

Dedup Policy — Per Entity, Never Universal

Every OPALE bronze table now carries as_of_date. Silver decides what to do with it:

delivery style	silver pattern	examples
Supersedes — newer delivery is authoritative	qualify row_number() over (partition by <natural_key> order by as_of_date desc) = 1	cost_centers, service_mandates, material_classifications, suppliers (as catalogue)
Complements — each delivery adds distinct content	keep all rows; let the attribute’s own time axis drive the step function	price_history (yearly), future packaging-size history
Events — each row is a timestamped fact	keep all rows; as_of_date is provenance only	billing events, material movements

“Latest wins” is one policy of three, not a default. Every silver model states its choice explicitly.

---

Delivering a New File

1. Drop YYYY-MM-DD[ optional tag].xlsx into the correct entity folder under the DLZ.
2. jin inspect --tenant pack.tenant — verify the member is discovered.
3. jin make — extracts incrementally (only new or hash-changed members), canonicalises, enriches, rebuilds the KLS.

The extract_log.jsonl ledger (afs/state/) records every (entry_id, member_date, sha256) tuple. Unchanged members are skipped on subsequent runs.

---

What Stage 2 Retired

• Flat output names with source-system prefixes (e1_raw_medikament_site_b.csv, e3e4_raw_hospitalisation.csv, …).
• e3e4_raw_hospitalisation1.csv as a separate entity — merged transparently by the (N) detector.
• In-place enrichment on a single build/csv/ — generations now form an immutable chain.
• Manual per-entity mapping for Excel-size splits — now generic.
• pipeline.yml’s output:, delivered_by, delivered_at, sha256, size_bytes, script: fields.
• extract: slot name — renamed to canonicalise: to reflect what that script actually does.
• Legacy script names: extract_opale_xlsx_csv.py → canonicalise_csvs.py.
• Legacy model names: bronze_e1_prices → bronze_price_history.

---

Related

• Extraction Guide — the pipeline.yml contract, the seven source types, the jin inspect CLI
• Pipeline Overview — how extract feeds Bronze → Silver → Gold
• Supply-Org Pricing — the (material, supply_org) pricing grain that rides on Gen 02 outputs