SQL voor Data Engineers

Introductie

Elke vacaturetekst voor data engineering noemt Python, Spark en steeds vaker ervaring met een cloudplatform — maar de vaardigheid die in letterlijk elke vacature terugkomt, en die het verschil maakt tussen engineers die snel kunnen bouwen en engineers die dat niet kunnen, is SQL. Niet "ik kan een SELECT met een JOIN schrijven"-SQL, maar SQL die deduplicatie correct afhandelt, data idempotent laadt, en niet stilletjes je omzetcijfers verdubbelt door een one-to-many join die je over het hoofd zag.

Dit hoofdstuk is geen SQL-syntaxcursus — als je al een basisquery kunt schrijven, behandelt dit de patronen die voortdurend terugkomen in echte pipelines en die de meeste SQL-cursussen volledig overslaan: window functions voor deduplicatie, MERGE voor idempotent laden, en het join- en query-executiegedrag dat subtiele, lastig te debuggen bugs veroorzaakt in productie.

Query Execution Order

Vóór de patronen eerst één fundamenteel concept dat de helft van de "waarom werkt dit niet"-verwarring bij beginners verklaart: SQL wordt in één volgorde geschreven, maar in een andere volgorde uitgevoerd.

FROM  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  ORDER BY  →  LIMIT

Dit is waarom je geen kolomalias uit SELECT kunt gebruiken in een WHERE-clausule — op het moment dat WHERE wordt geëvalueerd, is SELECT nog niet uitgevoerd:

-- Dit faalt: 'total' bestaat nog niet wanneer WHERE wordt geëvalueerd
SELECT amount * quantity AS total
FROM order_items
WHERE total > 100;   -- FOUT bij de meeste databases

-- Dit werkt wel: HAVING draait na SELECT/GROUP BY
SELECT amount * quantity AS total
FROM order_items
GROUP BY amount, quantity
HAVING amount * quantity > 100;

Het verklaart ook waarom filteren in WHERE doorgaans goedkoper is dan filteren in HAVING: WHERE reduceert het aantal rijen voordat er wordt gegroepeerd en geaggregeerd, terwijl HAVING filtert nadat de (mogelijk kostbare) aggregatie al over de volledige set is berekend. Deze execution order begrijpen is het nuttigste stukje SQL-theorie dat er is voor het schrijven van zowel correcte als snelle queries.

Window Functions in Detail

Window functions zijn de belangrijkste SQL-feature specifiek voor data engineering, omdat je er iets mee kunt berekenen over een set gerelateerde rijen zonder die rijen samen te voegen tot één, zoals GROUP BY doet. De syntax is altijd een functie gevolgd door OVER (PARTITION BY ... ORDER BY ...).

ROW_NUMBER() kent een uniek, opeenvolgend nummer toe binnen elke partitie — de ruggengraat van deduplicatie, hieronder uitgebreid behandeld.

RANK() en DENSE_RANK() lijken erop, maar gaan anders om met gelijkspel: RANK() laat gaten vallen na een gelijkspel (1, 2, 2, 4), DENSE_RANK() niet (1, 2, 2, 3). Gebruik DENSE_RANK() voor dingen als "top 3 categorieën op omzet" waarbij je exact 3 unieke rangwaarden wilt, ook bij gelijkspel.

LAG() en LEAD() kijken naar de vorige of volgende rij binnen een partitie — essentieel voor tijdreeksvergelijkingen zoals "omzet vs. vorige dag":

SELECT
    order_date,
    daily_revenue,
    LAG(daily_revenue) OVER (ORDER BY order_date) AS prev_day_revenue,
    daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date) AS day_over_day_change
FROM gold.daily_revenue
ORDER BY order_date;

Lopende totalen en voortschrijdende gemiddelden gebruiken aggregatiefuncties als window function door een frame-clausule toe te voegen:

SELECT
    order_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS trailing_7day_avg
FROM gold.daily_revenue;

Deduplicatiepatronen in Detail

Deduplicatie is een van de meest voorkomende bewerkingen in een data pipeline, en er zijn drie manieren om het te doen met betekenisvol verschillend gedrag en performance.

ROW_NUMBER() + filter — de meest expliciete en controleerbare aanpak, en degene waar je standaard voor moet kiezen:

SELECT * FROM (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY order_id
            ORDER BY updated_at DESC, ingested_at DESC  -- tie-breaker is belangrijk!
        ) AS rn
    FROM raw.orders
)
WHERE rn = 1;

Let op de tweekolommige ORDER BY in het window — updated_at DESC alleen is niet genoeg als twee versies van dezelfde rij een identieke timestamp hebben (wat vaker voorkomt dan je zou denken bij batch-geladen data). Zonder een deterministische tie-breaker kiest ROW_NUMBER() willekeurig, en welke rij "wint" kan verschillen tussen query-runs — een subtiele bron van niet-reproduceerbare pipeline-output die achteraf lastig te debuggen is.

SELECT DISTINCT verwijdert volledig identieke rijen, maar doet niets als rijen in ook maar één kolom verschillen (zoals een loaded_at-timestamp die tijdens ingestion is toegevoegd, wat extreem vaak voorkomt). Het is verleidelijk omdat het kort is, maar het is het verkeerde gereedschap voor "bewaar de laatste versie van elk record" — het kan alleen exacte duplicaten verwijderen, geen conflicterende versies.

QUALIFY (ondersteund in Snowflake, Databricks SQL en BigQuery) laat je direct filteren op een window function, zonder de subquery-wrapper:

SELECT *
FROM raw.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1;

Functioneel identiek aan het ROW_NUMBER() + subquery-patroon, maar aanzienlijk leesbaarder zodra je eraan gewend bent. Het bestaat niet in standaard ANSI SQL, PostgreSQL of SQL Server, dus het subquery-patroon blijft de portabele keuze wanneer je SQL over meerdere dialecten heen moet werken — relevant als je dbt-modellen schrijft die meerdere warehouse-adapters moeten ondersteunen.

Idempotente, Incrementele SQL Schrijven

Een pipeline die veilig opnieuw gedraaid kan worden — na een fout, voor een backfill, of gewoon omdat iemand hem per ongeluk twee keer triggerde — moet idempotent zijn: N keer draaien levert hetzelfde resultaat op als één keer draaien. MERGE (in sommige dialecten ook UPSERT genoemd) is hiervoor het belangrijkste gereedschap:

MERGE INTO silver.orders AS tgt
USING staging.orders AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.updated_at > tgt.updated_at THEN
    UPDATE SET amount = src.amount, updated_at = src.updated_at, status = src.status
WHEN NOT MATCHED THEN
    INSERT (order_id, amount, updated_at, status)
    VALUES (src.order_id, src.amount, src.updated_at, src.status);

Twee details die ertoe doen en die beginners vaak missen. Ten eerste de AND src.updated_at > tgt.updated_at-guard op de UPDATE-tak — zonder deze zou een herhaalde merge met verouderde staging-data (bijvoorbeeld een opnieuw getriggerde job die uit een bron leest die niet is veranderd) een nieuwere doelrij overschrijven met oudere data. Ten tweede te laat binnenkomende data: als je watermark-logica extraheert op basis van updated_at, kan een record dat na het verstrijken van je extractievenster wordt gebackfilled of gecorrigeerd volledig gemist worden, tenzij je extractiequery een lookback-buffer bevat (bijvoorbeeld altijd de laatste 3 dagen opnieuw ophalen, niet alleen "sinds de watermark") om late correcties op te vangen.

MySQL gebruikt een andere syntax voor hetzelfde idee — INSERT ... ON DUPLICATE KEY UPDATE — en PostgreSQL gebruikt INSERT ... ON CONFLICT ... DO UPDATE. Het concept is universeel, ook waar de syntax dat niet is; de SQL Generator-tool regelt de dialectvertaling voor je.

CTE's versus Subqueries versus Temp Tables versus Views

Alle vier kunnen dezelfde logische transformatie uitdrukken, maar ze gedragen zich verschillend genoeg dat de keuze ertoe doet:

• CTE's (WITH x AS (...)) zijn de meest leesbare manier om een complexe query op te breken in benoemde, opeenvolgende stappen. In de meeste moderne warehouses (Snowflake, Databricks SQL, PostgreSQL 12+) kan de optimizer een CTE inlinen of materialiseren afhankelijk van hoe hij wordt gebruikt — je hoeft je meestal geen zorgen te maken over performance, tenzij dezelfde CTE veel keren wordt aangeroepen, in welk geval sommige engines hem elke keer opnieuw uitvoeren in plaats van het resultaat te cachen.
• Subqueries zijn logisch equivalent aan CTE's, maar schaden de leesbaarheid zodra ze meer dan één of twee niveaus diep genest worden. Geef bij alles wat niet-triviaal is de voorkeur aan CTE's.
• Temp tables materialiseren resultaten fysiek, wat nuttig is wanneer hetzelfde tussenresultaat duur is om te berekenen en vaak wordt hergebruikt binnen een sessie — de afweging is dat je de schrijfkosten vooraf betaalt.
• Views slaan helemaal geen data op; het is een opgeslagen querydefinitie, die elke keer opnieuw wordt uitgevoerd bij bevraging. Handig als stabiele abstractie over veranderende brontabellen, maar geen performance-optimalisatie — als de onderliggende query traag is, is de view precies zo traag, elke keer weer. Dit onderscheid, en wanneer je in plaats daarvan naar een Snowflake Dynamic Table grijpt, wordt verder behandeld in het hoofdstuk Snowflake.

Joins en hun Performance-implicaties

Joins zijn waar correctheidsbugs zich het vaakst verstoppen, en de fan-out-valkuil is verreweg de meest voorkomende. Stel je voor dat je een orders-tabel (één rij per order) joint met een order_items-tabel (meerdere rijen per order — één per orderregel) en dan probeert de omzet te sommeren:

-- FOUT: dit vermenigvuldigt order-niveau totalen met het aantal items per order
SELECT
    o.order_id,
    o.order_total,          -- een kolom op order-niveau
    SUM(o.order_total) AS revenue   -- uitgewaaierd door de join, enorm overschat
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
GROUP BY o.order_id, o.order_total;

Omdat de join één rij per orderregel oplevert, wordt o.order_total herhaald voor elk item, en het sommeren daarvan overschat de omzet enorm. De oplossing is om order_items te aggregeren tot één rij per order vóórdat je joint, of om het order-niveau-totaal te selecteren uit een bron die al op de juiste granulariteit staat:

-- CORRECT: eerst aggregeren naar order-granulariteit, dan pas joinen indien nodig
SELECT order_id, SUM(order_total) AS revenue
FROM orders
GROUP BY order_id;

Deze "fan-out"-bug is verraderlijk makkelijk te introduceren in een grotere query met meerdere joins, en geeft zelden een foutmelding — hij produceert gewoon stilletjes een getal dat te hoog is. Wees altijd expliciet, in elk geval voor jezelf, over op welke granulariteit elke tabel in een join staat vóórdat je aggregeert.

Over het type join: kies standaard voor INNER JOIN, tenzij je specifiek niet-gematchte rijen van één kant nodig hebt, in welk geval je bewust een LEFT JOIN gebruikt. Een per ongeluk gebruikte LEFT JOIN waar een INNER JOIN bedoeld was, introduceert stilletjes NULL-rijen die downstream-aggregaties op subtiele wijze kunnen breken.

Veelvoorkomende Performance-valkuilen

• Een gefilterde kolom in een functie wikkelen. WHERE UPPER(email) = 'X@Y.COM' voorkomt dat de query-engine efficiënt kan pruning op email, omdat hij de functie op elke rij moet evalueren voordat hij kan filteren. Normaliseer data bij het schrijven in plaats van te leunen op runtime-functies bij het filteren.
• Impliciete type casts. Een VARCHAR-kolom vergelijken met een numerieke literal dwingt een impliciete cast af op elke rij, wat — afhankelijk van de engine — stilletjes partition pruning of indexering kan uitschakelen. Cast expliciet en consistent in plaats van te vertrouwen op de impliciete coercion-regels van de engine.
• SELECT DISTINCT als vervanging voor correcte deduplicatie. Zoals hierboven behandeld, verwijdert het alleen exact identieke rijen — het gebruiken om data "op te schonen" die eigenlijk conflicterende versies bevat, levert onjuiste resultaten op, niet alleen trage.
• Ontbrekende partition pruning. Filteren op een afgeleide expressie (WHERE YEAR(order_date) = 2026) in plaats van op de ruwe gepartitioneerde kolom (WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01') kan voorkomen dat de engine irrelevante partities overslaat, wat een volledige scan afdwingt.
• Per ongeluk cross joins. Een kommagescheiden FROM a, b zonder een echte JOIN ... ON-voorwaarde produceert stilletjes een cartesisch product — elke rij van a gekoppeld aan elke rij van b. Bij kleine tabellen valt dit misschien niet op; bij grote kan het een warehouse tot stilstand brengen.

Best Practices

• Gebruik altijd een expliciete, deterministische tie-breaker in de ROW_NUMBER()-ordening voor deduplicatie — neem nooit aan dat timestamps uniek zijn.
• Bescherm MERGE-updates met een freshness-check (src.updated_at > tgt.updated_at) zodat een herhaalde run met verouderde data nooit goede data kan overschrijven met oude data.
• Aggregeer naar de juiste granulariteit vóór het joinen, niet erna, om fan-out-bugs te voorkomen.
• Geef standaard de voorkeur aan INNER JOIN; gebruik LEFT JOIN bewust en controleer achteraf op onverwachte NULLs.
• Filter op ruwe kolommen, niet op afgeleide expressies, om partition pruning en indexgebruik te behouden.
• Gebruik CTE's om tussenstappen te benoemen — een query die leest als een reeks benoemde stappen is aanzienlijk makkelijker voor de volgende persoon (vaak jijzelf, over zes maanden) om te debuggen.

Veelgemaakte Fouten

• SELECT DISTINCT gebruiken om duplicaten "op te lossen" zonder te begrijpen of de duplicaten exacte kopieën zijn of conflicterende versies van hetzelfde record.
• De tie-breaker vergeten bij een ROW_NUMBER()-dedup, wat leidt tot niet-deterministische output die verschilt tussen runs.
• Joinen vóór het aggregeren en stilletjes een metric uitwaaieren, wat getallen oplevert die fout zijn maar niet duidelijk fout — de query draait prima en geeft plausibel ogende resultaten terug.
• MERGE als automatisch veilig beschouwen zonder freshness-guard, waardoor verouderde herhaalde runs data stilletjes kunnen terugdraaien.
• Subqueries nesten in plaats van CTE's gebruiken, wat SQL oplevert die technisch correct is maar bijna onmogelijk voor een collega om te reviewen.

Performance Tips

• Duw filters zo vroeg mogelijk — in CTE's, niet alleen in de buitenste query — zodat downstream joins en aggregaties op minder data hoeven te werken.
• Materialiseer dure, hergebruikte tussenresultaten als temp tables in plaats van dezelfde CTE-logica meerdere keren in één query te herhalen.
• Match je ORDER BY/PARTITION BY-kolommen met de clustering- of partitioneringssleutel van je tabel waar mogelijk, zodat window functions kunnen profiteren van al gesorteerde data in plaats van een volledige sortering te triggeren.
• Vermijd onnodige ORDER BY in subqueries — sorteervolgorde blijft toch zelden behouden na een buitenste aggregatie, dus vroeg sorteren verspilt compute zonder voordeel.
• Batch je MERGE-operaties — één rij tegelijk mergen in een loop is aanzienlijk trager dan een gestagede batch in één enkele statement mergen.

Interviewvragen

"Schrijf een query om de meest recente order per klant op te halen." Let op: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) met een filter op rn = 1, en idealiter een vermelding van tie-breaking.

"Wat is het verschil tussen RANK(), DENSE_RANK() en ROW_NUMBER()?" Let op: ROW_NUMBER() levert altijd unieke, opeenvolgende nummers op, ook bij gelijkspel; RANK() laat gaten vallen na gelijkspel; DENSE_RANK() niet.

"Hoe zou je een tabel dedupliceren waar hetzelfde record meerdere keren kan voorkomen met licht verschillende waarden?" Let op: het besef dat SELECT DISTINCT hier niet werkt, en een ROW_NUMBER()-gebaseerde aanpak met een duidelijke "welke versie wint"-regel.

"Wat is er mis met deze query?" (toon een fan-out-voorbeeld zoals hierboven) Let op: het herkennen dat de join een waarde op order-niveau vermenigvuldigt over orderregels vóór het sommeren, en een oplossing die eerst naar de juiste granulariteit aggregeert.

"Leg uit wat er gebeurt als je een MERGE-statement twee keer draait met dezelfde input." Let op: het begrip dat een goed geschreven MERGE idempotent is — de tweede run zou een no-op moeten zijn als er niets is veranderd — en het besef dat zonder freshness-guard verouderde herhaalde runs regressies kunnen veroorzaken.

"Wat is de SQL execution order, en waarom maakt het uit?" Let op: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT, en een concreet voorbeeld van waarom een SELECT-alias niet in WHERE gebruikt kan worden.

"Wanneer zou je een CTE gebruiken versus een temp table?" Let op: CTE's voor leesbaarheid en eenvoudige sequencing; temp tables wanneer een duur tussenresultaat gematerialiseerd en meerdere keren hergebruikt moet worden.

Relevante Documentatie

• Snowflake Window Functions — officiële syntaxreferentie, inclusief QUALIFY.
• PostgreSQL Window Functions Tutorial — het ANSI-standaardgedrag dat de meeste engines volgen.
• Databricks SQL Reference — MERGE INTO en window function-syntax op het lakehouse.
• dbt Labs: SQL Style Guide — CTE-naamgeving en structureringsconventies die in de industrie worden gebruikt.

Samenvatting

SQL voor data engineering is een andere vaardigheid dan SQL voor analytics, ook al overlapt de syntax bijna volledig. De patronen die ertoe doen zijn window functions voor deduplicatie en tijdreekslogica, MERGE voor idempotent laden met de juiste freshness-guards, zorgvuldige aandacht voor join-granulariteit om fan-out-bugs te voorkomen, en begrip van query execution order dat verklaart waarom bepaalde dingen wel of niet werken. Niets hiervan vereist exotische syntax — het vereist weten welke van de "voor de hand liggende" manieren om een query te schrijven stilletjes foute getallen oplevert onder realistische omstandigheden zoals retries, te laat binnenkomende data en one-to-many-relaties. Het volgende hoofdstuk bouwt direct voort op deze patronen om te laten zien hoe ze passen in een volledig dataplatform-ontwerp.