Model orders and customers | From Data Analyst to Analytics Engineer

Separate activity from description

A fact model records measurable activity with a clear row definition. A dimension model describes the people, products, or other entities used to group and filter that activity.

For this project, orders are the activity and customers provide descriptive attributes. Keeping them separate prevents every report from rebuilding the same customer cleanup and makes the join relationship easier to inspect.

Create the customer dimension

Create commerce/assets/analytics/dim_customers.sql:

/* @bruin
name: analytics.dim_customers
type: duckdb.sql
depends:
  - staging.stg_customers
materialization:
  type: table
  strategy: create+replace
columns:
  - name: customer_id
    type: string
    primary_key: true
    checks:
      - name: not_null
      - name: unique
@bruin */

SELECT
  customer_id,
  customer_name,
  country,
  created_date
FROM staging.stg_customers;

Each row in the customer dimension represents one customer. customer_id is therefore the primary key.

Create the order fact

Create commerce/assets/analytics/fct_orders.sql:

/* @bruin
name: analytics.fct_orders
type: duckdb.sql
depends:
  - staging.stg_orders
materialization:
  type: table
  strategy: create+replace
columns:
  - name: order_id
    type: integer
    primary_key: true
@bruin */

SELECT
  order_id,
  customer_id,
  order_ts,
  order_date,
  order_status,
  order_amount,
  order_status = 'paid' AS is_paid,
  CASE
    WHEN order_status = 'paid' THEN order_amount
    ELSE CAST(0 AS DECIMAL(12, 2))
  END AS recognized_revenue
FROM staging.stg_orders;

Each row in the order fact represents one order. The model preserves the original amount and adds recognized_revenue using the definition from the model contract.

Ask your coding agent

AI Prompt

Create or review analytics.dim_customers and analytics.fct_orders using the models in this lesson.

Keep customer details in the customer model and order activity in the order model. Use the existing staging tables as inputs. Then:

Run both models.
Compare the order count before and after joining customers.
List orders that do not match a customer.
Confirm that recognized revenue includes only paid orders.

Show the file diff, commands, and results. Do not change source data or remove rows to make a check pass.

Check the join relationship

Before building the reporting table, prove that every order matches at most one customer. This query compares the order count before and after the join:

bruin run commerce/assets/analytics/dim_customers.sql
bruin run commerce/assets/analytics/fct_orders.sql
bruin query --connection duckdb-default \
  --description "compare fact rows before and after the customer join" \
  --query "SELECT (SELECT COUNT(*) FROM analytics.fct_orders) AS fact_rows, (SELECT COUNT(*) FROM analytics.fct_orders f LEFT JOIN analytics.dim_customers c USING (customer_id)) AS joined_rows;"

Both counts should be 6. If joined_rows is larger, the customer table contains duplicate IDs. The join then repeats order rows and makes totals such as revenue too high.

Now check for orders without a customer match:

bruin query --connection duckdb-default \
  --description "find fact orders without a matching customer" \
  --query "SELECT f.customer_id, COUNT(*) AS orders FROM analytics.fct_orders f LEFT JOIN analytics.dim_customers c USING (customer_id) WHERE c.customer_id IS NULL GROUP BY f.customer_id;"

The query should return no rows.

Checkpoint

You should have:

One customer row per customer_id.
One order row per order_id.
Six fact rows before and after the customer join.
No unmatched customer IDs.