Skip to main content

Snowflake Documentation

Secured data sharing

Unravel leverages the Snowflake secured data share (Snow Share) option for data transfer over shared connections outside the Virtual Private Cloud (VPC) or through private links. This involves employing a serverless/warehouse task to independently replicate metadata from the schema in the customer Snowflake account, allowing you to share metadata directly with the Unravel Snowflake account in the same region. The data is polled and shared every 1 hour by default. The polling can be configured.

For secure data share from Snowflake, complete the prerequisites and configure the Secure data share option.

The solution provides the following benefits:

  • No requirement for a direct connection to Snowflake

  • Rapid setup and reduced complexity for security permissions

  • Availability of all Unravel features with configurable latency

However, the following potential disadvantages are also associated with this solution:

  • Serverless or warehouse task cost is determined by the required latency as a task runs to synchronize data from ACCOUNT_USAGE and INFORMATION_SCHEMA to a Snowflake temp table.

  • The increased latency is expected due to two polling activities: Customer ACCOUNT_USAGE to customer transit table and customer transit table to Unravel.

Prerequisites for Secured data sharing

The following prerequisites must be fulfilled before you use Snow Share for the Secured data sharing option:

Permissions

  • Create an unravel user in your Snowflake account.

  • Grant SELECT for the unravel user on SNOWFLAKE.ACCOUNT_USAGE schema, INFORMATION_SCHEMA, and the permission to monitor the warehouse (Query execution).

  • Create a database unraveldb  and a schema unravelschema. These databases are required to create a stored procedure for metadata collection from ACCOUNT_USAGE, INFORMATION_SCHEMA, and PROFILE, which is to be stored in the local schema.

  • Provide permissions to the unravel user to execute procedures for creating tables and serverless/warehouse tasks that populate the metadata.

  • Create replication procedures and execute in the customer Snowflake account to replicate the data.

  • Require Snow share access to share the local schema to the Unravel Snowflake account. Execute the snowshare script to give access to the Unravel Snowflake account.

Cost estimation

Snowflake Serverless task cost to the customer is approximately $39 per month with 4-hour latency plus approximately $20 per month for profile polling cost for 1000 queries per day.

Configuring the Secure data sharing option

The secure data sharing option for metadata loading is configured on both the customer's and Unravel's side. The following configurations must be performed at the customer end, and the share name must be confirmed with Unravel.

  1. Run the SQL statement from this link to create procedures and necessary functions.

  2. Run the following commands where UNRAVEL_SHARE is the sample database, and SCHEMA_4823_T is the sample schema. This is a one-time execution.

    Note

    If you have already created the database and schema, replace UNRAVEL_SHARE with the database name and SCHEMA_4823_T with the schema name you created. 

    CALL CREATE_TABLES('UNRAVEL_SHARE','SCHEMA_4823_T');
CALL REPLICATE_ACCOUNT_USAGE('UNRAVEL_SHARE','SCHEMA_4823_T',2);
CALL REPLICATE_HISTORY_QUERY('UNRAVEL_SHARE','SCHEMA_4823_T',2);
CALL WAREHOUSE_PROC('UNRAVEL_SHARE','SCHEMA_4823_T');
CALL REPLICATE_REALTIME_QUERY('UNRAVEL_SHARE','SCHEMA_4823_T',10);
CALL CREATE_QUERY_PROFILE(dbname => 'UNRAVEL_SHARE',schemaname => 'SCHEMA_4823_T', credit
=> '1', days => '1');

    After running these commands, you can verify that the tables are created in the schema.

    To continue the execution at specific intervals, you can create tasks, as shown in the following steps:

    Note

    The cost can be changed based on the requirements, schedule time, and lookback days.

    • Create account usage tables task

      CREATE OR REPLACE TASK replicate_metadata
WAREHOUSE = UNRAVELDATA
SCHEDULE = '60 MINUTE'
AS
CALL REPLICATE_ACCOUNT_USAGE('UNRAVEL_SHARE','SCHEMA_4823_T',2);

    • Create a history query task

      CREATE OR REPLACE TASK replicate_history_query
WAREHOUSE = UNRAVELDATA
SCHEDULE = '60 MINUTE'
AS
CALL REPLICATE_HISTORY_QUERY('UNRAVEL_SHARE','SCHEMA_4823_T',2);

    • Create warehouse replicate task

      CREATE OR REPLACE TASK createWarehouseTable
WAREHOUSE = UNRAVELDATA
SCHEDULE = '60 MINUTE'
AS
CALL warehouse_proc('UNRAVEL_SHARE','SCHEMA_4823_T');

    • Create profile replicate task

      CREATE OR REPLACE TASK createProfileTable
WAREHOUSE = UNRAVELDATA
SCHEDULE = '60 MINUTE'
AS
CALL create_query_profile(dbname => 'UNRAVEL_SHARE',schemaname => 'SCHEMA_4823_T', credit =>
'1', days => '1');

    • Create replicating information schema query history task

      CREATE OR REPLACE TASK replicate_realtime_query
WAREHOUSE = UNRAVELDATA
SCHEDULE = '30 MINUTE'
AS
CALL REPLICATE_REALTIME_QUERY('UNRAVEL_SHARE','SCHEMA_4823_T',10);

    • Start all the tasks

      ALTER TASK replicate_metadata RESUME;
ALTER TASK replicate_history_query RESUME;
ALTER TASK createWarehouseTable RESUME;
ALTER TASK createProfileTable RESUME;
ALTER TASK replicate_realtime_query RESUME;

  3. Run the following commands to share the Transient tables with the Unravel account. Replace ${CUSTOMER_NAME} with the name of the customer name.

    Note

    In the following sample, the HFB47355 account is in us-east-1 region, business-critical edition. For other regions, there can be different accounts to share. Confirm with unravel about the account that needs to be shared and replace HFB47355 with that account. 

    -- Share tables
Create share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
Grant Usage on database UNRAVEL_SHARE to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
Grant Usage on schema SCHEMA_4823_T to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE WAREHOUSE_METERING_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE WAREHOUSE_EVENTS_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE WAREHOUSE_LOAD_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE TABLES to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE METERING_DAILY_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE METERING_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE DATABASE_REPLICATION_USAGE_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE REPLICATION_GROUP_USAGE_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE DATABASE_STORAGE_USAGE_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE STAGE_STORAGE_USAGE_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE SEARCH_OPTIMIZATION_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE DATA_TRANSFER_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE AUTOMATIC_CLUSTERING_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE SNOWPIPE_STREAMING_FILE_MIGRATION_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE TAG_REFERENCES to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE QUERY_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE ACCESS_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE IS_QUERY_HISTORY to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE WAREHOUSE_PARAMETERS to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE WAREHOUSES to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE QUERY_PROFILE to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
GRANT SELECT ON TABLE REPLICATION_LOG to share ${CUSTOMER_NAME}_UNRAVEL_SHARE;
alter share ${CUSTOMER_NAME}_UNRAVEL_SHARE add accounts = HFB47355;
In this section: