About Data Science

Data science is the practice of using data to answer questions and make predictions: collecting it, cleaning it, exploring it, fitting statistical or machine-learning models, and communicating or deploying the result. In nearly every organisation the data lives in a database, so most of the day-to-day work is not modelling. It is finding the right tables, joining them correctly and reducing millions of rows to the dataset a model or a chart needs. That part is done in SQL.

This page looks at data science from the database side: where databases sit in the workflow, which SQL a data scientist actually writes, and the mistakes that produce wrong answers without any error message.

The workflow, and where the database appears

Stage	What happens	Where databases come in
Frame the question	Turn "why are customers leaving?" into something measurable	Check that the data needed to answer it is actually recorded
Collect	Bring data together from applications, logs and third parties	Operational databases are the source; a warehouse or lakehouse is the destination
Clean and explore	Find gaps, duplicates and outliers; understand distributions	SQL profiling queries, or DuckDB and pandas on an extract
Build features	Turn raw rows into one row per thing being predicted	SQL aggregation and window functions
Model	Fit and evaluate	Usually Python or R outside the database; sometimes in-database ML
Deliver	Dashboard, report, or a model serving predictions	Predictions are written back to tables; applications and BI tools read them

Three kinds of database show up:

• Operational (OLTP) databases such as PostgreSQL, MySQL and SQL Server run the application. They hold the freshest data, and they are the wrong place to run heavy analysis, because a large scan competes with customer traffic. Use a read replica or an extract.
• Analytical stores: cloud warehouses (Snowflake, BigQuery, Amazon Redshift) and lakehouses (Parquet files in object storage, queried by Spark, Trino or Databricks). They are columnar, built for scanning many rows of a few columns, and this is where most data-science SQL runs.
• Local analytical engines, mainly DuckDB, which runs inside a Python process and queries Parquet and CSV files directly. For data that fits on one machine this often removes the need for a cluster.

A small schema to work with

The examples use two tables and run as written on PostgreSQL.

CREATE TABLE customers (
    customer_id  integer PRIMARY KEY,
    signup_date  date NOT NULL,
    country      text
);

CREATE TABLE orders (
    order_id     integer PRIMARY KEY,
    customer_id  integer NOT NULL REFERENCES customers (customer_id),
    order_date   date NOT NULL,
    amount       numeric(10,2)
);

INSERT INTO customers VALUES
    (1, '2026-01-10', 'UK'),
    (2, '2026-02-03', 'DE'),
    (3, '2026-03-21', NULL);

INSERT INTO orders VALUES
    (101, 1, '2026-02-01', 40.00),
    (102, 1, '2026-05-15', 60.00),
    (103, 1, '2026-08-20', NULL),
    (104, 2, '2026-03-01', 25.00);

Customer 3 has never ordered and has no country recorded, and order 103 has no amount. Real data always has rows like these, and they are where the errors come from.

Step one: profile before you trust anything

Before modelling, count what you have:

SELECT count(*)                         AS orders,
       count(amount)                    AS orders_with_amount,
       count(DISTINCT customer_id)      AS customers_who_ordered,
       min(order_date)                  AS first_order,
       max(order_date)                  AS last_order,
       round(avg(amount), 2)            AS avg_amount
FROM orders;

 orders | orders_with_amount | customers_who_ordered | first_order | last_order | avg_amount
--------+--------------------+-----------------------+-------------+------------+------------
      4 |                  3 |                     2 | 2026-02-01  | 2026-08-20 |      41.67

Two things to read from this. count(*) and count(amount) differ, so one amount is NULL. And avg(amount) is 41.67, which is 125 divided by 3, not by 4: aggregate functions skip NULLs. Whether the missing amount should count as zero, be excluded or be investigated is a decision you have to make on purpose.

Step two: build a feature table

Most models need one row per entity. Suppose you want to predict which customers will order again, using what was known on 1 July 2026:

SELECT c.customer_id,
       coalesce(c.country, 'unknown')                    AS country,
       DATE '2026-07-01' - c.signup_date                 AS days_since_signup,
       count(o.order_id)                                 AS orders_before_cutoff,
       coalesce(sum(o.amount), 0)                        AS spend_before_cutoff,
       DATE '2026-07-01' - max(o.order_date)             AS days_since_last_order
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.order_date < DATE '2026-07-01'
GROUP BY c.customer_id, c.country, c.signup_date
ORDER BY c.customer_id;

 customer_id | country | days_since_signup | orders_before_cutoff | spend_before_cutoff | days_since_last_order
-------------+---------+-------------------+----------------------+---------------------+-----------------------
           1 | UK      |               172 |                    2 |              100.00 |                    47
           2 | DE      |               148 |                    1 |               25.00 |                   122
           3 | unknown |               102 |                    0 |                   0 |

The details that matter:

• LEFT JOIN, not JOIN. An inner join would silently drop customer 3, and a model trained only on customers who have ordered cannot learn anything about those who have not.
• The date filter is in the ON clause. Put o.order_date < ... in WHERE instead and the left join turns back into an inner join, because the NULL order dates of customer 3 fail the test.
• count(o.order_id), not count(*). count(*) would report one order for customer 3, counting the NULL-padded row.
• The cutoff date. Order 103 was placed in August, after the cutoff, so it is excluded. Using information from after the moment of prediction is called leakage. It makes a model look excellent in testing and fail in production, and it is the most common serious error in applied machine learning.

Step three: window functions for behaviour over time

Window functions compute across related rows without collapsing them, which is what sequence features need:

SELECT customer_id,
       order_id,
       order_date,
       row_number() OVER w                          AS order_number,
       order_date - lag(order_date) OVER w          AS days_since_previous,
       sum(amount) OVER w                           AS running_spend
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
ORDER BY customer_id, order_date;

 customer_id | order_id | order_date | order_number | days_since_previous | running_spend
-------------+----------+------------+--------------+---------------------+---------------
           1 |      101 | 2026-02-01 |            1 |                     |         40.00
           1 |      102 | 2026-05-15 |            2 |                 103 |        100.00
           1 |      103 | 2026-08-20 |            3 |                  97 |        100.00
           2 |      104 | 2026-03-01 |            1 |                     |         25.00

row_number(), lag(), lead() and running aggregates are supported by every current major database, including MySQL 8 and later and SQLite.

Getting data out to Python

Push the filtering and aggregation into the database and pull out only the result. This assumes you saved the feature query above as a view named customer_features:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg://analyst@db-replica.example.com/shop")
features = pd.read_sql("SELECT * FROM customer_features", engine)

SELECT * from a raw table with a hundred million rows will exhaust your laptop's memory. If you need a sample for exploration, use TABLESAMPLE. This is the PostgreSQL form; SQL Server writes it as TABLESAMPLE (1 PERCENT):

SELECT * FROM orders TABLESAMPLE BERNOULLI (1);

That returns roughly 1% of rows. The sample is different on each run unless you add REPEATABLE (seed), and it samples rows, not customers. To keep each customer's full history, sample customer IDs first and join back.

For files, DuckDB runs SQL straight over Parquet from Python:

import duckdb

duckdb.sql("""
    SELECT customer_id, sum(amount) AS spend
    FROM 'orders/*.parquet'
    GROUP BY customer_id
""").df()

Doing the modelling inside the database

Sometimes moving the data out is the bottleneck, and several platforms let you bring the model to the data:

• BigQuery ML and similar warehouse features train and score models with SQL statements.
• SQL Server Machine Learning Services (the successor to SQL Server R Services) runs Python and R scripts in the database engine.
• Vector search, through the pgvector extension for PostgreSQL and native vector types in several other engines, stores embeddings next to the rows they describe and finds nearest neighbours in SQL.

These suit scoring at scale and simple models. Experimentation with many model types is still easier in a notebook.

Mistakes that give wrong numbers without an error

• Join fan-out. Joining customers to orders and to support tickets in one query multiplies rows: 3 orders and 2 tickets become 6 rows, and sum(amount) doubles. Aggregate each child table separately, then join the results.
• Treating NULL as zero, or as a value. WHERE country <> 'UK' does not return customers whose country is NULL. Write country IS DISTINCT FROM 'UK' (PostgreSQL, SQL Server 2022 and later) or add OR country IS NULL.
• Averaging averages. The mean of per-country means is not the overall mean unless every country has the same number of rows.
• Time zones and day boundaries. A timestamp stored in UTC and grouped by date puts late-evening local orders on the wrong day.
• Running analysis on the production primary. A long query can hold back cleanup and slow the application. Ask for a replica or a warehouse.
• Unversioned queries. If the SQL that produced a training set is not saved alongside the model, the result cannot be reproduced or audited.

Further reading

• PostgreSQL documentation: Window functions tutorial
• PostgreSQL documentation: Aggregate functions
• DuckDB documentation
• pandas documentation: read_sql