FastDFS - Deep Feature Synthesis for Relational Database

FastDFS is a Python library for automated feature engineering using Deep Feature Synthesis (DFS). It augments target dataframes with rich features derived from relational database structures, making it easy to create powerful features for machine learning without manual feature engineering.

Core Concept

FastDFS treats feature engineering as a table augmentation process: given any target dataframe and a relational database (RDB) containing related tables, it automatically generates new features by aggregating information across relationships.

# Your target dataframe (what you want to predict on)
target_df = pd.DataFrame({
    "user_id": [1, 2, 3],
    "item_id": [100, 200, 300], 
    "interaction_time": ["2024-01-01", "2024-01-02", "2024-01-03"]
})

# Your relational database (context for feature generation)
rdb = fastdfs.load_rdb("ecommerce_data/")  # Contains user, item, interaction tables

# Generate features automatically
enriched_df = fastdfs.compute_dfs_features(
    rdb=rdb,
    target_dataframe=target_df,
    key_mappings={"user_id": "user.user_id", "item_id": "item.item_id"},
    cutoff_time_column="interaction_time"
)
# Result: Original columns + 50+ new features like user_avg_rating, item_count_purchases, etc.

🆕 Parallel SQL (DFS2SQL Engine)

DFS2SQL can run independent feature queries on multiple DuckDB connections via dfs2sql_sql_workers (default 1 = sequential). Cutoff plans are emitted as self-contained WITH … SELECT statements so workers do not share temp tables.

features = fastdfs.compute_dfs_features(
    rdb=rdb,
    target_dataframe=target_df,
    key_mappings=key_mappings,
    cutoff_time_column="interaction_time",
    config_overrides={
        "engine": "dfs2sql",
        "engine_path": ":memory:",
        "dfs2sql_sql_workers": 2,
        "dfs2sql_threads": 8,
        "dfs2sql_memory_limit": "4GB",
    },
)

Workers share one database, including when engine_path=":memory:". The worker count controls concurrent queries; dfs2sql_threads controls DuckDB's global thread budget. More workers can use more memory and are not always faster. Start with one worker when memory is limited.

Compatible feature queries share projections by default. Set dfs2sql_merge_queries=False to execute them separately. DuckDB resource settings default to its own defaults; dfs2sql_temp_directory sets the spill directory. The DuckDB memory limit does not limit pandas frames or total process memory. Parallel floating-point aggregation and tied MODE values can differ between runs.

Speedup vs sequential (n=10k, depth=4, workers=4)

Task	sql_exec	End-to-end wall
retailrocket (cvr)	3.7×	2.5×
amazon (churn)	4.7×	2.0×
stackexchange (churn)	1.1×	1.1×

Full tables and setup notes: dfs2sql parallel SQL benchmarks.

Engine Comparison

Feature	Featuretools	DFS2SQL
Performance	Good for small data	Excellent for large data; optional parallel SQL workers
Memory Usage	High (pandas)	Low (SQL-based)
Primitives	Rich set	Core primitives
Backend	Pandas	DuckDB

Installation

pip install fastdfs

Or for development:

git clone https://github.com/HKUSHXLab/fastdfs.git
cd fastdfs
pip install -e .

Quick Start

1. Prepare Your Data

FastDFS provides multiple ways to prepare your relational data.

Option A: Create from DataFrames (Recommended)

You can create an RDB directly from pandas DataFrames. FastDFS will automatically infer the schema.

import fastdfs
import pandas as pd

# 1. Define your tables
users_df = pd.DataFrame(...)
items_df = pd.DataFrame(...)
interactions_df = pd.DataFrame(...)

# 2. Create RDB with relationships
rdb = fastdfs.create_rdb(
    name="ecommerce",
    tables={
        "user": users_df,
        "item": items_df,
        "interaction": interactions_df
    },
    primary_keys={
        "user": "user_id",
        "item": "item_id"
    },
    foreign_keys=[
        ("interaction", "user_id", "user", "user_id"),
        ("interaction", "item_id", "item", "item_id")
    ],
    time_columns={
        "interaction": "timestamp"
    }
)

# 3. Save for later use
rdb.save("ecommerce_rdb/")

# 4. Load it back
rdb = fastdfs.load_rdb("ecommerce_rdb/")

Option B: Adapt Existing Datasets

FastDFS includes adapters for popular relational dataset benchmarks.

RelBench

from fastdfs.adapter.relbench import RelBenchAdapter

# Load and convert RelBench dataset
adapter = RelBenchAdapter("rel-stack")
rdb = adapter.load()
rdb.save("rel-stack-rdb/")

DBInfer

from fastdfs.adapter.dbinfer import DBInferAdapter

# Load and convert DBInfer dataset
adapter = DBInferAdapter("diginetica")
rdb = adapter.load()
rdb.save("diginetica-rdb/")

Option C: Load from Relational Database

FastDFS supports loading data directly from SQL databases (SQLite, MySQL, PostgreSQL, DuckDB).

from fastdfs.adapter.sqlite import SQLiteAdapter
# from fastdfs.adapter.mysql import MySQLAdapter
# from fastdfs.adapter.postgres import PostgreSQLAdapter

# Connect to database
adapter = SQLiteAdapter(
      "ecommerce.db",
      time_columns={"orders": "created_at"},  # (Optional) specify which column is the time column for which table
      type_hints={"users": {"age": "float"}}  # (Optional) specify the desired column data type
)

# Or for MySQL/PostgreSQL:
# adapter = MySQLAdapter("mysql+pymysql://user:pass@host/db")

rdb = adapter.load()

2. Generate Features

import fastdfs
import pandas as pd

# Prepare your rdb from the methods above
rdb = ...

# Create or load your target dataframe
target_df = pd.DataFrame({
    "user_id": [1, 2, 3],
    "item_id": [10, 20, 30],
    "prediction_time": ["2024-01-01", "2024-01-02", "2024-01-03"]
})

# Generate features
features = fastdfs.compute_dfs_features(
    rdb=rdb,
    target_dataframe=target_df, 
    key_mappings={
        "user_id": "user.user_id",
        "item_id": "item.item_id"  
    },
    cutoff_time_column="prediction_time",
    config_overrides={"max_depth": 2}
)

print(f"Original columns: {len(target_df.columns)}")
print(f"With features: {len(features.columns)}")

3. Advanced Usage with Transforms

# Apply preprocessing transforms before feature generation
from fastdfs.transform import RDBTransformWrapper, RDBTransformPipeline, HandleDummyTable, FeaturizeDatetime

pipeline = fastdfs.DFSPipeline(
    transform_pipeline=RDBTransformPipeline([
        HandleDummyTable(),
        RDBTransformWrapper(FeaturizeDatetime(features=["year", "month", "hour"]))
    ]),
    dfs_config=fastdfs.DFSConfig(max_depth=3, engine="dfs2sql")
)

features = pipeline.run(
    rdb=rdb,
    target_dataframe=target_df,
    key_mappings=key_mappings,
    cutoff_time_column="prediction_time"
)

Key Features

• Table-Centric Design: Augment any dataframe, not just predefined datasets
• Multiple DFS Engines: Choose between Featuretools (pandas) or DFS2SQL (high-performance)
• 🆕 Parallel Feature SQL: Scale dfs2sql with dfs2sql_sql_workers (multi-connection DuckDB)
• Temporal Consistency: Built-in cutoff time support prevents data leakage
• Flexible Key Mapping: Connect target data to RDB with simple column mappings
• Transform Pipeline: Composable preprocessing transforms for data cleaning
• Type Safety: Full type hints and runtime validation
• Minimal Dependencies: Focused, lightweight package

Documentation

• User Guide: Complete tutorial with concepts and examples
• API Reference: Detailed API documentation
• Aggregation Primitives: Supported agg_primitives for dfs2sql and featuretools
• 🆕 Parallel SQL Benchmarks: Sequential vs parallel dfs2sql speedups
• Examples: Runnable code examples

Why FastDFS?

Before FastDFS (manual feature engineering):

# Manual aggregations for each feature
user_avg_rating = interactions.groupby('user_id')['rating'].mean()
user_total_purchases = interactions.groupby('user_id').size()
item_avg_rating = interactions.groupby('item_id')['rating'].mean()
# ... dozens more features ...

With FastDFS (automated):

# Automatic generation of 50+ features
features = fastdfs.compute_dfs_features(rdb, target_df, key_mappings)

FastDFS automatically discovers relationships in your data and generates meaningful aggregation features, saving weeks of manual feature engineering work.

Contributing

We welcome contributions! See our development logs for project history and architecture decisions.

License

Apache-2.0 License