AWS Builder Center
Why Your Ecommerce Data Is Wrong—and How to Fix It

Why Your Ecommerce Data Is Wrong—and How to Fix It

Discover how designing a dual-path architecture—pairing low-latency streaming analytics via Amazon MSK and Redshift with a durable historical recovery pipeline via Amazon S3 and AWS Glue—can eliminate duplicate payments, handle late-arriving events, and finally give you trustworthy business reporting.

Series: Design, Therefore I Exist: Understanding Modern Cloud Architecture (2 articles)

  1. 2
    Why Your Ecommerce Data Is Wrong—and How to Fix It This article
Data engineering

Why Your Ecommerce Data Is Wrong—and How to Fix It

Designing a real-time data platform with Apache Kafka, Amazon Redshift, and AWS—without turning a data problem into an expensive infrastructure project.

At 9:15 on a Monday morning, the Finance team opens the weekend sales report.
Revenue is up 18%. Marketing wants to increase its advertising budget. Operations is preparing inventory for the next promotion. The leadership team is discussing whether the growth justifies expanding into another market.
Then someone compares the dashboard with the payment provider's settlement report.
The numbers don't match.
Some payments appear twice. Several captured payments have no associated order. A few orders are missing from the latest report.
The engineering team checks the infrastructure. Kafka is available. Consumers are running. The Redshift workgroup responds to queries. There are no obvious service outages.
The platform is processing data. It just isn't representing the business correctly.
This is where I would start designing the solution—not with a Kafka cluster, a warehouse, or a list of AWS services, but with a question:
What must the business be able to trust, and how quickly does it need to trust it?

That question determines the architecture, the engineering decisions, and ultimately how much money the company should invest.
For this case study, let's work with a fictional ecommerce company, Northstar Commerce.
The goal is to design a platform that makes sales and payment activity available for analysis within five minutes, supports historical reconciliation, and avoids unnecessary infrastructure costs.
This is a proposed architecture, not a description of an existing production deployment.




Executive Summary

The challenge is not simply to move ecommerce events from operational systems into an analytical warehouse. It is to preserve their business meaning throughout the journey.
The proposed platform uses:
  • Java and Spring Boot for application services and event production.
  • Amazon MSK (Kafka) for event streaming and service decoupling.
  • Amazon Redshift Serverless for near-real-time analytical ingestion and querying.
  • Amazon S3 as the durable event archive.
  • AWS Glue for historical processing and backfills.
  • dbt for analytical transformations and data quality tests.
  • Terraform for infrastructure as code.
  • Amazon CloudWatch for operational monitoring.
The architecture separates streaming ingestion from historical recovery, enforces business-level transaction integrity, and makes cost a design constraint from the beginning.

1. Translate Business Requirements into Engineering Constraints

Northstar has independent services for orders, payments, inventory, and shipping. They exchange information through events, but their analytical data is spread across different operational systems.
The leadership team wants a unified view of sales performance. Finance needs payment figures that can be reconciled against the payment provider. Operations wants visibility into order and inventory changes without waiting for an overnight batch.
The requirements are more specific than simply asking for real-time data.

Business requirement
Engineering implication
Sales data available within 5 minutes
Low-latency ingestion and transformation
No double-counted payments
Business-level idempotency and deduplication
Handle events arriving out of order
Event-time tracking and late-arriving data handling
Historical reconciliation
Durable event archive and replay strategy
Consistent financial reporting
Explicit fact-table grain and business definitions
Controlled infrastructure spending
Serverless services, workload limits, and cost monitoring
I would establish three service-level objectives before implementation:
  1. Analytical freshness: 95% of eligible events available to reporting within five minutes.
  1. Data integrity: no duplicate business transactions in curated payment reporting.
  1. Recoverability: the team can replay archived events and reconcile affected periods.

These are proposed targets to validate with the business, not measured performance claims.
The platform must be reliable enough for financial reporting, but it does not need the complexity of a massive enterprise streaming environment on day one.
That distinction will influence every architectural decision that follows.

2. Technology Selection: What Are We Building With?

The proposed stack is deliberately focused.
Layer
Technology
Responsibility
Application
Java / Spring Boot
Generate business events
Event streaming
Amazon MSK (Kafka)
Decouple services and transport events
Real-time analytics
Amazon Redshift Serverless
Ingest and query streaming data
Historical storage
Amazon S3
Preserve events for replay and reconciliation
Batch processing
AWS Glue
Historical processing and backfills
Transformation
dbt
Build trusted analytical models
Infrastructure
Terraform
Reproducible infrastructure
Observability
Amazon CloudWatch
Monitor service and pipeline health
Each component has a defined responsibility: Java and Spring Boot represent the application services. Kafka decouples producers from consumers. Redshift provides the analytical destination. S3 preserves the original data. Glue supports historical processing, while dbt organizes the business transformations.

I would deliberately avoid introducing Apache Flink, Kubernetes, additional orchestration services, or a separate processing cluster in the first iteration. Not because those technologies are unnecessary in every streaming architecture, but because we have not established a requirement that justifies their operational and financial overhead.

A good architecture is not the one with the most services. It is the one that satisfies the requirements with the fewest unnecessary moving parts.

3. Proposed Architecture: Two Paths, Two Responsibilities

The platform needs both near-real-time analytics and historical recoverability. Trying to make one mechanism solve both problems can create unnecessary coupling.
I would separate the flow into two paths:
  • Streaming path: delivers recent events to Redshift.
  • Archival path: preserves original events in S3 so the engineering team can replay and reconcile data independently of the analytical processing layer.

Architecture flow

flowchart TD
A["Order Service<br/>Java / Spring Boot"]
B["Payment Service<br/>Java / Spring Boot"]
C["Inventory Service"]
D["Amazon MSK<br/>Kafka Topics"]

A --> D
B --> D
C --> D

D --> E["Redshift Streaming Ingestion"]
E --> F["Streaming Materialized Views"]
F --> G["Staging and Validation"]
G --> H["Redshift Curated Models"]
H --> I["dbt Transformations"]
I --> J["Finance / BI / Operations"]

D --> K["Kafka Connect<br/>S3 Sink"]
K --> L["Amazon S3<br/>Raw Event Archive"]
L --> M["AWS Glue<br/>Historical Processing"]
M --> H

N["CloudWatch<br/>Metrics and Alarms"] -.-> D
N -.-> E
N -.-> G
N -.-> H

The streaming path connects Amazon MSK to Redshift through streaming ingestion.
Redshift consumes the topic into a streaming materialized view. This avoids the need to stage streaming records in S3 before they become available in Redshift.
There is an important implementation detail: streaming materialized views are not a replacement for the entire transformation layer. In particular, the MSK streaming materialized view cannot perform the joins required to build our complete order-and-payment model. Those transformations belong downstream.

The historical path has a different responsibility. A separate consumer or connector can archive the original events in S3. AWS Glue can process historical data, while dbt organizes the analytical transformations in Redshift.
This gives the engineering team a way to replay historical events, backfill data, and rebuild models without depending exclusively on the retention window of the Kafka topic.
The two paths are complementary, not redundant.

4. Architecture Decision Records: Making the Trade-offs Explicit

I would document the important decisions as Architecture Decision Records (ADRs).
ADRs are not paperwork for its own sake. They capture the context behind a decision, the alternatives considered, and the conditions under which the team should revisit it.

ADR-001 — Amazon MSK Serverless instead of a self-managed Kafka cluster

Decision: Use Amazon MSK Serverless for the initial streaming platform.
The application services already communicate through Kafka-compatible event contracts. Keeping Kafka reduces the changes required in the producer layer and allows us to use the Kafka ecosystem.
The reason for choosing Serverless is workload uncertainty. We don't yet have enough evidence to justify selecting broker sizes, managing capacity, or operating a self-managed Kafka cluster.
Trade-off: MSK Serverless has its own pricing model. We pay for cluster hours, partition hours, data written, data read, and storage. Serverless does not mean free or automatically inexpensive.
I would revisit this decision when traffic patterns, throughput, retention requirements, and cost per GB are measurable.

ADR-002 — Direct Redshift streaming ingestion instead of a custom ingestion service

Decision: Ingest MSK events directly into Redshift streaming materialized views.
Why not put a Lambda function between Kafka and Redshift?
Because we would be adding another compute layer, another failure boundary, and additional operational responsibilities without a demonstrated requirement for custom processing at that point.
Direct ingestion reduces the number of components in the critical path.
Trade-off: We must respect the capabilities and limitations of Redshift streaming materialized views. Complex joins, business-level deduplication, and dimensional modeling happen downstream.
If the platform later needs sophisticated stream processing, event-time windows, complex stateful enrichment, or extensive cross-stream correlation, I would evaluate Flink or another dedicated processing layer.
Not before.

ADR-003 — Redshift Serverless instead of provisioned Redshift

Decision: Start with Amazon Redshift Serverless.
The workload is not yet predictable enough to justify committing to provisioned cluster capacity. Serverless lets us control the base RPU capacity and pay for compute consumption in RPU-hours.
One RPU provides 16 GB of memory. The initial 4-RPU configuration is available in US East (N. Virginia) and supports smaller workloads, subject to its capacity limitations.
Trade-off: Serverless capacity and usage still require active cost management. We need to measure query duration, RPU consumption, concurrency, and workload peaks.
If usage becomes predictable and continuous, we should compare Serverless with provisioned Redshift and reservation options rather than assuming Serverless will always be cheaper.

ADR-004 — Preserve raw events in S3

Decision: Maintain an independent historical archive of the original Kafka events.
Why?
Because an analytical transformation can be wrong. Business rules can change. A dimension can be modeled incorrectly. A payment provider can correct a transaction.
If we only retain the curated tables, rebuilding the historical result becomes difficult.
S3 gives us a durable source for replay and reconciliation. The archive should include event identifiers, timestamps, source information, and an appropriate partitioning strategy.
Trade-off: Additional storage, connector, and data-management costs. Retention and lifecycle policies must be defined with the business.

ADR-005 — Apply business-level deduplication in the analytical layer

Decision: Use producer idempotence where appropriate, but enforce transaction-level deduplication in the downstream data model.
This is an important distinction.
Kafka producer idempotence protects against duplicates caused by certain producer retry scenarios. It does not guarantee that two separate application-level sends represent different business transactions.
For payments, we need a business key such as the payment provider's transaction identifier, combined with the event type and lifecycle semantics.
The curated model must distinguish a legitimate capture, refund, or partial capture from a repeated representation of the same operation.
We should never blindly deduplicate every event by  payment_id . That could erase legitimate business activity.

ADR-006 — Use dbt for analytical business transformations

Decision: Keep ingestion and analytical modeling separate.
Redshift receives and stages the records. dbt organizes the transformations into tested, understandable models.
That gives the team a place to define fact-table grain, enforce business rules, test relationships, and document how financial metrics are calculated.
Trade-off: The freshness objective now depends on the entire transformation schedule, not only on streaming ingestion.
We must measure the end-to-end pipeline.

5. Test the Design Against the Original Incident

Let's return to the $250 transaction.
The order service publishes:
{
"event_id": "evt-001",
"event_type": "OrderCreated",
"order_id": "ORD-10452",
"customer_id": "C-102",
"order_total": 250.00,
"currency": "USD",
"event_timestamp": "2026-10-27T10:02:00Z"
}

The payment service publishes:
{
"event_id": "evt-002",
"event_type": "PaymentCaptured",
"order_id": "ORD-10452",
"payment_id": "PAY-9081",
"provider_transaction_id": "TXN-77821",
"amount": 250.00,
"currency": "USD",
"event_timestamp": "2026-10-27T10:03:30Z"
}

Now introduce two failures:
  1. The payment event is published twice because of an application-level retry.
  1. The payment event arrives in Redshift before the order event.
The engineering response should be deliberate.

At the producer: configure Kafka idempotence and define stable event contracts. Use an event ID for event identity and a provider transaction ID for business transaction identity.
At ingestion: preserve incoming records and their source metadata. Do not discard a valid payment merely because its order has not arrived.
At the transformation layer: identify repeated payment operations according to their business semantics. Resolve the order relationship when the order becomes available.
At the reporting layer: calculate captured payments independently of order value, and expose unresolved records for reconciliation.

A simplified SQL example for identifying repeated capture records:
WITH ranked_captures AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY provider_transaction_id
ORDER BY event_timestamp DESC, ingested_at DESC
) AS row_num
FROM stg_payment_events
WHERE event_type = 'PaymentCaptured'
)
SELECT *
FROM ranked_captures
WHERE row_num = 1;

This illustrates the pattern, not a complete production implementation. The actual rule must account for provider-specific transaction semantics, corrections, and partial captures.
There is also an important Redshift distinction: streaming ingestion tracks Kafka topic, partition, and offset information to process stream records exactly once at the ingestion level. That does not deduplicate distinct records representing the same business payment.
The expected result for our scenario is one captured payment of $250, correctly associated with the order.
That is what we need to prove with integration tests and reconciliation—not simply that the records arrived.

6. Data Modeling: The Warehouse Must Understand the Business

Once the events reach Redshift, we still need to decide what the business is actually measuring.
I would not build a single wide table by joining every event and dimension together. That approach can multiply records and produce inflated totals.
Instead, I would define the grain of each fact table before writing transformations.
Model
Grain
Business question
 fact_order_items 
One row per order item
What was purchased?
 fact_payments 
One row per defined payment transaction
What was captured, refunded, or authorized?
 dim_customers 
One row per customer version
Who placed the order?
 dim_products 
One row per product version
What product attributes applied at the time?
 dim_date 
One row per calendar date
When did the business event occur?

The distinction between order value and payment value is essential.
An order can be created but never paid. A payment can be authorized and later declined during capture. A captured payment can later be refunded. Recognized revenue may follow accounting rules that differ from the payment timestamp.
The data model must reflect those differences.
This is also where the existing ecommerce warehouse repository provides a useful architectural reference: S3 raw and staging zones, Redshift, dimensional modeling, and dbt transformations.
The streaming extension described here adds a proposed event ingestion path. It should not be confused with functionality already implemented in that repository.

7. The Financial Decision: How Much Will This Cost?

An architecture can be technically elegant and still be a poor business decision if nobody has estimated its operating cost.
Let's put some numbers around the initial design.
For illustration, assume:
  • 5,000 orders per day.
  • Three business events per order on average.
  • Approximately 2 KB per event.
  • Roughly 15,000 events and 30 MB of raw event data per day.
  • Three Kafka topics with six partitions each.
  • One primary streaming consumer.
  • A continuously available analytics service with a 4-RPU Redshift Serverless baseline.
These are planning assumptions, not Northstar's measured traffic.

First-pass monthly cost model

Using the published US East pricing examples referenced for this estimate:
Component
Assumption
Approximate monthly cost
MSK Serverless cluster
$0.75 × 730 hours
$547.50
MSK partitions
18 × $0.0015 × 730 hours
$19.71
Redshift Serverless
4 RPU, $1.50 × 730 hours
$1,095.00
MSK data transfer
Low event volume
Variable
S3 archive
Approximately 0.9 GB raw events/month
Low, plus requests
AWS Glue
Occasional backfills
Variable
MSK Connect S3 sink
Connector worker configuration
Not included
CloudWatch, KMS, networking
Depends on configuration
Not included

Indicative baseline: approximately $1,662/month before variable charges and excluded components.
This number is important because it challenges a common assumption: serverless does not automatically mean inexpensive.
At this traffic level, the fixed hourly charges of an always-on MSK Serverless cluster and Redshift Serverless warehouse dominate the storage cost.
The estimate is not a complete AWS bill or a procurement quote. It excludes the S3 connector workers, Glue execution, CloudWatch ingestion and retention, KMS, networking, data transfer, backups, and other account-specific charges. Actual regional rates and workload behavior must be validated in the AWS Pricing Calculator.

Would I invest $1,662 every month immediately?

No.
I would separate the investment into two phases:
Phase 1 — Architecture validation
Build a limited development environment. Use representative event volumes, test duplicate and out-of-order delivery, validate Redshift ingestion, and measure query and transformation costs.
Destroy resources when they are not being tested. A development environment that is only active during working sessions has a different cost profile from a continuously available production platform.
Phase 2 — Production readiness
Keep the streaming services available according to the actual freshness requirement. Configure the archive, recovery strategy, monitoring, alarms, security, and infrastructure automation. Then compare the measured monthly cost with the business value of having the data available within five minutes.

If the business needs 24/7 freshness, the always-on cost is part of the service requirement. If it only needs fresh analytics during business hours, the operating schedule and architecture may be different.
The financial decision should be based on cost per useful business outcome, not on the number of AWS services deployed.

8. When Should We Change the Architecture?

The first architecture is a hypothesis. Production measurements tell us whether it remains the right one.
I would define explicit triggers for revisiting the ADRs.
Signal
Possible architectural response
Kafka throughput or partition demand grows substantially
Reassess MSK capacity and partition strategy
Complex event-time joins become necessary
Evaluate Flink or another stream-processing engine
Redshift RPU consumption becomes consistently high
Tune queries and models; compare Serverless and provisioned pricing
Historical replay becomes frequent
Reassess S3 retention, formats, and backfill orchestration
Freshness exceeds five minutes
Trace the full pipeline and identify the bottleneck
Payment reconciliation failures increase
Revisit event contracts and business deduplication rules
This is why ADRs matter. They give the team a record of the assumptions behind the original decision and a rational way to change course.
An architecture should evolve because the workload or business requirements changed—not because a new service appeared on the AWS console.

Conclusion: Engineering Is the Discipline of Choosing What Not to Build

We started with a Finance dashboard reporting the wrong revenue.
We could have responded by adding more infrastructure, more processing services, and more complexity. But none of those would have solved the underlying problem on their own.
The solution required us to define business requirements, establish event identity, handle late-arriving data, protect transaction semantics, model the warehouse correctly, and measure the full journey from source event to business report.
It also required us to ask whether the business was willing to pay for continuous low-latency analytics—and whether the value justified the investment.
That is the part of data engineering I find particularly interesting.
The technology matters, but the engineering work is in the decisions: understanding the constraints, evaluating trade-offs, designing for failure, and building only what the business needs.

The best data platform is not the one that processes the most events. It is the one that helps the business make the right decision at a cost it can justify.




Let's Discuss

If you were the lead engineer on this project, which decision would you challenge first?
Would you keep Amazon MSK Serverless and Redshift Serverless running continuously to meet the five-minute freshness target, or would you redesign the workload to reduce the fixed operating cost?
I'd be interested in the reasoning behind your choice.

Architecture and Technical References



Architecture disclaimer: Northstar Commerce is a fictional business scenario. The architecture, ADRs, costs, and expected behaviors are proposed design decisions based on the stated assumptions. The GitHub repository is an architectural reference for the warehouse and analytical modeling concepts; it does not demonstrate a production Kafka-to-Redshift deployment.
Build on. Always.

Series: Design, Therefore I Exist: Understanding Modern Cloud Architecture (2 articles)

  1. 2
    Why Your Ecommerce Data Is Wrong—and How to Fix It This article
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article