featurizer × donorschoose
Which classroom projects get funded? (projects ← resources, teacher & school history)
all-agg config · 1063 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
- 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.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 | 175 | 1 | 3000 | 177 | 0 | 0.4 | materialized |
| all-agg | 1063 | 1 | 3000 | 1065 | 0 | 7.6 | materialized |
| wide | 36802 | 32 | 3000 | 36804 | 0 | 470.1 | 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 |
|---|---|---|---|---|
| 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))