Skip to main content

Snowflake Documentation

Secured data sharing

Unravel leverages the Snowflake secured data 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

Architecture

Unravel Snowflake Architecture - Secured Data Sharing

Snowflake-architecture.png

The Unravel Snowflake Architecture - Secured data sharing is a comprehensive framework designed to integrate with the Snowflake data warehouse, providing users with robust data analytics and management capabilities. The architecture is divided into three main layers: User Layer, Core Layer, and Data Source Layer, each serving distinct purposes and functionalities.

The architecture emphasizes secure communication pathways, including external connections, HTTP/HTTPS traffic, and internal communications, ensuring data integrity and confidentiality. It provides a scalable and efficient platform for data analytics, capable of integrating with various external systems and notifying users through multiple channels.

  • User Layer: This layer interfaces directly with users and clients, offering web application access, REST API interactions, and external notifications through platforms such as Email, Slack, and Microsoft Teams. It ensures secure and authenticated access using username/password for web users and revocable tokens for API clients.

  • Core Layer: Deployed on virtual machines or cloud services like AWS, Azure, and SaaS, this layer includes critical components such as NGINX for load balancing, a notification service, various analytical and service modules, and a datastore. The Core Layer facilitates seamless data management and insight generation through components like the Intelligent Insight Generator (IIG) and Snowflake Worker. It supports development environments like Python and Java and runs on CentOS.

  • Data Source Layer: This layer securely connects to the Snowflake data warehouse using a Snowflake-JDBC secure link with local authentication. It handles both shared and customer-specific data, ensuring that data operations are performed within the same region (us-east-1) to optimize performance and reduce latency.

Sharing Data with Secured Data sharing

Snowflake-Sharingdata.png

This diagram outlines the process of securely sharing metadata between a customer's Snowflake account and Unravel (or a partner) using the Snowflake-Secured data sharing feature. This setup ensures that customer metadata remains within the customer's control while allowing Unravel to access and analyze the metadata as needed.

The workflow involves three main steps:

This process ensures secure, efficient, and on-demand access to customer metadata, enabling Unravel to perform analysis and monitoring

  • Export Metadata: Customers run scripts to create databases, schemas, and tables, exporting the metadata to transient tables.

  • Share Metadata via Snowflake Secured data sharing: The customer uses Snowflake's secure share feature to share the exported metadata with Unravel.

  • Poll Metadata: Unravel polls the shared metadata directly from the customer's storage through Unravel's Snowflake warehouse, without storing it on their end.

Prerequisites for secured Data sharing

The following prerequisites must be fulfilled before you use the secured data-sharing option:

Permissions and Roles

  • Assign the ACCOUNTADMIN role to the user.

  • Grant SELECT permission on the schema SNOWFLAKE.ACCOUNT_USAGE.

Unravel Account ID

Contact Unravel Support for Unravel account ID corresponding to the platform, region, and edition.

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: