featurizer × donorschoose
Which classroom projects get funded? (projects ← resources, teacher & school history)
all-agg config · 1063 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
- projects
- max_depth
- 2
- intervals
- P1M, P6M, P12M
- entities
- projects, resources, teacher_history, school_history
- relationships
- projects.entity_id ← resources.entity_id (as-of); projects.teacher_acctid ← teacher_history.teacher_acctid (as-of); projects.schoolid ← school_history.schoolid (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 2012-01-01 → 2013-12-31.
ontology.entities — 3000 rows
| column | type |
|---|---|
| entity_id | bigint |
| projectid | text |
| teacher_acctid | text |
| schoolid | text |
| school_state | text |
| school_metro | text |
| school_charter | boolean |
| school_magnet | boolean |
| teacher_prefix | text |
| teacher_teach_for_america | boolean |
| primary_focus_area | text |
| primary_focus_subject | text |
| resource_type | text |
| poverty_level | text |
| grade_level | text |
| eligible_double_match | boolean |
| total_price | numeric |
| students_reached | integer |
| date_posted | date |
ontology.events — 16682 rows
| column | type |
|---|---|
| event_id | bigint |
| entity_id | bigint |
| date | date |
| project_resource_type | text |
| item_unit_price | numeric |
| item_quantity | numeric |
| line_total | numeric |
| vendorid | text |
ontology.project_funding — 3000 rows
| column | type |
|---|---|
| entity_id | bigint |
| projectid | text |
| date_posted | date |
| total_price | numeric |
| raised_total | numeric |
| raised_4mo | numeric |
| unfunded_4mo | 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 | 175 | 1 | 3000 | 177 | 0 | 1.5 | materialized |
| all-agg | 1063 | 1 | 3000 | 1065 | 0 | 281.1 | materialized |
| wide | timeout | timeout | |||||
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 |
|---|---|---|---|---|
| students_reached | students_reached | variable | Direct column projects.students_reached | 22, 120, 22 |
| total_price | total_price | variable | Direct column projects.total_price | 578.53, 226.68, 814.38 |
| projects.grade_level=Grades 3-5 | projects.grade_level=Grades 3-5 | one_hot | 1 when projects.grade_level = 'Grades 3-5', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| projects.grade_level=Grades 6-8 | projects.grade_level=Grades 6-8 | one_hot | 1 when projects.grade_level = 'Grades 6-8', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 1, 1 |
| projects.grade_level=Grades 9-12 | projects.grade_level=Grades 9-12 | one_hot | 1 when projects.grade_level = 'Grades 9-12', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| projects.grade_level=Grades PreK-2 | projects.grade_level=Grades PreK-2 | one_hot | 1 when projects.grade_level = 'Grades PreK-2', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 1, 0, 0 |
| projects.poverty_level=high poverty | projects.poverty_level=high poverty | one_hot | 1 when projects.poverty_level = 'high poverty', else 0 (fixed-vocabulary one-hot; NULL/out-of-vocabulary rows are all-zero) | 0, 0, 0 |
| BURSTINESS(resources.date) | BURSTINESS(resources.date) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to resources.date | ∅, ∅, ∅ |
| BURSTINESS(resources.date|interval=P12M) | BURSTINESS(resources.date|interval=P12M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to resources.date, over the trailing P12M window | ∅, ∅, ∅ |
| BURSTINESS(resources.date|interval=P1M) | BURSTINESS(resources.date|interval=P1M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to resources.date, over the trailing P1M window | ∅, ∅, ∅ |
| BURSTINESS(resources.date|interval=P6M) | BURSTINESS(resources.date|interval=P6M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to resources.date, over the trailing P6M window | ∅, ∅, ∅ |
| BURSTINESS(school_history.date_posted) | BURSTINESS(school_history.date_posted) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to school_history.date_posted | ∅, ∅, ∅ |
| BURSTINESS(school_history.date_posted|interval=P12M) | BURSTINESS(school_history.date_posted|interval=P12M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to school_history.date_posted, over the trailing P12M window | ∅, ∅, ∅ |
| BURSTINESS(school_history.date_posted|interval=P1M) | BURSTINESS(school_history.date_posted|interval=P1M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to school_history.date_posted, over the trailing P1M window | ∅, ∅, ∅ |
| BURSTINESS(school_history.date_posted|interval=P6M) | BURSTINESS(school_history.date_posted|interval=P6M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to school_history.date_posted, over the trailing P6M window | ∅, ∅, ∅ |
| BURSTINESS(teacher_history.date_posted) | BURSTINESS(teacher_history.date_posted) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to teacher_history.date_posted | ∅, ∅, ∅ |
| BURSTINESS(teacher_history.date_posted|interval=P12M) | BURSTINESS(teacher_history.date_posted|interval=P12M) | derived | Goh-Barabasi burstiness index (-1 to 1), applied to teacher_history.date_posted, over the trailing P12M window | ∅, ∅, ∅ |
| COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|interval=P12M) | COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|int~0e6a801e capped | derived | Weekly cosinor amplitude (sin/cos regression approximation), applied to resources.item_unit_price, over the trailing P12M window | ∅, ∅, ∅ |
| COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|interval=P6M) | COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|int~18561e23 capped | derived | Weekly cosinor amplitude (sin/cos regression approximation), applied to resources.item_unit_price, over the trailing P6M window | ∅, ∅, ∅ |
| COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|interval=P1M) | COSINOR_AMPLITUDE_WEEKLY(resources.item_unit_price|int~2e0699ee capped | derived | Weekly cosinor amplitude (sin/cos regression approximation), applied to resources.item_unit_price, over the trailing P1M window | ∅, ∅, ∅ |
| COSINOR_AMPLITUDE_WEEKLY(school_history.students_reached|interval=P1M) | COSINOR_AMPLITUDE_WEEKLY(school_history.students_reach~361ad217 capped | derived | Weekly cosinor amplitude (sin/cos regression approximation), applied to school_history.students_reached, over the trailing P1M window | ∅, ∅, ∅ |
| ACF_1(resources.item_quantity) | ACF_1(resources.item_quantity) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_quantity | ∅, ∅, ∅ |
| ACF_1(resources.item_quantity|interval=P12M) | ACF_1(resources.item_quantity|interval=P12M) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_quantity, over the trailing P12M window | ∅, ∅, ∅ |
| ACF_1(resources.item_quantity|interval=P1M) | ACF_1(resources.item_quantity|interval=P1M) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_quantity, over the trailing P1M window | ∅, ∅, ∅ |
| ACF_1(resources.item_quantity|interval=P6M) | ACF_1(resources.item_quantity|interval=P6M) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_quantity, over the trailing P6M window | ∅, ∅, ∅ |
| ACF_1(resources.item_unit_price) | ACF_1(resources.item_unit_price) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_unit_price | 0.514, ∅, ∅ |
| ACF_1(resources.item_unit_price|interval=P12M) | ACF_1(resources.item_unit_price|interval=P12M) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_unit_price, over the trailing P12M window | ∅, ∅, ∅ |
| ACF_1(resources.item_unit_price|interval=P1M) | ACF_1(resources.item_unit_price|interval=P1M) | derived | Lag-1 autocorrelation: corr(x_t, x_{t-1}), applied to resources.item_unit_price, over the trailing P1M window | ∅, ∅, ∅ |
A set-based aggregator's SQL (companion pre-agg CTE, ADR-0010)
ENTROPY(resources.project_resource_type)
-SUM(freq::float / total * LN(freq::float / total))