search

snowplow / sql-runner

Run templatable playbooks of SQL scripts in series and parallel on Redshift, PostgreSQL, BigQuery and Snowflake
http://snowplowanalytics.com
Apache License 2.0
80 stars 14 forks source link

Handle Snowflake error `-00001:` (closes #191) #192

Closed colmsnowplow closed 2 years ago

colmsnowplow commented 2 years ago

Patch release to address an issue where the Snowflake driver returns -00001: error - causing a job failure - but the queries were actually successful.

The apparent cause of this is an empty response and empty error from the server.

We mitigate this by grabbing the query ID, and using it to poll Snowflake for a genuine result. If that result was success, we proceed with the job. If there was any error, we return that error rather than the one the driver produces.

We also provide the queryID in the error message for all failures from here in, to better enable us to debug similar issues in partnership with Snowflake.

coveralls commented 2 years ago

Coverage Status

Coverage decreased (-0.8%) to 21.619% when pulling ff92d49c26e6b055cfd6f0a1dc59a7c9dea18f2c on release/0.9.7 into 6f3439fb9b4cacff75fd2eaa9cf67d53422c2207 on master.

colmsnowplow commented 2 years ago

The anticipation here, is that even if we get the -00001 error code, the query had actually been successful so we should continue? You're antipating ending up in the case statement on Line 234 of snowflake_target.go?

What we have seen when we've encountered the error in the wild is that yes, the query was actually successful. But there's no saying that'll be the case every time. Also I don't know if we encounter this error while the query is still running or after it's finished (I suspect it's the latter).

So yes, when we get this error we enter the case statement on line 234 of snowflake_target.go - where we're basically polling for a result to that specific query, and when we have one we proceed accordingly.

Note though that we can't reproduce this scenario at the moment, at least not easily.

colmsnowplow commented 2 years ago

Thanks for giving this a thorough test @adatzer!