My dad and I built a SQL layer for DynamoDB (DynamoSQL) because we kept running into the same problem: we needed to query information across multiple tables (or multiple record types within a single table) and had no good way to do it. We would either duplicate data across lots of records (denormalize everything upfront) and hope we guessed right, do multiple fetches and join in application code, or export somewhere else entirely.

We tried PartiQL but it doesn't support JOINs and the aggregation support is limited. Athena works but the latency and cost model felt wrong for anything that needs to be synchronous. We got so fed up that we went ahead and built something that runs standard SQL directly against existing DynamoDB tables (using partition keys and GSIs so it's not just doing full scans).

How is everyone else getting around this problem? I've heard people say "design your schema correctly and you won't need JOINs" which, sure, but that's not always possible. If you work with DynamoDB, do you have a better solution? Or is this a struggle that most teams just live with?