Databricks

{{ config(
    materialized='table',
    file_format='delta',
    liquid_clustered_by=['customer_id', 'order_date'],
    tblproperties={
      'delta.autoOptimize.optimizeWrite': 'true',
      'delta.autoOptimize.autoCompact': 'true'
    }
) }}

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

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    merge_update_columns=['status', 'updated_at'],
    file_format='delta'
) }}

select
    event_id,
    user_id,
    status,
    updated_at
from {{ source('raw', 'events') }}

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

{{ config(
    materialized='streaming_table',
    file_format='delta'
) }}

-- Streaming from Auto Loader
select
    event_id,
    event_timestamp,
    event_data
from stream({{ source('streaming', 'raw_events') }})

-- Source definition with catalog
{{ config(
    database='prod_catalog',  -- Unity Catalog
    schema='analytics'
) }}

-- Or use generate_database_name macro
-- profiles.yml: database = prod_catalog
-- Resulting table: prod_catalog.analytics.dim_customers

select * from {{ ref('stg_customers') }}