Skip to main content

How to Add Tables/Views to an Existing Share

Snowflake, a cloud-based data warehousing platform, enables sharing data through the "share" objects. However, you can use the Modelling and Transformation orchestrator (MATE), a powerful tool for seamless data-sharing management and orchestration in Snowflake, to extend the scope of your data-sharing capabilities within your Snowflake environment.

This guide describes how to add tables or views to an existing share using the DataOps MATE orchestrator.

Step 1 - Configure the SQL model​

In DataOps.live, the top workflow is your project YAML file that includes SQL models to define data transformation.

  1. In your project directory, browse to /dataops/modelling/models.

  2. In the configuration block of your existing model, add a post-hook as shown in the below code snippet. Or create a new model and add the below snippet.

    This will trigger the sharing process.

dataops/modelling/models/example_model.yml
config(
  post_hook=["{{share_macro(share_names=['share_name1', 'share_name2'])}}"],
  materialized='view',
)

Make sure to replace share_name1 and share_name2 with the names of the shares you want to add the tables or views to.

Step 2 - Create a data-sharing macro​

You must create a macro to handle the sharing process.

  1. Create a SQL file at /dataops/modelling/macros.
  2. Add the following code snippet to define the share_macro:
share_macro.sql
{% macro share_macro(share_names,
                      database_name=target.database,
                      schema_name=this.schema,
                      resource=this) %}
{% if execute %}
    {# Use role that owns the share #}
    {{ dataops.execute_sql('USE ROLE ROLE_OWNING_THE_SHARE;')}}

    {% for share_name in share_names %}
        {{ dataops.execute_sql('GRANT USAGE ON DATABASE ' ~ database_name ~ ' TO SHARE ' ~ share_name ~ ';')}}
        {{ dataops.execute_sql('GRANT USAGE ON SCHEMA ' ~ database_name ~ '.' ~ schema_name ~ ' TO SHARE ' ~ share_name ~ ';')}}
        {{ dataops.execute_sql('GRANT SELECT ON VIEW ' ~ resource ~ ' TO SHARE ' ~ share_name ~ ';')}}
    {% endfor %}

    {# Put things back as they were #}
    {{ dataops.execute_sql('USE ROLE DATAOPS_WRITER;')}}
{% endif %}
{% endmacro %}

Ensure that you replace ROLE_OWNING_THE_SHARE with the role that owns the share, and DATAOPS_WRITER with the desired role to revert to after the sharing process is complete.

Step 3 - Run the data-sharing macro​

Once you have configured the model and created the share macro, you can run it. When the model runs, the share macro is triggered by the post-hook you defined, granting usage on the specified database, schema, and view to the designated shares.

Step 4 - Verify the share settings​

After running the share macro, verifying that the tables or views have been successfully added to the existing share is essential. You can do this by checking the share settings and permissions within Snowflake.

Using the MATE orchestrator, you can easily enhance the extent of your data-sharing capabilities and establish a strong data access control framework within your organization. This ensures that only authorized users and groups have appropriate access to the data they need to perform their job.

Last updated on