featurizer × chicago311
311 service-request modeling (requests ← area/type backlog streams)
all-agg config · 191 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
- requests
- max_depth
- 2
- intervals
- P7D, P30D, P90D
- entities
- requests, area_backlog, type_demand
- relationships
- requests.community_area ← area_backlog.community_area (as-of); requests.sr_short_code ← type_demand.sr_short_code (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 2019-01-01 09:15:54 → 2019-12-31 23:46:07.
ontology.entities — 30654 rows
| column | type |
|---|---|
| entity_id | bigint |
| sr_number | text |
| sr_type | text |
| sr_short_code | text |
| owner_department | text |
| origin | text |
| community_area | integer |
| ward | integer |
| police_district | text |
| zip_code | text |
| latitude | double precision |
| longitude | double precision |
| created_day_of_week | integer |
| created_hour | integer |
| created_month | integer |
| created_date | timestamp without time zone |
| closed_date | timestamp without time zone |
| status | text |
ontology.events — 30654 rows
| column | type |
|---|---|
| event_id | bigint |
| entity_id | bigint |
| community_area | integer |
| sr_type | text |
| sr_short_code | text |
| date | timestamp without time zone |
ontology.request_resolution — 30654 rows
| column | type |
|---|---|
| entity_id | bigint |
| sr_number | text |
| created_date | timestamp without time zone |
| closed_date | timestamp without time zone |
| status | text |
| days_to_close | numeric |
| slow_14d | boolean |
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 | 28 | 1 | 30654 | 30 | 0 | 0.8 | materialized |
| all-agg | 191 | 1 | 30654 | 193 | 0 | 6.0 | materialized |
| wide | 907 | 1 | 30654 | 909 | 0 | 49.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 |
|---|---|---|---|---|
| created_day_of_week | created_day_of_week | variable | Direct column requests.created_day_of_week | 3, 3, 3 |
| created_hour | created_hour | variable | Direct column requests.created_hour | 11, 12, 9 |
| created_month | created_month | variable | Direct column requests.created_month | 1, 1, 1 |
| ward | ward | variable | Direct column requests.ward | 1, 37, 28 |
| requests.origin=Alderman's Office | requests.origin=Alderman's Office | one_hot | 1 when requests.origin = 'Alderman's Office', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| requests.origin=Internet | requests.origin=Internet | one_hot | 1 when requests.origin = 'Internet', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 1, 0 |
| requests.origin=Mass Entry | requests.origin=Mass Entry | one_hot | 1 when requests.origin = 'Mass Entry', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| requests.origin=Mobile Device | requests.origin=Mobile Device | one_hot | 1 when requests.origin = 'Mobile Device', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 1, 0, 0 |
| requests.origin=Phone Call | requests.origin=Phone Call | one_hot | 1 when requests.origin = 'Phone Call', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| BURSTINESS(area_backlog.date) | BURSTINESS(area_backlog.date) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to area_backlog.date | 0.51466229220202605355, 0.45324959343636047283, 0.45324959343636047283 |
| BURSTINESS(area_backlog.date|interval=P30D) | BURSTINESS(area_backlog.date|interval=P30D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to area_backlog.date, over the trailing P30D window | 0.43049995334071474931, 0.35086606283489283593, 0.35086606283489283593 |
| BURSTINESS(area_backlog.date|interval=P7D) | BURSTINESS(area_backlog.date|interval=P7D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to area_backlog.date, over the trailing P7D window | 0.30263063447014416207, 0.24678646356611191735, 0.24678646356611191735 |
| BURSTINESS(area_backlog.date|interval=P90D) | BURSTINESS(area_backlog.date|interval=P90D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to area_backlog.date, over the trailing P90D window | 0.39340512520933384888, 0.38171798666384286943, 0.38171798666384286943 |
| BURSTINESS(type_demand.date) | BURSTINESS(type_demand.date) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to type_demand.date | 0.42405485102549013765, 0.42405485102549013765, 0.31310700304330119141 |
| BURSTINESS(type_demand.date|interval=P30D) | BURSTINESS(type_demand.date|interval=P30D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to type_demand.date, over the trailing P30D window | 0.28794997165380691715, 0.28794997165380691715, 0.26203256495682712591 |
| BURSTINESS(type_demand.date|interval=P7D) | BURSTINESS(type_demand.date|interval=P7D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to type_demand.date, over the trailing P7D window | 0.21874328898052731159, 0.21874328898052731159, 0.28653089733177411365 |
| BURSTINESS(type_demand.date|interval=P90D) | BURSTINESS(type_demand.date|interval=P90D) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to type_demand.date, over the trailing P90D window | 0.27891998035148428341, 0.27891998035148428341, 0.29308621372487645005 |
| ENTROPY(area_backlog.sr_type) | ENTROPY(area_backlog.sr_type) | derived | Shannon entropy of categorical distribution, applied to area_backlog.sr_type | 1.36, 1.44, 1.44 |
| ENTROPY(area_backlog.sr_type|interval=P30D) | ENTROPY(area_backlog.sr_type|interval=P30D) | derived | Shannon entropy of categorical distribution, applied to area_backlog.sr_type, over the trailing P30D window | 0.958, 1.22, 1.22 |
| AGE_IN_SYSTEM(area_backlog.date) | AGE_IN_SYSTEM(area_backlog.date) | derived | Alias of tenure: days since the first observed event, applied to area_backlog.date | 365, 365, 365 |
| AGE_IN_SYSTEM(area_backlog.date|interval=P30D) | AGE_IN_SYSTEM(area_backlog.date|interval=P30D) | derived | Alias of tenure: days since the first observed event, applied to area_backlog.date, over the trailing P30D window | 30, 30, 30 |
| AGE_IN_SYSTEM(area_backlog.date|interval=P7D) | AGE_IN_SYSTEM(area_backlog.date|interval=P7D) | derived | Alias of tenure: days since the first observed event, applied to area_backlog.date, over the trailing P7D window | 7, 6, 6 |
| AGE_IN_SYSTEM(area_backlog.date|interval=P90D) | AGE_IN_SYSTEM(area_backlog.date|interval=P90D) | derived | Alias of tenure: days since the first observed event, applied to area_backlog.date, over the trailing P90D window | 90, 90, 90 |
| AGE_IN_SYSTEM(type_demand.date) | AGE_IN_SYSTEM(type_demand.date) | derived | Alias of tenure: days since the first observed event, applied to type_demand.date | 365, 365, 365 |
| AGE_IN_SYSTEM(type_demand.date|interval=P30D) | AGE_IN_SYSTEM(type_demand.date|interval=P30D) | derived | Alias of tenure: days since the first observed event, applied to type_demand.date, over the trailing P30D window | 30, 30, 30 |
| AGE_IN_SYSTEM(type_demand.date|interval=P7D) | AGE_IN_SYSTEM(type_demand.date|interval=P7D) | derived | Alias of tenure: days since the first observed event, applied to type_demand.date, over the trailing P7D window | 6, 6, 7 |
| AGE_IN_SYSTEM(type_demand.date|interval=P90D) | AGE_IN_SYSTEM(type_demand.date|interval=P90D) | derived | Alias of tenure: days since the first observed event, applied to type_demand.date, over the trailing P90D window | 90, 90, 90 |
A set-based aggregator's SQL (companion pre-agg CTE, ADR-0010)
ENTROPY(area_backlog.sr_type)
-SUM(freq::float / total * LN(freq::float / total))