DEV Community

vinicius fagundes
vinicius fagundes

Posted on

Building Multi-Tenant Analytics with Snowflake RBAC and Sigma Computing: Part 1 - Foundation and Data Modeling

#analytics #dataengineering #datascience #snowflake

Introduction

As a data architect, I've seen countless teams struggle with building custom analytics dashboards that scale across multiple clients while maintaining strict data isolation. After implementing this architecture for several enterprise clients, I want to share the complete technical approach that eliminates 80% of custom dashboard complexity.

This post covers the end-to-end implementation of a multi-tenant analytics platform using Snowflake's RBAC system with Sigma Computing, including production-ready code samples and best practices.

Architecture Overview 

┌─────────────┐    ┌──────────────┐    ┌─────────────┐    ┌─────────────┐
│   MySQL     │───▶│  Snowflake   │───▶│    DBT      │───▶│   Sigma     │
│(Production) │    │  (Raw Layer) │    │(Transform)  │    │(Dashboards) │
└─────────────┘    └──────────────┘    └─────────────┘    └─────────────┘
                            │
                            ▼
                   ┌─────────────────┐
                   │   Gold Layer    │
                   │ (Business Ready)│
                   └─────────────────┘
Enter fullscreen mode Exit fullscreen mode

1. Snowflake RBAC Foundation

1.1 Creating the Foundation: Roles and Users

Assumptions: You have basic Snowflake admin access and understand role hierarchies. 


CREATE ROLE client_readonly_role;
CREATE WAREHOUSE analytics_warehouse WITH WAREHOUSE_SIZE = 'MEDIUM' AUTO_SUSPEND = 60;

CREATE ROLE client_jon_doe_tech_role;
CREATE ROLE client_acme_industries_role;
CREATE SCHEMA gold_client_jon_doe_tech;
CREATE SCHEMA gold_client_acme_industries;

GRANT ROLE client_readonly_role TO ROLE client_jon_doe_tech_role;
GRANT USAGE ON WAREHOUSE analytics_warehouse TO ROLE client_readonly_role;<br><br>
Enter fullscreen mode Exit fullscreen mode

2. Row-Level Security Implementation

Assumption: Your silver layer already has a client_code column for filtering. 


CREATE TABLE silver.user_client_mapping (
    username STRING,
    client_code STRING,
    access_level STRING
);

CREATE OR REPLACE SECURE VIEW gold.customer_metrics AS
SELECT cm.*
FROM silver.customer_metrics cm
JOIN silver.user_client_mapping ucm 
    ON cm.client_code = ucm.client_code
WHERE ucm.username = CURRENT_USER();
Enter fullscreen mode Exit fullscreen mode

This automatically filters data based on who's logged in. No application-level security needed!

3.1 DBT Profile Configuration 

# profiles.yml
analytics_platform:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      password: "{{ env_var('SNOWFLAKE_PASSWORD') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      database: analytics_platform
      warehouse: etl_warehouse
      schema: silver
      threads: 8
      keepalives_idle: 240
Enter fullscreen mode Exit fullscreen mode

3.2 Client-Specific Gold Layer Models 

{{ config(
    materialized='table',
    pre_hook="USE WAREHOUSE etl_warehouse"
) }}

WITH client_revenue AS (
    SELECT 
        client_code,
        DATE_TRUNC('month', order_date) as month,
        SUM(revenue) as monthly_revenue,
        COUNT(DISTINCT customer_id) as unique_customers,
        AVG(revenue) as avg_order_value
    FROM {{ ref('silver_orders') }}
    WHERE order_date >= DATEADD('month', -12, CURRENT_DATE())
    GROUP BY client_code, DATE_TRUNC('month', order_date)
),

revenue_trends AS (
    SELECT 
        *,
        LAG(monthly_revenue) OVER (
            PARTITION BY client_code 
            ORDER BY month
        ) as prev_month_revenue,
        (monthly_revenue - LAG(monthly_revenue) OVER (
            PARTITION BY client_code 
            ORDER BY month
        )) / NULLIF(LAG(monthly_revenue) OVER (
            PARTITION BY client_code 
            ORDER BY month
        ), 0) * 100 as growth_rate
    FROM client_revenue
)

SELECT 
    client_code,
    month,
    monthly_revenue,
    unique_customers,
    avg_order_value,
    prev_month_revenue,
    ROUND(growth_rate, 2) as growth_rate_percent
FROM revenue_trends
ORDER BY client_code, month DESC
Enter fullscreen mode Exit fullscreen mode

3.3 Macro for Client-Specific Filtering 

{% macro get_client_filter(column_name='client_code') %}
    {% if var('client_filter', none) %}
        WHERE {{ column_name }} = '{{ var('client_filter') }}'
    {% endif %}
{% endmacro %}

SELECT 
    customer_id,
    client_code,
    revenue,
    signup_date
FROM {{ ref('silver_customers') }}
{{ get_client_filter() }}
Enter fullscreen mode Exit fullscreen mode

Key Takeaways for Part 1

At this point, you've established:

  1. Secure Foundation: Client-specific roles with proper inheritance;
  2. Automated Security: Row-level security that works without application logic;
  3. Scalable Data Models: DBT transformations that handle multiple clients efficiently;
  4. Flexible Filtering: Macros that make client-specific data processing simple.

What's Next?


In Part 2, we'll dive into the advanced features that make this architecture production-ready:

Advanced Snowflake security features and cost controls
Sigma Computing integration and dynamic dashboards
Monitoring, observability, and performance optimization
Production deployment strategies and automated client onboarding

The foundation we've built here ensures that your data is secure and your transformations are maintainable. Part 2 will show you how to operationalize this at scale.

Top comments (0)