Advanced SQL for Data Engineers Window Functions CTEs and Query Optimization
Slow transformation SQL rarely fails loudly. It passes tests on sample data, then stalls during a nightly load, spills to disk, scans years of history, and blocks downstream models. Advanced SQL is not just about writing clever queries. It is about writing queries that survive production volume, late-arriving data, skewed keys, cloud warehouse pricing, and repeatable orchestration.
This post covers three areas that shape production-grade SQL: analytical window functions, common table expressions, and query performance tuning. The focus stays on patterns data engineers use every day, such as deduplication, incremental loads, hierarchy expansion, running totals, and scan reduction.
Window functions turn row-level data into analytical context
A window function calculates across a set of related rows while keeping each row visible. That makes it a natural fit for transformation work. Instead of grouping data and losing detail, the query can add sequence, comparison, ranking, and cumulative values.
The general pattern is:
```sql
function_name() OVER (
PARTITION BY partition_column
ORDER BY order_column
ROWS BETWEEN ... AND ...
)
```
`PARTITION BY` defines the logical group. `ORDER BY` defines sequence inside the group. The frame defines which rows inside that ordered set are visible to the function.
This matters in production because most transformation pipelines need context:
Which version of a record is the latest?
What was the previous status?
Has this customer crossed a spend threshold?
Which event started the current session?
Is this row a duplicate, a correction, or a new fact?
DENSE_RANK helps with ties and repeatable ordering
`DENSE_RANK()` assigns ranks without gaps. If two rows tie for first place, both get rank 1, and the next rank is 2. This differs from `RANK()`, which would skip to 3.
A common use is selecting the latest record per business key when multiple records can share the same timestamp.
```sql
WITH ranked_orders AS (
SELECT
order_id,
customer_id,
order_status,
updated_at,
ingestion_batch_id,
DENSE_RANK() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC
) AS recency_rank
FROM staging.order_events
)
SELECT *
FROM ranked_orders
WHERE recency_rank = 1;
```
This keeps all latest rows when there is a tie. That may be exactly what the business rule requires, especially when two source systems publish valid changes at the same time.
For a single surviving row, use `ROW_NUMBER()` with a deterministic tie-breaker:
```sql
ROW_NUMBER() OVER (
PARTITION BY order_id
ORDER BY updated_at DESC, ingestion_batch_id DESC
) AS row_num
```
In production, never rely on timestamp ordering alone if ties are possible. Add a stable column such as batch ID, source priority, sequence number, or file modification time.
LEAD and LAG expose changes between rows
`LAG()` reads a previous row. `LEAD()` reads a following row. These functions are ideal for change detection, slowly changing dimensions, event timelines, and status duration calculations.
```sql
WITH status_history AS (
SELECT
ticket_id,
status,
status_changed_at,
LAG(status) OVER (
PARTITION BY ticket_id
ORDER BY status_changed_at
) AS previous_status,
LEAD(status_changed_at) OVER (
PARTITION BY ticket_id
ORDER BY status_changed_at
) AS next_changed_at
FROM source.ticket_status_events
)
SELECT
ticket_id,
status,
previous_status,
status_changed_at AS valid_from,
COALESCE(next_changed_at, TIMESTAMP '9999-12-31 00:00:00') AS valid_to
FROM status_history
WHERE previous_status IS NULL
OR previous_status <> status;
```
This pattern removes repeated status rows and builds `valid_from` and `valid_to` ranges. It is widely used in dimensional modelling and lakehouse transformation jobs.
The production risk sits in ordering. If events can arrive late, use event time for business sequencing and ingestion time for replay logic. Store both. Event time answers “when did it happen?” Ingestion time answers “when did we learn about it?”
SUM OVER with frames controls cumulative logic
`SUM() OVER` is useful for running totals, rolling windows, balance calculations, and threshold detection. The frame clause controls how much history each row can see.
```sql
SELECT
account_id,
transaction_date,
transaction_id,
amount,
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_balance
FROM curated.account_transactions;
```
`ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` means each row sees all prior rows in the partition plus itself.
For rolling calculations, bound the frame:
```sql
SUM(amount) OVER (
PARTITION BY account_id
ORDER BY transaction_date, transaction_id
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_transaction_total
```
Use `ROWS` when physical row count matters. Use `RANGE` when value range matters, but be careful. `RANGE` can include multiple rows with the same ordering value. That can surprise teams when many records share a date or timestamp.
A strong production pattern is to pre-aggregate to the grain needed, then apply the window. For example, calculate daily customer revenue first, then calculate a 30-day running total. That reduces row volume and makes frame behaviour easier to validate.
Recursive CTEs make hierarchy logic readable when used carefully
A common table expression gives a query a named temporary result. A recursive CTE goes further. It references itself to walk a hierarchy, graph, or chain of relationships.
Typical use cases include:
Employee to manager trees
Product category paths
Account parent-child groups
Bill of materials expansion
Folder or location hierarchies
Dependency chains in orchestration metadata
A recursive CTE has two parts. The anchor query selects the starting rows. The recursive query joins back to the CTE to find the next level.
```sql
WITH recursive_category AS (
SELECT
category_id,
parent_category_id,
category_name,
CAST(category_name AS VARCHAR(1000)) AS category_path,
0 AS depth
FROM dim_category
WHERE parent_category_id IS NULL
UNION ALL
SELECT
c.category_id,
c.parent_category_id,
c.category_name,
CAST(rc.category_path || ' > ' || c.category_name AS VARCHAR(1000)) AS category_path,
rc.depth + 1 AS depth
FROM dim_category c
JOIN recursive_category rc
ON c.parent_category_id = rc.category_id
)
SELECT *
FROM recursive_category;
```
The exact syntax varies by engine. Some platforms use `WITH RECURSIVE`. Some T-SQL platforms use a normal `WITH` clause plus a recursive member. Some cloud warehouses limit or do not support recursion in the warehouse engine. When that happens, build the hierarchy in Spark, a notebook job, or a staged iterative process, then persist the flattened result for SQL consumption.
That practical point matters for Azure Synapse, Fabric Warehouse, and Databricks SQL. Recursive CTE support and limits can differ across runtimes and versions. Treat recursion as a modelling pattern first, then check engine support before making it part of a critical path.
Guardrails for production recursion
Recursive queries can explode if the data contains cycles or unexpected depth. Build explicit controls.
```sql
WITH category_tree AS (
SELECT
category_id,
parent_category_id,
category_name,
CAST(category_id AS VARCHAR(4000)) AS visited_ids,
0 AS depth
FROM dim_category
WHERE parent_category_id IS NULL
UNION ALL
SELECT
c.category_id,
c.parent_category_id,
c.category_name,
ct.visited_ids || ',' || CAST(c.category_id AS VARCHAR(50)),
ct.depth + 1
FROM dim_category c
JOIN category_tree ct
ON c.parent_category_id = ct.category_id
WHERE ct.depth < 20
AND ct.visited_ids NOT LIKE '%' || CAST(c.category_id AS VARCHAR(50)) || '%'
)
SELECT *
FROM category_tree;
```
The code above shows two safety ideas: a maximum depth and a visited list. In production, the visited check should be implemented carefully to avoid false matches. A delimiter-aware array or path table is safer where supported.
For large hierarchies, recursive CTEs are often best used during batch preparation, not in every reporting query. Persist the output as a bridge table or flattened dimension:
```sql
dim_category_hierarchy (
category_id,
ancestor_category_id,
depth_from_ancestor,
category_path,
effective_from,
effective_to
)
```
This supports fast joins, point-in-time reporting, and repeatable transformations.
Cloud warehouse indexing is really access path design
In traditional row-store systems, indexing often means creating B-tree indexes on filter and join columns. In cloud data warehouses, the idea changes. Performance depends on distribution, partitioning, clustering, columnar storage, file statistics, data skipping, and materialised intermediate tables.
The term “indexing strategy” still applies, but the objects differ by platform.
Platform | Practical access path choices | Production guidance |
Azure Synapse dedicated SQL pool | Clustered columnstore indexes, hash distribution, replicated dimensions, statistics, partitioning | Distribute large fact tables on stable high-cardinality join keys. Replicate small dimensions. Keep statistics current after major loads. |
Azure Synapse serverless SQL | File partitioning, Parquet layout, predicate pushdown, folder pruning | Store curated data in columnar files. Query selective folders. Avoid scanning raw unpartitioned lakes for recurring transformations. |
Microsoft Fabric Warehouse | Columnar storage, automatic engine tuning, statistics, table design, partition-friendly modelling | Design tables around query grain and filter patterns. Use staging tables to reduce data early before joining wide curated tables. |
Databricks SQL | Delta Lake file statistics, partition pruning, Z-ordering or liquid clustering where available, compacted files | Partition only on useful low-to-medium cardinality columns. Cluster on common filters and joins. Compact small files before heavy SQL workloads. |
A good access path starts with workload questions:
Which columns appear in `WHERE` clauses?
Which columns drive joins between facts and dimensions?
Which date range does the pipeline process each run?
Which tables are small enough to replicate or broadcast?
Which transformations touch only recent data?
Which columns have high skew?
For Azure Synapse dedicated SQL pools, distribution choices can dominate performance. A large fact table hash-distributed by `customer_id` joins well to another large table distributed by the same key. If the join keys differ, the engine may move data between distributions. That data movement can cost more than the SQL calculation itself.
For Databricks SQL, file layout matters. Delta table statistics allow the engine to skip files whose min and max values cannot match the filter. Clustering related values into fewer files improves that skipping. Partitioning every high-cardinality column is a mistake because it creates too many folders and small files.
For Fabric Warehouse, avoid assuming you can tune it like an old SMP database. Table design, model grain, statistics, and reducing intermediate row counts matter more than manually adding many indexes.
Full table scans disappear when filters match storage and pipelines process less data
A full table scan is not always bad. Columnar engines can scan large data quickly, especially when queries need many rows. The costly scans are the accidental ones: a pipeline reads five years of data to process yesterday’s changes, or a dashboard scans every file because the filter cannot be pushed down.
Keep filters sargable
A predicate is sargable when the engine can use statistics, partitions, or indexes to narrow the read. Avoid wrapping filtered columns in functions.
Poor pattern:
```sql
WHERE CAST(order_timestamp AS DATE) = DATE '2025-01-15'
```
Better pattern:
```sql
WHERE order_timestamp >= TIMESTAMP '2025-01-15 00:00:00'
AND order_timestamp < TIMESTAMP '2025-01-16 00:00:00'
```
The second form preserves the raw column and gives the engine a clean range.
Process changes, not the whole table
Incremental transformations should use watermarks, change tables, or merge windows.
```sql
WITH changed_orders AS (
SELECT *
FROM staging.orders
WHERE ingestion_timestamp > (
SELECT last_successful_watermark
FROM control.pipeline_watermarks
WHERE pipeline_name = 'orders_curated'
)
)
MERGE INTO curated.orders AS target
USING changed_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN UPDATE SET
target.status = source.status,
target.updated_at = source.updated_at
WHEN NOT MATCHED THEN INSERT (
order_id,
customer_id,
status,
updated_at
)
VALUES (
source.order_id,
source.customer_id,
source.status,
source.updated_at
);
```
A safer production version often uses a lookback window, such as reprocessing the last few days, to catch late-arriving updates. The target table should make that lookback cheap through partitioning, clustering, or distribution.
Filter before joining wide tables
Join order is the engine’s job, but query shape still matters. Reduce large datasets before bringing in many columns.
```sql
WITH recent_sales AS (
SELECT
sale_id,
customer_id,
product_id,
sale_date,
net_amount
FROM fact_sales
WHERE sale_date >= DATE '2025-01-01'
AND sale_date < DATE '2025-02-01'
),
active_products AS (
SELECT product_id, category_id
FROM dim_product
WHERE is_active = true
)
SELECT
rs.sale_date,
ap.category_id,
SUM(rs.net_amount) AS revenue
FROM recent_sales rs
JOIN active_products ap
ON rs.product_id = ap.product_id
GROUP BY
rs.sale_date,
ap.category_id;
```
This makes the intended grain clear and limits unnecessary columns. It also improves maintainability when the query becomes part of a scheduled model.
Replace repeated scans with persisted stages
If five downstream models read the same expensive CTE, materialise it. CTEs improve readability, but many engines inline them rather than storing them. That can cause repeated scans inside one query or across jobs.
Use a persisted stage when:
The intermediate result is reused by many models
The source table is very large
The filter is stable for a batch
The same deduplication logic appears in several queries
The transformation feeds both facts and dimensions
A production pattern for Advanced SQL for Data Engineers is to separate logic into clear layers:
Raw ingestion
Keep source shape, metadata, and file lineage.
Staging
Cast fields, standardise names, remove malformed rows, and deduplicate with window functions.
Curated dimensions and facts
Apply business keys, hierarchies, validity ranges, and conformed measures.
Serving aggregates
Precompute stable metrics for dashboards and downstream applications.
Each layer should reduce uncertainty. The best SQL transformations are not only fast, they are explainable during an incident.
The best advanced SQL patterns are predictable under load
Advanced SQL is useful when it makes production behaviour easier to reason about. Window functions add ordered context without losing row detail. `DENSE_RANK()` handles tied business events, `LEAD()` and `LAG()` expose change over time, and framed `SUM() OVER` expressions make cumulative logic explicit.
Recursive CTEs make hierarchy handling readable, but they need depth limits, cycle protection, and a clear plan for engines that do not support recursion well. For large or frequently queried hierarchies, persist the expanded structure.
Query performance in cloud warehouses comes from matching SQL patterns to storage design. In Synapse, think distribution, columnstore, replication, and statistics. In Fabric Warehouse, design table grain and transformations so the engine reads less. In Databricks SQL, manage Delta file layout, clustering, and partition pruning.
The next step is practical: pick one slow production transformation and inspect its filters, joins, windows, and storage layout. If the query scans data it does not need, fix that first. If the logic is hard to explain, split it into stable stages. Good data engineering SQL should be fast, correct, and calm at 02:00 when the pipeline is under pressure.
Comments