SQL for Machine Learning

Patterns for extracting ML-ready features from SQL Server — turning normalized relational data into the wide, denormalized, NULL-free datasets that models consume.

When to Use

• Building a feature table or training dataset from application data
• Feature engineering in T-SQL (rolling aggregations, lag features, RFM scoring)
• Encoding categorical variables (one-hot, ordinal, frequency encoding)
• Handling NULLs for ML (imputation strategies in SQL)
• Sampling and train/test splitting from SQL Server
• Exporting large datasets to CSV, Parquet, or pandas
• Preventing data leakage in temporal feature queries
• Writing queries that feed scikit-learn, XGBoost, or PyTorch pipelines

When NOT to use: application schema design (table design, naming conventions, access control), query performance tuning (execution plans, index tuning, wait stats), BI dashboards and summary reports (GROUPING SETS, pivot tables, dashboard queries), or running R/Python inside SQL Server (SQL Server ML Services).

Feature Table Build Workflow

Follow these steps in order. Each step has a validation checkpoint.

1. Set snapshot date — Anchor all features to a fixed @SnapshotDate. Never use GETDATE() inside feature queries.

   • Validate: SELECT @SnapshotDate returns the intended date.

2. Build base entity table — SELECT DISTINCT the entity key into a temp table #Base. One row per entity, primary key only — no features yet.

   SELECT DISTINCT CustomerId
   INTO #Base
   FROM Customers
   WHERE SignupDate < @SnapshotDate;

   • Validate: SELECT COUNT(*), COUNT(DISTINCT CustomerId) FROM #Base — counts must match (no duplicates).

3. Join features with temporal bounds — LEFT JOIN each feature set to #Base. Bound every join with AND EventDate <= @SnapshotDate.

   SELECT b.CustomerId,
          DATEDIFF(DAY, f.LastOrder, @SnapshotDate) AS Recency,
          f.OrderCount AS Frequency
   FROM #Base b
   LEFT JOIN FeatureCTE f ON f.CustomerId = b.CustomerId;

   • Validate: Row count equals #Base row count. No feature references dates after @SnapshotDate.

4. Impute NULLs — Replace every NULL with an explicit value. Use COALESCE with mean, median, zero, or a sentinel depending on the column semantics.

   • Validate: SELECT SUM(CASE WHEN col IS NULL THEN 1 ELSE 0 END) FROM FeatureTable returns 0 for every column.

5. Encode categoricals — One-hot, ordinal, or frequency encode all non-numeric columns.

   • Validate: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'FeatureTable' — all columns are numeric types (int, float, decimal, bit).

6. Split train/test — Use hash-based split for cross-sectional data or cutoff-date split for time series.

   • Validate: SELECT SplitLabel, COUNT(*) FROM FeatureTable GROUP BY SplitLabel — verify expected proportions (e.g., 80/20).

7. Export — BCP, pandas.read_sql, or FOR JSON depending on the consumer.

Feature Engineering Taxonomy

Quick Reference: SQL Pattern → ML Concept

Common Mistakes

Reference Files

• Feature Engineering — numeric, categorical, temporal, rolling windows, lag, RFM, text
• Sampling and Splitting — TABLESAMPLE, hash-based splits, NTILE k-fold, stratified, time-based
• NULL Imputation — mean/median/mode/forward-fill/constant, missingness indicators
• Export Patterns — BCP, pandas.read_sql, chunked reads, FOR JSON, Parquet, feature stores
• Data Leakage — temporal, target, train/test leakage; prevention patterns in SQL