Data model and rules

The hierarchy

Vendor            a company that sends data              code: chdc_health
  Project         one piece of work for that vendor      code: cleaning
    Folder        one dataset (one set of columns)       code: hospitals
      File        one upload (csv, tsv, anything)

• One folder = one dataset. All files in a folder are expected to have the same columns.
• Codes are 3-30 characters of lowercase letters, digits and single underscores, and never change (there is no rename).
• The lake table name is derived, never typed: <vendor code>__<project code>__<folder code>, for example chdc_health__cleaning__hospitals.

The three layers

Each folder has one table in each layer, in the Iceberg catalog lake: lake.bronze.<name>, lake.silver.<name>, lake.gold.<name>.

Layer	What is in it	Why it exists
Bronze	Every value as text, exactly as received, plus lineage columns _file_id, _source_file, _ingested_at, _row_id	A faithful copy that can be trusted as “what the vendor sent”
Silver	Trimmed text, blanks become null, types inferred (bigint, double, YYYY-MM-DD date, otherwise string; leading zeros stay text), exact duplicates inside the file removed	Clean, typed rows, still one row per source row
Gold	Distinct rows across all files of the folder, data columns only, rebuilt on every run	The “golden” set analysts and APIs normally read

Reprocessing a file first deletes its earlier rows by _file_id, so running the same file twice never doubles the data. If a new file has different columns from the folder’s earlier files it is refused with a message listing the difference.

The original file

The original goes to the raw bucket and is never modified or overwritten. The storage key is

<vendor>/<project>/<folder>/YYYY/MM/DD/<file uuid>/<cleaned file name>

File names are cleaned to letters, digits, ., _, space and -. Program types (exe, dll, bat, cmd, com, msi, scr, ps1, vbs, jar, sh) are refused. An empty file is refused. The largest accepted file is UPLOAD_MAX_MB (500 by default). Sending the same content twice is allowed, but the second response names the first (duplicateOf) and the audit note says so.

File statuses

uploaded -> processing -> processed
                       -> failed
                       -> quarantined      (file type not supported yet)

processed/failed/... -> archiving -> archived     (rows removed from the lake, original kept; can be restored)
                     -> deleting  -> deleted      (rows removed and original erased; the history row stays)

While a file is processing it also has a stage: starting, reading, bronze, silver, gold, done. Every change is written to file_events, which is never purged: it is the file’s life story.

Views

An analyst can save a query as a view. A view is its SQL and settings in the registry, and its result is real lake tables: lake.bronze.view__<name>, lake.silver.view__<name>, lake.gold.view__<name>.

• Bronze keeps the last three runs as text with _run_id and _materialized_at.
• Silver is the latest run only, trimmed and typed again, distinct.
• Gold is the distinct data columns, replaced in one atomic commit, so a reader never sees a half-built table.
• Query columns must be named with AS, lowercase, unique, and simple types. Arrays, maps, rows, JSON and binary are refused with a hint.
• A view rebuilds when you ask, when it is edited, when a file of a dataset it reads is processed, or when a view it reads finishes a newer build. Every build re-checks that the owner may still read the sources; if not, the view is blocked and its API answers 503.

A view is private to its author (and admins).

Who can read what

Role	Reads
Vendor	Its own vendor’s projects, folders and files in the web app. No SQL.
Analyst	Every dataset of the projects assigned to them, or of a whole vendor (including projects created later). Their own views.
Admin	Everything.

Access is checked in the API on every request. For SQL, the check happens twice: before the query reaches Trino (every dataset name in the text must be allowed) and again on Trino’s own plan of what it will really read.