Azure Cosmos DB

Azure Cosmos DB is Microsoft's fully-managed NoSQL database. It is serverless and designed for high-performance applications.

Supported services

Fivetran supports the following Azure Cosmos DB database services:

• Azure Cosmos DB for MongoDB

• Azure Cosmos DB for NoSQL

Supported configurations

Fivetran supports the following Azure Cosmos DB configurations:

Limitations

• We support Azure Cosmos DB with the native MongoDB API and NoSQL API. We do not support the Cassandra API, Gremlin API, and Table API instances. Contact Fivetran Support if you would like us to integrate with these other API instances.
• We utilize Azure Cosmos DB's change feed to sync your data. The change feed includes INSERT and UPDATE operations made to items within the container, but delete operations are not captured by the change feed. To capture deleted source data, we suggest that you add a soft-delete flag within your documents. Alternatively, if you are interested in tracking deleted data through Fivetran Teleport Sync, refer to our official documentation.

Features

Azure Cosmos DB for MongoDB

Azure Cosmos DB for NoSQL

Setup guide

This overview will give you a general idea of the kind of work needed to set up a Azure Cosmos DB connector. For specific instructions on how to set up your database, see the guide for your Azure Cosmos DB database type:

• Azure Cosmos DB for MongoDB
• Azure Cosmos DB for NoSQL

Sync overview

Once Fivetran is connected to your Azure Cosmos DB resource, we pull a full dump of all selected data from your database. The initial sync finishes when all containers that existed when the sync started have finished importing. Once the initial sync is complete, we use each container's change feed to pull all your new and changed data at regular intervals.

Data access methods

Pack mode options

Pack modes determine the form in which Fivetran delivers your data. To sync your data in Fivetran, you must select a pack mode. There are two pack modes, packed and unpacked.

NOTE: In the tables below, the text in parentheses next to the column name indicates the data type of that column. For example, "foo (INTEGER)" means the column name is foo and it stores INTEGER data.

Unpacked mode

Fivetran unpacks one layer of nested fields and infers data types. For example, the following source data:

{
  "_id": 1, <== key
  "foo": 2,
  "nested": {
    "baz": 3
  }
}

For Azure Cosmos DB for MongoDB, is delivered to your destination as follows:

For Azure Cosmos DB for NoSQL, is delivered to your destination as follows:

Packed mode (default)

In packed mode, the following source data:

{
  "_id": 1, <== key
  "foo": 2,
  "nested": {
    "baz": 3
  }
}

For Azure Cosmos DB for MongoDB, is delivered to your destination as follows:

For Azure Cosmos DB for NoSQL, is delivered to your destination as follows:

Switching pack modes

You can switch pack modes for a table at any time in your Fivetran dashboard.

IMPORTANT: We automatically perform a full connector re-sync when you change pack modes.

To change the pack mode for a connector, do the following:

1. In the connector dashboard, go to the Setup tab.
2. Click Edit connection details.
3. In the connector setup form, change the Pack Mode.
4. Click Save & Test.

History mode Private Preview

History mode is a sync mode that tracks the history of the changes in your source data. We leverage Azure Cosmos DB's all versions and deletes change feed mode to capture all intermediate changes. This change feed mode must be enabled in order for your connector to use history mode. The all versions and deletes change feed mode is currently in the preview phase, and it is only compatible with Azure Cosmos DB for NoSQL accounts.

TIP: To sign up for the all versions and deletes change mode preview, follow Microsoft's Get Started with Change feed modes in Azure Cosmos DB instructions.

Once you've enabled this feature along with continuous backups on your Azure Cosmos DB account, reach out to our Support Team to enable history mode for your existing connectors.

Replication speeds

Two major factors can cause disparities between our estimated and the exact replication speed for your Fivetran-connected databases: network latency and the amount of request units (RUs) you have provisioned for your Azure Cosmos DB resource. Make sure your monitored container is not experiencing throttling; otherwise, you will experience delays when syncing the change feed.

The ability to sync changes quickly also depends on the sync frequency you configure. The risk of the sync falling behind, or being unable to keep up with data changes, decreases as the sync frequency increases. We recommend a higher sync frequency for data sources with a high rate of data changes.

Schema information

Fivetran tries to replicate the exact databases and containers from your Azure Cosmos DB resource to your destination according to our standard database update strategies. For every schema in the Azure Cosmos DB container that you connect, we create a schema in your destination that maps directly to its native schema. This ensures that the data in your destination is in a familiar format to work with.

Fivetran-generated columns

Fivetran adds the following columns to every table in your destination:

• _fivetran_id (STRING) a one-way hashed value that uniquely identifies each row. This is generated from the id and optional partition key value of each Azure Cosmos DB item.
• _fivetran_deleted (BOOLEAN) marks rows that were deleted in the source collection.
• _fivetran_synced (UTC TIMESTAMP) indicates the time when Fivetran last successfully synced the row.

We add these columns to give you insight into the state of your data and the progress of your data syncs. For more information about these columns, see our System Columns and Tables documentation.

NOTE: _fivetran_id is not applicable to Azure Cosmos DB for MongoDB

Type transformations and mapping

As we extract your data, we match Azure Cosmos DB document-based data types to types that Fivetran supports. Fivetran supports all Azure Cosmos DB CORE data types.

The following table illustrates how we transform your Azure Cosmos DB data types into Fivetran-supported types:

NOTE: We do not support OBJECT as a primary key(_id) type for Azure Cosmos DB for MongoDB API.

If we are missing an important data type that you need, reach out to support.

In some cases, when loading data into your destination, we may need to convert Fivetran data types into data types that are supported by the destination. For more information, see the individual data destination pages.

Nested data in unpacked mode

If the first-level field is a simple data type, we map it to its own type. If it's a complex data type such as an array or JSON data, we map it to a JSON type without unpacking. We do not automatically unpack nested JSON objects to separate tables in the destination. Any nested JSON objects are preserved as is in the destination so that you can use JSON processing functions.

For example, the following JSON...

{"street"  : "Main St."
"city"     : "New York"
"country"  : "US"
"phone"    : "(555) 123-5555"
"zip code" : 12345
"people"   : ["John", "Jane", "Adam"]
"car"      : {"make" : "Honda",
              "year" : 2014,
              "type" : "AWD"}
}

...is converted to the following table when we load it into your destination:

Excluding source data

If you don’t want to sync all the data from your source database, you can exclude databases, containers, or partial data from your syncs on your Fivetran dashboard. To do so, go to your connector details page and uncheck the objects you would like to omit in subsequent syncs. For more information, see our Data Blocking and Column Hashing documentation.

Initial sync

When Fivetran connects to a new Azure Cosmos DB resource, we first copy all data from every container in every database (except for those you have excluded in your Fivetran dashboard) and add Fivetran-generated columns. We copy data by performing a read on the container change feed from its beginning.

NOTE: We mark the progress at a regular interval throughout the initial sync. In case of sync stoppage or failure, we will pick up from the last successful point of data replication and continue importing in the next sync.

Updating data

Fivetran performs incremental updates of any new or modified data from your source database. We use Azure Cosmos DB's change feed to detect changes to the selected containers.

Fivetran uses Azure Cosmos DB's built-in id field, along with the partition key value that may be present in each container, to uniquely identify rows. This unique identifier is stored in the destination as a new column, _fivetran_id. Once we identify updated records, we merge the changes to your documents into the corresponding tables in your destination using the identifier:

• Every inserted row in the source generates a new row in the destination with _fivetran_deleted = FALSE.
• Every updated row in the source updates the data in the corresponding row in the destination, with _fivetran_deleted = FALSE.

NOTE: _fivetran_id is not applicable to Azure Cosmos DB for MongoDB. The _id field uniquely identifies rows.

Deleted data