Automating Data Sharing with dbt and Snowflake Shares

In today’s data-driven world, securely sharing data with external partners can enhance collaboration and value creation. At the same time, managing these data exchanges must be scalable and secure. In this blog, we’ll explore how you can leverage dbt to automate the process of creating and managing Snowflake shares, allowing you to share data securely and effortlessly.

Why Use Snowflake Shares in dbt?

Traditionally, sharing data with external partners has involved manual processes that can be tedious and error-prone. With Snowflake’s Data Sharing feature, combined with dbt’s automated workflows, we can simplify this process. Here are some benefits of using Snowflake shares with dbt:

• Security: Only the necessary data is shared, and it’s securely stored in Snowflake.
• Scalability: Adding new partners is effortless with dbt’s version-controlled workflows.
• Version control: dbt allows us to control and audit the data being shared.

Creating a Snowflake Share (Manually)

To create a Snowflake share manually, We can run the following SQL commands:

create or replace share <share_name>;
alter share <share_name> add accounts=xy12345;
grant usage on database <database_name> to share <share_name>;

create schema if not exists <schema_name>;

create secure view <database_name>.<schema_name>.<view_name> as (
  select ...
);

grant usage on schema <database_name>.<schema_name> to share <share_name>;
grant select on view <database_name>.<schema_name>.<view_name> to share <share_name>;

Notes:

• Only accountadmin can create shares
  In Snowflake, the accountadmin role is the highest-level administrative role and has the necessary privileges to manage account-level operations, including the creation and management of data shares.
• When you share data in Snowflake via views, only secure views can be shared.
  A secure view is a type of Snowflake view that ensures the underlying query definition is fully encrypted and protected, so users cannot see how the data is queried or combined. It also prevents users from indirectly accessing data that isn’t meant to be shared by manipulating SQL queries.

Enter dbt

One of the key benefits of integrating dbt with Snowflake for data sharing is the ability to automate the entire process of creating and managing shares and views. Automation brings consistency, scalability, and error reduction compared to manual SQL execution.
While creating shares manually in Snowflake definitely works, using dbt streamlines the process by:

• Automating the creation of shares and views.
• Ensuring only production data is shared.
• Using hooks and macros for scalability.

Automating the Process Using dbt

Step 1: Create Reader Accounts
Reader accounts allow partners to access the shared data. You can automate this using tools like Terraform, which helps maintain version control over the accounts.

You can read more about using a Snowflake Reader Account for Easy Data Sharing in this Nimbus Intelligence blog post.

In my organization, we operate two Snowflake accounts. The first account collects data from various sources via the Nimbus Intelligence connector app. In this confidential account, we organize the data and filter out any sensitive information. We then use Snowflake’s sharing capabilities to securely transfer the necessary data to a second Snowflake account, which is dedicated to reporting purposes.

Step 2: Create the Share
Using dbt pre-hooks, you can automate the creation of Snowflake shares:

models:
  <project_name>:
    +pre-hook:
      - "{{ create_share('<share_name>', ['xy12345']) }}"  

and then use the create_share macro (in the macros folder >> macros/create_share.sql):

{% macro create_share(share_name, accounts) %}
  -- Ensuring only production data is shared
  {% if target.name == "Production" %} 

    {% set sql %}
      CREATE SHARE IF NOT EXISTS {{ share_name }};
      GRANT REFERENCE_USAGE ON DATABASE {{ target.database }}  TO SHARE {{ share_name }};
      GRANT USAGE ON DATABASE {{ target.database }} TO SHARE {{ share_name }};

      {% for account in accounts %}
        ALTER SHARE {{ share_name }} ADD ACCOUNTS = {{ account }};
      {% endfor %}

    {% endset %}

    {% set table = run_query(sql) %}

  {% endif %}

{% endmacro %}

Step 3-4: Create and share the Secure View
Define a Model in dbt
In your dbt project, create a new .sql file under the models directory for the view that you want to share. In the model definition, you’ll write the query that selects the data you want to expose:

select 
    order_id,
    product_id,
    quantity,
    price
from {{ ref('source_table') }}

Configure the Model as a Secure View
In dbt, you can specify that this model should be materialized as a secure view by adding the secure configuration to the model.

Share the Secure View
After the view is created, you can grant access to this view in Snowflake by creating a share (either via a dbt post-hook or manually in Snowflake). You’ll use a post-hook to automate the process of sharing the view after it’s created using the share_view macro:

{{ config(
  materialized='view',
  secure=true,
  post_hook="{{ share_view ('<view_schema_name>', '<view_name>', '<share_name>') }}"
) }}

The share_view macro (in the macros folder >> macros/share_view.sql):

{% macro share_view(view_schema, view_name, share_name) %}
  -- Ensuring only production data is shared
  {% if target.name == "Production" %}

    {% set sql %}
      --GRANT REFERENCE_USAGE ON DATABASE RAW TO SHARE {{ share_name }};
      GRANT USAGE ON SCHEMA {{ target.database }}.{{ view_schema }} TO SHARE {{ share_name }};
      GRANT SELECT ON TABLE {{ target.database }}.{{ view_schema}}.{{ view_name }} TO SHARE {{ share_name }}
    {% endset %}

    {% set table = run_query(sql) %}

  {% endif %}

{% endmacro %}

Managing Different Suppliers

In dbt, mapping suppliers via seeds involves creating a CSV file (seed) that contains the mapping between supplier IDs and their corresponding Snowflake account IDs. This seed is version-controlled and used within models to filter data based on the current supplier’s account ID, ensuring that only relevant data is shared with the appropriate supplier.

Manage Supplier Access via Seeds
We can map internal supplier IDs to Snowflake account IDs via a seed file (supplier_share_access_rules.csv) in dbt:

company_id,account_id
4,AB987654
5,CD123789
6,EF987321

In step 2, we created the share using a pre-hook configuration. We should add the different reader accounts to the configuration:

models:
  <project_name>:
    +pre-hook:
      - "{{ create_share('<share_name>', ['AB987654', 'CD123789', 'EF987321']) }}"

Include the mapping in the secure view
Add the following piece of code to the view in order to restrict it to the right supplier account:

WHERE company_id IN (
  SELECT company_id
  FROM {{ ref('supplier_share_access_rules') }}
  WHERE UPPER(account_id) = CURRENT_ACCOUNT()
)

Conclusion:

Automating Snowflake shares with dbt ensures that we can efficiently and securely manage data sharing with external partners. By leveraging hooks, macros, and version control, the process becomes scalable and much more manageable. Once everything is set up, adding new partners is as easy as adding a new entry to your seed file and running dbt.

Thank you for reading this blog.
Hope it helps!

Also check out our other blogs page to view more blogs on Power BI, Tableau, Alteryx, and Snowflake here.

Work together with one of our consultants and maximize the effects of your data. 

Contact us, and we’ll help you right away.