Configuring the Cosmos DB for NoSQL Copy Activity in Fabric Pipelines
If you've got an application running on Azure Cosmos DB and someone in the business has asked for a Power BI report over that data, you're going to end up in a Fabric pipeline copy activity sooner or later. It's the most common way we move Cosmos DB data into a lakehouse for Australian clients, and it's also the place where a few small settings make the difference between a pipeline that runs in four minutes and one that eats your RU budget and throttles the production app at 9am.
We wrote about the Cosmos DB for NoSQL connector more broadly a few months ago. This post is narrower. It's about the copy activity configuration itself: the source tab, the destination tab, mapping, and the settings I think are worth changing from their defaults.
Microsoft's reference for this is here: Configure Azure Cosmos DB for NoSQL in a copy activity. It lists every property. What it doesn't tell you is which ones matter in practice, so that's what I'll try to cover.
The basic shape
A copy activity has a source, a destination, mapping and settings. Cosmos DB for NoSQL can be on either side. Reading from Cosmos into a lakehouse table is what we do most often. Writing into Cosmos happens less, usually when a client is seeding a container from a SQL database or pushing processed results back for an application to use.
You'll need a connection to the Cosmos DB account first. Account key works, but we'd steer you towards organisational account or service principal authentication where you can, mostly so you're not pasting account keys into connection settings and then forgetting to rotate them.
Source settings
When Cosmos DB is the source, the main choices are:
Container. Pick the container you're reading from. Straightforward.
Use query. You can either read the whole container or supply a SQL query. Reading the whole container is fine for small containers or a one-off load. For anything ongoing, write a query. Even a simple SELECT c.id, c.customerId, c.total, c._ts FROM c WHERE c._ts > @lastRun is much better than pulling every document and every property every time.
The _ts property is your friend for incremental loads. It's the last modified timestamp (Unix epoch seconds) that Cosmos maintains on every document. Store the high watermark from the last successful run somewhere (a lakehouse table or a pipeline variable fed from a lookup activity), and pass it into the query. That's the simplest incremental pattern we use, and it works well for most append-heavy or update-heavy containers. It doesn't catch deletes, which I'll come back to.
Page size. This controls how many documents come back per page of results. The default is fine for most cases. If you've got very large documents and you're seeing timeouts, lowering it can help. I wouldn't touch it unless you have a reason.
Preferred regions. If your Cosmos account is replicated across regions, you can tell the copy activity to read from a specific one. This is worth setting. If your production app writes to Australia East and you've got a read replica in Australia Southeast, point the copy activity at the replica. Your analytics load stays off the primary region and you're not competing with the app for the same throughput.
Detect datetime. This tells the activity whether to try parsing string values that look like dates into datetime types. Sounds handy. In practice it can surprise you when a field that happens to look like a date in some documents gets typed differently from the same field in others. We usually leave datetime parsing to the downstream notebook so it's explicit.
Additional columns. You can add columns to the output, like the source file name or a static value. We use this to stamp a load timestamp or a source system identifier on every row, which helps a lot when you're debugging later.
Destination settings
When Cosmos DB is the destination, the settings that matter are:
Write behaviour. Insert or upsert. Insert will fail on documents with an id that already exists in the same partition. Upsert will replace them. For most reload scenarios you want upsert. For a genuine one-time seed into an empty container, insert is fine and slightly faster.
One thing to be clear on: upsert replaces the whole document. It doesn't merge properties. If your source only has five of the twelve properties a document has in Cosmos, the other seven are gone after the upsert. We've seen this bite a client who assumed it would patch. It doesn't.
Write batch size and write batch timeout. These control how many documents get written per batch and how long a batch can take. The defaults are reasonable. If you're getting 429 throttling errors, the fix is usually more RUs or fewer concurrent connections, not fiddling with batch size.
Max concurrent connections. This caps how many connections the activity opens against Cosmos. If you're writing into a container that's also serving a live application, set this lower than you think you need. Throttling the production app to finish a data load faster is a bad trade.
Disable performance metrics analytics. This turns off the extra metrics collection the service does to give you performance tuning suggestions. Most people leave it on. Some turn it off to shave overhead on very large loads. It's not a big deal either way.
Mapping is where JSON gets awkward
Cosmos documents are JSON, and JSON doesn't map neatly to tables. The copy activity mapping tab is where you deal with that.
For flat documents, import schemas and the mapping looks after itself. For nested documents, you'll need to map nested properties using JSON paths, like $['address']['postcode'] into a postcode column.
Arrays are the harder case. If an order document contains an array of line items, you can set a collection reference on the mapping so each array element becomes its own row, with parent properties repeated. That's useful, but it only works for one array per mapping. If a document has two arrays you want flattened (say line items and payments), you need two copy activities, or you copy the raw JSON into the lakehouse and flatten it with a notebook.
Honestly, for anything with real nesting, the second approach is what we recommend. Land the documents as-is into the lakehouse Files area or a table with a JSON string column, then flatten in a Spark notebook or a dataflow. You keep the raw data, you can reprocess it when the shape changes, and you're not trying to express complex flattening logic in a mapping grid. Cosmos schemas drift. Applications add properties without telling the data team. A raw landing zone absorbs that change without breaking the pipeline.
Things to watch out for
RU consumption. Reading a whole large container is RU-hungry. If the container is on provisioned throughput and you're doing a full scan during business hours, you can throttle your own application. Run heavy loads off-peak, use a replica region, or use incremental queries. Ideally all three.
Deletes don't come through. Incremental loads based on _ts see inserts and updates. They don't see deletes, because a deleted document no longer exists to be queried. If deletes matter for your reporting, you need either soft deletes in the application (an isDeleted flag), a periodic full reload to reconcile, or a different approach altogether.
Consider mirroring. Speaking of different approaches, Fabric mirroring for Cosmos DB replicates data into OneLake in near real time without you building a pipeline or consuming RUs on the transactional store. For a lot of analytics scenarios it's a better fit than a copy activity. We still use copy activities when clients need tight control over what gets moved, need to reshape data on the way in, or are writing into Cosmos rather than reading from it. But if all you want is "Cosmos data available in Fabric for reporting", check whether mirroring fits first.
Partition keys on write. When writing to Cosmos, every document needs the partition key property populated correctly. If your source data doesn't have a clean value for it, the write will fail or land documents somewhere you didn't expect. Sort that out in mapping or upstream before the copy.
What a sensible pipeline looks like
For a typical read-from-Cosmos job, our pattern is something like this:
- A lookup activity reads the last watermark from a control table in the lakehouse
- The copy activity reads from Cosmos with a query filtered on
_ts > watermark, pointing at a read region - It lands the documents raw into a staging table, with a load timestamp added as an additional column
- A notebook flattens and merges the staged data into the curated tables
- A final step updates the watermark
It's not fancy. It's also easy to debug at 7am when someone asks why the dashboard is stale, which matters more than clever.
Getting help with it
The copy activity is one of those features that looks simple in the UI and turns out to have a lot of edges once you're running it against real application data. If you're bringing Cosmos DB data into Fabric and want to get the pattern right the first time, our Microsoft Data Factory consultants work on this regularly. For the broader platform design, including lakehouse structure and whether mirroring or pipelines suit you better, talk to our Microsoft Fabric consultants. And if the end goal is putting AI over that operational data, our Azure AI consulting team can help with what comes after the data lands.