SQL for Data Scientists: Build a Leakage-Safe Feature Table

The first modelling error in this case study can occur before Python or R opens. If the feature table exposes casual or registered, the model can recover the total rental count because cnt = casual + registered. The score would measure access to the answer, not useful forecasting skill.

This guide uses SQL to make the analytical contract executable. DuckDB reads the pinned source, assigns explicit types, checks the target identity, defines temporal partitions and exports only fields that are available at the stated prediction moment.

What you will produce

The SQL stage writes two generated artefacts:

• artifacts/features.csv, containing 17,379 ordered observations, an explicit partition and no post-outcome components; and
• artifacts/data_quality.json, recording the engine version, quality checks and split counts.

The next guide consumes features.csv. This boundary is deliberate: the modelling code should receive a reviewed analytical table rather than rediscovering source semantics inside a notebook.

Prerequisites and prediction contract

You should be comfortable with columns, data types and basic regression vocabulary. You do not need database administration experience. DuckDB is embedded and the repository runner creates an in-memory database.

The prediction contract is:

Predict the total rentals for an hour before any rental count for that hour has been observed.

The checked-in file contains 2011–2012 Capital Bikeshare observations. It is a teaching benchmark, not current transport evidence.

This sentence determines feature eligibility. Calendar fields and a weather forecast could plausibly exist before the hour. casual, registered and cnt are recorded outcomes. In a production system, even the weather variables would need a sharper definition: forecast values available at prediction time are not equivalent to weather subsequently observed for the same hour.

Set up the exact environment

From the repository root:

python3 -m venv examples/programming-for-data-science/.venv
examples/programming-for-data-science/.venv/bin/python -m pip install \
  -r examples/programming-for-data-science/requirements.txt
examples/programming-for-data-science/.venv/bin/python \
  examples/programming-for-data-science/data/check_data.py

The final command checks the LF-normalised repository file against SHA-256 b03a2d02e8c10f435c43c7f0b358b7e34a003afea53dbc37f0183f2763295133. It also checks the exact header, row count, source identifier and timestamp uniqueness, first and last timestamps, and casual + registered = cnt for every row.

Do not skip the digest because a CSV opens successfully. A changed input may remain syntactically valid while producing incomparable outputs.

Why DuckDB here

DuckDB can query a local CSV without running a server and is well suited to an inspectable teaching package. That convenience does not make it a universal production choice. Existing warehouses may supply access control, lineage, scheduling, workload isolation and governed tables that an embedded local process does not. The SQL concepts travel; engine-specific syntax and operations require review.

Load a typed raw table

The executable source is examples/programming-for-data-science/sql/build_features.sql. The Python runner binds the absolute source path as a DuckDB variable, avoiding a machine-specific path in the query. The raw projection renames the outcome fields so their role is visible:

CREATE OR REPLACE TABLE raw_bike_hour AS
SELECT
  instant::INTEGER AS instant,
  dteday::DATE AS observation_date,
  hr::UTINYINT AS hour,
  casual::INTEGER AS casual_rentals,
  registered::INTEGER AS registered_rentals,
  cnt::INTEGER AS target_rentals
FROM read_csv(getvariable('data_path'), header = true, strict_mode = true);

The full query also types season, month, weekday, holiday, working-day, weather and normalised environmental fields. Explicit casts turn unexpected source changes into failures close to ingestion rather than silent changes deeper in the model.

The source contains 17 columns. The final feature table has 16, but that count does not mean only one source field was removed. It derives a timestamp, split and elapsed-day feature; it also removes both target components and retains the total solely as the supervised-learning outcome.

Build time before building features

Randomly distributing rows would allow later 2012 conditions to influence training records used to predict earlier months. The query instead creates one timestamp and partitions by date:

SELECT
  observation_date + hour * INTERVAL '1 hour' AS observed_at,
  CASE
    WHEN observation_date < DATE '2012-07-01' THEN 'train'
    WHEN observation_date < DATE '2012-10-01' THEN 'validation'
    ELSE 'test'
  END AS split
FROM raw_bike_hour;

The resulting counts are:

Partition	Period	Rows	Permitted use
Train	Before 1 July 2012	13,003	Estimate preprocessing, baseline groups and model coefficients
Validation	1 July–30 September 2012	2,208	Diagnose and compare candidate choices
Test	1 October–31 December 2012	2,168	One final assessment after choices are fixed

The source is hourly but not a perfectly complete hourly sequence. Uniqueness does not imply continuity. A production pipeline should decide whether missing hours represent zero demand, missing collection or another state; this exercise does not impute them.

Inspect temporal gaps with a window function

A window-function diagnostic makes that distinction visible without filling the gaps:

WITH ordered AS (
  SELECT
    observed_at,
    lag(observed_at) OVER (ORDER BY observed_at) AS previous_observed_at
  FROM bike_features
)
SELECT
  count(*) FILTER (
    WHERE observed_at - previous_observed_at > INTERVAL '1 hour'
  ) AS gaps_after_recorded_hours,
  max(observed_at - previous_observed_at) AS longest_gap
FROM ordered;

The pinned file contains 75 intervals longer than one hour; the longest is 37 hours. Those are diagnostics, not permission to treat the absent rows as zero demand. Any imputation rule would need source knowledge and should be fitted without reading the final holdout.

Make quality failures queryable

The SQL creates a data_quality table rather than hiding checks in console output. It tests:

• exactly 17,379 rows;
• 17,379 distinct source identifiers;
• 17,379 distinct timestamps;
• zero failures of the target identity;
• zero nulls in required fields; and
• zero out-of-range values under the published codebook.

Each row contains check_name, observed, expected and passed. The runner stops before export if any check is false. It then inspects the feature-table schema and fails if a prohibited post-outcome column appears.

This is a minimum contract. Real work should also measure missingness by field and period, unexpected category levels, distribution shifts, late-arriving rows, join cardinality and source freshness. A row-count assertion alone cannot establish analytical fitness.

Window functions are often useful for lagged features and rolling diagnostics, but every window must end before the prediction moment. This first table omits lagged outcomes because the source does not define their operational availability or a policy for missing hours.

Run and inspect the export

examples/programming-for-data-science/.venv/bin/python \
  examples/programming-for-data-science/sql/build_features.py

Expected console checkpoint:

features_ok rows=17379 train=13003 validation=2208 test=2168

Inspect the header before modelling:

instant,observed_at,split,season,month,hour,holiday,weekday,working_day,
weather_situation,temperature_normalised,feels_like_temperature_normalised,
humidity_normalised,wind_speed_normalised,days_since_start,target_rentals

casual_rentals and registered_rentals are absent. target_rentals remains because supervised training needs the outcome; the Python feature builder has its own allow-list and never places that field in the predictor vector.

Port the workflow carefully

This 1.1 MB file does not need indexes or distributed execution. Performance advice should follow evidence from a representative workload, not a generic checklist. For a warehouse-scale version:

• stage the immutable raw extract separately from analytical transformations;
• test join cardinality before adding station, weather or event tables;
• partition or cluster on fields used by actual scan patterns;
• inspect the engine’s execution plan and bytes scanned;
• materialise only when repeated computation, cost or lineage justifies it; and
• compare results after any dialect translation.

DuckDB’s read_csv, interval arithmetic, filtered aggregates and getvariable call are engine-specific details. PostgreSQL, BigQuery, Snowflake, SQL Server and Spark SQL differ in file access, date arithmetic, type names and scripting. Preserve the contract and tests when translating the syntax.

Treat local data as governed data

The example contains no direct personal identifiers, but public licensing does not remove operational responsibilities. In applied work:

• bind parameters rather than concatenating untrusted paths or predicates;
• grant the analysis identity only the required read and write permissions;
• keep secrets outside SQL files and notebooks;
• record the source version, licence and access conditions;
• avoid exporting row-level personal or commercially sensitive data into uncontrolled folders; and
• define retention and deletion rules for generated artefacts.

The runner escapes its repository-controlled export path. That narrow safeguard is not a general input-sanitisation layer.

What this guide deliberately leaves out

The original article surveyed database products, cloud trends and AI features. Those sections have been replaced by one tested workflow because product lists age quickly and do not teach an analytical decision. This guide also does not cover transactions, operational schema design, database administration or query-engine benchmarking.

SQL is the right tool here for typed projection, relational validation, temporal partitioning and a reviewable handover. It is not the only place to train a model or quantify uncertainty. Continue with Python for Data Scientists after the feature contract passes.

Primary references

• DuckDB (2026), DuckDB documentation. Use the versioned 1.5 behaviour when reproducing this package.
• DuckDB (2026), CSV import documentation.
• Fanaee-T, H. (2013), Bike Sharing, UCI Machine Learning Repository, CC BY 4.0.

AI assistance helped draft the guide and code. The executable source, dataset digests and quality assertions provide material for human review; they do not by themselves establish production suitability.