The federated query times out and everyone has an opinion
A dashboard joins a warehouse fact table to a table in an operational database through a federated query engine. It was fast when it was built. It now times out, and it is also showing up on the source database's slow query list.
What you would do first
Answer before revealing anything. The value of the exercise is entirely in committing to a diagnosis you can be wrong about.
- 1Read the plan and establish exactly what is pushed down and what is not. A join across two systems is almost never pushed, which is usually the whole answer.
- 2Measure what the source is actually asked to return, and what that costs the operational system in capacity and lock behaviour.
- 3Ask what freshness the consumer needs from the operational side. Federation is often chosen for freshness that nobody requires.
- 4Check whether the operational table is small and slow-changing, which would make a periodic copy trivially correct.
What is actually going on
The trap
The fix that looks right. Read it even if you got the answer — especially then.
Give the federation engine more workers and raise the timeout. The query completes again, the volume crossing the network is unchanged, and the operational database — which was never the thing you scaled — is now asked for the same large result more reliably and at a higher concurrency.