Datawarehouse Opzetten

>CREATE TABLE IF NOT EXISTS bronze.orders_raw (
  order_id        STRING,
  customer_id     STRING,
  order_date      STRING,          -- ruwe data: altijd STRING in bronze
  amount          STRING,
  status          STRING,
  _ingested_at    TIMESTAMP DEFAULT current_timestamp(),
  _source_file    STRING,
  _batch_id       STRING
)
USING DELTA
PARTITIONED BY (date_trunc('day', _ingested_at))
LOCATION 'abfss://bronze@storage.dfs.core.windows.net/orders/';

>INSERT INTO silver.orders
SELECT
  order_id,
  customer_id,
  CAST(order_date AS DATE)           AS order_date,
  CAST(amount AS DECIMAL(18,2))       AS amount,
  LOWER(status)                        AS status,
  _ingested_at,
  current_timestamp()                  AS _transformed_at
FROM bronze.orders_raw
WHERE
  order_id IS NOT NULL               -- validatie
  AND amount ~ '^[0-9]+(\.[0-9]+)?$'  -- numeriek check
  AND _ingested_at > (
    SELECT MAX(_ingested_at) FROM silver.orders
  );                                   -- incrementeel laden

>CREATE TABLE dim_customer (
  customer_sk     INTEGER         NOT NULL,   -- surrogate key: integer
  customer_id     VARCHAR(50)     NOT NULL,   -- business key: string
  customer_name   VARCHAR(200)    NOT NULL,
  email           VARCHAR(254),               -- max email lengte
  country_code    CHAR(2),                    -- ISO 3166-1: altijd 2 tekens
  annual_revenue  DECIMAL(18, 2),             -- geld: nooit FLOAT
  customer_tier   VARCHAR(20)
                  CHECK (customer_tier IN ('bronze', 'silver', 'gold')),
  is_active       BOOLEAN         DEFAULT TRUE,
  created_at      TIMESTAMP       NOT NULL,
  updated_at      TIMESTAMP,
  valid_from      DATE            NOT NULL,   -- SCD2
  valid_to        DATE,                        -- NULL = huidig actief record

  CONSTRAINT pk_dim_customer PRIMARY KEY (customer_sk)
);

>CREATE TABLE fct_orders (
  -- Surrogate keys (foreign keys naar dimensies)
  date_sk         INTEGER        NOT NULL REFERENCES dim_date(date_sk),
  customer_sk     INTEGER        NOT NULL REFERENCES dim_customer(customer_sk),
  product_sk      INTEGER        NOT NULL REFERENCES dim_product(product_sk),
  channel_sk      INTEGER        NOT NULL REFERENCES dim_channel(channel_sk),

  -- Business key (voor traceerbaarheid)
  order_id        VARCHAR(50)    NOT NULL,

  -- Feiten (meetbare waarden)
  quantity        INTEGER        NOT NULL,
  unit_price      DECIMAL(18,2)  NOT NULL,
  discount_amount DECIMAL(18,2)  DEFAULT 0,
  revenue         DECIMAL(18,2)  NOT NULL,
  cost            DECIMAL(18,2),

  CONSTRAINT pk_fct_orders PRIMARY KEY (order_id)
);

>-- Partitioneer feitentabellen op datum
CREATE TABLE fct_orders
USING DELTA
PARTITIONED BY (order_year INT, order_month INT)
AS SELECT
  *,
  YEAR(order_date)  AS order_year,
  MONTH(order_date) AS order_month
FROM silver.orders;

-- Z-ORDER clustering voor snellere point queries
OPTIMIZE fct_orders ZORDER BY (customer_id, product_id);

-- Vacuum (verwijder oude Delta bestanden)
VACUUM fct_orders RETAIN 168 HOURS;  -- 7 dagen history

>-- Clustering key instellen
ALTER TABLE fct_orders
CLUSTER BY (order_date, customer_id);

-- Clustering status controleren
SELECT SYSTEM$CLUSTERING_INFORMATION('fct_orders');

-- Handmatige recluster (bij grote wijzigingen)
ALTER TABLE fct_orders RECLUSTER;

>-- Slecht: subquery die per rij wordt uitgevoerd
SELECT
  customer_id,
  (SELECT COUNT(*) FROM fct_orders o2
   WHERE o2.customer_id = o1.customer_id) AS order_count
FROM dim_customer o1;

-- Goed: window function of join met aggregatie
SELECT
  c.customer_id,
  COALESCE(o.order_count, 0) AS order_count
FROM dim_customer c
LEFT JOIN (
  SELECT customer_id, COUNT(*) AS order_count
  FROM fct_orders
  GROUP BY customer_id
) o ON c.customer_id = o.customer_id;

-- Window function: running total per klant
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM fct_orders;

>-- Azure Synapse: statistieken handmatig updaten
UPDATE STATISTICS fct_orders (order_date) WITH FULLSCAN;
UPDATE STATISTICS fct_orders (customer_id) WITH SAMPLE 30 PERCENT;

-- PostgreSQL: automatisch (maar handmatig forceren na bulk load)
ANALYZE fct_orders;
VACUUM ANALYZE fct_orders;

>-- 1. Volledigheid: controleer verwacht aantal rijen
SELECT
  DATE_TRUNC('day', order_date) AS dag,
  COUNT(*)                       AS aantal_orders,
  SUM(amount)                    AS totaal_omzet
FROM fct_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY 1
ORDER BY 1 DESC;

-- 2. Uniciteit: geen dubbele primaire sleutels
SELECT order_id, COUNT(*) AS duplicaten
FROM fct_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- 3. Referentiële integriteit
SELECT COUNT(*) AS orphaned_orders
FROM fct_orders o
LEFT JOIN dim_customer c ON o.customer_sk = c.customer_sk
WHERE c.customer_sk IS NULL;

-- 4. Freshness: data niet ouder dan 26 uur
SELECT
  MAX(_ingested_at)                        AS laatste_ingest,
  DATEDIFF('hour', MAX(_ingested_at),
            CURRENT_TIMESTAMP())             AS uren_geleden,
  CASE WHEN
    DATEDIFF('hour', MAX(_ingested_at),
              CURRENT_TIMESTAMP()) > 26
  THEN 'ALERT: data te oud'
  ELSE 'OK'
  END AS status
FROM fct_orders;