Exersează construirea unui proiect dbt de la zero. Aplică-ți abilitățile pentru a încărca diferite tipuri de date în proiectul dbt și pentru a configura o varietate de modele de staging. Acest capitol se concentrează pe etapele E și L din procesul ELT.

Introducere în studiul de caz dbt

Verifică instalarea dbt

Inițializează proiectul dbt

Configurarea proiectului dbt și încărcarea datelor

Încarcă centrul de distribuție ca fișier seed dbt

Creează modelul de staging pentru centrele de distribuție

Încarcă și creează modelul de utilizatori prin dbt source

Mai multă practică cu sursele și modelele de staging dbt

Depanează erorile frecvente

Configurarea dbt

Aprofundează modelarea datelor în dbt. Construiește pipeline-ul de date de la modelele preliminare de staging până la modelele finale de data mart, necesare pentru a răspunde unor nevoi critice de business. Pe parcurs, vei dobândi experiență în crearea testelor de date pentru a proteja calitatea acestora.

Crearea descrierilor și testelor dbt

Adaugă descrieri de coloane în dbt

Adaugă teste de coloane dbt

Crearea data mart-urilor și a modelelor snapshot în dbt

Recapitulare a ierarhiei modelelor dbt

Creează modelul mart pentru clienți

Creează modelul mart pentru produse

Depanarea erorilor comune

Depanarea mai multor erori

Construirea modelelor snapshot în dbt

Construirea modelelor dbt

Învață să îți îmbunătățești proiectele dbt cu Jinja, simplificând codul, reducând redundanța și facilitând mentenanța. Vei exersa utilizarea variabilelor, buclelor și macrocomenzilor pentru a construi fluxuri de transformare mai eficiente.

Introducere în Jinja cu dbt

looker_ecommerce:
  outputs:
    dev:
      type: duckdb
      path: /home/repl/dbt.duckdb
  target: dev



# Name your project! Project names should contain only lowercase characters
# and underscores. A good package name should reflect your organization's
# name or the intended use of these models
name: 'looker_ecommerce'
version: '1.0.0'

# This setting configures which "profile" dbt uses for this project.
profile: 'looker_ecommerce'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


version: 2

seeds:
  - name: looker__distribution_centers


version: 2

sources:
  - name: looker_ecommerce
    tables:
      - name: users
      - name: events
      - name: inventory_items
      - name: order_items
      - name: orders
      - name: products

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location
    columns:
      - name: id 
        description: Unique ID for the distribution center
        data_tests:
          - unique
          - not_null          
      - name: name 
        description: Distribution center name
        data_tests:
          - not_null
      - name: latitude
        description: Distibution center location latitude
      - name: longitude
        description: Distribution center location longitude

  - name: stg_looker__users
    description: Users demographics and location information
    columns:
      - name: id
        description: Unique ID for the user
        data_tests:
          - unique
          - not_null
      - name: first_name
        description: User first name
        data_tests:
          - not_null
      - name: last_name
        description: User last name
        data_tests:
          - not_null
      - name: email
        description: User email
        data_tests:
          - not_null
      - name: age
        description: User age in years
        data_tests:
          - not_null
      - name: gender
        description: User gender
        data_tests:
          - accepted_values:
              values: ['F', 'M', null]
      - name: state
        description: User residing state
      - name: street_address
        description: User residing street address
      - name: postal_code
        description: User residing postal code
      - name: city
        description: User residing city 
      - name: country
        description: User residing country
      - name: latitude
        description: User residing address latitude
      - name: longitude
        description: User residing address longitude
      - name: traffic_source
        description: Traffic source for user information 
        data_tests:
          - not_null
          - accepted_values:
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search']
      - name: created_at
        description: User created at timestamp
        data_tests:
          - not_null

  - name: stg_looker__events
    description: User event data
    columns: 
      - name: id
        description: Unique ID for event entity
        data_tests:
          - unique
          - not_null
      - name: user_id
        description: User ID tied to the event 
      - name: sequence_number
        description: Sequence number for the event 
      - name: session_id
        description: Event session ID 
        data_tests:
          - not_null
      - name: created_at
        description: Event created at timestamp
        data_tests:
          - not_null
      - name: ip_address        
        description: Event IP address
        data_tests:
          - not_null
      - name: city
        description: City where event occurred 
      - name: state
        description: State where event occurred
      - name: postal_code
        description: Postal code where event occurred
      - name: browser
        description: Browser used for event 
        data_tests:
          - accepted_values:
              values: ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] 
      - name: traffic_source
        description: Traffic source for event information
        data_tests:
          - not_null
          - accepted_values:
              values: ['Adwords', 'Email', 'Facebook', 'Organic', 'YouTube']        
      - name: uri
        description: Event URI
      - name: event_type    
        description: Event type

  - name: stg_looker__inventory_items
    description: Inventory items information
    columns: 
      - name: id
        description: Unique ID for the inventory item
        data_tests:
          - unique
          - not_null
      - name: product_id
        description: Product ID associated with the inventory
        data_tests:
          - not_null
      - name: created_at
        description: Inventory created at timestamp 
        data_tests:
          - not_null
      - name: sold_at
        description: Timestamp inventory item was sold at 
      - name: cost
        description: Cost of the item 
      - name: product_category
        description: Product category
      - name: product_name
        description: Product name
      - name: product_brand
        description: Product brand 
      - name: product_retail_price
        description: Product retail price
      - name: product_department
        description: Product department
      - name: product_sku
        description: Product SKU number
      - name: product_distribution_center_id
        description: Distribution center ID where the product is stored 

  - name: stg_looker__order_items
    description: Order information on the item level
    columns: 
      - name: id
        description: Unique ID for the order item
        data_tests:
          - unique
          - not_null
      - name: order_id
        description: Order ID associated with the order item
      - name: user_id
        description: User ID associated with the order item
      - name: product_id
        description: Product ID associatd with the order item 
      - name: inventory_item_id
        description: Inventory item ID associated with the order item 
      - name: status
        description: Status of the order
      - name: created_at
        description: Order item created at timestamp 
      - name: shipped_at
        description: Order item shipped at timestamp
      - name: delivered_at
        description: Order item delivered at timestamp
      - name: returned_at
        description: Order item returned at timestamp
      - name: sale_price
        description: Order item sale price

  - name: stg_looker__orders
    description: Order information such as status and timestamps
    columns:
      - name: order_id
        description: Unique ID for the order
        data_tests:
          - unique
          - not_null
      - name: user_id
        description: User ID associated with the order
        data_tests:
          - not_null        
      - name: status
        description: Order status 
        data_tests:
          - not_null        
      - name: gender
        description: Gender of the user associated with the order
      - name: created_at
        description: Order created at timestamp
      - name: returned_at
        description: Order returned at timestamp 
      - name: shipped_at
        description: Order shipped at timestamp
      - name: delivered_at
        description: Order delivered at timestamp
      - name: num_of_item    
        description: Number of items ordered
    
  - name: stg_looker__products
    description: Product information such as SKU and category
    columns: 
      - name: id
        description: Unique ID for the product
        data_tests:
          - unique
          - not_null
      - name: cost
        description: Product cost
      - name: category
        description: Product category
      - name: name
        description: Product name 
      - name: brand
        description: Product brand 
      - name: retail_price
        description: Product retail price
      - name: department
        description: Product department
      - name: sku
        description: Product SKU number
      - name: distribution_center_id
        description: Distribution center ID where the product is stored 
        data_tests:
          - relationships:
              to: ref('stg_looker__distribution_centers')
              field: id            

SELECT 
  id,
  name,
  latitude,
  longitude
FROM {{ ref('looker__distribution_centers') }}

SELECT
  id,
  user_id,
  sequence_number,
  session_id,
  created_at,
  ip_address,
  city,
  state,
  postal_code,
  browser,
  traffic_source,
  uri,
  event_type
FROM {{ source('looker_ecommerce', 'events') }}

SELECT
  id,
  product_id,
  created_at,
  sold_at,
  cost,
  product_category,
  product_name,
  product_brand,
  product_retail_price,
  product_department,
  product_sku,
  product_distribution_center_id
FROM {{ source('looker_ecommerce', 'inventory_items') }}

SELECT
  id,
  order_id,
  user_id,
  product_id,
  inventory_item_id,
  status,
  created_at,
  shipped_at,
  delivered_at,
  returned_at,
  sale_price
FROM {{ source('looker_ecommerce', 'order_items') }}

SELECT 
  order_id,
  user_id,
  status,
  gender,
  created_at,
  returned_at,
  shipped_at,
  delivered_at,
  num_of_item
FROM {{ source('looker_ecommerce', 'orders') }}

SELECT
  id,
  cost,
  category,
  name,
  brand,
  retail_price,
  department,
  sku,
  distribution_center_id
FROM {{ source('looker_ecommerce', 'products') }}

SELECT 
  id,
  first_name,
  last_name,
  email,
  age,
  gender,
  state,
  street_address,
  postal_code,
  city,
  country,
  latitude,
  longitude,
  traffic_source,
  created_at
FROM {{ source('looker_ecommerce', 'users') }}

{% snapshot inventory_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='id',
      strategy='timestamp',
      updated_at='created_at' 
    )
}}

SELECT * 
FROM {{ source('looker_ecommerce', 'inventory_items') }}

{% endsnapshot %}


{% snapshot orders_snapshot %}

{{
    config(
      target_schema='main',
      unique_key='order_id',
      strategy='timestamp',
      updated_at='created_at' 
    )
}}

SELECT * 
FROM {{ source('looker_ecommerce', 'orders') }}

{% endsnapshot %}


version: 2

models:
  - name: customers
    description: Data mart for customer-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: user_id
        description: Unique ID for the user
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__users')
              field: id
      - name: customer_first_name
        description: Customer first name
        data_tests:
          - not_null
      - name: customer_last_name
        description: Customer last name
        data_tests:
          - not_null
      - name: customer_country
        description: Customer country
      - name: customer_acquisition_channel
        description: Customer acquisition channel
        data_tests:
          - not_null
          - accepted_values:
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search']
      - name: total_amount_spent 
        description: Total amount customer has spent
      - name: total_items_purchased
        description: Total items purchased
      - name: first_order_completed_at
        description: When the first order was completed at, if at all
      - name: last_order_completed_at
        description: When the last order was completed at, if at all
      - name: num_orders
        description: Total number of orders placed
      - name: num_orders_shipped
        description: Number of orders shipped
      - name: num_orders_complete
        description: Number of orders completed
      - name: num_orders_processing
        description: Number of orders still processing
      - name: num_orders_cancelled
        description: Number of orders cancelled
      - name: num_orders_returned
        description: Number of orders returned
      - name: num_web_sessions
        description: Number of web sessions

  - name: products
    description: Data mart for product-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: product_id
        description: Unique ID for the product
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__products')
              field: id
      - name: product_name
        description: Product name
      - name: product_category
        description: Product category
        data_tests:
          - not_null
      - name: sales_amount
        description: Total sales amount attributed to this product
      - name: cost_of_goods_sold
        description: Total cost of goods sold attributed to this product
        data_tests:
          - not_null
      - name: profit
        description: Profit, calculated by sales amount minus cost of goods sold


WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = 'Complete'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }}
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


WITH product_base AS (
    SELECT 
        id AS product_id,
        name AS product_name,
        category AS product_category,
        department AS product_department
    FROM {{ ref('stg_looker__products') }}
)

, inventory_items AS (
    SELECT 
        product_id,
        SUM(CASE WHEN sold_at IS NOT NULL THEN cost END) AS cost_of_goods_sold
    FROM {{ ref('stg_looker__inventory_items') }}
    GROUP BY 1
)

, order_items AS (
    SELECT 
        product_id,
        SUM(sale_price) AS sales_amount
    FROM {{ ref('stg_looker__order_items') }}
    GROUP BY 1    
)

SELECT 
    pb.product_id,
    pb.product_name,
    pb.product_category,
    oi.sales_amount,
    ii.cost_of_goods_sold,
    oi.sales_amount - ii.cost_of_goods_sold AS profit

FROM product_base pb 
LEFT JOIN inventory_items ii 
    ON pb.product_id = ii.product_id 
LEFT JOIN order_items oi 
    ON pb.product_id = oi.product_id


version: 2

models:
  - name: customers
    description: Data mart for customer-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: user_id
        description: Unique ID for the user
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__users')
              field: id
      - name: customer_first_name
        description: Customer first name
        data_tests:
          - not_null
      - name: customer_last_name
        description: Customer last name
        data_tests:
          - not_null
      - name: customer_country
        description: Customer country
      - name: customer_acquisition_channel
        description: Customer acquisition channel
        data_tests:
          - not_null
          - accepted_values:
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search']
      - name: total_amount_spent 
        description: Total amount customer has spent
      - name: total_items_purchased
        description: Total items purchased
      - name: first_order_completed_at
        description: When the first order was completed at, if at all
      - name: last_order_completed_at
        description: When the last order was completed at, if at all
      - name: num_orders
        description: Total number of orders placed
      - name: num_orders_shipped
        description: Number of orders shipped
      - name: num_orders_complete
        description: Number of orders completed
      - name: num_orders_processing
        description: Number of orders still processing
      - name: num_orders_cancelled
        description: Number of orders cancelled
      - name: num_orders_returned
        description: Number of orders returned
      - name: num_web_sessions
        description: Number of web sessions

  - name: products
    description: Data mart for product-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: product_id
        description: Unique ID for the product
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__products')
              field: id
      - name: product_name
        description: Product name
      - name: product_category
        description: Product category
        data_tests:
          - not_null
      - name: sales_amount
        description: Total sales amount attributed to this product
      - name: cost_of_goods_sold
        description: Total cost of goods sold attributed to this product
      - name: profit
        description: Profit, calculated by sales amount minus cost of goods sold


Încălzire cu dbt compile

version: 2

models:
  - name: customers
    description: Data mart for customer-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: user_id
        description: Unique ID for the user
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__users')
              field: id
      - name: customer_first_name
        description: Customer first name
        data_tests:
          - not_null
      - name: customer_last_name
        description: Customer last name
        data_tests:
          - not_null
      - name: customer_country
        description: Customer country
      - name: customer_acquisition_channel
        description: Customer acquisition channel
        data_tests:
          - not_null
          - accepted_values:
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search']
      - name: total_amount_spent 
        description: Total amount customer has spent
      - name: total_items_purchased
        description: Total items purchased
      - name: first_order_completed_at
        description: When the first order was completed at, if at all
      - name: last_order_completed_at
        description: When the last order was completed at, if at all
      - name: num_orders
        description: Total number of orders placed
      - name: num_orders_shipped
        description: Number of orders shipped
      - name: num_orders_complete
        description: Number of orders completed
      - name: num_orders_processing
        description: Number of orders still processing
      - name: num_orders_cancelled
        description: Number of orders cancelled
      - name: num_orders_returned
        description: Number of orders returned
      - name: num_web_sessions
        description: Number of web sessions

  - name: products
    description: Data mart for product-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: product_id
        description: Unique ID for the product
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__products')
              field: id
      - name: product_name
        description: Product name
      - name: product_category
        description: Product category
        data_tests:
          - not_null
      - name: sales_amount
        description: Total sales amount attributed to this product
      - name: cost_of_goods_sold
        description: Total cost of goods sold attributed to this product
        data_tests: # Remove not_null data test
          - not_null # Remove not_null data test
      - name: profit
        description: Profit, calculated by sales amount minus cost of goods sold


Încălzire cu dbt compile 2

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM stg_looker__users -- Fix Jinja reference
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = 'Complete'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM stg_looker__orders -- Fix Jinja reference
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


WITH product_base AS (
    SELECT 
        id AS product_id,
        name AS product_name,
        category AS product_category,
        department AS product_department
    FROM {{ ref('stg_looker__products') }} 
)

, inventory_items AS (
    SELECT 
        product_id,
        SUM(CASE WHEN sold_at IS NOT NULL THEN cost END) AS cost_of_goods_sold
    FROM {{ ref('stg_looker__inventory_items') }}
    GROUP BY 1
)

, order_items AS (
    SELECT 
        product_id,
        SUM(sale_price) AS sales_amount
    FROM {{ ref('stg_looker__order_items') }}
    GROUP BY 1    
)

SELECT 
    pb.product_id,
    pb.product_name,
    pb.product_category,
    oi.sales_amount,
    ii.cost_of_goods_sold,
    oi.sales_amount - ii.cost_of_goods_sold AS profit

FROM product_base pb 
LEFT JOIN inventory_items ii 
    ON pb.product_id = ii.product_id 
LEFT JOIN order_items oi 
    ON pb.product_id = oi.product_id


WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }} -- Fix Jinja reference
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = 'Complete'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }} -- Fix Jinja reference
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Folosirea dbt compile pentru depanare

-- Set the value to 'Complete'
{% set order_items_status = ____ %}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
	{# Pass in the variable order_items_status #}
    WHERE status = '{{ ____ }}'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }}
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


-- Set the value to 'Complete'
{% set order_items_status = 'Complete' %}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
  	{# Pass in the variable order_items_status #}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }}
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Setarea variabilelor cu dbt Jinja

{% set order_items_status = 'Complete' %}
{# Set a Jinja variable called country with value 'Australia' #}
____ 

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
	{# Replace with jinja dynamic filtering #}
    WHERE country = 'Australia' 
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }}
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


{% set order_items_status = 'Complete' %}
{# Set a Jinja variable called country with value 'Australia' #}
{% set country = 'Australia' %}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
 	{# Replace with jinja dynamic filtering #}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT 
            CASE WHEN status = 'Shipped'
            THEN order_id
            END) AS num_orders_shipped,
        COUNT(DISTINCT 
            CASE WHEN status = 'Complete'
            THEN order_id
            END) AS num_orders_complete,
        COUNT(DISTINCT 
            CASE WHEN status = 'Processing'
            THEN order_id
            END) AS num_orders_processing,
        COUNT(DISTINCT 
            CASE WHEN status = 'Cancelled'
            THEN order_id
            END) AS num_orders_cancelled,
        COUNT(DISTINCT 
            CASE WHEN status = 'Returned'
            THEN order_id
            END) AS num_orders_returned,
    FROM {{ ref('stg_looker__orders') }}
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.num_orders,
    o.num_orders_shipped,
    o.num_orders_complete,
    o.num_orders_processing,
    o.num_orders_cancelled,
    o.num_orders_returned,
    wt.num_web_sessions,
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Setarea variabilelor cu Jinja 2 în dbt

Utilizarea avansată a Jinja cu dbt

{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
	-- Replace the following logic with a SELECT * FROM {{ ref('int_orders__pivoted') }}
	SELECT 
        user_id,
        COUNT(DISTINCT order_id) AS num_orders,
        COUNT(DISTINCT CASE WHEN status = 'Shipped' THEN order_id END) AS num_orders_Shipped,
        COUNT(DISTINCT CASE WHEN status = 'Complete' THEN order_id END) AS num_orders_Complete,
        COUNT(DISTINCT CASE WHEN status = 'Processing' THEN order_id END) AS num_orders_Processing,
        COUNT(DISTINCT CASE WHEN status = 'Cancelled' THEN order_id END) AS num_orders_Cancelled,
        COUNT(DISTINCT CASE WHEN status = 'Returned' THEN order_id END) AS num_orders_Returned
  	FROM {{ ref('stg_looker__orders') }}
    -- Remember to remove the GROUP BY
  	GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,

        COUNT(DISTINCT CASE WHEN traffic_source = 'Adwords' THEN user_id END) AS num_traffic_source_Adwords,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Email' THEN user_id END) AS num_traffic_source_Email,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Facebook' THEN user_id END) AS num_traffic_source_Facebook,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Organic' THEN user_id END) AS num_traffic_source_Organic,
        COUNT(DISTINCT CASE WHEN traffic_source = 'YouTube' THEN user_id END) AS num_traffic_source_YouTube,

        COUNT(DISTINCT CASE WHEN browser = 'Chrome' THEN user_id END) AS num_browser_Chrome,
        COUNT(DISTINCT CASE WHEN browser = 'Firefox' THEN user_id END) AS num_browser_Firefox,
        COUNT(DISTINCT CASE WHEN browser = 'Safari' THEN user_id END) AS num_browser_Safari,
        COUNT(DISTINCT CASE WHEN browser = 'IE' THEN user_id END) AS num_browser_IE,
        COUNT(DISTINCT CASE WHEN browser = 'Other' THEN user_id END) AS num_browser_Other      
        FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


-- Fill in order status values in line 2
{% set order_statuses = ____ %} 

SELECT 
    user_id,
    COUNT(DISTINCT order_id) AS num_orders,
    -- Fill in lines 7 - 13 for looping through creating orders statuses pivot columns
    {%- ____ order_status ____ order_statuses %}
    COUNT(DISTINCT CASE WHEN status = '{{ order_status }}' THEN order_id END) AS num_orders_{{ order_status }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- ____ %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

-- Fill in order status values in line 2
{% set order_statuses = ['Shipped', 'Complete', 'Processing', 'Cancelled', 'Returned'] %} 

SELECT 
    user_id,
    COUNT(DISTINCT order_id) AS num_orders,
    -- Fill in lines 7 - 13 for looping through creating orders statuses pivot columns
    {%- for order_status in order_statuses %}
    COUNT(DISTINCT CASE WHEN status = '{{ order_status }}' THEN order_id END) AS num_orders_{{ order_status }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
	-- Replace the following logic with a SELECT * FROM {{ ref('int_orders__pivoted') }}
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
  	-- Remember to remove the GROUP BY
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,

        COUNT(DISTINCT CASE WHEN traffic_source = 'Adwords' THEN user_id END) AS num_traffic_source_Adwords,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Email' THEN user_id END) AS num_traffic_source_Email,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Facebook' THEN user_id END) AS num_traffic_source_Facebook,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Organic' THEN user_id END) AS num_traffic_source_Organic,
        COUNT(DISTINCT CASE WHEN traffic_source = 'YouTube' THEN user_id END) AS num_traffic_source_YouTube,

        COUNT(DISTINCT CASE WHEN browser = 'Chrome' THEN user_id END) AS num_browser_Chrome,
        COUNT(DISTINCT CASE WHEN browser = 'Firefox' THEN user_id END) AS num_browser_Firefox,
        COUNT(DISTINCT CASE WHEN browser = 'Safari' THEN user_id END) AS num_browser_Safari,
        COUNT(DISTINCT CASE WHEN browser = 'IE' THEN user_id END) AS num_browser_IE,
        COUNT(DISTINCT CASE WHEN browser = 'Other' THEN user_id END) AS num_browser_Other      
        FROM {{ ref('stg_looker__events') }}
    GROUP BY 1
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Utilizarea buclelor Jinja pentru SQL repetitiv 1

{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
)

, web_traffic AS (
  	-- Replace the following logic with a SELECT * FROM {{ ref('int_events__pivoted') }}
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,

        COUNT(DISTINCT CASE WHEN traffic_source = 'Adwords' THEN user_id END) AS num_traffic_source_Adwords,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Email' THEN user_id END) AS num_traffic_source_Email,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Facebook' THEN user_id END) AS num_traffic_source_Facebook,
        COUNT(DISTINCT CASE WHEN traffic_source = 'Organic' THEN user_id END) AS num_traffic_source_Organic,
        COUNT(DISTINCT CASE WHEN traffic_source = 'YouTube' THEN user_id END) AS num_traffic_source_YouTube,

        COUNT(DISTINCT CASE WHEN browser = 'Chrome' THEN user_id END) AS num_browser_Chrome,
        COUNT(DISTINCT CASE WHEN browser = 'Firefox' THEN user_id END) AS num_browser_Firefox,
        COUNT(DISTINCT CASE WHEN browser = 'Safari' THEN user_id END) AS num_browser_Safari,
        COUNT(DISTINCT CASE WHEN browser = 'IE' THEN user_id END) AS num_browser_IE,
        COUNT(DISTINCT CASE WHEN browser = 'Other' THEN user_id END) AS num_browser_Other      
        FROM {{ ref('stg_looker__events') }}
    -- Remember to remove the GROUP BY
    GROUP BY 1
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


{% set traffic_source_values = ['Adwords', 'Email', 'Facebook', 'Organic', 'YouTube'] %}
{% set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] %}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
        
    -- Fill in lines 9 - 11 for looping through traffic source pivot
    {%- for ____ in traffic_source_values %}
    COUNT(DISTINCT CASE WHEN traffic_source = '{{ ____ }}' THEN user_id END) AS num_traffic_source_{{ ____ }},
    {%- endfor %}
      
	-- Fill in lines 14 - 19 for looping through browser pivot
    {%- for ____ in browser_values %}
    COUNT(DISTINCT CASE WHEN browser = '{{ ____ }}' THEN user_id END) AS num_browser_{{ ____ }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- ____ %}

FROM {{ ref('stg_looker__events') }}
GROUP BY 1

{% set order_statuses = ['Shipped', 'Complete', 'Processing', 'Cancelled', 'Returned'] %}

SELECT 
    user_id,
    COUNT(DISTINCT order_id) AS num_orders,
    {%- for order_status in order_statuses %}
    COUNT(DISTINCT CASE WHEN status = '{{ order_status }}' THEN order_id END) AS num_orders_{{ order_status }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1

{% set traffic_source_values = ['Adwords', 'Email', 'Facebook', 'Organic', 'YouTube'] %}
{% set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] %}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
    
    -- Fill in lines 9 - 11 for looping through traffic source pivot    
    {%- for traffic_source_value in traffic_source_values %}
    COUNT(DISTINCT CASE WHEN traffic_source = '{{ traffic_source_value }}' THEN user_id END) AS num_traffic_source_{{ traffic_source_value }},
    {%- endfor %}
    
	-- Fill in lines 14 - 19 for looping through browser pivot
    {%- for browser_value in browser_values %}
    COUNT(DISTINCT CASE WHEN browser = '{{ browser_value }}' THEN user_id END) AS num_browser_{{ browser_value }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}      

FROM {{ ref('stg_looker__events') }}
GROUP BY 1

{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
)

, web_traffic AS (
  	-- Replace the following logic with a SELECT * FROM {{ ref('int_events__pivoted') }}
    SELECT *
  	FROM {{ ref('int_events__pivoted') }}
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Utilizarea buclelor Jinja în SQL repetat 2

version: 2

models:
  - name: customers
    description: Data mart for customer-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: user_id
        description: Unique ID for the user
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__users')
              field: id
      - name: customer_first_name
        description: Customer first name
        data_tests:
          - not_null
      - name: customer_last_name
        description: Customer last name
        data_tests:
          - not_null
      - name: customer_country
        description: Customer country
      - name: customer_acquisition_channel
        description: Customer acquisition channel
        data_tests:
          - not_null
          - accepted_values:
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search']
      - name: total_amount_spent 
        description: Total amount customer has spent
      - name: total_items_purchased
        description: Total items purchased
      - name: first_order_completed_at
        description: When the first order was completed at, if at all
      - name: last_order_completed_at
        description: When the last order was completed at, if at all
      - name: num_orders
        description: Total number of orders placed
      - name: num_orders_shipped
        description: Number of orders shipped
      - name: num_orders_complete
        description: Number of orders completed
      - name: num_orders_processing
        description: Number of orders still processing
      - name: num_orders_cancelled
        description: Number of orders cancelled
      - name: num_orders_returned
        description: Number of orders returned
      - name: num_web_sessions
        description: Number of web sessions

  - name: products
    description: Data mart for product-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: product_id
        description: Unique ID for the product
        data_tests:
          - unique
          - not_null
          - relationships:
              to: ref('stg_looker__products')
              field: id
      - name: product_name
        description: Product name
      - name: product_category
        description: Product category
        data_tests:
          - not_null
      - name: sales_amount
        description: Total sales amount attributed to this product
        data_tests:
          - not_null        
      - name: cost_of_goods_sold
        description: Total cost of goods sold attributed to this product
        data_tests:
          - not_null        
      - name: profit
        description: Profit, calculated by sales amount minus cost of goods sold
        data_tests:
          - not_null        


{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
)

, web_traffic AS (
    SELECT *
    FROM {{ ref('int_events__pivoted') }}
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


WITH product_base AS (
    SELECT 
        id AS product_id,
        name AS product_name,
        category AS product_category,
        department AS product_department
    FROM {{ ref('stg_looker__products') }}
)

, inventory_items AS (
    SELECT 
        product_id,
        SUM(CASE WHEN sold_at IS NOT NULL THEN cost END) AS cost_of_goods_sold
    FROM {{ ref('stg_looker__inventory_items') }}
    WHERE sold_at IS NULL
    GROUP BY 1
)

, order_items AS (
    SELECT 
        product_id,
        SUM(sale_price) AS sales_amount
    FROM {{ ref('stg_looker__order_items') }}
    GROUP BY 1    
)

SELECT 
    pb.product_id,
    pb.product_name,
    pb.product_category,
    
    {{ coalesce_and_round('oi.sales_amount', 2) }} AS sales_amount,
    
    -- Fill in the coalesce and rounding of the cost of goods sold to 2 decimals
	____ AS cost_of_goods_sold, 
    
    {{ coalesce_and_round('oi.sales_amount', 0) }} - {{ coalesce_and_round('ii.cost_of_goods_sold', 0) }} AS profit

FROM product_base pb 
LEFT JOIN inventory_items ii 
    ON pb.product_id = ii.product_id 
LEFT JOIN order_items oi 
    ON pb.product_id = oi.product_id


{% set traffic_source_values = ['Adwords', 'Email', 'Facebook', 'Organic', 'YouTube'] %}
{% set browser_values = ['Chrome', 'Firefox', 'Safari', 'IE', 'Other'] %}

SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
    
    {#- jinja loop for pivoting out traffic source values -#}
    {%- for traffic_source_value in traffic_source_values %}
    COUNT(DISTINCT CASE WHEN traffic_source = '{{ traffic_source_value }}' THEN user_id END) AS num_traffic_source_{{ traffic_source_value }},
    {%- endfor %}

    {#- jinja loop for pivoting out browser values -#}
    {%- for browser_value in browser_values %}
    COUNT(DISTINCT CASE WHEN browser = '{{ browser_value }}' THEN user_id END) AS num_browser_{{ browser_value }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}

FROM {{ ref('stg_looker__events') }}
GROUP BY 1

WITH product_base AS (
    SELECT 
        id AS product_id,
        name AS product_name,
        category AS product_category,
        department AS product_department
    FROM {{ ref('stg_looker__products') }}
)

, inventory_items AS (
    SELECT 
        product_id,
        SUM(CASE WHEN sold_at IS NOT NULL THEN cost END) AS cost_of_goods_sold
    FROM {{ ref('stg_looker__inventory_items') }}
    WHERE sold_at IS NULL
    GROUP BY 1
)

, order_items AS (
    SELECT 
        product_id,
        SUM(sale_price) AS sales_amount
    FROM {{ ref('stg_looker__order_items') }}
    GROUP BY 1    
)

SELECT 
    pb.product_id,
    pb.product_name,
    pb.product_category,

    {{ coalesce_and_round('oi.sales_amount', 2) }} AS sales_amount,

    -- Fill in the coalesce and rounding of the cost of goods sold to 2 decimals
    {{ coalesce_and_round('ii.cost_of_goods_sold', 2) }} AS cost_of_goods_sold,
    
    {{ coalesce_and_round('oi.sales_amount', 0) }} - {{ coalesce_and_round('ii.cost_of_goods_sold', 0) }} AS profit

FROM product_base pb 
LEFT JOIN inventory_items ii 
    ON pb.product_id = ii.product_id 
LEFT JOIN order_items oi 
    ON pb.product_id = oi.product_id


-- Copy paste the Jinja macro here
{%- macro coalesce_and_round(column_name, decimal_places = 2) -%}
    ROUND(COALESCE({{ column_name }}, 0), {{ decimal_places }})
{%- endmacro %}

Macrouri cu Jinja

{# Modify lines 2 and 3 to trim whitespaces #}
{% set order_items_status = 'Complete' %}
{% set country = 'Australia' %}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
)

, web_traffic AS (
    SELECT *
  	FROM {{ ref('int_events__pivoted') }}
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


SELECT 
    user_id,
    COUNT(DISTINCT session_id) AS num_web_sessions,
    
    {#- jinja loop for pivoting out traffic source values -#}
    {%- for traffic_source_value in traffic_source_values %}
    COUNT(DISTINCT CASE WHEN traffic_source = '{{ traffic_source_value }}' THEN user_id END) AS num_traffic_source_{{ traffic_source_value }},
    {%- endfor %}

    {#- jinja loop for pivoting out browser values -#}
    {%- for browser_value in browser_values %}
    COUNT(DISTINCT CASE WHEN browser = '{{ browser_value }}' THEN user_id END) AS num_browser_{{ browser_value }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}

FROM {{ ref('stg_looker__events') }}
GROUP BY 1
      

{% set order_statuses = ['Shipped', 'Complete', 'Processing', 'Cancelled', 'Returned'] %}

SELECT 
    user_id,
    COUNT(DISTINCT order_id) AS num_orders,
    {%- for order_status in order_statuses %}
    COUNT(DISTINCT CASE WHEN status = '{{ order_status }}' THEN order_id END) AS num_orders_{{ order_status }}
    {%- if not loop.last -%}
        ,
    {%- endif -%}
    {%- endfor %}
FROM {{ ref('stg_looker__orders') }}
GROUP BY 1


{# Modify lines 2 and 3 to trim whitespaces #}
{%- set order_items_status = 'Complete' -%}
{%- set country = 'Australia' -%}

WITH customer_base AS (
    SELECT 
        id AS user_id,
        first_name AS customer_first_name,
        last_name AS customer_last_name,
        country AS customer_country,
        traffic_source AS customer_acquisition_channel
    FROM {{ ref('stg_looker__users') }}
    WHERE country = '{{ country }}'
)

, order_items AS (
    SELECT 
        user_id,
        SUM(sale_price) AS total_amount_spent,
        COUNT(DISTINCT id) AS total_items_purchased,
        MIN(created_at) AS first_order_completed_at,
        MAX(created_at) AS last_order_completed_at,
    FROM {{ ref('stg_looker__order_items') }}
    WHERE status = '{{ order_items_status }}'
    GROUP BY 1
)

, orders AS (
    SELECT * 
    FROM {{ ref('int_orders__pivoted') }}
)

, web_traffic AS (
    SELECT *
  	FROM {{ ref('int_events__pivoted') }}
)

SELECT
    cb.user_id,
    -- dimensions
    cb.customer_first_name,
    cb.customer_last_name,
    cb.customer_country,
    cb.customer_acquisition_channel,
    -- facts 
    oi.total_amount_spent,
    oi.total_items_purchased,
    oi.first_order_completed_at,
    oi.last_order_completed_at,
    o.* EXCLUDE(user_id),
    wt.* EXCLUDE(user_id)
FROM customer_base cb
LEFT JOIN order_items oi 
    on cb.user_id = oi.user_id
LEFT JOIN orders o
    ON cb.user_id = o.user_id 
LEFT JOIN web_traffic wt 
    ON cb.user_id = wt.user_id


Eliminarea spațiilor albe cu Jinja

Recapitulare: studiu de caz dbt

Îmbunătățirea dbt cu Jinja

Descoperă puterea transformării datelor cu acest curs practic dedicat dbt (Data Build Tool). Conceput pentru utilizatorii dbt la început de drum, cursul pune accent pe exerciții practice, din lumea reală, care îți oferă mai multe oportunități de a construi, transforma și consolida înțelegerea comenzilor esențiale dbt. Prin practică directă, vei căpăta încredere în fiecare etapă a procesului – de la configurarea mediului dbt până la crearea unor modele avansate care rezolvă provocări reale de business. Lucrând prin exerciții interactive, vei dobândi experiența necesară pentru a aborda cu încredere proiecte reale de pipeline ETL.

Transformă datele brute în modele curate și fiabile folosind dbt (Data Build Tool) - un framework modern de transformare bazat pe SQL, utilizat de echipele de date din întreaga lume. Acest curs practic bazat pe studii de caz este conceput pentru cursanții aflați la început de drum, care vor să-și dezvolte abilități reale prin exerciții ghidate și aplicate. Vei configura propriul tău mediu dbt, vei modela date la scară și vei scrie cod reutilizabil folosind funcțiile încorporate ale dbt.<br><br><h2>Configurează-ți proiectul și explorează datele</h2>Începe prin a configura un proiect dbt și a lucra cu un set de date real de E-Commerce. Vei structura datele brute, vei configura profiluri și vei depana problemele de sintaxă, în timp ce vei înțelege contextul de business din spatele fiecărui pas de transformare.<br><br><h2>Construiește și validează modele</h2>Învață să creezi modele de staging scalabile și să aplici verificări ale calității datelor pentru a te asigura că seturile tale de date sunt corecte și pregătite pentru analiză. Vei construi o bază solidă pentru a răspunde la întrebări esențiale de business.<br><br><h2>Automatizează cu Jinja</h2>Finalizează cursul învățând cum să folosești Jinja pentru a scrie cod reutilizabil și ușor de întreținut. Vei folosi variabile, fluxul de control și buclele pentru a urma principiul DRY (Don't Repeat Yourself) și a-ți eficientiza fluxul de lucru dbt.

Data Manipulation in SQL

Intermediate dbt

Învață să transformi date brute în modele curate și fiabile cu dbt prin exerciții practice.

Studiu de caz: Construirea modelelor de date pentru e-commerce cu dbt

Învață să transformi datele brute în modele curate și fiabile cu dbt, prin exerciții practice, din lumea reală. 

Încălzire cu dbt compile 2

Studiu de caz: Construirea modelelor de date pentru e-commerce cu dbt

Exercițiu interactiv practic