# models/marts/fct_orders.yml
version: 2

models:
  - name: fct_orders
    description: >
      Fact table containing all order transactions. This is the central
      fact table for the order analytics domain.
      
      **Grain**: One row per order
      
      **Key Business Questions**:
      - What is our total revenue?
      - How many orders do we process?
      - What is the average order value?
      
      **Data Source**: Shopify via Fivetran
      **Refresh Frequency**: Hourly
      **Owner**: Data Engineering Team
    
    config:
      tags: ['finance', 'core', 'production']
      meta:
        owner: data-engineering
        team: analytics
        cost_center: finance
    
    columns:
      - name: order_id
        description: "Unique identifier for each order"
        data_tests:
          - unique
          - not_null
        meta:
          system: shopify
          pii: false
      
      - name: customer_id
        description: "Foreign key to dim_customers"
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
        meta:
          pii: false
      
      - name: order_date
        description: "Date when the order was placed"
        data_tests:
          - not_null
        meta:
          format: YYYY-MM-DD

-- models/staging/stg_orders.sql
{{
    config(
        materialized='view',
        schema='staging'
    )
}}

with source as (
    select * from {{ source('shopify', 'orders') }}
),

renamed as (
    select
        -- Primary keys
        id as order_id,
        customer_id,
        
        -- Dimensions
        status as order_status,
        financial_status,
        fulfillment_status,
        
        -- Measures
        {{ dbt_utils.money_snapshot(amount) }} as order_amount,
        {{ dbt_utils.money_snapshot(total_discounts) }} as discount_amount,
        {{ dbt_utils.money_snapshot(total_tax) }} as tax_amount,
        
        -- Timestamps
        created_at as order_created_at,
        updated_at as order_updated_at,
        
        -- Metadata
        _fivetran_synced as synced_at
    from source
)

select * from renamed

-- models/marts/fct_orders.sql
{{
    config(
        materialized='incremental',
        unique_key='order_id',
        incremental_strategy='merge',
        partition_by={
            "field": "order_date",
            "data_type": "date"
        },
        cluster_by=['customer_id', 'order_status'],
        tags=['finance', 'core', 'production']
    )
}}

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

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

order_items as (
    select * from {{ ref('stg_order_items') }}
),

final as (
    select
        -- Primary key
        orders.order_id,
        
        -- Foreign keys
        orders.customer_id,
        
        -- Dimensions
        customers.customer_name,
        customers.segment as customer_segment,
        orders.order_status,
        orders.order_date,
        
        -- Measures
        orders.order_amount,
        orders.discount_amount,
        orders.tax_amount,
        count(order_items.item_id) as item_count,
        
        -- Timestamps
        orders.order_created_at,
        orders.order_updated_at,
        current_timestamp() as dbt_updated_at
    from orders
    left join customers on orders.customer_id = customers.customer_id
    left join order_items on orders.order_id = order_items.order_id
    group by 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11
)

select * from final

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

# models/marts/fct_orders_tests.yml
version: 2

models:
  - name: fct_orders
    tests:
      - dbt_utils.unique_combination_of_columns:
          combination_of_columns:
            - order_id
      
      - dbt_utils.accepted_range:
          min_value: 0
          max_value: 1000000
          column_name: order_amount
    
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null
      
      - name: customer_id
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      
      - name: order_amount
        data_tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 1000000
      
      - name: order_date
        data_tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "order_date <= current_date()"

-- macros/generate_generic_test.sql
{% macro generate_generic_test(test_name, model, column_name, config) %}
    {% set test_sql %}
        select
            '{{ test_name }}' as test_name,
            '{{ model }}' as model_name,
            '{{ column_name }}' as column_name,
            count(*) as failures
        from {{ model }}
        where {{ column_name }} is null
    {% endset %}
    
    {% set result = run_query(test_sql) %}
    
    {{ return(result) }}
{% endmacro %}