Skip to main content

Business Layer Design (Staged → Curated)

Once data has been cleaned and standardized in the Staging (Silver) layer, it must be transformed into a format that the business can easily understand and query. This is the Business or Curated (Gold) Layer.

Business users do not want to write 12-table JOIN queries to find the total revenue of a region. The Business Layer does the heavy lifting of denormalization and aggregation.

Topics Covered​

  • Purpose of the Business Layer
  • Transformation Patterns (Denormalization & Aggregation)
  • Secure Views for the Serving Layer
  • Best Practices

Purpose of the Business Layer​

The Business Layer serves three primary purposes:

  1. Simplified Querying: Pre-joining facts and dimensions (Star Schemas or One-Big-Table patterns) so BI tools can query them efficiently.
  2. Business Logic Application: Applying complex calculations (e.g., "Net Profit = Gross Revenue - Tax - Logistics Cost") once, rather than forcing every analyst to calculate it themselves.
  3. Security: Serving as the only layer exposed to BI tools, ensuring row-level security and column masking are strictly enforced before data leaves Snowflake.

Transformation Patterns​

Let's assume we are building a Business Layer for a ride-sharing startup in Bengaluru.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_RIDESHARE_DB;
USE DATABASE BEEMS_RIDESHARE_DB;

CREATE SCHEMA IF NOT EXISTS SILVER;
CREATE SCHEMA IF NOT EXISTS GOLD;

-- Mock Staging (Silver) Data
CREATE OR REPLACE TABLE SILVER.TRIPS (TRIP_ID INT, DRIVER_ID INT, CITY_ID INT, DISTANCE_KM NUMBER, FARE_INR NUMBER);
CREATE OR REPLACE TABLE SILVER.DRIVERS (DRIVER_ID INT, NAME STRING, RATING NUMBER);
CREATE OR REPLACE TABLE SILVER.CITIES (CITY_ID INT, CITY_NAME STRING, STATE STRING);

INSERT INTO SILVER.TRIPS VALUES (1, 101, 1, 15.5, 350), (2, 102, 1, 5.0, 150), (3, 101, 2, 45.0, 900);
INSERT INTO SILVER.DRIVERS VALUES (101, 'Arjun Nair', 4.8), (102, 'Priya Sharma', 4.9);
INSERT INTO SILVER.CITIES VALUES (1, 'Bengaluru', 'Karnataka'), (2, 'Mysuru', 'Karnataka');

2. Denormalization (The "One-Big-Table" Pattern)​

While traditional Star Schemas are great, many modern BI tools (like Tableau) and columnar databases (like Snowflake) perform incredibly well when data is denormalized into a wide "One-Big-Table" (OBT). This avoids expensive runtime joins.

We use a Dynamic Table to automatically maintain this denormalized structure.

-- Denormalize Trips, Drivers, and Cities into one table
CREATE OR REPLACE DYNAMIC TABLE GOLD.DENORM_TRIP_FACTS
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
t.TRIP_ID,
t.DISTANCE_KM,
t.FARE_INR,
d.NAME AS DRIVER_NAME,
d.RATING AS DRIVER_RATING,
c.CITY_NAME,
c.STATE
FROM SILVER.TRIPS t
LEFT JOIN SILVER.DRIVERS d ON t.DRIVER_ID = d.DRIVER_ID
LEFT JOIN SILVER.CITIES c ON t.CITY_ID = c.CITY_ID;

3. Aggregation (The "Reporting" Pattern)​

Next, the executive team wants a dashboard showing total revenue by City. We pre-aggregate this from our denormalized table.

-- Pre-aggregate data for executive dashboards
CREATE OR REPLACE DYNAMIC TABLE GOLD.CITY_REVENUE_SUMMARY
TARGET_LAG = '10 minutes'
WAREHOUSE = COMPUTE_WH
AS
SELECT
CITY_NAME,
STATE,
COUNT(TRIP_ID) AS TOTAL_TRIPS,
SUM(FARE_INR) AS TOTAL_REVENUE,
AVG(DRIVER_RATING) AS AVERAGE_RATING
FROM GOLD.DENORM_TRIP_FACTS
GROUP BY CITY_NAME, STATE;

-- Query the Business layer
SELECT * FROM GOLD.CITY_REVENUE_SUMMARY ORDER BY TOTAL_REVENUE DESC;

The Serving Layer: Secure Views​

It is a best practice to never expose tables directly to BI tools or end-users.

Instead, you expose Secure Views that point to the Gold tables. This provides an abstraction layer. If you ever need to rebuild the GOLD.CITY_REVENUE_SUMMARY table from scratch, you can do it without breaking the BI dashboards, because the view remains unchanged.

(To verify your role to ensure you can create views, run SELECT CURRENT_ROLE();)

-- The BI Tool queries this view, NOT the underlying dynamic table
CREATE OR REPLACE SECURE VIEW GOLD.VW_CITY_REVENUE AS
SELECT
CITY_NAME,
TOTAL_TRIPS,
TOTAL_REVENUE
FROM GOLD.CITY_REVENUE_SUMMARY;

Secure Views also hide the underlying SQL logic from users (preventing them from seeing the table names using GET_DDL()), which is critical for security and compliance.

Summary​

  • The Business Layer (Gold) transforms standardized data into analytics-ready datasets.
  • Use Denormalization to pre-join tables, reducing query complexity for end-users.
  • Use Aggregation to pre-compute heavy metrics for high-level dashboards.
  • Always use Secure Views as the final serving layer exposed to BI tools to maintain an abstraction layer and enforce security.