CDC Sink: Schema Design

Name: RavenDB
Author: RavenDB

CDC Sink maps a relational schema to a RavenDB document model through configuration.
This article explains the three building blocks:
root tables, embedded tables, and linked tables - and how to combine them.
In this article:

Root tables

A root table maps a source table to a RavenDB collection. Each row in the source table becomes one document.

Each root table is configured with a CdcSinkTableConfig object. This configuration defines the source table to read from, the target RavenDB collection to write to, the primary key columns used to build document IDs, and the columns to map into each document.

csharp

new CdcSinkTableConfig
{
    CollectionName = "Orders",    // RavenDB collection name
    SourceTableSchema = "public", // Source schema (optional; default is provider-specific)
    SourceTableName = "orders",   // Source table name
    PrimaryKeyColumns = ["id"],   // Used for document ID generation
    Columns =
    [
        new CdcColumnMapping() { Column = "id",            Name = "Id" },
        new CdcColumnMapping() { Column = "customer_name", Name = "CustomerName" },
        new CdcColumnMapping() { Column = "total",         Name = "Total" },
    ]
}

The default SourceTableSchema is provider-specific: PostgreSQL uses public, SQL Server uses dbo,
and MySQL/MariaDB use the database name from the connection string.

Document ID generation:
CDC Sink generates document IDs in this format:
{CollectionName}/{primary-key-value-1}/{primary-key-value-2}/...

The braces indicate placeholders, not literal text.
For example, a row with id = 42 and CollectionName = "Orders" becomes Orders/42.
A composite primary key (region, id) with values (US, 42) becomes Orders/US/42.

CDC Sink uses the CollectionName verbatim as the document ID prefix.

Column mapping:
Only columns listed in Columns are stored as direct mapped fields.

Embedded and linked table configurations can add additional document properties,
and unmapped source columns can still be used for joins, links, and patch scripts.

Embedded tables

An embedded table creates nested data within a parent document.
For example, a order_lines table becomes an array inside each Orders document.

Embedded tables are configured inside the root table's CdcSinkTableConfig.
The example below configures an Orders root document with a Lines embedded array from the order_lines table.

csharp

new CdcSinkTableConfig
{
    CollectionName = "Orders",
    SourceTableName = "orders",
    PrimaryKeyColumns = ["id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "id",            Name = "Id" },
        new CdcColumnMapping() { Column = "customer_name", Name = "CustomerName" },
    ],
    EmbeddedTables =
    [
        new CdcSinkEmbeddedTableConfig
        {
            SourceTableName = "order_lines",
            PropertyName = "Lines",           // Property in parent document
            Type = CdcSinkRelationType.Array, // Array of items
            JoinColumns = ["order_id"],       // FK referencing parent's PK
            PrimaryKeyColumns = ["line_id"],  // Used to match items on update/delete
            Columns =
            [
                new CdcColumnMapping() { Column = "line_id",  Name = "LineId" },
                new CdcColumnMapping() { Column = "product",  Name = "Product" },
                new CdcColumnMapping() { Column = "quantity", Name = "Quantity" },
            ]
        }
    ]
}

This produces documents like:

json

{
    "Id": 1,
    "CustomerName": "Alice",
    "Lines": [
        { "LineId": 1, "Product": "Apples", "Quantity": 5 },
        { "LineId": 2, "Product": "Bananas", "Quantity": 3 }
    ],
    "@metadata": { "@collection": "Orders" }
}

Linked tables

A linked table creates a document ID reference rather than embedding data.
A foreign key in the source row becomes a RavenDB document ID.

csharp

new CdcSinkTableConfig
{
    CollectionName = "Orders",
    SourceTableName = "orders",
    PrimaryKeyColumns = ["id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "id",          Name = "Id" },
        new CdcColumnMapping() { Column = "customer_id", Name = "CustomerId" },
    ],
    LinkedTables =
    [
        new CdcSinkLinkedTableConfig
        {
            SourceTableName = "customers",
            PropertyName = "Customer",          // Property in parent document
            LinkedCollectionName = "Customers", // Target collection for ID
            JoinColumns = ["customer_id"]       // FK used to build the ID
        }
    ]
}

With customer_id = 42, the document gets "Customer": "Customers/42".

For composite references, list JoinColumns in the same order as the referenced table's PrimaryKeyColumns,
so the generated ID matches the referenced document ID.

A linked table does NOT load or maintain the linked document by itself.
It only writes a document ID reference such as Customers/42 into the current document. The referenced Customers/42 document is maintained only if customers is also configured as a root table.

Primary key and join column requirements

Root tables

The PrimaryKeyColumns list defines which source columns are used to generate the document ID.

All PK columns must be present in every INSERT, UPDATE, and DELETE event.
Every column listed in PrimaryKeyColumns must also appear in the Columns list.

In the samples, Columns is the list of CdcColumnMapping entries,
where Column is the source SQL column name and Name is the RavenDB document field name.

Embedded tables (one level)

An embedded table needs:

PrimaryKeyColumns - Used to match items within the parent's array or map for UPDATE and DELETE
JoinColumns - Foreign key referencing the parent's PrimaryKeyColumns

The JoinColumns must correspond 1:1 (same count and order) to the parent's PrimaryKeyColumns:

Parent PK	Required JoinColumns	Valid?
`[id]`	`[order_id]` where `order_id` = parent's `id`	✓
`[id, year]`	`[order_id, order_year]`	✓
`[id]`	`[customer_id, order_id]`	✗ Extra column not from parent PK
`[id, year]`	`[order_id]`	✗ Missing `order_year`

DELETE events and REPLICA IDENTITY (PostgreSQL)

For DELETE events, the source database must include the join column values so CDC Sink can route the delete to the correct parent document.

If the join column is not part of the source table's primary key, the source database may need additional configuration to include it in DELETE events. For PostgreSQL, this is controlled by REPLICA IDENTITY.

See REPLICA IDENTITY for the PostgreSQL-specific requirement and how CDC Sink handles it automatically.

Multi-level nesting

Embedded tables can themselves have embedded tables, creating arbitrarily deep hierarchies.

Example: Multi-level nesting with companies, departments, and employees

csharp

new CdcSinkTableConfig
{
    CollectionName = "Companies",
    SourceTableName = "companies",
    PrimaryKeyColumns = ["company_id"],
    Columns =
    [
        new CdcColumnMapping() { Column = "company_id", Name = "CompanyId" },
        new CdcColumnMapping() { Column = "name",       Name = "Name" },
    ],
    EmbeddedTables =
    [
        new CdcSinkEmbeddedTableConfig
        {
            SourceTableName = "departments",
            PropertyName = "Departments",
            Type = CdcSinkRelationType.Array,
            JoinColumns = ["company_id"], // Root FK
            PrimaryKeyColumns = ["dept_id"],
            Columns =
            [
                new CdcColumnMapping() { Column = "dept_id",   Name = "DeptId" },
                new CdcColumnMapping() { Column = "dept_name", Name = "DeptName" },
            ],
            EmbeddedTables =
            [
                new CdcSinkEmbeddedTableConfig
                {
                    SourceTableName = "employees",
                    PropertyName = "Employees",
                    Type = CdcSinkRelationType.Array,
                    JoinColumns = ["dept_id"], // FK to the direct parent (departments) PK
                    PrimaryKeyColumns = ["emp_id"],
                    Columns =
                    [
                        new CdcColumnMapping() { Column = "emp_id",   Name = "EmpId" },
                        new CdcColumnMapping() { Column = "emp_name", Name = "EmpName" },
                    ]
                }
            ]
        }
    ]
}

Critical requirement for deep nesting:
All descendant tables must carry the root-level join columns as denormalized columns. These are the columns from the first embedded table's JoinColumns, which correspond to the root table's PrimaryKeyColumns.

In this example, departments.JoinColumns = ["company_id"], so the employees table must also include company_id even though it joins directly to departments via dept_id.

CDC Sink needs these values to route each descendant row to the correct root document in a single pass, without additional lookups.

Source schema to support this:

CREATE TABLE employees (
    company_id INT NOT NULL,   -- Denormalized root FK
    dept_id    INT NOT NULL,   -- Parent FK
    emp_id     INT NOT NULL,   -- Local PK
    emp_name   VARCHAR(200),
    PRIMARY KEY (company_id, dept_id, emp_id)
);

The denormalized root-level join column values must be present in the source row, but they do not need to be listed in the nested table's PrimaryKeyColumns.

For embedded tables, PrimaryKeyColumns identifies the item within its direct parent.

For PostgreSQL, including all routing columns in the source table's primary key also avoids the need for REPLICA IDENTITY configuration - the default DELETE events include all source primary-key columns.

Relation types

When defining a CDC Sink task, you choose how related SQL tables are represented in RavenDB:
embedded inside the parent document, or linked as references to separate documents.

The Type property on embedded tables controls the document structure:

Type	Use Case	Document Structure
`Array`	One-to-many: parent has many children	`"Lines": [{ ... }, { ... }]`
`Map`	One-to-many with direct key lookup	`"Lines": { "1": { ... }, "2": { ... } }`
`Value`	One-to-one: parent owns a single child	`"ShippingInfo": { ... }`

Array
Items are matched by PrimaryKeyColumns for UPDATE and DELETE.
Use when you need to iterate over all items.

Map
Items are stored as a JSON object keyed by the primary key value(s).
Use when you need fast direct-key access within the document.

Value
Stores a single embedded object.
Use for a single child object owned by the parent, such as an order's shipping info or billing address.

The following table summarizes how embedded and linked tables compare:

Consideration	Embedded	Linked
Data location	Stored inside parent document	Stored in a separate document
Access pattern	Read parent to get all data	Load parent, then load referenced doc
Updates	Automatic via CDC	Only if the referenced table is also configured as a root table
Document size	Grows with embedded items	Parent stays small
Use case	Parent owns child (orders own lines)	Independent entities (orders reference customers)

Choosing between embedded and linked

Use embedded tables when:

The child entity has no meaning outside the parent (order lines without an order)
You always read the parent and child together
You want a single-document read

Use linked tables when:

The referenced entity is independently meaningful (customers exist without orders)
The referenced entity is shared by many parents
You want to load referenced documents with RavenDB includes

Root tables​

Embedded tables​

Linked tables​

Primary key and join column requirements​

Root tables​

Embedded tables (one level)​

DELETE events and REPLICA IDENTITY (PostgreSQL)​

Multi-level nesting​

Relation types​

Choosing between embedded and linked​

Root tables

Embedded tables

Linked tables

Primary key and join column requirements

Root tables

Embedded tables (one level)

DELETE events and REPLICA IDENTITY (PostgreSQL)

Multi-level nesting

Relation types

Choosing between embedded and linked