Snowflake

On this page:

How Tabular works with Snowflake

To query Tabular tables from Snowflake, you configure a Snowflake user and role that Tabular in turn uses to create and update external Iceberg tables in your Snowflake account. You must also set up a Snowflake catalog integration and external volume with the same S3 bucket you use for your Tabular warehouse.

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

Terminology: A Tabular warehouse is semantically similar to a Snowflake database. Both are the top-level container in a 3-part table identifier. For example, <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; mirrored tables should be considered read-only. Making changes to the tables directly from Snowflake does not update Tabular and may break future synchronization.
• Snowflake does not leverage Tabular’s permission system to access tables. We recommend you use Snowflake’s permission system directly to restrict access as needed.

Connecting and configuring Tabular and Snowflake

Details for setting up and enabling the Snowflake Mirror are available on the warehouse Settings page.

Step 1: Create a Snowflake Connection

In this step, you provide Tabular with your Snowflake account URL, plus the Snowflake user name and role name 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. To get your exact account url, execute the following SQL in snowflake and copy the results:

select 
  'https://' || lower(current_account()) || '.snowflakecomputing.com' as snowflake_account_url;

• Snowflake User Name: The login name of the Snowflake user that Tabular authenticates with and uses to create and manage Snowflake tables. This user need not exist already; if it doesn’t, it will be created in the next step.
• Snowflake Role Name: The Snowflake role that owns the Snowflake objects created by Tabular. This role also need not exist already; if it doesn’t, it, too, will be created in the next step.

After you complete these 3 fields, in the top right area of the section click Apply Changes.

Upon completion, the system displays the following additional information, which is necessary to complete the remaining steps:

• Snowflake User Public Key: The public key you will associate with the configured Snowflake user in Step 2. This enables Tabular to authenticate to your Snowflake account for mirroring.
• Catalog Integration ID: The name of the catalog integration object you will create in Step 2.
• External Volume ID: The name of the external volume object you will create in Step 2.
• Warehouse s3 Location: The S3 path to your Tabular warehouse. Use this path to configure the Snowflake external volume with S3.

Step 2: Set up Snowflake

In this step, you create and configure the necessary Snowflake resources to enable Tabular to create and manage external tables:

• USER
• ROLE
• CATALOG INTEGRATION
• EXTERNAL VOLUME

Creating these resources typically requires the Snowflake ACCOUNTADMIN role.

From the Tabular warehouse Settings page, go to the Snowflake Mirroring section and click Step 2. Next, click the link to generate the necessary Snowflake SQL commands.

Step 3: Set up AWS

In this step, you configure AWS to enable Snowflake read access to the Tabular warehouse S3 location. You can do this either 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 this is enabled, Tabular automatically mirrors all databases and tables in a warehouse to Snowflake. You must complete all previous steps before attempting to enable mirroring.

When this is disabled, Tabular removes all the mirrored objects from Snowflake.
You can restore objects by re-enabling the mirror.