> For the complete documentation index, see [llms.txt](https://docs.adiom.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.adiom.io/enterprise/running-dsynct/data-transformations/data-types.md). # IDs, Data Types and CDC Events ## Data Format During a sync, data can appear in three different formats depending on the operation: 1. **Initial Sync** -- A single binary blob representing the full document. 2. **Updates** -- A list representing the ID fields, plus a binary blob of the updated data. 3. **Deletes** -- A list representing only the ID fields. A correct mapping must handle all three cases. This means you may need to provide a mapping for both the data (to cover case 1) and the ID (to cover cases 2 and 3). The exact documents and events each source produces are listed per connector under [Connectors](/reference/connectors.md). ## CDC Events During CDC the transformer receives one of four event types. What it can see, and which parts of the mapping run, depends on the type. | Event | What the transformer receives | What runs | | --------------------- | ------------------------------------------------------------------- | ---------------------------------------------- | | Initial sync document | The full document. The ID is read from the document using `idkeys`. | `filter`, field mappings, `self`. Not `mapid`. | | Insert | The ID and the full document. | `filter`, `mapid`, field mappings, `self`. | | Update | The ID and the full document. | `filter`, `mapid`, field mappings, `self`. | | Partial update | The ID, the changed fields only, and a list of removed fields. | `filter`, `mapid`, field mappings, `self`. | | Delete | The ID only. | `filter`, `mapid`. | The MongoDB, Cosmos DB NoSQL, SQL Batch and DynamoDB sources send full documents on every update. They do not send partial updates. ### Rules That Follow From This **Map the ID in two places.** `mapid` sets the ID that updates and deletes are applied to. It does not change the document. The ID field inside the document is set by a `cel` expression. Both must produce the same value with the same type, or updates and deletes will not find the documents written by the initial sync. ```yaml mapid: id # ID used by updates and deletes cel: _id: id # ID field stored in the document ``` A `map` entry on the ID field is the exception: `map: {_id: bson_object_id}` is applied to the document field and to the ID of updates and deletes, so it does not need a matching `mapid`. This is why the ObjectID examples below work with `mapid: id`. **Build the destination ID from `id` only.** `mapid` and `filter` cannot read document fields, because deletes do not have them. If the destination ID needs a value that is not part of the source ID, that design will not work for CDC. **Use `id`, not the ID field, in expressions.** Write `_id: id` and not `_id: parent.id`. Both work for the initial sync, but `id` is the value that `mapid` also receives, so the two stay consistent. **A filter applies to all event types.** If `filter` drops an ID, its inserts, updates and deletes are all dropped. **Dropping by content does not drop the delete.** A document dropped because `self` returned `optional.none()` was never written, but a later delete for that ID is still sent to the destination, where it matches nothing. **Partial updates only contain the changed fields.** If a source sends partial updates, an expression that combines several fields (for example `doc.first + " " + doc.last`) fails when one of them is not part of the change. Field removals are passed on by name unless the mapping has a `partialunset` option. When `partialunset` is set and at least one removed field matches an entry, only the matching entries are passed on and the other removed fields in that event are dropped. List every field that can be removed, mapping fields that keep their name to themselves. ### Checking CDC Behavior Transform Studio shows the output for `Data` (initial sync), `Insert`, `Update`, `Partial Update` and `Delete` side by side. Compare the ID in the `Data` result with the ID in the `Update` and `Delete` results: they must be identical, including the type. See [Designing and Testing Transforms](/enterprise/running-dsynct/data-transformations/designing-and-testing.md#step-7-test-cdc). ## Data Types Connectors may support different data types. Currently we support **JSON** and **BSON**. When transferring data between connectors that use different types, you need to provide configuration to map between them. ### ID Mapping The ID is particularly important to transform because the data type and/or field names may differ between source and destination. **Defaults:** * **JSON**: single field called `id` * **BSON**: single field called `_id` IDs can also be composed of multiple fields. Use the `idkeys` (source) and `finalidkeys` (destination) properties in the config if the ID format does not match the defaults on either side. **General approach:** * Specify new fields under the `add` property. * Specify old fields to remove under the `delete` property. * Define a `mapid` expression so that update IDs can be set correctly. * Add a `cel` expression for each new ID field showing how it is populated from the data. #### The `id` Variable in CEL In CEL expressions, `id` is a built-in variable representing the document's ID. * If the ID has **one field**, `id` is the value of that field directly. * If the ID has **multiple fields**, `id` is a list of values. ```cel # Given id: {a: 1, b: 2} id[1] # returns 2 # Given id: {b: 2} id # returns 2 ``` You can set `idlist: true` at the top level of the config to force `id` to always be a list, even when the ID contains only one field. #### Values as CEL Sees Them Some source values do not arrive in CEL as the type you might expect. This matters most for IDs. | Source value | In CEL | To convert | | ----------------------------- | --------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------------- | | JSON number | A string. Arithmetic fails with `no such overload`, while string operations such as `self + "!"` or `size(self)` succeed silently | `int(self)` or `double(self)` | | Numeric ID from a JSON source | An integer on CDC events, a string on initial sync | `int(id)` in both `mapid` and `cel`, so both paths give the same type | | BSON ObjectID | Bytes | `bson_object_id_string(self)` for the 24-character string. `string(self)` fails. | | BSON date | Integer, milliseconds since epoch | `timestamp(self / 1000)` | | BSON int32, int64 | Integer (64-bit) | Add `map: int32` to write a 32-bit integer | | BSON binary, UUID, Decimal128 | Opaque objects unless `unwrapbson` is set. `string(self)` fails. A binary exposes `self.Data` (bytes) and `self.Subtype` | `bson_uuid_string(self)`, `bson_decimal128_string(self)`, `base64.encode(self.Data)`, or set `unwrapbson: true` | Fields that have no `cel` or `map` entry are converted automatically between JSON and BSON. From BSON to JSON, ObjectIDs become 24-character strings, dates become RFC 3339 strings and Decimal128 values become strings. Binary values, including UUIDs, have no JSON form and become an object `{"Subtype": , "Data": ""}` unless `unwrapbson` is set or the field has a mapping. From JSON to BSON, numbers become 64-bit integers or doubles and everything else keeps its JSON type, so dates stay strings unless you convert them with `timestamp(self)`. #### JSON to BSON Examples The source ID must be a string in these examples. For a numeric ID, use `int(id)` in place of `id`. Rename a string `id` (`"123"`) to a string `_id` (`"123"`): ```yaml defaultmapping: default mappings: - namespace: default delete: ["id"] add: ["_id"] mapid: id cel: _id: id ``` Map a string `id` (`"123"`) to an ObjectID `_id` (`{"$oid": "202cb962ac59075b964b0715"}`): ```yaml defaultmapping: default mappings: - namespace: default delete: ["id"] add: ["_id"] mapid: id cel: _id: id map: _id: bson_object_id ``` #### BSON to JSON Examples Rename a string `_id` (`"123"`) to a string `id` (`"123"`): ```yaml defaultmapping: default mappings: - namespace: default delete: ["_id"] add: ["id"] mapid: id cel: id: id ``` Convert an ObjectID `_id` (`{"$oid": "202cb962ac59075b964b0715"}`) to a string `id` (`"202cb962ac59075b964b0715"`): ```yaml defaultmapping: default mappings: - namespace: default delete: ["_id"] add: ["id"] mapid: bson_object_id_string(id) cel: id: bson_object_id_string(id) ``` #### Multi-Part ID Examples When the source ID is composed of multiple fields, `id` becomes a list. Use `idkeys` to declare the source ID fields and `finalidkeys` for the destination. Individual parts are accessed with `id[0]`, `id[1]`, etc. Map a two-part JSON ID (`region` and `user_id`) to BSON, renaming them to `_region` and `_user_id`: ```yaml defaultmapping: default mappings: - namespace: default idkeys: ["region", "user_id"] finalidkeys: ["_region", "_user_id"] delete: ["region", "user_id"] add: ["_region", "_user_id"] mapid: id cel: _region: id[0] _user_id: id[1] ``` Collapse a two-part JSON ID (`tenant` and `record_id`) into a single BSON `_id` string by concatenating them: ```yaml defaultmapping: default mappings: - namespace: default idkeys: ["tenant", "record_id"] delete: ["tenant", "record_id"] add: ["_id"] mapid: string(id[0]) + ":" + string(id[1]) cel: _id: string(id[0]) + ":" + string(id[1]) ``` Expand a single BSON `_id` back into a two-part JSON ID by splitting on a delimiter: ```yaml defaultmapping: default mappings: - namespace: default finalidkeys: ["tenant", "record_id"] delete: ["_id"] add: ["tenant", "record_id"] mapid: '[string(id).split(":")[0], string(id).split(":")[1]]' cel: tenant: string(id).split(":")[0] record_id: string(id).split(":")[1] ```