API Custom Connector Silent Failure

[mike-prittie]

Hi all, I'm troubleshooting a situation using the API Connector Builder where I've configured a paginated stream that uses Page Increment with both a Page Number and a Page Size. I've set the page size to 1000 records, starting with page 0. The sync runs and completes "successfully" without errors. The destination is Snowflake, and when I check the resulting table, I find less than half of the records that I expect (~300k of the ~700k). I am using the Full Sync | Override sync mode.

I've verified the GET params and number of pages expected using Postman. The job log shows warnings but no errors (log output attached). The worker appears to be making its way through the pages (~ the first 300 pages) and then suddenly stops for an unknown reason. Any ideas on what's going on here and a possible resolution?

I'm wondering if the worker isn't freeing up memory along the way after each page is processed, is running out of memory, the scheduler mistakenly thinks it's done, and victory is declared.

Any help here would be much appreciated. I figured I'd post here before submitting an issue.

Job Logs:
default_workspace_logs_211_txt.txt

[girarda]

Hi @mike-prittie ,

Thanks for reporting the issue! I don't see anything obviously wrong from the logs. Are you willing the share the YAML file describing the connector? Feel free to DM me on Slack if you don't want to share it publicly (@alexandre Girard)

[mike-prittie]

Thanks for the response @girarda, I appreciate it! I don't mind sharing the YAML Config at all (there's nothing top-secret in there :D ).

Addition information that may be useful:

• Version 0.50.43
• Installed on AWS EC2 t2.large using docker-compose with default config.
• Target API Swagger docs for reference. Target endpoint is /reporting/customers-paginated (https://api.pos.dutchie.com/swagger/index.html)
• Attached YAML as txt file since upload won't accept yaml files
  dutchie_pos_api_test_yaml.txt

If there is anything else I provide you to help diagnose this issue, please let me know!

[mike-prittie]

Hi @girarda,

I spent some time trying to see if I could come up with a workaround for this issue, and while doing so I surfaced some more information. Sorry if this is a bit long, but I want to walk you though my process.

1. I ran the full sync, with a 1k page size, and it completes prematurely about halfway again (~700k total records). I looked at the generated table in Snowflake, and it contained 310,999 records. You'd think it would be 310,000 or 311,000 with a page size of 1k.

2. Using Postman, I take samples moving up the pages, and everything seems to be there until I hit page 312. It appears to get everything until it hits a page it doesn't like, then quits.

3. So, I move the starting page to 350 and start the job, and it pulls in another 379,999 records. Now I'm beginning to wonder if there is a troublesome response and/or record causing the problem.

4. Next, I decrease the page size to 100 and start at page 3110. The job runs quickly and exits, only loading a couple of hundred rows (some like 199 or 299, but I don't remember exactly).

I rinse and repeat this a few times to zero in on the troublesome record, but it doesn't help that the page contents are slowly shifting (by whatever ordering criteria the API implementation uses). I don't see anything jump out, so I use Postman to grab two pages of 100 near the source of the problem to scan manually, looking for something off. That's when I noticed that page N has 100 records, as you'd expect, but page N+1 only has 99 records!

5. So, I ran two jobs at the same starting page somewhere before the issue, but one with a page size of 100 and the other with a page size of 10. The jobs run quickly and exit. The destination table in Snowflake associated with a page size of 100 has 299 records, and the one with a page size of 10 has 229 records!

What I suspect is happening:

• Target API: Some post-fetch from backing store / pre-response error handling or filter is being applied to the records (or something similar) and is resulting in a record being removed from one of its response batches (the affected page size of 1000 becomes 999, if the page size is 100 becomes 99, 10 becomes 9, etc.)
• Airbyte: The Aibyte worker expects to receive pages of the declared page size until it receives a page with num_records < page_size. This is why there is no error. Airbyte thinks it hit the end of the line since it received a response with fewer records than the declared page size. If this is indeed the case, you might consider the alternative approach of looking for an empty page as the indicator of reaching the end of the line instead.

Let me know what you think of this and if I can do anything to help test a solution.

[lmossman]

@mike-prittie thank you for the detailed investigation and writeup, it is super clear! The issue you've described makes sense and seems to clearly explain why you are seeing this behavior.

I am going to capture this in an issue and bring it to the team for discussion, and will let you know if we could use your help at some point.

[mike-prittie]

Sure thing, I'm happy to help! Looking forward to a resolution here.

[mike-prittie]

Linking the associated issue here for reference: https://github.com/airbytehq/airbyte/issues/34883