DEV Community

vinicius fagundes
vinicius fagundes

Posted on

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

#analytics #snowflake #dataengineering #datascience

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

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';
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

4.2 Automated Client Onboarding 

import snowflake.connector def create_client_role(client_name, client_code): # Connection and commands here
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)