Operational Analytics: Turning Transactional Data into Decisions

Most organisations sit on a gold mine of operational data locked inside their transactional databases — order management systems, inventory platforms, support ticketing tools, logistics databases — that never gets analysed. The reason is rarely a lack of interest; it is a fear of impacting production performance. Running analytical queries against a live OLTP database is a well-documented disaster: complex aggregations compete with write transactions, indexes optimised for point lookups slow down under full-table scans, and operations teams end up with a choice between slow dashboards or slow applications.

Operational analytics on AWS solves this by decoupling the transactional workload from the analytical workload through change data capture, near-real-time replication, and purpose-built analytical serving. This post explains how to build that architecture.

What Operational Analytics Is (and Isn’t)

Operational analytics is distinct from strategic analytics. Strategic analytics answers long-horizon questions — annual revenue trends, multi-year customer cohort performance, strategic market analysis. Operational analytics answers questions that drive same-day or same-hour decisions:

• How many orders are currently awaiting fulfilment, broken down by warehouse?
• Which support tickets have been open for more than 48 hours, by team and priority?
• What is the current inventory coverage for our top-20 SKUs across all distribution centres?
• Which delivery routes are running behind schedule right now?

These questions require data that is minutes to hours old, not days. They are asked by operations managers, supply chain analysts, support team leads, and logistics coordinators — people who need fast answers during their workday, not data scientists running overnight batch jobs.

Extracting Data from OLTP Without Disrupting Production

The safest and most scalable way to get data out of a production Amazon RDS or Aurora database is Change Data Capture (CDC) via AWS Database Migration Service (DMS). DMS reads the database transaction log (binary log for MySQL/Aurora MySQL, WAL for PostgreSQL/Aurora PostgreSQL) and streams every insert, update, and delete event to a target — without executing queries against your application tables.

A typical DMS configuration for operational analytics:

Source: Amazon Aurora PostgreSQL (production)
  ↓
AWS DMS Replication Instance (r5.large)
  ↓
Target: Amazon Kinesis Data Streams (for near-real-time)
     OR Amazon S3 (for batch CDC via DMS full-load + CDC mode)

For the near-real-time path, DMS writes change events to Kinesis in JSON format. Each event includes the operation type (INSERT, UPDATE, DELETE), the table name, the before-image of the row, and the after-image. A downstream Lambda function or Kinesis Data Firehose then transforms and delivers these events to your analytical store.

For the batch path — suitable when you need same-day freshness but not minute-level freshness — DMS performs a full load of your source tables to S3 on initial setup, then continuously appends CDC files (in CSV or Parquet) to S3. A scheduled Glue job processes these CDC files and applies the changes to your analytical layer.

The Analytical Serving Layer: Redshift or Athena?

For operational analytics, Amazon Redshift is typically the better choice over Athena for two reasons. First, operational queries often involve complex multi-table joins across normalised schemas — order headers joined to order lines joined to products joined to customers joined to fulfilment events. Redshift’s columnar storage and query optimiser handle these patterns far better than Athena’s distributed query engine. Second, operational dashboards need sub-second query response times, which requires either SPICE caching in QuickSight or Redshift materialised views — not cold Athena queries that spin up compute for each request.

That said, if your operational data volumes are very large and your queries are simpler aggregations over time-partitioned data, Athena over S3 can work well and at lower cost. The Redshift vs. Athena comparison covers the trade-offs in detail.

A practical Redshift setup for operational analytics uses the following pattern:

1. Staging schema — raw CDC events land here, including the full before/after row images
2. Operational schema — materialised views or derived tables that present the current state of each entity (current inventory levels, open orders, active support tickets)
3. History schema — slowly changing dimension tables that track how entities changed over time, enabling trend analysis on top of the operational data

Building Operational Metrics: A Fulfilment Example

Consider a warehouse operations team that needs to monitor order fulfilment performance throughout the day. Their source system is an order management application running on Amazon Aurora MySQL. The operational schema in Redshift, refreshed every 15 minutes via Redshift stored procedure, looks like this:

-- Materialised view: current open orders by status and warehouse
CREATE MATERIALIZED VIEW ops.mv_open_orders AS
SELECT
  o.warehouse_id,
  w.warehouse_name,
  o.order_status,
  COUNT(*)                                              AS order_count,
  SUM(o.order_value_cad)                               AS total_value_cad,
  AVG(DATEDIFF('minute', o.created_at, GETDATE()))     AS avg_age_minutes,
  MAX(DATEDIFF('minute', o.created_at, GETDATE()))     AS max_age_minutes,
  COUNT(CASE WHEN DATEDIFF('hour', o.created_at, GETDATE()) > 24
             THEN 1 END)                               AS orders_over_24h
FROM staging.orders o
JOIN dim.warehouses w ON o.warehouse_id = w.warehouse_id
WHERE o.order_status NOT IN ('SHIPPED', 'CANCELLED', 'DELIVERED')
GROUP BY 1, 2, 3;

-- Refresh every 15 minutes via Redshift Scheduler

This materialised view gives operations managers a live-ish view of the order queue. The orders_over_24h column drives SLA alerting — QuickSight can be configured to send email or Slack notifications when this value exceeds a threshold.

Handling Schema Changes in Production Systems

One of the trickiest aspects of operational analytics is that production application schemas change — columns get added, renamed, or removed as developers release new application versions. Your CDC pipeline must handle these changes without breaking.

Best practices for schema evolution resilience:

• Capture all columns, not just the ones you currently need. DMS can be configured to capture the full row. Store the raw JSON events in S3 before transformation so you have a complete record even if your Redshift schema does not yet include a new column.
• Use a schema registry. If you are streaming CDC events through Kinesis, AWS Glue Schema Registry validates event schemas against registered definitions and routes schema-breaking changes to a dead-letter queue for manual review rather than silently corrupting downstream data.
• Automate schema migration. When a new column appears in the CDC events and passes validation, a Lambda function can automatically run ALTER TABLE ADD COLUMN in Redshift staging and update the corresponding dbt model. This requires discipline in your column naming conventions.

For teams building their operational analytics on top of a modern data stack, dbt integrates well with this pattern. See the post on dbt and AWS analytics engineering for how to structure models that incrementally process CDC data.

Operational Dashboards: Latency and Access Patterns

Operational dashboards differ from strategic dashboards in their access patterns. A strategic dashboard might be viewed once a day by a VP; an operational dashboard might be viewed by 20 warehouse coordinators every few minutes throughout a shift. This puts different demands on your query layer.

Amazon QuickSight in SPICE mode pre-computes results and serves them from in-memory storage, giving consistent sub-second response times regardless of concurrent users. Schedule SPICE refreshes to match your CDC pipeline refresh cadence — if Redshift materialised views refresh every 15 minutes, SPICE should refresh at the same interval.

For dashboards where 15-minute staleness is not acceptable (real-time logistics, live inventory management), the pattern shifts to a streaming architecture using Kinesis and a purpose-built time-series store like Amazon Timestream, or a direct Redshift connection with aggressive materialised view refresh — effectively polling the database. This is covered in depth in the post on real-time dashboards with Kinesis.

Conclusion

Operational analytics is not about moving all your production data into a warehouse and running reports. It is about establishing a disciplined change data capture pipeline that keeps your analytical layer current, your production systems healthy, and your operational teams equipped to make decisions in the moment — not the day after.

AWS provides the right set of services for this: DMS for non-intrusive CDC, Kinesis for streaming, Glue for transformation, Redshift for fast analytical serving, and QuickSight for dashboard delivery. The architecture is proven, scalable, and maintainable by a small data engineering team.

If your organisation is ready to unlock the operational data sitting in your production databases, contact Infra IT Consulting to design and build an operational analytics pipeline that works at the pace your business demands.