SnowFlake

SnowFlake is a cloud-native, fully-managed data warehouse-as-a-service (DWaaS) that enables scalable, secure, and high-performance data storage, processing, and analytics. Built on a unique multi-cluster, shared-data architecture, Snowflake decouples storage and compute resources, allowing independent scaling and cost optimization. For Boltic integration, Snowflake serves as a robust platform to:

1. Ingest and store structured, semi-structured, and unstructured data.
2. Process and query data efficiently through parallel execution, caching, and query optimization.
3. Integrate seamlessly with pipelines for data migration and transformation.
4. Facilitate secure role-based access controls for multi-tenant environments.

Snowflake is compatible with major cloud providers (AWS, GCP, Azure) and integrates with Boltic for flexible data operations, supporting features like:

• Data sharing across organizations.
• Time-travel and fail-safe for data recovery.
• Schema and query optimizations for real-time insights.

Getting Started

Requirements

Before proceeding, ensure the following:

1. Access to a Snowflake account with appropriate administrative privileges.
2. A clear naming convention for roles, users, warehouses, databases, and schemas.
3. An execution role with the ability to grant roles and privileges (e.g., ACCOUNTADMIN).

Step-by-Step Guide

1. Setting Variables

Define variables to dynamically manage roles, users, and other objects. Snowflake requires variables to be uppercase.

set boltic_role = 'BOLTIC_CDC_ROLE';
set boltic_username = 'BOLTIC_CDC_USER';
set boltic_warehouse = 'BOLTIC_CDC_WAREHOUSE';
set boltic_database = 'BOLTIC_CDC_DATABASE';
set boltic_schema = 'BOLTIC_CDC_SCHEMA';
set boltic_password = 'password';

2. Creating Roles

Roles are essential for managing user permissions.

use role securityadmin;
create role if not exists identifier($boltic_role);
grant role identifier($boltic_role) to role SYSADMIN;

3. Creating Users

Users authenticate and perform operations in Snowflake.

create user if not exists identifier($boltic_username)
password = $boltic_password
default_role = $boltic_role
default_warehouse = $boltic_warehouse;

grant role identifier($boltic_role) to user identifier($boltic_username);

4. Setting Up Warehouses

Warehouses handle compute operations.

-- change role to sysadmin for warehouse/database steps
use role sysadmin;

-- create warehouse
create warehouse if not exists identifier($boltic_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;

-- grant warehouse access to role
grant USAGE
on warehouse identifier($boltic_warehouse)
to role identifier($boltic_role);

5. Setting Up Databases

-- create database
create database if not exists identifier($boltic_database);

-- grant database access to role
grant OWNERSHIP
on database identifier($boltic_database)
to role identifier($boltic_role);

6. Create schema for a database

-- switch to db for which we need to schema
USE DATABASE identifier($boltic_database);

-- create schema
CREATE SCHEMA IF NOT EXISTS identifier($boltic_schema);

-- grant ownership of schema to the role
grant OWNERSHIP
on schema identifier($boltic_schema)
to role identifier($boltic_role);

7. (Optional) Using an Existing Warehouse, Database, and Schemas

   Minimal Privileges Setup:

To retrieve a list of databases and their associated schemas, the following privilege requirements must be met:

• Database-Level Privileges: The role must have the following privileges on the database:

• CREATE SCHEMA

• USAGE

• Schema-Level Privileges: The role must have one or more of the following privileges on the schema:

• USAGE

• CREATE EXTERNAL TABLE

• CREATE TABLE

• MODIFY

• MONITOR

• CREATE STAGE

-- Grant warehouse usage to the role
GRANT USAGE ON WAREHOUSE identifier($boltic_warehouse) TO ROLE identifier($boltic_role);

-- get current user
SELECT CURRENT_USER() AS executing_user;

-- grants the CREATE DATABASE privilege at the account level to the role if you want to allow new database creation
GRANT CREATE DATABASE ON ACCOUNT TO ROLE identifier($boltic_role);

-- Grant these privileges at database level
GRANT USAGE, CREATE SCHEMA ON DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant these privileges at schema level
GRANT USAGE, CREATE EXTERNAL TABLE, CREATE TABLE, MODIFY, MONITOR, CREATE STAGE ON SCHEMA identifier($boltic_schema) TO ROLE identifier($boltic_role);

EASY SETUP:

-- Grant all privileges on the database
GRANT ALL PRIVILEGES ON DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant create schema privilege on the database
GRANT CREATE SCHEMA ON DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant all privileges on all existing schemas in the database
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant all privileges on all existing tables in the specified schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA identifier($boltic_schema) TO ROLE identifier($boltic_role);

-- Grant create table privilege in the specific schema
GRANT CREATE TABLE ON SCHEMA identifier($boltic_schema) TO ROLE identifier($boltic_role);

-- Grant all privileges on future tables in the specified schema
GRANT ALL PRIVILEGES ON FUTURE TABLES IN SCHEMA identifier($boltic_schema) TO ROLE identifier($boltic_role);

-- Grant all privileges on future schemas in the database
GRANT ALL PRIVILEGES ON FUTURE SCHEMAS IN DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant all privileges on future stages in the database
GRANT ALL PRIVILEGES ON FUTURE STAGES IN DATABASE identifier($boltic_database) TO ROLE identifier($boltic_role);

-- Grant all privileges on existing stages in the specified schema
GRANT ALL PRIVILEGES ON ALL STAGES IN SCHEMA identifier($boltic_schema) TO ROLE identifier($boltic_role);

Test Configuration

To verify the setup, execute the following queries:

-- Verify user access
SELECT CURRENT_ROLE(), CURRENT_USER(), CURRENT_WAREHOUSE();

-- Check granted roles and privileges
SHOW GRANTS TO USER IDENTIFIER($boltic_username);
SHOW GRANTS TO ROLE IDENTIFIER($boltic_role);

Best Practices

• Use descriptive naming conventions for roles, warehouses, and schemas.
• Regularly review user's permissions for security compliance.
• Suspend warehouses when not in use to optimize costs.

Note

• When getting list of database and schema associated with those schema, it will only show those where
  • database privileges: ['CREATE SCHEMA', 'USAGE']
  • schema privileges : ['USAGE', 'CREATE EXTERNAL TABLE', 'CREATE TABLE', 'MODIFY', 'MONITOR', 'CREATE STAGE']

Setup Guide to Integrate Snowflake With Boltic

This guide will give you a brief idea of what steps you need to follow to integrate Snowflake into Boltic.

1. Search for snowflake destination: Go to integrations > destinations > Add new destination

   

2. Add new destination integration: Enter a unique name for this Snowflake integration.

3. Add new destination integration: Add description and further snowflake account credentials

4. Test and save: Validate your configuration by clicking Test & Save. This ensures that the connection is successfully established.

Congratulations! You’ve successfully configured Snowflake roles, users, warehouses, databases, and schemas for Boltic integration. For further assistance, refer to Snowflake’s official documentation.