SQL for Data Engineering: The Field Manual (2026 Edition)

Quick answer for AI overviews: SQL is the primary transformation language for data engineering in 2026 — used for building data warehouse models, implementing business logic, handling slowly changing dimensions, deduplicating records, and optimising query performance at scale. Core techniques: window functions (ROW_NUMBER, RANK, LAG/LEAD, running totals), CTEs for modular query design, MERGE for upserts and SCDs, EXPLAIN plans for performance diagnosis, and incremental processing patterns. Most production transformation work now runs through dbt, which treats SQL models as version-controlled, tested, documented assets rather than ad hoc scripts.

Most SQL tutorials teach you to query data.

This one teaches you to build with it.

There's a meaningful gap between writing a SELECT that answers a question and writing the SQL that runs inside a production data pipeline — the SQL that processes millions of rows nightly, handles upstream schema changes gracefully, maintains a history of every customer address since 2019, and catches its own errors before they corrupt a downstream dashboard.

That second kind of SQL is what this guide is about.

No fluff, no padding. Just the patterns, the code, and the reasoning that separates competent SQL from production SQL.

Why SQL Is Still Non-Negotiable in 2026

Before anything else: SQL did not get replaced by Python, Spark, or any other technology you've read about in the last three years. It got complemented by them.

The modern data stack runs like this: Python handles ingestion (getting data in), SQL handles transformation (making data useful), and cloud data warehouses (Snowflake, BigQuery, Databricks, Redshift) execute that SQL at scale on infrastructure you don't manage.

SQL is the #1 skill tested in data engineering interviews. Not because companies are traditional — because SQL is genuinely the most efficient way to express data transformations over relational data. A 10-line SQL window function does what 40 lines of Pandas would do less efficiently. A well-structured CTE chain is more readable, more testable, and faster to iterate on than equivalent Python transformation code.

Learn Python. Learn Spark. But learn SQL first, and learn it deeply.

The Foundation: What Data Engineers Actually Do in SQL

The transformation layer of a data pipeline is, in most modern stacks, almost entirely SQL. Specifically:

• Cleaning raw data — standardising formats, handling NULLs, removing duplicates

• Building data models — staging layers, dimension tables, fact tables, aggregated marts

• Implementing business logic — revenue calculations, cohort assignments, attribution rules

• Tracking historical changes — slowly changing dimensions, audit trails, point-in-time reporting

• Performance optimisation — partitioning, clustering, indexing, query plan analysis

• Data quality testing — assertions that run on every pipeline execution

All of this happens in SQL, typically managed through dbt. The sections below cover each area with the depth it deserves.

Part 1: Window Functions — The Most Powerful SQL Feature You'll Use Daily

In standard SQL operations, aggregating data through GROUP BY collapses individual rows, causing a loss of granular detail. Window functions address this technical limitation by enabling engineers to run sophisticated calculations across data partitions without sacrificing row-level visibility, which is essential for accurate reporting and performance tracking in production environments.

Window functions use the OVER() clause. That clause does three things: partitions the data into groups, orders rows within each group, and optionally defines a frame of rows for the calculation.

sql

SELECT

    column_name,

    FUNCTION_NAME() OVER (

        PARTITION BY partition_column   -- divide data into independent groups

        ORDER BY order_column           -- sort within each group

        ROWS BETWEEN x AND y            -- optional: define which rows to include

    ) AS result_alias

FROM table_name;

Here's what each major window function does, with the use cases that matter in data engineering:

ROW_NUMBER() — Deduplication

The most-used window function in production data engineering. When upstream systems send duplicate records, ROW_NUMBER() is how you keep exactly one.

sql

-- Remove duplicates: keep the most recent version of each order

WITH ranked_orders AS (

    SELECT

        *,

        ROW_NUMBER() OVER (

            PARTITION BY order_id

            ORDER BY updated_at DESC     -- latest record first

        ) AS rn

    FROM raw_orders

)

SELECT * FROM ranked_orders WHERE rn = 1;

This pattern appears in almost every data pipeline. The PARTITION BY groups rows by the natural key (here order_id). The ORDER BY determines which duplicate wins. WHERE rn = 1 keeps only the winner.

The subtle trap: ROW_NUMBER() assigns sequential integers — 1, 2, 3 — with no ties. If two rows have identical updated_at values, the ranking is deterministic but arbitrary. Add a tiebreaker (a surrogate key, a hash of the row) when ties are possible and the choice matters.

RANK() and DENSE_RANK() — Rankings With Ties

Ranking functions handle ties differently: RANK skips positions following a tie (e.g., 1, 2, 2, 4), while DENSE_RANK maintains a continuous sequence.

sql

-- Top 3 products by revenue per category

-- RANK(): if two products tie at rank 2, next rank is 4

-- DENSE_RANK(): if two products tie at rank 2, next rank is 3

SELECT

    category,

    product_name,

    revenue,

    RANK()       OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_with_gaps,

    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_continuous

FROM product_revenue;

Use RANK() when you want "top 3 positions" (accepting ties). Use DENSE_RANK() when you want "top 3 distinct ranks." Use ROW_NUMBER() when you want exactly N rows, no ties allowed.

LAG() and LEAD() — Comparing Rows Across Time

The LAG() function facilitates time-series analysis by retrieving values from previous rows to calculate deltas such as performance improvement over time.

sql

-- Month-over-month revenue change per product

SELECT

    product_id,

    month,

    revenue,

    LAG(revenue, 1) OVER (

        PARTITION BY product_id

        ORDER BY month

    ) AS prev_month_revenue,

    revenue - LAG(revenue, 1) OVER (

        PARTITION BY product_id

        ORDER BY month

    ) AS mom_change,

    ROUND(

        100.0 * (revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month))

        / NULLIF(LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month), 0),

        2

    ) AS mom_pct_change

FROM monthly_product_revenue

ORDER BY product_id, month;

The NULLIF(..., 0) wrapper on the denominator prevents division-by-zero errors when previous month revenue is 0. This is a production habit — raw calculations that can divide by zero will break your pipeline eventually.

Running Totals and Moving Averages

sql

-- Running total and 7-day moving average of daily sales

SELECT

    sale_date,

    daily_sales,

    -- Cumulative total from the start

    SUM(daily_sales) OVER (

        ORDER BY sale_date

        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

    ) AS cumulative_sales,

    -- 7-day moving average (current day + 6 preceding days)

    ROUND(

        AVG(daily_sales) OVER (

            ORDER BY sale_date

            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

        ),

        2

    ) AS moving_avg_7d

FROM daily_sales_summary

ORDER BY sale_date;

The ROWS BETWEEN frame specification is what controls which rows are included in the window calculation. UNBOUNDED PRECEDING AND CURRENT ROW means "from the very beginning up to now." 6 PRECEDING AND CURRENT ROW means "the last 7 rows including this one."

Named Windows — The Performance Trick Most Ignore

Named windows (WINDOW clause) reduce code duplication and can improve query plan optimization. Filter data in WHERE clauses before window function evaluation to maintain performance on large tables.

sql

-- Without named window: same definition repeated three times

SELECT

    department,

    employee_id,

    salary,

    RANK()   OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,

    SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_total,

    AVG(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS dept_avg

FROM employees;

-- With named window: defined once, referenced three times

SELECT

    department,

    employee_id,

    salary,

    RANK()      OVER dept_window AS dept_rank,

    SUM(salary) OVER dept_window AS dept_total,

    AVG(salary) OVER dept_window AS dept_avg

FROM employees

WINDOW dept_window AS (PARTITION BY department ORDER BY salary DESC);

The WINDOW clause (supported in PostgreSQL, MySQL 8+, and BigQuery) signals to the query planner that multiple functions share the same window computation — allowing it to calculate the partition once rather than three times. On large tables, this matters.

Part 2: CTEs — Writing SQL That Humans Can Read

CTEs simplify complex queries by allowing you to define temporary result sets that can be referenced within a single query. This modular approach enhances readability and maintainability.

The test for when to use a CTE: if you catch yourself writing a subquery inside a subquery inside another subquery, stop. Name the intermediate result and use a CTE.

sql

-- Without CTEs: nested subquery pyramid of doom

SELECT

    c.customer_id,

    c.name,

    order_summary.total_orders,

    order_summary.total_spent

FROM customers c

JOIN (

    SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_spent

    FROM (

        SELECT customer_id, amount

        FROM orders

        WHERE status = 'completed'

        AND created_at >= '2026-01-01'

    ) completed_orders

    GROUP BY customer_id

) order_summary ON c.customer_id = order_summary.customer_id

WHERE order_summary.total_spent > 1000;

-- With CTEs: each step is named, readable, debuggable

WITH completed_orders AS (

    SELECT customer_id, amount

    FROM orders

    WHERE status = 'completed'

    AND created_at >= '2026-01-01'

),

customer_summary AS (

    SELECT

        customer_id,

        COUNT(*) AS total_orders,

        SUM(amount) AS total_spent

    FROM completed_orders

    GROUP BY customer_id

)

SELECT

    c.customer_id,

    c.name,

    s.total_orders,

    s.total_spent

FROM customers c

JOIN customer_summary s ON c.customer_id = s.customer_id

WHERE s.total_spent > 1000;

Same result. Dramatically different maintainability.

Recursive CTEs — Hierarchical Data

Recursive queries in SQL are a powerful technique for working with hierarchical data. Unlike traditional SQL queries that process data in a single pass, recursive queries can call themselves repeatedly until a specific condition is met.

The canonical use case: organisational hierarchies. Find everyone who reports (directly or indirectly) to a given manager.

sql

-- Find all employees under manager_id = 42, at any level of the org

WITH RECURSIVE org_hierarchy AS (

    -- Base case: the manager themselves

    SELECT

        employee_id,

        name,

        manager_id,

        0 AS depth

    FROM employees

    WHERE employee_id = 42

    UNION ALL

    -- Recursive step: find direct reports of the previous level

    SELECT

        e.employee_id,

        e.name,

        e.manager_id,

        h.depth + 1

    FROM employees e

    JOIN org_hierarchy h ON e.manager_id = h.employee_id

    WHERE h.depth < 10    -- ALWAYS include a depth limit to prevent infinite loops

)

SELECT * FROM org_hierarchy ORDER BY depth, name;

Recursive CTEs can be slow on large datasets. Always include a depth limit (WHERE depth < 10) and ensure the join column (manager_id) is indexed. For very deep hierarchies, consider materialized path or nested set patterns instead.

The depth limit is not optional. A circular reference in the data (employee A reports to B, B reports to A) will cause the recursive CTE to loop infinitely without it, consuming memory until the query is killed.

CTE vs Temp Table vs Subquery: When to Use Which

PostgreSQL's CTE behaviour deserves a specific note: PostgreSQL recalculates a CTE's results each time it is referenced in the main query, leading to performance bottlenecks. This often forces developers to use temporary tables when the same data transformation is needed across multiple stages or queries. Materialized CTEs offer a way to "save" the results of a CTE in memory.

In PostgreSQL 12+, you can control this explicitly:

sql

-- Force materialisation: calculate once, reference many times

WITH expensive_calculation AS MATERIALIZED (

    SELECT ... FROM large_table WHERE ...

)

SELECT ... FROM expensive_calculation

UNION ALL

SELECT ... FROM expensive_calculation;

Part 3: MERGE — The Statement That Powers Production Pipelines

MERGE (also written as INSERT ... ON CONFLICT in PostgreSQL, or UPSERT in some dialects) is what makes pipelines idempotent. It handles the fundamental production requirement: "process new records, update changed records, leave everything else alone."

sql

-- Generic MERGE pattern (Snowflake/BigQuery/Databricks/SQL Server syntax)

MERGE INTO target_table AS target

USING source_data AS source

ON target.natural_key = source.natural_key

-- When the key exists and something changed: update

WHEN MATCHED AND (

    target.column_a != source.column_a

    OR target.column_b != source.column_b

)

THEN UPDATE SET

    target.column_a = source.column_a,

    target.column_b = source.column_b,

    target.updated_at = CURRENT_TIMESTAMP

-- When the key doesn't exist in target: insert

WHEN NOT MATCHED BY TARGET

THEN INSERT (natural_key, column_a, column_b, created_at, updated_at)

VALUES (source.natural_key, source.column_a, source.column_b,

        CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)

Optional: when target has a record that source no longer has (soft delete)

WHEN NOT MATCHED BY SOURCE

THEN UPDATE SET target.is_deleted = TRUE, target.deleted_at = CURRENT_TIMESTAMP;

The WHEN MATCHED AND (...) condition is the key production detail. Without it, every matched row gets updated on every pipeline run — touching rows that haven't changed, triggering downstream recalculations unnecessarily, and producing meaningless updated_at timestamps. Only update when something actually changed.

Part 4: Slowly Changing Dimensions — History in a Data Warehouse

This is the topic that separates data engineers who've built real warehouses from those who haven't.

A dimension table stores descriptive attributes — customer names, product categories, store locations. The question is: when those attributes change, what do you do?

The answer determines whether your warehouse can answer "what was this customer's segment in Q3 2025?" or only "what is their segment today?"

SCD Type 1 — Overwrite (No History)

SCD Type 1 overwrites values — no history is maintained. The simplest approach. Use it when the old value has no business meaning and you'll never need to ask "what was it before?"

sql

-- Type 1 via MERGE: update existing, insert new

MERGE INTO dim_customers AS target

USING staging_customers AS source

ON target.customer_id = source.customer_id

WHEN MATCHED THEN

    UPDATE SET

        target.name    = source.name,

        target.email   = source.email,

        target.segment = source.segment,

        target.updated_at = CURRENT_TIMESTAMP

WHEN NOT MATCHED THEN

    INSERT (customer_id, name, email, segment, created_at, updated_at)

    VALUES (source.customer_id, source.name, source.email, source.segment,

            CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

Use Type 1 when: the old value is wrong (a typo correction), or when historical accuracy of that attribute has no analytical value.

SCD Type 2 — Full History

SCD Type 2 preserves history by adding rows and setting flags: when an attribute changes, the current row gets "closed" via end_date / valid_to dates or an is_current flag. A new row is inserted with the updated value. Both versions are kept.

This is the pattern that enables time-travel analysis: "what segment was customer 12345 in on June 15, 2025?"

sql

-- SCD Type 2 MERGE: close existing record, insert new version

MERGE INTO dim_customers AS target

USING (

    SELECT

        customer_id,

        name,

        email,

        segment,

        CURRENT_TIMESTAMP AS effective_start,

        NULL             AS effective_end,

        TRUE             AS is_current

    FROM staging_customers

) AS source

ON target.customer_id = source.customer_id

AND target.is_current = TRUE

-- When matched AND something changed: close the old record

WHEN MATCHED AND (

    target.name    != source.name

    OR target.email   != source.email

    OR target.segment != source.segment

)

THEN UPDATE SET

    target.effective_end = CURRENT_TIMESTAMP,

    target.is_current    = FALSE

-- New records (not yet in dimension)

WHEN NOT MATCHED BY TARGET

THEN INSERT (customer_id, name, email, segment,

             effective_start, effective_end, is_current)

VALUES (source.customer_id, source.name, source.email, source.segment,

        source.effective_start, source.effective_end, source.is_current);

-- After MERGE: insert new versions for records that were just closed

INSERT INTO dim_customers (customer_id, name, email, segment,

                           effective_start, effective_end, is_current)

SELECT

    s.customer_id, s.name, s.email, s.segment,

    CURRENT_TIMESTAMP, NULL, TRUE

FROM staging_customers s

JOIN dim_customers t

    ON s.customer_id = t.customer_id

    AND t.effective_end = CURRENT_TIMESTAMP   -- just-closed records

    AND t.is_current = FALSE;

To query the current state of a customer:

sql

SELECT * FROM dim_customers WHERE is_current = TRUE;

To query what a customer's record looked like at a specific point in time:

sql

SELECT *

FROM dim_customers

WHERE customer_id = 12345

  AND effective_start <= '2025-06-15'

  AND (effective_end > '2025-06-15' OR effective_end IS NULL);

Use Type 2 when: historical accuracy matters for analysis. Customer segments, subscription plans, product categories, pricing tiers, employee department assignments — anywhere that "what was it then?" is a question the business needs to answer.

dbt Snapshots — Type 2 Without the Boilerplate

Writing and maintaining SCD Type 2 MERGE logic manually is error-prone. dbt's snapshot feature implements Type 2 automatically:

sql

-- snapshots/snap_customers.sql

{% snapshot snap_customers %}

{{

    config(

        target_schema='snapshots',

        unique_key='customer_id',

        strategy='timestamp',       -- use updated_at to detect changes

        updated_at='updated_at',    -- or 'check' to compare specific columns

        invalidate_hard_deletes=True

    )

}}

SELECT

    customer_id,

    name,

    email,

    segment,

    updated_at

FROM {{ source('crm', 'customers') }}

{% endsnapshot %}

dbt manages dbt_valid_from, dbt_valid_to, and dbt_updated_at automatically. Running dbt snapshot compares the current source state against the snapshot table and generates the correct inserts and updates.

Use timestamp strategy for metrics that change frequently and where every change matters. Use check strategy for categorical shifts where you're tracking transitions. If a visitor's page view count increases but their segment stays the same, no new snapshot record.

The honest trade-off: SCD2 is not free. A campaign that changes daily creates 365 records per year. Multiply by thousands of campaigns. Query complexity: historical queries require date filters and window functions. Not every analyst is comfortable with these. Before snapshotting everything, ask: do you actually need historical tracking?

Part 5: The Gaps-and-Islands Problem

This pattern comes up in almost every data engineering role and trips up almost every candidate who hasn't seen it before.

The problem: given a sequence of events, identify consecutive runs — "sessions" of activity, subscription periods, consecutive login days.

sql

Find consecutive active subscription periods per customer

Given: a table of daily subscription statuses (active/inactive)

WITH numbered AS (

    SELECT

        customer_id,

        date,

        status,

        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date) AS rn_all,

        ROW_NUMBER() OVER (PARTITION BY customer_id, status ORDER BY date) AS rn_status

    FROM subscription_daily

),

groups AS (

    SELECT

        customer_id,

        date,

        status,

        -- The difference between these two row numbers is constant within a consecutive run

        rn_all - rn_status AS group_id

    FROM numbered

)

SELECT

    customer_id,

    status,

    MIN(date) AS period_start,

    MAX(date) AS period_end,

    COUNT(*)  AS days_in_period

FROM groups

GROUP BY customer_id, status, group_id

ORDER BY customer_id, period_start;

The key insight: subtract the row number within each status group from the overall row number. Within a consecutive run of the same status, this difference stays constant. When the status breaks and resumes, the difference changes — identifying the gap.

The gaps-and-islands pattern identifies consecutive sequences in data — active subscription periods, consecutive login days, or uninterrupted production runs. This technique combines ROW_NUMBER with date arithmetic.

Part 6: Query Performance — Diagnose Before You Optimise

Diagnose slow queries with EXPLAIN plans. Identify full table scans, bad join strategies, and sort spills. Adding one index can turn a 45-second query into 0.3 seconds.

The discipline is: never guess about performance. Measure first, then act.

sql

-- PostgreSQL: EXPLAIN ANALYZE shows estimated AND actual execution

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)

SELECT

    c.customer_id,

    SUM(o.amount) AS total_spent

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

WHERE o.created_at >= '2026-01-01'

GROUP BY c.customer_id

HAVING SUM(o.amount) > 500;

The output tells you:

• Seq Scan vs Index Scan — full table scan is a red flag on large tables

• Actual rows vs estimated rows — a large discrepancy means stale statistics (run ANALYZE)

• Sort operations — ORDER BY without an index can spill to disk on large datasets

• Hash join vs Nested loop — wrong join strategy on large tables tanks performance

The Indexing Rules That Actually Matter

sql

Rule 1: Index columns used in WHERE clauses

CREATE INDEX idx_orders_created_at ON orders(created_at);

Rule 2: Index foreign keys used in JOIN conditions

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Rule 3: Composite index for multi-column filters

Index order matters: put the equality filter first, range filter second

CREATE INDEX idx_orders_status_date ON orders(status, created_at);

This index helps: WHERE status = 'completed' AND created_at >= '2026-01-01'

This index doesn't help: WHERE created_at >= '2026-01-01' AND status = 'completed'

(leading column of composite index must match the filter pattern)

Rule 4: For window functions, index PARTITION BY and ORDER BY columns

PARTITION BY customer_id ORDER BY created_at

CREATE INDEX idx_orders_customer_date ON orders(customer_id, created_at);

Index the columns used in PARTITION BY and ORDER BY clauses. A composite index matching the window definition eliminates sort operations.

The Query Optimisation Checklist

Before blaming the database, work through this list:

□  Filter rows early — WHERE clause before JOIN reduces data size

□  Use LIMIT during development — don't scan 100M rows to test logic

□  Avoid SELECT * in production — pull only the columns you need

□  Check for implicit type casts — WHERE date_col = '2026-01-01' on a timestamp

   column forces a cast on every row; use explicit date_trunc() instead

□  Check statistics freshness — ANALYZE if estimated rows are far from actual

□  Avoid functions on indexed columns in WHERE clauses

   -- Bad:  WHERE UPPER(status) = 'COMPLETED'  (can't use index)

   -- Good: WHERE status = 'completed'  (store normalised values)

□  Partition large tables — BigQuery and Snowflake partition on date columns;

   queries that filter on the partition column skip irrelevant partitions entirely

□  Use APPROXIMATE functions for exploratory work

   -- APPROX_COUNT_DISTINCT() is 10-100x faster than COUNT(DISTINCT)

   -- Good enough for dashboards; use exact only when precision is required

Part 7: SQL in dbt — The Production Standard

dbt (data build tool) is how serious data teams write and manage SQL transformation code in 2026. It brings software engineering practices — version control, testing, documentation, modular design — to SQL that previously lived in ad hoc scripts.

The core concept: every SQL file in a dbt project is a model. A model defines a table or view. Models reference each other using {{ ref() }}. dbt builds the dependency graph and executes models in the right order.

sql

-- models/staging/stg_orders.sql

-- Staging layer: clean and standardise raw source data

WITH source AS (

    SELECT * FROM {{ source('ecommerce', 'raw_orders') }}

),

cleaned AS (

    SELECT

        order_id,

        customer_id,

        LOWER(TRIM(status))                    AS status,

        CAST(amount AS DECIMAL(10, 2))         AS amount,

        DATE_TRUNC('day', created_at)          AS order_date,

        CURRENT_TIMESTAMP                      AS _loaded_at

    FROM source

    WHERE order_id IS NOT NULL        -- remove null PKs at staging

)

SELECT * FROM cleaned

sql

-- models/marts/fct_orders.sql

-- Fact table: business-level order metrics

WITH orders AS (

    SELECT * FROM {{ ref('stg_orders') }}   -- reference the staging model

),

customers AS (

    SELECT * FROM {{ ref('stg_customers') }}

)

SELECT

    o.order_id,

    o.customer_id,

    c.segment                       AS customer_segment,

    o.status,

    o.amount,

    o.order_date,

    -- Business metric calculated in SQL

    CASE

        WHEN o.amount >= 500 THEN 'high_value'

        WHEN o.amount >= 100 THEN 'mid_value'

        ELSE 'low_value'

    END                             AS order_tier

FROM orders o

LEFT JOIN customers c ON o.customer_id = c.customer_id

Testing in dbt runs as SQL assertions:

yaml

# models/marts/schema.yml

models:

  - name: fct_orders

    columns:

      - name: order_id

        tests:

          - not_null

          - unique                  # every order_id appears exactly once

      - name: status

        tests:

          - accepted_values:

              values: ['pending', 'completed', 'failed', 'refunded']

      - name: customer_id

        tests:

          - not_null

          - relationships:

              to: ref('stg_customers')

              field: customer_id   # every customer_id in orders exists in customers

dbt test runs all of these assertions against the actual data in your warehouse, on every deployment. Tests that fail block the pipeline from completing — catching data quality issues before they reach dashboards.

The SQL Skills That Get You Hired

SQL is the #1 skill tested in data engineering interviews. The most commonly required experience level is 2-4 years, appearing in 17% of job postings. Around half of job listings don't mention a specific experience requirement — companies care more about what you can actually do than how many years you've been in the field.

The patterns that come up most in interviews and most in production:

Deduplication with ROW_NUMBER() — almost every pipeline needs it
Running totals and cohort analysis with window functions — dashboard staples
CTEs for complex multi-step logic — readable, debuggable transformation chains
MERGE for upserts — idempotent pipelines
SCD Type 2 — history tracking in warehouses
Gaps-and-islands — session analysis, consecutive period identification
EXPLAIN plan reading — diagnosing why a query is slow
Incremental processing logic — processing only new/changed records, not full reloads

Knowing these patterns conceptually is not the same as writing them under interview conditions on a problem you haven't seen before. That fluency comes from practice — writing and debugging real SQL on real data, not reading about it.

Build the SQL Foundation, Then Go Further

SQL mastery is the floor, not the ceiling.

The best data engineers know SQL deeply enough to write it fluently without reference — then layer Python, Spark, orchestration, and cloud infrastructure on top of that foundation. The SQL is what makes everything else possible: the models, the tests, the documented transformation logic that others can read, modify, and extend without a three-hour archaeology session.

IABAC's Certified Data Scientist programme covers the analytical SQL foundation alongside the Python engineering skills, statistical thinking, and ML data infrastructure knowledge that round out a complete data practitioner. For those building specifically toward analytics engineering or data warehousing roles, the curriculum covers the dbt-centric modern data stack in depth.

Explore the full IABAC Data Science certification portfolio or continue with the Python for Data Engineering guide to see where SQL ends and Python picks up.

Sources Referenced

• Airbyte: 15 Advanced SQL Concepts with Examples 2026 — window functions, CTEs, recursive queries, pivoting

• SharpSkill: SQL Window Functions, CTEs and Advanced Queries for Data Analysts 2026 — WINDOW clause, performance indexing rules

• DataForge: Advanced SQL Concepts for Data Engineers — CTE materialisation, correlated subqueries, PostgreSQL CTE behaviour

• DataVidhya: Learn SQL for Data Engineering 2026 — 30 Practice Problems — interview patterns, EXPLAIN plans, incremental processing

• Medium / EDTS (Sallu Muharomah): Advanced SQL Techniques for Data Engineers — window function use cases, CTE modular design

• Dev|Journal (earezki.com): Advanced SQL Techniques — Mastering Window Functions and CTEs 2026 — GROUP BY vs window function distinction

• DataDriven: Slowly Changing Dimensions 2026 — SCD Types 1–6, interview context, dbt snapshot patterns

• DataEngineerAcademy: Slowly Changing Dimensions Type 2 with SQL and dbt — MERGE patterns, check vs timestamp strategy

• OneUptime: Data Warehouse Type 1 SCD 2026 — dbt incremental model implementation

• DataSchool (Matthias Albert): dbt Snapshots and Slowly Changing Dimensions — dbt valid_from/valid_to, point-in-time queries

• Databricks Blog: Stop Hand-Coding Change Data Capture Pipelines — AutoCDC, MERGE complexity, declarative patterns

• The Pipe and the Line (Alejandro Aboy): SQL to dbt Guide — Slowly Changing Dimensions with dbt Snapshots — LAG with dbt_valid_from, trade-offs of SCD2

• dataloopr: Advanced SQL for Data Analysis — CTEs and Window Functions — feature engineering, LAG for training data

Related reading from IABAC:

• Python for Data Engineering — where SQL hands off to Python in the stack

• What Does a Data Engineer Do? — the full role, skills, and career guide

• MLOps Tools You Should Know — the operational layer above data pipelines

• AI Applications & Technologies Complete Guide