SQL Data Pipelines

Building Robust Data Architecture

Fredrick Ochuodho

What is a Data Pipeline?

A data pipeline is a system that moves data from a source to a destination.

• Ingestion: Collecting raw data from various sources.
• Transformation: Cleaning and structuring the data.
• Storage: Loading data into a data warehouse.
• Analysis: Querying data for business insights.

The ETL vs. ELT Architecture

Modern SQL pipelines generally follow one of two patterns:

ETL

• Extract, Transform, Load
• Traditional method.
• Transforms data before saving.
• Better for strict privacy regulations.

ELT

• Extract, Load, Transform
• Modern cloud method.
• Transforms data inside the warehouse.
• Highly scalable with SQL.

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
CREATE TABLE analytics.clean_orders AS 
SELECT 
    order_id,
    user_id,
    LOWER(status) AS order_status,
    CAST(created_at AS DATE) AS order_date,
    COALESCE(amount, 0) AS revenue
FROM staging.raw_orders
WHERE order_id IS NOT NULL;

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.

Questions?

Thank you for attending!

Please use the arrow keys on your keyboard to navigate the slides.