01 · Basic Aggregations

This notebook demonstrates the core workflow of Featurizer using a simple e-commerce scenario:

Customers (parent entity) with attributes like country and age
Orders (child entity) with amounts and statuses

We’ll generate features for customers by aggregating their order history.

1. Setup

First, let’s set up the environment and create sample data.

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. Understanding the Configuration

Featurizer uses YAML configuration to define entities, relationships, and feature generation parameters.

# Let's examine the configuration file
with open("config.yaml") as f:
    print(f.read())

# E-commerce feature generation: Customers with Orders

target: customers
max_depth: 2

intervals:
  - P7D   # Last 7 days
  - P30D  # Last 30 days

# A focused primitive set keeps the feature matrix small and readable. The full
# default set would synthesize thousands of columns — past PostgreSQL's 1664
# per-row limit and far past anything legible in a tutorial.
aggregations:
  - count
  - sum
  - mean
  - min
  - max
  - stddev
  - nunique
transformations:
  - identity
  - abs

entities:
  - alias: customers
    id: customer_id
    table: customers
    temporal_ix: signup_date
    variables:
      # role: categorical one-hot encodes a direct categorical against a FIXED
      # vocabulary (declared here, or a column's PostgreSQL ENUM) — split-blind
      # and fit-free. Each value becomes a "customers.country=<value>" 0/1
      # column. Without a role, a raw text/categorical column passes through
      # unencoded (and warns), which a downstream encoder usually can't take.
      country:
        type: categorical
        role: categorical
        vocabulary: [AU, CA, DE, FR, UK, US]
      age:
        type: numeric

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

relationships:
  - parent:
      entity: customers
      key: customer_id
    child:
      entity: orders
      key: customer_id

Key Configuration Elements:

target: The entity we want to generate features for (customers)
max_depth: How deep to traverse relationships (2 levels)
intervals: Time windows for aggregations (P7D = 7 days, P30D = 30 days)
entities: Define tables with their columns and types
relationships: Define parent-child connections via foreign keys

3. Creating the Featurizer

Load the configuration and create a Featurizer instance.

from featurizer import Featurizer

# Create featurizer from config
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"Number of entities: {len(list(featurizer.entities))}")
print(f"Number of relationships: {len(featurizer.relationships)}")

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


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


[32m2026-06-21 13:29:05.610[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:05.610[0m | [1mINFO    [0m | [36mfeaturizer.planner[0m:[36m_build_features[0m:[36m276[0m - [1mMaximum recursion depth reached at depth 2; materializing orders without traversing further.[0m


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


Target entity: customers
Max depth: 2
Intervals: ['P7D', 'P30D']
Number of entities: 2
Number of relationships: 1

4. Exploring the Entity Graph

Let’s examine the entities and their relationships.

# List all entities
print("Entities:")
for entity in featurizer.entities:
    print(f"\n  {entity.alias}:")
    print(f"    Table: {entity.table}")
    print(f"    ID: {entity.id.name if entity.id else 'None'}")
    print(
        f"    Temporal index: {entity.temporal_ix.name if entity.temporal_ix else 'None'}"
    )
    print(f"    Features: {len(entity.features)}")

Entities:

  customers:
    Table: customers
    ID: customer_id
    Temporal index: signup_date
    Features: 4

  orders:
    Table: orders
    ID: order_id
    Temporal index: order_date
    Features: 4

# Show relationships
print("Relationships:")
for rel in featurizer.relationships:
    print(f"  {rel.parent.alias}.{rel.parent_key} -> {rel.child.alias}.{rel.child_key}")

Relationships:
  customers.customer_id -> orders.customer_id

5. Generated Features

Featurizer automatically synthesizes features by:

Taking base features from each entity (columns declared as variables)
Applying aggregations when traversing backward relationships
Applying transformations to all features

# Get features for the target entity
target_features = featurizer.features[featurizer.target.alias]

print(f"Total features generated: {len(target_features)}")
print("\nSample features (first 20):")
for i, feature in enumerate(sorted(target_features, key=lambda f: f.name)[:20], 1):
    print(f"  {i:2}. {feature.name}")

Total features generated: 107

Sample features (first 20):
   1. "ABS(customers.COUNT(orders.order_date))"
   2. "ABS(customers.COUNT(orders.order_date|interval=P30D))"
   3. "ABS(customers.COUNT(orders.order_date|interval=P7D))"
   4. "ABS(customers.COUNT(orders.order_id))"
   5. "ABS(customers.COUNT(orders.order_id|interval=P30D))"
   6. "ABS(customers.COUNT(orders.order_id|interval=P7D))"
   7. "ABS(customers.COUNT(orders.status))"
   8. "ABS(customers.COUNT(orders.status|interval=P30D))"
   9. "ABS(customers.COUNT(orders.status|interval=P7D))"
  10. "ABS(customers.MAX(orders.ABS(orders.amount)))"
  11. "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P30D))"
  12. "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P7D))"
  13. "ABS(customers.MAX(orders.amount))"
  14. "ABS(customers.MAX(orders.amount|interval=P30D))"
  15. "ABS(customers.MAX(orders.amount|interval=P7D))"
  16. "ABS(customers.MEAN(orders.ABS(orders.amount)))"
  17. "ABS(customers.MEAN(orders.ABS(orders.amount)|interval=P30D))"
  18. "ABS(customers.MEAN(orders.ABS(orders.amount)|interval=P7D))"
  19. "ABS(customers.MEAN(orders.amount))"
  20. "ABS(customers.MEAN(orders.amount|interval=P30D))"

# Categorize features by type
feature_types = {}
for f in target_features:
    feature_types.setdefault(f.type, []).append(f)

print("Features by type:")
for ftype, features in sorted(feature_types.items()):
    print(f"  {ftype}: {len(features)}")

Features by type:
  categorical: 1
  index: 2
  numeric: 104

6. Understanding Feature Names

Feature names follow a pattern that describes how they were created:

AGGREGATION(entity.column) - Basic aggregation
AGGREGATION(entity.column|interval=P7D) - Time-windowed aggregation
TRANSFORM(entity.column) - Transformation applied

# Show aggregation features
agg_features = [
    f
    for f in target_features
    if "SUM(" in f.name or "MEAN(" in f.name or "COUNT(" in f.name
]

print("Sample aggregation features:")
for f in sorted(agg_features, key=lambda x: x.name)[:10]:
    print(f"  {f.name}")

Sample aggregation features:
  "ABS(customers.COUNT(orders.order_date))"
  "ABS(customers.COUNT(orders.order_date|interval=P30D))"
  "ABS(customers.COUNT(orders.order_date|interval=P7D))"
  "ABS(customers.COUNT(orders.order_id))"
  "ABS(customers.COUNT(orders.order_id|interval=P30D))"
  "ABS(customers.COUNT(orders.order_id|interval=P7D))"
  "ABS(customers.COUNT(orders.status))"
  "ABS(customers.COUNT(orders.status|interval=P30D))"
  "ABS(customers.COUNT(orders.status|interval=P7D))"
  "ABS(customers.MEAN(orders.ABS(orders.amount)))"

# Show time-windowed features
windowed = [f for f in target_features if "interval=" in f.name]

print(f"Time-windowed features: {len(windowed)}")
print("\nSample windowed features:")
for f in sorted(windowed, key=lambda x: x.name)[:10]:
    print(f"  {f.name}")

Time-windowed features: 64

Sample windowed features:
  "ABS(customers.COUNT(orders.order_date|interval=P30D))"
  "ABS(customers.COUNT(orders.order_date|interval=P7D))"
  "ABS(customers.COUNT(orders.order_id|interval=P30D))"
  "ABS(customers.COUNT(orders.order_id|interval=P7D))"
  "ABS(customers.COUNT(orders.status|interval=P30D))"
  "ABS(customers.COUNT(orders.status|interval=P7D))"
  "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P30D))"
  "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P7D))"
  "ABS(customers.MAX(orders.amount|interval=P30D))"
  "ABS(customers.MAX(orders.amount|interval=P7D))"

7. Examining the Generated SQL

Featurizer generates a PostgreSQL query with CTEs (Common Table Expressions) for each stage.

# Get the generated SQL query
sql = featurizer.query

print("Generated SQL Query:")
print("=" * 80)
print(sql)
print("=" * 80)

[32m2026-06-21 13:29:05.628[0m | [34m[1mDEBUG   [0m | [36mfeaturizer.sql[0m:[36mrender[0m:[36m40[0m - [34m[1mRendered SQL for target 'customers': 5 CTEs, 18886 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 orders
        orders_synth as (
        select
        orders.order_id, orders.order_date, orders.customer_id, amount, status
        from orders


        )
        ,
        -- transform orders
        orders_transform as (
        select
        order_id, order_date, customer_id,  abs(amount)  as "ABS(orders.amount)" , amount as amount, status as status
        from orders_synth _ego
        )
        ,
        -- Aggregate for customers
        orders_aggs_for_customers as (
        select
        orders_transform.customer_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 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "COUNT(orders.order_date|interval=P7D)" ,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 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "COUNT(orders.order_id|interval=P7D)" ,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 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "COUNT(orders.status|interval=P7D)" ,max( "ABS(orders.amount)" ) as "MAX(orders.ABS(orders.amount))" ,max( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MAX(orders.ABS(orders.amount)|interval=P30D)" ,max( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MAX(orders.ABS(orders.amount)|interval=P7D)" ,max( amount ) as "MAX(orders.amount)" ,max( amount )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MAX(orders.amount|interval=P30D)" ,max( amount )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MAX(orders.amount|interval=P7D)" ,avg( "ABS(orders.amount)" ) as "MEAN(orders.ABS(orders.amount))" ,avg( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MEAN(orders.ABS(orders.amount)|interval=P30D)" ,avg( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MEAN(orders.ABS(orders.amount)|interval=P7D)" ,avg( amount ) as "MEAN(orders.amount)" ,avg( amount )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MEAN(orders.amount|interval=P30D)" ,avg( amount )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MEAN(orders.amount|interval=P7D)" ,min( "ABS(orders.amount)" ) as "MIN(orders.ABS(orders.amount))" ,min( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MIN(orders.ABS(orders.amount)|interval=P30D)" ,min( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MIN(orders.ABS(orders.amount)|interval=P7D)" ,min( amount ) as "MIN(orders.amount)" ,min( amount )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MIN(orders.amount|interval=P30D)" ,min( amount )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "MIN(orders.amount|interval=P7D)" ,count(distinct order_date ) as "NUNIQUE(orders.order_date)" ,count(distinct order_date )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.order_date|interval=P30D)" ,count(distinct order_date )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.order_date|interval=P7D)" ,count(distinct order_id ) as "NUNIQUE(orders.order_id)" ,count(distinct order_id )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.order_id|interval=P30D)" ,count(distinct order_id )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.order_id|interval=P7D)" ,count(distinct status ) as "NUNIQUE(orders.status)" ,count(distinct status )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.status|interval=P30D)" ,count(distinct status )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "NUNIQUE(orders.status|interval=P7D)" ,stddev( "ABS(orders.amount)" ) as "STDDEV(orders.ABS(orders.amount))" ,stddev( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "STDDEV(orders.ABS(orders.amount)|interval=P30D)" ,stddev( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "STDDEV(orders.ABS(orders.amount)|interval=P7D)" ,stddev( amount ) as "STDDEV(orders.amount)" ,stddev( amount )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "STDDEV(orders.amount|interval=P30D)" ,stddev( amount )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "STDDEV(orders.amount|interval=P7D)" ,sum( "ABS(orders.amount)" ) as "SUM(orders.ABS(orders.amount))" ,sum( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "SUM(orders.ABS(orders.amount)|interval=P30D)" ,sum( "ABS(orders.amount)" )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "SUM(orders.ABS(orders.amount)|interval=P7D)" ,sum( amount ) as "SUM(orders.amount)" ,sum( amount )  filter (where daterange((aod.as_of_date - interval 'P30D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "SUM(orders.amount|interval=P30D)" ,sum( amount )  filter (where daterange((aod.as_of_date - interval 'P7D')::date, aod.as_of_date::date, '[]') @> order_date::date)  as "SUM(orders.amount|interval=P7D)"
        from orders_transform
         where order_date <= aod.as_of_date
        group by customer_id
        )
        ,
        -- sythetize aggregations and direct features for customers
        customers_synth as (
        select
        customers.customer_id, customers.signup_date, "COUNT(orders.order_date)", "COUNT(orders.order_date|interval=P30D)", "COUNT(orders.order_date|interval=P7D)", "COUNT(orders.order_id)", "COUNT(orders.order_id|interval=P30D)", "COUNT(orders.order_id|interval=P7D)", "COUNT(orders.status)", "COUNT(orders.status|interval=P30D)", "COUNT(orders.status|interval=P7D)", "MAX(orders.ABS(orders.amount))", "MAX(orders.ABS(orders.amount)|interval=P30D)", "MAX(orders.ABS(orders.amount)|interval=P7D)", "MAX(orders.amount)", "MAX(orders.amount|interval=P30D)", "MAX(orders.amount|interval=P7D)", "MEAN(orders.ABS(orders.amount))", "MEAN(orders.ABS(orders.amount)|interval=P30D)", "MEAN(orders.ABS(orders.amount)|interval=P7D)", "MEAN(orders.amount)", "MEAN(orders.amount|interval=P30D)", "MEAN(orders.amount|interval=P7D)", "MIN(orders.ABS(orders.amount))", "MIN(orders.ABS(orders.amount)|interval=P30D)", "MIN(orders.ABS(orders.amount)|interval=P7D)", "MIN(orders.amount)", "MIN(orders.amount|interval=P30D)", "MIN(orders.amount|interval=P7D)", "NUNIQUE(orders.order_date)", "NUNIQUE(orders.order_date|interval=P30D)", "NUNIQUE(orders.order_date|interval=P7D)", "NUNIQUE(orders.order_id)", "NUNIQUE(orders.order_id|interval=P30D)", "NUNIQUE(orders.order_id|interval=P7D)", "NUNIQUE(orders.status)", "NUNIQUE(orders.status|interval=P30D)", "NUNIQUE(orders.status|interval=P7D)", "STDDEV(orders.ABS(orders.amount))", "STDDEV(orders.ABS(orders.amount)|interval=P30D)", "STDDEV(orders.ABS(orders.amount)|interval=P7D)", "STDDEV(orders.amount)", "STDDEV(orders.amount|interval=P30D)", "STDDEV(orders.amount|interval=P7D)", "SUM(orders.ABS(orders.amount))", "SUM(orders.ABS(orders.amount)|interval=P30D)", "SUM(orders.ABS(orders.amount)|interval=P7D)", "SUM(orders.amount)", "SUM(orders.amount|interval=P30D)", "SUM(orders.amount|interval=P7D)", age, country
        from customers
         left join
         orders_aggs_for_customers on orders_aggs_for_customers.customer_id = customers.customer_id
        )
        ,
        -- transform customers
        customers_transform as (
        select
        customer_id, signup_date,  abs("COUNT(orders.order_date)")  as "ABS(customers.COUNT(orders.order_date))" ,  abs("COUNT(orders.order_date|interval=P30D)")  as "ABS(customers.COUNT(orders.order_date|interval=P30D))" ,  abs("COUNT(orders.order_date|interval=P7D)")  as "ABS(customers.COUNT(orders.order_date|interval=P7D))" ,  abs("COUNT(orders.order_id)")  as "ABS(customers.COUNT(orders.order_id))" ,  abs("COUNT(orders.order_id|interval=P30D)")  as "ABS(customers.COUNT(orders.order_id|interval=P30D))" ,  abs("COUNT(orders.order_id|interval=P7D)")  as "ABS(customers.COUNT(orders.order_id|interval=P7D))" ,  abs("COUNT(orders.status)")  as "ABS(customers.COUNT(orders.status))" ,  abs("COUNT(orders.status|interval=P30D)")  as "ABS(customers.COUNT(orders.status|interval=P30D))" ,  abs("COUNT(orders.status|interval=P7D)")  as "ABS(customers.COUNT(orders.status|interval=P7D))" ,  abs("MAX(orders.ABS(orders.amount))")  as "ABS(customers.MAX(orders.ABS(orders.amount)))" ,  abs("MAX(orders.ABS(orders.amount)|interval=P30D)")  as "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P30D))" ,  abs("MAX(orders.ABS(orders.amount)|interval=P7D)")  as "ABS(customers.MAX(orders.ABS(orders.amount)|interval=P7D))" ,  abs("MAX(orders.amount)")  as "ABS(customers.MAX(orders.amount))" ,  abs("MAX(orders.amount|interval=P30D)")  as "ABS(customers.MAX(orders.amount|interval=P30D))" ,  abs("MAX(orders.amount|interval=P7D)")  as "ABS(customers.MAX(orders.amount|interval=P7D))" ,  abs("MEAN(orders.ABS(orders.amount))")  as "ABS(customers.MEAN(orders.ABS(orders.amount)))" ,  abs("MEAN(orders.ABS(orders.amount)|interval=P30D)")  as "ABS(customers.MEAN(orders.ABS(orders.amount)|interval=P30D))" ,  abs("MEAN(orders.ABS(orders.amount)|interval=P7D)")  as "ABS(customers.MEAN(orders.ABS(orders.amount)|interval=P7D))" ,  abs("MEAN(orders.amount)")  as "ABS(customers.MEAN(orders.amount))" ,  abs("MEAN(orders.amount|interval=P30D)")  as "ABS(customers.MEAN(orders.amount|interval=P30D))" ,  abs("MEAN(orders.amount|interval=P7D)")  as "ABS(customers.MEAN(orders.amount|interval=P7D))" ,  abs("MIN(orders.ABS(orders.amount))")  as "ABS(customers.MIN(orders.ABS(orders.amount)))" ,  abs("MIN(orders.ABS(orders.amount)|interval=P30D)")  as "ABS(customers.MIN(orders.ABS(orders.amount)|interval=P30D))" ,  abs("MIN(orders.ABS(orders.amount)|interval=P7D)")  as "ABS(customers.MIN(orders.ABS(orders.amount)|interval=P7D))" ,  abs("MIN(orders.amount)")  as "ABS(customers.MIN(orders.amount))" ,  abs("MIN(orders.amount|interval=P30D)")  as "ABS(customers.MIN(orders.amount|interval=P30D))" ,  abs("MIN(orders.amount|interval=P7D)")  as "ABS(customers.MIN(orders.amount|interval=P7D))" ,  abs("NUNIQUE(orders.order_date)")  as "ABS(customers.NUNIQUE(orders.order_date))" ,  abs("NUNIQUE(orders.order_date|interval=P30D)")  as "ABS(customers.NUNIQUE(orders.order_date|interval=P30D))" ,  abs("NUNIQUE(orders.order_date|interval=P7D)")  as "ABS(customers.NUNIQUE(orders.order_date|interval=P7D))" ,  abs("NUNIQUE(orders.order_id)")  as "ABS(customers.NUNIQUE(orders.order_id))" ,  abs("NUNIQUE(orders.order_id|interval=P30D)")  as "ABS(customers.NUNIQUE(orders.order_id|interval=P30D))" ,  abs("NUNIQUE(orders.order_id|interval=P7D)")  as "ABS(customers.NUNIQUE(orders.order_id|interval=P7D))" ,  abs("NUNIQUE(orders.status)")  as "ABS(customers.NUNIQUE(orders.status))" ,  abs("NUNIQUE(orders.status|interval=P30D)")  as "ABS(customers.NUNIQUE(orders.status|interval=P30D))" ,  abs("NUNIQUE(orders.status|interval=P7D)")  as "ABS(customers.NUNIQUE(orders.status|interval=P7D))" ,  abs("STDDEV(orders.ABS(orders.amount))")  as "ABS(customers.STDDEV(orders.ABS(orders.amount)))" ,  abs("STDDEV(orders.ABS(orders.amount)|interval=P30D)")  as "ABS(customers.STDDEV(orders.ABS(orders.amount)|interval=P30D))" ,  abs("STDDEV(orders.ABS(orders.amount)|interval=P7D)")  as "ABS(customers.STDDEV(orders.ABS(orders.amount)|interval=P7D))" ,  abs("STDDEV(orders.amount)")  as "ABS(customers.STDDEV(orders.amount))" ,  abs("STDDEV(orders.amount|interval=P30D)")  as "ABS(customers.STDDEV(orders.amount|interval=P30D))" ,  abs("STDDEV(orders.amount|interval=P7D)")  as "ABS(customers.STDDEV(orders.amount|interval=P7D))" ,  abs("SUM(orders.ABS(orders.amount))")  as "ABS(customers.SUM(orders.ABS(orders.amount)))" ,  abs("SUM(orders.ABS(orders.amount)|interval=P30D)")  as "ABS(customers.SUM(orders.ABS(orders.amount)|interval=P30D))" ,  abs("SUM(orders.ABS(orders.amount)|interval=P7D)")  as "ABS(customers.SUM(orders.ABS(orders.amount)|interval=P7D))" ,  abs("SUM(orders.amount)")  as "ABS(customers.SUM(orders.amount))" ,  abs("SUM(orders.amount|interval=P30D)")  as "ABS(customers.SUM(orders.amount|interval=P30D))" ,  abs("SUM(orders.amount|interval=P7D)")  as "ABS(customers.SUM(orders.amount|interval=P7D))" ,  abs(age)  as "ABS(customers.age)" , "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=P7D)" as "COUNT(orders.order_date|interval=P7D)", "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=P7D)" as "COUNT(orders.order_id|interval=P7D)", "COUNT(orders.status)" as "COUNT(orders.status)", "COUNT(orders.status|interval=P30D)" as "COUNT(orders.status|interval=P30D)", "COUNT(orders.status|interval=P7D)" as "COUNT(orders.status|interval=P7D)", "MAX(orders.ABS(orders.amount))" as "MAX(orders.ABS(orders.amount))", "MAX(orders.ABS(orders.amount)|interval=P30D)" as "MAX(orders.ABS(orders.amount)|interval=P30D)", "MAX(orders.ABS(orders.amount)|interval=P7D)" as "MAX(orders.ABS(orders.amount)|interval=P7D)", "MAX(orders.amount)" as "MAX(orders.amount)", "MAX(orders.amount|interval=P30D)" as "MAX(orders.amount|interval=P30D)", "MAX(orders.amount|interval=P7D)" as "MAX(orders.amount|interval=P7D)", "MEAN(orders.ABS(orders.amount))" as "MEAN(orders.ABS(orders.amount))", "MEAN(orders.ABS(orders.amount)|interval=P30D)" as "MEAN(orders.ABS(orders.amount)|interval=P30D)", "MEAN(orders.ABS(orders.amount)|interval=P7D)" as "MEAN(orders.ABS(orders.amount)|interval=P7D)", "MEAN(orders.amount)" as "MEAN(orders.amount)", "MEAN(orders.amount|interval=P30D)" as "MEAN(orders.amount|interval=P30D)", "MEAN(orders.amount|interval=P7D)" as "MEAN(orders.amount|interval=P7D)", "MIN(orders.ABS(orders.amount))" as "MIN(orders.ABS(orders.amount))", "MIN(orders.ABS(orders.amount)|interval=P30D)" as "MIN(orders.ABS(orders.amount)|interval=P30D)", "MIN(orders.ABS(orders.amount)|interval=P7D)" as "MIN(orders.ABS(orders.amount)|interval=P7D)", "MIN(orders.amount)" as "MIN(orders.amount)", "MIN(orders.amount|interval=P30D)" as "MIN(orders.amount|interval=P30D)", "MIN(orders.amount|interval=P7D)" as "MIN(orders.amount|interval=P7D)", "NUNIQUE(orders.order_date)" as "NUNIQUE(orders.order_date)", "NUNIQUE(orders.order_date|interval=P30D)" as "NUNIQUE(orders.order_date|interval=P30D)", "NUNIQUE(orders.order_date|interval=P7D)" as "NUNIQUE(orders.order_date|interval=P7D)", "NUNIQUE(orders.order_id)" as "NUNIQUE(orders.order_id)", "NUNIQUE(orders.order_id|interval=P30D)" as "NUNIQUE(orders.order_id|interval=P30D)", "NUNIQUE(orders.order_id|interval=P7D)" as "NUNIQUE(orders.order_id|interval=P7D)", "NUNIQUE(orders.status)" as "NUNIQUE(orders.status)", "NUNIQUE(orders.status|interval=P30D)" as "NUNIQUE(orders.status|interval=P30D)", "NUNIQUE(orders.status|interval=P7D)" as "NUNIQUE(orders.status|interval=P7D)", "STDDEV(orders.ABS(orders.amount))" as "STDDEV(orders.ABS(orders.amount))", "STDDEV(orders.ABS(orders.amount)|interval=P30D)" as "STDDEV(orders.ABS(orders.amount)|interval=P30D)", "STDDEV(orders.ABS(orders.amount)|interval=P7D)" as "STDDEV(orders.ABS(orders.amount)|interval=P7D)", "STDDEV(orders.amount)" as "STDDEV(orders.amount)", "STDDEV(orders.amount|interval=P30D)" as "STDDEV(orders.amount|interval=P30D)", "STDDEV(orders.amount|interval=P7D)" as "STDDEV(orders.amount|interval=P7D)", "SUM(orders.ABS(orders.amount))" as "SUM(orders.ABS(orders.amount))", "SUM(orders.ABS(orders.amount)|interval=P30D)" as "SUM(orders.ABS(orders.amount)|interval=P30D)", "SUM(orders.ABS(orders.amount)|interval=P7D)" as "SUM(orders.ABS(orders.amount)|interval=P7D)", "SUM(orders.amount)" as "SUM(orders.amount)", "SUM(orders.amount|interval=P30D)" as "SUM(orders.amount|interval=P30D)", "SUM(orders.amount|interval=P7D)" as "SUM(orders.amount|interval=P7D)", case when country::text = 'AU' then 1 else 0 end as "customers.country=AU" , case when country::text = 'CA' then 1 else 0 end as "customers.country=CA" , case when country::text = 'DE' then 1 else 0 end as "customers.country=DE" , case when country::text = 'FR' then 1 else 0 end as "customers.country=FR" , case when country::text = 'UK' then 1 else 0 end as "customers.country=UK" , case when country::text = 'US' then 1 else 0 end as "customers.country=US" , age as age
        from customers_synth _ego
        )


        select * from customers_transform
        ) as t

        order by aod.as_of_date

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

SQL Structure:

The query has this structure:

SELECT aod.as_of_date, t.*
FROM as_of_dates AS aod
CROSS JOIN LATERAL (
    WITH
        -- CTEs for each entity's synthesis and transformation
        orders_synth AS (...),
        orders_transform AS (...),
        orders_aggs_for_customers AS (...),
        customers_synth AS (...),
        customers_transform AS (...)
    SELECT * FROM customers_transform
) AS t
ORDER BY aod.as_of_date

# Show the CTEs that were generated
print(f"Number of CTEs: {len(featurizer.ctes)}")
print("\nCTE names:")
for cte in featurizer.ctes:
    # Extract CTE name from the query
    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: 5

CTE names:
  - orders_synth
  - orders_transform
  - orders_aggs_for_customers
  - customers_synth
  - customers_transform

8. Available Primitives

Featurizer comes with many built-in primitives. Let’s explore them.

from featurizer.primitives.utils import list_aggregations, list_transformations

# List available aggregations
aggs = list(list_aggregations())
print(f"Available aggregations ({len(aggs)}):")
print(f"  {', '.join(aggs)}")

Available aggregations (69):
  acf_1, age_in_system, all, any, bbox_area, burstiness, cosinor_amplitude_weekly, count, cross_type_latency, cv, distance_travelled, entropy, event_rate, first_passage_time, gap_cv, gap_max, gap_mean, gap_min, gap_stddev, geometric_mean, gini, harmonic_mean, hhi, inter_event_hazard_proxy, iqr, kl_drift, kurtosis, longest_streak, markov_conditional_entropy, max, max_transition_prob, mean, mean_deviation, median, median_absolute_deviation, min, min_max_scale, mode, ngram_2_freq, ngram_3_freq, nunique, p10, p25, p75, p90, p95, p99, radius_of_gyration, range, recency, recurrence_interval, rework_count, right_censoring_indicator, sequence_entropy, skewness, spatial_std, state_volatility, stddev, sum, tenure, theil, time_in_current_state, time_span, transition_matrix_summary, trimmed_mean_10, variance, variance_ratio, wasserstein_drift, z_score

# List available transformations
transforms = list(list_transformations())
print(f"\nAvailable transformations ({len(transforms)}):")

# Group by category
categories = {
    "math": [
        "abs",
        "exp",
        "ln",
        "log",
        "sqrt",
        "cbrt",
        "sign",
        "ceil",
        "floor",
        "trunc",
    ],
    "date": [
        "day",
        "dow",
        "dom",
        "doy",
        "year",
        "month",
        "hour",
        "quarter",
        "week",
        "week_of_year",
        "century",
    ],
    "rolling": [t for t in transforms if "rolling" in t],
    "lag": [t for t in transforms if "lag_" in t],
    "cumulative": ["cum_sum", "cum_mean", "cum_max", "cum_min", "cum_count"],
}

for cat, items in categories.items():
    available = [i for i in items if i in transforms]
    if available:
        print(f"  {cat}: {', '.join(available)}")

Available transformations (83):
  math: abs, exp, ln, log, sqrt, cbrt, sign, ceil, floor, trunc
  date: day, dow, dom, doy, year, month, hour, quarter, week, week_of_year, century
  rolling: rolling_iqr_14, rolling_iqr_7, rolling_mean_14, rolling_mean_3, rolling_mean_7, rolling_median_5, rolling_median_7, rolling_std_14, rolling_std_3, rolling_std_7
  lag: lag_1, lag_3, lag_7
  cumulative: cum_sum, cum_mean, cum_max, cum_min, cum_count

9. Summary

In this tutorial, we learned:

Configuration: How to define entities, relationships, and feature generation parameters in YAML
Entity Graph: How Featurizer models the relationships between tables
Feature Synthesis: How features are automatically generated through aggregations and transformations
SQL Generation: How the planner produces CTEs and the renderer creates the final query
Primitives: The available aggregation and transformation primitives

Next Steps:

Try Example 2 (Temporal Joins) for as-of join semantics
Try Example 3 (Deep Nesting) for multi-level relationships
Try Example 4 (Custom Primitives) to extend Featurizer

# Final summary
print("Feature Generation Summary")
print("=" * 40)
print(f"Target: {featurizer.target.alias}")
print(f"Depth: {featurizer.max_depth}")
print(f"Intervals: {', '.join(featurizer.intervals)}")
print(f"Total features: {len(target_features)}")
print(f"SQL query length: {len(sql):,} characters")
print(f"CTEs generated: {len(featurizer.ctes)}")

Feature Generation Summary
========================================
Target: customers
Depth: 2
Intervals: P7D, P30D
Total features: 107
SQL query length: 18,886 characters
CTEs generated: 5