Build a clean staging layer | From Data Analyst to Analytics Engineer

Give later models a stable input

A staging model stays close to one source. It standardizes names and types, handles obvious cleanup, and exposes source quirks instead of burying business logic inside every report.

Keep staging boring. Revenue definitions and multi-table joins belong later, where reviewers can see that the model is making a business decision.

Stage the orders

Create commerce/assets/staging/stg_orders.sql:

/* @bruin
name: staging.stg_orders
type: duckdb.sql
depends:
  - raw.orders
materialization:
  type: table
  strategy: create+replace
@bruin */

SELECT
  CAST(order_id AS INTEGER) AS order_id,
  NULLIF(TRIM(customer_id), '') AS customer_id,
  CAST(order_ts AS TIMESTAMP) AS order_ts,
  CAST(order_ts AS DATE) AS order_date,
  LOWER(TRIM(status)) AS order_status,
  CAST(amount AS DECIMAL(12, 2)) AS order_amount
FROM raw.orders;

The model renames status and amount to make their business object explicit. It also derives order_date once, so later models do not repeat timestamp conversion logic.

depends tells Bruin that this table comes from raw.orders, so Bruin runs the files in the right order. create+replace rebuilds this small local table each time. Later, you will update only selected dates in the reporting table.

Stage the customers

Create commerce/assets/staging/stg_customers.sql:

/* @bruin
name: staging.stg_customers
type: duckdb.sql
depends:
  - raw.customers
materialization:
  type: table
  strategy: create+replace
@bruin */

SELECT
  NULLIF(TRIM(customer_id), '') AS customer_id,
  TRIM(customer_name) AS customer_name,
  UPPER(TRIM(country)) AS country,
  CAST(created_date AS DATE) AS created_date
FROM raw.customers;

This course treats raw.customers as the current customer record. In a real project, ask whether customer details change and whether reports need the current value or the value at the time of an order. That decision affects how you store customer history.

Ask your coding agent

AI Prompt

Create or review these two staging models in my course project:

commerce/assets/staging/stg_orders.sql
commerce/assets/staging/stg_customers.sql

Follow the SQL and Bruin headers in this lesson. Keep the models close to the source: clean names and types, derive order_date, and normalize status and country values. Do not add revenue rules or joins.

Then run both models and compare raw and staged row counts. Show me the diff, the commands, and the counts. Do not commit the changes.

Run and compare the layers

bruin run commerce/assets/staging/stg_orders.sql
bruin run commerce/assets/staging/stg_customers.sql
bruin query --connection duckdb-default \
  --description "compare staged order status counts with the raw source" \
  --query "SELECT order_status, COUNT(*) AS orders FROM staging.stg_orders GROUP BY order_status ORDER BY order_status;"

The staged order count should remain six, and the status counts should match the raw profile. A cleanup step should not silently lose records.

Checkpoint

Compare raw.orders and staging.stg_orders:

bruin query --connection duckdb-default \
  --description "compare raw and staged order row counts" \
  --query "SELECT (SELECT COUNT(*) FROM raw.orders) AS raw_rows, (SELECT COUNT(*) FROM staging.stg_orders) AS staged_rows;"