New column in CosmosDB NoSQL does not show up in Analytical Store

@Jon Hawkins

Thanks for reaching out to Microsoft Q&A

It looks like you’re encountering a common issue with Azure Cosmos DB’s Analytical Store when adding new columns. Here are some insights and potential solutions:

Understanding the Issue

Schema Inference: The Analytical Store in Cosmos DB uses schema inference, meaning it automatically detects and represents the schema based on the documents in your container. If a new column is added only to some documents, it may not appear in the Analytical Store until it is present in all documents.

Impact on Queries: As you’ve noticed, this can lead to unexpected behavior in queries, especially for fields adjacent to the new column, which may return nulls if the new column is not consistently present.

steps to resolve

1. Manual Schema Definition: Unfortunately, Cosmos DB does not allow you to manually define the schema for the Analytical Store. It relies on the inferred schema from the transactional store. However, you can ensure that the new column is added to all relevant documents to make it visible in the Analytical Store.
2. Update Existing Documents: To make the new column available without modifying every document manually, consider using a bulk update operation. You can write a script to iterate through your documents and add the new column with a default value. This way, the new column will be present in all documents, allowing it to show up in the Analytical Store.
3. Use of Change Feed: If you’re frequently adding new columns, consider leveraging the Change Feed feature. This allows you to listen for changes in your container and automatically update documents as needed.
4. Check Analytical Store Settings: Ensure that the Analytical Store is properly configured for your container. You can verify this in the Azure portal under your Cosmos DB account settings

Hope this helps. Do let us know if you any further queries.