Skip to main content
The Silver layer contains cleaned and standardized tables from both CRM and ERP source systems. All tables include a dwh_create_date timestamp column for audit tracking.

​
CRM Tables

​
crm_cust_info

Cleaned customer information from the CRM system with standardized gender and marital status values.
​
cst_id
INT
Customer ID (primary identifier)
​
cst_key
VARCHAR(50)
Customer key (trimmed)
​
cst_firstname
VARCHAR(100)
Customer first name
​
cst_lastname
VARCHAR(100)
Customer last name
​
cst_marital_status
VARCHAR(100)
Marital status (standardized: ‘Single’, ‘Married’, ‘n/a’)
​
cst_gndr
VARCHAR(10)
Gender (standardized: ‘Male’, ‘Female’, ‘n/a’)
​
cst_create_date
DATE
Date when customer record was created
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.crm_cust_info;
CREATE TABLE silver.crm_cust_info (
    cst_id INT,
    cst_key VARCHAR(50),
    cst_firstname VARCHAR(100),
    cst_lastname VARCHAR(100),
    cst_marital_status VARCHAR(100),
    cst_gndr VARCHAR(10),
    cst_create_date DATE,
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
crm_prd_info

Cleaned product information with parsed category IDs and standardized product lines.
​
prd_id
INT
Product ID
​
cat_id
VARCHAR(50)
Category ID (extracted from product key)
​
prd_key
VARCHAR(50)
Product key (parsed from original)
​
prd_name
VARCHAR(100)
Product name
​
prd_cost
DECIMAL(10, 2)
Product cost
​
prd_line
VARCHAR(50)
Product line (standardized: ‘Mountain’, ‘Road’, ‘Other sales’, ‘Touring’, ‘n/a’)
​
prd_start_dt
DATE
Product availability start date
​
prd_end_dt
DATE
Product availability end date (calculated using LEAD window function)
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.crm_prd_info;
CREATE TABLE silver.crm_prd_info (
    prd_id INT,
    cat_id VARCHAR(50),
    prd_key VARCHAR(50),
    prd_name VARCHAR(100),
    prd_cost DECIMAL(10, 2),
    prd_line VARCHAR(50),
    prd_start_dt DATE,
    prd_end_dt DATE,
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
crm_sales_details

Cleaned sales transaction details with validated dates and corrected price/sales calculations.
​
sls_ord_num
VARCHAR(50)
Sales order number
​
sls_prd_key
VARCHAR(50)
Product key (foreign key to products)
​
sls_cust_id
INT
Customer ID (foreign key to customers)
​
sls_ord_dt
DATE
Order date (validated and converted from YYYYMMDD format)
​
sls_ship_dt
DATE
Ship date (validated and converted from YYYYMMDD format)
​
sls_due_dt
DATE
Due date (validated and converted from YYYYMMDD format)
​
sls_sales
INT
Total sales amount (calculated/corrected as needed)
​
sls_quantity
INT
Quantity sold
​
sls_price
DECIMAL(10, 2)
Unit price (validated and corrected)
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.crm_sales_details;
CREATE TABLE silver.crm_sales_details (
    sls_ord_num VARCHAR(50),
    sls_prd_key VARCHAR(50),
    sls_cust_id INT,
    sls_ord_dt DATE,
    sls_ship_dt DATE,
    sls_due_dt DATE,
    sls_sales INT,
    sls_quantity INT,
    sls_price DECIMAL(10, 2),
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
ERP Tables

​
erp_cust_az12

Cleaned ERP customer data with validated birth dates and standardized gender values.
​
cid
VARCHAR(50)
Customer ID (cleaned from source)
​
bdate
DATE
Birth date (validated - future dates set to NULL)
​
gen
VARCHAR(10)
Gender (standardized: ‘Male’, ‘Female’, ‘n/a’)
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.erp_cust_az12;
CREATE TABLE silver.erp_cust_az12 (
    cid VARCHAR(50),
    bdate DATE,
    gen VARCHAR(10),
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
erp_loc_a101

Cleaned ERP location data with standardized country names.
​
cid
VARCHAR(50)
Customer ID (hyphens removed)
​
cntry
VARCHAR(100)
Country (standardized: ‘United States’, ‘Canada’, ‘Germany’, etc.)
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.erp_loc_a101;
CREATE TABLE silver.erp_loc_a101 (
    cid VARCHAR(50),
    cntry VARCHAR(100),
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

​
erp_px_cat_g1v2

ERP product category data (minimal transformations applied).
​
id
VARCHAR(50)
Product category ID
​
cat
VARCHAR(50)
Category
​
subcat
VARCHAR(50)
Subcategory
​
manteinance
VARCHAR(50)
Maintenance information
​
dwh_create_date
TIMESTAMP
Data warehouse ingestion timestamp (default: CURRENT_TIMESTAMP)
DROP TABLE IF EXISTS silver.erp_px_cat_g1v2;
CREATE TABLE silver.erp_px_cat_g1v2 (
    id VARCHAR(50),
    cat VARCHAR(50),
    subcat VARCHAR(50),
    manteinance VARCHAR(50),
    dwh_create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
All Silver tables are truncated and reloaded during each ETL run via the silver.load_silver() stored procedure.

Build docs developers (and LLMs) love

Get started for freeTalk to us