Create new Models

1. Create a new SQL file, models/stg_customers.sql

select
    id as customer_id,
    first_name,
    last_name

from raw.jaffle_shop.customers

2. Create a another SQL file, models/stg_orders.sql

select
    id as order_id,
    user_id as customer_id,
    order_date,
    status

from raw.jaffle_shop.orders

3. Create a another SQL file customers.sql in models folder with the below SQL

with customers as (

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

),

orders as (

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

),

customer_orders as (

    select
        customer_id,

        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from orders

    group by 1

),

final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.first_order_date,
        customer_orders.most_recent_order_date,
        coalesce(customer_orders.number_of_orders, 0) as number_of_orders

    from customers

    left join customer_orders using (customer_id)

)

select * from final

4. Open the terminal at the bottom and run the command dbt run. It will create tables in Snowflake. Go to Snowflake and check whether the tables were created in Analytics database.