Practica construyendo un proyecto de dbt desde cero. Aplica tus habilidades para cargar distintos tipos de datos en el proyecto de dbt y configurar varios modelos de staging en dbt. Este capítulo se centra en las partes E y L del proceso ELT.

Presentación del caso práctico de dbt

Comprobar la instalación de dbt

Inicializa el proyecto de dbt

Configurar el proyecto de dbt y cargar datos

Cargar el centro de distribución como un archivo seed de dbt

Crea el modelo de staging de centros de distribución

Cargar y crear el modelo de usuarios con dbt source

Más práctica con fuentes y modelos de staging en dbt

Depurar errores comunes

Configurar dbt

Profundiza en el modelado de datos con dbt. Construye la canalización de datos desde los modelos de staging preliminares hasta los modelos finales del data mart para responder a necesidades críticas del negocio. Por el camino, practicarás la creación de pruebas de datos para poner barandillas contra la deriva de la calidad de los datos.

Crear descripciones y tests en 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
      - name: name 
        description: Distribution center name
      - name: latitude
        description: Distribution center location latitude
      - name: ____ # Fill in the column name for longitude
        description: ____ # Fill in the column description

  - name: stg_looker__users
    description: Users demographics and location information
    columns:
      - name: id
        description: Unique ID for the user
      - name: first_name
        description: User first name
      - name: last_name
        description: User last name
      - name: email
        description: User email
      - name: age
        description: User age in years
      - name: gender
        description: User gender
      - 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 
      - name: created_at
        description: User created at timestamp

  - name: stg_looker__events
    description: User event data
    columns: 
      - name: id
        description: Unique ID for event entity
      - 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 
      - name: created_at
        description: Event created at timestamp
      - name: ip_address        
        description: Event IP address
      - 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 
      - name: traffic_source
        description: Traffic source for event information
      - 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
      - name: product_id
        description: Product ID associated with the inventory
      - name: created_at
        description: Inventory created at timestamp 
      - 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
      - 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
      - name: user_id
        description: User ID associated with the order
      - name: status
        description: Order status 
      - 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: ____ # Fill in the table name for staging products 
    description: Product information such as SKU and category
    columns: 
      - name: ____ # Fill in the column name for the ID
        description: ____ # Fill in a column description for the ID 
      - 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
    

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') }}

version: 2

models:
  - name: stg_looker__distribution_centers
    description: Distribution center name and location
    columns:
      - name: id 
        description: Unique ID for the distribution center
      - name: name 
        description: Distribution center name
      - name: latitude
        description: Distribution center location latitude
      - name: longitude # Fill in the column name for longitude
        description: Distribution center location longitude # Fill in the column description

  - name: stg_looker__users
    description: Users demographics and location information
    columns:
      - name: id
        description: Unique ID for the user
      - name: first_name
        description: User first name
      - name: last_name
        description: User last name
      - name: email
        description: User email
      - name: age
        description: User age in years
      - name: gender
        description: User gender
      - 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 
      - name: created_at
        description: User created at timestamp

  - name: stg_looker__events
    description: User event data
    columns: 
      - name: id
        description: Unique ID for event entity
      - 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 
      - name: created_at
        description: Event created at timestamp
      - name: ip_address        
        description: Event IP address
      - 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 
      - name: traffic_source
        description: Traffic source for event information
      - 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
      - name: product_id
        description: Product ID associated with the inventory
      - name: created_at
        description: Inventory created at timestamp 
      - 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
      - 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
      - name: user_id
        description: User ID associated with the order
      - name: status
        description: Order status 
      - 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 # Fill in the table name for staging products 
    description: Product information such as SKU and category
    columns: 
      - name: id # Fill in the column name for the ID
        description: Unique ID for the product # Fill in a column description for the ID 
      - 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
    

Añade descripciones de columnas en dbt

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:
          - ____ # Fill in the data test for uniqueness
          - ____ # Fill in the data test for not null          
      - name: name 
        description: Distribution center name
        data_tests:
          - ____ # Fill in the data test for not null
      - name: latitude
        description: Distribution center location latitude
      - name: longtitude
        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
      - 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:
          - ____ # Fill in the data test for not null
          - ____ # Fill in the data test for an accepted_values test
              values: ____ # Fill in values accepted for an accepted_values test
      - 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 
      - 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 


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 # Fill in the data test for uniqueness
          - not_null # Fill in the data test for not null
      - name: name 
        description: Distribution center name
        data_tests:
          - not_null # Fill in the data test for not null
      - name: latitude
        description: Distribution center location latitude
      - name: longtitude
        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
      - 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 # Fill in the data test for not null
          - accepted_values: # Fill in the data test for an accepted_values test
              values: ['Display', 'Email', 'Facebook', 'Organic', 'Search'] # Fill in values accepted for an accepted_values test
      - 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 
      - 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 


Añade pruebas de columnas en dbt

Crear data marts y modelos snapshot en dbt

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: Distribution center location latitude
      - name: longtitude
        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 
      - 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 
      - name: traffic_source
        description: Traffic source for event information
      - 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
      - name: status
        description: Order status 
      - 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 


{% 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
      - 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,
    -- 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.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') }}
    -- 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,
    -- dimensions
    pb.product_name,
    pb.product_category,
    -- facts (e.g. profit analysis)
    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

Repaso de la jerarquía de modelos en dbt

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: Distribution center location latitude
      - name: longtitude
        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
      - 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 
      - 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
        data_tests:
          - not_null     
      - 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 


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:
          ____ # Fill in uniqueness test
          ____ # Fill in not null test 
          ____ # Fill in relationship test to staging users
            ____
            ____
      - 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
          ____ # Fill in accepted value test 
            ____
      - 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

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 _____ -- Replace with dbt ref model
)

, 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 stg_looker__order_items _____ -- Replace with dbt ref model
    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 _____ -- Replace with dbt ref model
    GROUP BY 1
)

, web_traffic AS (
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) AS num_web_sessions,
    FROM stg_looker__events _____ -- Replace with dbt ref model
    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 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


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

Crea el modelo de mart para customers

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: _____ # Fill in table name 
    description: Data mart for product-related dimensions and facts, for analytical and reporting purposes
    columns:
      - name: product_id
        description: Unique ID for the product
		____ # Declare the data_tests for product ID
          ____ # Fill in unique, not null, and foreign key data tests
          ____
          ____
            ____
            ____
      - name: product_name
        description: Product name
      - name: product_category
        description: Product category
		____ # Fill in not null datatest for product category
          ____
      - 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
        

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 (
	-- Fill in the SQL syntax for creating a CTE with two columns: product_id, and a calculated sales amount per product
    SELECT 
        product_id,
        ____ AS sales_amount
    FROM ____
    GROUP BY ____
)

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 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 (
    -- Fill in the SQL syntax for creating a CTE with two columns: product_id, and a calculated sales amount per product
    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


Crea el modelo de mart para products

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


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_item 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: user_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


Depurar más errores

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


Crear modelos snapshot en dbt

Crear modelos de dbt

Aprende a mejorar tus proyectos de dbt con Jinja optimizando el código, reduciendo la redundancia y mejorando el mantenimiento. Practicarás el uso de variables, bucles y macros para crear flujos de transformación más eficientes.

Introducción a Jinja con dbt

Calentando motores con dbt compile

Calentando motores con dbt compile 2

Usar dbt compile para depurar

Configurar variables con Jinja en dbt

Definir variables con dbt Jinja 2

Uso avanzado de Jinja con dbt

Usar bucles de Jinja para SQL repetitivo 1

Usar bucles de Jinja en SQL repetitivo 2

Macros con Jinja

Recortar espacios en blanco con Jinja

Recap: estudio de caso de dbt

Mejorar dbt con Jinja

Explora el poder de la transformación de datos con nuestro curso práctico de estudio de caso sobre dbt (Data Build Tool). Diseñado para quienes están empezando con dbt, este curso se centra en ejercicios prácticos y realistas que te darán múltiples oportunidades para crear, transformar y afianzar tu dominio de los comandos esenciales de dbt. Mediante la práctica guiada, ganarás confianza en cada paso del proceso: desde la configuración de tu entorno de dbt hasta la creación de modelos avanzados que resuelven necesidades clave del negocio. A través de ejercicios interactivos, adquirirás la experiencia necesaria para abordar con seguridad proyectos reales de canalizaciones ETL de datos.

Transforma datos sin procesar en modelos limpios y fiables utilizando dbt (Data Build Tool), un moderno marco de transformación basado en SQL utilizado por equipos de datos de todo el mundo. Este curso práctico basado en casos prácticos está diseñado para estudiantes principiantes que desean desarrollar habilidades útiles en el mundo real a través de ejercicios prácticos guiados. Configurarás tu propio entorno dbt, modelarás datos a escala y escribirás código reutilizable utilizando las funciones integradas de dbt.<br><br><h2>Configura tu proyecto y explora los datos</h2>Empieza configurando un proyecto dbt y trabajando con un conjunto de datos reales de comercio electrónico. Estructurarás datos sin procesar, configurarás perfiles y depurarás problemas de sintaxis, al tiempo que obtienes información sobre el contexto empresarial que hay detrás de cada paso de la transformación.<br><br><h2>Crear y validar modelos</h2>Aprende a crear modelos de ensayo escalables y a aplicar controles de calidad de los datos para garantizar que tus conjuntos de datos sean precisos y estén listos para su análisis. Crearás una base sólida para responder a preguntas clave sobre el negocio.<br><br><h2>Automatiza con Jinja</h2>Termina el curso aprendiendo a utilizar Jinja para escribir código reutilizable y fácil de mantener. Utilizarás variables, control de flujo y bucles para seguir el principio DRY (Don't Repeat Yourself, no te repitas) y optimizar tu flujo de trabajo en dbt.

Data Manipulation in SQL

Intermediate dbt

Aprende a transformar datos en modelos fiables con dbt mediante ejercicios prácticos y reales.

Estudio de caso: creación de modelos de datos de comercio electrónico con dbt

Aprende a transformar datos sin procesar en modelos limpios y fiables con dbt a través de ejercicios prácticos y reales. 

Repaso de la jerarquía de modelos en dbt

Estudio de caso: creación de modelos de datos de comercio electrónico con dbt

ejercicio interactivo práctico