10.7.3 – Enrichment Patterns

Module: Section 10 – Kafka Ecosystem & Tools
Section: 10.7 – Joins & Enrichment with ksqlDB
Goal: Master real-world enrichment patterns

Multi-Level Enrichment

-- Level 1: Enrich with user data
CREATE STREAM orders_with_users AS
SELECT o.*, u.username, u.tier
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
EMIT CHANGES;

-- Level 2: Enrich with product data
CREATE STREAM fully_enriched_orders AS
SELECT ou.*, p.product_name, p.category
FROM orders_with_users ou
LEFT JOIN products p ON ou.product_id = p.product_id
EMIT CHANGES;

Conditional Enrichment

SELECT
  o.order_id,
  o.amount,
  CASE
    WHEN u.tier = 'PREMIUM' THEN u.email
    ELSE NULL
  END AS contact_email
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
EMIT CHANGES;

Null Handling

SELECT
  o.order_id,
  COALESCE(u.username, 'Unknown User') AS username,
  COALESCE(u.tier, 'STANDARD') AS tier
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
EMIT CHANGES;

Key Takeaways

1. Chain Joins: Multiple enrichment levels
2. Conditional Logic: CASE statements
3. Null Handling: COALESCE for defaults

What's Next?

Next Module: 10.7.4 – Materialized Views