I’m implementing an application which manages appointments for the barbershops. Each barbershop might have from 1 to thousands of clients. Upon appointment completion, I need to collect data and generate analytics dashboards as shown on the image below.
My current technical stack is:
- DynamoDB as a primary database
- API Gateway and Lambda for HTTP
- Amazon SNS for internal event publishing
Context:
SNS Events that my application publish:
- AppointmentFinished
- AppointmentBooked
- AppointmentCancelled
- …and others
My initial attempt was:
Storing pre-aggregated data (daily, monthly, yearly) in DynamoDB for efficient retrieval.
e.g
# Daily Aggregate
PK: BEAUTY_PAGE#<id>
SK: DAY#<yyyy-MM-dd>
appointmentsNumber: 0
uniqueClients: 0
# Monthly Aggregate
PK: BEAUTY_PAGE#<id>
SK: MONTH#<yyyy-MM>
appointmentsNumber: 0
uniqueClients: 0
# Yearly Aggregate
PK: BEAUTY_PAGE#<id>
SK: YEAR#<yyyy-MM>
appointmentsNumber: 0
uniqueClients: 0
Challenge:
After trying to implement another part of the analytics, I found that queries like “Find top 15 within a date range by appointment number” become difficult to execute. It requires loading all data into the application layer and performing sorting, which might not be efficient for 100,000 records.
Question/Request:
I’m open to exploring other AWS services for scalable and cost-effective analytics. Given my limited experience beyond the mentioned services, could you please suggest suitable solutions for implementing these analytics? And it’d be nice if you could explain in details which service to use and why.
Additional Considerations:
- Scalability for future growth is important.
- Cost-effectiveness is a current concern.
Thanks!