02. Beginner-Friendly Test Setup
Beginner-Friendly Test Setup
This lesson uses the same E-Commerce sample data as the rest of the DBT training project. Instead of inventing a brand-new demo database, we will use your existing BEEMS_TECHNE_ECOMM project and add a simple test setup around real order and customer data.
By the end of this lesson, you will have:
- a DBT source definition for the raw ECOMM tables
- a staging model for orders
- a small example schema with test-ready columns
- a
dbt runcommand to build the model - validation queries to confirm the output in Snowflake
This is the easiest way to understand how DBT tests work before we move into not_null, unique, and relationships checks.
1⃣ Use the Existing ECOMM Project
Your project already has the raw data in Snowflake under:
BEEMS_TECHNE_ECOMM.RAW
In the earlier project setup, the key source tables include:
RAW.CUSTOMERSRAW.ORDERSRAW.ORDER_ITEMSRAW.PAYMENTS
We can use these as a beginner-friendly test example.
2⃣ Sample Data for the Lesson
A simple and realistic test dataset is already available in the project. For example, the raw orders table includes rows like:
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS,
CURRENCY
FROM BEEMS_TECHNE_ECOMM.RAW.ORDERS
ORDER BY ORDER_ID
LIMIT 5;
Example output:
| ORDER_ID | CUSTOMER_ID | ORDER_DATE | ORDER_STATUS | CURRENCY |
|---|---|---|---|---|
| 50001 | 10001 | 2025-02-01 | DELIVERED | INR |
| 50002 | 10002 | 2025-02-02 | DELIVERED | SGD |
| 50003 | 10003 | 2025-02-03 | SHIPPED | INR |
| 50004 | 10004 | 2025-02-04 | CANCELLED | INR |
| 50005 | 10006 | 2025-02-05 | DELIVERED | SGD |
This is a good starter dataset because it has:
- unique order IDs
- valid customer references
- different order statuses
- real amounts and dates
This is enough to understand how tests will behave in a real project.
3⃣ Add the Source Definition in models/schema.yml
Open or create your project models/schema.yml file and add this section:
version: 2
sources:
- name: ecommerce_raw
database: BEEMS_TECHNE_ECOMM
schema: RAW
tables:
- name: customers
description: "Raw customer data from the ECOMM application"
columns:
- name: customer_id
description: "Primary key for customer records"
- name: first_name
description: "Customer first name"
- name: last_name
description: "Customer last name"
- name: email
description: "Customer email"
- name: country
description: "Customer country"
- name: orders
description: "Raw order data from the sales system"
columns:
- name: order_id
description: "Primary key for each order"
- name: customer_id
description: "Customer who placed the order"
- name: order_date
description: "Date the order was placed"
- name: order_status
description: "Current order status"
- name: currency
description: "Order currency"
- name: order_items
description: "Raw item-level order details"
columns:
- name: order_item_id
description: "Identifier for each line item"
- name: order_id
description: "Parent order ID"
- name: product_id
description: "Product purchased"
- name: quantity
description: "Qty purchased"
- name: unit_price
description: "Unit price for the product"
models:
- name: stg_orders
description: "Cleaned order staging model"
columns:
- name: order_id
description: "Unique order identifier"
- name: customer_id
description: "Customer who placed the order"
- name: order_date
description: "Date of the order"
- name: order_status
description: "Normalized order status"
- name: currency
description: "Currency of the order"
This file is the beginning of your testing setup. dbt uses this YAML to understand the structure of your sources and models before running tests.
4⃣ Create a Simple Staging Model
Create the file:
models/staging/stg_orders.sql
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
UPPER(TRIM(ORDER_STATUS)) AS ORDER_STATUS,
UPPER(TRIM(CURRENCY)) AS CURRENCY,
UPDATED_AT
FROM {{ source('ecommerce_raw', 'orders') }}
This model keeps the data simple and beginner-friendly. It cleans the raw values and creates a staging table that is easy to test.
5⃣ Run the Model
Now build the model in dbt:
dbt run --select stg_orders
If the project is configured correctly, dbt will compile the model and create the table/view in your warehouse using the project schema and target settings.
6⃣ Validate the Result in Snowflake
After dbt run, check the table in Snowflake:
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS,
CURRENCY
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_ORDERS
ORDER BY ORDER_ID
LIMIT 5;
You should see rows similar to:
| ORDER_ID | CUSTOMER_ID | ORDER_DATE | ORDER_STATUS | CURRENCY |
|---|---|---|---|---|
| 50001 | 10001 | 2025-02-01 | DELIVERED | INR |
| 50002 | 10002 | 2025-02-02 | DELIVERED | SGD |
| 50003 | 10003 | 2025-02-03 | SHIPPED | INR |
| 50004 | 10004 | 2025-02-04 | CANCELLED | INR |
| 50005 | 10006 | 2025-02-05 | DELIVERED | SGD |
You can also validate row counts:
SELECT COUNT(*) AS total_orders
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_ORDERS;
And check for missing IDs:
SELECT COUNT(*) AS missing_customer_ids
FROM BEEMS_TECHNE_ECOMM.BRONZE.STG_ORDERS
WHERE CUSTOMER_ID IS NULL;
7⃣ What This Gives You
At this point, your project has:
- source definitions in
schema.yml - a real ECOMM staging model
- working raw data from
BEEMS_TECHNE_ECOMM.RAW - a dbt model that has been built with
dbt run - validation queries you can use before adding tests
This is the best beginner setup because you are working with the same project and sample data that the rest of the course uses.
8⃣ Next Step
In the next lesson, we will add DBT built-in tests like:
not_nulluniqueaccepted_valuesrelationships
to this same stg_orders model.
You will then be able to see how DBT identifies bad data before it reaches downstream reports.