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

Introduction

Welcome back! In Part 1, 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;

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';
$$;

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'
};

2.2 Dynamic Schema Selection in Sigma

The beauty of this approach is that 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';

This means one Sigma workbook can serve multiple clients - the data automatically filters based on who's logged in!

3. Monitoring and Observability

3.1 Query Performance Monitoring

CREATE OR REPLACE VIEW operations.client_query_metrics AS
SELECT
    DATE_TRUNC('hour', start_time) as query_hour,
    JSON_EXTRACT_PATH_TEXT(query_tag, 'client') as client_name,
    COUNT(*) as query_count,
    AVG(execution_time) as avg_execution_time_ms,
    SUM(credits_used_cloud_services) as total_credits_used,
    AVG(rows_produced) as avg_rows_returned
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
    AND query_tag IS NOT NULL
    AND JSON_EXTRACT_PATH_TEXT(query_tag, 'client') IS NOT NULL
GROUP BY 1, 2
ORDER BY query_hour DESC, client_name;

3.2 Client Usage Analytics

SELECT
    client_code,
    DATE_TRUNC('day', query_date) as usage_date,
    COUNT(DISTINCT user_name) as active_users,
    COUNT(*) as total_queries,
    SUM(bytes_scanned) / (1024*1024*1024) as gb_scanned,
    AVG(execution_time) as avg_query_time_ms
FROM (
    SELECT
        JSON_EXTRACT_PATH_TEXT(query_tag, 'client') as client_code,
        DATE(start_time) as query_date,
        user_name,
        bytes_scanned,
        execution_time
    FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
    WHERE start_time >= DATEADD('month', -1, CURRENT_TIMESTAMP())
        AND query_tag IS NOT NULL
)
WHERE client_code IS NOT NULL
GROUP BY client_code, usage_date
ORDER BY usage_date DESC, client_code;

4. Production Deployment Strategies

4.1 Environment-Specific Role Management

CREATE ROLE IF NOT EXISTS prod_client_jon_doe_tech_role;
GRANT USAGE ON SCHEMA gold_client_jon_doe_tech TO ROLE prod_client_jon_doe_tech_role;
GRANT SELECT ON ALL TABLES IN SCHEMA gold_client_jon_doe_tech TO ROLE prod_client_jon_doe_tech_role;

4.2 Automated Client Onboarding

import snowflake.connector

def create_client_role(client_name, client_code):
    # Connection and commands here

5. Best Practices for Production

• Performance Optimization
• Security Considerations
• Cost Optimization

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