DynamoDB - Extract date and Query

Question

I am having the following table in my DynamoDB. I want to get/extract all the data using the following condition or filters

• This Month data : This will be the set of records that belongs to 1st of this month to today. ( I think this I can achieve using the BEGINS_WITH filter , again not sure whether this is the correct approach )
• This Quarter data : This will be the set of records that belongs to this quarter, basically from 1st of April 2021 to 30th June 2021
• This Year data : This will be set of records that belongs to this entire year

Question : How I can filter/query the data using the date column from the above table to get these 3 types (Month , Quarter ,Year ) of data.

Other Details

• Table Size : 25 GB

• Item Count : 4,081,678

Answer 1

As mentioned in the above answer by @Seth Geoghegan , the above table design is not correct, ideally you should think before placing your Partition Key and Sort Key, still for the people like me who already have such kind of scenarios, here is the steps which I followed to mitigate my issue.

• Enabled DynamoDB Steams
• Re-trigger the data so that they can pass through the DDB Streams ( I added one additional column updated_dttm to all of my records using one of the script )
• Process the Streams record , in my case I broken down the date column above to three more columns , event_date , category , sub_category respectively and updated back to the original record using the Lambda

Then I was able to query my data using event_date column , I can also create index over event_date column and make my query/search more effective

Points to Consider

• Cost for updating records so that they can go to DDB Streams
• Cost of reprocessing the records
• Cost for updating records back to DDB

Answer 2

It looks like you have time-based access patterns (e.g. fetch by month, quarter, year, etc).

Because your sort key starts with a date, you can implement your access patterns using the between condition on your sort key. For example (in pseudo code):

Fetch User 1 data for this month

query where user_id = 1 and date between 2021-06-01 and 2021-06-30

Fetch User 1 data for this quarter

query where user_id = 1 and date between 2021-01-01 and 2021-03-31

Fetch User 1 data for this month

query where user_id = 1 and date between 2021-06-01 and 2021-06-30

If you need to fetch across all users, you could use the same approach using the scan operation. While scan is commonly considered wasteful/inefficient, it's a fine approach if you run this type of query infrequently.

However, if this is a common access pattern, you might want to consider re-organizing your data to make this operation more efficient.