Mastering SQL for Data Engineering: Advanced Queries, Optimization, and Data Modelling Best Practices

Structured Query Language (SQL) is Key in the world of Data Engineering. From managing large amounts of data to working on complex data processing pipelines, understanding SQL is essential for extracting, transforming, and analyzing data. For data engineers, SQL is not only a tool for querying databases but the foundation for building efficient data pipelines, optimizing queries, and ensuring scalability. In this article, we'll explore advanced SQL techniques, query optimization strategies, and best practices in data modelling that every data engineer should know.

Core SQL Concepts for Data Engineering

It’s important to have a solid understanding on the core SQL concepts that form the foundation of data engineering.
SELECT, WHERE, JOIN, GROUP BY, and HAVING
These are the building blocks of SQL and will be used in almost every query. Here's a brief overview of how they work:
• SELECT: An SQL statement Used to specify the columns you want to retrieve from a table in a database.
• WHERE: Filters data based on a given condition.
• JOIN: Combines data from multiple tables based on a related column.
• GROUP BY: Aggregates rows that have the same values in specified columns.
• HAVING: Filters aggregated results, similar to the WHERE clause but for grouped data.

Real-World Use Case: Data Pipelines and ETL Processes

In a typical ETL (Extract, Transform, Load) pipeline, these concepts are frequently used to pull data from different sources, filter unnecessary data, and transform it into a structured format.

Lets use an example to demonstrate this concept. Imagine you have 3 tables: employee_data, salary_data and payroll_data. The task is to extract the data from both employee_data and salary_data, clean it, and then load it into a structured table (payroll_data) that can be used for payroll reports.

-- Extract employee details from raw_employees
SELECT employee_id, employee_name, department
FROM employee_data;

-- Extract salary details from raw_salaries
SELECT employee_id, salary
FROM salary_data;

-- Transform data: Join employee and salary tables, and calculate salary after tax
SELECT 
    e.employee_id, 
    e.employee_name, 
    e.department, 
    s.salary, 
    (s.salary * 0.9) AS salary_after_tax
FROM 
    employee_data e
JOIN 
    salary_data s ON e.employee_id = s.employee_id;

-- Load transformed data into a new table (payroll_data)
CREATE TABLE payroll_data AS
SELECT 
    e.employee_id, 
    e.employee_name, 
    e.department, 
    s.salary, 
    (s.salary * 0.9) AS salary_after_tax
FROM 
    employee_data e
JOIN 
    salary_data s ON e.employee_id = s.employee_id;

Advanced SQL Techniques

In this section we are going to dive into more complex queries that can improve your efficiency and problem-solving skills in data engineering.

• Recursive Queries and Common Table Expressions (CTEs) Recursive queries are useful when dealing with hierarchical data (e.g., organizational structures).

Common Table Expressions (CTEs) is a temporary result set that is defined within the execution of a query. After the execution the result no longer exists. They make the queries more readable and easier to manage by allowing you to define for example a subquery and reference it multiple times within the main query. CTEs are defined using the WITH keyword, followed by the CTE name and the query that generates the result set.

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT column1, column2
FROM cte_name;

Window Functions for Running Totals, Ranking, and Partitioning

Window functions are powerful tools for performing calculations across a set of rows that are related to the current row, without collapsing the result set unlike aggregate functions.
Examples:

• ROW_NUMBER() for ranking.

• SUM() OVER() for running totals.

• RANK() for ranking data with ties.
  

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (PARTITION BY department_id ORDER BY salary DESC) AS running_total
FROM employees;
This query computes a running total of salaries per department, ordered by salary in descending order.

Complex JOINs and Subqueries for Efficient Data Retrieval

Complex joins like LEFT JOIN, RIGHT JOIN, and INNER JOIN are used to combine data across different tables in flexible ways.

SELECT e.employee_id, e.name, e.salary, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > (
    SELECT AVG(salary) FROM employees WHERE department_id = e.department_id
);
This query returns employees who earn more than the average salary in their respective departments.

Query Optimization and Performance Tuning

As your database grows, query optimization becomes a critical skill. Without proper tuning, even the simplest queries can become slow and inefficient. We are going to look at indexing and stored procedure

• Indexing Indexes are essential for speeding up queries, especially when dealing with large datasets. By creating an index on columns that are frequently queried, you can reduce the time needed to retrieve data.

CREATE INDEX idx_order_date ON orders(order_date);
This index speeds up queries that filter by order_date.

• Stored Procedure Stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. Stored procedures are particularly useful for repetitive tasks, improving both performance and maintainability.

--- An example of stored procedure that selects records from a table
CREATE PROCEDURE GetOrdersByDate 
AS
BEGIN
    SELECT order_id, customer_id, order_date
    FROM orders
    WHERE order_date = @order_date;
END;
--- Execute the stored procedure
EXEC GetOrdersByDate;

Data Modeling Best Practices

Effective data modeling ensures that your database schema is optimized for both storage and query performance.

1. Normalization vs. Denormalization
   • Normalization reduces data redundancy and ensures data integrity by breaking down data into smaller tables.
   • Denormalization involves merging tables to reduce joins, which can speed up read-heavy applications, at the cost of additional storage and potential update anomalies.

2. Designing Efficient Relational Schemas
   When designing a schema, focus on the following:
   • Minimize redundancy and maintain consistency.
   • Ensure foreign key relationships between tables to enforce integrity.

3. Star Schema vs. Snowflake Schema
   • Star Schema: A simplified schema with a central fact table surrounded by dimension tables. Ideal for analytical queries.
   • Snowflake Schema: A more complex schema where dimension tables are normalized. While it saves storage, it may lead to slower queries due to the need for more joins.
   

Example of a star schema:
CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    sales_amount DECIMAL(10, 2),
    sale_date DATE
);

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(100)
);

CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255)
);
In this example, the fact_sales table holds the sales data, while dim_product and dim_customer contain information on products and customers, respectively.

Real-World Application & Case Study

Consider the following query, which joins multiple tables but is slow due to lack of indexing

SELECT orders.order_id, customers.customer_name, SUM(order_items.quantity * order_items.price) AS total
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
GROUP BY orders.order_id, customers.customer_name;

--- By adding indexes to the foreign key columns (customer_id, order_id), you can significantly improve performance.

Case Study: Transforming Raw Data into Structured Reports
Imagine you are tasked with transforming raw transactional data into monthly sales reports. Using SQL, you can aggregate data, join it with dimension tables for more context, and present it in a clean, readable format.

SELECT 
    MONTH(order_date) AS month,
    SUM(order_items.quantity * order_items.price) AS total_sales
FROM orders
JOIN order_items ON orders.order_id = order_items.order_id
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY MONTH(order_date)
ORDER BY month;

Conclusion

Mastering SQL is a vital skill for any data engineer. By understanding and applying advanced SQL techniques, optimizing queries, and designing efficient data models, you can significantly enhance your ability to work with large datasets and build scalable data pipelines. The key takeaways are:

1. Understand core SQL concepts, including joins, filtering, and grouping.
2. Use advanced techniques like recursive queries, window functions, and CTEs to solve complex problems.
3. Focus on query optimization by analyzing execution plans and leveraging indexing.
4. Follow data modelling best practices to design scalable and efficient schemas. By applying these strategies to real-world projects, you'll be well on your way to mastering SQL and becoming a more effective data engineer.