Staging models: cleaning, dedupe, typing

The stg/ layer is where dirty raw becomes clean dim/fact-ready data. Three jobs: cast types explicitly, dedupe rows that should be unique, and rename columns to the convention the marts layer expects.

cloud_run_dbt/dbt/models/stg/fact_order_items_stg.sql
sql
{{ config(materialized='table') }}

SELECT
    S.order_id
    ,S.order_item_id
    ,S.product_id
    ,S.seller_id
    ,S.shipping_limit_date
    ,S.price
    ,S.freight_value
    ,CURRENT_TIMESTAMP() AS last_extract_ts
FROM `{{ var("gcp_project_id") }}.ecommerce_raw.olist_order_items_dataset` S

A staging model. Materialized as a table for downstream speed. Pulls from the raw dataset with the var-driven project ID. Adds a loaded_at timestamp for freshness.

cloud_run_dbt/dbt/models/stg/dim_customers_stg.sql
sql
{{ config(materialized='table') }}

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY ingestion_timestamp DESC
    ) AS rn
  FROM `{{ var("gcp_project_id") }}.ecommerce_raw.olist_customers_dataset`
)
SELECT * EXCEPT (rn) FROM ranked WHERE rn = 1

The classic dbt dedupe pattern. row_number() over a partition picks the latest row per business key. A WHERE rn=1 clause keeps it.

Table for hot stg models that downstream marts read frequently (faster reads). View for stg models that change shape often during dev (no rebuild cost on each model change). The course uses table for production speed.

Quiz: Quiz

Loading practice…