Snowflake Loader: auto-configure staging location paths

[istreeter]

Currently, to use a staging location with the snowflake loader, you need to provide both a name and a path in the hocon file.

But we could reduce this down to just providing the name. The loader could lookup the location for itself using:

DESCRIBE STAGE my_snowplow_stage

[jbeemster]

Think only caveat for this is that to be deterministic the STAGE_LOCATION can only contain one S3 URL where technically it can have many URLs in an array.

[istreeter]

The loader can pick from the array the location that matches the location from the sqs message.

Re being "non-deterministic" -- are you referring to the case where files with the same name are found as sub-paths of multiple locations in the array? That's an interesting case that I had not thought of before. But... I believe the same problem exists even if we provide the name and location in the hocon file; so we don't make it any worse by switching to this different method.

[jbeemster]

The loader can pick from the array the location that matches the location from the sqs message.

Very true - just more complicated logic was all I was thinking about!

Re being "non-deterministic" -- are you referring to the case where files with the same name are found as sub-paths of multiple locations in the array?

I guess in this case you could pick the most specific path?

---

Agreed it doesn't make it worse just wanted to flag it was an array of paths so that case needs to be handled.

[spenes]

Type of the STAGE_LOCATION is array but Snowflake doesn't allow to set it to empty array or array that contains more than one item. Therefore, it should be okay to use first item in the array.