dltm-markwan · February 1, 2024 10:03
diff --git a/ex1_dim_customers.sql b/ex1_dim_customers.sql
 {{
    config(
        materialized='view'
    )
 }}

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

 select 
    *
 from
    final
diff --git a/ex2_silver_docs.yml b/ex2_silver_docs.yml
 version: 2

 models:
    - name: dim_locations
      description: store locations further classified by country
      columns:
        - name: id
          tests:
            - not_null
    - name: dim_customers
      description: Customer details with email addresses
      columns:
        - name: id
          tests:
            - not_null
        - name: unique_id
          tests:
            - unique
diff --git a/ex3_top_customer_with_negative_spend.sql b/ex3_top_customer_with_negative_spend.sql
 {{
    config(
        error_if = '>10'
        , warn_if = '>0'
    )
 }}

 select 
    *
 from
    {{ ref('top_customers') }}
 where 
    total_spend < 0
diff --git a/ex3_top_customers.sql b/ex3_top_customers.sql
 with sales_items as (
    select * from {{ ref('sales_items') }}
 ),
 sales as (
    select * from {{ ref('sales') }}
 ),
 customers as (
    select * from {{ ref('dim_customers') }}
 ),

 final as (
    select 
        sales_items.store_id,
        sales.unique_customer_id,
        customers.name,
        sum(product_cost) as total_spend
    from
        sales_items
        join sales on sales_items.sale_id = sales.id
        join customers on sales.unique_customer_id = customers.unique_id
    where
        sales.unique_customer_id is not null
    group by sales_items.store_id, sales.unique_customer_id, customers.name
    order by total_spend
    limit 100
 )

 select 
    *
 from 
    final
diff --git a/ex_3dim_customer_snapshot.sql b/ex_3dim_customer_snapshot.sql
 {% snapshot dim_customers_snapshot %}
    {{
        config(
            unique_key='unique_id',
            strategy='check',
            check_cols = 'all'
        )
    }}

    select * from {{ ref('dim_customers') }}
 {% endsnapshot %}
	{{
	config(
	materialized='view'
	)
	}}

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

	select
	*
	from
	final
	version: 2

	models:
	- name: dim_locations
	description: store locations further classified by country
	columns:
	- name: id
	tests:
	- not_null
	- name: dim_customers
	description: Customer details with email addresses
	columns:
	- name: id
	tests:
	- not_null
	- name: unique_id
	tests:
	- unique
	{{
	config(
	error_if = '>10'
	, warn_if = '>0'
	)
	}}

	select
	*
	from
	{{ ref('top_customers') }}
	where
	total_spend < 0
	with sales_items as (
	select * from {{ ref('sales_items') }}
	),
	sales as (
	select * from {{ ref('sales') }}
	),
	customers as (
	select * from {{ ref('dim_customers') }}
	),

	final as (
	select
	sales_items.store_id,
	sales.unique_customer_id,
	customers.name,
	sum(product_cost) as total_spend
	from
	sales_items
	join sales on sales_items.sale_id = sales.id
	join customers on sales.unique_customer_id = customers.unique_id
	where
	sales.unique_customer_id is not null
	group by sales_items.store_id, sales.unique_customer_id, customers.name
	order by total_spend
	limit 100
	)

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

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