Azure synapse Serverless SQL pool query returns NULL for column values

Question

I have enabled analytical store for my cosmos DB . When I query table via synapse (serverless SQL pool ) , It returns NULL for some columns.
But I am sure that those columns exist with values in COSMOS DB

I understood the reason for getting null values
Reason - Property name renamed in multiple documents .

We tried below approach, but no luck
1.Removed all existing documents from cosmos and tried .

Refer below screenshots for reference

From Cosmos:-

From Synapse:-

Please help me to fix this issue ASAP.
Thanks

Kadir

Answer 1

You are right ! If you change the name of a property across one or more documents, it will be treated as a new field. By applying this rename to every document in the collection, all the data will transfer to this newly named field, leaving the original field with NULL values in its place.

To better understand the situation, you can read more here : https://learn.microsoft.com/en-us/azure/cosmos-db/analytical-store-introduction

Based on this thread :

If you rename a property, in one or many documents, it will be considered a new column. If you execute the same rename in all documents in the collection, all data will be migrated to the new column and the old column will be represented with NULL values