Athena Spill Bucket Issue - Dynamo Connect Lambda

Hello,

The reason that you will find that same connector (i.e. same code) have different results on queries with different filtering condition is due to the design of spill bucket[1] while running a Federated query. As you may know, the Federated query rely on Lambda function to connect to external Data source (DynamoDB in this case). However, every Lambda function has a response limit of 6MB[2]. If the Lambda function response size exceeds 6MB, it need a S3 bucket to spill the data. On the other hand, if the required query amount of your SQL syntax is less than the limit size, the operation of "s3:PutObject" on the spill S3 bucket will not happen and the query shall succeed.

In summary, your testing and observation in this case is expected in the Federated query and limitation of Lambda. For the best practice of preparing the IAM role policy of Lambda execution role in Federated query, it is recommended to grant S3 actions as below, to the result and the spill bucket. For more information, please refer to document[3].

To resolve this issue, please add S3 permissions to the spill bucket to the Lambda execution role. As best practice, please consider reviewing example policies[3] and add required permission. As long as your Lambda execution role has correct permission, the Athena Federated query shall be able to succeed for all SQL syntax.

Please see the third party links for for PowerBI and DynamoDB[4][5].

Thank you.

References:

[1] https://github.com/awslabs/aws-athena-query-federation/wiki/FAQ

[2] Lambda quotas - https://docs.aws.amazon.com/lambda/latest/dg/gettingstarted-limits.html

[3] Example IAM Permissions Policies to Allow Athena Federated Query - https://docs.aws.amazon.com/athena/latest/ug/federated-query-iam-access.html

[4] Third party link - https://medium.com/@nadakkannu.smart/how-to-create-a-table-from-aws-dynamodb-load-the-data-to-power-bi-desktop-using-odbc-connector-2a28c22e00be

[5]Third party link - https://dynamodb.topowerbi.com/