Manifest missing file returned in describeBatch.js

[adamwpipes]

We are have begun load testing the lambda-redshift-loader in Production with a data source that is identical to our old process we must sunset. We noticed that when we compared our load test redshift table we were missing records. After investigating some specific examples, I found the following:

• The file was formatted correctly in S3 and triggered an event that started Lambda.
• The file was assigned to a batch with 22 other files (23 in total in the batch). Running "describeBatch.js" returns all 23 files with the associated S3 manifest file. However, when I view the manifest file it contains 22 files and is missing the 23rd file (last in the describeBatch.js result).

Looking at Redshift, I can see that the manifest was correctly loaded but without the file in question. There is no reference to the file in any of the stl_load tables.

• The associated batchId is in status "complete".
• LambdaRedshiftProcessedFiles returns the association between the missing file and batchId.
• The lambda doesn't fail, but occasionally an error is seen in CW logs indicating

error: The conditional request failed
{
"code": "ConditionalCheckFailedException",
"requestId": "",
"retryDelay": 25.41047740462168,
"retryable": false,
"statusCode": 400,
"time": "2022-12-22T00:22:32.534Z"
}

error:
"TableName": "LambdaRedshiftBatches",
"AttributeUpdates": {
"status": {
"Action": "PUT",
"Value": {
"S": "locked"
}
},
"lastUpdate": {
"Action": "PUT",
"Value": {
"N": "1671669428.94"
}
}
},
"Expected": {
"status": {
"AttributeValueList": [
{
"S": "open"
}
],
"ComparisonOperator": "EQ"
}
},
"ReturnValues": "ALL_NEW"
}

I regenerated the same file with a new name as a test and the missing records appeared in the load test table.

Do you have any ideas on where the error could exist?

[IanMeyers]

Hello,

Sorry to hear about this - we've have customers running this utility in production for many years without issue, so this is quite surprising to hear. The issue will have occurred in the createManifest() function (probably), where somehow the entries in the batch table haven't all been flushed to S3. I'll look at anything that might have changed in this part of the software to see if there's anything obvious, but it would also be helpful if you could share the logs from this batch run. These should be in CloudWatch Logs and you can search on the Batch ID.

Thx,

Ian

[IanMeyers]

One other thing - can you check if the file that was not found in the manifest is linked to the batch correctly in the LambdaRedshiftProcessedFiles table?

[IanMeyers]

I'd also like to point out that this module is on the path to deprecation, due to the release of native Redshift automatic loading from S3: https://aws.amazon.com/about-aws/whats-new/2022/11/amazon-redshift-supports-auto-copy-amazon-s3/

[adamwpipes]

Thanks Ian for the quick response! I'll try and extract the CW logs for the batch today. Regarding the LambdaRedshiftProcessedFiles table, the filename in question was linked properly to the batch.

Thanks for sharing the auto copy feature - this will probably work well for our use case. Essentially, we are needing to move our Redshift into a private subnet and previously we were streaming from Kinesis directly to Redshift (which requires opening the kinesis port). I will test the feature, but we have a short deadline likely before it moves out of preview.

I'll update if I am able to find any other culprit in the meantime.