Using synthetic data in Snowflake¶

Overview¶

Synthetic data is data that is generated programmatically to serve as a substitute for real data. You can use synthetic data to represent data that is sensitive, confidential, or otherwise restricted. The synthetic data set has characteristics of the data set it represents, such as numbers of columns, column names, the data type of each column, and synthetically generated data values for each column. A data engineer can use synthetic data to test and validate workloads in Snowflake, particularly when the original data is sensitive and should not be accessible to unauthorized users.

In Snowflake, you can call the SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA stored procedure to generate synthetic data from up to five input tables. You specify an output table to store the synthetic data. Snowflake automatically creates and grants access to the output table to the role that calls the stored procedure. If you need to generate the output table again, you can overwrite the existing output table by modifying the stored procedure parameters.

Access control requirements¶

To generate synthetic data, you must use a role with each the following grants:

• USAGE on the warehouse that you want to use for queries.

• SELECT on the input table from which you want to generate synthetic data.

• USAGE on the database and schema that contain the input table, and on the database that contains the output table.

• CREATE TABLE on the schema to contain the output table.

All users can access the SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA stored procedure. Access is made available using the SNOWFLAKE.CORE_VIEWER database role, which is granted to the PUBLIC role.

Example: synthetic data from multiple tables¶

This example uses the Snowflake Sample Data database, which has been shared with nearly all Snowflake customers.

Follow these steps to generate synthetic data from multiple input table:

1. Configure the access control for the data_engineer role and allow them to create all of the necessary objects:

   USE ROLE ACCOUNTADMIN;
   CREATE OR REPLACE ROLE data_engineer;
   CREATE OR REPLACE DATABASE syndata_db;
   CREATE OR REPLACE WAREHOUSE syndata_wh;
   
   GRANT OWNERSHIP ON DATABASE syndata_db TO ROLE data_engineer;
   GRANT USAGE ON WAREHOUSE syndata_wh TO ROLE data_engineer;
   GRANT ROLE data_engineer TO USER jsmith;
   

   Copy

2. Create views from the Snowflake Sample Data database:

   CREATE SCHEMA syndata_db.sch;
   CREATE OR REPLACE VIEW syndata_db.sch.TPC_ORDERS_5K as (
       SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.ORDERS
       LIMIT 5000
   );
   CREATE OR REPLACE VIEW syndata_db.sch.TPC_CUSTOMERS_5K as (
       SELECT * from SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER
       LIMIT 5000
   );
   

   Copy

3. Query the input tables to view the data and confirm each table has 5000 rows:

   USE WAREHOUSE syndata_wh;
   SELECT * FROM syndata_db.sch.TPC_ORDERS_1K;
   

   Copy

4. Call the GENERATE_SYNTHETIC_DATA stored procedure to generate the synthetic data and store it in an output table:

   CALL SNOWFLAKE.DATA_PRIVACY.GENERATE_SYNTHETIC_DATA({
       'datasets':[
           {
             'input_table': 'syndata_db.sch.TPC_ORDERS_5K',
             'output_table': 'syndata_db.sch.TPC_ORDERS_5K_SYNTHETIC',
             'columns': {'O_ORDERKEY': {'join_key': True}, 'O_CUSTKEY': {'join_key': True}}
           },
           {
             'input_table': 'syndata_db.sch.TPC_CUSTOMERS_5K',
             'output_table': 'syndata_db.sch.TPC_CUSTOMERS_5K_SYNTHETIC',
             'columns' : {'C_CUSTKEY': {'join_key': True}}
   
           }
         ],
         'replace_output_tables':True
     });
   

   Copy

5. Query the output table to view the synthetic data:

   SELECT * FROM syndata_db.sch.TPC_ORDERS_5K_SYNTHETIC;
   

   Copy