dbt_grow_exercises_w_bonus.sql

--  1A) dim_customers SQL

select 
    store_id || "-" || cast(id as string) as unique_id
    , id
    , store_id
    , name
    , email
from {{ source('apjuice', 'users') }}  

--  1B) change config

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


--  2A) test and docs

    - name: dim_customers
      description: customer details
      columns:
        - name: unique_id
          description: unique customer_id and store_id combination
          tests:
            - not_null
            - unique

--  2B) singular test

{{
    config(
        error_if = '>10'
        , warn_if = '>0'
    )
}}

select *
from {{ ref('sales_items') }}
where product_cost < 0



--  3A) top_customers

select 
    s.store_id
    , ss.unique_customer_id
    , c.name
    , sum(product_cost) as total_spend

from {{ ref('sales_items') }} as s 
join {{ ref('sales') }} as ss on s.sale_id = ss.id
join {{ ref('dim_customers') }} as c on ss.unique_customer_id = c.unique_id

where 
    ss.unique_customer_id is not null
group by s.store_id, ss.unique_customer_id, c.name
order by total_spend desc
limit 100


--  3B) dim_customer_snapshot

{% snapshot dim_customers_snapshot %}
    {{
        config(
            unique_key='unique_id',
            strategy='check',
            check_cols='all'
        )
    }}

    select * from {{ ref('dim_customers') }}
 
 {% endsnapshot %}