Change Data You Can Trust: An Architect’s Guide to CDC, Auditing, and CQRS in .NET

1 The Inevitability of Change: Why Capturing Data Evolution is Mission-Critical

Every system we design in .NET eventually collides with the same truth: data does not stay still. Orders are created, invoices corrected, customer addresses updated, and sometimes even deleted for compliance reasons. As architects and senior developers, we are not only responsible for capturing these changes but also for ensuring they can be trusted. The way we handle change determines whether our systems remain resilient under scale or crack under subtle inconsistencies.

In this section, we’ll ground ourselves in why capturing data evolution is not just a technical curiosity but a foundational pillar of modern system design. We’ll start by dissecting the cracks in traditional approaches, then examine the demands of contemporary architectures, and finally introduce the core concepts that will guide the rest of this article.

1.1 The Cracks in Traditional Architectures

Before exploring modern approaches, it’s worth revisiting the patterns that dominated legacy systems. These are the ones many of us have either inherited or unknowingly repeated—and the ones that often fail in production.

1.1.1 The Dual-Write Anti-Pattern

Imagine a .NET application processing a new customer order. It needs to:

Save the order in SQL Server.
Publish an event to a message broker (e.g., RabbitMQ, Kafka, Azure Service Bus) to notify downstream systems.

A naïve implementation might look like this:

public async Task PlaceOrderAsync(Order order)
{
    await _dbContext.Orders.AddAsync(order);
    await _dbContext.SaveChangesAsync();

    await _messageBus.PublishAsync(new OrderCreatedEvent(order.Id, order.Total));
}

At first glance, this seems fine. But what happens if the database transaction succeeds and the message bus call fails due to a network hiccup? The order exists, but downstream services (like Shipping) never hear about it. Reverse the failure order, and we might ship a product that was never actually persisted.

Pitfall: Dual writes break atomicity. Once your system starts dropping or duplicating messages, debugging is a nightmare. A handful of inconsistent states can snowball into angry customers, compliance issues, and expensive data reconciliation jobs.

1.1.2 Querying Production Tables for Analytics

Another tempting shortcut is to point reporting systems directly at production tables. After all, why not just run queries against Orders or Transactions?

The issue is twofold:

Performance Drain: Analytical queries (aggregations, joins across large tables, historical lookups) compete for resources with mission-critical OLTP operations. The result? Sluggish response times for users placing orders.
Stale or Inconsistent Results: Without a controlled mechanism for change capture, analytics often operate on snapshots. A CFO pulling a revenue report at 11:59 PM may unknowingly exclude a batch of late orders still in transaction.

Trade-off: While this shortcut reduces integration complexity in the short term, it introduces operational risk and often forces teams to oversize their production databases just to accommodate analytics load.

1.1.3 The Limitations of Trigger-Based Auditing

Many .NET teams have relied on database triggers to keep an audit trail. Example:

CREATE TRIGGER trg_Orders_Audit
ON Orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO Orders_Audit (OrderId, Operation, ChangedAt, ChangedBy)
    SELECT i.Id, 'INSERT', GETUTCDATE(), SYSTEM_USER
    FROM inserted i;
END

Triggers provide an illusion of simplicity: everything happens “automatically.” But beneath the surface, they carry sharp edges:

Performance Impact: Every insert, update, or delete must also perform the audit insert, slowing down the original transaction.
Maintenance Pain: Triggers are notoriously opaque. Debugging cascades of triggers across related tables is fragile and error-prone.
Business Blind Spots: Triggers only see row-level mutations. They don’t capture intent (e.g., “Order shipped because of business rule X”).

Pro Tip: Use triggers sparingly for enforcing integrity, not for building distributed, event-driven systems.

1.2 Modern Architectural Demands

Today’s systems don’t just need to store and retrieve records. They need to integrate seamlessly across services, satisfy regulators, and deliver insights in real time. Let’s unpack the drivers behind this shift.

1.2.1 Microservices Integration

In a microservices ecosystem, services must share data without being tightly coupled. The Order Service must tell the Shipping Service when an order is placed—but without direct synchronous calls that risk cascading failures.

Requirement: A reliable, asynchronous mechanism that ensures every downstream service sees the same truth, in order, without gaps.

Incorrect:

Direct API calls between services (tight coupling).
Scheduled jobs polling for “new rows” in SQL tables (lag and inefficiency).

Correct:

Streaming change events captured at the source and broadcast to consumers.

1.2.2 Auditing and Compliance

Whether it’s GDPR’s “right to be forgotten” or SOX’s strict auditability of financial data, systems must provide tamper-evident, immutable histories. Regulators don’t accept “best effort.”

Key Needs:

Immutability: Once written, history should not change.
Traceability: Who changed what, and when?
Accessibility: Compliance officers need to query changes without relying on developers.

SQL Server Temporal Tables and the Transactional Outbox pattern are common answers here, as we’ll see later.

1.2.3 Real-time Analytics

Batch ETL pipelines running at midnight no longer suffice. Businesses want dashboards that reflect the last 5 minutes, not yesterday. Recommendation engines, fraud detection, and customer personalization all depend on fresh data.

Scenario: A retail platform wants to update its “Top 10 Trending Products” list in near real time. Waiting for a nightly batch job means losing the sales spike of a product that went viral at 4 PM.

1.2.4 CQRS: Keeping Read Models in Sync

Command Query Responsibility Segregation (CQRS) thrives on having read models optimized for queries, separate from write models optimized for transactions.

Challenge: How do you guarantee that read models (like OrderSummaryView) reflect the latest state of the write model (Orders) without drifting out of sync?

Answer: Consuming reliable streams of change events that are guaranteed to reflect every mutation in the write database.

1.3 Introducing the Core Concepts

Now that we’ve seen why traditional approaches falter and what modern systems demand, let’s define the vocabulary we’ll use throughout this guide.

1.3.1 Change Data Capture (CDC)

CDC is the practice of capturing row-level changes (INSERT, UPDATE, DELETE) as they happen in a database, without bolting on intrusive mechanisms like triggers.

How It Works in SQL Server:

The engine reads the transaction log asynchronously.
It records before/after values in dedicated change tables.
Applications can query or stream these changes.

CDC gives you a truthful log of what changed, when, and how—perfect for feeding downstream systems.

1.3.2 Event Sourcing vs. CDC

It’s easy to conflate Event Sourcing and CDC, but the distinction matters.

Event Sourcing:
- The application decides what the events are (OrderPlaced, OrderCancelled).
- Events are the primary source of truth. The database state is a projection of events.
- Great for new systems designed from scratch.
Change Data Capture:
- The database is the source of truth.
- CDC captures the result of changes (row updated, deleted, etc.).
- Ideal for legacy or hybrid systems where introducing full Event Sourcing isn’t feasible.

Trade-off: Event Sourcing gives you more business intent but requires upfront design discipline. CDC is pragmatic for existing state-based systems.

1.3.3 The Goal: Reliable, Ordered, Durable Streams

Across all these patterns, our endgame is clear:

Reliable: Every change is captured exactly once, no gaps.
Ordered: Changes are replayable in the sequence they occurred.
Durable: Once captured, events can be reprocessed even after consumer failures.

These streams become the backbone of modern integration, auditing, and CQRS implementations.

2 The “In-Database” Toolset: SQL Server’s Native Capabilities

When working in .NET with SQL Server, you don’t always need to jump straight into Kafka or Cosmos DB. SQL Server itself has a mature set of built-in features for capturing change data. They differ in power, performance, and complexity, but each deserves careful consideration.

2.1 SQL Server Change Tracking: The Lightweight Option

Change Tracking is the gentlest on-ramp to capturing data changes. It records which rows changed but not the actual before/after values.

2.1.1 How It Works

When enabled on a table, SQL Server maintains a version number for each row. Applications can query for rows that changed since a given version.

ALTER DATABASE MyAppDb  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

ALTER TABLE Orders  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON);

2.1.2 Use Case: Client Cache Synchronization

Suppose you’re building a WPF or Blazor app with a local cache of reference data (e.g., Products). Instead of fetching the entire table every time, you only pull rows changed since your last sync version.

2.1.3 .NET Example with Dapper

var lastSyncVersion = await _cache.GetVersionAsync();

var sql = @"
    SELECT o.Id, o.Name, o.Price
    FROM CHANGETABLE(CHANGES dbo.Products, @lastSyncVersion) AS c
    JOIN Products o ON o.Id = c.Id";

var products = await connection.QueryAsync<Product>(sql, new { lastSyncVersion });

Verdict: Change Tracking is low overhead and simple, but unsuitable for auditing or integration. It doesn’t store historical data, only what has changed.

2.2 Temporal Tables: The Built-in Audit Log

SQL Server 2016 introduced system-versioned temporal tables—a powerful feature that automatically maintains full history.

2.2.1 How It Works

When you enable temporal tables, SQL Server creates a paired history table. Every update or delete results in the previous version being stored in the history table with valid-from and valid-to timestamps.

CREATE TABLE Orders
(
    OrderId INT PRIMARY KEY,
    TotalAmount DECIMAL(18,2),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)  
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

2.2.2 Use Case: As-Of Queries

Auditors might ask: “What did the Orders table look like on March 1st at 3 PM?” With temporal tables, you can query directly:

SELECT * 
FROM Orders FOR SYSTEM_TIME AS OF '2025-03-01T15:00:00';

2.2.3 EF Core Example

With EF Core 6+, you can map entities as temporal:

modelBuilder.Entity<Order>().ToTable("Orders", b => b.IsTemporal());

Querying as-of a point in time:

var snapshot = await context.Orders
    .TemporalAsOf(new DateTime(2025, 3, 1, 15, 0, 0))
    .ToListAsync();

Verdict: Temporal tables are excellent for in-database auditing. However, they complicate schema evolution and aren’t ideal for integration pipelines (consumers must query the history table themselves).

2.3 SQL Server Change Data Capture (CDC): The Heavy Hitter

For teams who need more than just “what rows changed,” SQL Server Change Data Capture (CDC) is the heavyweight contender. Unlike Change Tracking, CDC doesn’t just tell you that a row has changed; it records the full before-and-after values for inserts, updates, and deletes. This detail makes it a powerful foundation for downstream auditing, analytics, and integration pipelines.

2.3.1 How It Works

CDC operates asynchronously. Rather than slowing down OLTP operations with synchronous triggers, it reads the transaction log in the background. Each change is materialized into dedicated “change tables” that mirror the schema of your source tables with additional metadata columns.

Enabling CDC is a two-step process: turn it on for the database, then for each table.

-- Step 1: Enable CDC at the database level
EXEC sys.sp_cdc_enable_db;

-- Step 2: Enable CDC on the Orders table
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name   = 'Orders',
    @role_name     = NULL;

After this, SQL Server creates system tables like cdc.dbo_Orders_CT and functions such as:

SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, 'all');

These functions allow you to fetch changes between two Log Sequence Numbers (LSNs), which represent positions in the transaction log.

Note: CDC captures changes asynchronously, which means your queries won’t impact the source transaction. However, there will be a slight lag (usually seconds) before changes appear in the change tables.

2.3.2 Example: Polling CDC from .NET

A typical .NET implementation queries CDC tables periodically, using LSN bookmarks to avoid reprocessing the same changes.

public class CdcReader
{
    private readonly string _connectionString;

    public CdcReader(string connectionString)
    {
        _connectionString = connectionString;
    }

    public async Task<IEnumerable<OrderChange>> GetOrderChangesAsync(byte[] fromLsn, byte[] toLsn)
    {
        using var connection = new SqlConnection(_connectionString);

        var sql = @"
            SELECT *
            FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, 'all')";

        return await connection.QueryAsync<OrderChange>(sql, new { from_lsn = fromLsn, to_lsn = toLsn });
    }
}

The OrderChange object would typically map not just the business columns (OrderId, TotalAmount) but also CDC metadata (__$operation, __$start_lsn).

Pro Tip: The __$operation column encodes whether a change was an insert, update (before), update (after), or delete. You’ll usually want to handle updates by pairing before/after rows.

2.3.3 Why Direct Polling Is Risky

While polling CDC tables works, it has pitfalls:

Tight Coupling: Your application logic becomes tied directly to CDC’s schema and system tables.
Cleanup Risks: If you fall behind processing, CDC’s cleanup job may purge older entries before you consume them.
Operational Overhead: Managing LSNs, checkpointing, and retries correctly is non-trivial.

Pitfall: Many teams start with direct polling, only to discover they’ve reinvented a fragile message bus.

This is why CDC is often treated as a stepping stone: it gives you access to changes without triggers, but the real power comes when paired with an external streaming system like Kafka or Azure Event Hubs. We’ll cover that in the next section of this article.

2.3.4 Integration Scenario

Let’s imagine we need to propagate Order changes to a downstream BI system. A simple CDC-driven job could look like this:

public async Task SyncOrdersToWarehouse()
{
    var fromLsn = await _checkpointStore.GetLastProcessedLsnAsync();
    var toLsn = await _cdcHelper.GetMaxLsnAsync();

    var changes = await _cdcReader.GetOrderChangesAsync(fromLsn, toLsn);

    foreach (var change in changes)
    {
        await _warehouseClient.UpsertOrderAsync(change);
    }

    await _checkpointStore.SaveLastProcessedLsnAsync(toLsn);
}

This approach works, but scaling it across multiple services and ensuring reliability under load is where CDC alone starts to creak. That’s when architects reach for a dedicated event streaming backbone.

3 The Streaming Revolution: Decoupling with the Transaction Log

In-database approaches like Temporal Tables or SQL Server CDC solve many problems, but they share a limiting characteristic: they keep consumers tightly tied to the database itself. As soon as multiple teams or services need to react to changes, polling and querying CDC tables start to feel brittle and slow.

The streaming revolution changes the equation by treating the transaction log not as a passive persistence artifact, but as the primary source of truth for change events. By tailing the log directly and streaming changes into a durable event backbone, we decouple producers and consumers entirely. One update in a source database can now power a dozen independent services—each scaling, retrying, and replaying at its own pace.

Let’s dive into two major approaches: log-based CDC with Debezium + Kafka, and the native change feed capabilities of Azure Cosmos DB.

3.1 The Log-Based CDC Pattern: Debezium and Kafka

Debezium is the de facto standard for log-based CDC in modern architectures. Running on top of Kafka Connect, it monitors a database’s transaction log, produces structured change events, and ensures that schema evolution is handled gracefully. Combined with Kafka, it delivers a resilient backbone where .NET services can consume events without touching the source database.

3.1.1 Architecture Deep Dive

At its core, the architecture involves three moving parts:

The Database Transaction Log SQL Server’s transaction log records every change in order, including before/after values. This log is the ground truth of state evolution. Rather than polling tables or triggers, Debezium reads directly from here.
Debezium as the Connector Debezium runs as a Kafka Connect connector. It tails the transaction log, translates changes into structured events (with schema, metadata, and operation type), and pushes them into Kafka topics. It also manages schema versions via Kafka’s Schema Registry.

Example Debezium event structure (simplified JSON):
```
{
  "before": { "OrderId": 42, "TotalAmount": 100.0 },
  "after": { "OrderId": 42, "TotalAmount": 120.0 },
  "source": {
    "db": "Sales",
    "table": "Orders",
    "lsn": "0000016b:0000005a:0001"
  },
  "op": "u",
  "ts_ms": 1735772000000
}
```
Here, op indicates the operation (c for create, u for update, d for delete), and before/after contain row states.
Kafka as the Event Backbone Kafka stores these events durably and in order, partitioned by keys (e.g., OrderId). This allows horizontal scaling across consumers while guaranteeing ordering within a key. Consumers can rewind, replay, or join streams without ever touching SQL Server again.

Pro Tip: Kafka’s replay capability is a game-changer. A downstream service that suffers a bug can reprocess last week’s events by resetting its consumer offset—no need to touch the database or run backfill scripts.

3.1.2 Setting It Up for SQL Server

Running Debezium against SQL Server requires configuring both the source database and the connector.

Step 1: Configure SQL Server Enable CDC on the database and tables (as described in section 2.3). Debezium will read from the same transaction log mechanism used by SQL Server CDC.

Step 2: Start Kafka + Connect Spin up Kafka, Zookeeper, and Kafka Connect. Many teams use Docker Compose for local development:

version: '3.7'
services:
  zookeeper:
    image: confluentinc/cp-zookeeper:7.4.0
    environment:
      ZOOKEEPER_CLIENT_PORT: 2181

  kafka:
    image: confluentinc/cp-kafka:7.4.0
    depends_on: [zookeeper]
    environment:
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
      KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1

  connect:
    image: debezium/connect:2.6
    depends_on: [kafka]
    environment:
      BOOTSTRAP_SERVERS: kafka:9092
      GROUP_ID: 1
      CONFIG_STORAGE_TOPIC: my_connect_configs
      OFFSET_STORAGE_TOPIC: my_connect_offsets
      STATUS_STORAGE_TOPIC: my_connect_statuses

Step 3: Register the Debezium Connector Post connector configuration to Kafka Connect’s REST API:

{
  "name": "sqlserver-orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "sqlserver",
    "database.port": "1433",
    "database.user": "cdc_user",
    "database.password": "cdc_pwd",
    "database.dbname": "Sales",
    "database.server.name": "sqlserver1",
    "table.include.list": "dbo.Orders",
    "database.history.kafka.bootstrap.servers": "kafka:9092",
    "database.history.kafka.topic": "schema-changes.orders"
  }
}

Once active, Debezium streams every Orders change into a Kafka topic named sqlserver1.dbo.Orders.

3.1.3 Consuming Events in .NET

On the consumer side, .NET services can subscribe to Kafka topics and handle changes. The Confluent.Kafka client is the go-to library.

public class OrderChangeConsumer : BackgroundService
{
    private readonly IConsumer<string, string> _consumer;

    public OrderChangeConsumer(IConfiguration config)
    {
        var consumerConfig = new ConsumerConfig
        {
            BootstrapServers = config["Kafka:BootstrapServers"],
            GroupId = "order-service",
            AutoOffsetReset = AutoOffsetReset.Earliest
        };

        _consumer = new ConsumerBuilder<string, string>(consumerConfig).Build();
        _consumer.Subscribe("sqlserver1.dbo.Orders");
    }

    protected override Task ExecuteAsync(CancellationToken stoppingToken)
    {
        return Task.Run(() =>
        {
            while (!stoppingToken.IsCancellationRequested)
            {
                var result = _consumer.Consume(stoppingToken);
                HandleOrderChange(result.Message.Value);
            }
        }, stoppingToken);
    }

    private void HandleOrderChange(string json)
    {
        var change = JsonSerializer.Deserialize<DebeziumChange<Order>>(json);
        Console.WriteLine($"Order {change.After.OrderId} updated. New total: {change.After.TotalAmount}");
    }
}

Pitfall: Always design consumers for idempotency. If Kafka redelivers an event, your service must handle it gracefully without duplicating work.

3.1.4 The Power of Decoupling

Once changes flow into Kafka, the database is no longer the integration hub. Multiple consumers can process the same events independently:

Auditing Service: Writes events to immutable storage like S3 or Elasticsearch.
Analytics Service: Transforms and streams events into a data warehouse.
CQRS Read Model Updater: Updates denormalized views in MongoDB or Redis.
Notification Service: Sends emails or pushes real-time UI updates.

None of these consumers impact SQL Server’s performance or reliability. Each can fail, retry, or replay independently.

Trade-off: The flip side of decoupling is operational overhead. Running Kafka and Debezium requires infrastructure skills. For smaller teams, this may feel heavy compared to database-native options.

3.2 The NoSQL Approach: Azure Cosmos DB Change Feed

Not every system runs on SQL Server. For applications built natively on Azure, Cosmos DB offers a first-class alternative: the Change Feed. Unlike CDC, the Change Feed is not a bolt-on feature; it’s a core part of the database.

3.2.1 Architecture Deep Dive

Cosmos DB automatically records every insert and update to a container in the Change Feed. The feed exposes these changes as an ordered sequence that can be read incrementally.

Key points:

The Change Feed is ordered by logical partition key, ensuring strong ordering guarantees within a partition.
Deletes are not captured by default (a design decision that encourages soft deletes with a tombstone flag).
Unlike polling SQL Server tables, you don’t manage LSNs or cleanup. Cosmos manages continuation tokens internally.

The Change Feed Processor is the client-side component that:

Distributes work across multiple consumers using a leases container.
Tracks continuation tokens automatically so consumers always pick up where they left off.
Handles scaling out horizontally across many worker instances.

3.2.2 .NET Implementation Example

Suppose we have a Cosmos DB container for Orders. We can process its change feed with the Azure Cosmos SDK:

public class OrderChangeFeedProcessor
{
    private readonly CosmosClient _cosmosClient;

    public OrderChangeFeedProcessor(CosmosClient cosmosClient)
    {
        _cosmosClient = cosmosClient;
    }

    public async Task StartAsync()
    {
        var container = _cosmosClient.GetContainer("SalesDb", "Orders");
        var leaseContainer = _cosmosClient.GetContainer("SalesDb", "OrderLeases");

        var processor = container.GetChangeFeedProcessorBuilder<Order>(
                processorName: "OrderProcessor",
                onChangesDelegate: async (IReadOnlyCollection<Order> changes, CancellationToken cancellationToken) =>
                {
                    foreach (var order in changes)
                    {
                        Console.WriteLine($"Order {order.Id} changed with total {order.TotalAmount}");
                        await HandleOrderAsync(order);
                    }
                })
            .WithInstanceName(Environment.MachineName)
            .WithLeaseContainer(leaseContainer)
            .Build();

        await processor.StartAsync();
    }

    private Task HandleOrderAsync(Order order)
    {
        // Business logic: update read model, publish event, etc.
        return Task.CompletedTask;
    }
}

Here, the OrderLeases container tracks progress for each processor instance. If one instance crashes, another can take over seamlessly.

Note: Cosmos DB’s pay-as-you-go model means you’re billed for Request Units (RUs). The Change Feed is efficient, but be mindful of throughput configuration when consuming high-volume workloads.

3.2.3 Key Concepts

Leases Container: Stores metadata for each processor instance (partition ownership, continuation tokens).
Continuation Tokens: Mark the consumer’s position in the Change Feed. Managed automatically by the processor.
Pull vs. Push Model: The Change Feed is technically “pull-based,” but the SDK abstracts it to feel like an event stream.

Pro Tip: If you need deletes in your Change Feed, implement a soft-delete strategy by adding a IsDeleted flag. This aligns with Cosmos’s design while still allowing downstream consumers to track removals.

3.2.4 Verdict

Cosmos DB’s Change Feed offers a serverless, fully managed alternative to running Kafka. It’s ideal for teams building greenfield applications on Azure, where operational simplicity matters more than cross-cloud portability.

Strengths:
- Zero-infrastructure (no Kafka clusters).
- Built-in scaling and state management.
- Strong integration with Azure Functions for event-driven processing.
Weaknesses:
- No native delete events.
- Tied to Azure (no portability).
- Pricing depends on RU provisioning, which can spike with high-volume workloads.

Trade-off: For cloud-native teams, the Change Feed is often the most cost-effective and simplest path to reliable change streaming. For multi-cloud or hybrid strategies, Kafka/Debezium provides more flexibility.

4 The Enterprise .NET Playbook: Production-Ready Implementation Patterns

So far, we’ve built a strong foundation of theory and database-level capabilities. We’ve also seen how modern streaming platforms like Debezium and Kafka, or Cosmos DB Change Feed, can decouple producers from consumers. But patterns don’t live on whiteboards. They must be translated into production-grade .NET code that can withstand failures, audits, and unpredictable workloads.

In this section, we’ll anchor the discussion in a practical sample domain: an Order Processing system. Orders are placed, updated, and shipped. These operations need to be auditable, integrated across microservices, and queryable through denormalized read models. We’ll walk through four key patterns, each reinforcing the theme of change data you can trust.

4.1 Pattern: The Immutable, Searchable Audit Log

Auditing is often the first driver behind change data. Businesses, regulators, and even internal teams want answers to questions like:

Who changed this order?
What was its previous state?
Can we reconstruct a timeline of changes?

4.1.1 The Goal

Our goal is to record every significant state change in an immutable log that is:

Durable (no overwrites).
Searchable (by entity, operation, or timeframe).
Tamper-evident (hard to manipulate retroactively).

This log must not undermine transactional integrity. If an order fails to persist, we must not record a phantom audit entry.

4.1.2 Why Not EF Core Interceptors Alone?

It’s tempting to hook into EF Core’s SaveChanges interceptors and write to an AuditLog table. However, there’s a subtle race condition:

If the audit log entry is written before the main entity, and the transaction fails, the audit record becomes misleading.
If it’s written after and the process crashes, the entity exists without an audit record.

Pitfall: Audit systems must reflect exactly what committed. Anything else leads to mistrust of the audit log—a compliance nightmare.

4.1.3 The Transactional Outbox Strategy

The solution is the Transactional Outbox Pattern:

Within the same EF Core transaction, persist both the business entity (e.g., Order) and an OutboxMessage.
The outbox table acts as a durable buffer, ensuring messages are only created if the transaction commits.
Debezium monitors the outbox table, streaming its contents into Kafka topics (like audit-events).
A dedicated audit consumer service processes these events and persists them into long-term storage.

4.1.4 Entity and DbContext Setup

public class Order
{
    public Guid Id { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; } = "Created";
}

public class OutboxMessage
{
    public Guid Id { get; set; }
    public string Type { get; set; } = default!;
    public string Payload { get; set; } = default!;
    public DateTime OccurredAt { get; set; } = DateTime.UtcNow;
}

public class OrdersDbContext : DbContext
{
    public DbSet<Order> Orders => Set<Order>();
    public DbSet<OutboxMessage> OutboxMessages => Set<OutboxMessage>();

    public OrdersDbContext(DbContextOptions<OrdersDbContext> options) : base(options) { }
}

4.1.5 Command Handling with MediatR Pipeline

We can integrate the outbox into a MediatR pipeline, ensuring commands generate outbox entries consistently.

public class OutboxBehavior<TRequest, TResponse> : IPipelineBehavior<TRequest, TResponse>
{
    private readonly OrdersDbContext _db;

    public OutboxBehavior(OrdersDbContext db) => _db = db;

    public async Task<TResponse> Handle(TRequest request, RequestHandlerDelegate<TResponse> next, CancellationToken ct)
    {
        var response = await next();

        var message = new OutboxMessage
        {
            Id = Guid.NewGuid(),
            Type = request.GetType().Name,
            Payload = JsonSerializer.Serialize(request),
            OccurredAt = DateTime.UtcNow
        };

        _db.OutboxMessages.Add(message);
        await _db.SaveChangesAsync(ct);

        return response;
    }
}

With this in place, placing an order automatically results in both an Order and an OutboxMessage persisted atomically.

4.1.6 The Audit Consumer

Debezium publishes outbox entries to Kafka. A separate .NET consumer writes them to Elasticsearch for searchability.

public class AuditConsumer : BackgroundService
{
    private readonly IConsumer<string, string> _consumer;
    private readonly ElasticClient _elastic;

    public AuditConsumer(IConsumer<string, string> consumer, ElasticClient elastic)
    {
        _consumer = consumer;
        _elastic = elastic;
    }

    protected override Task ExecuteAsync(CancellationToken stoppingToken) =>
        Task.Run(() =>
        {
            _consumer.Subscribe("audit-events");

            while (!stoppingToken.IsCancellationRequested)
            {
                var result = _consumer.Consume(stoppingToken);
                var msg = JsonSerializer.Deserialize<OutboxMessage>(result.Message.Value);

                _elastic.IndexDocument(msg!);
            }
        }, stoppingToken);
}

Pro Tip: Storing audit logs in Elasticsearch or another search-optimized store is far more practical than querying relational outbox tables directly.

4.1.7 Verdict

The outbox-based audit log guarantees atomicity, immutability, and scalability. It’s the backbone of trustworthy auditing in distributed .NET systems.

4.2 Pattern: Building a Resilient Integration Pipeline

Once auditing is in place, the next natural challenge is integrating services. Our Order Service must notify a Shipping Service when an order is placed. The goal is reliable propagation—no missed events, no duplicates causing extra shipments.

4.2.1 Producer: Extending the Outbox

The same outbox used for auditing can also produce integration events like OrderCreated.

public class CreateOrderHandler : IRequestHandler<CreateOrderCommand, Guid>
{
    private readonly OrdersDbContext _db;

    public CreateOrderHandler(OrdersDbContext db) => _db = db;

    public async Task<Guid> Handle(CreateOrderCommand request, CancellationToken ct)
    {
        var order = new Order { Id = Guid.NewGuid(), TotalAmount = request.Total };
        _db.Orders.Add(order);

        var outboxMessage = new OutboxMessage
        {
            Id = Guid.NewGuid(),
            Type = "OrderCreated",
            Payload = JsonSerializer.Serialize(new { order.Id, order.TotalAmount })
        };

        _db.OutboxMessages.Add(outboxMessage);

        await _db.SaveChangesAsync(ct);

        return order.Id;
    }
}

Debezium streams these outbox entries to a Kafka topic shipping-events.

4.2.2 Consumer: The Shipping Service

The Shipping Service consumes from shipping-events.

public class ShippingConsumer : BackgroundService
{
    private readonly IConsumer<string, string> _consumer;
    private readonly ShippingDbContext _db;

    public ShippingConsumer(IConsumer<string, string> consumer, ShippingDbContext db)
    {
        _consumer = consumer;
        _db = db;
    }

    protected override Task ExecuteAsync(CancellationToken stoppingToken) =>
        Task.Run(() =>
        {
            _consumer.Subscribe("shipping-events");

            while (!stoppingToken.IsCancellationRequested)
            {
                var result = _consumer.Consume(stoppingToken);
                var evt = JsonSerializer.Deserialize<OrderCreatedEvent>(result.Message.Value);

                HandleEvent(evt!);
            }
        }, stoppingToken);

    private void HandleEvent(OrderCreatedEvent evt)
    {
        if (_db.Shipments.Any(s => s.OrderId == evt.OrderId))
            return; // idempotency check

        _db.Shipments.Add(new Shipment { OrderId = evt.OrderId, Status = "Pending" });
        _db.SaveChanges();
    }
}

4.2.3 Idempotency and Checkpointing

Kafka consumer groups manage offsets, ensuring each event is processed at least once. But duplicates can occur. Idempotency is therefore a consumer’s responsibility.

Pro Tip: Use business keys (OrderId) instead of relying solely on message IDs for deduplication. This prevents logical duplicates in cases where the same order event is replayed.

4.2.4 Verdict

By extending the outbox, we achieve reliable event propagation with atomic guarantees, while Kafka offsets and idempotency ensure resilience against duplication.

4.3 Pattern: Handling Failure with Polly and Dead-Letter Queues

Real-world systems are messy. Networks drop, APIs time out, databases lock. A single malformed message can halt a naïve consumer loop. A resilient pipeline anticipates these failures.

4.3.1 Retry Logic with Polly

Polly provides flexible retry policies for transient errors.

var policy = Policy
    .Handle<SqlException>()
    .Or<HttpRequestException>()
    .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(Math.Pow(2, attempt)));

4.3.2 Integrating Polly with Kafka Consumer

private async Task ProcessMessageAsync(string json)
{
    await _policy.ExecuteAsync(async () =>
    {
        var evt = JsonSerializer.Deserialize<OrderCreatedEvent>(json);
        await _shippingService.ScheduleShipmentAsync(evt!);
    });
}

Note: Always distinguish between transient (retryable) errors and permanent ones. Retrying a malformed payload is wasted effort.

4.3.3 Dead-Letter Queues (DLQ)

After several failed retries, messages should be moved to a DLQ topic.

catch (Exception ex)
{
    _logger.LogError(ex, "Failed to process message. Sending to DLQ.");

    await _dlqProducer.ProduceAsync("shipping-events-dlq", new Message<string, string>
    {
        Key = Guid.NewGuid().ToString(),
        Value = json
    });
}

Operators can monitor the DLQ topic, investigate issues, and decide whether to replay or discard messages.

Pitfall: DLQs should not be ignored. Treat them as operational alerts, not black holes.

4.3.4 Verdict

Combining Polly for retries with DLQs for poison messages gives your pipeline resilience against both temporary hiccups and persistent data issues.

4.4 Pattern: Powering CQRS Read Models

Finally, let’s use change data to maintain denormalized read models for CQRS. Read models allow for fast, query-optimized views, separate from write models.

4.4.1 The Goal

Our system must support queries like:

Show all pending orders with customer names.
List today’s top 10 highest-value orders.

Running these queries directly on transactional tables is inefficient. Instead, we maintain a dedicated read model: OrderSummary.

4.4.2 Consumer for Read Model Updates

A dedicated service consumes order events and updates the read database.

public class OrderSummaryConsumer : BackgroundService
{
    private readonly IConsumer<string, string> _consumer;
    private readonly ReadDbContext _db;

    public OrderSummaryConsumer(IConsumer<string, string> consumer, ReadDbContext db)
    {
        _consumer = consumer;
        _db = db;
    }

    protected override Task ExecuteAsync(CancellationToken stoppingToken) =>
        Task.Run(() =>
        {
            _consumer.Subscribe("sqlserver1.dbo.Orders");

            while (!stoppingToken.IsCancellationRequested)
            {
                var result = _consumer.Consume(stoppingToken);
                var change = JsonSerializer.Deserialize<DebeziumChange<Order>>(result.Message.Value);

                ApplyChange(change!);
            }
        }, stoppingToken);

    private void ApplyChange(DebeziumChange<Order> change)
    {
        switch (change.Op)
        {
            case "c": // create
                _db.OrderSummaries.Add(new OrderSummary
                {
                    Id = change.After.Id,
                    Total = change.After.TotalAmount,
                    Status = change.After.Status
                });
                break;

            case "u": // update
                var existing = _db.OrderSummaries.Find(change.After.Id);
                if (existing != null)
                {
                    existing.Total = change.After.TotalAmount;
                    existing.Status = change.After.Status;
                }
                break;

            case "d": // delete
                var toDelete = _db.OrderSummaries.Find(change.Before.Id);
                if (toDelete != null)
                    _db.OrderSummaries.Remove(toDelete);
                break;
        }

        _db.SaveChanges();
    }
}

4.4.3 Denormalization Example

We can even enrich the read model with data from other topics (e.g., customer names). Kafka Streams or consumer-side joins can merge data before persisting.

Pro Tip: Keep read models disposable. If they fall out of sync, simply replay events from Kafka to rebuild.

4.4.4 Verdict

CQRS read models powered by CDC and streaming deliver the best of both worlds: transactional consistency in writes, and high-performance, flexible queries in reads.

5 The Architect’s Decision Matrix: Choosing Your Weapon

With so many tools and patterns on the table—Temporal Tables, SQL Server CDC, Debezium with Kafka, and Cosmos DB Change Feed—it’s natural for architects to wonder: which option is right for my system? There is no one-size-fits-all answer. The correct choice depends on your requirements around compliance, performance, integration, and operational complexity.

This section provides a decision matrix to compare the options across key dimensions, followed by pragmatic recommendations for typical scenarios that .NET solution architects face.

5.1 Comparison Table

The following table contrasts the four major approaches across commonly evaluated criteria:

Feature / Axis	Temporal Tables	SQL Server CDC	Debezium + Kafka	Cosmos DB Change Feed
Setup & Operational Complexity	Minimal – one DDL change to enable	Moderate – requires DB config and cleanup jobs	High – requires Kafka, Connect, Schema Registry, ops team	Low – native, managed by Azure
Performance Impact on Source DB	Medium – extra writes to history table on every change	Low – asynchronous log reading	Low – asynchronous log streaming	Low – server-managed feed
Latency	Query-time only, not event-driven	Seconds (depends on log scanning)	Milliseconds to seconds (near real-time)	Milliseconds to seconds
Decoupling & Scalability	Limited – history only in DB	Moderate – consumers poll DB	Excellent – fully decoupled event backbone, multiple independent consumers	Good – serverless, partition-based, integrates with Functions/AKS
Cost	Storage grows with history; query cost	Moderate – storage + management overhead	Higher – infra + ops, but scales horizontally	Pay-as-you-go RU consumption; elastic but may spike
Ordering Guarantees	Strong within table + system time	Strong per LSN	Strong per partition key	Strong within logical partition key
Best Fit For	Auditing, “as of” queries, regulatory history	Data warehouse feeds, moderate integration needs	Enterprise microservices, multi-consumer event-driven systems	Azure-native serverless, event-driven workloads

Note: Costs here include not only cloud/infra spend but also operational complexity cost (DevOps hours, cognitive load, ongoing maintenance).

Trade-off: The more decoupled and real-time you want your system, the higher the operational burden—unless you choose a managed solution like Cosmos DB Change Feed.

5.2 Practical Recommendations

Let’s distill the table into actionable guidance for typical scenarios.

5.2.1 For Simple, In-Database Auditing

If your need is straightforward auditing—for example, “what did this record look like last quarter?”—then Temporal Tables are the cleanest fit. You get:

Zero application code changes.
Built-in SQL queries for time-travel.
Perfect integration with EF Core’s temporal querying.

Pitfall: Do not mistake temporal tables for an integration mechanism. They are designed for compliance and history, not for building streaming architectures.

5.2.2 For a Monolithic Application Feeding a Data Warehouse

If your system is still primarily monolithic, but analysts need data in a warehouse (like Synapse or Snowflake), SQL Server CDC is a great fit. It:

Captures detailed before/after values asynchronously.
Allows ETL tools or custom .NET jobs to pull incremental changes.
Minimizes OLTP impact compared to triggers or snapshot ETL.

Pro Tip: Configure CDC retention carefully. A stalled pipeline can easily lose data if cleanup jobs purge entries before they’re consumed.

5.2.3 For Distributed Microservices Architectures

When building microservices that integrate via events, Debezium + Kafka combined with the Transactional Outbox pattern is the gold standard. Benefits include:

Reliable, decoupled event propagation.
Ability to replay streams for new consumers or recovery.
Multiple independent consumers (analytics, CQRS, notifications).
Strong ordering guarantees per partition key.

Trade-off: The ops burden is real. Running Kafka clusters, connectors, and schema registries is non-trivial. Many enterprises offset this by using managed Kafka services like Confluent Cloud or Azure Event Hubs for Kafka.

5.2.4 For Azure-Native, Serverless Applications

If you’re building a greenfield application in Azure and want minimal ops, Cosmos DB Change Feed is the clear winner. It:

Exposes inserts/updates as an ordered stream.
Handles scaling, leases, and continuation automatically.
Integrates seamlessly with Azure Functions for reactive pipelines.

Note: Lack of native delete tracking means you must plan for soft deletes or tombstones.

5.2.5 General Guidance

Start small and simple: use Temporal Tables if all you need is history.
For ETL or warehouse sync, prefer SQL Server CDC.
For real-time microservices, adopt Debezium + Kafka with outbox.
For Azure serverless workloads, lean into Cosmos DB Change Feed.

Pro Tip: Resist the temptation to over-engineer. A local ERP system does not need Kafka. Conversely, a multinational e-commerce platform should not rely solely on polling CDC tables.

6 Conclusion: The Future is Event-Driven

Capturing change data isn’t just about compliance checkboxes or analytics refreshes. It’s about building trustworthy systems that can evolve, scale, and integrate without breaking.

We began by exposing the cracks in traditional architectures: dual writes, trigger-based auditing, and heavy analytical queries on OLTP systems. We then explored SQL Server’s in-database toolset, from lightweight Change Tracking to Temporal Tables and CDC. Next, we shifted to the streaming revolution with Debezium and Kafka, and Cosmos DB’s Change Feed for cloud-native scenarios. Finally, we translated these into production-ready .NET playbooks: immutable audit logs, resilient integration pipelines, robust error handling, and CQRS read models.

The key takeaway is simple: Move beyond fragile shortcuts. Adopt patterns that make change data durable, ordered, and replayable. Use them as a foundation for auditing, CQRS, analytics, and more.

As .NET architects, embracing change data streams unlocks more than compliance—it enables innovation. From real-time machine learning feature engineering to operational dashboards and predictive analytics, these streams become the nervous system of modern digital enterprises.

The future is event-driven. The sooner you make change data a first-class citizen in your architecture, the sooner your systems can deliver on the promise of resilience and trust.

Change Data You Can Trust: An Architect's Guide to CDC, Auditing, and CQRS in .NET