SQL Project Types

Editorial

Overview

In the real world, SQL is used in many different types of projects. Understanding these project types helps you see the bigger picture of how data teams work and where your skills fit in.

In this lesson, we'll explore the most common SQL project types you'll encounter in the industry, and then we'll explain how our project combines several of them together.

Data Warehouse Projects

A Data Warehouse project is about building a centralized storage system that collects data from multiple sources and organizes it for analysis and reporting.

Imagine a company that uses one system for tracking customers (CRM), another system for managing orders (ERP), and yet another for tracking website analytics. Each system has its own database with its own structure. A data warehouse brings all of that data together into a single, clean, well-organized database.

What You Build

Database schemas designed for analytical queries (not day-to-day transactions)
Tables organized in layers or by subject area (customers, products, sales, etc.)
Data pipelines that move data from source systems into the warehouse
Views and aggregations that make it easy for analysts to query the data

Key Skills Involved

Database design and data modeling
Writing DDL statements (CREATE TABLE, ALTER TABLE)
Understanding business requirements and translating them into table structures
Designing for query performance (indexing, partitioning)

-- Example: Creating a fact table in a data warehouse
-- (a generic table — our project's gold.fact_sales is a view, built later)
CREATE TABLE dw.fact_orders (
    sale_id       INT IDENTITY(1,1) PRIMARY KEY,
    customer_key  INT NOT NULL,
    product_key   INT NOT NULL,
    order_date    DATE NOT NULL,
    quantity      INT NOT NULL,
    total_amount  DECIMAL(10,2) NOT NULL
);

Data warehouse projects are common in medium to large companies. The resulting warehouse is the foundation that supports all reporting, dashboards, and data-driven decision making.

ETL Pipeline Projects

ETL stands for Extract, Transform, Load. An ETL pipeline project focuses on building the processes that move data from point A to point B, cleaning and reshaping it along the way.

Think of it like a factory assembly line:

Extract: Raw materials (data) come in from various suppliers (source systems)
Transform: The materials are cleaned, reshaped, and assembled
Load: The finished product is placed on the shelf (loaded into the target database)

What You Build

Stored procedures that extract data from source tables or files
Transformation logic that cleans, standardizes, and enriches the data
Scheduled jobs that run the pipeline automatically (daily, hourly, etc.)
Error handling and logging so you know when something goes wrong

-- Example: A simple ETL stored procedure
CREATE PROCEDURE silver.load_customers
AS
BEGIN
    -- Truncate and reload
    TRUNCATE TABLE silver.customers;

    INSERT INTO silver.customers (
        customer_id,
        full_name,
        email,
        country
    )
    SELECT
        customer_id,
        TRIM(first_name) + ' ' + TRIM(last_name) AS full_name,
        LOWER(TRIM(email)) AS email,
        ISNULL(country, 'Unknown') AS country
    FROM bronze.crm_customers;
END;

ETL pipelines are the backbone of any data system. Without them, data stays siloed in source systems and can't be used for analytics. Building reliable ETL pipelines is one of the most in-demand skills for data engineers.

Data Analytics Projects

A Data Analytics project is about writing SQL queries to extract insights from data. The goal is to answer business questions like:

What were our total sales last quarter?
Which product category is growing the fastest?
What percentage of customers made a repeat purchase?
Which region has the highest customer churn rate?

Analytics projects are typically done on top of a data warehouse. Once data is clean and organized, analysts and data scientists can write queries to explore it.

What You Build

Analytical queries using GROUP BY, JOIN, window functions, CTEs, and aggregations
Views that encapsulate complex queries for reuse
Reports and dashboards (often connecting SQL to tools like Power BI or Tableau)
Ad-hoc analyses to answer one-off business questions

-- Example: Analytics query — monthly sales trend
SELECT
    FORMAT(order_date, 'yyyy-MM') AS order_month,
    COUNT(DISTINCT customer_key) AS unique_customers,
    SUM(quantity) AS total_units_sold,
    SUM(total_amount) AS total_revenue
FROM dw.fact_orders
GROUP BY FORMAT(order_date, 'yyyy-MM')
ORDER BY order_month;

Data analytics is often the most visible type of SQL work because the results go directly to business leaders and decision makers. Strong SQL skills in analytics open doors to roles like Data Analyst, Business Intelligence Analyst, and Analytics Engineer.

Database Administration Projects

Database Administration (DBA) projects focus on the health, performance, and security of the database itself. A DBA is like the mechanic who keeps the engine running smoothly.

While other project types focus on what's inside the database (data, queries, pipelines), DBA work focuses on the database system itself.

What You Work On

Performance tuning: Optimizing slow queries, adding indexes, analyzing execution plans
Backup and recovery: Setting up automated backups and testing restore procedures
Security: Managing user permissions, roles, and access control
Monitoring: Watching for disk space issues, blocking queries, and resource bottlenecks
Maintenance: Rebuilding indexes, updating statistics, managing database growth

-- Example: DBA tasks

-- Create a backup
-- (add COMPRESSION on Standard edition or higher; Express, which this course uses, can't compress)
BACKUP DATABASE DataWarehouse
TO DISK = 'C:\Backups\DataWarehouse.bak'
WITH FORMAT;

-- Check index fragmentation
SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 30;

DBA skills are important even if you're not a dedicated DBA. Understanding how to optimize queries, manage permissions, and maintain databases makes you a more well-rounded SQL professional.

How Our Project Combines Multiple Types

The project we're building in this course isn't just one type — it's a Data Warehouse + ETL + Analytics project all in one. Here's how the pieces fit together:

Our project — three project types in one: design the warehouse, build the pipelines, analyze the result. The project's pipeline: CSV files (CRM, ERP) → Bronze tables → Silver tables → Gold views → analysis queries. 1, the data warehouse covers Bronze, Silver and Gold: design the layers — Bronze and Silver tables, Gold views. 2, the ETL pipelines cover the CSV files, Bronze and Silver: load_bronze reads the CSVs, load_silver cleans them. 3, the data analytics covers Gold and the analysis: Gold views, then the EDA and advanced analyses. Not in this course: DBA work — backups, tuning, security.

This multi-type approach mirrors how real projects work in industry. A data engineer rarely does just one thing — they design the warehouse, build the pipelines, and sometimes even write the analytics queries.

By working through all three aspects in a single project, you'll develop a complete understanding of how data flows from raw source files all the way to actionable business insights.

Summary

Here's a summary of the SQL project types and how they relate to our project:

Project Type	Focus	In Our Project?
Data Warehouse	Designing structured storage for analytics	✅ Yes — we build the full Medallion Architecture
ETL Pipelines	Moving and transforming data between systems	✅ Yes — stored procedures load Bronze and Silver
Data Analytics	Querying data for business insights	✅ Yes — we create Gold layer views and reports
Database Administration	Managing database health and performance	⬜ No — not covered in this course

Understanding these project types gives you a mental framework for how the SQL skills you're learning are applied in real-world jobs.