Proyecto dbt en acción:
Del CSV al Data Mart

with source as (
    select * from {{ ref('raw_customers') }}
),

renamed as (
    select
        customer_id,
        first_name,
        last_name,
        first_name || ' ' || last_name as full_name,
        lower(email) as email,
        country,
        city,
        cast(signup_date as date) as signup_date,
        upper(segment) as segment,
        current_timestamp as _loaded_at
    from source
)

select * from renamed

with source as (
    select * from {{ ref('raw_products') }}
),

renamed as (
    select
        product_id,
        product_name,
        category,
        subcategory,
        price,
        cost,
        price - cost as gross_margin,
        round((price - cost) / price * 100, 2) as margin_pct,
        supplier,
        current_timestamp as _loaded_at
    from source
)

select * from renamed

with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('stg_customers') }}
),

products as (
    select * from {{ ref('stg_products') }}
),

enriched as (
    select
        o.order_id,
        o.order_date,
        date_trunc('month', o.order_date) as order_month,
        date_trunc('quarter', o.order_date) as order_quarter,
        extract(year from o.order_date) as order_year,

        -- Customer info
        o.customer_id,
        c.full_name as customer_name,
        c.segment as customer_segment,
        c.country as customer_country,
        c.city as customer_city,

        -- Product info
        o.product_id,
        p.product_name,
        p.category as product_category,
        p.subcategory as product_subcategory,

        -- Order details
        o.quantity,
        o.discount_rate,
        o.status,
        o.channel,

        -- Financial calculations
        p.price as unit_price,
        p.cost as unit_cost,
        p.price * o.quantity as gross_revenue,
        p.price * o.quantity * (1 - o.discount_rate) as net_revenue,
        p.cost * o.quantity as total_cost,
        p.price * o.quantity * (1 - o.discount_rate) - p.cost * o.quantity as gross_profit,

        -- Margin
        case
            when p.price * o.quantity * (1 - o.discount_rate) > 0
            then round(
                (p.price * o.quantity * (1 - o.discount_rate) - p.cost * o.quantity)
                / (p.price * o.quantity * (1 - o.discount_rate)) * 100, 2
            )
            else 0
        end as margin_pct

    from orders o
    left join customers c on o.customer_id = c.customer_id
    left join products p on o.product_id = p.product_id
)

select * from enriched

with enriched_orders as (
    select * from {{ ref('int_orders_enriched') }}
),

final as (
    select
        order_id,
        order_date,
        order_month,
        order_quarter,
        order_year,

        customer_id,
        customer_name,
        customer_segment,
        customer_country,

        product_id,
        product_name,
        product_category,

        quantity,
        discount_rate,
        status,
        channel,

        unit_price,
        unit_cost,
        gross_revenue,
        net_revenue,
        total_cost,
        gross_profit,
        margin_pct,

        -- Flags
        case when status = 'completed' then true else false end as is_completed,
        case when discount_rate > 0 then true else false end as has_discount,
        case when channel = 'online' then true else false end as is_online

    from enriched_orders
)

select * from final

with customers as (
    select * from {{ ref('stg_customers') }}
),

orders as (
    select
        customer_id,
        count(*) as total_orders,
        count(case when status = 'completed' then 1 end) as completed_orders,
        sum(case when status = 'completed' then net_revenue else 0 end) as total_revenue,
        sum(case when status = 'completed' then gross_profit else 0 end) as total_profit,
        min(order_date) as first_order_date,
        max(order_date) as last_order_date,
        avg(case when status = 'completed' then net_revenue end) as avg_order_value
    from {{ ref('int_orders_enriched') }}
    group by 1
),

final as (
    select
        c.customer_id,
        c.full_name,
        c.email,
        c.country,
        c.city,
        c.segment,
        c.signup_date,

        coalesce(o.total_orders, 0) as total_orders,
        coalesce(o.completed_orders, 0) as completed_orders,
        coalesce(o.total_revenue, 0) as lifetime_revenue,
        coalesce(o.total_profit, 0) as lifetime_profit,
        o.first_order_date,
        o.last_order_date,
        coalesce(o.avg_order_value, 0) as avg_order_value,

        case
            when coalesce(o.total_revenue, 0) >= 2000 then 'VIP'
            when coalesce(o.total_revenue, 0) >= 1000 then 'High Value'
            when coalesce(o.total_revenue, 0) >= 500 then 'Medium Value'
            else 'Low Value'
        end as value_tier

    from customers c
    left join orders o on c.customer_id = o.customer_id
)

select * from final