Choosing a Data Store for a Reporting Workload
A retail company has an order system on a relational database that handles about 2,000 writes per second at peak. Finance wants near-real-time dashboards over the same data, plus the ability to run ad hoc queries across three years of history. Today analysts query a read replica directly, and their long-running queries are causing replication lag that affects failover readiness. The order system must keep its transactional guarantees and low write latency. The company wants the analytics workload isolated so it cannot degrade the order path, and it wants the dashboards to refresh within a few minutes. There is a fixed budget and a small platform team. The data includes customer identifiers, so access must be controlled and auditable. The team is open to managed services but wants to avoid operating a cluster themselves.
1. Recommend an architecture that isolates analytics from the transactional system, and justify each component.
Read the worked answer
Stop querying the read replica for analytics. Keep the relational database as the system of record for transactional writes, because it provides the guarantees and latency the order path needs. Stream changes out of it using change data capture into a managed streaming service, then land the data in a managed analytical store or warehouse designed for large scans and ad hoc queries. Dashboards read from the analytical store, so long queries never touch the replication stream and failover readiness is restored. Choose managed services to fit the small platform team: a managed streaming service removes broker operations, and a managed warehouse or query service removes cluster tuning. Refresh latency of a few minutes is achievable with near-real-time ingestion; if true sub-minute freshness were required, the design would need a different serving layer, so state that assumption. For the customer identifiers, encrypt at rest and in transit, restrict access with least-privilege roles, mask or tokenize identifiers in the analytical layer where full values are not needed, and enable query and access logging so auditor questions can be answered.
2. An analyst asks why they cannot simply add a second read replica and keep querying it. Give the reasoning.
Read the worked answer
A second replica adds capacity but does not isolate the workload in the way that matters. Replicas still apply the same write stream, and a heavy analytical query can consume CPU, memory, and I/O on the replica, delaying log application and widening lag. It also duplicates the transactional schema and its operational overhead without giving analysts a model suited to large scans and ad hoc joins. More importantly, it leaves analytics coupled to the order system's availability and maintenance windows, so a replica rebuild or failover still affects reporting and vice versa. The change-data-capture design decouples the two: the transactional system publishes changes and the analytical store absorbs query load independently, so each side can scale and be maintained on its own schedule. The trade-off to acknowledge is added moving parts and some ingestion delay; that is acceptable given the few-minute refresh target, but it should be measured, not assumed.