Dynamic Database Routing

Dynamic database routing is a multi-tenant architecture pattern where a single dashboard definition runs against different physical databases depending on which tenant is viewing it. Each tenant's data lives in a completely separate database. The BI tool reads a tenant identifier from the user's JWT token and routes the query to the corresponding database at request time.

You build the dashboard once. Every tenant sees the same visualizations, the same filters, the same layout. But each query executes against that tenant's dedicated database, which contains only their data. The routing is invisible to the end user.

The architecture

The setup has three components:

1. One dashboard definition. You build and maintain a single set of dashboards. The SQL, metrics, filters, and layouts are defined once and shared across all tenants.

2. One database per tenant. Each tenant has a dedicated database instance – or at minimum a dedicated database within a shared cluster. The schemas are identical across tenants, but the data is completely separated.

3. A routing mechanism. When a user loads a dashboard, the BI tool extracts the tenant identifier from the JWT claims and resolves it to a database connection. The query runs against that connection. No cross-tenant data is ever in scope.

The tenant-to-database mapping typically lives in a configuration table or environment variable. Adding a new tenant means provisioning a new database, loading the schema, and registering the connection – without touching any dashboard definitions.

When to use it

Dynamic database routing fits specific requirements:

Strict regulatory isolation. Industries like healthcare, financial services, and government often require that tenant data never coexist in the same database. Physical separation satisfies compliance auditors in a way that row-level filters cannot.

Contractual data isolation. Enterprise customers sometimes demand – and pay for – dedicated infrastructure. A separate database per tenant is a concrete guarantee you can point to in a security review.

Different data residency requirements. When tenants require data stored in specific geographic regions, separate databases let you place each tenant's data in the mandated region while serving dashboards from a single application layer.

Acquisition or migration scenarios. When you onboard customers from different source systems, each with its own database, dynamic routing lets you serve dashboards immediately without migrating everything into a shared schema.

How routing works in practice

The flow follows a consistent pattern across BI tools that support it:

1. The host application authenticates the user and generates a JWT containing a tenant identifier – typically a claim like tenant_id or org_id.
2. The embedded BI component receives the token and validates it.
3. The BI tool looks up the database connection associated with that tenant identifier.
4. All queries for that session execute against the resolved database connection.
5. Results render in the shared dashboard template.

The key technical detail is connection management. The BI tool needs to maintain connection pools for each tenant database. With ten tenants, this is trivial. With a thousand, connection pool management becomes a real operational concern.

Advantages

Complete physical isolation. There is no possibility of one tenant's query returning another tenant's rows. The data doesn't exist in the same database. This is the strongest isolation guarantee available.

Simple security model. You don't need row-level security rules or tenant filter columns. Isolation is structural. If the routing is correct, the security is correct.

Independent scaling. Heavy-use tenants can get larger database instances. You can tune indexes, materialized views, and resources per tenant without affecting others.

Tradeoffs

The cost is operational complexity. Every tenant database needs schema migrations applied independently. Monitoring multiplies – you're watching N databases instead of one. Backup and disaster recovery plans cover N targets. Connection pool sizing requires planning, especially if your BI tool holds persistent connections.

Compare this with dynamic schema routing, which offers a lighter-weight isolation model within a shared database. And for a broader view of tenant separation strategies, see multi-tenant analytics.

The decision comes down to whether your isolation requirements justify the operational overhead. If compliance or customer contracts demand physical separation, database routing is the correct architecture. If schema-level separation is sufficient, the additional infrastructure burden is rarely justified.