The Challenge: Ensuring Data Isolation in a Multi-Tenant Environment
A fundamental challenge for any platform offering embedded analytics is guaranteeing data separation between tenants. Stakeholders must have confidence that Customer A can never access Customer B’s data.
I consulted with a client facing this exact issue. They had selected Sigma as their embedded analytics platform and needed to develop a secure, scalable solution for their dashboards. The immediate objective was to establish a proof of concept where users could log into a portal, access a shared dashboard, and only view their respective data.
Our solution needed to meet the following:
Security: Achieve zero possibility of data leakage, with security enforced at the foundational level.
Performance: Ensure dashboards load quickly, even when querying complex, large datasets.
Scalability: Design an architecture that allows for easy, repeatable onboarding of new customers without requiring modifications to the core data model.
Maintainability: Implement a security model that is straightforward for the development team to understand and manage.
Evaluating RLS Options in Sigma Computing
User Attributes: Custom attributes can be passed to Sigma (either assigned within the platform or dynamically via JWT claims during embedding). These attributes are then referenced in formulas or custom SQL to filter data.
Team-Based RLS: This involves using functions like
CurrentUserInTeam()within the data model to filter rows based on a user’s membership in a specific Sigma team.User/Email-Based RLS: The
CurrentUserEmail()function is used to filter data by matching the current user’s email address against user designations within the data columns.
Why Standard Filtering Approaches Were Insufficient
When evaluated against our core requirements, the standard filtering methods presented significant drawbacks:
Performance Concerns (Methods 2 & 3): These methods often result in data being queried from the warehouse before the filter is applied. For large tables, this slows down dashboard load times due to the increased volume of data processed by Sigma.
Maintenance Complexity (Methods 2 & 3): Basing security on user emails or teams within the data model increases the administrative burden when user roles or organizational structures change.
Inconsistent Data Models and Values: Our dashboards sourced data from various models where not only were the client identifier field names different, but the client name values themselves were inconsistent. Implementing
WHEREclause filtering across disparate data models would require complex, dashboard-specific logic, creating a significant maintenance liability instead of a unified security approach.
We required a solution that provided physical data isolation at the database level – a method that would work consistently regardless of the underlying data structure.
Our Solution: Schema-Level Access Control with Dynamic References
We ultimately landed on an approach that leverages the security and governance capabilities of our data warehouse, Snowflake, and combines it with the dynamic reference functionality of Sigma’s User Attributes.
Phase 1: Database Architecture and RBAC in Snowflake
We built an architecture that isolated each client’s data and leveraged Snowflake’s Role Based Access Control (RBAC) to enforce security.
1. Defining Client-Specific Schemas
We established a separate Reporting Database.
Within this database, we created isolated schemas for each tenant (e.g.,
client_a_gold,client_b_gold). This isolation was extended to regional requirements for some clients.The data engineering team implemented pipelines (utilizing dbt) that populate tables within these schemas. Each schema’s tables are physically filtered to contain only the data relevant to that specific client or region.
2. Implementing Snowflake RBAC
We utilized Snowflake’s Role-Based Access Control (RBAC) to enforce security at the physical data layer.
We created specific Snowflake roles for each client.
Crucially, these roles were granted
SELECTaccess only to their corresponding schema.This step provides the foundational security layer: if a user’s underlying role attempts to query any schema outside of its granted permissions, the request is denied by Snowflake, ensuring data isolation is enforced at the source.
Phase 2: Dynamic Referencing in Sigma
We used Sigma’s User Attributes to dynamically connect the logged-in user to their correct, isolated schema.
3. Building Data Models with Dynamic Schema References
In Sigma, the data models were built using custom SQL that references a User Attribute:Â
SELECT * FROM prod_db.{{raw
system::CurrentUserAttributeText::client_schema_ua}}.table
The {{#raw system::CurrentUserAttributeText::client_schema_ua}} syntax is the key mechanism, instructing Sigma to inject the corresponding schema name from the client_schema_ua attribute.
4. Configuring User Attributes and Teams
We defined a custom Sigma User Attribute named
client_schema_ua.We created Sigma Teams for each client.
The
client_schema_uaattribute value was assigned to the corresponding tenant Team (e.g., the “Client A Team” is assigned the valueclient_a_gold).
The End-to-End Flow
User Login: A user authenticates in the application portal, generating an embed JWT.
Sigma Context: Sigma authenticates the embed user and links them to the appropriate Team.
Attribute Retrieval: The user’s context resolves to the
client_schema_uaattribute value (e.g.,client_a_gold).Dynamic Query: The dashboard query executes, dynamically resolving to the correct schema:
SELECT * FROM prod_db.client_a_gold.table.Data Isolation: Snowflake executes the query, retrieving data only from the isolated
client_a_goldschema, enforced by its native RBAC.
Advantages of This Architectural Approach
This model provides significant benefits across security, performance, and maintenance:
Security Through Physical Isolation: Security is enforced by the database platform and its access controls. The reliance on physical isolation minimizes the risk of data exposure from a misconfigured application filter.
Superior Performance: Queries operate on pre-filtered, isolated schemas, reducing the volume of data scanned by Snowflake. This eliminates the performance overhead associated with post-query filtering on massive tables.
Scalability and Maintainability: Onboarding a new client becomes a predictable operational task: create the schema and populate the tables in the data layer, then assign the corresponding
client_schema_uaattribute in Sigma. The core dashboards and workbooks remain unchanged.Consistency: The security model is unified. Since security is enforced by referencing the correct schema, inconsistencies in field names or values across different dashboards are neutralized – every dashboard automatically connects to the correct, isolated dataset.
One Consideration: Schema Proliferation
A notable trade-off is the proliferation of database schemas, particularly when regional isolation is required. This increases the number of database objects to manage and monitor. However, leveraging robust automation tools like dbt transforms this into a manageable operational cost, which is easily justified by the gains in security and performance.
Conclusion
Effective RLS in embedded analytics requires an architectural approach that prioritizes data isolation at the source. While application-layer filtering might seem simpler initially, it introduces scaling and security risks.
By combining schema-level isolation enforced by Snowflake RBAC with the dynamic querying capabilities of Sigma User Attributes, we established a solution that is demonstrably secure by design and highly performant. This investment in foundational data architecture allows for rapid client onboarding and reliable, quick delivery of embedded analytics.
Want to simplify row-level security at scale?
phData works with teams to translate patterns like schema isolation plus Snowflake RBAC into production-ready embedded analytics solutions in Sigma. We can help you design a secure, high-performing architecture that fits your organization.
