DEV Community

vinicius fagundes
vinicius fagundes

Posted on

Building Multi-Tenant Analytics with Snowflake RBAC and Sigma Computing: Part 3

#analytics #snowflake #dataengineering #datascience

Introduction

Welcome back! In Part 2, we established the foundational architecture for our multi-tenant analytics platform with Snowflake RBAC and DBT transformations. Now let's make it production-ready.

In this part, we'll cover the operational aspects that transform your proof-of-concept into a scalable, monitored, and cost-effective production system.

1. Advanced Snowflake Cost Controls

1.1 Resource Monitors for Client Boundaries

One of the biggest concerns with multi-tenant systems is cost control. Here's how to set boundaries:

CREATE RESOURCE MONITOR client_usage_monitor
    WITH CREDIT_QUOTA = 100
    FREQUENCY = MONTHLY
    START_TIMESTAMP = IMMEDIATELY
    TRIGGERS
        ON 75 PERCENT DO NOTIFY
        ON 90 PERCENT DO SUSPEND
        ON 100 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE analytics_warehouse
    SET RESOURCE_MONITOR = client_usage_monitor;
Enter fullscreen mode Exit fullscreen mode

1.2 Query Tags for Client Attribution

Track which clients are consuming resources:

ALTER SESSION SET QUERY_TAG = '{"client": "JON_DOE_TECH", "department": "analytics", "priority": "high"}';

CREATE OR REPLACE PROCEDURE set_client_query_tag()
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
    var role = snowflake.execute({sqlText: "SELECT CURRENT_ROLE()"}).getResultSet();
    role.next();
    var currentRole = role.getColumnValue(1);

    if (currentRole.includes('client_')) {
        var clientName = currentRole.replace('client_', '').replace('_role', '').toUpperCase().replace('_', ' ');
        var tag = JSON.stringify({
            client: clientName,
            session_type: 'dashboard',
            timestamp: new Date().toISOString()
        });

        snowflake.execute({sqlText: `ALTER SESSION SET QUERY_TAG = '${tag}'`});
        return `Query tag set for client: ${clientName}`;
    }

    return 'No client-specific tag needed';
$$;
Enter fullscreen mode Exit fullscreen mode

2. Sigma Computing Integration

2.1 Connection Configuration 

const sigmaConfig = {
  connectionType: 'snowflake',
  host: 'your-account.snowflakecomputing.com',
  warehouse: 'ANALYTICS_WAREHOUSE',
  database: 'ANALYTICS_PLATFORM',
  schema: 'GOLD',
  authMethod: 'oauth',
  defaultRole: 'CLIENT_READONLY_ROLE'
};
Enter fullscreen mode Exit fullscreen mode

2.2 Dynamic Schema Selection in Sigma

Sigma automatically respects your Snowflake security:

CREATE OR REPLACE VIEW gold.dynamic_client_data AS
SELECT
    'JON_DOE_TECH' as client_name,
    *
FROM gold_client_jon_doe_tech.dashboard_metrics
WHERE CURRENT_ROLE() = 'client_jon_doe_tech_role'

UNION ALL

SELECT
    'ACME_INDUSTRIES' as client_name,
    *
FROM gold_client_acme_industries.dashboard_metrics  
WHERE CURRENT_ROLE() = 'client_acme_industries_role';
Enter fullscreen mode Exit fullscreen mode

3. Ensuring Sigma Dashboard Security with JWT and IP Validation

Embedding analytics securely in your application is crucial. Our approach combines JWT-based authentication and IP validation to protect embedded Sigma dashboards.

3.1 JWT Authentication with FastAPI

Generate secure JWT tokens to manage dashboard access, encoding client identity and permissions, with quick expiration for enhanced security.

3.2 IP Validation

Validate initial client request IP addresses to prevent token misuse.

3.3 Client Integration Samples

We provide easy-to-follow code samples for diverse client environments:

Python:

import requests
response = requests.get('https://api.yourservice.com/embed-dashboard', headers={'Authorization': 'Bearer YOUR_JWT_TOKEN'})
Enter fullscreen mode Exit fullscreen mode

Go:

package main

import (
    "fmt"
    "net/http"
)

func main() {
    req, _ := http.NewRequest("GET", "https://api.yourservice.com/embed-dashboard", nil)
    req.Header.Set("Authorization", "Bearer YOUR_JWT_TOKEN")
    client := &http.Client{}
    resp, _ := client.Do(req)
    defer resp.Body.Close()
    fmt.Println(resp.Status)
}
Enter fullscreen mode Exit fullscreen mode

Elixir:

HTTPoison.get!("https://api.yourservice.com/embed-dashboard", ["Authorization": "Bearer YOUR_JWT_TOKEN"])
Enter fullscreen mode Exit fullscreen mode

JavaScript:

fetch('https://api.yourservice.com/embed-dashboard', {
  headers: { 'Authorization': 'Bearer YOUR_JWT_TOKEN' }
}).then(res => res.json()).then(console.log);
Enter fullscreen mode Exit fullscreen mode

3.4 Dashboard Visual Examples

Below are examples of two different Sigma dashboards:

  • Sales Distribution Pie Chart: Represents sales by region or product category.
  • Monthly Performance Bar Chart: Shows performance metrics like revenue or customer acquisition over time.

These visualizations enhance client insights and facilitate informed decision-making.

Conclusion

✅ Security | ✅ Scalability | ✅ Cost Efficiency | ✅ Developer Productivity | ✅ Compliance

Next Steps

  • Start Small: Implement with 2-3 pilot clients first
  • Monitor Closely: Set up alerts on cost and performance metrics
  • Iterate: Gather user feedback and refine the data models
  • Scale Gradually: Add more clients as you validate the architecture

Top comments (0)