featurizer × dirtyduck
Which food facilities are likely to fail an inspection? (facilities ← inspections)
all-agg config · 272 features · v0.6.0 · live-DB validated
What changed since the Jul-6 snapshot. v0.6.0 rewrote the correlated-subquery aggregator tier to set-based pre-aggregation (one window pre-pass +
GROUP BY per family, ADR-0010). This revalidation first fixed a latent SyntaxError: an over-63-byte companion-CTE name was hash-capped with a ~ separator and emitted unquoted, which PostgreSQL parses as an operator — it hard-blocked the all-agg config on data with long categorical column names (this DB). Result (measured, not projected): the all-agg matrix now materializes on every live DB (it previously errored or would not finish). The times are honest, though: on the larger event streams the advanced tier is still heavy — well above ADR-0010's synthetic <120 s target (that target was never actually run on this DB, since the config SyntaxError'd). The wide variant (all-agg × 14 transformers) still trips unguarded ln/sqrt on negative/zero inputs — the known math-hardening follow-up, not a v0.6.0 regression.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.6.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.4 | materialized |
| all-agg | 272 | 1 | 22169 | 272 | 0 | 356.8 | materialized |
| wide | RuntimeError: Featurizer materialized query execution failed (cannot take logarithm of a negat | err | |||||
shards = ⌈features / 1664⌉ group-queries (PostgreSQL's per-row column cap); 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))