Advanced SQL Pipeline

Beyond Basic Queries

You know how to pull data from a database. But what if the data you pull is a mess? Real-world datasets are rarely clean. They have duplicates, inconsistent formatting, and missing values. A common approach is to pull the raw data into a Python script and clean it with libraries like Pandas. This works, but it's not always the most efficient way.

Shifting the cleaning process into the database itself can be much faster. By transforming data at the source with SQL, you reduce the amount of data sent over the network and leverage the database's powerful, optimised engine for set-based operations. This is a core practice in modern data engineering, turning the database from a simple storage unit into an active data processing tool.

Tackling Duplicates and Disorder

One of the most frequent problems is duplicate data. Not just identical rows, but logical duplicates, like multiple entries for the same customer added on different days. Simply using DISTINCT won't catch these. For this, we need a more powerful tool: window functionss.

WITH NumberedCustomers AS (
    SELECT
        customer_id,
        email,
        registration_date,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY registration_date DESC) as rn
    FROM customers
)
SELECT
    customer_id, 
    email, 
    registration_date
FROM NumberedCustomers
WHERE rn = 1;

This query finds the most recent entry for each customer email. Let's break it down. The WITH clause creates a temporary result set. The magic happens inside the OVER() clause. PARTITION BY email groups the rows by email address. ORDER BY registration_date DESC sorts each group from newest to oldest. ROW_NUMBER() then assigns a unique, sequential number to each row within its group, starting with 1 for the newest record.

The final SELECT statement simply keeps the rows where the row number is 1, effectively deduplicating our data while keeping the latest record for each customer.

Standardising Inconsistent Data

Data is often entered by humans, leading to inconsistencies. You might find 'UK', 'United Kingdom', and 'U.K.' all in the same column. To make this data useful for analysis, it needs to be standardised. The CASE statement is your first line of defence.

UPDATE customers
SET country = 
    CASE 
        WHEN country IN ('USA', 'United States', 'America') THEN 'United States'
        WHEN country IN ('UK', 'GB', 'Great Britain') THEN 'United Kingdom'
        ELSE country
    END;

This statement inspects the country column and maps several variations to a single, standard name. It’s a powerful way to enforce consistency on categorical data.

For more complex structural problems, like inconsistent date formats or codes embedded in text fields, we turn to Regular Expressionss. Regex lets you define a search pattern to find, validate, or extract strings.

-- PostgreSQL Syntax
SELECT 
    order_id,
    -- Extracts a date in YYYY-MM-DD format from a text field
    REGEXP_MATCHES(order_notes, '\d{4}-\d{2}-\d{2}') as extracted_date
FROM orders
WHERE order_notes ~ '\d{4}-\d{2}-\d{2}';

Here, REGEXP_MATCHES uses a pattern to find a substring that looks like a date (\d{4} means four digits, \d{2} means two digits). This allows you to pull structured information out of unstructured text, directly within your query. Most modern databases support regex functions, though the exact syntax may vary slightly.

Filling the Gaps

Missing data, represented as NULL, can break calculations and skew analysis. A simple way to handle this is with COALESCE, which returns the first non-NULL value in a list of arguments. It's great for providing a default value.

SELECT COALESCE(product_description, 'No description provided') FROM products;

But what if the right value depends on other rows? For time-series data, a missing value might be best estimated from the previous or next entry. This is where LAG and LEAD come in. These window functions let you access data from a preceding or succeeding row.

-- Fills missing daily revenue with the value from the previous day
WITH DailySales AS (
    SELECT 
        sale_date,
        revenue,
        LAG(revenue, 1) OVER (ORDER BY sale_date) as prev_day_revenue
    FROM sales_report
)
SELECT 
    sale_date,
    COALESCE(revenue, prev_day_revenue) as imputed_revenue
FROM DailySales;

In this query, LAG(revenue, 1) fetches the revenue from the previous row, ordered by date. We then use COALESCE to fill in the current row's revenue with this previous value if it's NULL. This is a much more intelligent imputation strategy than just filling with a global average.

Database vs. Client

So, when should you clean data in SQL versus in a client-side tool like Python? It's a trade-off.

	Database-Side (SQL)	Client-Side (Python/Pandas)
Pros	Faster for large datasets. Reduces network traffic. Leverages optimised database engine.	More flexible, complex logic. Richer libraries for stats/ML. Easier for iterative exploration.
Cons	SQL can be less expressive for complex logic. Less portable between database systems.	Can be slow with huge data. Consumes significant local memory. Requires pulling all raw data first.

A good rule of thumb is to perform large-scale, set-based operations like deduplication, standardisation, and simple imputation in SQL. For complex statistical imputation or transformations that require iterative logic, a client-side library is often a better fit. Mastering both allows you to build efficient and robust data pipelines.

Quiz Questions 1/6

What is a primary advantage of performing data cleaning transformations directly within a SQL database rather than in a client-side application like a Python script?

Quiz Questions 2/6

You have a customers table with duplicate entries for the same email address. Which query correctly uses a window function to select only the most recent entry for each customer based on registration_date?

Professional Data Analytics Mastery