menu logo
Tabular.io Log In Get Started

Snowflake

Support for Apache Iceberg tables in Snowflake is currently in Private Preview. Before connecting Tabular to Snowflake, work with your Snowflake representative to ensure that Iceberg support is enabled for your account.

Overview

In order to query Tabular tables from Snowflake, you will configure a Snowflake user and role that Tabular will use to create and update external Iceberg tables in your Snowflake account. You must also set up a Snowflake external storage integration with the same s3 bucket that you are using for your Tabular warehouse.

When enabled for a warehouse, Tabular will mirror all the databases and tables in Tabular to the target Snowflake account. Any updates to these Tabular resources will automatically be reflected in Snowflake.

Terminology: A Tabular warehouse is semantically similar to a Snowflake database; the top-level container in a three part table identifier. E.g. <warehouse_name>.<database_name>.<table_name> in Tabular is equivalent to <database_name>.<schema_name>.<table_name> in Snowflake.

Current Limitations

  • This is a one-way replication, so mirrored tables should be considered read-only. Making changes to the tables directly from Snowflake will not update Tabular and may break future synchronization.
  • Snowflake does not leverage Tabular’s permission system to access tables. It is recommended that you use Snowflake’s permission system directly to restrict access as needed.

Configuration

Setting up and enabling the Snowflake Mirror is available on the warehouse Settings page.

Step 1: Create a Snowflake Connection

In this step, you will provide Tabular with your Snowflake account url along with the Snowflake user name and role name that you want Tabular to use when creating and managing Snowflake resources.

  • Snowflake Account URL: The full url to your snowflake account. This is typically of the form https://<account_id>.snowflakecomputing.com
  • Snowflake User Name: The login name of the Snowflake user that Tabular will authenticate with and will be used to create and manage Snowflake tables. This user does not have to exist, if not, it will be created in the next step.
  • Snowflake Role Name: The Snowflake role that will own the Snowflake objects created by Tabular. This role does not have to exist, if not, it will be created in the next step.

After filling out these three fields, click Apply Changes in the top right area of the section.

Upon completion, the following additional information will be displayed which will be used to complete the remaining steps:

  • Snowflake User Public Key: The public key that you will associate with the configured Snowflake user in Step 2. This enables Tabular to authenticate to your Snowflake account for mirroring.
  • Storage Integration ID: The name of the storage integration object that you will create in Step 2.
  • Warehouse s3 Location: The s3 path to your Tabular warehouse. You will use this to configure the Snowflake storage integration with s3.

Step 2: Setup Snowflake

In this step, we will create and configure the necessary Snowflake resources to enable Tabular to create and manage external tables: USER, ROLE, and STORAGE INTEGRATION. Creating these resources typically requires the Snowflake ACCOUNTADMIN role.

On the Tabular warehouse Settings page, click on Step 2 in the Snowflake Mirroring section. Next click on the link to generate the necessary Snowflake SQL commands.

Example Snowflake SQL commands:

-- This is the user that Tabular will use to mirror your warehouse
CREATE USER <snowflake_user_name> rsa_public_key='<snowflake_user_public_key>';

-- This is the role that Tabular will use to mirror your warehouse
CREATE ROLE <snowflake_role_name>;

GRANT ROLE <snowflake_role_name> TO USER <snowflake_user_name>;

-- allow Tabular to create databases
GRANT CREATE DATABASE ON ACCOUNT TO ROLE <snowflake_role_name>;

/*
    Next we create the Snowflake storage integration object pointing to
    the AWS bucket where your Tabular warehouse exists.  
    We also specify the AWS Role that you will use to allow Snowflake access to your bucket. 
    (we defaulted the the AWS Role name below, but you could change that if needed)
    
    This integration object can be created prior you creating a policy and role within AWS 
    as long as you follow up in the next step and create the AWS Role with the name below (STORAGE_AWS_ROLE_ARN).
    
    After STORAGE INTEGRATION creation, DESCRIBE INTEGRATION will give you two values to place into your AWS's Role's Trust Relationship.
      1. STORAGE_AWS_IAM_USER_ARN, this is placed in your AWS Role's Trust Relationship's Principal value
      2. STORAGE_AWS_EXTERNAL_ID, this is placed in your AWS Role's Trust Relationship's ExternalId value
*/

CREATE STORAGE INTEGRATION <storage_integration_id> 
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE          
  STORAGE_AWS_ROLE_ARN = '<aws_iam_role_arn>'
  STORAGE_ALLOWED_LOCATIONS = ('<warehouse_s3_location');
  
GRANT USAGE ON INTEGRATION <storage_integration_id> TO ROLE <snowflake_role_name>;
  
DESCRIBE INTEGRATION <storage_integration_id>;

/*
    We are almost there! The Snowflake side is prepared!
    Complete the setup in AWS by creating a Policy and Role enabling Snowflake access
    to your Tabular Warehouse bucket using the STORAGE_AWS_IAM_USER_ARN and STORAGE_AWS_EXTERNAL_ID found
    in the "DESCRIBE INTEGRATION <storage_integration_id>" results.
*/

Step 3: Setup AWS

In this step, you will configure AWS to enable Snowflake read access to the Tabular warehouse s3 location. This can be done directly in AWS or by using the Cloud Formation Template provided by Tabular.

  • Storage AWS IAM User ARN: The Snowflake AWS user that will assume your IAM role when accessing s3.
  • Storage AWS External ID: A unique identifier provided by Snowflake to secure the IAM role trust policy.

Example AWS IAM Policy:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
              "s3:GetObject",
              "s3:GetObjectVersion"
            ],
            "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::<bucket>",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "<prefix>/*"
                    ]
                }
            }
        }
    ]
}

Replace <bucket> and <prefix> with the appropriate values from the Warehouse s3 Location from Step 1.

Example IAM Role Role Trust Policy:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "AWS": "<snowflake_user_arn>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<snowflake_external_id>"
        }
      }
    }
  ]
}

Step 4: Enable Snowflake Mirroring

When enabled, Tabular will automatically mirror all databases and tables in a warehouse to Snowflake. All previous steps must be completed before attempting to enable mirroring.

When disabled, Tabular will remove all the mirrored objects from Snowflake.
Objects can be restored by re-enabling the mirror.