05 · Direct Categoricals, Output & Imputation

View on GitHub · Download .ipynb

Examples 1–4 inspect the config, the synthesized features, and the generated SQL — none of which touch a database. This tutorial executes against PostgreSQL so you see the actual feature matrix, plus the v0.4.0 consumer-facing features: direct-categorical one-hot encoding, the feature manifest, the output formats, and the imputation contract.

Scenario: food inspections — a facilities target with a categorical facility_type and an identifier name, plus an inspections child stream.

Start a database first: just db-up (exports DATABASE_URL), or set DATABASE_URL / PG* to your own PostgreSQL.

1. Setup

import sys
from pathlib import Path

# This tutorial EXECUTES against PostgreSQL (see the note above).
sys.path.insert(0, str(Path.cwd().parent.parent))  # repo root, for `featurizer`
sys.path.insert(0, str(Path.cwd().parent))  # examples/, for `_db`

2. The configuration — variable roles

A direct variable declares a role that controls how it reaches the matrix:

• identifier (name) — excluded from the output, loudly.
• categorical (facility_type) — one-hot encoded against a fixed vocabulary (declared here, or a column’s PostgreSQL ENUM).
• numeric / no role — passthrough.

print(Path("config.yaml").read_text())

# Direct categoricals, output formats, and imputation.
#
# A facilities target carrying a direct categorical (facility_type) and an
# identifier (name), with an inspections child for count-vs-measure imputation.

target: facilities
max_depth: 2

intervals:
  - P1Y

aggregations:
  - count   # count-like  -> imputes to 0
  - mean    # a measure   -> stays NULL, gets a __missing flag
transformations:
  - identity

entities:
  - alias: facilities
    id: license_no
    table: facilities
    temporal_ix: first_seen
    variables:
      # identifier: excluded from the feature output (loudly). A name / license
      # number / exact address is an identifier, not a feature.
      name:
        type: text
        role: identifier
      # categorical: one-hot encoded against a FIXED vocabulary. Featurizer is
      # split-blind and fit-free — it never learns the set from the data. The
      # vocabulary is declared here; alternatively, type the column as a
      # PostgreSQL ENUM and featurizer reads the labels (pass connection=).
      facility_type:
        type: categorical
        role: categorical
        vocabulary: [Bakery, Grocery Store, Restaurant, School]

  - alias: inspections
    id: inspection_id
    table: inspections
    temporal_ix: inspection_date
    variables:
      license_no:
        type: index
      score:
        type: numeric

relationships:
  - parent:
      entity: facilities
      key: license_no
    child:
      entity: inspections
      key: license_no

3. Build the Featurizer (no database needed yet)

A declared vocabulary keeps construction database-free. (Watch for the loud log line excluding the name identifier.)

from featurizer import Featurizer

featurizer = Featurizer("config.yaml")
print("Target entity:", featurizer.target.alias)
print("Intervals:", featurizer.intervals)

2026-07-08 18:22:55.394 | DEBUG    | featurizer.planner:plan:250 - Starting feature build for target facilities


2026-07-08 18:22:55.394 | DEBUG    | featurizer.planner:_build_features:276 - build_features(facilities) depth=0


2026-07-08 18:22:55.394 | DEBUG    | featurizer.planner:_build_features:276 - build_features(inspections) depth=1


2026-07-08 18:22:55.394 | INFO     | featurizer.planner:_build_features:296 - Maximum recursion depth reached at depth 2; materializing inspections without traversing further.


2026-07-08 18:22:55.395 | DEBUG    | featurizer.planner:_build_aggregations:1075 - Processing backward relationship Entity(facilities).license_no -> Entity(inspections).license_no


2026-07-08 18:22:55.395 | WARNING  | featurizer.planner:_apply_direct_roles:1258 - Excluding identifier variable 'facilities.name' from feature output (role: identifier).


Target entity: facilities
Intervals: ['P1Y']

4. The one-hot SQL

Each vocabulary value becomes a deterministic 0/1 column. The ::text cast plus else 0 make a NULL or out-of-vocabulary value an all-zero row — never a crash.

import re

fragments = re.findall(
    r"case when facility_type::text = '[^']*' then 1 else 0 end as \"[^\"]+\"",
    featurizer.query,
)
print("\n".join(fragments))

2026-07-08 18:22:55.398 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'facilities': 5 CTEs, 3882 chars


case when facility_type::text = 'Bakery' then 1 else 0 end as "facilities.facility_type=Bakery"
case when facility_type::text = 'Grocery Store' then 1 else 0 end as "facilities.facility_type=Grocery Store"
case when facility_type::text = 'Restaurant' then 1 else 0 end as "facilities.facility_type=Restaurant"
case when facility_type::text = 'School' then 1 else 0 end as "facilities.facility_type=School"

5. The feature manifest

feature_manifest / manifest_dataframe() map every output column to its full, untruncated label (recovering names the 63-byte identifier cap would otherwise erase), with the kind and, for one-hots, the source_column and value they encode.

Since v0.5.0 each entry also carries lineage — depth (derivation depth), parents (immediate parent labels), source_alias (the relationship/entity stream a derived feature was computed over), the outermost interval window — and a mechanically generated human description.

manifest = featurizer.manifest_dataframe()
manifest[manifest["kind"] == "one_hot"][
    ["column", "label", "kind", "source_column", "value"]
]

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

	column	label	kind	source_column	value
8	facilities.facility_type=Bakery	facilities.facility_type=Bakery	one_hot	facility_type	Bakery
9	facilities.facility_type=Grocery Store	facilities.facility_type=Grocery Store	one_hot	facility_type	Grocery Store
10	facilities.facility_type=Restaurant	facilities.facility_type=Restaurant	one_hot	facility_type	Restaurant
11	facilities.facility_type=School	facilities.facility_type=School	one_hot	facility_type	School

# 'name' (the identifier) is absent; aggregates show up as 'derived'.
print("name in output columns:", "name" in set(manifest["column"]))
manifest["kind"].value_counts()

name in output columns: False





kind
derived    8
one_hot    4
Name: count, dtype: int64

# v0.5.0 lineage + generated descriptions: where each derived column came
# from (parents / stream / window) and a readable explanation of what it is.
manifest[manifest["kind"] == "derived"][
    ["column", "source_alias", "depth", "parents", "interval", "description"]
].head(6)

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

	column	source_alias	depth	parents	interval	description
0	COUNT(inspections.inspection_date)	inspections	1	inspection_date	None	Count of non-null values, applied to inspectio...
1	COUNT(inspections.inspection_date|interval=P1Y)	inspections	1	inspection_date	P1Y	Count of non-null values, applied to inspectio...
2	COUNT(inspections.inspection_id)	inspections	1	inspection_id	None	Count of non-null values, applied to inspectio...
3	COUNT(inspections.inspection_id|interval=P1Y)	inspections	1	inspection_id	P1Y	Count of non-null values, applied to inspectio...
4	COUNT(inspections.license_no)	inspections	1	license_no	None	Count of non-null values, applied to inspectio...
5	COUNT(inspections.license_no|interval=P1Y)	inspections	1	license_no	P1Y	Count of non-null values, applied to inspectio...

6. Execute against PostgreSQL

Seed the example_05 schema, then open one connection with that schema on the search_path and pass it to the output calls.

import runpy
import psycopg

import _db

# Idempotent: drops + recreates example_05 (uses DATABASE_URL / PG*).
runpy.run_path("create_data.py", run_name="__main__")

conn = psycopg.connect(_db.require_conninfo(), autocommit=True)
conn.execute("set search_path to example_05")

✓ Data loaded successfully!

Statistics:
  Facilities: 30 (NULL facility_type: 1)
  Inspections: 60
  Declared vocabulary: ['Bakery', 'Grocery Store', 'Restaurant', 'School']
  Out-of-vocabulary value present: 'Food Truck'

Schema: example_05





<psycopg.Cursor [COMMAND_OK] [IDLE] (host=localhost port=55432 user=postgres database=featurizer_test) at 0x111d03890>

7. The feature matrix

to_dataframe returns a pandas frame indexed by (as_of_date, license_no). The categorical is now one-hot columns; the name identifier is gone.

df = featurizer.to_dataframe(connection=conn)
one_hot = [e.column for e in featurizer.feature_manifest if e.kind == "one_hot"]

print("Shape:", df.shape)
print("'name' in columns:", "name" in df.columns)
df[one_hot].head(8)

2026-07-08 18:22:55.500 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'facilities': 5 CTEs, 3882 chars


Shape: (60, 13)
'name' in columns: False

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

		facilities.facility_type=Bakery	facilities.facility_type=Grocery Store	facilities.facility_type=Restaurant	facilities.facility_type=School
as_of_date	license_no
2024-01-01	1	0	1	0	0
	2	0	0	1	0
	3	0	0	0	1
	4	1	0	0	0
	5	0	1	0	0
	6	0	0	1	0
	7	0	0	0	1
	8	1	0	0	0

8. NULL and out-of-vocabulary → all-zero

The data deliberately includes a facility with a NULL facility_type (license 29) and one with an out-of-vocabulary value 'Food Truck' (license 30). Both must one-hot to an all-zero row.

snap = df.reset_index()
snap = snap[snap["as_of_date"].astype(str) == "2024-01-01"].set_index("license_no")
sample = snap.loc[[1, 29, 30], one_hot]
display(sample)
print("license 29 (NULL) all-zero:        ", bool((sample.loc[29] == 0).all()))
print("license 30 (Food Truck, OOV) zero: ", bool((sample.loc[30] == 0).all()))

.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

	facilities.facility_type=Bakery	facilities.facility_type=Grocery Store	facilities.facility_type=Restaurant	facilities.facility_type=School
license_no
1	0	1	0	0
29	0	0	0	0
30	0	0	0	0

license 29 (NULL) all-zero:         True
license 30 (Food Truck, OOV) zero:  True

9. Imputation — __missing, count vs measure

impute=True is opt-in. It fills count-like features (COUNT) with the structural 0, leaves measures (MEAN) NULL, and — for every column that had NULLs — emits a <feature>__missing 0/1 flag, recorded before the fill. One-hots are never NULL, so they get no __missing flag.

License 5 has zero inspections, so its child aggregates are NULL.

imp = featurizer.to_dataframe(connection=conn, impute=True)


def col(prefix):
    return next(
        c
        for c in imp.columns
        if c.startswith(prefix) and "interval" not in c and not c.endswith("__missing")
    )


cnt, mean = col("COUNT(inspections.inspection_id)"), col("MEAN(inspections.score)")
imp_snap = imp.reset_index()
imp_snap = imp_snap[imp_snap["as_of_date"].astype(str) == "2024-01-01"].set_index(
    "license_no"
)
row = imp_snap.loc[5]  # license 5 has zero inspections

print(f"{cnt} = {row[cnt]}  (count-like -> filled 0)")
print(f"{cnt}__missing = {row[cnt + '__missing']}")
print(f"{mean} = {row[mean]}  (measure -> stays NULL)")
print(f"{mean}__missing = {row[mean + '__missing']}")
print()
print(
    "one-hot columns with a __missing flag:",
    [c for c in imp.columns if c.startswith("facilities.") and c.endswith("__missing")],
)

2026-07-08 18:22:55.536 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'facilities': 5 CTEs, 3882 chars


COUNT(inspections.inspection_id) = 0.0  (count-like -> filled 0)
COUNT(inspections.inspection_id)__missing = 1
MEAN(inspections.score) = nan  (measure -> stays NULL)
MEAN(inspections.score)__missing = 1

one-hot columns with a __missing flag: []

10. Arrow output

to_arrow streams the matrix out of PostgreSQL with binary COPY (no pandas hop): a SQL NULL stays an Arrow null (never NaN), and the keys are ordinary leading columns rather than an index.

table = featurizer.to_arrow(connection=conn)
print("Arrow columns:", len(table.column_names))
print("Leading key columns:", table.column_names[:2])
print("One-hot dtypes:", {c: str(table.schema.field(c).type) for c in one_hot[:2]})

2026-07-08 18:22:55.548 | DEBUG    | featurizer.sql:render:40 - Rendered SQL for target 'facilities': 5 CTEs, 3882 chars


Arrow columns: 15
Leading key columns: ['as_of_date', 'license_no']
One-hot dtypes: {'facilities.facility_type=Bakery': 'int32', 'facilities.facility_type=Grocery Store': 'int32'}

11. Summary

• role: categorical → fixed-vocabulary, fit-free one-hot columns named "<entity>.<col>=<value>"; NULL / out-of-vocabulary → all-zero.
• role: identifier → excluded from the output (loudly).
• Split-blind: the vocabulary is declared or read from a PostgreSQL ENUM — never learned from the data (that train-only transform belongs to the consumer).
• feature_manifest recovers the full intended name for every column — plus lineage (depth, parents, source_alias, interval) and a generated description (v0.5.0).
• to_tables(schema) persists the manifest as "<schema>"."<stem>_manifest" beside the feature-group tables (v0.5.0).
• to_dataframe / to_arrow give the matrix; impute=True adds the count-vs-measure fill and __missing flags.

ENUM alternative: instead of declaring the vocabulary, type the column as a PostgreSQL ENUM and featurizer reads its labels — pass Featurizer('config.yaml', connection=conn) (or it opens one from DATABASE_URL / PG*). See ADR-0007 and the top-level README.