Cant extract data from quickbase, getting error "Errors during transform"

[mrdeds]

Cant extract data from Quickbase using Stitch, getting error "Errors during transform"

main - INFO Exit status is: Discovery succeeded. Tap failed with code 1 and error message: "Errors during transform". Target succeeded.

[dmosorast]

@mrdeds I was able to look into this a bit recently, and discovered some strange things with the Quick Base service. This specific case involves a datetime with the value 574395040800000, which is intended to be a Unix timestamp in milliseconds.

This specific timestamp translates to 20171-11-08 18:00:00 (UTC), which indicates a typo in the Quick Base UI, since it looks like it should be 2017 instead. It's unfortunate that Quick Base doesn't validate against this case and instead creates a timestamp that is unable to be parsed by most common libraries. There's no real 100% way I can think of to fix this case other than providing more information in the error message to help track down the data with this issue, and fixing it in Quick Base itself.

Tracking Down the Broken Record

In the meantime, you can find this information in the extraction logs by looking for the message towards the bottom that contains the text "does not match". This message is in the format:

[<field name>: <value> does not match {'format': 'date-time', 'inclusion': 'available', 'type': ['string', 'null'], 'selected': True}, : { <full record>: does not match <full schema> }]

The values listed in the above message where <full record> appears should have some information about the record that is in error, like IDs or other fields that you can use in the Quick Base UI to point you towards what table and record is having the issue.

---

If you'd like to submit a ticket to Quick Base to improve their date input validation to limit the year to 4 numbers, that may help. Their validation would be the most effective in preventing this in the future, since we can only detect issues, but not make a judgement call on where the typo is at this point in the data stream.

[dmosorast]

As of version 2.0.1 (#19), the messaging has been improved. The tap will now log the record at fault for more context, and issue a top-level message that contains the table name, timestamp value, and field name.

Keeping this issue open for now to track this.

[mrdeds]

Great info. We’ll be looking at that particular time stamp. I agree about the QuickBase issue. Thanks a lot El El jue, 5 de julio de 2018 a la(s) 3:09 p. m., Dan Mosora < notifications@github.com> escribió:

…

As of version 2.0.1 (#19 <#19>), the messaging has been improved. The tap will now log the record at fault for more context, and issue a top-level message that contains the table name, timestamp value, and field name. Keeping this issue open for now to track this. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#18 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AI3wQpy7-Sc1DxrHA6FK2j3IF64hsuusks5uDnJggaJpZM4U24SE> .

[emlama]

Hi, @dmosorast and @mrdeds, I'm a product manager at Quick Base and will pass this thread along to our API team. In the meantime, some additional details would be helpful:

1. Which API were you using?
2. Were you calling the Quick Base API with the human readable timestamp or using a Unix timestamp?

Feel free to add any other details that might be helpful!

[dmosorast]

Hi @msaforrian, thanks for jumping in! Below is my understanding of how the tap is calling the Quick Base API. To answer your questions by number quickly, I can point to the code.

1. Here is the code that makes all of the requests for the tap. It is constructing a GET request with query params specified un the URL, so I believe it's the non-XML API mentioned here in the Quick base documentation.
2. Here the tap is constructing the filter for the query. The value is stored as a human readable timestamp, but is converted to a Unix timestamp before the request. I should note that this appears to be working correctly. The request returns only data that has been updated for the specified table.

Here is an example request, for visual purposes:

{
    'clist': 'field1.field2.field3',
    'slist': '2',
    'options': "num-100",
    'query': '{2.AF.1533303489}'
}

Additional Info

I was able to reproduce the issue @mrdeds was having like so:

1. Create a table and add a Date/Time field to it.
2. Add a record to that table and intentionally typo the date (like 20171-11-08 18:00:00)
3. Make a query using the GET api for that record and see that it returns a very large timestamp that refers to the year 20171 (574395040800000)

In this case, the data wasn't mangled in any way, that's actually the value that I typed into the field, it's just that it's not quite a valid timestamp for most parsers.