SQL & Databases for AI — Tutorial

Why SQL matters in ML pipelines

Most ML training data lives in relational databases, not CSV files. A production ML team uses SQL for:

Data extraction: SELECT features and labels from operational databases to build training sets.

Feature stores: materialise derived features (rolling averages, user activity counts) as SQL queries that run on schedule.

Experiment metadata: store run parameters, metrics, and model versions in a structured table for querying and comparison (complementing tools like MLflow).

Model outputs: write predictions back to a database for downstream reporting, monitoring, and A/B analysis.

The core SQL operations for ML are: filtering training examples (WHERE), aggregating features (GROUP BY + aggregate functions), joining multiple tables to enrich features, and windowed calculations (OVER / PARTITION BY) for time-series features.

Querying for ML: filters, aggregates, and joins

Filtering: SELECT * FROM events WHERE event_date >= '2024-01-01' AND label IS NOT NULL eliminates nulls and restricts the date window.

Aggregation: GROUP BY creates one row per group. Common ML aggregates: COUNT(*), AVG(value), STDDEV(value), MAX(value), MIN(value), COUNT(DISTINCT user_id).

Joins for feature enrichment: - INNER JOIN: only rows matching in both tables (safe default — excludes NULLs). - LEFT JOIN: all rows from the left table, NULLs for unmatched right rows. Used when a feature may be absent. - Avoid accidental cartesian products — always join on a specific key.

Window functions for time-series features: SELECT user_id, event_time, AVG(spend) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg FROM transactions

PARTITION BY groups like GROUP BY; ORDER BY within the window. ROWS BETWEEN defines the window frame. Window functions do not collapse rows — they add a new column to each row.

SQLite in Python and writing ML-ready queries

For local ML work, SQLite (built into Python's standard library) covers most needs — no server required. For production, the same SQL patterns work on PostgreSQL, BigQuery, Snowflake, and DuckDB.

Python integration: import sqlite3 conn = sqlite3.connect("data.db") df = pd.read_sql_query("SELECT ...", conn)

DuckDB is increasingly popular for ML data work: it runs in-process (like SQLite), speaks SQL, reads Parquet/CSV/JSON natively, and uses vectorised columnar execution — 10–100× faster than SQLite on analytical queries.

Best practices: - Use parameterised queries (cursor.execute("SELECT * FROM t WHERE id=?", (user_id,))) — never string-format user input into SQL (SQL injection). - Use EXPLAIN QUERY PLAN to check whether indexes are used. - Create indexes on columns used in WHERE and JOIN conditions. - Use LIMIT during exploration — SELECT * FROM large_table without LIMIT can return millions of rows.

Continue your research

Use these connected ModelRefs sections to compare alternatives, inspect implementation paths, and review the evidence and governance boundaries relevant to SQL & Databases for AI — Tutorial.

• Browse models
• Review providers
• Inspect benchmarks
• Explore workflows
• Learn AI concepts
• Read decision guides
• Review trust and methodology
• Start a decision path