Join Streaming Data in Amazon Kinesis Firehose with Data in S3 via Athena using a Data transformation Lambda function

Theoretically yes.

However, the Lambda synchronous invocation mode has a payload size limit of 6 MB for both the request and the response. Make sure that your buffering size for sending the request to the function is less than or equal to 6 MB. Also ensure that the response that your function returns doesn't exceed 6 MB.

Kinesis Data Firehose supports Lambda executions limited to 5 minutes per invocation. If your Lambda function exceeds 5 minutes you get the following error: Firehose encountered timeout errors when calling AWS Lambda. The maximum supported function timeout is 5 minutes.

The preferred method is to perform a lookup instead of query. Consider querying a DynamoDB table.

Also, consider pre-processing records using Lambda functions attached to your Kinesis Data Analytics application and performing reference data lookups in S3. Read this - > https://docs.aws.amazon.com/kinesisanalytics/latest/dev/lambda-preprocessing.html and https://docs.aws.amazon.com/firehose/latest/dev/data-transformation.html