{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "# Lesson 18: SQL Feature Engineering and Data Contracts\n", "\n", "**CS230 inspiration:** Full-cycle project work and practical ML systems beyond the model.\n", "\n", "**Demand forecasting focus:** Build leakage-safe demand features from relational tables using SQL window functions.\n", "\n", "**Learning objectives**\n", "\n", "- Create a normalized transactional schema for demand forecasting.\n", "- Write SQL that joins dimensions, creates lags, and computes rolling features.\n", "- Validate feature availability and grain before modeling.\n", "\n", "**Senior-readiness checkpoint:** You can prove your features are available at forecast time and explain the data contract to analytics engineering.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Code Cell 1 Explanation\n", "\n", "**What this cell does:** This setup cell imports the shared scientific Python stack and the curriculum helpers used by later cells. It also establishes the deterministic seed convention so repeated runs are comparable.\n", "\n", "**Why it matters:** A restart-and-run notebook should make its dependencies explicit before any modeling work begins. For forecasting, reproducibility is especially important because small data or seed changes can change validation conclusions.\n", "\n", "**Key operations to notice:** confirm that shared helper imports match the lesson's code path; check that `SEED` is defined before any generated data or model training\n", "\n", "**What to inspect:** Check that imports are grouped by purpose: general analysis packages, forecasting data helpers, metrics, plotting utilities, and any lesson-specific libraries.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "import matplotlib.pyplot as plt\n", "import numpy as np\n", "import pandas as pd\n", "\n", "from demand_forecasting_curriculum.data import (\n", " DEFAULT_FEATURE_COLUMNS,\n", " generate_daily_demand,\n", " make_sequence_dataset,\n", " make_supervised_table,\n", " time_series_split,\n", ")\n", "from demand_forecasting_curriculum.metrics import (\n", " interval_coverage,\n", " mase,\n", " pinball_loss,\n", " smape,\n", " wmape,\n", ")\n", "from demand_forecasting_curriculum.plotting import plot_predictions, plot_series\n", "\n", "SEED = 42\n", "np.random.seed(SEED)\n", "\n", "import sqlite3\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Hiring-Manager Signal\n", "\n", "A strong Data Scientist must be able to move between raw relational data and model-ready features. This lesson makes SQL, grain, leakage prevention, and feature contracts visible instead of assuming clean pandas inputs appear by magic.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Code Cell 2 Explanation\n", "\n", "**What this cell does:** This cell advances **SQL Feature Engineering and Data Contracts** by working on synthetic demand generation, SQL feature engineering. It creates or updates `df`, `item_dim`, `store_dim`, `conn` and prepares evidence that later cells use rather than hiding state in prose.\n", "\n", "**Why it matters:** The goal is tied to this lesson's forecasting focus: Build leakage-safe demand features from relational tables using SQL window functions. Each object is kept inspectable so the learner can connect code, assumptions, and modeling consequences.\n", "\n", "**Key operations to notice:** creates a synthetic retail panel with known demand drivers; creates an in-memory relational database for SQL practice; creates an inspectable table for assumptions, metrics, or decisions\n", "\n", "**What to inspect:** Before moving on, inspect shapes, date ranges, metric values, segment behavior, or generated tables. If a value looks surprisingly good, surprisingly bad, or unavailable at forecast time, treat that as a modeling clue.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = generate_daily_demand(n_stores=3, n_items=5, periods=360, seed=SEED)\n", "item_dim = pd.DataFrame(\n", " {\n", " \"item_id\": sorted(df[\"item_id\"].unique()),\n", " \"category\": [\"staple\", \"seasonal\", \"premium\", \"value\", \"impulse\"],\n", " \"case_pack\": [12, 6, 4, 24, 10],\n", " }\n", ")\n", "store_dim = pd.DataFrame(\n", " {\n", " \"store_id\": sorted(df[\"store_id\"].unique()),\n", " \"region\": [\"north\", \"central\", \"south\"],\n", " \"format\": [\"urban\", \"suburban\", \"rural\"],\n", " }\n", ")\n", "\n", "conn = sqlite3.connect(\":memory:\")\n", "df.to_sql(\"fact_daily_demand\", conn, index=False, if_exists=\"replace\")\n", "item_dim.to_sql(\"dim_item\", conn, index=False, if_exists=\"replace\")\n", "store_dim.to_sql(\"dim_store\", conn, index=False, if_exists=\"replace\")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Code Cell 3 Explanation\n", "\n", "**What this cell does:** This cell advances **SQL Feature Engineering and Data Contracts** by working on SQL feature engineering. It creates or updates `feature_sql`, `feature_table` and prepares evidence that later cells use rather than hiding state in prose.\n", "\n", "**Why it matters:** The goal is tied to this lesson's forecasting focus: Build leakage-safe demand features from relational tables using SQL window functions. Each object is kept inspectable so the learner can connect code, assumptions, and modeling consequences.\n", "\n", "**Key operations to notice:** loads SQL-engineered features back into pandas\n", "\n", "**What to inspect:** Before moving on, inspect shapes, date ranges, metric values, segment behavior, or generated tables. If a value looks surprisingly good, surprisingly bad, or unavailable at forecast time, treat that as a modeling clue.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "feature_sql = \"\"\"\n", "WITH enriched AS (\n", " SELECT\n", " demand.date,\n", " demand.store_id,\n", " demand.item_id,\n", " store.region,\n", " store.format,\n", " item.category,\n", " item.case_pack,\n", " demand.demand,\n", " demand.price,\n", " demand.promo,\n", " demand.holiday\n", " FROM fact_daily_demand AS demand\n", " JOIN dim_store AS store\n", " ON demand.store_id = store.store_id\n", " JOIN dim_item AS item\n", " ON demand.item_id = item.item_id\n", "),\n", "features AS (\n", " SELECT\n", " *,\n", " LAG(demand, 1) OVER (\n", " PARTITION BY store_id, item_id ORDER BY date\n", " ) AS lag_1,\n", " LAG(demand, 7) OVER (\n", " PARTITION BY store_id, item_id ORDER BY date\n", " ) AS lag_7,\n", " AVG(demand) OVER (\n", " PARTITION BY store_id, item_id\n", " ORDER BY date\n", " ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING\n", " ) AS rolling_mean_28,\n", " LEAD(demand, 7) OVER (\n", " PARTITION BY store_id, item_id ORDER BY date\n", " ) AS y\n", " FROM enriched\n", ")\n", "SELECT *\n", "FROM features\n", "WHERE lag_7 IS NOT NULL\n", " AND rolling_mean_28 IS NOT NULL\n", " AND y IS NOT NULL\n", "\"\"\"\n", "\n", "feature_table = pd.read_sql_query(feature_sql, conn, parse_dates=[\"date\"])\n", "feature_table.head()\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Code Cell 4 Explanation\n", "\n", "**What this cell does:** This cell advances **SQL Feature Engineering and Data Contracts** by working on the lesson workflow. It creates or updates `contract`, `grain_is_unique`, `missing_features`, `null_rates` and prepares evidence that later cells use rather than hiding state in prose.\n", "\n", "**Why it matters:** The goal is tied to this lesson's forecasting focus: Build leakage-safe demand features from relational tables using SQL window functions. Each object is kept inspectable so the learner can connect code, assumptions, and modeling consequences.\n", "\n", "**Key operations to notice:** follow the assignments, final displayed object, and any checks in the cell because those are the evidence the next cell depends on\n", "\n", "**What to inspect:** Before moving on, inspect shapes, date ranges, metric values, segment behavior, or generated tables. If a value looks surprisingly good, surprisingly bad, or unavailable at forecast time, treat that as a modeling clue.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "contract = {\n", " \"grain\": [\"date\", \"store_id\", \"item_id\"],\n", " \"target\": \"y\",\n", " \"horizon_days\": 7,\n", " \"required_features\": [\n", " \"price\",\n", " \"promo\",\n", " \"holiday\",\n", " \"lag_1\",\n", " \"lag_7\",\n", " \"rolling_mean_28\",\n", " \"category\",\n", " \"region\",\n", " ],\n", "}\n", "\n", "grain_is_unique = not feature_table[contract[\"grain\"]].duplicated().any()\n", "missing_features = sorted(set(contract[\"required_features\"]) - set(feature_table.columns))\n", "null_rates = feature_table[contract[\"required_features\"]].isna().mean().sort_values(ascending=False)\n", "\n", "{\n", " \"grain_is_unique\": grain_is_unique,\n", " \"missing_features\": missing_features,\n", " \"max_feature_null_rate\": float(null_rates.max()),\n", " \"rows\": len(feature_table),\n", "}\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Code Cell 5 Explanation\n", "\n", "**What this cell does:** This cell advances **SQL Feature Engineering and Data Contracts** by working on the lesson workflow. It creates or updates intermediate values and prepares evidence that later cells use rather than hiding state in prose.\n", "\n", "**Why it matters:** The goal is tied to this lesson's forecasting focus: Build leakage-safe demand features from relational tables using SQL window functions. Each object is kept inspectable so the learner can connect code, assumptions, and modeling consequences.\n", "\n", "**Key operations to notice:** creates an inspectable table for assumptions, metrics, or decisions\n", "\n", "**What to inspect:** Before moving on, inspect shapes, date ranges, metric values, segment behavior, or generated tables. If a value looks surprisingly good, surprisingly bad, or unavailable at forecast time, treat that as a modeling clue.\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "pd.DataFrame(\n", " [\n", " (\"date\", \"forecast origin date\", \"known\"),\n", " (\"price\", \"planned selling price\", \"known if pricing calendar is locked\"),\n", " (\"promo\", \"planned promotion flag\", \"known if promo calendar is locked\"),\n", " (\"lag_7\", \"observed demand seven days before origin\", \"known\"),\n", " (\"rolling_mean_28\", \"historical demand average before origin\", \"known\"),\n", " (\"y\", \"demand seven days after origin\", \"target only\"),\n", " ],\n", " columns=[\"column\", \"meaning\", \"availability\"],\n", ")\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Lesson Recap\n", "\n", "In this lesson, we used 5 runnable code cells to work through **SQL Feature Engineering and Data Contracts**. The practical focus was: Build leakage-safe demand features from relational tables using SQL window functions.\n", "\n", "We did this because deep learning for demand forecasting is not only about choosing an architecture. The learner needs to understand the data contract, the target and horizon, the validation path, the metric, and the operational decision supported by the forecast.\n", "\n", "The core takeaways were:\n", "\n", "- Create a normalized transactional schema for demand forecasting.\n", "- Write SQL that joins dimensions, creates lags, and computes rolling features.\n", "- Validate feature availability and grain before modeling.\n", "\n", "By the end, you should be able to explain not just what the code produced, but why each step was necessary and what could go wrong if the same step were skipped or performed with leakage.\n", "\n", "**Senior-readiness reminder:** You can prove your features are available at forecast time and explain the data contract to analytics engineering.\n" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "## Exercises\n", "\n", "- Add a `dim_calendar` table and join in week-of-year without using future demand.\n", "- Write one SQL query that checks uniqueness at the forecast grain.\n", "- Explain which features require upstream data contracts with merchandising or pricing teams.\n" ] } ], "metadata": { "kernelspec": { "display_name": "Python (deep-learning-demand)", "language": "python", "name": "deep-learning-demand" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.12" } }, "nbformat": 4, "nbformat_minor": 5 }