Redshift Destination - Buffer Stream Uploading Duplicate files to S3 Staging area

  • Is this your first time deploying Airbyte?: No
  • OS Version / Instance: AWS Linux
  • Memory / Disk: 8Gb / 200 GB
  • Deployment: Docker Compose on Ec2
  • Airbyte Version: 0.40.22
  • Source name/version: MySQL 1.0.13 (also occurs on my other sources - Hubspot, Posthog
  • Destination name/version: Redshift 0.3.51
  • Step: Sync - Specifically during upload of buffer file to S3 Storage
  • Description of issue I saw:
    Essentially, for a small subset of tables (often seemingly random), the Redshift connector is uploading duplicate buffer files to S3, before it runs the COPY statement into the target _airbyte_raw_table_name. I can see that in S3, there is 1 file uploaded (I use {DATE} for the S3 Filename Pattern), and in the manifest file that is used in the COPY statement, the same file URL is listed twice. Therefore it is copy the same file twice. Causing the duplicates.

Solution: I found is that when I set the S3 filename parameter to {Timestamp}. then I get 2 files with different names (since the second upload take place 5-10 seconds after the first one). In this case, each file has a unique URL in the manifest. Each file has a different set of data, therefore it does not result in duplicates in the data.
See the log to showcase this situation.
Log File duplicate example.txt (6.5 KB)

Question: What causes certain streams to need to upload two files to S3, where other streams only need to upload 1? Table size is not a factor here, I see tables of all different sizes doing this. The first file seems to always be very small, with most of the data in the second file.

Suggestion: I would take away the option for users to set the S3 filename to {date} in the redshift connector. When Airbyte (seemingly randomly) creates two files to be uploaded to S3 instead of one, then the second file will overwrite the existing file; however, the manifest will have 2 URLs added to it, both pointing to the latest file. This causes duplicate data uploaded to the _airbyte_raw staging table for that final table. Airbyte should only let users select a naming convenction that results in a unique file name to separate between the files.

I look forward to hearing your input.

Hello there! You are receiving this message because none of your fellow community members has stepped in to respond to your topic post. (If you are a community member and you are reading this response, feel free to jump in if you have the answer!) As a result, the Community Assistance Team has been made aware of this topic and will be investigating and responding as quickly as possible.
Some important considerations that will help your to get your issue solved faster:

  • It is best to use our topic creation template; if you haven’t yet, we recommend posting a followup with the requested information. With that information the team will be able to more quickly search for similar issues with connectors and the platform and troubleshoot more quickly your specific question or problem.
  • Make sure to upload the complete log file; a common investigation roadblock is that sometimes the error for the issue happens well before the problem is surfaced to the user, and so having the tail of the log is less useful than having the whole log to scan through.
  • Be as descriptive and specific as possible; when investigating it is extremely valuable to know what steps were taken to encounter the issue, what version of connector / platform / Java / Python / docker / k8s was used, etc. The more context supplied, the quicker the investigation can start on your topic and the faster we can drive towards an answer.
  • We in the Community Assistance Team are glad you’ve made yourself part of our community, and we’ll do our best to answer your questions and resolve the problems as quickly as possible. Expect to hear from a specific team member as soon as possible.

Thank you for your time and attention.
Best,
The Community Assistance Team

Hello, thanks for all the info! I’m looking into this, but as this sounds like an intricate issue and we have a small team, I might need a few days to come up with some debugging ideas. Thanks for your patience and understanding!

Thanks!

The solution I used was to not set the S3 Filename pattern to {date} since that caused the overwrite behavior. When setting it to {timestamp} there was no duplication issue. I would recommend removing the {date} pattern suggestion for those using the Redshift destination with S3 staging.