PostgreSQL

{{ config(
    materialized='table',
    post_hook=[
      "CREATE INDEX IF NOT EXISTS idx_{{ this.name }}_customer_id ON {{ this }} (customer_id)",
      "CREATE INDEX IF NOT EXISTS idx_{{ this.name }}_order_date ON {{ this }} (order_date)"
    ]
) }}

select
    order_id,
    customer_id,
    order_date,
    total_amount
from {{ source('raw', 'orders') }}

-- First, create the partitioned table structure
-- This is typically done in a migration or manually

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='append'
) }}

-- dbt will insert into the partitioned table
-- PostgreSQL automatically routes to correct partition
select
    event_id,
    event_timestamp,
    user_id,
    event_data
from {{ source('raw', 'events') }}

{% if is_incremental() %}
where event_timestamp > (select max(event_timestamp) from {{ this }})

{{ config(
    materialized='materialized_view'
) }}

-- Requires unique index for CONCURRENTLY refresh
-- Add in post_hook: CREATE UNIQUE INDEX ON {{ this }} (id)

select
    date_trunc('day', created_at) as day,
    product_id,
    sum(quantity) as total_quantity,
    sum(amount) as total_revenue
from {{ ref('fct_orders') }}
group by 1, 2

-- Refresh with: REFRESH MATERIALIZED VIEW CONCURRENTLY {{ this }}

select
    customer_id,
    order_id,
    order_date,
    total_amount,
    
    -- Window functions
    row_number() over (partition by customer_id order by order_date) as order_sequence,
    sum(total_amount) over (partition by customer_id) as customer_lifetime_value,
    lag(order_date) over (partition by customer_id order by order_date) as previous_order_date,
    
    -- JSON aggregation
    jsonb_agg(
        jsonb_build_object('product', product_name, 'qty', quantity)
    ) as order_items
    
from {{ ref('stg_orders') }}
left join {{ ref('stg_order_items') }} using (order_id)
group by 1, 2, 3, 4