Force Recreate Storage Integrations

[jpfrisch-emory]

We are using terraform to create the SnowDDL admin user/role and storage integrations and there have been cases where the integrations need to be updated for new buckets or other policy modifications. Is there a command line method to force the recreation of external stages to refresh the link?

[littleK0i]

I did some tests around it. Yes, it seems, replacing STORAGE INTEGRATION breaks associated stages silently.

• No changes in output of SHOW STAGES;
• DESC STAGE xxx returns error Integration 'XXX' associated with the stage 'XXX' cannot be found.;
• Running ALTER STAGE xxx SET STORAGE_INTEGRATION = "XXX"; fixes stage and makes it working again;

I suspect, all tools are affected equally. To be honest, I am not sure how to handle this safely.

If we add CLI option to replace stages, it might break associated pipes and directory tables.

If we try to add special reaction on SQL error from DESC command and run ALTER .. SET STORAGE INTEGRATION, this won't work well with plan action. DESC won't work until we apply ALTER, and ALTER cannot be applied during plan.

Since this change should be a relatively rare occasion, you may try running additional commands ALTER STAGE xxx SET STORAGE_INTEGRATION = "XXX" for each affected stage manually.

Alternatively, you may look into ways to update STORAGE INTEGRATION objects without REPLACE, but with ALTER. It should be possible to change everything with ALTER.

[jpfrisch-emory]

How does the underlying blueprint work for stages? I'm wondering if we could force an alter..set.. for each stage with a programmatic config on apply.

We keep all our external stages in a dedicated db/schema then grant privs as needed. This makes the organization/security easy to maintain but perhaps it also would support something like:

• procedure to run DESC then ALTER if needed

I'll look into terraform options for altering the storage integrations vs recreate.

Thanks

[littleK0i]

It is basic: https://github.com/littleK0i/SnowDDL/blob/master/snowddl/resolver/stage.py

1. Run SHOW STAGES, get some info.
2. Run DESC STAGE xxx, get detailed properties for each stage.
3. Any discrepancies? Apply ALTER STAGE to fix it.

Broken stages are not visible in SHOW STAGES. And DESC STAGE returns error instead, so we do not get properties until stage is fixed.

In theory, we could add CLI option to enforce ALTER STAGE ... SET STORAGE_INTEGRATION. The only problem is plan mode, which only produced SQL commands, but no execution. And unless we execute SET STORAGE INTEGRATION, we never get stage properties and cannot plan other queries for this stage.

So.. it is mostly Snowflake being inconsistent. It would be nice if they simply set storage_integration to NULL in output of SHOW STAGES command. SnowDDL would fix it automatically.

[littleK0i]

Another option is ... maybe you can avoid altering storage integration altogether by using wildcards? You do not need to list every external bucket, a wildcard would suffice.

E.g. s3://<org_name>-*.