Optimize Your
Snowflake Data Warehouse

Use Case:

"Automate Data Ingestion from Multiple Sources"

Solution:

                        
-- Create an external stage for data ingestion
CREATE OR REPLACE STAGE my_s3_stage
  URL='s3://my-bucket/data/'
  STORAGE_INTEGRATION = my_s3_integration;

-- Copy data into Snowflake tables
COPY INTO my_table
FROM @my_s3_stage
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
ON_ERROR = 'CONTINUE';

-- Automate the process using tasks and streams
CREATE OR REPLACE STREAM my_table_stream ON TABLE my_table;

CREATE OR REPLACE TASK my_ingestion_task
  WAREHOUSE = my_warehouse
  SCHEDULE = '1 hour'
AS
  COPY INTO my_table
  FROM @my_s3_stage
  FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY = '"')
  ON_ERROR = 'CONTINUE';
                    

Use Case:

"Implement Secure Data Sharing with Partners"

Solution:

                        
-- Create a share to provide access to specific data
CREATE SHARE partner_data_share;

-- Add necessary database and schema privileges
GRANT USAGE ON DATABASE sales_db TO SHARE partner_data_share;
GRANT USAGE ON SCHEMA sales_db.public TO SHARE partner_data_share;

-- Grant access to specific tables or views
GRANT SELECT ON TABLE sales_db.public.monthly_sales TO SHARE partner_data_share;

-- Add consumers to the share
ALTER SHARE partner_data_share ADD ACCOUNTS = 'partner_account';

-- On the consumer's side, create a database from the share
CREATE DATABASE partner_sales_db FROM SHARE provider_account.partner_data_share;