Write concept features to a DuckDB table, for in-database scoring — materialize_concept

Builds the same per-row lookback-window features as extract_sparse_concept_matrix(), but as a wide table inside DuckDB: row_id (1-based parquet position), person_id, index_date, the outcome y if the parquet has one, and one numeric column per requested concept, named <domain>_<concept_id> exactly as in as_tidymodels_data(). That is the table to point a scoring query at: a model trained in tidymodels can be converted to SQL with orbital::orbital() (workflows) or tidypredict::tidypredict_sql() (glm and friends) and evaluated inside DuckDB on this table, on the training cohort or on any new cohort parquet, with exactly the same window rules, so training and scoring features cannot drift apart.

Usage

materialize_concept_features(
  con,
  features_parquet,
  concepts,
  table = "ml_concept_features",
  lookback_days = 365,
  value = "count",
  include_index_date = FALSE,
  person_col = NULL,
  index_date_col = NULL,
  outcome_col = NULL,
  temporary = FALSE,
  anchor_date = c("auto", "admit_date", "discharge_date"),
  washin_buffer_days = 0,
  washin_buffer_hours = 0
)

Arguments

con: Active DBI connection to DuckDB holding the OMOP CDM tables (read-only is fine). If the vocabulary lives in an attached central_vocab catalog, concept names are picked up automatically.
features_parquet: Path (or glob) of the cohort parquet, one row per index event, with a person id and an index date column; an outcome column is carried through if present. Columns are auto-detected (person_id/subject_id/patid; index_date/cohort_start_date/end_date/admit_date...; y/outcome_flag/label) or set with person_col, index_date_col, outcome_col.
concepts: A data.frame with domain and concept_id columns: typically res$concepts, or the subset returned by model_concepts().
table: Name of the table to create (replaced if it exists).
lookback_days: Window length in days, or NULL for all prior history.
value: "count" (records per concept) or "binary" (presence).
include_index_date: Also use events dated exactly on the index date (default FALSE).
person_col, index_date_col, outcome_col: Optional explicit column names in the parquet.
temporary: Create a temporary table (default FALSE).
anchor_date: Strategy for selecting index date column from cohort parquet: "auto" (default), "admit_date" (prioritizes admission date to prevent in-hospital leakage), or "discharge_date" (prioritizes discharge date).
washin_buffer_days: Post-anchor observation buffer days to include in the window (default 0).
washin_buffer_hours: Post-anchor observation buffer hours (e.g. 24 for 24h post-admission, default 0).

Value

Invisibly, a list with table, features (column names created) and n_rows.

Details

The SQL has one expression per concept, so request only what the model needs (model_concepts()).

Examples

if (FALSE) { # \dontrun{
used <- model_concepts(fit, res$concepts)
orb <- orbital::orbital(fit)
scores <- orbital::augment(orb, dplyr::tbl(con, "to_score")) |> dplyr::collect()
} # }