Sporadic failure of Snowflake user defined functions in the carto h3 module
larssnek opened this issue · 5 comments
Describe the bug
We use the carto h3 module in Snowflake. We are using dbt and often when updating a model using the carto h3 module the user-defined functions are not available, but upon rerunning they suddenly are available.
As far as I can tell there is no pattern to when this happens and not. See my screenshot for two consecutive runs where one failed and one was successful. Nothing has changed between the runs and they should both have been successful.
To Reproduce
Hard to reproduce as this happens sporadic. I have been running a query in the Snowflake UI as well to try to reproduce. When running the following command it first failed and then it suddenly worked. Nothing changed in between
select sfcarto.carto.h3_fromlonglat(1, 2, 9);Expected behavior
I expect this to always be successful.
Screenshots
Additional context
N/A
Hello @larssnek
The listing SFCARTO was deprecated some months ago, so probably it's not fully available nowadays.
I recommend using the current listing of the "Analytics Toolbox": https://app.snowflake.com/marketplace/listing/GZT0ZKUCHJO. Instead of SFCARTO, the database is called CARTO_OS and it's active and maintained.
Hi, @Jesus89! I should probably have mentioned that we did this change. This is the CARTO_OS database, I've just renamed it to SFCARTO 😅 By the way I also think that e.g. the udf h3_fromlonglat has been renamed from the old SFCARTO listing to the new CARTO_OS listing, so this wouldn't have worked on the old one.
So the issue is still valid, could you please look into this?
Adding a screenshot from the link you sent me as "proof" it is installed and just renamed 👍
OK, so you are using the active listing.
Not sure if it was the cause of your issue, but there is a downtime of approx 10min in the replication process every time we deploy the toolbox. Maybe you executed your queries in that window.
We have had a meeting with the Snowflake Team in charge of Marketplace and replications and they say that they are working on two things:
- Providing a programmatic way of triggering the replications (now we need to manually edit the frequency of replication)
- Develop a zero-downtime automatic replication system.
Regarding the Analytics Toolbox, we will switch from continuous deployment to 2w-1m deployments, so this will happen with less frequency, and as soon as we have any of the options available, we will update our deployment system to have zero downtime.