Stap 2:SQL schrijven

Inleiding

In DataOwn gebruik je de SQL-editor om queries te schrijven waarmee je je data verkent en transformeert. De SQL-editor biedt een krachtige omgeving waar je queries direct kunt exporteren, maar ook testen op je ingeladen data voordat je ze promoveert naar herbruikbare datamodellen. Deze handleiding geeft wat tips maar gaat ervan uit dat je SQL machtig bent. Anders zijn er online en offline genoeg (gratis) cursussen. Het gebruikte SQL-dialect is PostgreSQL.

De SQL-editor is je werkplek voor verkennen en het beantwoorden van ad-hoc vragen. Je kunt de gegevens meteen exporteren naar CSV of Excel.

Ook is het de plek om te experimenteren en valideren voor nieuwe modellen. Als je tevreden bent met je query, kun je deze promoveren naar een DBT-model dat beschikbaar komt in Lightdash voor het maken van visualisaties en dashboards.

De SQL-editor interface

De SQL-editor is onderverdeeld in verschillende secties die je helpen bij het schrijven en testen van queries:

1. Schema browser - Links zie je alle beschikbare tabellen en kolommen
2. Code editor - In het midden schrijf je je SQL met syntax highlighting
3. Query uitvoeren - Bovenaan kun je je query uitvoeren
4. Resultaten - Onderaan zie je de output van je query in tabelvorm
5. Opgeslagen queries - Rechtsboven vind je je opgeslagen queries
6. Promote knop - Rechtsonder kun je je query promoveren naar een DBT-model

Schema browser

In het menu links zie je alle beschikbare tabellen en views, en de bijbehorende kolommen. De views onder analytics_{organisatienaam} zijn het resultaat van de DBT-modellen maar kunnen dus ook verder worden gebruikt. De tabellen onder raw_{organisatienaam} zijn de informatie zoals die uit de bron is ingeladen.

Door op het pijltje naast de tabel- of view-naam te klikken kun je de kolommen en het datatype van die kolommen zien. Door op het menu (drie puntjes) te klikken kun je een "select"-query van die tabel toevoegen aan de editor.

Code editor en query uitvoeren

Hier kun je SQL schrijven, waarbij je geholpen wordt door syntax highlighting. Zo worden keywords zoals SELECT en FROM standaard blauw. Het gekozen SQL-dialect is PostgreSQL. Het is enkel mogelijk om gegevens te selecteren en niet om tabellen of views te wijzigen vanaf hier. Eventueel kun je aan meerdere queries tegelijk werken door boven de knoppen run, save en promote meerdere tabbladen toe te voegen. Het is nog niet mogelijk om tijdelijke tabellen te gebruiken.

Queries kunnen worden uitgevoerd door op de run-knop te klikken of door Ctrl+Enter. Het is verstandig om queries tijdens het ontwikkelen zoveel mogelijk te eindigen met "LIMIT 100", zodat enkel de bovenste 100 rijen worden binnengehaald. Begin je queries daarnaast simpel en bouw stapsgewijs de complexiteit op.

Resultaten

Na uitvoeren van de query komen hier de resultaten - of de foutmelding. Hier kun je beoordelen of de query doet wat hij moet doen, of wat de inhoud van de tabellen is.

Voor het beantwoorden van ad-hoc vragen zijn soms de resultaten van een SQL-query al genoeg. In dat geval kun je met de knop "Copy CSV" de resultaten kopiëren. Als je ze in een kladblok-bestand opslaat als ".csv" heb je een CSV-bestand. Plak je deze in een spreadsheetprogramma dan kun je deze laten omzetten naar geschikt formaat door te kiezen voor de komma als scheidingsteken. LibreOffice Calc vraagt dit bij plakken direct, bij MS Excel ga je naar "Gegevens" en dan "Tekst naar kolommen".

Opgeslagen queries

Een van de belangrijkste features van de SQL-editor is het kunnen opslaan van queries. Dit maakt je werk herbruikbaar en deelbaar met collega's. Ook biedt dit documentatie omdat je een naam en beschrijving kan geven.

Als je een query wilt opslaan kies je save bovenin. De reeds opgeslagen queries kun je terugvinden in de linkerbalk, als je "Saved queries" kiest in plaats van "Tables". Naast de naam en de beschrijving zie je ook degene die het laatst heeft opgeslagen en wanneer de query voor het laatst gedraaid is. Net als bij de tabellen kun je ook hier je queries doorzoeken. Load vervangt het huidige scherm door de opgeslagen query. Via het menu (puntjes) kun je een opgeslagen query verwijderen.

Je kunt de linkerbalk breder maken om te zorgen dat lange beschrijvingen niet te veel regels innemen.

Promoveren naar DBT-modellen

Als je SQL-query goed werkt en je wilt er grafieken en dashboards mee gaan maken, kun je deze promoveren naar een DBT-model. Dit is de cruciale stap die je query omzet in een herbruikbaar datamodel dat beschikbaar komt in Lightdash.

Dit doe je door op "Promote" te klikken. Dan kies je een modelnaam en dan "Create model". Daarna ga je door naar de pagina Models om je model verder vorm te geven: dit wordt uitgelegd in Stap 3: Modellen maken, waar je leert hoe je dit model verder configureert met metrics, dimensies en joins.

Controleer of je SQL correct werkt

Voordat je een query opslaat of promoveert naar een model, is het belangrijk om te valideren dat deze correct werkt:

• Run je query - Voer deze uit en controleer of de resultaten kloppen
• Check datatypes - Zorg dat kolommen het juiste datatype hebben
• Verifieer aantallen - Controleer of het aantal rijen klopt met je verwachting
• Test edge cases - Kijk hoe je query omgaat met NULL waarden of lege resultaten
• Gebruik LIMIT tijdens ontwikkeling - Voeg 'LIMIT 100' toe voor snellere testresultaten

Best practices

Gebruik duidelijke namen

Kies herkenbare aliassen (bijv. 'order_datum' in plaats van 'dt')

Voeg comments toe

Leg complexe logica uit met SQL-comments (-- of /* */). Dit geldt ook voor de (functionele) redenen achter keuzes.

Test met LIMIT

Gebruik LIMIT tijdens ontwikkeling voor snellere resultaten en minder systeembelasting.

Bouw incrementeel op

Start met basis queries via een tabel en voeg stap voor stap complexiteit toe.

Gebruik CTEs voor leesbaarheid

Break complexe queries op in logische stappen. Een CTE schrijf je met een WITH cte_naam AS ()-statement en kun je vanaf dan aanroepen alsof het een tabel is.

Format je SQL

Gebruik consistente indentatie en regelafbrekingen. Meest gebruikt is een nieuwe regel voor elk nieuw veld, nieuwe join of nieuw criterium. De SELECT, FROM en WHERE zet je volledig links, de regels ertussen spring je een tab in.

Sla tussenstappen op

Bewaar je query regelmatig tijdens het ontwikkelen. Als je naar een ander scherm gaat ben je jouw resultaten anders kwijt.

Neem een relevante steekproef

Als je geen ORDER BY-statement toevoegt worden de resultaten op een niet voorspelbare maar ook niet willekeurige wijze gesorteerd. Dit betekent dat het regelmatig voorkomt dat de eerste regels een bepaald veld gevuld hebben, maar de regels daaronder niet. Test bijvoorbeeld met alle gevallen waar het ID eindigt op 111, of op een andere manier zodat je wel een willekeurige steekproef krijgt.

Veelvoorkomende problemen

Query duurt te lang

Voeg filters toe om minder data op te halen, gebruik LIMIT tijdens testen, of check of je JOIN condities efficiënt zijn.

Syntax errors

Controleer op typfouten, ontbrekende komma's, of niet-afsluitende haakjes. De error message geeft vaak de regel aan waar het fout gaat.

Verkeerde resultaten

Verifieer je JOIN-condities en WHERE-clauses. Test elke stap apart door CTEs te gebruiken.

NULL-waarden

Gebruik COALESCE() om NULL-waarden te vervangen, of CASE-statements om ze op te vangen.

Dubbele rijen

Check of je JOIN duplicaten creëert, vaak moet je in een van de tabellen verder filteren. Probeer de bron-oorzaak te achterhalen, en niet zomaar een DISTINCT of GROUP BY toe te passen.