Load and profile the source data

Inspect before transforming

Source tables reflect the application that produced them. They may contain useful identifiers and timestamps, but you should not assume that names, types, status values, or records will stay the same.

You will use two small CSV files as the source for this course. Small data lets you compare every result with the original rows by hand, which is useful while learning the workflow.

Add the order source

Create commerce/assets/raw/orders.csv:

order_id,customer_id,order_ts,status,amount
1001,C001,2026-01-01 09:14:00,paid,49.00
1002,C001,2026-01-01 12:03:00,refunded,15.00
1003,C002,2026-01-02 08:45:00,paid,85.50
1004,C003,2026-01-02 16:30:00,paid,25.00
1005,C002,2026-01-03 10:00:00,pending,42.00
1006,C004,2026-01-03 14:20:00,paid,120.00

Beside it, create commerce/assets/raw/raw_orders.asset.yml:

name: raw.orders
type: duckdb.seed

parameters:
  path: orders.csv

columns:
  - name: order_id
    type: integer
  - name: customer_id
    type: string
  - name: order_ts
    type: timestamp
  - name: status
    type: string
  - name: amount
    type: float

The seed asset loads the versioned file into raw.orders. Declared types prevent DuckDB from guessing differently when the file changes.

Add the customer source

Create commerce/assets/raw/customers.csv:

customer_id,customer_name,country,created_date
C001,Ada Corp,GB,2025-11-01
C002,Northstar Labs,DE,2025-12-15
C003,Deniz Goods,TR,2026-01-02
C004,Maple Market,CA,2025-10-20

Create commerce/assets/raw/raw_customers.asset.yml:

name: raw.customers
type: duckdb.seed

parameters:
  path: customers.csv

columns:
  - name: customer_id
    type: string
  - name: customer_name
    type: string
  - name: country
    type: string
  - name: created_date
    type: date

Load and profile both sources

Run the pipeline, then inspect the order statuses and date range:

bruin run commerce/pipeline.yml
bruin query --connection duckdb-default \
  --description "profile raw order statuses" \
  --query "SELECT status, COUNT(*) AS orders, SUM(amount) AS amount FROM raw.orders GROUP BY status ORDER BY status;"
bruin query --connection duckdb-default \
  --description "profile raw order dates and customers" \
  --query "SELECT MIN(order_ts) AS first_order, MAX(order_ts) AS last_order, COUNT(DISTINCT customer_id) AS customers FROM raw.orders;"
bruin query --connection duckdb-default \
  --description "compare raw order rows with distinct order IDs" \
  --query "SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_order_ids FROM raw.orders;"

Ask your coding agent

This prompt runs the same checks shown above:

AI Prompt

From the root of my analytics engineering course project, run the commerce pipeline and inspect raw.orders.

Report:

the row count
the count and total amount for each status
the first and last order timestamps
the number of customers
whether order_id appears unique

Use bruin query with the duckdb-default connection. Do not edit any files. Show the commands you ran and a short table of results. If a command fails, stop and explain the error instead of changing the project.

You should find six orders, four customers, and three status values: paid, pending, and refunded.

Write down two source assumptions before continuing:

This sample has six distinct order_id values. You will enforce that assumption with a staging check later.
Only paid orders contribute to the course definition of recognized revenue.

Checkpoint

The raw tables should exist in DuckDB, and your counts should match the CSV files. If they do not, stop here and resolve the difference. Otherwise, the later row and revenue checks will also be wrong.