Skip to main content

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 VARIANT columns.
  • 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:

  1. Type Casting: Everything in JSON is technically a string or generic number. We must cast to explicit Snowflake types (::INT, ::NUMBER).
  2. Standardization: "mumbai" and "DELHI" must be title-cased. "chennai " has trailing whitespace.
  3. Null Handling: The string "null" must be converted to an actual SQL NULL.
  4. 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​

  1. Always Include Audit Columns: Ensure your staging table includes BRONZE_INGESTED_AT or SOURCE_FILE_NAME so you can trace a row back to its origin if data quality issues arise.
  2. 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.
  3. Use Views or Dynamic Tables: Avoid manual INSERT scripts. 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, and TO_TIMESTAMP.
  • Always preserve audit columns for data lineage.