1

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.

Scheballs
  • 532
  • 3
  • 14
  • 1
    I recommend discussing this with your account team. It has implications beyond technical matters such contractual and legal considerations. – Greg Pavlik Sep 23 '21 at 17:29
  • 1
    Thanks @GregPavlik I did reach out to my account team as well. I am just curious if anyone out there has come across this scenario. – Scheballs Sep 23 '21 at 17:40

1 Answers1

2

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

  • Tables E1, E2

Database A (for customer A)

  • Tables I1, I2 (for customer to write into)
  • Secure Views V1, V2 (which offer relevant columns from X.E1 and X.E2, filtered to customer A)

Database B (for customer B)

  • Tables I1, I2 (for customer to write into)
  • Secure Views V1, V2 (which offer relevant columns from X.E1 and X.E2, filtered to 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.

Dylan Kaufman
  • 98
  • 9
  • Thank you Dylan, this makes sense to me. It might be worth a try. Also, here is the quick response I got from my sales engineer at snowflake; ""Well that could be easily done with grants where you guys manage one database and the other company manage/update/insert/delete on theirs. Definitely easier than managing FTP etc"" – Scheballs Sep 23 '21 at 19:17
  • 1
    Makes sense. And if any of your customers are Snowflake customers, you can do it with data sharing, but the same basic method. – Dylan Kaufman Sep 24 '21 at 23:36