Relational Data Architecture: OLTP, CDC, and Warehouses

I still trust relational systems for many core transactional paths. I recommend protecting OLTP first, reading changes safely, and building analytics from governed copies instead of punishing the source database.

Where relational truth lives

When I talk about "Where relational truth lives", I am checking whether Relational Data Architecture can be traced back, trusted, and used by someone making a decision.

PostgreSQL, MySQL, SQL Server, Oracle, Aurora, Cloud SQL, AlloyDB, Spanner, SQL Server, Elasticsearch or OpenSearch for search projections.

Managed relational options

Transaction pattern to remember

My recommendation in "Transaction pattern to remember" is to keep the raw source close enough that the answer can be explained later.

Google Spanner is the public large company reference for relational data at global scale: it keeps SQL and transactions while distributing data across regions. The lesson is not that every team needs Spanner; it is that relational guarantees still matter when money or ownership changes.

Order lifecycle example

I use "Order lifecycle example" to keep Relational Data Architecture grounded in a real system, because abstract patterns are too easy to agree with and too hard to operate.

In an order platform, the order service owns relational tables. CDC publishes every insert and update into a stream. The analytics team builds order lifecycle tables, while support search indexes order id, customer email, and shipment state.

Constraint backed code

When I show "Constraint backed code", I want the code in Relational Data Architecture to reveal the production decision, not just the syntax.

select
  o.order_id,
  c.email,
  p.captured_at,
  s.shipment_status
from orders o
join customers c on c.customer_id = o.customer_id
join payments p on p.order_id = o.order_id and p.status = 'CAPTURED'
left join shipments s on s.order_id = o.order_id
where s.order_id is null
  and p.captured_at < current_timestamp - interval '2 days';

The query finds orders with captured payments but no shipment after two days. The joins are not reporting decoration; they expose a broken business workflow.

Relational traps to watch

• Do not run heavy analytics directly on OLTP primaries.
• Capture deletes and schema changes in CDC.
• Keep referential meaning when denormalizing for search.

Related relational reads

• Everything Data: The Asset Behind Modern Software
• YCSB: A Useful Benchmark for Data Stores
• Finding Performance Bottlenecks in a Serverless Microservice Chain