02. Snowflake RAW Setup
Source Database
The source system for this demo is Snowflake database BEEMS_TECHNE_ECOMM. RAW preserves application data as received. dbt reads these tables but does not overwrite them.
CREATE DATABASE IF NOT EXISTS BEEMS_TECHNE_ECOMM;
CREATE SCHEMA IF NOT EXISTS BEEMS_TECHNE_ECOMM.RAW;
CREATE SCHEMA IF NOT EXISTS BEEMS_TECHNE_ECOMM.BRONZE;
CREATE SCHEMA IF NOT EXISTS BEEMS_TECHNE_ECOMM.SILVER;
CREATE SCHEMA IF NOT EXISTS BEEMS_TECHNE_ECOMM.GOLD;
CREATE SCHEMA IF NOT EXISTS BEEMS_TECHNE_ECOMM.MART;
USE DATABASE BEEMS_TECHNE_ECOMM;
USE SCHEMA RAW;
RAW Source Dataset
The demo uses twelve source tables: CUSTOMERS, CUSTOMER_ADDRESSES, CATEGORIES, PRODUCTS, ORDERS, ORDER_ITEMS, PAYMENTS, SHIPMENTS, RETURNS, PROMOTIONS, INVENTORY_SNAPSHOTS, and PRODUCT_REVIEWS.
Create the source tables
Run the following DDL in BEEMS_TECHNE_ECOMM.RAW:
CREATE OR REPLACE TABLE RAW.CUSTOMERS
(
CUSTOMER_ID NUMBER(38, 0),
FIRST_NAME VARCHAR(100),
LAST_NAME VARCHAR(100),
EMAIL VARCHAR(255),
COUNTRY VARCHAR(100),
CREATED_AT TIMESTAMP_NTZ,
UPDATED_AT TIMESTAMP_NTZ,
CUSTOMER_STATUS VARCHAR(30)
);
CREATE OR REPLACE TABLE RAW.CATEGORIES
(
CATEGORY_ID NUMBER(38, 0),
CATEGORY_NAME VARCHAR(100),
PARENT_CATEGORY_ID NUMBER(38, 0)
);
CREATE OR REPLACE TABLE RAW.PRODUCTS
(
PRODUCT_ID NUMBER(38, 0),
CATEGORY_ID NUMBER(38, 0),
PRODUCT_NAME VARCHAR(255),
UNIT_PRICE NUMBER(12, 2),
PRODUCT_STATUS VARCHAR(30),
UPDATED_AT TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE RAW.ORDERS
(
ORDER_ID NUMBER(38, 0),
CUSTOMER_ID NUMBER(38, 0),
PROMOTION_ID NUMBER(38, 0),
ORDER_DATE DATE,
ORDER_STATUS VARCHAR(30),
CURRENCY VARCHAR(10),
SHIPPING_ADDRESS_ID NUMBER(38, 0),
UPDATED_AT TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE RAW.ORDER_ITEMS
(
ORDER_ITEM_ID NUMBER(38, 0),
ORDER_ID NUMBER(38, 0),
PRODUCT_ID NUMBER(38, 0),
QUANTITY NUMBER(38, 0),
UNIT_PRICE NUMBER(12, 2),
DISCOUNT_AMOUNT NUMBER(12, 2)
);
CREATE OR REPLACE TABLE RAW.PAYMENTS
(
PAYMENT_ID NUMBER(38, 0),
ORDER_ID NUMBER(38, 0),
PAYMENT_METHOD VARCHAR(50),
PAYMENT_STATUS VARCHAR(30),
AMOUNT NUMBER(12, 2),
PAYMENT_DATE TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE RAW.SHIPMENTS
(
SHIPMENT_ID NUMBER(38, 0),
ORDER_ID NUMBER(38, 0),
SELLER_ID NUMBER(38, 0),
COURIER_PARTNER VARCHAR(100),
AWB_NUMBER VARCHAR(100),
SHIPMENT_DATE DATE,
EXPECTED_DELIVERY_DATE DATE,
SHIPPING_STATUS VARCHAR(30),
ORIGIN_PINCODE VARCHAR(10),
DESTINATION_PINCODE VARCHAR(10),
CREATED_AT TIMESTAMP_NTZ,
UPDATED_AT TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE RAW.CUSTOMER_ADDRESSES
(
ADDRESS_ID NUMBER(38, 0),
CUSTOMER_ID NUMBER(38, 0),
ADDRESS_TYPE VARCHAR(30),
CITY VARCHAR(100),
STATE VARCHAR(100),
POSTAL_CODE VARCHAR(20),
IS_CURRENT BOOLEAN,
UPDATED_AT TIMESTAMP_NTZ
);
CREATE OR REPLACE TABLE RAW.RETURNS
(
RETURN_ID NUMBER(38, 0),
ORDER_ITEM_ID NUMBER(38, 0),
RETURN_DATE DATE,
RETURN_QUANTITY NUMBER(38, 0),
RETURN_REASON VARCHAR(255),
RETURN_STATUS VARCHAR(30)
);
CREATE OR REPLACE TABLE RAW.PROMOTIONS
(
PROMOTION_ID NUMBER(38, 0),
PROMOTION_CODE VARCHAR(50),
DISCOUNT_PERCENT NUMBER(5, 2),
STARTS_ON DATE,
ENDS_ON DATE,
PROMOTION_STATUS VARCHAR(30)
);
CREATE OR REPLACE TABLE RAW.INVENTORY_SNAPSHOTS
(
SNAPSHOT_DATE DATE,
PRODUCT_ID NUMBER(38, 0),
WAREHOUSE_CODE VARCHAR(30),
QUANTITY_ON_HAND NUMBER(38, 0),
REORDER_LEVEL NUMBER(38, 0)
);
CREATE OR REPLACE TABLE RAW.PRODUCT_REVIEWS
(
REVIEW_ID NUMBER(38, 0),
PRODUCT_ID NUMBER(38, 0),
CUSTOMER_ID NUMBER(38, 0),
RATING NUMBER(2, 0),
REVIEW_TEXT VARCHAR(2000),
REVIEWED_AT TIMESTAMP_NTZ
);
Load sample data
INSERT INTO RAW.CUSTOMERS
(CUSTOMER_ID, FIRST_NAME, LAST_NAME, EMAIL, COUNTRY,
CREATED_AT, UPDATED_AT, CUSTOMER_STATUS)
VALUES
(10001, 'Asha', 'Sharma', 'asha@example.com', 'India',
'2025-01-05 09:00:00', '2025-01-05 09:00:00', 'ACTIVE'),
(10002, 'Daniel', 'Lee', 'daniel@example.com', 'Singapore',
'2025-01-06 10:30:00', '2025-01-06 10:30:00', 'ACTIVE'),
(10003, 'Maya', 'Patel', 'maya@example.com', 'India',
'2025-01-07 11:15:00', '2025-01-07 11:15:00', 'ACTIVE'),
(10004, 'Ravi', 'Kumar', 'ravi@example.com', 'India',
'2025-01-08 12:00:00', '2025-01-08 12:00:00', 'ACTIVE'),
(10006, 'Nora', 'Tan', 'nora@example.com', 'Singapore',
'2025-01-09 13:30:00', '2025-01-09 13:30:00', 'ACTIVE');
INSERT INTO RAW.CATEGORIES
(CATEGORY_ID, CATEGORY_NAME, PARENT_CATEGORY_ID)
VALUES
(10, 'Data Engineering', NULL),
(20, 'Cloud Training', NULL);
INSERT INTO RAW.PRODUCTS
(PRODUCT_ID, CATEGORY_ID, PRODUCT_NAME, UNIT_PRICE, PRODUCT_STATUS, UPDATED_AT)
VALUES
(101, 10, 'Snowflake Fundamentals', 76700.00, 'ACTIVE', '2025-01-10 08:00:00'),
(102, 10, 'dbt Project Workshop', 81419.00, 'ACTIVE', '2025-01-10 08:00:00'),
(103, 20, 'Data Quality Lab', 7769.00, 'ACTIVE', '2025-01-10 08:00:00'),
(104, 20, 'Premium Support Package', 5359.00, 'ACTIVE', '2025-01-10 08:00:00'),
(105, 20, 'Data Governance Toolkit', 4769.00, 'ACTIVE', '2025-01-10 08:00:00');
INSERT INTO RAW.ORDERS
(ORDER_ID, CUSTOMER_ID, PROMOTION_ID, ORDER_DATE, ORDER_STATUS,
CURRENCY, SHIPPING_ADDRESS_ID, UPDATED_AT)
VALUES
(50001, 10001, NULL, '2025-02-01', 'DELIVERED', 'INR', NULL, '2025-02-01 12:00:00'),
(50002, 10002, NULL, '2025-02-02', 'DELIVERED', 'SGD', NULL, '2025-02-02 13:00:00'),
(50003, 10003, NULL, '2025-02-03', 'SHIPPED', 'INR', NULL, '2025-02-03 14:00:00'),
(50004, 10004, NULL, '2025-02-04', 'CANCELLED', 'INR', NULL, '2025-02-04 15:00:00'),
(50005, 10006, NULL, '2025-02-05', 'DELIVERED', 'SGD', NULL, '2025-02-05 16:00:00');
INSERT INTO RAW.ORDER_ITEMS
(ORDER_ITEM_ID, ORDER_ID, PRODUCT_ID, QUANTITY, UNIT_PRICE, DISCOUNT_AMOUNT)
VALUES
(1, 50001, 101, 1, 76700.00, 0.00),
(2, 50002, 102, 1, 81419.00, 0.00),
(3, 50003, 103, 1, 7769.00, 0.00),
(4, 50004, 104, 1, 5359.00, 0.00),
(5, 50005, 105, 1, 4769.00, 0.00);
INSERT INTO RAW.PAYMENTS
(PAYMENT_ID, ORDER_ID, PAYMENT_METHOD, PAYMENT_STATUS, AMOUNT, PAYMENT_DATE)
VALUES
(90001, 50001, 'CARD', 'PAID', 76700.00, '2025-02-01 12:05:00'),
(90002, 50002, 'BANK_TRANSFER', 'PAID', 81419.00, '2025-02-02 13:05:00'),
(90003, 50003, 'CARD', 'PAID', 7769.00, '2025-02-03 14:05:00'),
(90004, 50004, 'CARD', 'FAILED', 5359.00, '2025-02-04 15:05:00'),
(90005, 50005, 'WALLET', 'PAID', 4769.00, '2025-02-05 16:05:00');
Verify the sample data
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
ORDER_STATUS
FROM BEEMS_TECHNE_ECOMM.RAW.ORDERS
ORDER BY ORDER_ID;
Expected output:
| ORDER_ID | CUSTOMER_ID | ORDER_DATE | ORDER_STATUS |
|---|---|---|---|
| 50001 | 10001 | 2025-02-01 | DELIVERED |
| 50002 | 10002 | 2025-02-02 | DELIVERED |
| 50003 | 10003 | 2025-02-03 | SHIPPED |
| 50004 | 10004 | 2025-02-04 | CANCELLED |
| 50005 | 10006 | 2025-02-05 | DELIVERED |
Continue with 03. dbt Project Structure.