Skip to main content

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_IDCUSTOMER_IDORDER_DATEORDER_STATUS
50001100012025-02-01DELIVERED
50002100022025-02-02DELIVERED
50003100032025-02-03SHIPPED
50004100042025-02-04CANCELLED
50005100062025-02-05DELIVERED

Continue with 03. dbt Project Structure.