BigQuery

{{ config(
    materialized='table',
    partition_by={
      "field": "event_date",
      "data_type": "date",
      "granularity": "day"
    },
    cluster_by=["user_id", "event_type"],
    require_partition_filter=true
) }}

select
    date(event_timestamp) as event_date,
    user_id,
    event_type,
    event_data
from {{ source('raw', 'events') }}

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    partition_by={"field": "event_date", "data_type": "date"}
) }}

select
    event_id,
    event_date,
    user_id,
    amount
from {{ source('raw', 'transactions') }}

{% if is_incremental() %}
where event_date >= date_sub(current_date(), interval 3 day)
  and event_date > (select max(event_date) from {{ this }})
{% endif %}

{{ config(
    materialized='materialized_view',
    enable_refresh=true,
    refresh_interval_minutes=30
) }}

select
    date_trunc(event_date, month) as month,
    product_category,
    sum(revenue) as total_revenue,
    count(distinct user_id) as unique_users
from {{ ref('fct_orders') }}
group by 1, 2

-- Flatten nested arrays
select
    order_id,
    customer_id,
    item.product_id,
    item.quantity,
    item.unit_price
from {{ source('raw', 'orders') }},
unnest(line_items) as item

-- Keep nested structure for analytics
select
    user_id,
    array_agg(struct(
        event_type,
        event_timestamp,
        event_data
    )) as user_events
from {{ ref('stg_events') }}
group by user_id