featurizer × chicago311
311 service-request modeling (requests ← area/type backlog streams)
all-agg config · 191 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
- 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.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 | 28 | 1 | 30654 | 30 | 0 | 1.0 | materialized |
| all-agg | 191 | 1 | 30654 | 193 | 0 | 10.1 | materialized |
| wide | RuntimeError: Featurizer materialized query execution failed (cannot take logarithm of zero). | 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 |
|---|---|---|---|---|
| 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))