Metadata-Version: 2.4
Name: dst-parquet-layout
Version: 0.2.0
Provides-Extra: convert
Summary: Plans and builds a long-term Parquet layout for a raw DST register delivery
Keywords: parquet,sas7bdat,register data,Statistics Denmark
Author: Tobias Kragholm
License-Expression: MIT
Requires-Python: >=3.8
Description-Content-Type: text/markdown; charset=UTF-8; variant=GFM

# dst-parquet

Converts a raw delivery of SAS register files from Statistics Denmark into a
tree of Parquet files, which R, Python and Stata can read. Each SAS file becomes
one Parquet file, in a folder per register:

```
<delivery>/<provider>/<register>/year=<YYYY>/<source file>.parquet
```

for example `2025/dst/bef/year=2015/bef201512.parquet`. Only a register whose
SAS files are split by year gets the `year=` folders. A register delivered as
one file is written as one file.

## Install

```
pip install dst-parquet-layout
```

This installs the command `dst-parquet`, and nothing else is needed.
`dst-parquet --version` checks that it is there.

## Converting a delivery

Commands are shown on one line, which is how cmd and PowerShell take them. Put a
path in quotes when it holds a space.

First a dry run, which reads the SAS headers and writes nothing:

```
dst-parquet convert --root "E:\rawdata\project" --out "E:\workdata\project\data" --dry-run
```

`--root` is the folder the delivery's `.sas7bdat` files are under, and `--out`
is where the tree is written. Then the conversion itself:

```
dst-parquet convert --root "E:\rawdata\project" --out "E:\workdata\project\data"
```

A full delivery takes about a day. While it runs, a panel at the bottom of the
window shows how far it has come and how long is left. The last line starts with
`RESULT` and says whether everything was converted, and what to do if not.

Stopping is safe. Ctrl+C, a closed window or a restart loses only the files
being written at that moment. Run the same command again and it continues:
files already written are left as they are, and half-written ones are removed.

Other folders and files in `--out` are left alone. Files being written have
names that start with `_`, which the Parquet readers of R and Python skip, so
the tree can be read while a run is going. Only one run at a time can write to a
tree, and a second one is refused.

## What you get

Each register folder holds its Parquet files and a `_layout.json`. That file
records which SAS file each Parquet file came from, with its full path, the row
counts, anything found wrong, and the release and rules that wrote it.

Column names are in lowercase. Columns are matched across years by name, so a
column that is missing from one year is there as empty values in that year's
files, and `_layout.json` names the files it was missing from.

Columns whose names start with `_` were added by the conversion and are not in
the raw data:

- `_src_file`, in every file: the name of the SAS file the row came from. The
  same name recurs across deliveries, and `_layout.json` gives its full path.
- `_population`, in registers from the two Grunddata extracts: `popc` or
  `popfuld`. The extracts overlap, so combining them counts people twice.
- `_src_month`, for a register delivered several times a year: the month in the
  file name.
- `_ref_date`, for `bef`: the date the population count refers to.
- One for each part of a file name that tells otherwise identical tables apart,
  such as `_tabel` in the DRG tables.

## When it refuses or reports a problem

Each refusal names the register and says what to do. The common ones:

- An earlier conversion is there. The register was written by an earlier
  release or under other rules, or by a run that stopped before it finished.
  Add `--replace`, which removes what that run wrote in that register's folder
  and converts the register again. Nothing else is removed.
- Another run is writing to the tree. Wait for it to finish, or stop it.
- The sources differ from the ones converted. A SAS file was replaced or added
  since the register was converted. `--new-version <register>` writes the
  register again as `<register>_v2` beside the old one, which is kept.
- A register is blocked. The rules cannot place one of its files.
  `dst-parquet rules explain <register>` says which rule decides it and what to
  write in an overrides file to change it.
- Row counts, repeated keys or U+FFFD. The files are written, but something in
  them needs a look: fewer or more rows than the SAS header says, a person twice
  in one population snapshot, or text that could not be decoded. The run counts
  as failed until somebody has looked.

## Words used here and in the output

- Delivery: one batch of files from Statistics Denmark or the Danish Health Data
  Authority, named by the year it arrived, such as `2025`.
- Provider: who sent it, `dst` or `sds`.
- Register: one table, such as `bef` or `lmdb`, however many files it arrives
  in.
- Register directory: the folder a register's Parquet files are written to.
- Sidecar: the `_layout.json` in each register directory.
- Snapshot: one file of a register delivered several times a year, such as
  `bef` at the end of each quarter.
- Reference and pooled: registers with no year in their file names. A reference
  table is a code list, and a pooled table holds events from many years in one
  file. Both are written whole.
- Rules: the built-in `rules.toml`, which decides all of the above.
  `dst-parquet rules show` prints it.

## Other commands

`convert` works out the layout itself as it starts. `plan` does the same
without converting anything, and writes six files to read or to send to
somebody deciding whether this is the structure they want:

```
dst-parquet plan --root "E:\rawdata\project" --out-dir plan
```

`TREE.txt` is the whole layout as a directory tree, one line per register
directory. `SUMMARY.txt` is the status of every register directory and every
open decision. `REGISTER_PLAN.csv` is one row per destination directory,
`PLACEMENT.csv` one row per source file, and `REVIEW.csv` the decisions that
need a yes and the ones that block. `PROVENANCE.json` records the version, the
time, and digests of the rules and the file list. Given `--plan plan`, a
conversion refuses to run if the rules or the file list have changed since.

`plan` can also read a per-file list, or an exported register map, which needs
no access to the server:

```
dst-parquet plan --files files.csv --out-dir plan
dst-parquet plan --map 2026_register_map --out-dir plan
```

`dates` dates a delivery directory whose name does not say when it arrived:

```
dst-parquet dates --root "E:\rawdata\project" --out-dir dates
```

`resolve` names one copy of each snapshot delivered more than once, and compares
the others with it:

```
dst-parquet resolve --out "E:\workdata\project\data" --out-dir resolution
```

`retime` is a one-off repair for a tree written before SAS times were written
as times of day, described under "How it is written" below.

`plan` exits non-zero when a register is blocked, so it can gate a conversion in
a script, and `convert` when it skipped a register or found something wrong in
what it wrote. A path that does not exist, or a register name the delivery does
not have, stops either command before it starts.

# Reference

The rest of this file is for whoever maintains the rules or wants to know how a
conversion decides what it does.

The delivery the tool was written against is 3,032 GB in 1,469 files. It is a
single binary, first written in Python and ported to Rust with the same
commands, rules file and layout. The conversion reads each source once, puts it
into its register directory's schema, writes it and measures it in the same
pass, and holds about a gigabyte however large the source is.
`dst-parquet-system-heap` is the same program with the platform's own memory
allocator in place of mimalloc, for timing the two on the machine that runs the
conversion.

With its output sent to a file, `convert` prints a line per register directory
as it finishes and the panel's figures every five minutes. `--quiet` prints only
the summary.

## What a conversion checks and refuses

`convert` plans for itself from the rules it runs with, and refuses every
register directory that plan blocks. Given `--plan`, it also refuses to run if
the rules or the file list have changed since that plan was written. A plan is
read by people and acted on by a program, sometimes weeks apart, and nothing
about editing `rules.toml` in between leaves a mark on the plan.
`--accept-rules-change` overrides it, out loud. Either way every register's
`_layout.json` records the release and the rules that wrote it.

Every source is read once, and what is checked is checked as the rows go past:

- The rows written are compared with the count in the source's header, less any
  rows the header marks deleted. A header that declares exactly 4,294,967,295
  rows has a saturated counter, which says nothing about the file: it is read to
  its last page and reported, not compared.
- Every string cell is checked for U+FFFD, the replacement character a lossy
  decode leaves behind, which no later repair can undo. The reader decodes every
  windows-1252 byte, so in this delivery it can only appear in a file that is not
  what it declares. A file holding one stays written and fails the run.
  `--verify-utf8` is still accepted, and no longer needed.
- A stock series' key, `PNR` for `bef`, is checked for a person held twice.
- For a cumulative series, the file kept for a year is asked how many of its rows
  fall before each dropped file's cut-off, against the count in the dropped
  file's header.
- Every file gets a fingerprint of its content, independent of row order and
  taken column by column, which `resolve` compares across deliveries.

What was measured goes into the register directory's `_layout.json`, and into each
file's own Parquet footer beside the release and rules that wrote it. A file that
is already there is not read again, so a run that resumes it reports again
whatever the run that wrote it found wrong, marked as an earlier run's finding,
and fails as that run did.

A run is resumable: a file that is already there is left alone, and the partial
files an interrupted run left behind in the directories being converted are swept
first. A file is written as `_<name>.part`, and a pooled source's `year=` files
under `_<stem>.part`, and moved into place when it is finished. A register gets
a `_layout.json` marked `in_progress` before its first file and the full one
after its last, so a run cut off in between leaves a register the next run
finishes, taking what the cut-off run found from the footers of the files it
wrote. A run holds `_dst-parquet.lock` in `--out` while it writes, which the
operating system releases when the run ends however it ends.

`--jobs` converts several register directories at once, largest first.
`--threads` caps what the whole run uses, decoding and encoding together.

A register directory is written once and then left alone. Each source's size and
the creation stamp in its SAS header are recorded beside the data, and a
conversion whose sources no longer match refuses and names the files that differ.
`--new-version bef` then writes the corrected data to the next free `bef_v2`
beside the directory already there, which is left exactly as it is.

A register directory written under other rules, as `_layout.json` records them,
is refused too, and so is one holding Parquet files with no `_layout.json` that
can be read, as a release before 0.2 leaves a register it was stopped in.
`--replace` removes what that run wrote and converts the register again, so a
release with new rules can be run over the tree an earlier one wrote:

```
dst-parquet convert --root "E:\rawdata\project" --out "E:\workdata\project\data" --replace --dry-run
dst-parquet convert --root "E:\rawdata\project" --out "E:\workdata\project\data" --replace
```

Only the Parquet files of the sources the register's `_layout.json` names, or
this run places there, are removed, in that register's directory and its
`<register>_v<n>` beside it, so nothing else under `--out` is touched. A Parquet
file there that neither accounts for stops the register, with nothing removed,
until it is moved away. The sidecar goes last, so a run cut
off partway removes the rest next time. The rules include the overrides file,
so changing either makes every register due for replacing.

`--io-backend` chooses how sources are read. `buffered`, the default, reads each
file in large pieces on several threads at once. `mmap` maps the file into memory
instead, and `auto` maps a local file and reads a network share in pieces. A
conversion reads every byte of a file once, front to back, which is what large
reads in parallel suit.

## How it is written

Files are written with zstd at the level the rules set. Floats are split by byte
stream and dates and times delta-encoded, and a column nearly every value of which
is distinct in its first rows does without a dictionary, which is what lets those
encodings apply. On a synthetic `bef`-shaped file that made the output 28 MB where
the Python implementation's polars writer made 72 MB.

Column types are the reader's own, carried through, with one exception. A SAS
TIME column is seconds since midnight, and the reader gives it as a duration,
because nothing stops a SAS time from being negative or a day and more. It is
written as a time of day, which R, polars and DuckDB read as a clock time; a
value outside 00:00 to 24:00 is written as null, and the summary and the
register's `_layout.json` say how many, in which file and column. The rule
`[layout] sas_time = "duration"` writes every value as it is instead. Where the
rules promote a code column from Float64 to String, a whole number is written
without its decimal point: `1301.0` is `"1301"`.

A tree converted before SAS times became times of day holds them as durations.

```
dst-parquet retime --out E:/workdata/<project>/parquet
```

rewrites each file that has such a column, in place, with the same rule for
values outside a day, and records the counts in the register's `_layout.json`.
`--out` may be the tree or any directory inside or above it: each register's
files are found from where its own `_layout.json` sits, and one whose files are
not there is named. Several files are rewritten at once (`--jobs`), each file's
row groups are decoded in parallel, and the progress panel shows them as
`convert` does. A time hashes as the duration it was, so a file's fingerprint
only changes where a value became null. `--dry-run` reads only the duration
columns, reports what it would change, and writes nothing.

## What is still open

`OPEN_ITEMS.md`, beside this file, is the list: the questions only a person can
answer, the decisions the rules make that want a yes, and what is left undone in
the code on purpose.

## The rules

`rules.toml` holds every judgement, one entry each: which directory is which
delivery and provider, which files are the same table, which registers are not
partitioned by year, how a type conflict is promoted, which collisions are
resolved and how, and what several files in one year of a register are to each
other. The binary carries the file it was built with, and `--rules PATH` reads
another. Changing a decision is a change to that file, not to the program, and
`tests/` pins the current answers, so an edit says out loud which files it moved.

### Changing a few of them

A delivery that does not fit the rules is blocked in the plan, and the reason
says which rule to change and what to write. To see what decides a register or a
file, and the lines that would change it:

```
dst-parquet rules explain forloeb --plan plan/
dst-parquet rules explain bef201912.sas7bdat
```

The changes go in an overrides file, which holds only the entries it changes, in
the sections `rules.toml` uses:

```toml
[undated.partition_on]
forloeb = "dato_henvisning"

[layout]
compression_level = 6
```

A key replaces the key it names. An entry in a list, such as a `[[series]]` or a
`[[delivery]]`, is matched by what identifies it (`register`, `snapshot`) and
only the fields given change; `remove = true` deletes it, and a new
`[[register]]` is tried before the built-in ones. A register named under one of
`[undated]`'s treatments leaves the others. Check the file, and see what it
changes, before planning with it:

```
dst-parquet --overrides overrides.toml rules check
dst-parquet --overrides overrides.toml plan --root E:/rawdata/<delivery> --out-dir plan/
dst-parquet --overrides overrides.toml convert --root ... --plan plan/
```

The plan lists the changes in `SUMMARY.txt`, keeps a copy as `OVERRIDES.toml`
and pins the rules and the overrides together, so a conversion without the same
overrides is refused and told which flag it is missing. `dst-parquet rules show`
prints the built-in rules, comments and all, which is where every default and
its reason is written down.

## What the rules have to survive

Each of these is observed in an August 2026 walk of one delivery.

The register is not the alphabetic prefix of the filename. `soma_l2_t19_proc19`
and `soma_l3_t19_diag19` share a prefix and are different tables. Read as one
register they give a schema in which 197 of 197 columns go missing somewhere.

115 files carry no year, for three different reasons: code lists with no time
dimension, event tables pooled across the whole period, and tables the provider
split into numbered shards. Each needs a different answer, and a register
directory that is partitioned for some of its files and not others cannot be
scanned at all.

A pooled table carries no year in its filename and thirty of them in its rows.
It is written whole, because a register is partitioned by year only where its
source is split into year tables. `[undated.partition_on]` can name a date
column to cut one into the `year=` shape of a dated register. A row whose date
is missing then keeps a null year in the standard `__HIVE_DEFAULT_PARTITION__`
directory: it is never dropped and never given a year it does not have, and the
count is reported.

`dnt_1159.sas7bdat` parses as the year 1159 under any rule that takes four digits
and stops. `soma_l2_t19_drgkont19` carries its year as `19`.
`dimcancergruppering_icd10` carries no year at all. `bef199312` carries a month
that must not be lost.

350 register-years appear in more than one delivery and 139 of those disagree on
row count, usually by a handful of rows. Deliveries stay apart and nothing is
deduplicated across them. Two files that would land in one partition with nothing
to tell them apart block until a rule says which wins.

A month in a filename is a period in one register and a cut-off in another, and
the filename does not say which. `bef200803` is the population at the end of
March 2008 and all four of that year's files are wanted, where `lmdb202406` is
every prescription of 2024 up to the end of June and is already inside
`lmdb202412`. So a register that arrives more than once a year declares its grain
in `[[series]]`, the plan checks that against the file sizes before acting on it,
and a year holding more than one file whose register declares nothing refuses to
convert.

A year in a filename is a reading of a filename, not a measurement. A file more
than eight times the median file of its own register is reported with the ratio,
because `udd_grundskole_fravaer_kmd2024` is 39.7 million rows where that
register's years run 3 to 5 million.

624 of 2,491 register-columns are absent from at least one file of their own
register, and 721 columns move position between years. Schemas are therefore
built by name, never by order, names are lowercased, and each register directory
carries a `_layout.json` naming the files each column was absent from.

Every file declares windows-1252 and Parquet declares UTF-8. Conversion checks
the declaration per file and refuses a register that declares anything else.

## Development

```
cargo nextest run -p dst-parquet
cargo clippy -p dst-parquet --all-targets -- -D warnings
just dst-parquet-wheel      # the Windows and macOS wheels, sanitised and scanned
just dst-parquet-publish    # uploads them, after asking
```

The conversion tests write real `.sas7bdat` files with `sas7bdat-writer` and read
them through the published reader, so they test the same decode a delivery goes
through. `tests/real_sources.rs` converts a small synthetic delivery of six such
files, written by `tests/synthetic/` into each test's own directory, since no
`.sas7bdat` file is committed.

