Skip to main content

​
Procedure Signature

silver.load_silver()
Returns: void Language: PL/pgSQL Security: SECURITY DEFINER

​
Purpose and Functionality

The silver.load_silver() procedure transforms and cleanses raw data from the bronze layer into curated, business-ready data in the silver layer. This is the second stage of the data warehouse ETL pipeline, where data quality improvements and standardization are applied. Key responsibilities:
  • Data cleansing and standardization
  • Removing duplicates and handling nulls
  • Transforming coded values into human-readable formats
  • Applying business rules and validations
  • Type conversions and data formatting

​
Key Transformations Applied

​
CRM Customer Information (silver.crm_cust_info)

-- Keep only the most recent record per customer
ROW_NUMBER() OVER (PARTITION BY cst_id ORDER BY cst_create_date DESC) AS flag_last
WHERE flag_last = 1

​
CRM Product Information (silver.crm_prd_info)

-- Extract category ID from product key
replace(substring(trim(prd_key), 1, 5), '-','_') as cat_id
substring(prd_key, 7, length(prd_key)) as prd_key

​
CRM Sales Details (silver.crm_sales_details)

-- Validate and convert date fields
CASE
  WHEN sls_ord_dt IS NULL OR sls_ord_dt = 0 OR length(sls_ord_dt::text) != 8
  THEN NULL
  ELSE to_date(TRIM(sls_ord_dt::text), 'YYYYMMDD')
END AS sls_ord_dt

​
ERP Customer Data (silver.erp_cust_az12)

-- Remove 'NAS' prefix from customer IDs
case 
  when cid like 'NAS%' then substring(cid, 4, length(cid))
  else cid
end as cid

​
ERP Location Data (silver.erp_loc_a101)

-- Remove dashes from customer IDs
replace(cid, '-', '') as cid

​
ERP Product Category Data (silver.erp_px_cat_g1v2)

-- Direct copy with no transformations
insert into silver.erp_px_cat_g1v2 (id, cat, subcat, manteinance)
select id, cat, subcat, manteinance
from bronze.erp_px_cat_g1v2;

​
Steps Performed

1

Transform CRM Customer Information

Load silver.crm_cust_info with deduplication, text trimming, and code expansion for marital status and gender
2

Transform CRM Product Information

Load silver.crm_prd_info with category extraction, null handling, product line expansion, and end date calculation
3

Transform CRM Sales Details

Load silver.crm_sales_details with date validation, sales amount correction, and price validation
4

Clean ERP Customer Data

Load silver.erp_cust_az12 with ID prefix removal, birthdate validation, and gender standardization
5

Clean ERP Location Data

Load silver.erp_loc_a101 with ID formatting and country code expansion
6

Load ERP Product Category Data

Load silver.erp_px_cat_g1v2 with direct copy (no transformations needed)

​
Usage Example

CALL silver.load_silver();
Expected Output: 
NOTICE:  [16:35:12] load silver started
NOTICE:  Step [load silver.crm_cust_info] completed successfully (3 s)
NOTICE:  Step [load silver.crm_prd_info] completed successfully (2 s)
NOTICE:  Step [load silver.crm_sales_details] completed successfully (4 s)
NOTICE:  Step [load silver.erp_cust_az12] completed successfully (1 s)
NOTICE:  Step [load silver.erp_loc_a101] completed successfully (1 s)
NOTICE:  Step [load silver.erp_px_cat_g1v2] completed successfully (1 s)
NOTICE:  load silver finished in [12] seconds
NOTICE:  [16:35:24] load silver started

​
Prerequisites

The bronze layer must be loaded first before running this procedure:
CALL bronze.load_bronze();
CALL silver.load_silver();

​
Error Handling

The procedure implements robust error handling at each transformation step:
  • Step-level Exception Handling: Each table transformation is isolated with its own error handler
  • Descriptive Error Messages: Errors include the step name and PostgreSQL error details
  • Fail-Fast Behavior: Execution halts immediately upon encountering an error
  • Performance Metrics: Each step logs execution time for monitoring
  • Success Tracking: Counter variables track successful (v_ok) and failed (v_err) operations
If any transformation step fails, the procedure raises an exception:
Error in step: [step_name] - [error_details]
All subsequent steps are skipped to maintain data consistency.

​
Data Quality Rules

The procedure enforces several data quality rules:
Rule TypeDescriptionExample
DeduplicationKeep only latest recordsCRM customer info by create date
Null HandlingReplace nulls with defaultsProduct cost defaults to 0
Date ValidationReject invalid datesFuture birthdates become NULL
Value StandardizationExpand codes to full names’M’ → ‘Male’, ‘S’ → ‘Single’
ID FormattingRemove unwanted charactersRemove ‘NAS’ prefix, dashes
Calculated FieldsDerive values from other columnsSales = Quantity × Price
Type ConversionConvert string dates to DATE type’20240101’ → 2024-01-01

​
Permissions

The procedure grants the following permissions: 
GRANT EXECUTE ON PROCEDURE silver.load_silver() TO PUBLIC;
GRANT USAGE ON SCHEMA silver TO PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA silver TO PUBLIC;

​
Data Flow

Build docs developers (and LLMs) love

Get started for freeTalk to us