The SQL Factor: Why It Still Matters in Data Engineering#

Milestones in SQL’s Development#

• 1970s: Edgar F. Codd formalizes the relational model.
• 1979: Oracle (then Relational Software Inc.) releases the first commercial SQL-based RDBMS.
• 1986: SQL becomes an ANSI (American National Standards Institute) standard.
• 1992: SQL-92 standard further refines query capabilities and syntax.
• 1999 and beyond: Continued evolution with additional features like window functions, recursive queries, and extended data types.

SQL’s longevity is partly due to its adaptability—although the underlying principles remain consistent, the language itself evolves to support new data types, indexing strategies, and performance optimizations.

Installing a Database#

Common relational database management systems include:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle Database

Whether you install these databases locally or access them via a cloud service (e.g., Amazon RDS, Azure SQL Database, or Google Cloud SQL), the basic principles of SQL remain the same. Let’s take PostgreSQL as an example for setting up a local environment:

1. Download and install PostgreSQL from the official website.
2. During installation, set a secure password for the default superuser, often named “postgres.”
3. Use a GUI tool like pgAdmin or the command-line interface psql to start practicing queries.

3.1 Creating a Database and a Table#

After installation, you can create a database and then a table to store your data.

1
-- Create a new database
2
CREATE DATABASE company_db;
3

4
-- Switch to the new database (in psql or a similar tool)
5
\c company_db;
6

7
-- Create a table named employees
8
CREATE TABLE employees (
9
    employee_id SERIAL PRIMARY KEY,
10
    first_name VARCHAR(50),
11
    last_name VARCHAR(50),
12
    department VARCHAR(50),
13
    salary DECIMAL(10, 2)
14
);

The SERIAL type is a PostgreSQL-specific method of creating an auto-incrementing ID. Other databases may use AUTO_INCREMENT (MySQL) or IDENTITY (SQL Server).

3.2 Inserting Data#

To populate the table with records:

1
INSERT INTO employees (first_name, last_name, department, salary)
2
VALUES
3
    ('Alice', 'Johnson', 'Finance', 50000.00),
4
    ('Bob', 'Smith', 'Engineering', 70000.00),
5
    ('Carol', 'Williams', 'Engineering', 72000.00),
6
    ('David', 'Brown', 'HR', 45000.00);

3.3 Selecting Data#

The SELECT statement is the heart of SQL for data retrieval:

1
SELECT *
2
FROM employees;

This will return all columns and rows from the employees table.

1
SELECT employee_id, first_name, last_name
2
FROM employees
3
WHERE department = 'Engineering';

1
SELECT employee_id, first_name, last_name, salary
2
FROM employees
3
ORDER BY salary DESC;  -- Highest salary first

1
SELECT *
2
FROM employees
3
LIMIT 2;

1
SELECT *
2
FROM employees
3
ORDER BY employee_id
4
LIMIT 2
5
OFFSET 2;

3.4 Updating and Deleting Data#

You can update existing data:

1
UPDATE employees
2
SET salary = 75000.00
3
WHERE first_name = 'Bob' AND last_name = 'Smith';

And remove specific rows:

1
DELETE FROM employees
2
WHERE employee_id = 4;

Practicing With the Basics#

At this stage, it’s beneficial to experiment with different clauses and small datasets. Understanding these fundamental operations—creating, inserting, selecting, updating, and deleting—is key before diving into more advanced aspects of SQL.

4.1 Joins#

Joins allow you to combine data from multiple tables, making relational databases so powerful.

1
CREATE TABLE projects (
2
    project_id SERIAL PRIMARY KEY,
3
    project_name VARCHAR(50),
4
    department VARCHAR(50)
5
);

1
INSERT INTO projects (project_name, department)
2
VALUES
3
    ('Project Alpha', 'Engineering'),
4
    ('Project Beta', 'Finance'),
5
    ('Project Gamma', 'Engineering');

1
SELECT e.first_name, e.last_name, p.project_name
2
FROM employees e
3
INNER JOIN projects p
4
    ON e.department = p.department
5
ORDER BY e.last_name;

4.2 Grouping and Aggregations#

To aggregate data, you can use functions such as COUNT(), SUM(), AVG(), MIN(), and MAX() alongside the GROUP BY clause.

1
SELECT department, AVG(salary) AS avg_salary
2
FROM employees
3
GROUP BY department;

You can further refine groups with a HAVING clause, which filters aggregated data.

1
SELECT department, COUNT(*) AS dept_count
2
FROM employees
3
GROUP BY department
4
HAVING COUNT(*) > 1;

4.3 Subqueries#

SQL supports nested queries (subqueries), which can be used in various parts of a statement.

1
SELECT first_name, last_name, salary
2
FROM employees
3
WHERE salary > (
4
    SELECT AVG(salary)
5
    FROM employees
6
);

4.4 Window Functions#

Window functions expand on the concept of aggregations, allowing calculations over a set of rows that are related to the current row.

For example, consider calculating a running total of salaries in each department:

1
SELECT
2
    employee_id,
3
    first_name,
4
    department,
5
    salary,
6
    SUM(salary) OVER (PARTITION BY department ORDER BY employee_id) AS running_total
7
FROM employees;

Breaking down the syntax:

• OVER (PARTITION BY department ORDER BY employee_id) defines the window.
• SUM(salary) calculates the running total across that window.

Window functions are vital for complex analytics, especially in scenarios where you need row-by-row comparisons, ranking, or advanced calculations without losing individual row context.

4.5 Common Table Expressions (CTEs)#

CTEs let you break down complex queries into manageable chunks using WITH <cte_name> AS (...). They can be recursive or non-recursive, providing a more readable alternative to deeply nested subqueries.

1
WITH engineering_emps AS (
2
    SELECT employee_id, first_name, salary
3
    FROM employees
4
    WHERE department = 'Engineering'
5
)
6
SELECT e.employee_id, e.first_name, p.project_name
7
FROM engineering_emps e
8
JOIN projects p ON p.department = 'Engineering';

5.1 Data Modeling#

Data engineering typically starts with designing schemas and data models. Normalization ensures each table has a single, well-defined responsibility.

• 1st Normal Form (1NF): No repeating groups of columns; atomic column values.
• 2nd Normal Form (2NF): Every non-key column depends on the entire primary key.
• 3rd Normal Form (3NF): No transitive dependencies—attributes depend only on the primary key.

Data engineers often weigh normalization (for efficiency in OLTP—Online Transaction Processing) versus denormalization (for analytics, OLAP—Online Analytical Processing).

5.2 Data Pipelines#

Data pipelines connect multiple stages: ingestion, transformation, validation, and loading. SQL can serve as the transformation layer in an ELT (Extract, Load, Transform) or ETL pipeline:

1. Extract: Retrieve raw data from sources (API, CSVs, NoSQL stores).
2. Load: Load data into a staging area.
3. Transform: Use SQL for transformations, aggregations, and cleansing.

Example of a transformation query in a staging environment might look like:

1
INSERT INTO employees_cleaned (employee_id, first_name, last_name, dept, salary)
2
SELECT
3
    e.employee_id,
4
    INITCAP(e.first_name) AS first_name,
5
    INITCAP(e.last_name) AS last_name,
6
    CASE
7
        WHEN e.department IN ('finance', 'FIN') THEN 'Finance'
8
        ELSE e.department
9
    END,
10
    e.salary
11
FROM employees_raw e;

5.3 Integrating with Other Tools#

• Apache Airflow: Orchestrates complex SQL tasks and dependencies.
• Apache Spark or PySpark: Supports SQL queries on distributed datasets.
• dbt (data build tool): Uses SQL to define transformations in a version-controlled, testable environment.

SQL remains a core skill in data engineering because it works harmoniously with these frameworks. It’s often easier to express transformations using declarative SQL than to write multiple lines of lower-level code.

6.1 Log Analysis in a Data Warehouse#

A global e-commerce company might store application logs in CSV files on cloud storage, then load them into a relational data warehouse (e.g., Amazon Redshift, Snowflake, or Google BigQuery). By writing SQL queries, data engineers can:

• Identify IP addresses causing suspicious traffic (JOIN with a table of known malicious IPs).
• Compute daily transaction volumes and compare them with historical averages.
• Aggregate error rates by application version or geographic region.

6.2 Customer 360 Data#

Organizations aim to get a unified view of their customers—often called a “Customer 360” view. Data may be spread across CRM systems, web analytics platforms, and ERP databases. Data engineers consolidate all this information into a single relational schema, where SQL queries link customer IDs across multiple tables:

1
SELECT c.customer_id,
2
       c.name,
3
       c.email,
4
       s.last_purchase_date,
5
       s.total_spend,
6
       t.num_tickets_open
7
FROM customer c
8
LEFT JOIN sales s ON c.customer_id = s.customer_id
9
LEFT JOIN tickets t ON c.customer_id = t.customer_id
10
ORDER BY c.customer_id;

This query helps generate a single pane of glass for marketing, customer service, and analytics teams.

6.3 Time-Series Data#

While time-series databases exist, many organizations still rely on relational systems for historical or near-real-time analytics. SQL queries can handle time-series data by leveraging:

• Timestamp columns for date-based filtering.
• Window functions for rolling averages or cumulative sums.
• Partitioning by time intervals for performance.

7.1 Indexing Strategies#

Indexes are data structures that speed up queries by providing quick lookups of values in specific columns. However, over-indexing can slow down writes and consume significant storage.

1
CREATE INDEX idx_employee_department
2
ON employees (department);

7.2 Query Optimization#

• Analyze Execution Plans: Use EXPLAIN or EXPLAIN ANALYZE to see how the database plans to execute your query.
• **Avoid Select ****: Selecting all columns frequently leads to suboptimal query performance. Specify only needed columns.
• Optimize Joins: Ensure columns used in JOIN conditions are indexed.

7.3 Partitioning and Sharding#

Partitioning breaks a large table into smaller sections (e.g., by date range), improving query times and manageability. Sharding distributes data across multiple database servers or nodes, which is essential for scaling horizontally.

7.4 Caching Strategies#

• Database-Level Caching: Some database engines offer in-memory caching.
• Application-Level Caching: Tools like Redis store frequently accessed data to reduce round trips to the database.

7.5 Data Warehouse Optimization#

In an OLAP environment, consider columnar storage (e.g., Redshift, Snowflake) and use metadata-based queries to minimize the data scanned.

8.1 Cloud-Based Data Warehouses#

With the advent of fully-managed database services, complex query processing can be offloaded to cloud providers:

• Amazon Redshift: Massively parallel processing.
• Google BigQuery: Serverless and scales automatically.
• Snowflake: Separates compute and storage for flexible scaling.

These platforms still rely heavily on SQL. They may feature extended SQL syntax to handle semi-structured data (like JSON).

8.2 Big Data and Distributed Queries#

Tools such as Apache Hive, Presto/Trino, and Spark SQL allow you to run SQL queries on distributed datasets stored in Hadoop or cloud object storage:

• Hive: Translates SQL into MapReduce jobs.
• Presto/Trino: Executes queries in memory, better for interactive analysis.
• Spark SQL: Integrates with the Spark ecosystem, providing transformations in SQL.

8.3 SQL on NoSQL#

Some NoSQL databases (e.g., Cassandra, Couchbase) have added SQL-like syntaxes, bridging the gap between structured and unstructured worlds. This hybrid approach maintains the scalability of NoSQL while providing the familiar analysis power of SQL.

8.4 Real-Time Data Streaming#

Technologies like Apache Kafka and Kafka Streams often integrate with SQL through connectors or via specialized engines such as ksqlDB, enabling continuous queries on streaming data in real-time.

9.1 Continuous Evolution of Standards#

SQL is not static. The SQL:2016 standard added support for JSON. Modern RDBMS continue to evolve, supporting geospatial queries, text search, machine learning extensions, and more.

9.2 Integration with Machine Learning#

Some databases now provide the ability to train and deploy machine learning models in-database, making it possible to do advanced analytics without exporting data to external systems. This reduces data movement and ensures more secure, consistent access to data.

9.3 Wider Tool Ecosystem#

SQL continues to be the bedrock in a broader data technology landscape that includes:

• Data catalogs for metadata management.
• Data lineage and governance tools.
• Data orchestration platforms.

SQL knowledge remains indispensable for effectively navigating these tools, optimizing data flows, and delivering trusted data products.