{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell01",
   "metadata": {},
   "source": [
    "# Featurizer Tutorial: Direct Categoricals, Output & Imputation\n",
    "\n",
    "Examples 1–4 inspect the config, the synthesized features, and the generated\n",
    "SQL — none of which touch a database. **This tutorial executes against\n",
    "PostgreSQL** so you see the actual feature matrix, plus the v0.4.0\n",
    "consumer-facing features: direct-categorical one-hot encoding, the feature\n",
    "manifest, the output formats, and the imputation contract.\n",
    "\n",
    "**Scenario:** food inspections — a `facilities` target with a categorical\n",
    "`facility_type` and an identifier `name`, plus an `inspections` child stream.\n",
    "\n",
    "> Start a database first: `just db-up` (exports `DATABASE_URL`), or set\n",
    "> `DATABASE_URL` / `PG*` to your own PostgreSQL."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell02",
   "metadata": {},
   "source": [
    "## 1. Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cell03",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.160525Z",
     "iopub.status.busy": "2026-07-09T00:22:55.160394Z",
     "iopub.status.idle": "2026-07-09T00:22:55.163905Z",
     "shell.execute_reply": "2026-07-09T00:22:55.163411Z"
    }
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "from pathlib import Path\n",
    "\n",
    "# This tutorial EXECUTES against PostgreSQL (see the note above).\n",
    "sys.path.insert(0, str(Path.cwd().parent.parent))  # repo root, for `featurizer`\n",
    "sys.path.insert(0, str(Path.cwd().parent))  # examples/, for `_db`"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell04",
   "metadata": {},
   "source": [
    "## 2. The configuration — variable roles\n",
    "\n",
    "A direct variable declares a `role` that controls how it reaches the matrix:\n",
    "\n",
    "- `identifier` (`name`) — excluded from the output, loudly.\n",
    "- `categorical` (`facility_type`) — one-hot encoded against a **fixed**\n",
    "  vocabulary (declared here, or a column's PostgreSQL `ENUM`).\n",
    "- `numeric` / no role — passthrough."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cell05",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.165814Z",
     "iopub.status.busy": "2026-07-09T00:22:55.165682Z",
     "iopub.status.idle": "2026-07-09T00:22:55.168384Z",
     "shell.execute_reply": "2026-07-09T00:22:55.167583Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "# Direct categoricals, output formats, and imputation.\n",
      "#\n",
      "# A facilities target carrying a direct categorical (facility_type) and an\n",
      "# identifier (name), with an inspections child for count-vs-measure imputation.\n",
      "\n",
      "target: facilities\n",
      "max_depth: 2\n",
      "\n",
      "intervals:\n",
      "  - P1Y\n",
      "\n",
      "aggregations:\n",
      "  - count   # count-like  -> imputes to 0\n",
      "  - mean    # a measure   -> stays NULL, gets a __missing flag\n",
      "transformations:\n",
      "  - identity\n",
      "\n",
      "entities:\n",
      "  - alias: facilities\n",
      "    id: license_no\n",
      "    table: facilities\n",
      "    temporal_ix: first_seen\n",
      "    variables:\n",
      "      # identifier: excluded from the feature output (loudly). A name / license\n",
      "      # number / exact address is an identifier, not a feature.\n",
      "      name:\n",
      "        type: text\n",
      "        role: identifier\n",
      "      # categorical: one-hot encoded against a FIXED vocabulary. Featurizer is\n",
      "      # split-blind and fit-free — it never learns the set from the data. The\n",
      "      # vocabulary is declared here; alternatively, type the column as a\n",
      "      # PostgreSQL ENUM and featurizer reads the labels (pass connection=).\n",
      "      facility_type:\n",
      "        type: categorical\n",
      "        role: categorical\n",
      "        vocabulary: [Bakery, Grocery Store, Restaurant, School]\n",
      "\n",
      "  - alias: inspections\n",
      "    id: inspection_id\n",
      "    table: inspections\n",
      "    temporal_ix: inspection_date\n",
      "    variables:\n",
      "      license_no:\n",
      "        type: index\n",
      "      score:\n",
      "        type: numeric\n",
      "\n",
      "relationships:\n",
      "  - parent:\n",
      "      entity: facilities\n",
      "      key: license_no\n",
      "    child:\n",
      "      entity: inspections\n",
      "      key: license_no\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print(Path(\"config.yaml\").read_text())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell06",
   "metadata": {},
   "source": [
    "## 3. Build the Featurizer (no database needed yet)\n",
    "\n",
    "A **declared** vocabulary keeps construction database-free. (Watch for the\n",
    "loud log line excluding the `name` identifier.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "cell07",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.170166Z",
     "iopub.status.busy": "2026-07-09T00:22:55.170028Z",
     "iopub.status.idle": "2026-07-09T00:22:55.397081Z",
     "shell.execute_reply": "2026-07-09T00:22:55.396533Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.394\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36mplan\u001b[0m:\u001b[36m250\u001b[0m - \u001b[34m\u001b[1mStarting feature build for target facilities\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.394\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m276\u001b[0m - \u001b[34m\u001b[1mbuild_features(facilities) depth=0\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.394\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m276\u001b[0m - \u001b[34m\u001b[1mbuild_features(inspections) depth=1\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.394\u001b[0m | \u001b[1mINFO    \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m296\u001b[0m - \u001b[1mMaximum recursion depth reached at depth 2; materializing inspections without traversing further.\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.395\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1075\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(facilities).license_no -> Entity(inspections).license_no\u001b[0m\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.395\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_apply_direct_roles\u001b[0m:\u001b[36m1258\u001b[0m - \u001b[33m\u001b[1mExcluding identifier variable 'facilities.name' from feature output (role: identifier).\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Target entity: facilities\n",
      "Intervals: ['P1Y']\n"
     ]
    }
   ],
   "source": [
    "from featurizer import Featurizer\n",
    "\n",
    "featurizer = Featurizer(\"config.yaml\")\n",
    "print(\"Target entity:\", featurizer.target.alias)\n",
    "print(\"Intervals:\", featurizer.intervals)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell08",
   "metadata": {},
   "source": [
    "## 4. The one-hot SQL\n",
    "\n",
    "Each vocabulary value becomes a deterministic `0/1` column. The `::text` cast\n",
    "plus `else 0` make a NULL **or** out-of-vocabulary value an all-zero row —\n",
    "never a crash."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cell09",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.398197Z",
     "iopub.status.busy": "2026-07-09T00:22:55.398102Z",
     "iopub.status.idle": "2026-07-09T00:22:55.400016Z",
     "shell.execute_reply": "2026-07-09T00:22:55.399707Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.398\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'facilities': 5 CTEs, 3882 chars\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "case when facility_type::text = 'Bakery' then 1 else 0 end as \"facilities.facility_type=Bakery\"\n",
      "case when facility_type::text = 'Grocery Store' then 1 else 0 end as \"facilities.facility_type=Grocery Store\"\n",
      "case when facility_type::text = 'Restaurant' then 1 else 0 end as \"facilities.facility_type=Restaurant\"\n",
      "case when facility_type::text = 'School' then 1 else 0 end as \"facilities.facility_type=School\"\n"
     ]
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "fragments = re.findall(\n",
    "    r\"case when facility_type::text = '[^']*' then 1 else 0 end as \\\"[^\\\"]+\\\"\",\n",
    "    featurizer.query,\n",
    ")\n",
    "print(\"\\n\".join(fragments))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell10",
   "metadata": {},
   "source": [
    "## 5. The feature manifest\n",
    "\n",
    "`feature_manifest` / `manifest_dataframe()` map every output column to its\n",
    "full, untruncated `label` (recovering names the 63-byte identifier cap would\n",
    "otherwise erase), with the `kind` and, for one-hots, the `source_column` and\n",
    "`value` they encode.\n",
    "\n",
    "Since v0.5.0 each entry also carries **lineage** — `depth` (derivation\n",
    "depth), `parents` (immediate parent labels), `source_alias` (the\n",
    "relationship/entity stream a derived feature was computed over), the\n",
    "outermost `interval` window — and a mechanically generated human\n",
    "`description`.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cell11",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.400913Z",
     "iopub.status.busy": "2026-07-09T00:22:55.400857Z",
     "iopub.status.idle": "2026-07-09T00:22:55.410416Z",
     "shell.execute_reply": "2026-07-09T00:22:55.410119Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column</th>\n",
       "      <th>label</th>\n",
       "      <th>kind</th>\n",
       "      <th>source_column</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>facilities.facility_type=Bakery</td>\n",
       "      <td>facilities.facility_type=Bakery</td>\n",
       "      <td>one_hot</td>\n",
       "      <td>facility_type</td>\n",
       "      <td>Bakery</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>facilities.facility_type=Grocery Store</td>\n",
       "      <td>facilities.facility_type=Grocery Store</td>\n",
       "      <td>one_hot</td>\n",
       "      <td>facility_type</td>\n",
       "      <td>Grocery Store</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>facilities.facility_type=Restaurant</td>\n",
       "      <td>facilities.facility_type=Restaurant</td>\n",
       "      <td>one_hot</td>\n",
       "      <td>facility_type</td>\n",
       "      <td>Restaurant</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>facilities.facility_type=School</td>\n",
       "      <td>facilities.facility_type=School</td>\n",
       "      <td>one_hot</td>\n",
       "      <td>facility_type</td>\n",
       "      <td>School</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                    column  \\\n",
       "8          facilities.facility_type=Bakery   \n",
       "9   facilities.facility_type=Grocery Store   \n",
       "10     facilities.facility_type=Restaurant   \n",
       "11         facilities.facility_type=School   \n",
       "\n",
       "                                     label     kind  source_column  \\\n",
       "8          facilities.facility_type=Bakery  one_hot  facility_type   \n",
       "9   facilities.facility_type=Grocery Store  one_hot  facility_type   \n",
       "10     facilities.facility_type=Restaurant  one_hot  facility_type   \n",
       "11         facilities.facility_type=School  one_hot  facility_type   \n",
       "\n",
       "            value  \n",
       "8          Bakery  \n",
       "9   Grocery Store  \n",
       "10     Restaurant  \n",
       "11         School  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "manifest = featurizer.manifest_dataframe()\n",
    "manifest[manifest[\"kind\"] == \"one_hot\"][\n",
    "    [\"column\", \"label\", \"kind\", \"source_column\", \"value\"]\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cell12",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.411354Z",
     "iopub.status.busy": "2026-07-09T00:22:55.411301Z",
     "iopub.status.idle": "2026-07-09T00:22:55.414040Z",
     "shell.execute_reply": "2026-07-09T00:22:55.413703Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "name in output columns: False\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "kind\n",
       "derived    8\n",
       "one_hot    4\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 'name' (the identifier) is absent; aggregates show up as 'derived'.\n",
    "print(\"name in output columns:\", \"name\" in set(manifest[\"column\"]))\n",
    "manifest[\"kind\"].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2a895b28",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.414873Z",
     "iopub.status.busy": "2026-07-09T00:22:55.414825Z",
     "iopub.status.idle": "2026-07-09T00:22:55.417911Z",
     "shell.execute_reply": "2026-07-09T00:22:55.417546Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>column</th>\n",
       "      <th>source_alias</th>\n",
       "      <th>depth</th>\n",
       "      <th>parents</th>\n",
       "      <th>interval</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>COUNT(inspections.inspection_date)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>inspection_date</td>\n",
       "      <td>None</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>COUNT(inspections.inspection_date|interval=P1Y)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>inspection_date</td>\n",
       "      <td>P1Y</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>COUNT(inspections.inspection_id)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>inspection_id</td>\n",
       "      <td>None</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>COUNT(inspections.inspection_id|interval=P1Y)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>inspection_id</td>\n",
       "      <td>P1Y</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>COUNT(inspections.license_no)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>license_no</td>\n",
       "      <td>None</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>COUNT(inspections.license_no|interval=P1Y)</td>\n",
       "      <td>inspections</td>\n",
       "      <td>1</td>\n",
       "      <td>license_no</td>\n",
       "      <td>P1Y</td>\n",
       "      <td>Count of non-null values, applied to inspectio...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            column source_alias  depth  \\\n",
       "0               COUNT(inspections.inspection_date)  inspections      1   \n",
       "1  COUNT(inspections.inspection_date|interval=P1Y)  inspections      1   \n",
       "2                 COUNT(inspections.inspection_id)  inspections      1   \n",
       "3    COUNT(inspections.inspection_id|interval=P1Y)  inspections      1   \n",
       "4                    COUNT(inspections.license_no)  inspections      1   \n",
       "5       COUNT(inspections.license_no|interval=P1Y)  inspections      1   \n",
       "\n",
       "           parents interval                                        description  \n",
       "0  inspection_date     None  Count of non-null values, applied to inspectio...  \n",
       "1  inspection_date      P1Y  Count of non-null values, applied to inspectio...  \n",
       "2    inspection_id     None  Count of non-null values, applied to inspectio...  \n",
       "3    inspection_id      P1Y  Count of non-null values, applied to inspectio...  \n",
       "4       license_no     None  Count of non-null values, applied to inspectio...  \n",
       "5       license_no      P1Y  Count of non-null values, applied to inspectio...  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# v0.5.0 lineage + generated descriptions: where each derived column came\n",
    "# from (parents / stream / window) and a readable explanation of what it is.\n",
    "manifest[manifest[\"kind\"] == \"derived\"][\n",
    "    [\"column\", \"source_alias\", \"depth\", \"parents\", \"interval\", \"description\"]\n",
    "].head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell13",
   "metadata": {},
   "source": [
    "## 6. Execute against PostgreSQL\n",
    "\n",
    "Seed the `example_05` schema, then open one connection with that schema on the\n",
    "search_path and pass it to the output calls."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cell14",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.418768Z",
     "iopub.status.busy": "2026-07-09T00:22:55.418714Z",
     "iopub.status.idle": "2026-07-09T00:22:55.498530Z",
     "shell.execute_reply": "2026-07-09T00:22:55.498140Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "✓ Data loaded successfully!\n",
      "\n",
      "Statistics:\n",
      "  Facilities: 30 (NULL facility_type: 1)\n",
      "  Inspections: 60\n",
      "  Declared vocabulary: ['Bakery', 'Grocery Store', 'Restaurant', 'School']\n",
      "  Out-of-vocabulary value present: 'Food Truck'\n",
      "\n",
      "Schema: example_05\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<psycopg.Cursor [COMMAND_OK] [IDLE] (host=localhost port=55432 user=postgres database=featurizer_test) at 0x111d03890>"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import runpy\n",
    "import psycopg\n",
    "\n",
    "import _db\n",
    "\n",
    "# Idempotent: drops + recreates example_05 (uses DATABASE_URL / PG*).\n",
    "runpy.run_path(\"create_data.py\", run_name=\"__main__\")\n",
    "\n",
    "conn = psycopg.connect(_db.require_conninfo(), autocommit=True)\n",
    "conn.execute(\"set search_path to example_05\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell15",
   "metadata": {},
   "source": [
    "## 7. The feature matrix\n",
    "\n",
    "`to_dataframe` returns a pandas frame indexed by `(as_of_date, license_no)`.\n",
    "The categorical is now one-hot columns; the `name` identifier is gone."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cell16",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.499590Z",
     "iopub.status.busy": "2026-07-09T00:22:55.499525Z",
     "iopub.status.idle": "2026-07-09T00:22:55.529140Z",
     "shell.execute_reply": "2026-07-09T00:22:55.528691Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.500\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'facilities': 5 CTEs, 3882 chars\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Shape: (60, 13)\n",
      "'name' in columns: False\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>facilities.facility_type=Bakery</th>\n",
       "      <th>facilities.facility_type=Grocery Store</th>\n",
       "      <th>facilities.facility_type=Restaurant</th>\n",
       "      <th>facilities.facility_type=School</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>as_of_date</th>\n",
       "      <th>license_no</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"8\" valign=\"top\">2024-01-01</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       facilities.facility_type=Bakery  \\\n",
       "as_of_date license_no                                    \n",
       "2024-01-01 1                                         0   \n",
       "           2                                         0   \n",
       "           3                                         0   \n",
       "           4                                         1   \n",
       "           5                                         0   \n",
       "           6                                         0   \n",
       "           7                                         0   \n",
       "           8                                         1   \n",
       "\n",
       "                       facilities.facility_type=Grocery Store  \\\n",
       "as_of_date license_no                                           \n",
       "2024-01-01 1                                                1   \n",
       "           2                                                0   \n",
       "           3                                                0   \n",
       "           4                                                0   \n",
       "           5                                                1   \n",
       "           6                                                0   \n",
       "           7                                                0   \n",
       "           8                                                0   \n",
       "\n",
       "                       facilities.facility_type=Restaurant  \\\n",
       "as_of_date license_no                                        \n",
       "2024-01-01 1                                             0   \n",
       "           2                                             1   \n",
       "           3                                             0   \n",
       "           4                                             0   \n",
       "           5                                             0   \n",
       "           6                                             1   \n",
       "           7                                             0   \n",
       "           8                                             0   \n",
       "\n",
       "                       facilities.facility_type=School  \n",
       "as_of_date license_no                                   \n",
       "2024-01-01 1                                         0  \n",
       "           2                                         0  \n",
       "           3                                         1  \n",
       "           4                                         0  \n",
       "           5                                         0  \n",
       "           6                                         0  \n",
       "           7                                         1  \n",
       "           8                                         0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = featurizer.to_dataframe(connection=conn)\n",
    "one_hot = [e.column for e in featurizer.feature_manifest if e.kind == \"one_hot\"]\n",
    "\n",
    "print(\"Shape:\", df.shape)\n",
    "print(\"'name' in columns:\", \"name\" in df.columns)\n",
    "df[one_hot].head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell17",
   "metadata": {},
   "source": [
    "## 8. NULL and out-of-vocabulary → all-zero\n",
    "\n",
    "The data deliberately includes a facility with a NULL `facility_type`\n",
    "(license 29) and one with an out-of-vocabulary value `'Food Truck'`\n",
    "(license 30). Both must one-hot to an all-zero row."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cell18",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.530265Z",
     "iopub.status.busy": "2026-07-09T00:22:55.530193Z",
     "iopub.status.idle": "2026-07-09T00:22:55.534287Z",
     "shell.execute_reply": "2026-07-09T00:22:55.533985Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>facilities.facility_type=Bakery</th>\n",
       "      <th>facilities.facility_type=Grocery Store</th>\n",
       "      <th>facilities.facility_type=Restaurant</th>\n",
       "      <th>facilities.facility_type=School</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>license_no</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            facilities.facility_type=Bakery  \\\n",
       "license_no                                    \n",
       "1                                         0   \n",
       "29                                        0   \n",
       "30                                        0   \n",
       "\n",
       "            facilities.facility_type=Grocery Store  \\\n",
       "license_no                                           \n",
       "1                                                1   \n",
       "29                                               0   \n",
       "30                                               0   \n",
       "\n",
       "            facilities.facility_type=Restaurant  \\\n",
       "license_no                                        \n",
       "1                                             0   \n",
       "29                                            0   \n",
       "30                                            0   \n",
       "\n",
       "            facilities.facility_type=School  \n",
       "license_no                                   \n",
       "1                                         0  \n",
       "29                                        0  \n",
       "30                                        0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "license 29 (NULL) all-zero:         True\n",
      "license 30 (Food Truck, OOV) zero:  True\n"
     ]
    }
   ],
   "source": [
    "snap = df.reset_index()\n",
    "snap = snap[snap[\"as_of_date\"].astype(str) == \"2024-01-01\"].set_index(\"license_no\")\n",
    "sample = snap.loc[[1, 29, 30], one_hot]\n",
    "display(sample)\n",
    "print(\"license 29 (NULL) all-zero:        \", bool((sample.loc[29] == 0).all()))\n",
    "print(\"license 30 (Food Truck, OOV) zero: \", bool((sample.loc[30] == 0).all()))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell19",
   "metadata": {},
   "source": [
    "## 9. Imputation — `__missing`, count vs measure\n",
    "\n",
    "`impute=True` is opt-in. It fills **count-like** features (`COUNT`) with the\n",
    "structural `0`, leaves **measures** (`MEAN`) NULL, and — for every column that\n",
    "had NULLs — emits a `<feature>__missing` `0/1` flag, recorded *before* the\n",
    "fill. One-hots are never NULL, so they get no `__missing` flag.\n",
    "\n",
    "License 5 has zero inspections, so its child aggregates are NULL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cell20",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.535260Z",
     "iopub.status.busy": "2026-07-09T00:22:55.535194Z",
     "iopub.status.idle": "2026-07-09T00:22:55.547507Z",
     "shell.execute_reply": "2026-07-09T00:22:55.547169Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.536\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'facilities': 5 CTEs, 3882 chars\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "COUNT(inspections.inspection_id) = 0.0  (count-like -> filled 0)\n",
      "COUNT(inspections.inspection_id)__missing = 1\n",
      "MEAN(inspections.score) = nan  (measure -> stays NULL)\n",
      "MEAN(inspections.score)__missing = 1\n",
      "\n",
      "one-hot columns with a __missing flag: []\n"
     ]
    }
   ],
   "source": [
    "imp = featurizer.to_dataframe(connection=conn, impute=True)\n",
    "\n",
    "\n",
    "def col(prefix):\n",
    "    return next(\n",
    "        c\n",
    "        for c in imp.columns\n",
    "        if c.startswith(prefix) and \"interval\" not in c and not c.endswith(\"__missing\")\n",
    "    )\n",
    "\n",
    "\n",
    "cnt, mean = col(\"COUNT(inspections.inspection_id)\"), col(\"MEAN(inspections.score)\")\n",
    "imp_snap = imp.reset_index()\n",
    "imp_snap = imp_snap[imp_snap[\"as_of_date\"].astype(str) == \"2024-01-01\"].set_index(\n",
    "    \"license_no\"\n",
    ")\n",
    "row = imp_snap.loc[5]  # license 5 has zero inspections\n",
    "\n",
    "print(f\"{cnt} = {row[cnt]}  (count-like -> filled 0)\")\n",
    "print(f\"{cnt}__missing = {row[cnt + '__missing']}\")\n",
    "print(f\"{mean} = {row[mean]}  (measure -> stays NULL)\")\n",
    "print(f\"{mean}__missing = {row[mean + '__missing']}\")\n",
    "print()\n",
    "print(\n",
    "    \"one-hot columns with a __missing flag:\",\n",
    "    [c for c in imp.columns if c.startswith(\"facilities.\") and c.endswith(\"__missing\")],\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell21",
   "metadata": {},
   "source": [
    "## 10. Arrow output\n",
    "\n",
    "`to_arrow` streams the matrix out of PostgreSQL with binary `COPY` (no pandas\n",
    "hop): a SQL `NULL` stays an Arrow null (never `NaN`), and the keys are ordinary\n",
    "leading columns rather than an index."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cell22",
   "metadata": {
    "execution": {
     "iopub.execute_input": "2026-07-09T00:22:55.548484Z",
     "iopub.status.busy": "2026-07-09T00:22:55.548428Z",
     "iopub.status.idle": "2026-07-09T00:22:55.567676Z",
     "shell.execute_reply": "2026-07-09T00:22:55.567133Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[32m2026-07-08 18:22:55.548\u001b[0m | \u001b[34m\u001b[1mDEBUG   \u001b[0m | \u001b[36mfeaturizer.sql\u001b[0m:\u001b[36mrender\u001b[0m:\u001b[36m40\u001b[0m - \u001b[34m\u001b[1mRendered SQL for target 'facilities': 5 CTEs, 3882 chars\u001b[0m\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Arrow columns: 15\n",
      "Leading key columns: ['as_of_date', 'license_no']\n",
      "One-hot dtypes: {'facilities.facility_type=Bakery': 'int32', 'facilities.facility_type=Grocery Store': 'int32'}\n"
     ]
    }
   ],
   "source": [
    "table = featurizer.to_arrow(connection=conn)\n",
    "print(\"Arrow columns:\", len(table.column_names))\n",
    "print(\"Leading key columns:\", table.column_names[:2])\n",
    "print(\"One-hot dtypes:\", {c: str(table.schema.field(c).type) for c in one_hot[:2]})"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell23",
   "metadata": {},
   "source": [
    "## 11. Summary\n",
    "\n",
    "- `role: categorical` → fixed-vocabulary, fit-free **one-hot** columns named\n",
    "  `\"<entity>.<col>=<value>\"`; NULL / out-of-vocabulary → all-zero.\n",
    "- `role: identifier` → excluded from the output (loudly).\n",
    "- **Split-blind:** the vocabulary is *declared* or read from a PostgreSQL\n",
    "  `ENUM` — never learned from the data (that train-only transform belongs to\n",
    "  the consumer).\n",
    "- `feature_manifest` recovers the full intended name for every column —\n",
    "  plus lineage (`depth`, `parents`, `source_alias`, `interval`) and a\n",
    "  generated `description` (v0.5.0).\n",
    "- `to_tables(schema)` persists the manifest as\n",
    "  `\"<schema>\".\"<stem>_manifest\"` beside the feature-group tables (v0.5.0).\n",
    "- `to_dataframe` / `to_arrow` give the matrix; `impute=True` adds the\n",
    "  count-vs-measure fill and `__missing` flags.\n",
    "\n",
    "**ENUM alternative:** instead of declaring the vocabulary, type the column as\n",
    "a PostgreSQL `ENUM` and featurizer reads its labels — pass\n",
    "`Featurizer('config.yaml', connection=conn)` (or it opens one from\n",
    "`DATABASE_URL` / `PG*`). See ADR-0007 and the top-level README."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}