Databases

Top SQL Concepts Every Data Engineer Should Know

A practical guide to the SQL skills behind production data pipelines, from joins and window functions to MERGE, SCD2, and data quality checks.

·
14 min read
·
AI-assisted

AI tooling disclosure

Generative AI materially contributed to this article.

Human review confirmed by the author.

Top SQL Concepts Every Data Engineer Should Know

Tools come and go. Hadoop gave way to Spark, orchestrators multiplied, and every year brings a new “modern data stack.” Through all of it, SQL has stayed the one skill that nearly every data engineer uses daily. Whether you’re modeling a warehouse, debugging a broken pipeline, or tuning a query that takes forty minutes, your SQL fundamentals decide how fast and how safely you can move.

This guide covers the concepts that matter most in day-to-day data engineering, each with a diagram and a short example. The examples use standard SQL and adapt easily to Postgres, Snowflake, BigQuery, Redshift, or Spark SQL. Syntax differs slightly between engines, but the ideas travel.

Here is where SQL lives in a typical data platform. Almost every box below is built, tested, or debugged with SQL:

flowchart LR
    A["Source systems<br/>apps, APIs, events"] --> B["Raw layer<br/>data as received"]
    B --> C["Staging<br/>clean, cast, dedupe"]
    C --> D["Core models<br/>facts and dimensions"]
    D --> E["Marts<br/>business-ready tables"]
    E --> F["Consumers<br/>BI, ML, reverse ETL"]

1. Joins and the Fan-Out Problem

Most people learn INNER, LEFT, RIGHT, and FULL OUTER joins early on. What separates experienced engineers is thinking about join cardinality: how many rows on one side match each row on the other.

Start by choosing the join that matches the question you are asking:

flowchart TD
    Q{"What do you need?"}
    Q -->|"Only rows that match on both sides"| I["INNER JOIN"]
    Q -->|"Every left row, matches optional"| L["LEFT JOIN"]
    Q -->|"Every row from both sides"| F["FULL OUTER JOIN"]
    Q -->|"Left rows with NO match"| A["Anti-join<br/>NOT EXISTS"]
    Q -->|"Filter by existence, no duplicates"| S["Semi-join<br/>EXISTS"]

The classic bug is the fan-out. Suppose you want revenue per customer:

-- Wrong: order_total is repeated once for every item in the order
SELECT o.customer_id, SUM(o.order_total) AS revenue
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
GROUP BY o.customer_id;

An order with three items gets counted three times:

flowchart LR
    O["orders<br/>1 row: order 1001, total 100"] --> J["After the join<br/>3 rows, each carrying total 100"]
    I["order_items<br/>3 rows for order 1001"] --> J
    J -->|"SUM of order_total"| R["Revenue = 300<br/>Expected = 100"]

The fix is to aggregate at the right grain before joining, so the many-side is reduced to one row per order:

-- Right: collapse items to one row per order, then join
SELECT
  o.customer_id,
  SUM(o.order_total) AS revenue,
  SUM(i.item_count)  AS items_sold
FROM orders o
JOIN (
  SELECT order_id, COUNT(*) AS item_count
  FROM order_items
  GROUP BY order_id
) i ON i.order_id = o.order_id
GROUP BY o.customer_id;

And if you don’t need any item columns at all, skip the join entirely.

The two existence patterns look like this:

-- Anti-join: customers who have never ordered
SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

-- Semi-join: customers with at least one order, with no duplicated rows
SELECT c.customer_id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

A good habit: before trusting any join, check that the key you’re joining on is unique on the side you expect it to be.


2. Window Functions

Window functions compute values across a set of related rows without collapsing them the way GROUP BY does. They are arguably the single most valuable intermediate SQL skill for data engineers.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,
  LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS previous_order_date
FROM orders;

Every window function follows the same three-step mental model:

flowchart TD
    A["All rows from the query"] --> B["PARTITION BY<br/>split rows into groups"]
    B --> C["ORDER BY<br/>sort rows inside each group"]
    C --> D["Frame clause<br/>pick which rows count for each row"]
    D --> E["Function runs once per row<br/>using its frame of rows"]

The functions you’ll reach for most often:

  • ROW_NUMBER, RANK, DENSE_RANK for ranking and picking the top N per group

  • LAG and LEAD for comparing a row to the one before or after it (churn, session gaps, period-over-period change)

  • SUM, AVG, COUNT with a frame for running totals and moving averages

  • FIRST_VALUE and LAST_VALUE for grabbing boundary values within a group

The three ranking functions differ only in how they treat ties. Given scores of 100, 90, 90, and 80:

score

ROW_NUMBER

RANK

DENSE_RANK

100

1

1

1

90

2

2

2

90

3

2

2

80

4

4

3

One gotcha: if you add ORDER BY to an aggregate window but omit the frame clause, most engines default to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the sort column are treated as one peer group and get the same running total. For a true row-by-row running total, spell out ROWS BETWEEN, as the example above does.


3. Deduplication

Duplicates are a fact of life. Source systems retry, event streams deliver at-least-once, and backfills overlap. Sooner or later you will write a dedup query, and the standard pattern is ROW_NUMBER:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM raw.customers
)
SELECT *
FROM ranked
WHERE rn = 1;
flowchart LR
    A["raw.customers<br/>contains duplicates"] --> B["ROW_NUMBER<br/>partition by customer_id<br/>order by updated_at DESC"]
    B --> C{"rn = 1 ?"}
    C -->|Yes| D["Keep the latest row"]
    C -->|No| E["Discard older rows"]

This keeps the latest record per customer. Snowflake, BigQuery, and Databricks offer a QUALIFY clause that lets you filter on the window function directly and skip the extra CTE:

SELECT *
FROM raw.customers
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY customer_id
  ORDER BY updated_at DESC, ingested_at DESC  -- ingested_at breaks ties
) = 1;

The important design decision is the tiebreaker. If two rows share the same updated_at, the result is nondeterministic unless you add a secondary sort column. Non-deterministic dedup is a frequent cause of “the numbers changed between runs” bugs.


4. CTEs and Query Structure

Common Table Expressions (CTEs) let you break a complex query into named, readable steps:

WITH daily_orders AS (
  SELECT customer_id, CAST(created_at AS DATE) AS order_date, SUM(amount) AS daily_total
  FROM orders
  GROUP BY 1, 2
),
active_customers AS (
  SELECT customer_id
  FROM daily_orders
  GROUP BY customer_id
  HAVING COUNT(*) >= 3
)
SELECT d.*
FROM daily_orders d
JOIN active_customers a USING (customer_id);

Think of a chain of CTEs as a small pipeline inside a single query:

flowchart LR
    A["orders"] --> B["daily_orders<br/>total per customer per day"]
    B --> C["active_customers<br/>3 or more active days"]
    B --> D["Final SELECT"]
    C --> D

CTEs are mostly about readability and maintainability, which matter a great deal when someone else (or future you) has to debug the query at 2 a.m. Two things to keep in mind:

  • Depending on the engine, a CTE may be inlined or materialized. In some databases, referencing the same CTE twice recomputes it; in others it’s computed once. Check your engine’s behavior when performance matters.

  • Recursive CTEs handle hierarchical data such as org charts, category trees, and bill-of-materials structures, which are common in warehouses.


5. Aggregation, GROUP BY, and Conditional Aggregation

Beyond COUNT and SUM, strong SQL means knowing how to compute several metrics in a single pass using conditional aggregation:

SELECT
  order_date,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_orders,
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue
FROM orders
GROUP BY order_date;

The difference between WHERE and HAVING comes down to the logical order of execution, which is different from the order you write the clauses in:

flowchart LR
    A["FROM and JOIN"] --> B["WHERE<br/>filters rows"]
    B --> C["GROUP BY"]
    C --> D["HAVING<br/>filters groups"]
    D --> E["SELECT and<br/>window functions"]
    E --> F["DISTINCT"]
    F --> G["ORDER BY"]
    G --> H["LIMIT"]

Because WHERE runs before grouping, it can’t see aggregates, so a condition like COUNT(*) >= 3 belongs in HAVING. Going the other way, filtering raw rows in WHERE is cheaper than doing it in HAVING, because it shrinks the data before aggregation. The same order explains why you can’t reference a window function in WHERE: it hasn’t been computed yet.

Also know when to reach for GROUPING SETS, ROLLUP, and CUBE to produce subtotals and multiple aggregation levels in one query, and COUNT(DISTINCT ...) versus approximate alternatives (such as HyperLogLog-based functions) when you’re counting at massive scale.


6. NULL Handling

NULL means “unknown,” not “zero” or “empty,” and it behaves in ways that surprise people:

  • NULL = NULL is not true. Use IS NULL and IS NOT NULL.

  • NOT IN returns no rows if the subquery contains even one NULL. Prefer NOT EXISTS.

  • Aggregates like SUM and AVG ignore NULLs, but COUNT(*) counts every row while COUNT(column) skips NULLs.

  • Joins never match on NULL keys, so rows with NULL join keys silently disappear from inner joins.

Here is why the NOT IN trap bites, step by step:

flowchart LR
    A["5 NOT IN (1, 2, NULL)"] --> B["5 != 1 AND 5 != 2 AND 5 != NULL"]
    B --> C["TRUE AND TRUE AND UNKNOWN"]
    C --> D["Result is UNKNOWN"]
    D --> E["Row is filtered out"]

Use COALESCE to supply defaults and NULLIF to avoid divide-by-zero errors:

SELECT revenue / NULLIF(orders, 0) AS avg_order_value
FROM daily_metrics;

Many subtle data quality bugs trace back to NULL semantics, so it pays to be deliberate.


7. Indexes and Execution Plans

Writing correct SQL is step one. Writing SQL that runs efficiently is what makes you valuable at scale. Learn to read an execution plan with EXPLAIN (or EXPLAIN ANALYZE). A plan is a tree, and you read it from the leaves upward:

flowchart TD
    A["Aggregate<br/>SUM amount by customer"] --> B["Hash Join<br/>orders and customers"]
    B --> C["Seq Scan on orders<br/>suspicious if the query<br/>had a selective filter"]
    B --> D["Index Scan on customers<br/>uses customer_id index"]

When reading a plan, look for:

  • Full table scans where an index or partition filter should apply

  • Join strategy: nested loop, hash join, or merge join, and whether the planner chose well

  • Row estimate mismatches, where the optimizer expected 100 rows and got 10 million, which usually signals stale statistics

  • Expensive sorts and shuffles, especially in distributed engines

In row-oriented databases, indexes (B-tree, composite, covering) are the main speed tool. Note that an index on (customer_id, order_date) helps queries filtering on customer_id, or on both columns, but usually can’t help a query that filters on order_date alone (some newer engines add skip-scan optimizations, so always confirm with EXPLAIN). Also avoid wrapping indexed columns in functions inside WHERE clauses, because WHERE DATE(created_at) = '2026-01-01' often prevents the index from being used. A range filter such as created_at >= '2026-01-01' AND created_at < '2026-01-02' is usually index-friendly.

Columnar warehouses like Snowflake, BigQuery, and Redshift work differently. They rely less on traditional indexes and more on the next concept.


8. Partitioning, Clustering, and Pruning

In large-scale analytical systems, the fastest data to process is the data you never read. Partitioning splits a table into segments (usually by date), and clustering or sort keys organize data within those segments so the engine can skip irrelevant blocks.

-- BigQuery syntax
CREATE TABLE events (
  event_id BIGINT,
  user_id BIGINT,
  event_type STRING,
  event_date DATE
)
PARTITION BY event_date
CLUSTER BY user_id;

Here is pruning in action for a query that asks about a single day:

flowchart LR
    Q["Query<br/>WHERE event_date = 2026-01-02"] --> P{"Partition pruning"}
    P -->|skip| A["Partition 2026-01-01"]
    P -->|read| B["Partition 2026-01-02"]
    P -->|skip| C["Partition 2026-01-03"]

    classDef skipped fill:#eeeeee,stroke:#bbbbbb,color:#888888
    class A,C skipped

The payoff only arrives if your queries filter on the partition column, so always include a date filter on big fact tables. Avoid partitioning on columns with extremely high cardinality, which creates thousands of tiny partitions and hurts more than it helps. Every platform spells this differently (Snowflake manages micro-partitions automatically and lets you define a clustering key, Redshift uses distribution and sort keys, and PostgreSQL uses declarative PARTITION BY RANGE), but the idea is the same everywhere.


9. Incremental Loads, MERGE, and Idempotency

Reprocessing an entire table every night works until the table gets big. Production pipelines load data incrementally, and that introduces a critical requirement: idempotency. Running the same job twice must produce the same result as running it once, because retries and reruns are inevitable.

The workhorse for this is MERGE (also called upsert):

MERGE INTO dim_customer AS target
USING staging_customer AS source
  ON target.customer_id = source.customer_id
WHEN MATCHED AND source.updated_at > target.updated_at THEN
  UPDATE SET
    name = source.name,
    email = source.email,
    updated_at = source.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, updated_at)
  VALUES (source.customer_id, source.name, source.email, source.updated_at);

Here is the decision logic MERGE applies to every incoming row:

flowchart TD
    S["Incoming row from staging<br/>already deduplicated"] --> M{"Key exists in target?"}
    M -->|No| I["INSERT new row"]
    M -->|Yes| U{"source.updated_at is newer?"}
    U -->|Yes| UP["UPDATE existing row"]
    U -->|No| SK["Skip: target already current"]
    I --> T[("dim_customer")]
    UP --> T
    SK --> T

Re-running the same batch is harmless: the second pass finds nothing newer to apply.

Two practical notes. MERGE is available in Snowflake, BigQuery, Databricks, SQL Server, Oracle, and PostgreSQL 15 and later; older PostgreSQL versions use INSERT ... ON CONFLICT DO UPDATE for the same purpose. And a basic MERGE never notices rows that disappear from the source, so decide up front how deletes are handled: a soft-delete flag in the source, or a WHEN NOT MATCHED BY SOURCE clause where your engine supports it.

Other idempotent patterns include delete-then-insert for a given partition and overwriting a single partition atomically. Whichever you choose, deduplicate staging first (see section 3), because a MERGE with duplicate source keys can fail or produce unpredictable results.


10. Slowly Changing Dimensions (SCD Type 2)

Business attributes change: a customer moves, an account upgrades its plan, a product changes category. If you overwrite the old value, you lose history, and reports run against past periods will change retroactively. SCD Type 2 solves this by keeping every version of a record with validity dates:

customer_id

plan

valid_from

valid_to

is_current

101

Free

2025-01-01

2025-08-15

false

101

Pro

2025-08-15

9999-12-31

true

Notice that the old row’s valid_to equals the new row’s valid_from. Treat valid_to as exclusive, a half-open interval, so the versions tile the timeline with no gaps and no overlaps. Mixing inclusive and exclusive end dates is a well-known SCD2 bug, and it silently drops or double-counts rows on boundary days.

When a change arrives, two things happen together:

flowchart TD
    A["Change detected<br/>customer 101 moves from Free to Pro"] --> B["Close the current row<br/>valid_to = change date<br/>is_current = false"]
    B --> C["Insert a new row<br/>valid_from = change date<br/>valid_to = 9999-12-31<br/>is_current = true"]
    C --> D["Full history preserved"]

To analyze data as of a point in time, join the fact table to the dimension on the key and a date range:

SELECT f.order_id, d.plan
FROM fact_orders f
JOIN dim_customer_scd2 d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.valid_from
 AND f.order_date < d.valid_to;

In practice, run both steps in a single transaction (see section 12), so a failure between them can never leave a customer with no current row or two current rows.


11. Data Modeling: Normalization, Star Schemas, and Grain

SQL skill isn’t only about queries; it’s also about the shape of the tables you query.

  • Normalization (3NF) reduces redundancy and protects integrity. It’s the standard for transactional systems.

  • Dimensional modeling (star and snowflake schemas) organizes analytical data into fact tables (events and measurements) surrounded by dimension tables (descriptive context). It trades some redundancy for simpler, faster analytics.

A star schema looks like this, with one central fact table and dimensions radiating out:

erDiagram
    FACT_ORDERS }o--|| DIM_CUSTOMER : "placed by"
    FACT_ORDERS }o--|| DIM_PRODUCT : "contains"
    FACT_ORDERS }o--|| DIM_DATE : "ordered on"

    FACT_ORDERS {
        bigint order_line_id PK
        bigint customer_key FK
        bigint product_key FK
        int date_key FK
        int quantity
        decimal amount
    }
    DIM_CUSTOMER {
        bigint customer_key PK
        string name
        string region
    }
    DIM_PRODUCT {
        bigint product_key PK
        string product_name
        string category
    }
    DIM_DATE {
        int date_key PK
        date full_date
        int month
        int year
    }

The most important modeling concept is grain: a precise statement of what one row in a table represents. “One row per order line item” is a grain. Decide it first, write it down, and make sure every column respects it. Most fan-out bugs and double-counting errors are really grain errors in disguise.


12. Transactions, ACID, and Isolation Levels

A transaction groups statements so they succeed or fail together. The ACID properties (Atomicity, Consistency, Isolation, Durability) are what make relational databases trustworthy.

stateDiagram-v2
    [*] --> Active: BEGIN
    Active --> Active: INSERT, UPDATE, DELETE
    Active --> Committed: COMMIT
    Active --> RolledBack: ROLLBACK or error
    Committed --> [*]
    RolledBack --> [*]

For data engineers, the practical takeaways are:

  • Wrap multi-step loads in a transaction where your platform supports it, so a half-finished load never becomes visible to downstream consumers.

  • Understand isolation levels (read committed, repeatable read, serializable) and the anomalies they prevent, such as dirty reads and phantom reads, especially when pipelines read from live operational databases.

  • Know that many cloud warehouses and lakehouse table formats (Delta Lake, Iceberg, Hudi) provide transactional guarantees on top of object storage, which is what makes safe concurrent writes and reliable MERGE operations possible.


13. Dates, Time Zones, and Semi-Structured Data

Two things cause a disproportionate share of pipeline bugs: time and nested data.

Time zones. A “day” depends on whose clock you use. An order placed at 11:30 p.m. in New York on January 1 is already January 2 in UTC, so a daily revenue report can move orders between days depending on the zone. The safe convention is to store timestamps in UTC and convert to the business time zone only at the reporting edge:

flowchart LR
    A["Source timestamps<br/>mixed time zones"] --> B["Normalize to UTC<br/>at ingestion"]
    B --> C["Store as a timestamp<br/>in the warehouse"]
    C --> D["Convert to local time<br/>only when reporting"]
-- PostgreSQL: created_at is a timestamptz stored in UTC
SELECT
  DATE_TRUNC('day', created_at AT TIME ZONE 'Asia/Kolkata') AS order_day_ist,
  COUNT(*) AS orders
FROM orders
GROUP BY 1;

Also distinguish event time (when something happened) from load time (when it reached your warehouse). An incremental job that filters only on load time can mislabel late-arriving records, and one that filters only on event time can miss them entirely, so many teams reprocess a small trailing window on every run.

Semi-structured data. Modern sources deliver JSON constantly, and warehouses have native types for it (JSONB in PostgreSQL, VARIANT in Snowflake, JSON, STRUCT, and ARRAY in BigQuery). Extract the fields you need into typed columns in your staging layer so downstream models don’t repeat the parsing:

-- PostgreSQL JSONB: pull fields out and flatten an array
SELECT
  e.event_id,
  e.payload ->> 'user_id' AS user_id,
  item ->> 'sku'          AS sku
FROM raw.events e,
     jsonb_array_elements(e.payload -> 'items') AS item;

Flattening an array produces one row per element, which is another route to the fan-out problem from section 1. Re-check your grain every time you unnest.


14. Data Quality Checks in SQL

A pipeline that runs successfully but produces wrong data is worse than one that fails loudly. Get comfortable writing validation queries and wiring them into your workflow, whether through dbt tests, Great Expectations, or plain SQL assertions:

-- Duplicate primary keys
SELECT order_id, COUNT(*)
FROM fact_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Orphaned foreign keys
SELECT f.order_id
FROM fact_orders f
LEFT JOIN dim_customer c ON c.customer_id = f.customer_id
WHERE c.customer_id IS NULL;

-- Freshness
SELECT MAX(loaded_at) AS last_load FROM fact_orders;

Treat checks as a gate between your transformations and your consumers:

flowchart LR
    A["Raw data"] --> B["Transform with SQL"]
    B --> C{"Quality checks pass?"}
    C -->|Pass| D["Publish to marts"]
    C -->|Fail| E["Stop pipeline<br/>and alert the team"]

Check uniqueness, nullability, referential integrity, accepted values, row-count reconciliation against the source, and freshness. These checks are cheap to write and save hours of debugging.


Putting It All Together

If you’re looking for a learning path, here’s a sensible order:

flowchart TD
    A["1. Core querying<br/>joins, aggregations, NULLs"] --> B["2. Expressive SQL<br/>window functions, CTEs"]
    B --> C["3. Production patterns<br/>dedup, incremental loads, MERGE,<br/>time zones and JSON"]
    C --> D["4. Performance<br/>execution plans, partitioning, clustering"]
    D --> E["5. Trust and structure<br/>data modeling, SCD2, data quality"]

Each stage builds on the last: window functions are easier once joins feel natural, and incremental loading only makes sense once you can deduplicate.

Quick Reference

Concept

Remember this

Classic pitfall

Joins

Know the grain on both sides

Fan-out inflates sums

Window functions

Partition, order, then frame

Default RANGE frame with tied values

Deduplication

ROW_NUMBER with a tiebreaker

Non-deterministic results between runs

CTEs

Name each step of the pipeline

Recomputation in some engines

Aggregation

WHERE filters rows, HAVING filters groups

Filtering in the wrong clause

NULLs

NULL means unknown

NOT IN with a NULL in the list

Execution plans

Read from the leaves upward

Stale statistics, unexpected full scans

Partitioning

Filter on the partition column

Too many tiny partitions

MERGE

Deduplicate the source first

Duplicate source keys, unhandled deletes

SCD2

Use half-open date ranges

Gaps or overlaps in history

Data modeling

Declare the grain up front

Mixed grains in one table

Transactions

All or nothing

Half-loaded tables visible downstream

Dates and JSON

Store UTC, flatten deliberately

Shifting day boundaries, unnest multiplying rows

Data quality

Gate before publishing

A green pipeline with wrong data

The best way to internalize all of this is to build something real: pull a public dataset into a warehouse, model it into a star schema, load it incrementally, and add tests. Every concept above will come up, and each one will stick far better than it would from reading alone.

SQL may be decades old, but depth in SQL is still one of the clearest signals of a strong data engineer. Invest in the fundamentals, and every new tool you pick up will make more sense.

Comments(0)

Join the discussion by logging into your account.

No comments yet. Be the first to comment!

Shobit Singh

Passionate developer sharing knowledge about modern web technologies and best practices.

Subscribe to Shobit Singh's Newsletter

Direct email dispatches when new stories are published. Zero algorithms.