Understanding Modern Data Engineering: How Data Pipelines Power Enterprise Intelligence

-- dbt Analytical Model: Transforming Raw Customer Orders into Executive Metric Table
WITH raw_orders AS (
    SELECT id AS order_id, customer_id, total_amount, status, created_at
    FROM {{ source('production', 'orders') }}
),
customer_aggregates AS (
    SELECT 
        customer_id,
        COUNT(order_id) AS total_orders,
        SUM(CASE WHEN status = 'COMPLETED' THEN total_amount ELSE 0 END) AS lifetime_revenue,
        MIN(created_at) AS first_order_date,
        MAX(created_at) AS latest_order_date
    FROM raw_orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    total_orders,
    lifetime_revenue,
    CASE 
        WHEN lifetime_revenue > 10000 THEN 'VIP_ENTERPRISE'
        WHEN lifetime_revenue > 2500 THEN 'MID_MARKET'
        ELSE 'STANDARD'
    END AS customer_tier
FROM customer_aggregates;