SQL Data Pipeline

Building Robust Data Architecture

The Modern SQL pipelines generally follow one of the following two patterns:

1. ETL [Extract, Transform, Load]

Traditional method that transforms data before saving which is better for strict privacy regulations.

2. ELT [Extract, Load, Transform]

This is a modern cloud method in which data is being transformed inside the warehouse and is a highly scalable with SQL.

Step by Step Guide

Step 1: Data Extraction

Data originates from multiple distinct sources:

• Production databases (e.g., PostgreSQL, MySQL)

• Application APIs (e.g., Salesforce, Stripe)

• Flat files (e.g., CSV, JSON logs)

• Goal: Move this raw data into a central “Staging Area” with minimal changes.

Step 2: Data Transformation with SQL

This is where SQL shines. Raw data is converted into clean models.

• Deduplication: Removing identical records.

• Type Casting: Converting strings to dates or integers.

• Aggregations: Calculating daily totals or averages.

• Business Logic: Standardising naming conventions.

Transformation Example (SQL)

Cleaning raw e-commerce orders

WITH raw_data AS (
    SELECT 
        order_id,
        user_id,
        LOWER(status) AS order_status,
        created_at,
        COALESCE(amount, 0) AS revenue
    FROM staging.raw_orders
    WHERE order_id IS NOT NULL
),

cleaned AS (
    SELECT 
        order_id,
        user_id,
        order_status,
        CAST(created_at AS DATE) AS order_date,
        revenue
    FROM raw_data
)

INSERT INTO analytics.clean_orders
SELECT * FROM cleaned;

Data Quality Testing

Bad data breaks downstream dashboards. Pipelines must validate data automatically.

• Primary Key Tests: Ensuring IDs are unique and not null.

• Relationship Tests: Verifying a foreign key exists in the parent table.

• Accepted Value Tests: Restricting columns to expected statuses (e.g., completed, pending).

• Tools: Implemented natively using dbt tests, Great Expectations, or custom SQL assertions.

Testing Implementation (SQL)

You can write tests as SQL queries. A test passes if it returns zero rows.

Test: Ensure order_id is completely unique

SELECT 
    order_id, 
    COUNT(*) 
FROM analytics.clean_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Test: Ensure revenue is never a negative number

SELECT 
  * 
FROM analytics.clean_orders 
WHERE revenue < 0;

Full Refresh vs. Incremental Loading

How do we update our tables every day?

• Full Refresh: Rebuilds the entire destination table from scratch. Safe, simple, but slow and expensive for massive datasets.

• Incremental Load: Only processes data that has changed since the last pipeline run. Fast and cost-efficient, but requires careful code logic.

• Incremental Strategy: Append-Only [Best for immutable log data like page clicks or transactional history.]

– Find the latest record we already processed

DECLARE last_update TIMESTAMP;
SET last_update = (SELECT MAX(created_at) FROM analytics.clickstream);

-- Insert only new rows generated after that timestamp
INSERT INTO analytics.clickstream
SELECT click_id, user_id, page_url, created_at
FROM staging.raw_clicks
WHERE created_at > last_update;

-- Incremental Strategy: Upsert (Merge)

-- Best for mutable data like user profiles or order statuses that change over time.

-- MERGE updates existing records and inserts brand new ones
MERGE INTO analytics.customers target
USING staging.raw_customers source
ON target.customer_id = source.customer_id
WHEN MATCHED AND target.updated_at < source.updated_at THEN
  UPDATE SET target.email = source.email, target.updated_at = source.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, email, updated_at) 
  VALUES (source.customer_id, source.email, source.updated_at);

Step 3: Loading into Data Models

Cleaned data is structured into schemas optimized for quick reporting.

Fact Tables: Contain measurable, quantitative data (e.g., sales, clicks).

Dimension Tables: Contain descriptive attributes (e.g., customer details, product categories).

This structure is commonly known as a Star Schema.

Step 4: Pipeline Orchestration

Pipelines must run automatically, reliably, and in the correct order.

Scheduling: Running tasks hourly, daily, or weekly.

Dependency Management: Ensuring Table B only updates after Table A finishes.

Tools: Apache Airflow, Prefect, or dbt (Data Build Tool).

Summary of Benefits

Why build your pipeline around SQL?

Universal Language: Almost all data professionals know SQL.

Performance: Modern cloud warehouses process SQL queries fast.

Version Control: SQL code can be easily tracked in Git.