Data Integration Architect

⚡ When to Use

💡 Examples

Example 1: E-commerce Platform — OLTP + Search + Analytics

Scenario: Online retailer with PostgreSQL for orders/products, Elasticsearch for product search, Snowflake for business analytics. Current architecture uses dual writes from application code. Products sometimes appear in search before inventory is updated; analytics dashboards occasionally show orders that do not exist in PostgreSQL.

Trigger: "Our Elasticsearch and PostgreSQL are drifting. Products show in search that are out of stock. How do we fix the architecture?"

Process:

Step 1 identifies dual writes as the anti-pattern causing drift

Step 2 designates PostgreSQL as system of record for products, orders, inventory

Step 3 selects CDC (Debezium) from PostgreSQL WAL → Kafka → Elasticsearch/Snowflake consumers

Step 6 confirms that per-product ordering (route by product_id partition) is sufficient; total ordering across all products is not required

Step 7 verifies Flink consumers are idempotent (upsert by product_id handles redelivery)

Output: Replace dual writes with Debezium CDC pipeline. Elasticsearch and Snowflake become read-only derived views, populated exclusively from the Kafka event log. Drift is eliminated because both stores process the same ordered event sequence from the same source.

---

Example 2: Financial Services — Multi-Partition Transfer with End-to-End Correctness

Scenario: Payment processing system where transferring money requires debiting one account (partition A) and crediting another (partition B). Current implementation uses two-phase commit across partitions; this causes availability problems when the coordinator fails.

Trigger: "We're using 2PC for cross-account transfers and it's killing our availability. How do we redesign this?"

Process:

Step 3 selects event sourcing: log the transfer request as a single message

Step 6 identifies that per-request ordering (route by request_id) is needed to prevent duplicate application

Step 7 applies the multi-partition correctness pattern:

1. Client generates UUID request_id; application appends transfer request to Kafka (keyed by request_id) 2. Stream processor reads request; emits debit instruction to partition A's stream (with request_id) and credit instruction to partition B's stream (with request_id) 3. Account processors for A and B each deduplicate by request_id using a unique constraint on a requests table

If the stream processor crashes and reprocesses the request, it produces identical debit/credit instructions; the unique constraint suppresses the duplicates

Output: Remove 2PC. Achieve equivalent correctness (every transfer applied exactly once to both accounts) without cross-partition coordination. Availability improves because no coordinator failure mode exists.

---

Example 3: Social Platform — Causal Ordering Across Services

Scenario: Social network with friendship status stored in service A, notification delivery in service B. Users report receiving notifications from people they have unfriended — the unfriend event and the message-send event are processed in the wrong order.

Trigger: "Users are getting messages from people they unfriended. The unfriend event seems to arrive after the message sometimes."

Process:

Step 1 identifies that friendship and messaging are independent event streams; no total order exists between them

Step 6 identifies a causal dependency: the message-send event causally depends on the friendship status the sender observed

Fix: When the sender sends a message, log the event identifier of the most recent friendship-status read (the state the sender saw). The notification service checks this event ID; if it has not yet processed past that event in the friendship log, it defers the notification.

Alternative: Route all friendship and messaging events through a single Kafka topic partitioned by the pair of user IDs, imposing a per-pair total order

Output: Causal dependency captured via event identifiers. Notification service becomes causally consistent without requiring total ordering across all users.

---