Ebook · Hoofdstuk 8 van 10

Performance Tuning en Kostenoptimalisatie

Snelle queries en lage kosten zijn vaak twee kanten van dezelfde medaille. De technieken die echt werken.

Performance is een kostenpost

21 min leestijd Gevorderd

In een cloud DWH zijn snelheid en kosten direct gekoppeld: een trage query die meer compute verbruikt is letterlijk een duurdere query. Dit hoofdstuk gaat over de zes hefbomen die in vrijwel elk modern warehouse werken — onafhankelijk van het platform.

1. Lees de query plan

Tunen zonder query plan is gokken. EXPLAIN (of het visuele equivalent in de UI) laat zien wat de engine echt doet. Wat te zoeken:

-- Snowflake: query profile + statistieken
EXPLAIN USING JSON
SELECT customer_id, SUM(amount)
FROM   fact_sales
WHERE  date_key BETWEEN 20260101 AND 20260331
GROUP  BY customer_id;

-- BigQuery: dry run kost niets en geeft scanned bytes
SELECT customer_id, SUM(amount)
FROM   `proj.retail.fact_sales`
WHERE  date BETWEEN '2026-01-01' AND '2026-03-31'
GROUP  BY customer_id;
-- Klik 'dry run' in console → "This query will process 2.3 GB"

2. Partitioning

Partitioning splitst een tabel fysiek in stukken op basis van een kolom (vaak datum). Queries met een filter op die kolom slaan irrelevante partitions over — partition pruning.

-- BigQuery: time-unit partitioning
CREATE TABLE retail.fact_sales (
  sales_id INT64, date DATE, customer_id STRING, amount NUMERIC
)
PARTITION BY date
OPTIONS (require_partition_filter = TRUE);   -- dwingt filter af!

-- Snowflake: micro-partitions zijn automatisch (op insertvolgorde)
-- Voor expliciete clustering:
ALTER TABLE fact_sales CLUSTER BY (date_key);

-- Redshift: distribution + sort key in provisioned mode
CREATE TABLE fact_sales (...)
DISTKEY (customer_id)
SORTKEY (date);

De gouden regel: partitioneer op de kolom waarop bijna elke query filtert. Voor een DWH is dat 90% van de tijd een datumkolom.

Niet over-partitioneren

Eén partition per dag bij 5 jaar historie = 1.825 partitions. Prima. Eén partition per minuut = 2.6M partitions. Catastrofe — metadata-overhead overstijgt het voordeel. Houd partitions tussen ~50 MB en ~10 GB.

3. Clustering / sort keys

Waar partitioning grof is (per dag), is clustering fijnzinnig. Het sorteert rijen binnen partitions op één of meer kolommen, zodat lookups op die kolommen sneller worden.

-- BigQuery: clustering tot 4 kolommen
CREATE TABLE retail.fact_sales (...)
PARTITION BY date
CLUSTER BY customer_id, product_id;

-- Snowflake: clustering key
ALTER TABLE fact_sales CLUSTER BY (date_key, customer_id);

-- Check Snowflake clustering depth (lager = beter)
SELECT SYSTEM$CLUSTERING_INFORMATION('fact_sales', '(date_key, customer_id)');

Clustering werkt het best op kolommen die je vaak in WHERE-filters of JOIN-condities gebruikt, en die niet super-laag-cardinaliteit hebben (een bool-kolom is geen goede cluster key).

4. Materialized views

Als dezelfde aggregatie tien keer per minuut gequeryed wordt, doe je het werk tien keer voor niets. Een materialized view bewaart het resultaat en updatet automatisch (of bijna).

-- BigQuery: materialized view met smart refresh
CREATE MATERIALIZED VIEW retail.daily_revenue AS
SELECT date, SUM(amount) AS revenue, COUNT(*) AS orders
FROM   retail.fact_sales
GROUP  BY date;

-- Snowflake: materialized view (Enterprise edition)
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_key, SUM(amount) AS revenue, COUNT(*) AS orders
FROM   fact_sales
GROUP  BY date_key;

-- Redshift: materialized view, manual refresh
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date, SUM(amount), COUNT(*)
FROM   fact_sales
GROUP  BY date;

REFRESH MATERIALIZED VIEW daily_revenue;

Maar pas op: materialized views zijn niet gratis. Elke base-table-update triggert (geheel of incrementeel) een refresh. Op snel veranderende tabellen kan de refresh-kost het query-voordeel opeten.

5. Caching

Elk modern DWH heeft meerdere cache-lagen:

Concrete impact: zorg dat dashboards identieke queries genereren (geen NOW()-injecties), zodat de result cache hit. BI-tools kunnen vaak parameters cachen — verifieer.

6. Concurrency tuning

Te weinig concurrency: gebruikers wachten op elkaar. Te veel: alles draait, niets is snel. De oplossing verschilt per platform:

-- Snowflake multi-cluster bij concurrency-pieken
CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 5            -- schaalt automatisch tot 5 clusters
  SCALING_POLICY = 'STANDARD';      -- 'ECONOMY' = zuiniger maar langzamer schalen

SQL-patterns die je moet vermijden

Voor en na

-- ❌ Trager
SELECT *
FROM   fact_sales
WHERE  EXTRACT(YEAR FROM date) = 2026
   AND UPPER(country) = 'NL';

-- ✅ Sneller
SELECT sales_id, customer_id, amount
FROM   fact_sales
WHERE  date >= '2026-01-01' AND date < '2027-01-01'
   AND country = 'NL';

Kostencontrole

De technische tuning bovenstaand levert direct besparingen op. Maar ook:

-- Snowflake: resource monitor
CREATE RESOURCE MONITOR rm_etl
  WITH CREDIT_QUOTA = 1000
  TRIGGERS
    ON 75 PERCENT DO NOTIFY
    ON 90 PERCENT DO SUSPEND
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE etl_wh SET RESOURCE_MONITOR = rm_etl;

Specifieke patterns voor zware joins

Een join tussen een fact-tabel met miljarden rijen en een dimensie met miljoenen rijen is een klassieke performance-killer. Drie patronen die dit oplossen:

Workload-isolatie

Eén warehouse voor alles is een klassieke valkuil. Een zware dbt-build zet een dashboard in de wachtrij; een ad-hoc data scientist trekt een hele cluster naar zich toe. Splits werklasten:

Op BigQuery los je dit op met aparte slot reservations; op Synapse/Fabric met aparte capacities. De extra kosten zijn klein vergeleken met de voorspelbare performance.

De 80/20-regel van performance

In de praktijk komt 80% van de winst uit:

  1. Partitionering met enforced filter (require_partition_filter = TRUE).
  2. Geen SELECT * — alleen kolommen die je echt gebruikt.
  3. Query plans bekijken voor de top-10 zwaarste queries.
  4. Right-sized warehouses / slot-reservations.
  5. Auto-suspend op 60-300s.

Materialized views, custom clustering keys en advanced WLM-tuning zijn de overige 20%, en kosten meer onderhoud. Begin bij de basics.

Compaction en file-size tuning op lakehouses

Op Delta Lake, Iceberg en Hudi (Databricks, Microsoft Fabric, Snowflake Iceberg) is een aparte performance-overweging: file size. Streaming ingestion en frequente kleine commits creëren duizenden kleine bestanden, en kleine bestanden zijn ramp voor scan-performance.

Benchmarken in productie

Tunen zonder meten is gokken. Monitor minimaal:

Key takeaways

  • Performance en kosten zijn in cloud-DWH twee zijden van dezelfde medaille.
  • Partitioneren op datum + verbieden van filter-loze queries is de grootste winst.
  • Clustering is het tweede niveau — fijnzinniger dan partitioning.
  • Materialized views alleen op stabiele aggregaties.
  • Vermijd SELECT *, niet-sargable predicates en hidden cartesian joins.
  • Auto-suspend, resource monitors en right-sizing besparen direct geld.
  • Splits werklasten in eigen warehouses om elkaar niet te storen.

Veelgestelde vragen

Hoe optimaliseer je query performance?

Begin bij partitioning op datum met require_partition_filter aan. Voeg clustering toe op kolommen in joins en filters. Vermijd SELECT * en niet-sargable predicates. Lees query plans om de top-10 zwaarste queries gericht te tunen.

Wat is het verschil tussen partitioning en clustering?

Partitioning splitst de tabel fysiek (vaak per datum) zodat partitions worden overgeslagen bij filter. Clustering sorteert rijen binnen partitions op extra kolommen voor snellere lookups. Een typische combinatie: partitioneer op datum, cluster op customer_id.

Wanneer gebruik je een materialized view?

Wanneer een aggregatie veel vaker gequeryed wordt dan dat de base table wijzigt. Bij snel wijzigende tabellen kan de refresh-kost het query-voordeel opeten — meet eerst, materialiseer dan.

Hoe houd je de kosten onder controle?

Auto-suspend op 60-300 sec, resource monitors met budget-cap, right-sizing, partition filters afdwingen, storage tiering voor oude data en temp-tabellen opruimen. Monitor de top-10 duurste queries dagelijks.

Wat zijn niet-sargable predicates?

Predicates die geen index of partition pruning kunnen gebruiken. WHERE EXTRACT(YEAR FROM date) = 2026 is niet-sargable; WHERE date >= '2026-01-01' AND date < '2027-01-01' wel. Functies of casts op filter-kolommen breken meestal performance.

Hoe lees je een query execution plan?

Zoek full table scans waar partitions zouden helpen, cartesian joins, spills naar disk en data skew. Snowflake toont pruning ratio, BigQuery scanned bytes. Gebruik de visuele query profilers van beide UI's.

Hoofdstuk 7 Inhoudsopgave Hoofdstuk 9: Monitoring