featurizer × dirtyduck
Which food facilities are likely to fail an inspection? (facilities ← inspections)
all-agg config · 272 features · v0.8.0 · live-DB validated
What changed since the v0.6.0 snapshot. Two release cycles of measured fixes. 0.7.0: the two-window drift aggregators went set-based (ADR-0012, dirtyduck all-agg 356.8s → 27.6s), the executor now
ANALYZEs as_of_dates first (ADR-0013, donorschoose all-agg 294s → 7.5s — the planner had assumed a ~2550-row default for a never-analyzed table), ln/log/sqrt became domain-guarded (ADR-0011: NULL out-of-domain, no more ln-of-negative aborts), and conservative planner tuning (work_mem 64MB, collapse limits 20) became an executor default. 0.8.0: column groups are packed by dependency lineage and bounded by a window-function budget (ADR-0005 amendment) — PostgreSQL's planning memory is superlinear in same-statement window-function count, and the old order-based chunking gave every group a CTE closure spanning most of the plan (donorschoose wide: 979-CTE closures, backend OOM-killed during a plain EXPLAIN). Result (measured, not projected): all-agg materializes in seconds on every live DB, and the wide variant (all-agg × 14 transformers) now materializes on all three databases — including donorschoose (~36.8k columns), which crashed the backend in every previous snapshot.1 · Featurizer configuration
- target
- facilities
- max_depth
- 2
- intervals
- P1M, P3M, P6M
- entities
- facilities, inspections
- relationships
- facilities.entity_id ← inspections.entity_id (as-of)
- aggregations
- the 65-primitive default-active set (incl. the set-based gap/categorical/sequence families)
2 · Live database structure
Event knowledge dates span 2014-01-02 → 2017-12-29.
ontology.entities — 22169 rows
| column | type |
|---|---|
| entity_id | bigint |
| license_num | integer |
| facility | text |
| facility_aka | text |
| facility_type | text |
| address | text |
| zip_code | text |
| location | USER-DEFINED |
| start_time | date |
| end_time | date |
| activity_period | daterange |
ontology.events — 74191 rows
| column | type |
|---|---|
| event_id | integer |
| entity_id | bigint |
| type | USER-DEFINED |
| date | date |
| risk | USER-DEFINED |
| result | USER-DEFINED |
| violations | jsonb |
3 · Full-cohort materialization (v0.8.0)
Each variant materialized through featurizer's grouped/TEMP-shard path (to_dataframe(connection=…)) at one as-of date; no permanent writes. narrow = the triage config's own primitives; all-agg = the full 65-aggregator default set with identity; wide = all-agg × 14 transformers (lags, rolling, EMA, cusum…).
| variant | features | shards | rows | cols | dup-names | seconds | status |
|---|---|---|---|---|---|---|---|
| narrow | 147 | 1 | 22169 | 147 | 0 | 3.0 | materialized |
| all-agg | 272 | 1 | 22169 | 272 | 0 | 7.5 | materialized |
| wide | 1252 | 1 | 22169 | 1252 | 0 | 63.2 | materialized |
shards = actual column-group queries emitted (lineage + window-budget packing, ADR-0005 amendment); dup-names = duplicate output column identifiers (0 = the 63-byte-cap hashing is collision-free).
4 · Generated features — a sample
Full intended label, the rendered short-name (the ≤63-byte column identifier; hash-capped when long), kind, a generated description, and a few real values from the live DB.
| label | short-name (column) | kind | description | values |
|---|---|---|---|---|
| facilities.facility_type=bakery | facilities.facility_type=bakery | one_hot | 1 when facilities.facility_type = 'bakery', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| facilities.facility_type=catering | facilities.facility_type=catering | one_hot | 1 when facilities.facility_type = 'catering', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| facilities.facility_type=children's services facility | facilities.facility_type=children's services facility | one_hot | 1 when facilities.facility_type = 'children's services facility', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 1, 0 |
| facilities.facility_type=daycare (2 - 6 years) | facilities.facility_type=daycare (2 - 6 years) | one_hot | 1 when facilities.facility_type = 'daycare (2 - 6 years)', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 1, 0, 0 |
| facilities.facility_type=daycare above and under 2 years | facilities.facility_type=daycare above and under 2 years | one_hot | 1 when facilities.facility_type = 'daycare above and under 2 years', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| BURSTINESS(inspections.date) | BURSTINESS(inspections.date) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to inspections.date | ∅, -0.3097720994773271657, -0.6389954919812702494 |
| BURSTINESS(inspections.date|interval=P1M) | BURSTINESS(inspections.date|interval=P1M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to inspections.date, over the trailing P1M window | ∅, ∅, ∅ |
| BURSTINESS(inspections.date|interval=P3M) | BURSTINESS(inspections.date|interval=P3M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to inspections.date, over the trailing P3M window | ∅, ∅, ∅ |
| BURSTINESS(inspections.date|interval=P6M) | BURSTINESS(inspections.date|interval=P6M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to inspections.date, over the trailing P6M window | ∅, ∅, ∅ |
| ENTROPY(inspections.result) | ENTROPY(inspections.result) | derived | Shannon entropy of categorical distribution, applied to inspections.result | 0.693, -0, -0 |
| ENTROPY(inspections.result|interval=P1M) | ENTROPY(inspections.result|interval=P1M) | derived | Shannon entropy of categorical distribution, applied to inspections.result, over the trailing P1M window | ∅, -0, ∅ |
| ENTROPY(inspections.result|interval=P3M) | ENTROPY(inspections.result|interval=P3M) | derived | Shannon entropy of categorical distribution, applied to inspections.result, over the trailing P3M window | ∅, -0, ∅ |
| ENTROPY(inspections.result|interval=P6M) | ENTROPY(inspections.result|interval=P6M) | derived | Shannon entropy of categorical distribution, applied to inspections.result, over the trailing P6M window | ∅, -0, ∅ |
| ENTROPY(inspections.risk) | ENTROPY(inspections.risk) | derived | Shannon entropy of categorical distribution, applied to inspections.risk | -0, -0, -0 |
| ENTROPY(inspections.risk|interval=P1M) | ENTROPY(inspections.risk|interval=P1M) | derived | Shannon entropy of categorical distribution, applied to inspections.risk, over the trailing P1M window | ∅, -0, ∅ |
| AGE_IN_SYSTEM(inspections.date) | AGE_IN_SYSTEM(inspections.date) | derived | Alias of tenure: days since the first observed event, applied to inspections.date | 1460, 1460, 1460 |
| AGE_IN_SYSTEM(inspections.date|interval=P1M) | AGE_IN_SYSTEM(inspections.date|interval=P1M) | derived | Alias of tenure: days since the first observed event, applied to inspections.date, over the trailing P1M window | ∅, 14, ∅ |
| AGE_IN_SYSTEM(inspections.date|interval=P3M) | AGE_IN_SYSTEM(inspections.date|interval=P3M) | derived | Alias of tenure: days since the first observed event, applied to inspections.date, over the trailing P3M window | ∅, 14, ∅ |
| AGE_IN_SYSTEM(inspections.date|interval=P6M) | AGE_IN_SYSTEM(inspections.date|interval=P6M) | derived | Alias of tenure: days since the first observed event, applied to inspections.date, over the trailing P6M window | ∅, 14, ∅ |
A set-based aggregator's SQL (companion pre-agg CTE, ADR-0010)
ENTROPY(inspections.result)
-SUM(freq::float / total * LN(freq::float / total))