# Snowflake

Connect your data warehouse for AI-powered analysis and blending

<Image align="left" src="https://docs.scoopanalytics.com/assets/legacy/89bd6472d424ad9403d631c65f8336478e103c61682b67fbd35b56e726c938d8-image-7b0debafe2.png" />

<br />

Connect Snowflake to Scoop to bring your data warehouse into AI-powered analysis. Query tables and views, snapshot entity data for change tracking, and blend warehouse data with CRM, marketing, and operational systems.

<br />

# Why Connect Snowflake to Scoop

| Capability | Benefit |
|------------|---------|
| **AI Analysis** | Natural language queries against warehouse data |
| **Snapshot Tracking** | Track changes in entity tables over time |
| **Data Blending** | Join warehouse data with SaaS applications |
| **Visualization** | Present warehouse data in interactive dashboards |
| **Scheduled Sync** | Automatic daily extracts keep data fresh |

# What You Can Analyze

| Data Type | Analysis Examples |
|-----------|-------------------|
| **Customer Data** | 360 views, segmentation, lifetime value |
| **Product Usage** | Feature adoption, engagement patterns |
| **Financial** | Revenue trends, cost analysis |
| **Operational** | Inventory, fulfillment, logistics |
| **Marketing** | Campaign performance, attribution |

# Connecting to Snowflake

## Step 1: Select Database Source

On the datasets page, select **Applications** as a source:

<Image align="center" src="https://docs.scoopanalytics.com/assets/legacy/e10fcf467ad6e91eb1ef9aa3dc49cb5d93f62ebd118f856e45b88282bc346232-image-b9bbec5801.png" />

Then select **Database**:

<Image align="center" src="https://docs.scoopanalytics.com/assets/legacy/d26a9ef8e7c4df20849e1456b429b40581febe1377481cabc56c4a35dd149984-image-6814becedf.png" />

## Step 2: Choose Dataset Type

Select whether the data should be snapshotted or treated as transactional:

<Image align="center" src="https://docs.scoopanalytics.com/assets/legacy/ae031d62e2dc3c4cb2622fd243e0aae96633f86e547032f7cf2c4b4b37893d28-image-14572fba34.png" />

| Type | Use When | Examples |
|------|----------|----------|
| **Snapshot** | Entity tables where records change | Customer profiles, inventory, accounts |
| **Transactional** | Event tables with immutable records | Orders, events, logs |

## Step 3: Configure Connection

Enter your Snowflake connection details:

<Image align="right" src="https://docs.scoopanalytics.com/assets/legacy/19e7929e22e5fe1fb38e6d1c56b01155a468cf37432a3c11e97b659fabbca934-image-eaf2de50e8.png" />

### Connection Parameters

| Parameter | Description | Example |
|-----------|-------------|---------|
| **Account** | Snowflake account identifier | `xy12345.us-east-1` |
| **Warehouse** | Compute warehouse name | `ANALYTICS_WH` |
| **Database** | Database name | `PRODUCTION` |
| **Schema** | Schema name | `PUBLIC` |
| **Username** | Service account user | `SCOOP_SERVICE` |
| **Password** | Service account password | `********` |

### Connection String Format

```
jdbc:snowflake://<account>.snowflakecomputing.com/?warehouse=<warehouse>&db=<database>&schema=<schema>
```

## Step 4: Name the Connector

Provide a descriptive name for this connection:

<Image align="center" src="https://docs.scoopanalytics.com/assets/legacy/fa34479992914538a512946ac76dcd0c38919cbaaed9dba6be653863973ea589-image-3aa635e7db.png" />

## Step 5: Configure Query

After saving the connection, specify:
- **Table or View**: Select the data source
- **Columns**: Choose which columns to extract
- **Filters**: Optionally limit the data

## Step 6: Schedule and Extract

- Check **Extract data now** for immediate sync
- Scoop automatically syncs overnight daily

# Query Best Practices

## Selecting Tables vs. Views

| Approach | When to Use |
|----------|-------------|
| **Direct table** | Simple extracts, full table needed |
| **View** | Complex joins, pre-filtered data, security |
| **Custom query** | Dynamic filters, aggregations |

## Recommended View Patterns

Create Snowflake views optimized for Scoop:

```sql
-- Customer 360 view for Scoop
CREATE VIEW scoop_customer_360 AS
SELECT
    customer_id,
    customer_name,
    segment,
    lifetime_value,
    last_activity_date,
    status
FROM customers
WHERE is_active = true;
```

## Filtering Large Tables

For tables with millions of rows:

1. **Create a filtered view** in Snowflake
2. Or use Scoop's **incremental loading** for transactional data
3. Or filter by date range in the query

# Snapshot Analysis

Configure as **Snapshot** to track changes in entity tables:

## When to Snapshot

| Table Type | Snapshot? | Why |
|------------|-----------|-----|
| Customer profiles | Yes | Track attribute changes over time |
| Product catalog | Maybe | Track price/status changes |
| Inventory levels | Yes | Track stock level trends |
| Orders | No | Transactional—records don't change |
| Events | No | Immutable log data |

## What Snapshotting Enables

- Track when customer segments change
- See inventory level trends over time
- Identify accounts at risk of churn
- Monitor pricing changes

See [Snapshot Datasets](../../Preparing%20Datasets/scoop-dataset-basics/snapshot-datasets) for details.

# Blending Snowflake with Other Data

## Snowflake + CRM

**Goal**: Enrich warehouse data with real-time CRM context

| Snowflake Data | CRM Data | Combined Insight |
|----------------|----------|------------------|
| Revenue history | Current pipeline | Customer potential |
| Usage metrics | Account health | Churn prediction |
| Historical orders | Current contacts | Relationship mapping |

## Snowflake + Marketing

**Goal**: Connect campaigns to downstream outcomes

- Campaign exposure → Conversion
- Attribution across touchpoints
- Full-funnel marketing ROI

## Snowflake + Support

**Goal**: Correlate product data with service needs

- Usage patterns → Ticket volume
- Feature adoption → Support costs
- Customer success scoring

# Security Best Practices

## Service Account Setup

Create a dedicated service account for Scoop:

```sql
-- Create service account
CREATE USER scoop_service PASSWORD = 'secure_password';
CREATE ROLE scoop_reader;

-- Grant read-only access
GRANT USAGE ON WAREHOUSE analytics_wh TO ROLE scoop_reader;
GRANT USAGE ON DATABASE production TO ROLE scoop_reader;
GRANT USAGE ON SCHEMA production.public TO ROLE scoop_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA production.public TO ROLE scoop_reader;

-- Assign role to user
GRANT ROLE scoop_reader TO USER scoop_service;
```

## Network Security

- Use Snowflake's network policies if needed
- Scoop connects from AWS IP ranges
- Consider PrivateLink for enhanced security

## Data Minimization

- Create views with only necessary columns
- Filter out sensitive data before extraction
- Use role-based access in Snowflake

# Performance Optimization

## Warehouse Sizing

| Data Volume | Recommended Size |
|-------------|------------------|
| < 1M rows | X-Small |
| 1-10M rows | Small |
| 10-100M rows | Medium |
| 100M+ rows | Large |

## Query Optimization

- Use clustered tables for large datasets
- Create materialized views for complex queries
- Schedule syncs during off-peak hours

## Incremental Loading

For large transactional tables:

1. Include a timestamp column
2. Configure incremental loading in Scoop
3. Only new records are synced each run

# Troubleshooting

## Connection Failures

- Verify account identifier format
- Check warehouse is running
- Confirm user credentials
- Review network/firewall settings

## Slow Extracts

- Increase warehouse size
- Add filters to reduce data volume
- Use views instead of complex queries
- Check for missing cluster keys

## Missing Data

- Verify table/view permissions
- Check filter conditions
- Confirm schema hasn't changed

# Related Topics

- [Database Connections](../databases) - Other database connectors
- [Snapshot Datasets](../../Preparing%20Datasets/scoop-dataset-basics/snapshot-datasets) - Track changes over time
- [Blending Datasets](../../Preparing%20Datasets/blending-two-datasets) - Combine with other sources
- [Adding Calculated Columns](../../Preparing%20Datasets/adding-calculated-columns) - Transform extracted data