Querying Employee Data in Cosmos DB by Effective and Updated Dates

Question

How can employee data be stored in Cosmos DB to allow querying by updated date and effective date?

The updated date represents when data is changed in the database, while the effective date indicates when that change becomes effective. For example, if an employee has a name change effective on February 9th and a transfer on February 15th, the queries with effective date should yield the following results:

• On February 8th, it should retrieve the original name and original location.
• On or after February 9th and before February 15th, it should retrieve the new name and the original location.
• On or after February 15th, it should retrieve the new name and new location.

In addition to this, while queried on updated date, it should retrieve all the employees changed by that date.

What strategies or query patterns can achieve this functionality effectively?

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

To store and query employee data in Azure Cosmos DB with the ability to handle both updatedDate and effectiveDate, you need a structure that accommodates versioning and time-travel-like queries.

1.Schema Design

Each update to an employee's data should be stored as a separate document. Use the following fields in the document to support querying:

• EmployeeId: The unique identifier of the employee.
• EffectiveDate: When the change becomes effective.
• UpdatedDate: When the document was modified in the database.
• Validity: An optional field to indicate the start and end of the effective period (if you're querying historical data).
• Attributes: Store employee details such as name, location, etc.

2. Partitioning

Partition data by EmployeeId to group all documents for an employee together, making it easier to query by employee.

3.Query by Effective Date

4.Query by Updated Date

5.Query with Effective Periods

Providing an example to handle the scenarios you described:

1. Querying on February 8th (EffectiveDate):
   • Returns the document with EffectiveDate <= 2025-02-08T23:59:59Z.
   1. Querying on February 9th to 14th:
      • Returns the document with EffectiveDate <= 2025-02-09T00:00:00Z and excludes any newer ones.
2. Querying on February 15th or later:
   • Returns the document with EffectiveDate = 2025-02-15T00:00:00Z.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.