Is it possible for Snowflake users/customers to allow external business partners, say the IT team from a different company, the ability to create tables and insert into them data they want to send you and vice versa, you make data tables available to them?
We currently exchange countless files of raw data between ourselves and other companies. They give us raw data and we process them and provide enhanced data back to them. It's a two way data exchange, but I want to know if they would be required to setup their own Snowflake Account or not. Ideally I would manage their users on my platform.
Currently files are exchanged back and forth via ftp servers or email so I'm wondering if a multi organization on one Snowflake account with permitted is possible and or if it's ever been tried before.
Essentially I'm asking if two way data exchange can take place on one master Snowflake Account that we manage.
From a purely technical perspective, it seems to me that you could create a user in your Snowflake account for each of your customers and set their permissions correctly such that they can write to one or more tables and read from one or more tables or views.
For example, suppose that your primary database is X and it contains tables E1 and E2, each of which has enhanced data for ALL customers. Meanwhile, you want the users to be able to deliver data to tables I1 and I2. One way to do this for customers A and B would be:
Database X
Database A (for customer A)
Database B (for customer B)
You could even turn streams on for all the I tables and then have tasks set up to take the data from them, enhance it, and add it to the E tables in database X.
Obviously, you need to make sure that users can only see data they're allowed to, including not seeing data that other people have written to the in-bound tables (I), which is why I suggest setting up a separate database for each customer. You could do it with schemas instead, or if you really wanted, you could do it all in one schema, but it seems to me the security will be easier if you at least put the customers in separate schemas. That way, you can have a script that, given a customer name, can create the new schema, tables, views, streams, tasks, and roles all at one shot.
And, of course, it's probably worthwhile to talk to your account team, though I'd be surprised if Snowflake would insist on your customers being Snowflake customers for this particular use case.