ADE SQL for Data Engineers

SQL is the daily language of a data engineer. Azure SQL Database, Synapse, Databricks SQL, and Fabric warehouses all speak it. A solid grip on a few core commands lets you clean data, join sources, and build reports on any of these platforms.

Sample Data for This Topic

RoadRunner Cabs, a fictional taxi company, keeps two tables.

TableColumns
tripstrip_id, driver_id, city, fare, trip_date
driversdriver_id, driver_name, joined_date

Core Commands

SELECT and WHERE

SELECT chooses columns. WHERE keeps only rows that match a condition.

SELECT trip_id, city, fare
FROM trips
WHERE fare > 20 AND city = 'Pune';

GROUP BY and Aggregates

GROUP BY collapses rows into groups. COUNT, SUM, AVG, MIN, and MAX summarize each group.

SELECT city, COUNT(*) AS trip_count, SUM(fare) AS total_fare
FROM trips
GROUP BY city;

JOIN

JOIN connects rows from two tables through a shared key. An INNER JOIN keeps only matching rows. A LEFT JOIN keeps every row from the left table and fills missing matches with NULL.

 trips                         drivers
+----------+------------+     +-----------+-------------+
| trip_id  | driver_id  |     | driver_id | driver_name |
+----------+------------+     +-----------+-------------+
| 1        | D10        |-----| D10       | Asha        |
| 2        | D11        |-----| D11       | Ravi        |
| 3        | D99        |--X  (no match)
+----------+------------+

INNER JOIN returns trips 1 and 2.
LEFT JOIN returns trips 1, 2, and 3 (trip 3 shows NULL for the driver name).

SELECT t.trip_id, d.driver_name, t.fare
FROM trips AS t
LEFT JOIN drivers AS d ON t.driver_id = d.driver_id;

Common Table Expressions

A CTE names a temporary result so a long query reads like a short story. Each CTE handles one small step.

WITH city_totals AS (
    SELECT city, SUM(fare) AS total_fare
    FROM trips
    GROUP BY city
)
SELECT city, total_fare
FROM city_totals
WHERE total_fare > 100000;

Window Functions

A window function calculates across related rows without collapsing them. Imagine a classroom where every student keeps a seat, and the teacher still writes each student's rank beside the name.

SELECT driver_id, city, fare,
       ROW_NUMBER() OVER (PARTITION BY city ORDER BY fare DESC) AS fare_rank
FROM trips;

PARTITION BY splits the rows into groups. ORDER BY sorts inside each group. ROW_NUMBER numbers the sorted rows from one.

Removing Duplicates

Source systems often send the same record twice. ROW_NUMBER solves the problem by keeping only the newest copy of each key.

WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY trip_id ORDER BY loaded_at DESC) AS rn
    FROM staging_trips
)
SELECT * FROM ranked WHERE rn = 1;

MERGE for Upserts

An upsert updates rows that exist and inserts rows that do not. Azure SQL Database handles this in one statement.

MERGE trips AS target
USING staging_trips AS source
    ON target.trip_id = source.trip_id
WHEN MATCHED THEN
    UPDATE SET target.fare = source.fare
WHEN NOT MATCHED THEN
    INSERT (trip_id, driver_id, city, fare, trip_date)
    VALUES (source.trip_id, source.driver_id, source.city, source.fare, source.trip_date);

Filtering Groups with HAVING

WHERE filters rows before grouping. HAVING filters groups after the totals exist. A manager who wants only the cities with more than 5,000 trips writes the condition in HAVING, because the trip count appears only after GROUP BY finishes.

SELECT city, COUNT(*) AS trip_count
FROM trips
GROUP BY city
HAVING COUNT(*) > 5000;

Order in Which SQL Thinks

SQL reads a query in a different order than you write it. The engine starts with FROM and JOIN, applies WHERE, builds groups with GROUP BY, applies HAVING, picks columns with SELECT, and sorts with ORDER BY last. This order explains why a column alias created in SELECT does not work inside WHERE on most platforms.

Choosing the Right Join

Join TypeRows ReturnedTypical Data Engineering Use
INNER JOINOnly rows with a match on both sidesAttach driver names to valid trips
LEFT JOINAll left rows, matches from the rightFind trips that have no driver record
FULL OUTER JOINAll rows from both sidesCompare a source table with a target table
CROSS JOINEvery possible pairingBuild a calendar of cities and dates

A LEFT JOIN with a filter for NULL on the right side finds orphan records quickly. Data engineers use this trick in quality checks to catch trips whose driver never reached the drivers table.

Habits That Save Time

  • Select only the columns you need instead of using SELECT *.
  • Filter early so the engine reads fewer rows.
  • Check join keys for duplicates, since duplicates multiply rows.
  • Name every CTE after the job it performs.
  • Test queries on a small sample before running them on the full table.

Key Points

  • SELECT, WHERE, GROUP BY, and JOIN cover most daily work.
  • CTEs break complex logic into readable steps.
  • Window functions rank and compare rows without losing detail.
  • ROW_NUMBER removes duplicates, and MERGE keeps tables current.
Back to course