Dynamic Schema Routing

Dynamic schema routing is a multi-tenant architecture pattern where queries route to different schemas within the same database based on the viewing tenant. Each tenant gets a dedicated schema – a namespace containing its own set of tables – while sharing a single database instance. The BI tool resolves the target schema from JWT claims and sets the schema search path before executing queries.

This gives you logical data isolation without the operational weight of maintaining separate databases per tenant.

The architecture

Three elements define the pattern:

1. One shared database. All tenants live in the same database instance. Connection management stays simple – one connection pool, one set of credentials, one monitoring target.

2. One schema per tenant. Each tenant's tables exist inside a dedicated schema. Schema tenant_acme contains the same table structure as tenant_globex, but the data is completely separate. In PostgreSQL terms, these are distinct namespaces within the same database. In BigQuery, they're separate datasets. In Snowflake, separate schemas within a database.

3. Schema-level routing. When a user loads a dashboard, the BI tool reads the tenant identifier from the JWT, maps it to a schema name, and sets the query's search path accordingly. All table references resolve within that tenant's schema.

The dashboard definition references table names without schema prefixes – just orders instead of tenant_acme.orders. The routing layer prepends the correct schema at query time. One dashboard template serves every tenant.

How it differs from database routing

Dynamic database routing places each tenant in a separate database. Schema routing places them in separate schemas within one database. The practical differences are significant:

Operational simplicity. Schema routing means one database to back up, one to monitor, one to apply migrations to. Adding a tenant means creating a schema and copying the table structure – a lightweight operation compared to provisioning a new database.

Shared infrastructure costs. All tenants share compute, memory, and storage resources at the database level. This reduces per-tenant infrastructure cost, especially for smaller tenants with low query volume.

Weaker isolation boundary. A schema is a logical boundary rather than a physical one. A misconfigured query or a bug in the routing logic could theoretically cross schema boundaries. Database-level routing eliminates that class of error entirely.

Connection pool efficiency. One connection pool serves all tenants. With database routing, the BI tool needs a separate pool – or at least a separate connection configuration – per tenant, which becomes a bottleneck at scale.

When to use schema routing

Schema routing fits when you need meaningful data separation without the overhead of per-tenant infrastructure:

Cost-conscious multi-tenancy. If you're serving hundreds or thousands of tenants – especially smaller ones – a dedicated database per tenant becomes expensive and operationally painful. Schema routing keeps costs proportional.

Moderate compliance requirements. Many regulatory frameworks accept logical separation when paired with proper access controls. If your auditor accepts schema-level isolation with enforced query routing, you avoid the complexity of physical separation.

Rapid tenant provisioning. Creating a schema and populating it with table definitions takes seconds. Creating a full database takes longer and often involves infrastructure automation. If your product onboards tenants frequently, schema routing reduces provisioning friction.

Shared reference data. Some architectures maintain shared tables – product catalogs, configuration data, lookup tables – alongside tenant-specific data. A shared database makes it easy to join tenant tables with shared reference tables in a single query.

How routing works in practice

The mechanics mirror database routing but operate at the schema level:

1. The host application generates a JWT with a tenant identifier claim.
2. The BI tool validates the token and extracts the tenant ID.
3. A mapping resolves the tenant ID to a schema name – either through a lookup table or a naming convention like tenant_{id}.
4. The BI tool sets the schema search path for the query session.
5. All table references in the dashboard's SQL resolve within that schema.

The critical implementation detail is ensuring the search path is set before every query – at request time, rather than only at connection time. Connection pooling can reuse connections across tenants, so the schema context must be applied per request.

Tradeoffs

Schema routing trades isolation strength for operational simplicity. If a regulatory requirement or customer contract specifically demands physical database separation, schema routing won't satisfy it. For everything else, it's the lighter-weight path. See multi-tenant analytics for a broader comparison of tenant separation strategies.