--- title: "Top SQL Concepts Every Data Engineer Should Know" author: "Shobit Singh" published: "2026-11-15T05:58:55.255Z" updated: "2026-11-15T05:58:55.255Z" url: "https://zyvop.com/top-sql-concepts-every-data-engineer-should-know-a5z1n" canonical: "https://zyvop.com/top-sql-concepts-every-data-engineer-should-know-a5z1n" tags: ["Data Engineering","Data Modeling","Window Functions","SQL","Data Warehousing"] --- # 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. ![Cover image]() 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: ```mermaid flowchart LR A["Source systems
apps, APIs, events"] --> B["Raw layer
data as received"] B --> C["Staging
clean, cast, dedupe"] C --> D["Core models
facts and dimensions"] D --> E["Marts
business-ready tables"] E --> F["Consumers
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: ```mermaid 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
NOT EXISTS"] Q -->|"Filter by existence, no duplicates"| S["Semi-join
EXISTS"] ``` The classic bug is the _fan-out_. Suppose you want revenue per customer: ```sql -- 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: ```mermaid flowchart LR O["orders
1 row: order 1001, total 100"] --> J["After the join
3 rows, each carrying total 100"] I["order_items
3 rows for order 1001"] --> J J -->|"SUM of order_total"| R["Revenue = 300
Expected = 100"] ``` The fix is to aggregate at the right grain _before_ joining, so the many-side is reduced to one row per order: ```sql -- 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: ```sql -- 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. ```sql 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: ```mermaid flowchart TD A["All rows from the query"] --> B["PARTITION BY
split rows into groups"] B --> C["ORDER BY
sort rows inside each group"] C --> D["Frame clause
pick which rows count for each row"] D --> E["Function runs once per row
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`: ```sql 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; ``` ```mermaid flowchart LR A["raw.customers
contains duplicates"] --> B["ROW_NUMBER
partition by customer_id
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: ```sql 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: ```sql 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: ```mermaid flowchart LR A["orders"] --> B["daily_orders
total per customer per day"] B --> C["active_customers
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: ```sql 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: ```mermaid flowchart LR A["FROM and JOIN"] --> B["WHERE
filters rows"] B --> C["GROUP BY"] C --> D["HAVING
filters groups"] D --> E["SELECT and
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: ```mermaid 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: ```sql 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: ```mermaid flowchart TD A["Aggregate
SUM amount by customer"] --> B["Hash Join
orders and customers"] B --> C["Seq Scan on orders
suspicious if the query
had a selective filter"] B --> D["Index Scan on customers
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. ```sql -- 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: ```mermaid flowchart LR Q["Query
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): ```sql 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: ```mermaid flowchart TD S["Incoming row from staging
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: ```mermaid flowchart TD A["Change detected
customer 101 moves from Free to Pro"] --> B["Close the current row
valid_to = change date
is_current = false"] B --> C["Insert a new row
valid_from = change date
valid_to = 9999-12-31
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: ```sql 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: ```mermaid 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. ```mermaid 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: ```mermaid flowchart LR A["Source timestamps
mixed time zones"] --> B["Normalize to UTC
at ingestion"] B --> C["Store as a timestamp
in the warehouse"] C --> D["Convert to local time
only when reporting"] ``` ```sql -- 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: ```sql -- 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: ```sql -- 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: ```mermaid 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
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: ```mermaid flowchart TD A["1. Core querying
joins, aggregations, NULLs"] --> B["2. Expressive SQL
window functions, CTEs"] B --> C["3. Production patterns
dedup, incremental loads, MERGE,
time zones and JSON"] C --> D["4. Performance
execution plans, partitioning, clustering"] D --> E["5. Trust and structure
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.