28 Sep 19 · AzharuddinK ·       Add to Favorites  

Copying Prod data into isolated Dev Environment along with all the objects in Snowflake

I have a requirement where I need to copy the entire database from Production for a particular role which has restricted access to few tables and views into an isolated Dev Environment ? any suggestions of implementing it


posted on 28 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Two options:

  • Are you open to create a data share, to share the prod database / schema with DEV environment. That way you can do a select insert ?
  • Export the data to files, ftp them to the staging are of the DEV environment and then insert them into the tables

So let me rephrase my question again as you are pretty close to my requirements.

1) So my current production is shared with these sets of users who have a restricted role. The tables which are non-sensitive are stored in the schema which everyone else uses. The tables which are sensitive are kept in the separate schema and have data that is masked (For the sensitive data I have used secure views in the Prod env).

There is a requirement that the current data in production is what they want as is in an isolated DT environment. Now request will come in more frequently where I need to delete all the data from the DT environment and need to reload the data from Production again. 

I want to make the above process automated so that whenever they want this request I just execute a single script and all of the above tasks get completed.

Please feel free to add your comments...

You mean you have DatabaseA with masked tables,  Whenever the user requests, you will have to copy all the tables into a new DatabaseB which is not masked. Do i understand your requirement correctly ?

And this is in Snowflake or Google Bigquery ?

Sorry, And what does DT mean 🙂. I have read your question multiple times to understand the core of your requirement, so i could try to help!

Sorry, my bad... we have DT(Development) , QA and PROD environment.

So we have a single database in the PROD environment... where tables are distributed into different schema's. So for the sensitive tables in PROD, we have created secure views of the main table and stored in a separate schema. The rest of the non-sensitive tables are stored in different schema where we have just given the grant to select the tables.

Schema 1 --> Grant only to required tables to select

Schema 2 --> This has masked data.Created secure views of the tables where the masking logic is applied and as it is secure view definitions are hidden.

Now there is a request to move the entire data from PROD to DT(Development) Environment on regular basis. So as the PROD data is already masked we just need to move these data into DT(Development Env). 

These environments are in the same snowflake instance. 

Please feel free to add any suggestions as I am very new to writing as well :)

I get it now. 

Snowflake data share should work. Simply ask your account admin to share the Prod database as a outbound share with your isolated DEV environment. Then you can use zero copy cloning to clone the shared database in DEV to make another copy and work on it

Powered by Hittly.com