How would you implement query routing in a federated data mesh?

Query routing in a federated data mesh is the art of sending each user query to the right data products and the right engines without centralizing all the data. Think of it as air traffic control for analytics. The router interprets intent, finds authoritative sources, respects policy, and executes a plan that minimizes data movement while meeting latency and cost goals. Mastering this topic shows strong distributed systems instincts in any system design interview.

Why It Matters

A data mesh distributes ownership across domains. That unlocks speed, but it also fragments storage engines, schemas, and policies. Query routing reconnects these islands. Done well, it avoids massive data copies, cuts query times, and enforces governance like data residency and row level access. In interviews it signals that you can design scalable architecture that works with real organizational constraints rather than ignoring them.

How It Works Step by Step

1. Intake and parsing The router accepts a query from a BI tool or service. It authenticates the caller, captures purpose and sensitivity tags, and parses the statement into a logical plan. For non SQL interfaces, the router maps the request to a canonical logical plan so later steps remain consistent.

2. Semantic resolution with a catalog Logical table names map to data products. A metadata catalog stores schemas, ownership, quality scores, freshness, lineage, cost hints, and residency. The router uses the catalog to find authoritative sources, expand views, and confirm that required columns exist. If multiple candidates exist, the router prefers the freshest source that satisfies policy.

3. Policy evaluation early A policy engine evaluates attribute based access, data residency, consent flags, masking, and row or column rules. Deny early if needed. Otherwise attach filter predicates and projection rules so the execution plan never over fetches sensitive data.

4. Source selection and duplication control For each logical relation, choose a concrete source and engine. If duplicates exist, pick one using a tie break set such as gold quality score, freshness, and residency. Record the choice to support observability and repeatability.

5. Plan decomposition into subqueries Convert the logical plan into a graph of subqueries that can run inside each domain. Push down filters, projections, and partial aggregations so that each domain computes as much as possible locally. This is bring compute to data rather than bring data to compute.

6. Join strategy across domains Cross domain joins can be expensive. The router favors patterns that cut data movement. Examples include semi join with a bloom filter, broadcast of a small dimension table, or local pre aggregation to reduce cardinality before any shuffle. For very large joins, the router can schedule a two phase pipeline that materializes intermediate results in a neutral zone with strict governance.

7. Engine choice and orchestration Some queries run through a federated SQL engine. Others call domain gateways that expose SQL or API endpoints. The router creates an execution DAG, assigns steps to engines, and passes identity context end to end so row level filters and masking apply consistently. Retries and timeouts are set per edge to avoid cascading failures.

8. Data movement with minimal blast radius When data must move, the router prefers columnar and compressed transfer, prunes columns, and uses range or hash partition alignment to slash shuffle cost. It avoids hot partitions by using dynamic partition pruning and by splitting large reads into balanced chunks.

9. Governance, lineage, and audit Every routed query carries a trace id. The mesh emits lineage edges from sources to outputs, logs policy versions used, and captures which sensitive fields were masked or aggregated. This record supports explainability and proves compliance.

10. Caching and reuse The router maintains a result cache and a registry of materialized views. It records stable query fingerprints and invalidates caches on upstream changes. Frequently used cross domain aggregates can be precomputed to turn multi minute joins into seconds.

11. Cost control and fairness The router tracks compute and egress cost per domain and per team. It applies quotas, concurrency controls, and query class prioritization so one heavy request cannot starve core workloads. Budgets and alerts prevent silent cost drift.

12. Observability and feedback loop Metrics include parse to plan time, per step latency, bytes scanned per domain, cache hit ratio, and failure rate by policy rule. The router uses these signals to adapt future source selection and to recommend new materialized views.

Real World Example

Consider a global streaming platform that needs a daily dashboard that joins user watch events, device telemetry, and content metadata. Watch events live in a regional lakehouse. Device telemetry sits in a time series store owned by a platform team. Content metadata lives in a relational catalog. The router reads the logical query, resolves each table to its domain source, pushes filters into each store, aggregates per device upstream, broadcasts small content dimension tables, and performs the final join in a neutral compute layer inside the region where user data originates. The result reaches the dashboard fast, with residency and masking enforced automatically.