
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)
- 2Why Your Ecommerce Data Is Wrong—and How to Fix It This article
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:
- Analytical freshness: 95% of eligible events available to reporting within five minutes.
- Data integrity: no duplicate business transactions in curated payment reporting.
- 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:
- The payment event is published twice because of an application-level retry.
- 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_capturesWHERE 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)
- 2Why Your Ecommerce Data Is Wrong—and How to Fix It This article
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article