Snowflake, BigQuery & Databricks · Amazon Redshift

Designing multi-tenant schemas and row-level security in Redshift

awsHardredshiftmulti-tenancyrow-level-securitysecurity-architecture

Interview question

How do you architect a multi-tenant warehouse in Redshift with strict isolation between clients?

How to answer

The short answer

Architecting a multi-tenant analytical warehouse requires balancing strict data isolation with cluster management simplicity and cost efficiency.

Shared schema with Row-Level Security (RLS)

In a unified multi-tenant architecture, all tenants share the same fact and , with each row tagged by a tenant_id column. To prevent cross-tenant data leaks, attach Row-Level Security policies to tables:

SQL
-- Define RLS predicate
CREATE RLS POLICY tenant_isolation_policy
WITH (tenant_id VARCHAR)
USING (tenant_id = CURRENT_SETTING('app.current_tenant', true));

-- Attach to sensitive table
ATTACH RLS POLICY tenant_isolation_policy ON sales_records TO ROLE analyst_role;

When an application connects, it sets the session context variable:

SQL
SET app.current_tenant = 'tenant_enterprise_42';

Redshift automatically rewrites all user queries behind the scenes, injecting WHERE tenant_id = 'tenant_enterprise_42' into every scan. Queries attempting to access other tenants return zero rows.

Column-level security and dynamic masking

To protect personally identifiable information () like phone numbers or credit card tokens:

  • Column-level grants: Grant SELECT(order_id, amount) without granting access to sensitive columns.
  • Dynamic data masking: Apply masking policies that display XXXX-XXXX-1234 for non-privileged roles while revealing full strings to compliance officers.

Separate schema per tenant pattern

For enterprise clients with contractual data segregation requirements, create a dedicated schema per tenant (tenant_a.sales, tenant_b.sales) inside a single cluster. This simplifies per-tenant data drops, backups, and quota tracking, but increases metadata catalog overhead if tenant counts exceed hundreds.