operational-vs-analytical-splitter

name

operational-vs-analytical-splitter

description

Decide which workloads must leave the operational (transactional) store and how — classify queries by shape (OLTP point reads/writes vs OLAP scans/aggregations), find what is actually hurting the transactional path (long scans, lock pressure, dashboard fan-out, export jobs), choose the offload mechanism per workload (read replica, CDC into a warehouse/lake, materialized/pre-aggregated views, or a cache) against each consumer's stated freshness tolerance, and produce the split boundary with a staged cutover and a stop-doing list for the operational store. The DECISION skill: destination design is warehouse-lake-architect, CDC transport design is streaming-event-architect, and single-query tuning is query-plan-reader. Use when reports/dashboards/exports slow production, when analytics queries hit the primary, or when deciding replica vs warehouse vs cache for a read workload. Do NOT use to design the warehouse itself or to fix one slow query.

Operational vs Analytical Splitter

Purpose

The operational store exists to serve transactions in milliseconds; the moment dashboards, exports, and month-end reports run against it, both masters are betrayed — checkout latency inherits the reporting scan, and reports still time out. This skill produces the split decision: which workloads stay on the transactional path, which leave, what mechanism carries each one out (replica, CDC into an analytical store, materialization, cache), and what freshness each consumer actually tolerates — because the whole tradeoff is latency-of-truth vs load. It decides and sequences the split; it does not design the warehouse it may prescribe (warehouse-lake-architect), the CDC pipeline it may prescribe (streaming-event-architect), or the one bad query that may make the whole split unnecessary (query-plan-reader).

Use When

• Use when: reports, dashboards, admin exports, or analytics queries run against the operational store and transactional latency suffers — or will, at projected growth.
• Use when: deciding where a new read-heavy workload (customer-facing analytics, usage metering views, search-adjacent listing) should live.
• Use when: choosing between read replica, warehouse/CDC, materialized views, and cache for a specific workload, and the tradeoffs need stating.
• Use when: an incident review shows a reporting query caused transactional degradation and a standing boundary is needed.
• Do NOT use when: ONE query is slow and fixable — read its plan first (query-plan-reader); a missing index does not justify a warehouse.
• Do NOT use when: the split is decided and the analytical destination needs designing — zones, modeling, formats are warehouse-lake-architect.
• Do NOT use when: the split is decided and the CDC/event transport needs designing — that is streaming-event-architect.
• Do NOT use when: the pressure is write-path scaling — sharding or partitioning the operational store itself for write/size throughput is data-partitioning-sharding-strategist; re-sharding for tenant ISOLATION (pooled↔silo) is multi-tenant-data-architect. Offloading reads does not save a saturated write path.
• Do NOT use when: the workload is caching a computed result inside the request path — caching-strategy-designer owns cache design; this skill may PRESCRIBE "cache" as a mechanism and then hands over.

Inputs to Inspect

1. The workload inventory on the operational store: top queries by total time and by peak concurrency, scheduled jobs, exports, dashboard refresh patterns — from query statistics, slow-query logs, or the store's activity views.
2. Evidence of interference: lock waits, replication lag spikes, transactional p99 correlated with reporting windows, connection-pool exhaustion during refreshes.
3. Each analytical consumer's freshness tolerance — asked, not assumed: real-time (seconds), operational reporting (minutes), BI/finance (hours/daily). This single number drives mechanism choice.
4. Data shape and volume: table sizes, growth rate, whether analytical queries need full history or recent windows.
5. What already exists: replicas and their lag, any warehouse/lake, any CDC, materialized views — the split should extend, not duplicate.
6. Tenant posture: whether analytical workloads are per-tenant-facing (customer dashboards) or internal — it changes isolation and freshness requirements.

Workflow

1. Classify every significant workload by shape. OLTP: point reads/writes, short transactions, indexed access. OLAP: scans, aggregations, joins across large ranges, window functions. Hybrid symptoms (an "operational" endpoint doing a 9-table aggregate) get flagged individually — they are the usual culprits.
2. Attribute the pain. Tie observed degradation to specific workloads with evidence (lock waits, IO saturation windows, correlated p99). A split justified by vibes moves work without moving the problem. If the evidence points at ONE fixable query, stop — route to query-plan-reader and re-evaluate after the fix.
3. Record freshness tolerance per consumer. For each analytical consumer: the staleness it can actually accept, in seconds/minutes/ hours, with the owner's sign-off. "Real-time" claims get interrogated — what decision changes in under a minute?
4. Choose the mechanism per workload from the decision table (details in references):
   • Read replica: same schema/engine, near-real-time, cheap to adopt — but carries the same query-shape mismatch and replica lag caveats.
   • CDC → analytical store: schema reshaped for analytics, history retained, minutes-fresh — the durable answer for true OLAP; prescribes streaming-event-architect (transport) and warehouse-lake-architect (destination).
   • Materialized/pre-aggregated views: seconds-to-minutes fresh for KNOWN query shapes; refresh cost and staleness stated per view.
   • Cache: for repeated identical reads with tolerance for staleness — prescribe and hand to caching-strategy-designer. State per workload: mechanism, freshness delivered vs tolerated, and why not the cheaper alternative.
5. Draw the split boundary. The workloads that STAY (transactional, plus operational lookups that genuinely need current-transaction visibility) and the stop-doing list: workloads banned from the primary once their offload lands, enforced by review convention and, where available, by connection/role separation (reporting roles that cannot reach the primary).
6. Sequence the cutover. Order by pain × ease; each move gets: dual-run window (old and new path compared), consumer sign-off on parity and freshness, then the primary-side access removed. Standing parity/freshness checks after cutover are data-quality-monitor-designer scope; a one-time heavy backfill is a data-migration-runbook-author runbook.
7. State the residual load honestly. What remains on the operational store after the split, projected headroom, and the trigger for the NEXT split review (growth threshold or new workload class).

Mechanism decision table, freshness-tier definitions, and the workload classification worksheet: references/split-decision-worksheet.md.

Output Format

OPERATIONAL/ANALYTICAL SPLIT DECISION — <store/system>
Workloads classified: <N OLTP-stay / M offload candidates, evidence per candidate>
Pain attribution:     <symptom → workload, with evidence source>
Per-workload verdict:
  <workload>: mechanism=<replica|CDC→analytical|materialized|cache|stay>
  freshness: tolerated=<t> delivered=<d>  rationale=<why not cheaper option>
  handoff=<warehouse-lake-architect | streaming-event-architect | caching-strategy-designer | n/a>
Split boundary:  stays=<list> stop-doing=<workloads banned from primary + enforcement>
Cutover order:   <sequenced moves, each with dual-run + sign-off gate>
Residual:        <load remaining on primary; next-review trigger>
Escape checked:  <the one-bad-query check was performed; result>

Validation Checklist

• Every offload candidate has evidence tying it to real or projected transactional pain — no split by fashion.
• The one-bad-query escape was checked before any architecture: if a plan fix dissolves the problem, the verdict says so.
• Every consumer has a stated, owner-confirmed freshness tolerance; every mechanism choice delivers within it.
• Each mechanism choice explains why the CHEAPER option was rejected (replica before warehouse, view before pipeline).
• The stop-doing list exists with an enforcement mechanism — a split that lets new dashboards quietly return to the primary is temporary.
• Cutover moves have dual-run windows and consumer sign-off, not flag-day switches.
• Handoffs are named per prescription; this document designs no warehouse zones, no CDC topology, no cache keys.

Gotchas

• Replica lag is a feature of physics, not a defect: consumers moved to a replica WILL occasionally read stale data — if a consumer cannot tolerate that, it either stays or needs the read-your-writes cases explicitly handled. Ask before moving.
• The replica inherits the query-shape problem: a 9-table aggregate that hammers the primary hammers the replica too, and its lag then poisons every other replica consumer. OLAP shapes need an OLAP home, not a copy of the OLTP one.
• "Real-time dashboard" usually means "refreshed while I watch": most claimed real-time needs dissolve under the what-decision-changes question into minutes — the cheapest mechanisms live there.
• The export nobody owns: scheduled full-table exports are the classic invisible primary load. The workload inventory step exists to find them; several usually die outright instead of moving.
• Hybrid endpoints resist the split: an API response mixing current transactional state with aggregates needs decomposing (current from primary, aggregate from the analytical path) — flag these, don't force them wholesale into either side.
• Materialized views quietly become load: refresh cost scales with frequency × view count; a hundred eager views recreate the problem they solved. Refresh budgets are part of the verdict.
• The split leaks back: without the stop-doing enforcement, next quarter's dashboard lands on the primary again because it was easiest. Role/connection separation outlasts good intentions.

Stop Conditions

• The evidence shows the pain is ONE query with a broken plan → stop the split; route to query-plan-reader; re-evaluate only if pain persists after the fix. Do not prescribe architecture where an index suffices.
• No freshness tolerance can be obtained for a consumer (owner unknown or unwilling to commit) → hold that workload's verdict; a mechanism chosen against an assumed tolerance will be relitigated as an incident.
• The store's activity/query statistics are unavailable and interference cannot be evidenced → stop and say what instrumentation is needed first; splitting blind moves cost without proof of benefit.
• The real pressure is write-path/throughput scaling → route to data-partitioning-sharding-strategist (shard key, range/hash partitioning, resharding a hot key); tenant-ISOLATION re-sharding (pooled↔silo) is multi-tenant-data-architect. Offloading reads will not save a saturated write path.
• Asked to also design the destination warehouse, the CDC pipeline, or the cache in the same pass → decline those slices and hand off; the split decision must stay reviewable on its own.

Supporting Files

• references/split-decision-worksheet.md — the mechanism decision table with tradeoff rows, freshness-tier definitions, workload classification worksheet, and the stop-doing enforcement options.
• evals/evals.json — behavior cases including the one-bad-query escape and the dashboard-on-primary refusal.
• evals/trigger-evals.json — discrimination against warehouse-lake-architect, streaming-event-architect, query-plan-reader, and caching-strategy-designer.