03 · Deep Nesting

View on GitHub · Download .ipynb

This notebook demonstrates feature generation across multiple levels of entity relationships using a retail supply chain scenario:

Stores (target, depth 0)
Orders (depth 1)
OrderItems (depth 2) → Products (depth 2)
Suppliers (depth 3)

We’ll see how features propagate up through the entity graph with max_depth=3.

1. Setup

import sys
from pathlib import Path

# This tutorial is database-free: it loads the config and inspects the
# synthesized features and the generated SQL — none of which touch a
# database. To actually *execute* the features against PostgreSQL, run the
# example script instead (`just example <NN>`, or create_data.py +
# run_example.py with DATABASE_URL / PG* set). See the example README.
sys.path.insert(0, str(Path.cwd().parent.parent))

2. The Entity Graph

This example has a deeper entity graph than Examples 01 and 02. The relationships form a chain:

Stores ←── Orders ←── OrderItems ──→ Products ←── Suppliers

With max_depth=3, Featurizer traverses all the way from Stores down to Suppliers.

with open("config.yaml") as f:
    print(f.read())

# Retail supply chain: Deep nesting example

target: stores
max_depth: 3

intervals:
  - P30D  # Last 30 days
  - P90D  # Last 90 days

# Deep nesting multiplies features fast across 5 entities, so keep a minimal
# primitive set — the point here is the depth-3 traversal, not primitive breadth.
# (The full default set would blow past PostgreSQL's 1664 columns-per-row limit.)
aggregations:
  - count
  - sum
  - mean
transformations:
  - identity

entities:
  - alias: stores
    id: store_id
    table: stores
    temporal_ix: open_date
    variables:
      region:
        type: categorical
      size_sqft:
        type: numeric

  - alias: orders
    id: order_id
    table: orders
    temporal_ix: order_date
    variables:
      status:
        type: categorical

  - alias: order_items
    id: item_id
    table: order_items
    variables:
      quantity:
        type: numeric
      unit_price:
        type: numeric

  - alias: products
    id: product_id
    table: products
    variables:
      category:
        type: categorical
      base_cost:
        type: numeric

  - alias: suppliers
    id: supplier_id
    table: suppliers
    variables:
      country:
        type: categorical
      rating:
        type: numeric

relationships:
  # Depth 1: Stores → Orders
  - parent:
      entity: stores
      key: store_id
    child:
      entity: orders
      key: store_id

  # Depth 2: Orders → OrderItems
  - parent:
      entity: orders
      key: order_id
    child:
      entity: order_items
      key: order_id

  # Depth 2: OrderItems → Products
  - parent:
      entity: order_items
      key: product_id
    child:
      entity: products
      key: product_id

  # Depth 3: Products → Suppliers
  - parent:
      entity: products
      key: supplier_id
    child:
      entity: suppliers
      key: supplier_id

3. Create Featurizer

Let’s load the configuration and see how the entity graph is structured.

from featurizer import Featurizer

featurizer = Featurizer("config.yaml")

print(f"Target entity: {featurizer.target.alias}")
print(f"Max depth: {featurizer.max_depth}")
print(f"Intervals: {featurizer.intervals}")
print(f"Entities: {len(list(featurizer.entities))}")
print(f"Relationships: {len(featurizer.relationships)}")

[32m2026-06-21 13:29:17.742[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36mplan[0m:[36m230[0m - [34m[1mStarting feature build for target stores[0m


[32m2026-06-21 13:29:17.742[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m256[0m - [34m[1mbuild_features(stores) depth=0[0m


[32m2026-06-21 13:29:17.742[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m256[0m - [34m[1mbuild_features(orders) depth=1[0m


[32m2026-06-21 13:29:17.742[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m256[0m - [34m[1mbuild_features(order_items) depth=2[0m


[32m2026-06-21 13:29:17.743[0m | [1mINFO    [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m276[0m - [1mMaximum recursion depth reached at depth 3; materializing order_items without traversing further.[0m


[32m2026-06-21 13:29:17.743[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1014[0m - [34m[1mProcessing backward relationship Entity(orders).order_id -> Entity(order_items).order_id[0m


[32m2026-06-21 13:29:17.743[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.743[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.743[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.743[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.743[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.744[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.744[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.744[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.744[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1027[0m - [33m[1mEntity Entity(order_items) lacks temporal index; skipping interval-based aggregation[0m


[32m2026-06-21 13:29:17.744[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.planner[0m:[36m_build_aggregations[0m:[36m1014[0m - [34m[1mProcessing backward relationship Entity(stores).store_id -> Entity(orders).store_id[0m


[32m2026-06-21 13:29:17.745[0m | [33m[1mWARNING [0m | [36mfeaturizer.planner[0m:[36m_apply_direct_roles[0m:[36m1155[0m - [33m[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.[0m


Target entity: stores
Max depth: 3
Intervals: ['P30D', 'P90D']
Entities: 5
Relationships: 4

print("Entity Graph:")
for rel in featurizer.relationships:
    print(
        f"  {rel.parent.alias}.{rel.parent_key} ←── {rel.child.alias}.{rel.child_key}"
    )

Entity Graph:
  stores.store_id ←── orders.store_id
  orders.order_id ←── order_items.order_id
  order_items.product_id ←── products.product_id
  products.supplier_id ←── suppliers.supplier_id

4. Feature Propagation Across Depths

At each depth level, Featurizer aggregates child features and applies transformations. Let’s examine how features are distributed across entities.

print("Features by entity:")
for entity_alias, features in featurizer.features.items():
    print(f"\n  {entity_alias}: {len(features)} features")
    sample = sorted(features, key=lambda f: f.name)[:5]
    for feat in sample:
        print(f"    - {feat.name}")

Features by entity:

  stores: 43 features
    - "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)"

  orders: 47 features
    - "COUNT(order_items.item_id)"
    - "COUNT(orders.order_date)"
    - "COUNT(orders.order_date|interval=P30D)"
    - "COUNT(orders.order_date|interval=P90D)"
    - "COUNT(orders.order_id)"

  order_items: 8 features
    - "COUNT(order_items.item_id)"
    - "MEAN(order_items.quantity)"
    - "MEAN(order_items.unit_price)"
    - "SUM(order_items.quantity)"
    - "SUM(order_items.unit_price)"

  products: 3 features
    - base_cost
    - category
    - product_id

  suppliers: 3 features
    - country
    - rating
    - supplier_id

5. The CTE Chain

With deep nesting, the generated SQL has CTEs for each entity at each depth level. The naming pattern is:

<entity>_synth — joins aggregated and direct features
<entity>_transform — applies transformations
<child>_aggs_for_<parent> — aggregation CTE

Let’s see the full CTE structure.

print(f"Number of CTEs: {len(featurizer.ctes)}")
print("\nCTE names:")
for cte in featurizer.ctes:
    lines = cte.strip().split("\n")
    for line in lines:
        if " as (" in line:
            name = line.split(" as (")[0].strip().lstrip("-").strip()
            print(f"  - {name}")
            break

Number of CTEs: 8

CTE names:
  - order_items_synth
  - order_items_transform
  - order_items_aggs_for_orders
  - orders_synth
  - orders_transform
  - orders_aggs_for_stores
  - stores_synth
  - stores_transform

6. Generated SQL

The full SQL query shows the depth-3 traversal with lateral joins and time-windowed aggregations.

sql = featurizer.query
print("Generated SQL Query:")
print("=" * 80)
print(sql)
print("=" * 80)
print(f"\nSQL length: {len(sql):,} characters")

[32m2026-06-21 13:29:17.757[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.sql[0m:[36mrender[0m:[36m40[0m - [34m[1mRendered SQL for target 'stores': 8 CTEs, 14919 chars[0m


Generated SQL Query:
================================================================================

        select aod.as_of_date, t.*
        from as_of_dates as aod
        cross join lateral (

        with


        -- sythetize aggregations and direct features for order_items
        order_items_synth as (
        select
        order_items.item_id, order_items.order_id, quantity, unit_price
        from order_items


        )
        ,
        -- transform order_items
        order_items_transform as (
        select
        item_id, order_id, quantity as quantity, unit_price as unit_price
        from order_items_synth _ego
        )
        ,
        -- Aggregate for orders
        order_items_aggs_for_orders as (
        select
        order_items_transform.order_id,
        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)"
        from order_items_transform

        group by order_id
        )
        ,
        -- sythetize aggregations and direct features for orders
        orders_synth as (
        select
        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
        from orders
         left join
         order_items_aggs_for_orders on order_items_aggs_for_orders.order_id = orders.order_id
        )
        ,
        -- transform orders
        orders_transform as (
        select
        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
        from orders_synth _ego
        )
        ,
        -- Aggregate for stores
        orders_aggs_for_stores as (
        select
        orders_transform.store_id,
        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)"
        from orders_transform
         where order_date <= aod.as_of_date
        group by store_id
        )
        ,
        -- sythetize aggregations and direct features for stores
        stores_synth as (
        select
        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
        from stores
         left join
         orders_aggs_for_stores on orders_aggs_for_stores.store_id = stores.store_id
        )
        ,
        -- transform stores
        stores_transform as (
        select
        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
        from stores_synth _ego
        )


        select * from stores_transform
        ) as t

        order by aod.as_of_date

================================================================================

SQL length: 14,919 characters

7. Feature Count Growth

Deep nesting causes a combinatorial explosion of features. Each depth level multiplies the feature count by the number of aggregations and transformations.

target_features = featurizer.features[featurizer.target.alias]
print(f"Total target features: {len(target_features)}")

# Analyze feature names
depth_indicators = {
    "Direct (stores)": [f for f in target_features if "orders" not in f.name.lower()],
    "Depth 1 (orders)": [
        f
        for f in target_features
        if "orders" in f.name.lower() and "order_items" not in f.name.lower()
    ],
    "Depth 2+ (items/products)": [
        f
        for f in target_features
        if "order_items" in f.name.lower() or "products" in f.name.lower()
    ],
}

for label, feats in depth_indicators.items():
    print(f"  {label}: ~{len(feats)} features")

Total target features: 43
  Direct (stores): ~4 features
  Depth 1 (orders): ~9 features
  Depth 2+ (items/products): ~30 features

8. Summary

In this tutorial, we learned:

Deep entity graphs: How to configure multi-level relationships (Stores → Orders → OrderItems → Products → Suppliers)
Feature propagation: How aggregated features at each depth level become inputs to the next level
CTE chain: The naming pattern for CTEs at each entity and depth
Feature explosion: How feature count grows combinatorially with depth, intervals, and primitives

Performance Note

Deep nesting (depth > 3) can generate very large SQL queries. Consider:

Reducing max_depth for initial exploration
Using fewer intervals
Selecting specific aggregations/transformations rather than defaults

print("Deep Nesting Summary")
print("=" * 40)
print(f"Target: {featurizer.target.alias}")
print(f"Depth: {featurizer.max_depth}")
print(f"Intervals: {', '.join(featurizer.intervals)}")
print(f"Entities: {len(list(featurizer.entities))}")
print(f"Relationships: {len(featurizer.relationships)}")
print(f"Total features: {len(target_features)}")
print(f"SQL length: {len(sql):,} characters")
print(f"CTEs generated: {len(featurizer.ctes)}")

Deep Nesting Summary
========================================
Target: stores
Depth: 3
Intervals: P30D, P90D
Entities: 5
Relationships: 4
Total features: 43
SQL length: 14,919 characters
CTEs generated: 8