{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Featurizer Tutorial: Deep Nesting\n", "\n", "This notebook demonstrates feature generation across multiple levels of entity relationships using a retail supply chain scenario:\n", "- **Stores** (target, depth 0)\n", "- **Orders** (depth 1)\n", "- **OrderItems** (depth 2) → **Products** (depth 2)\n", "- **Suppliers** (depth 3)\n", "\n", "We'll see how features propagate up through the entity graph with `max_depth=3`." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 1. Setup" ] }, { "cell_type": "code", "execution_count": 1, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.480739Z", "iopub.status.busy": "2026-06-21T19:29:17.480414Z", "iopub.status.idle": "2026-06-21T19:29:17.487278Z", "shell.execute_reply": "2026-06-21T19:29:17.486427Z" } }, "outputs": [], "source": [ "import sys\n", "from pathlib import Path\n", "\n", "# This tutorial is database-free: it loads the config and inspects the\n", "# synthesized features and the generated SQL — none of which touch a\n", "# database. To actually *execute* the features against PostgreSQL, run the\n", "# example script instead (`just example `, or create_data.py +\n", "# run_example.py with DATABASE_URL / PG* set). See the example README.\n", "sys.path.insert(0, str(Path.cwd().parent.parent))" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 2. The Entity Graph\n", "\n", "This example has a deeper entity graph than Examples 01 and 02. The relationships form a chain:\n", "\n", "```\n", "Stores ←── Orders ←── OrderItems ──→ Products ←── Suppliers\n", "```\n", "\n", "With `max_depth=3`, Featurizer traverses all the way from Stores down to Suppliers." ] }, { "cell_type": "code", "execution_count": 2, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.489872Z", "iopub.status.busy": "2026-06-21T19:29:17.489640Z", "iopub.status.idle": "2026-06-21T19:29:17.493370Z", "shell.execute_reply": "2026-06-21T19:29:17.492743Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "# Retail supply chain: Deep nesting example\n", "\n", "target: stores\n", "max_depth: 3\n", "\n", "intervals:\n", " - P30D # Last 30 days\n", " - P90D # Last 90 days\n", "\n", "# Deep nesting multiplies features fast across 5 entities, so keep a minimal\n", "# primitive set — the point here is the depth-3 traversal, not primitive breadth.\n", "# (The full default set would blow past PostgreSQL's 1664 columns-per-row limit.)\n", "aggregations:\n", " - count\n", " - sum\n", " - mean\n", "transformations:\n", " - identity\n", "\n", "entities:\n", " - alias: stores\n", " id: store_id\n", " table: stores\n", " temporal_ix: open_date\n", " variables:\n", " region:\n", " type: categorical\n", " size_sqft:\n", " type: numeric\n", "\n", " - alias: orders\n", " id: order_id\n", " table: orders\n", " temporal_ix: order_date\n", " variables:\n", " status:\n", " type: categorical\n", "\n", " - alias: order_items\n", " id: item_id\n", " table: order_items\n", " variables:\n", " quantity:\n", " type: numeric\n", " unit_price:\n", " type: numeric\n", "\n", " - alias: products\n", " id: product_id\n", " table: products\n", " variables:\n", " category:\n", " type: categorical\n", " base_cost:\n", " type: numeric\n", "\n", " - alias: suppliers\n", " id: supplier_id\n", " table: suppliers\n", " variables:\n", " country:\n", " type: categorical\n", " rating:\n", " type: numeric\n", "\n", "relationships:\n", " # Depth 1: Stores → Orders\n", " - parent:\n", " entity: stores\n", " key: store_id\n", " child:\n", " entity: orders\n", " key: store_id\n", "\n", " # Depth 2: Orders → OrderItems\n", " - parent:\n", " entity: orders\n", " key: order_id\n", " child:\n", " entity: order_items\n", " key: order_id\n", "\n", " # Depth 2: OrderItems → Products\n", " - parent:\n", " entity: order_items\n", " key: product_id\n", " child:\n", " entity: products\n", " key: product_id\n", "\n", " # Depth 3: Products → Suppliers\n", " - parent:\n", " entity: products\n", " key: supplier_id\n", " child:\n", " entity: suppliers\n", " key: supplier_id\n", "\n" ] } ], "source": [ "with open(\"config.yaml\") as f:\n", " print(f.read())" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 3. Create Featurizer\n", "\n", "Let's load the configuration and see how the entity graph is structured." ] }, { "cell_type": "code", "execution_count": 3, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.523851Z", "iopub.status.busy": "2026-06-21T19:29:17.523697Z", "iopub.status.idle": "2026-06-21T19:29:17.746880Z", "shell.execute_reply": "2026-06-21T19:29:17.746369Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.742\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36mplan\u001b[0m:\u001b[36m230\u001b[0m - \u001b[34m\u001b[1mStarting feature build for target stores\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.742\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m256\u001b[0m - \u001b[34m\u001b[1mbuild_features(stores) depth=0\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.742\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m256\u001b[0m - \u001b[34m\u001b[1mbuild_features(orders) depth=1\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.742\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m256\u001b[0m - \u001b[34m\u001b[1mbuild_features(order_items) depth=2\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[1mINFO \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_features\u001b[0m:\u001b[36m276\u001b[0m - \u001b[1mMaximum recursion depth reached at depth 3; materializing order_items without traversing further.\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1014\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(orders).order_id -> Entity(order_items).order_id\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.743\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.744\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.744\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.744\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.744\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1027\u001b[0m - \u001b[33m\u001b[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.744\u001b[0m | \u001b[34m\u001b[1mDEBUG \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_build_aggregations\u001b[0m:\u001b[36m1014\u001b[0m - \u001b[34m\u001b[1mProcessing backward relationship Entity(stores).store_id -> Entity(orders).store_id\u001b[0m\n" ] }, { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.745\u001b[0m | \u001b[33m\u001b[1mWARNING \u001b[0m | \u001b[36mfeaturizer.planner\u001b[0m:\u001b[36m_apply_direct_roles\u001b[0m:\u001b[36m1155\u001b[0m - \u001b[33m\u001b[1mDirect variable 'stores.region' (type: categorical) will pass through as a raw string column and is likely to crash a downstream encoder. Set role: categorical (with a declared vocabulary or a PostgreSQL ENUM) to one-hot encode it, or role: identifier to exclude it.\u001b[0m\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Target entity: stores\n", "Max depth: 3\n", "Intervals: ['P30D', 'P90D']\n", "Entities: 5\n", "Relationships: 4\n" ] } ], "source": [ "from featurizer import Featurizer\n", "\n", "featurizer = Featurizer(\"config.yaml\")\n", "\n", "print(f\"Target entity: {featurizer.target.alias}\")\n", "print(f\"Max depth: {featurizer.max_depth}\")\n", "print(f\"Intervals: {featurizer.intervals}\")\n", "print(f\"Entities: {len(list(featurizer.entities))}\")\n", "print(f\"Relationships: {len(featurizer.relationships)}\")" ] }, { "cell_type": "code", "execution_count": 4, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.748845Z", "iopub.status.busy": "2026-06-21T19:29:17.748732Z", "iopub.status.idle": "2026-06-21T19:29:17.750428Z", "shell.execute_reply": "2026-06-21T19:29:17.750151Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Entity Graph:\n", " stores.store_id ←── orders.store_id\n", " orders.order_id ←── order_items.order_id\n", " order_items.product_id ←── products.product_id\n", " products.supplier_id ←── suppliers.supplier_id\n" ] } ], "source": [ "print(\"Entity Graph:\")\n", "for rel in featurizer.relationships:\n", " print(\n", " f\" {rel.parent.alias}.{rel.parent_key} ←── {rel.child.alias}.{rel.child_key}\"\n", " )" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 4. Feature Propagation Across Depths\n", "\n", "At each depth level, Featurizer aggregates child features and applies transformations. Let's examine how features are distributed across entities." ] }, { "cell_type": "code", "execution_count": 5, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.751471Z", "iopub.status.busy": "2026-06-21T19:29:17.751408Z", "iopub.status.idle": "2026-06-21T19:29:17.753226Z", "shell.execute_reply": "2026-06-21T19:29:17.752927Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Features by entity:\n", "\n", " stores: 43 features\n", " - \"COUNT(orders.order_date)\"\n", " - \"COUNT(orders.order_date|interval=P30D)\"\n", " - \"COUNT(orders.order_date|interval=P90D)\"\n", " - \"COUNT(orders.order_id)\"\n", " - \"COUNT(orders.order_id|interval=P30D)\"\n", "\n", " orders: 47 features\n", " - \"COUNT(order_items.item_id)\"\n", " - \"COUNT(orders.order_date)\"\n", " - \"COUNT(orders.order_date|interval=P30D)\"\n", " - \"COUNT(orders.order_date|interval=P90D)\"\n", " - \"COUNT(orders.order_id)\"\n", "\n", " order_items: 8 features\n", " - \"COUNT(order_items.item_id)\"\n", " - \"MEAN(order_items.quantity)\"\n", " - \"MEAN(order_items.unit_price)\"\n", " - \"SUM(order_items.quantity)\"\n", " - \"SUM(order_items.unit_price)\"\n", "\n", " products: 3 features\n", " - base_cost\n", " - category\n", " - product_id\n", "\n", " suppliers: 3 features\n", " - country\n", " - rating\n", " - supplier_id\n" ] } ], "source": [ "print(\"Features by entity:\")\n", "for entity_alias, features in featurizer.features.items():\n", " print(f\"\\n {entity_alias}: {len(features)} features\")\n", " sample = sorted(features, key=lambda f: f.name)[:5]\n", " for feat in sample:\n", " print(f\" - {feat.name}\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 5. The CTE Chain\n", "\n", "With deep nesting, the generated SQL has CTEs for each entity at each depth level. The naming pattern is:\n", "- `_synth` — joins aggregated and direct features\n", "- `_transform` — applies transformations\n", "- `_aggs_for_` — aggregation CTE\n", "\n", "Let's see the full CTE structure." ] }, { "cell_type": "code", "execution_count": 6, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.754173Z", "iopub.status.busy": "2026-06-21T19:29:17.754117Z", "iopub.status.idle": "2026-06-21T19:29:17.755871Z", "shell.execute_reply": "2026-06-21T19:29:17.755560Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Number of CTEs: 8\n", "\n", "CTE names:\n", " - order_items_synth\n", " - order_items_transform\n", " - order_items_aggs_for_orders\n", " - orders_synth\n", " - orders_transform\n", " - orders_aggs_for_stores\n", " - stores_synth\n", " - stores_transform\n" ] } ], "source": [ "print(f\"Number of CTEs: {len(featurizer.ctes)}\")\n", "print(\"\\nCTE names:\")\n", "for cte in featurizer.ctes:\n", " lines = cte.strip().split(\"\\n\")\n", " for line in lines:\n", " if \" as (\" in line:\n", " name = line.split(\" as (\")[0].strip().lstrip(\"-\").strip()\n", " print(f\" - {name}\")\n", " break" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 6. Generated SQL\n", "\n", "The full SQL query shows the depth-3 traversal with lateral joins and time-windowed aggregations." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.756743Z", "iopub.status.busy": "2026-06-21T19:29:17.756690Z", "iopub.status.idle": "2026-06-21T19:29:17.758591Z", "shell.execute_reply": "2026-06-21T19:29:17.758278Z" } }, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "\u001b[32m2026-06-21 13:29:17.757\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 'stores': 8 CTEs, 14919 chars\u001b[0m\n" ] }, { "name": "stdout", "output_type": "stream", "text": [ "Generated SQL Query:\n", "================================================================================\n", "\n", " select aod.as_of_date, t.*\n", " from as_of_dates as aod\n", " cross join lateral (\n", "\n", " with\n", "\n", " \n", " -- sythetize aggregations and direct features for order_items\n", " order_items_synth as (\n", " select\n", " order_items.item_id, order_items.order_id, quantity, unit_price\n", " from order_items\n", " \n", " \n", " )\n", " ,\n", " -- transform order_items\n", " order_items_transform as (\n", " select\n", " item_id, order_id, quantity as quantity, unit_price as unit_price\n", " from order_items_synth _ego\n", " )\n", " ,\n", " -- Aggregate for orders\n", " order_items_aggs_for_orders as (\n", " select\n", " order_items_transform.order_id,\n", " count( item_id ) as \"COUNT(order_items.item_id)\" ,avg( quantity ) as \"MEAN(order_items.quantity)\" ,avg( unit_price ) as \"MEAN(order_items.unit_price)\" ,sum( quantity ) as \"SUM(order_items.quantity)\" ,sum( unit_price ) as \"SUM(order_items.unit_price)\" \n", " from order_items_transform\n", " \n", " group by order_id\n", " )\n", " ,\n", " -- sythetize aggregations and direct features for orders\n", " orders_synth as (\n", " select\n", " orders.order_id, orders.order_date, orders.store_id, \"COUNT(order_items.item_id)\", \"MEAN(order_items.quantity)\", \"MEAN(order_items.unit_price)\", \"SUM(order_items.quantity)\", \"SUM(order_items.unit_price)\", status\n", " from orders\n", " left join \n", " order_items_aggs_for_orders on order_items_aggs_for_orders.order_id = orders.order_id \n", " )\n", " ,\n", " -- transform orders\n", " orders_transform as (\n", " select\n", " order_id, order_date, store_id, \"COUNT(order_items.item_id)\" as \"COUNT(order_items.item_id)\", \"MEAN(order_items.quantity)\" as \"MEAN(order_items.quantity)\", \"MEAN(order_items.unit_price)\" as \"MEAN(order_items.unit_price)\", \"SUM(order_items.quantity)\" as \"SUM(order_items.quantity)\", \"SUM(order_items.unit_price)\" as \"SUM(order_items.unit_price)\", status as status\n", " from orders_synth _ego\n", " )\n", " ,\n", " -- Aggregate for stores\n", " orders_aggs_for_stores as (\n", " select\n", " orders_transform.store_id,\n", " count( order_date ) as \"COUNT(orders.order_date)\" ,count( order_date ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.order_date|interval=P30D)\" ,count( order_date ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.order_date|interval=P90D)\" ,count( order_id ) as \"COUNT(orders.order_id)\" ,count( order_id ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.order_id|interval=P30D)\" ,count( order_id ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.order_id|interval=P90D)\" ,count( status ) as \"COUNT(orders.status)\" ,count( status ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.status|interval=P30D)\" ,count( status ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"COUNT(orders.status|interval=P90D)\" ,avg( \"COUNT(order_items.item_id)\" ) as \"MEAN(orders.COUNT(order_items.item_id))\" ,avg( \"COUNT(order_items.item_id)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.COUNT(order_items.item_id)|interval=P30D)\" ,avg( \"COUNT(order_items.item_id)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.COUNT(order_items.item_id)|interval=P90D)\" ,avg( \"MEAN(order_items.quantity)\" ) as \"MEAN(orders.MEAN(order_items.quantity))\" ,avg( \"MEAN(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.MEAN(order_items.quantity)|interval=P30D)\" ,avg( \"MEAN(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.MEAN(order_items.quantity)|interval=P90D)\" ,avg( \"MEAN(order_items.unit_price)\" ) as \"MEAN(orders.MEAN(order_items.unit_price))\" ,avg( \"MEAN(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.MEAN(order_items.unit_price)|interval=P30D)\" ,avg( \"MEAN(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.MEAN(order_items.unit_price)|interval=P90D)\" ,avg( \"SUM(order_items.quantity)\" ) as \"MEAN(orders.SUM(order_items.quantity))\" ,avg( \"SUM(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.SUM(order_items.quantity)|interval=P30D)\" ,avg( \"SUM(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.SUM(order_items.quantity)|interval=P90D)\" ,avg( \"SUM(order_items.unit_price)\" ) as \"MEAN(orders.SUM(order_items.unit_price))\" ,avg( \"SUM(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.SUM(order_items.unit_price)|interval=P30D)\" ,avg( \"SUM(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"MEAN(orders.SUM(order_items.unit_price)|interval=P90D)\" ,sum( \"COUNT(order_items.item_id)\" ) as \"SUM(orders.COUNT(order_items.item_id))\" ,sum( \"COUNT(order_items.item_id)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.COUNT(order_items.item_id)|interval=P30D)\" ,sum( \"COUNT(order_items.item_id)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.COUNT(order_items.item_id)|interval=P90D)\" ,sum( \"MEAN(order_items.quantity)\" ) as \"SUM(orders.MEAN(order_items.quantity))\" ,sum( \"MEAN(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.MEAN(order_items.quantity)|interval=P30D)\" ,sum( \"MEAN(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.MEAN(order_items.quantity)|interval=P90D)\" ,sum( \"MEAN(order_items.unit_price)\" ) as \"SUM(orders.MEAN(order_items.unit_price))\" ,sum( \"MEAN(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.MEAN(order_items.unit_price)|interval=P30D)\" ,sum( \"MEAN(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.MEAN(order_items.unit_price)|interval=P90D)\" ,sum( \"SUM(order_items.quantity)\" ) as \"SUM(orders.SUM(order_items.quantity))\" ,sum( \"SUM(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.SUM(order_items.quantity)|interval=P30D)\" ,sum( \"SUM(order_items.quantity)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.SUM(order_items.quantity)|interval=P90D)\" ,sum( \"SUM(order_items.unit_price)\" ) as \"SUM(orders.SUM(order_items.unit_price))\" ,sum( \"SUM(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.SUM(order_items.unit_price)|interval=P30D)\" ,sum( \"SUM(order_items.unit_price)\" ) filter (where daterange((aod.as_of_date - interval 'P90D')::date, aod.as_of_date::date, '[]') @> order_date::date) as \"SUM(orders.SUM(order_items.unit_price)|interval=P90D)\" \n", " from orders_transform\n", " where order_date <= aod.as_of_date\n", " group by store_id\n", " )\n", " ,\n", " -- sythetize aggregations and direct features for stores\n", " stores_synth as (\n", " select\n", " stores.store_id, stores.open_date, \"COUNT(orders.order_date)\", \"COUNT(orders.order_date|interval=P30D)\", \"COUNT(orders.order_date|interval=P90D)\", \"COUNT(orders.order_id)\", \"COUNT(orders.order_id|interval=P30D)\", \"COUNT(orders.order_id|interval=P90D)\", \"COUNT(orders.status)\", \"COUNT(orders.status|interval=P30D)\", \"COUNT(orders.status|interval=P90D)\", \"MEAN(orders.COUNT(order_items.item_id))\", \"MEAN(orders.COUNT(order_items.item_id)|interval=P30D)\", \"MEAN(orders.COUNT(order_items.item_id)|interval=P90D)\", \"MEAN(orders.MEAN(order_items.quantity))\", \"MEAN(orders.MEAN(order_items.quantity)|interval=P30D)\", \"MEAN(orders.MEAN(order_items.quantity)|interval=P90D)\", \"MEAN(orders.MEAN(order_items.unit_price))\", \"MEAN(orders.MEAN(order_items.unit_price)|interval=P30D)\", \"MEAN(orders.MEAN(order_items.unit_price)|interval=P90D)\", \"MEAN(orders.SUM(order_items.quantity))\", \"MEAN(orders.SUM(order_items.quantity)|interval=P30D)\", \"MEAN(orders.SUM(order_items.quantity)|interval=P90D)\", \"MEAN(orders.SUM(order_items.unit_price))\", \"MEAN(orders.SUM(order_items.unit_price)|interval=P30D)\", \"MEAN(orders.SUM(order_items.unit_price)|interval=P90D)\", \"SUM(orders.COUNT(order_items.item_id))\", \"SUM(orders.COUNT(order_items.item_id)|interval=P30D)\", \"SUM(orders.COUNT(order_items.item_id)|interval=P90D)\", \"SUM(orders.MEAN(order_items.quantity))\", \"SUM(orders.MEAN(order_items.quantity)|interval=P30D)\", \"SUM(orders.MEAN(order_items.quantity)|interval=P90D)\", \"SUM(orders.MEAN(order_items.unit_price))\", \"SUM(orders.MEAN(order_items.unit_price)|interval=P30D)\", \"SUM(orders.MEAN(order_items.unit_price)|interval=P90D)\", \"SUM(orders.SUM(order_items.quantity))\", \"SUM(orders.SUM(order_items.quantity)|interval=P30D)\", \"SUM(orders.SUM(order_items.quantity)|interval=P90D)\", \"SUM(orders.SUM(order_items.unit_price))\", \"SUM(orders.SUM(order_items.unit_price)|interval=P30D)\", \"SUM(orders.SUM(order_items.unit_price)|interval=P90D)\", region, size_sqft\n", " from stores\n", " left join \n", " orders_aggs_for_stores on orders_aggs_for_stores.store_id = stores.store_id \n", " )\n", " ,\n", " -- transform stores\n", " stores_transform as (\n", " select\n", " store_id, open_date, \"COUNT(orders.order_date)\" as \"COUNT(orders.order_date)\", \"COUNT(orders.order_date|interval=P30D)\" as \"COUNT(orders.order_date|interval=P30D)\", \"COUNT(orders.order_date|interval=P90D)\" as \"COUNT(orders.order_date|interval=P90D)\", \"COUNT(orders.order_id)\" as \"COUNT(orders.order_id)\", \"COUNT(orders.order_id|interval=P30D)\" as \"COUNT(orders.order_id|interval=P30D)\", \"COUNT(orders.order_id|interval=P90D)\" as \"COUNT(orders.order_id|interval=P90D)\", \"COUNT(orders.status)\" as \"COUNT(orders.status)\", \"COUNT(orders.status|interval=P30D)\" as \"COUNT(orders.status|interval=P30D)\", \"COUNT(orders.status|interval=P90D)\" as \"COUNT(orders.status|interval=P90D)\", \"MEAN(orders.COUNT(order_items.item_id))\" as \"MEAN(orders.COUNT(order_items.item_id))\", \"MEAN(orders.COUNT(order_items.item_id)|interval=P30D)\" as \"MEAN(orders.COUNT(order_items.item_id)|interval=P30D)\", \"MEAN(orders.COUNT(order_items.item_id)|interval=P90D)\" as \"MEAN(orders.COUNT(order_items.item_id)|interval=P90D)\", \"MEAN(orders.MEAN(order_items.quantity))\" as \"MEAN(orders.MEAN(order_items.quantity))\", \"MEAN(orders.MEAN(order_items.quantity)|interval=P30D)\" as \"MEAN(orders.MEAN(order_items.quantity)|interval=P30D)\", \"MEAN(orders.MEAN(order_items.quantity)|interval=P90D)\" as \"MEAN(orders.MEAN(order_items.quantity)|interval=P90D)\", \"MEAN(orders.MEAN(order_items.unit_price))\" as \"MEAN(orders.MEAN(order_items.unit_price))\", \"MEAN(orders.MEAN(order_items.unit_price)|interval=P30D)\" as \"MEAN(orders.MEAN(order_items.unit_price)|interval=P30D)\", \"MEAN(orders.MEAN(order_items.unit_price)|interval=P90D)\" as \"MEAN(orders.MEAN(order_items.unit_price)|interval=P90D)\", \"MEAN(orders.SUM(order_items.quantity))\" as \"MEAN(orders.SUM(order_items.quantity))\", \"MEAN(orders.SUM(order_items.quantity)|interval=P30D)\" as \"MEAN(orders.SUM(order_items.quantity)|interval=P30D)\", \"MEAN(orders.SUM(order_items.quantity)|interval=P90D)\" as \"MEAN(orders.SUM(order_items.quantity)|interval=P90D)\", \"MEAN(orders.SUM(order_items.unit_price))\" as \"MEAN(orders.SUM(order_items.unit_price))\", \"MEAN(orders.SUM(order_items.unit_price)|interval=P30D)\" as \"MEAN(orders.SUM(order_items.unit_price)|interval=P30D)\", \"MEAN(orders.SUM(order_items.unit_price)|interval=P90D)\" as \"MEAN(orders.SUM(order_items.unit_price)|interval=P90D)\", \"SUM(orders.COUNT(order_items.item_id))\" as \"SUM(orders.COUNT(order_items.item_id))\", \"SUM(orders.COUNT(order_items.item_id)|interval=P30D)\" as \"SUM(orders.COUNT(order_items.item_id)|interval=P30D)\", \"SUM(orders.COUNT(order_items.item_id)|interval=P90D)\" as \"SUM(orders.COUNT(order_items.item_id)|interval=P90D)\", \"SUM(orders.MEAN(order_items.quantity))\" as \"SUM(orders.MEAN(order_items.quantity))\", \"SUM(orders.MEAN(order_items.quantity)|interval=P30D)\" as \"SUM(orders.MEAN(order_items.quantity)|interval=P30D)\", \"SUM(orders.MEAN(order_items.quantity)|interval=P90D)\" as \"SUM(orders.MEAN(order_items.quantity)|interval=P90D)\", \"SUM(orders.MEAN(order_items.unit_price))\" as \"SUM(orders.MEAN(order_items.unit_price))\", \"SUM(orders.MEAN(order_items.unit_price)|interval=P30D)\" as \"SUM(orders.MEAN(order_items.unit_price)|interval=P30D)\", \"SUM(orders.MEAN(order_items.unit_price)|interval=P90D)\" as \"SUM(orders.MEAN(order_items.unit_price)|interval=P90D)\", \"SUM(orders.SUM(order_items.quantity))\" as \"SUM(orders.SUM(order_items.quantity))\", \"SUM(orders.SUM(order_items.quantity)|interval=P30D)\" as \"SUM(orders.SUM(order_items.quantity)|interval=P30D)\", \"SUM(orders.SUM(order_items.quantity)|interval=P90D)\" as \"SUM(orders.SUM(order_items.quantity)|interval=P90D)\", \"SUM(orders.SUM(order_items.unit_price))\" as \"SUM(orders.SUM(order_items.unit_price))\", \"SUM(orders.SUM(order_items.unit_price)|interval=P30D)\" as \"SUM(orders.SUM(order_items.unit_price)|interval=P30D)\", \"SUM(orders.SUM(order_items.unit_price)|interval=P90D)\" as \"SUM(orders.SUM(order_items.unit_price)|interval=P90D)\", region as region, size_sqft as size_sqft\n", " from stores_synth _ego\n", " )\n", " \n", "\n", " select * from stores_transform\n", " ) as t\n", "\n", " order by aod.as_of_date\n", " \n", "================================================================================\n", "\n", "SQL length: 14,919 characters\n" ] } ], "source": [ "sql = featurizer.query\n", "print(\"Generated SQL Query:\")\n", "print(\"=\" * 80)\n", "print(sql)\n", "print(\"=\" * 80)\n", "print(f\"\\nSQL length: {len(sql):,} characters\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 7. Feature Count Growth\n", "\n", "Deep nesting causes a combinatorial explosion of features. Each depth level multiplies the feature count by the number of aggregations and transformations." ] }, { "cell_type": "code", "execution_count": 8, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.759471Z", "iopub.status.busy": "2026-06-21T19:29:17.759425Z", "iopub.status.idle": "2026-06-21T19:29:17.761366Z", "shell.execute_reply": "2026-06-21T19:29:17.760995Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Total target features: 43\n", " Direct (stores): ~4 features\n", " Depth 1 (orders): ~9 features\n", " Depth 2+ (items/products): ~30 features\n" ] } ], "source": [ "target_features = featurizer.features[featurizer.target.alias]\n", "print(f\"Total target features: {len(target_features)}\")\n", "\n", "# Analyze feature names\n", "depth_indicators = {\n", " \"Direct (stores)\": [f for f in target_features if \"orders\" not in f.name.lower()],\n", " \"Depth 1 (orders)\": [\n", " f\n", " for f in target_features\n", " if \"orders\" in f.name.lower() and \"order_items\" not in f.name.lower()\n", " ],\n", " \"Depth 2+ (items/products)\": [\n", " f\n", " for f in target_features\n", " if \"order_items\" in f.name.lower() or \"products\" in f.name.lower()\n", " ],\n", "}\n", "\n", "for label, feats in depth_indicators.items():\n", " print(f\" {label}: ~{len(feats)} features\")" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## 8. Summary\n", "\n", "In this tutorial, we learned:\n", "\n", "1. **Deep entity graphs**: How to configure multi-level relationships (Stores → Orders → OrderItems → Products → Suppliers)\n", "2. **Feature propagation**: How aggregated features at each depth level become inputs to the next level\n", "3. **CTE chain**: The naming pattern for CTEs at each entity and depth\n", "4. **Feature explosion**: How feature count grows combinatorially with depth, intervals, and primitives\n", "\n", "### Performance Note\n", "Deep nesting (depth > 3) can generate very large SQL queries. Consider:\n", "- Reducing `max_depth` for initial exploration\n", "- Using fewer `intervals`\n", "- Selecting specific aggregations/transformations rather than defaults" ] }, { "cell_type": "code", "execution_count": 9, "metadata": { "execution": { "iopub.execute_input": "2026-06-21T19:29:17.762246Z", "iopub.status.busy": "2026-06-21T19:29:17.762187Z", "iopub.status.idle": "2026-06-21T19:29:17.763956Z", "shell.execute_reply": "2026-06-21T19:29:17.763645Z" } }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Deep Nesting Summary\n", "========================================\n", "Target: stores\n", "Depth: 3\n", "Intervals: P30D, P90D\n", "Entities: 5\n", "Relationships: 4\n", "Total features: 43\n", "SQL length: 14,919 characters\n", "CTEs generated: 8\n" ] } ], "source": [ "print(\"Deep Nesting Summary\")\n", "print(\"=\" * 40)\n", "print(f\"Target: {featurizer.target.alias}\")\n", "print(f\"Depth: {featurizer.max_depth}\")\n", "print(f\"Intervals: {', '.join(featurizer.intervals)}\")\n", "print(f\"Entities: {len(list(featurizer.entities))}\")\n", "print(f\"Relationships: {len(featurizer.relationships)}\")\n", "print(f\"Total features: {len(target_features)}\")\n", "print(f\"SQL length: {len(sql):,} characters\")\n", "print(f\"CTEs generated: {len(featurizer.ctes)}\")" ] } ], "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": 4 }