Staging Layer Design (Raw → Staged)
In an ELT architecture, the first step after ingestion is transforming the messy, raw data into a clean, standardized format. This transition—moving data from the Bronze (Raw) layer to the Silver (Staged/Cleaned) layer—is the foundation of data quality.
If the staging layer is built poorly, all downstream models and dashboards will be compromised.
Topics Covered
- Raw Zone vs Staging Zone
- Schema-on-Read vs Schema-on-Write
- Data Cleansing Techniques
- Building the Staging Layer
Raw Zone vs Staging Zone
The Raw Zone (Bronze)
- Purpose: To land data from source systems exactly as it arrives. No transformations.
- Format: Often raw JSON, CSV strings, or heavily nested Parquet loaded into
VARIANTcolumns. - Why: If a transformation pipeline breaks, you need the unmodified raw data to replay the pipeline and fix the error without going back to the source system.
The Staging Zone (Silver)
- Purpose: To clean, standardize, and type-cast the data.
- Format: Strictly typed columnar tables (e.g.,
VARCHAR,TIMESTAMP,NUMBER). - Why: Downstream BI tools and business analysts cannot query raw JSON efficiently.
Schema-on-Read vs Schema-on-Write
Historically, ETL tools used Schema-on-Write: the tool forced the data to fit a strict schema before loading it into the database. If the upstream system added a column, the ETL tool broke.
Snowflake embraces Schema-on-Read. You load the raw data exactly as it is (often into a VARIANT column). Then, you use SQL views or Dynamic Tables to apply a schema when reading the data into the Staging layer. This handles schema drift seamlessly.
Hands-On: Building the Staging Layer
Let's assume a telecommunications company in Mumbai is receiving raw JSON telemetry from cell towers. We need to build a Staging Layer that extracts the JSON, cleans the data, and standardizes the timestamps.
1. Database and Raw Data Setup
(Run this in your Snowflake worksheet)
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_TELECOM_DB;
USE DATABASE BEEMS_TELECOM_DB;
CREATE SCHEMA IF NOT EXISTS BRONZE;
CREATE SCHEMA IF NOT EXISTS SILVER;
-- The Raw Zone (Schema-on-Read)
CREATE OR REPLACE TABLE BRONZE.RAW_TOWER_LOGS (
FILE_NAME STRING,
INGESTED_AT TIMESTAMP,
RAW_JSON VARIANT
);
-- Insert messy mock data
INSERT INTO BRONZE.RAW_TOWER_LOGS (FILE_NAME, INGESTED_AT, RAW_JSON)
SELECT 'log_1.json', CURRENT_TIMESTAMP(), PARSE_JSON(column1) FROM VALUES
('{"tower_id": "T-100", "city": "mumbai", "signal_strength": "85.5", "timestamp": "1698765432"}'),
('{"tower_id": "T-101", "city": "DELHI", "signal_strength": "null", "timestamp": "1698765440"}'),
('{"tower_id": "T-102", "city": "chennai ", "signal_strength": "-99", "timestamp": "1698765450"}');
2. Data Cleansing Techniques
In the Staging layer, we must address several issues:
- Type Casting: Everything in JSON is technically a string or generic number. We must cast to explicit Snowflake types (
::INT,::NUMBER). - Standardization: "mumbai" and "DELHI" must be title-cased. "chennai " has trailing whitespace.
- Null Handling: The string
"null"must be converted to an actual SQLNULL. - Epoch Conversion: The Unix timestamp must be converted to a readable
TIMESTAMP.
3. Creating the Staging Table
We use a Dynamic Table to automatically process the raw data and apply our cleansing rules.
(To ensure you have the correct privileges, verify your role with SELECT CURRENT_ROLE();)
CREATE OR REPLACE DYNAMIC TABLE SILVER.STG_TOWER_LOGS
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
-- Type casting and extraction
RAW_JSON:tower_id::STRING AS TOWER_ID,
-- String standardization (Trim whitespace, Title Case)
INITCAP(TRIM(RAW_JSON:city::STRING)) AS CITY,
-- Null handling and type casting
IFF(RAW_JSON:signal_strength::STRING = 'null', NULL, RAW_JSON:signal_strength::NUMBER(5,2)) AS SIGNAL_STRENGTH,
-- Epoch timestamp conversion
TO_TIMESTAMP(RAW_JSON:timestamp::INT) AS EVENT_TIMESTAMP,
-- Audit columns (Crucial for debugging)
INGESTED_AT AS BRONZE_INGESTED_AT
FROM BRONZE.RAW_TOWER_LOGS
-- Basic row-level data quality filtering (e.g., dropping invalid signal strengths)
WHERE IFF(RAW_JSON:signal_strength::STRING = 'null', NULL, RAW_JSON:signal_strength::NUMBER(5,2)) > -100;
-- Query the cleaned staging table
SELECT * FROM SILVER.STG_TOWER_LOGS;
Notice how the output is perfectly clean, properly typed, and ready for analytical queries.
Staging Best Practices
- Always Include Audit Columns: Ensure your staging table includes
BRONZE_INGESTED_ATorSOURCE_FILE_NAMEso you can trace a row back to its origin if data quality issues arise. - Never Drop Raw Data: The Staging layer filters out invalid rows (like the -99 signal strength). If you realize later that -99 was actually a valid error code, you can easily alter the Dynamic Table to include it, because the raw JSON is safely preserved in the Bronze layer.
- Use Views or Dynamic Tables: Avoid manual
INSERTscripts. Use Declarative pipelines so the staging layer self-maintains.
Summary
- The Raw Zone stores untyped, messy data (
VARIANT) to preserve the exact source format. - The Staging Zone applies schema-on-read extraction, type casting, and string standardization.
- Implement Data Cleansing logic using SQL functions like
INITCAP,TRIM,IFF, andTO_TIMESTAMP. - Always preserve audit columns for data lineage.