--- title: "Master datasets" vignette: > %\VignetteIndexEntry{Master datasets} %\VignetteEngine{quarto::html} %\VignetteEncoding{UTF-8} format: html --- ```{r} #| label: setup #| include: false knitr::opts_chunk$set(collapse = TRUE, comment = "#>") library(hvtiRdatabuild) .master_deps <- requireNamespace("duckdb", quietly = TRUE) && requireNamespace("arrow", quietly = TRUE) && requireNamespace("jsonlite", quietly = TRUE) && requireNamespace("tidyselect", quietly = TRUE) && requireNamespace("dplyr", quietly = TRUE) && requireNamespace("withr", quietly = TRUE) ``` ## What a master is, and why a view A study build does not read `bd.data.master.sas` fresh every time; it reads a big rollup dataset, such as the cardiac-surgery or mitral master, that some earlier program already built and that dozens of downstream studies share. That sharing is exactly what makes a fix to it valuable and dangerous at the same time: a chart-review correction found by one study should reach every study reading that master, but a hand-edit to the SAS build program reaches only the next person who happens to rerun it. A warehouse view solves this the way a view usually does, by giving every reader the same query instead of the same file: `master_cardiac` sits over a frozen base table, corrections apply inside the view definition, and the day the corrections table gains a new accepted row, every study reading the view sees it without anyone touching their own code. The design that worked this out, including why the corrections model lives here rather than waiting for a future warehouse-wide layer, is `dev/specs/2026-09-23-cardiac-master-view-design.md`. ## `master.yml` Everything the six exports need to know about one master lives in a `master.yml` kept outside the package, next to the study configs, because it names paths on a shared volume. `read_master_config()` reads and validates it: ```{r} cfg_path <- system.file("extdata", "master-example.yml", package = "hvtiRdatabuild") writeLines(readLines(cfg_path)) ``` ```{r} cfg <- read_master_config(cfg_path) cfg$key cfg$alt_keys ``` Field by field: - `name` names the master, and also the warehouse view and the corrections tables built from it (`master_example_corrections`, `master_example_corrections_stale`, and so on). - `key` is the one primary key corrections match on and the view joins on. Here it is two columns, `ccfid` and `dt_surg`, because a patient identifier alone does not name one surgery. - `alt_keys` is a named list of bridges to other systems, Epic's `emrn` and `encounter_date` in this example. Each is checked unique where it is non-null, and its columns ride along on every correction for reference, but nothing matches on them except through `propose_correction()`'s explicit `alt_key` argument. - `parent` names an upstream master this one is built on top of (`master_parent`, read through the `master` libref), or is absent for a master with no parent. - `snapshots`, `current`, and `history` locate the SAS builds: the directory, the current build's file name, and a regular expression matching historical builds. - `build_program` is the SAS program whose inline patient-level fixes `backfill_corrections()` reads. ## Phase 0: `snapshot_master()` and lineage from the log Before anything reaches the warehouse, the SAS build is frozen as parquet with [snapshot_oracle()], the same way `snapshot_oracle()` freezes any oracle. `snapshot_master()` adds two things a plain snapshot does not need: it checks the primary and alternate keys, and it works out which release of the parent master this build actually read. That lineage question matters because a build program can be edited after it ran, so the current text of `bd.data.master.sas` is not proof of what happened the day the frozen dataset was produced. `snapshot_master()` reads it from evidence, in order: the SAS log whose timestamp brackets the dataset (SAS writes a `NOTE:` line for every dataset it reads), then, for the current build only, the build program's own `set` statements, then a `parent_release` declared in `master.yml`. A current build whose parent cannot be decided that way stops rather than guess; a historical build, whose log is often long gone, is recorded `"unknown"`. Only those `NOTE:` lines are ever read, never a data line, so nothing printed here can carry a key or a value. ```{r} #| eval: !expr .master_deps dir <- tempfile("master") dir.create(dir) file.copy(system.file("extdata", "oracle_small.sas7bdat", package = "hvtiRdatabuild"), file.path(dir, "built.sas7bdat")) writeLines("data m; set base; run;", file.path(dir, "bd.data.sas")) cfg_path <- file.path(dir, "master.yml") writeLines(c("name: master_demo", "key: [ccfidu]", paste0("snapshots: ", dir), "current: built.sas7bdat", paste0("build_program: ", file.path(dir, "bd.data.sas"))), cfg_path) snap <- snapshot_master(read_master_config(cfg_path), tempfile("out"), which = "current") snap[, c("file", "status", "n_rows", "key_verdict", "parent_source")] ``` With no `parent:` in this `master.yml`, `parent_source` reads `"none"`. Give the master a parent and `snapshot_master()` reports `"log"`, `"program"`, or `"declared"` instead, naming where it found the answer, never the answer's underlying data. ## Phases 1 and 2: `lift_master()` and `backfill_corrections()`, dry run first `lift_master()` moves a snapshot into the warehouse. It checks the key is unique, creates a base table named for the snapshot's release, loads the parquet a row group at a time, and only then proves the load equal to the snapshot: a quick check first (counts, aggregates, a sample), a full column-by-column comparison second. The master's view is created only after both pass. `dry_run = TRUE`, the default, stops after writing the DDL next to the snapshot, so a hand-off to whoever holds warehouse write rights costs nothing to try: ```{r} #| eval: !expr .master_deps pq <- file.path(dir, "built_demo.parquet") arrow::write_parquet(data.frame(id = c("K1", "K2"), x = c(1, 2)), pq) jsonlite::write_json( list(parquet_sha256 = digest::digest(pq, algo = "sha256", file = TRUE), columns = list(list(variable = "id", r_class = "character"), list(variable = "x", r_class = "numeric"))), file.path(dir, "built_demo.meta.json"), auto_unbox = TRUE) cfg_path <- file.path(dir, "master.yml") writeLines(c("name: master_demo2", "key: [id]", paste0("snapshots: ", dir), "current: built_demo.sas7bdat", paste0("build_program: ", file.path(dir, "bd.sas"))), cfg_path) con <- DBI::dbConnect(duckdb::duckdb()) dry <- lift_master(read_master_config(cfg_path), con, pq, dialect = "duckdb") dry$dry_run ``` Once the DDL is right, the same call with `dry_run = FALSE` creates the base table, loads it, checks parity, and publishes the view: ```{r} #| eval: !expr .master_deps lifted <- lift_master(read_master_config(cfg_path), con, pq, dry_run = FALSE, dialect = "duckdb") lifted$verdict DBI::dbGetQuery(con, "SELECT * FROM master_demo2 ORDER BY id") ``` `backfill_corrections()` closes the gap between a master newly in the warehouse and a build program that still carries its patient-level fixes inline as `if ccfid = 'K1' then age = 66;` statements. It reads those fixes, resolves each one against the base table, and records the ones that resolve to exactly one record as a correction with an unknown prior and a `bake` decision, meaning it is already present in the snapshot rather than something the view still needs to apply. A fix that changes a key column, matches no record, matches more than one, or cannot be parsed is reported by reason and line number, never recorded. It also requires a passing parity record, so it only runs after `lift_master()` has succeeded once. Dry run first, as with `lift_master()`: ```{r} #| eval: !expr .master_deps d <- data.frame(ccfid = c("K1", "K2", "K3"), dt_surg = as.Date("2020-01-01") + 0:2, emrn = c("E1", NA, "E3"), age = c(65.5, NA, 70), surgeon = c("S1", "S2", "S3"), stringsAsFactors = FALSE) pq2 <- file.path(dir, "built_t.parquet") arrow::write_parquet(d, pq2) jsonlite::write_json( list(parquet_sha256 = digest::digest(pq2, algo = "sha256", file = TRUE), columns = list( list(variable = "ccfid", r_class = "character"), list(variable = "dt_surg", r_class = "Date"), list(variable = "emrn", r_class = "character"), list(variable = "age", r_class = "numeric"), list(variable = "surgeon", r_class = "character") )), file.path(dir, "built_t.meta.json"), auto_unbox = TRUE ) writeLines(c("data m; set base;", "if ccfid = 'K1' then age = 66;", "if ccfid = 'K2' then ccfid = 'K9';", "run;"), file.path(dir, "bd.sas")) cfg2_path <- file.path(dir, "master.yml") writeLines(c("name: master_c", "key: [ccfid, dt_surg]", "alt_keys:", " epic: [emrn]", paste0("snapshots: ", dir), "current: built_t.sas7bdat", paste0("build_program: ", file.path(dir, "bd.sas"))), cfg2_path) cfg2 <- read_master_config(cfg2_path) invisible(lift_master(cfg2, con, pq2, dry_run = FALSE, dialect = "duckdb")) backfill_corrections(cfg2, con, dialect = "duckdb") ``` The dry run above reports one fix resolved (the `age` fix) and writes nothing. The `ccfid` remap is a key change, not a cell correction, so it is not counted among the resolved facts; identity corrections of that kind are designed separately (see the last section). Run it for real and the correction and its `bake` decision land in the tables, and the master's view is regenerated: ```{r} #| eval: !expr .master_deps backfill_corrections(cfg2, con, dry_run = FALSE, dialect = "duckdb") DBI::dbGetQuery(con, "SELECT decision FROM master_c_correction_decisions") ``` ## The everyday workflow: `propose_correction()`, `decide_correction()`, the stale view Once a master is lifted and backfilled, most corrections do not come from a SAS build at all. They come from an investigator's chart review, one cell at a time, and `propose_correction()` is how that reaches the table: ```{r} #| eval: !expr .master_deps prop <- propose_correction(cfg2, con, key_values = list(emrn = "E3"), alt_key = "epic", variable = "age", expected_prior = 70, new_value = 71, evidence_type = "chart_review", evidence_ref = "invented", asserted_by = "tester", dialect = "duckdb") prop$verdict ``` Finding the record by `emrn` through `alt_key` rather than by `ccfid` and `dt_surg` resolves to the primary key before anything is written, so the stored row still carries the primary key, with `emrn` filled in for reference. A proposal writes no decision, so it does not apply until someone accepts it: ```{r} #| eval: !expr .master_deps decide_correction(cfg2, con, prop$correction_id, "accept", "tester", dialect = "duckdb") DBI::dbGetQuery(con, "SELECT age FROM master_c WHERE emrn = 'E3'") ``` Not every accepted correction applies, and that is what the stale view is for. A correction goes stale when its variable is not a correctable column, its key no longer matches a row, its recorded prior does not match what the base currently holds, or either value fails to cast to the column's type. Rather than silently skip a correction that no longer fits, `lift_master()` and `backfill_corrections()` publish a `_corrections_stale` view alongside the master's own, naming exactly which corrections did not apply and why, so a stale correction is a thing you can query, not a thing you have to notice went quiet. ```{r} #| eval: !expr .master_deps DBI::dbGetQuery(con, "SELECT * FROM master_c_corrections_stale") ``` ```{r} #| eval: !expr .master_deps #| include: false DBI::dbDisconnect(con, shutdown = TRUE) ``` ## What is not done yet Three things this vignette does not show, because the package does not do them yet: - **The port.** Nothing here writes a study's `bd.R`/`vars.R` against a master view; that is the next spec in the replacement path, once a master read function exists. - **Identity corrections.** `backfill_corrections()` finds `ccfid`-remap statements and reports them unresolved rather than recording them, because a remapped key changes which row every other correction points at. Applying them before cell corrections is designed in the mitral port spec, not implemented here. - **A stated limit of the lifted state.** A child master's base is a snapshot with its parent's values already baked in at build time. A correction made to the parent afterward does not reach the child until the child is ported to read the parent's view directly, which is also part of that port.