Snowflake

Snowflake is a cloud-based data platform that provides data warehouse-as-a-service designed for the cloud. The Data Connect integration with Snowflake allows you to automatically sync your Contentsquare data to Snowflake for advanced analysis and joining with other business data.

Before setting up the Snowflake integration, ensure you have:

• A Snowflake account with appropriate access
• Snowflake user credentials with a role that has the following privileges:
  • USAGE on database and schema
  • CREATE TABLE on schema
  • SELECT, INSERT, UPDATE, DELETE on tables
  • CREATE VIEW on schema (for all_events and other views)

To get started, you need to have the following information about your Snowflake account:

• Your Snowflake account name: Your account name can be found in the URL used to access Snowflake: <account_name>.snowflakecomputing.com.

• Your account region: We currently support the following regions:

  AWS:

  • us-west-2 (Oregon): region not included in snowflake URLs for this region only
  • us-east-1 (N. Virginia)
  • us-east-2 (Ohio)
  • eu-west-1 (Ireland)
  • eu-central-1 (Frankfurt)
  • ap-southeast-2 (Sydney)
  • ca-central-1 (Canada Central)

  Azure:

  • east-us-2 (Virginia)
  • west-us-2 (Washington)
  • canada-central (Toronto)
  • west-europe (Netherlands)
  • australia-east (New South Wales)
  • north-europe (Ireland)
  • centralus (Iowa)
  • uksouth (London)

  GCP:

  • us-central1 (Iowa)
  • europe-west4 (Netherlands)

If you are outside of these regions, let us know and we will work to add support for you.

To find this information in your Snowflake account, see the Analysis Setup > Connect > Snowflake page in Contentsquare. This page will only be available once Data Connect has been enabled for you by someone on our end.

1. Log in to Contentsquare.

2. Navigate to Analysis setup > Data Connect.

3. Select Connect next to Snowflake.

4. Create a database in Snowflake by copying and running the generated snippet:

   CREATE DATABASE heap_main_production FROM SHARE heap_uswest.share_<env_id>

5. Select Next.

6. Enter your Snowflake hostname ↗.

7. Select Connect.

Contentsquare uses Snowflake's Secure Data Sharing feature to provide you with access to your data without copying or moving it.

How Data Sharing Works:

• Contentsquare creates a share containing your data
• You create a database from this share in your Snowflake account
• Data stays in Contentsquare's Snowflake account (no data transfer)
• You query the data as if it were in your account
• No compute costs for data storage on your end

Benefits:

• Instant access: No wait time for data copying
• Always up-to-date: See data as soon as syncs complete
• No storage costs: Data lives in Contentsquare's account
• Secure: Snowflake's secure sharing with access controls

Managing Shared Data:

-- View available shares
SHOW SHARES;

-- See databases created from shares
SHOW DATABASES LIKE '%heap%';

-- Check when data was last updated
SELECT
  table_name,
  last_altered
FROM heap_main_production.information_schema.tables
WHERE table_schema = 'PUBLIC'
ORDER BY last_altered DESC;

While Snowflake data shares are efficient, following these practices ensures optimal query speed:

1. Use appropriate warehouse sizes:

-- For light queries (< 10K rows)
USE WAREHOUSE small_wh;

-- For heavy analytics (millions of rows)
USE WAREHOUSE large_wh;

2. Filter early and often:

-- Good: Filter before joins
WITH recent_sessions AS (
  SELECT *
  FROM heap_main_production.public.sessions
  WHERE time::date >= DATEADD('day', -30, CURRENT_DATE())
)
SELECT * FROM recent_sessions;

3. Leverage clustering hints:

-- Add clustering information for query optimizer
SELECT /*+ CLUSTER_BY(time) */ *
FROM heap_main_production.public.all_events
WHERE time >= DATEADD('day', -7, CURRENT_DATE());

4. Create materialized views for frequent queries:

CREATE MATERIALIZED VIEW my_database.my_schema.daily_sessions AS
SELECT
  time::date as session_date,
  device_type,
  COUNT(DISTINCT session_id) as session_count,
  COUNT(DISTINCT user_id) as user_count
FROM heap_main_production.public.sessions
GROUP BY session_date, device_type;

Snowflake's native integrations allow you to connect Data Connect data with popular BI tools:

Tableau:

• Use Snowflake connector
• Point to shared database: heap_main_production
• Enable query pushdown for optimal performance

Looker:

• Configure Snowflake connection
• Use heap_main_production as database
• Consider Looker Blocks (see below)

Power BI:

• Use Snowflake connector in Power BI Desktop
• Import mode recommended for smaller datasets
• DirectQuery for larger datasets

Metabase:

• Add Snowflake database connection
• Select heap_main_production database
• Build dashboards directly on shared data