---
title: "Snowflake · Scrape.ly Docs"
description: "Add or upsert the results of each successful run as rows in your own Snowflake table."
url: https://scrape.ly/docs/snowflake
markdown: https://scrape.ly/docs/snowflake.md
---

# Snowflake

> Add or upsert the results of each successful run as rows in your own Snowflake table.

Adds the dataset items of each successful run as rows to a table in your own Snowflake account. Scrape.ly signs in with key-pair authentication: you create a Snowflake user for Scrape.ly, give it a public key, and paste the matching private key. The private key is stored encrypted and never shown again. There is no app to install and nothing to approve.

## Prepare Snowflake

Scrape.ly writes into a table that already exists; it never creates or changes tables. Create one with a column for each result field you want:

```sql
CREATE TABLE ANALYTICS.PUBLIC.RESULTS (
  URL        STRING,
  TITLE      STRING,
  PRICE      NUMBER(10,2),
  TAGS       VARIANT,
  SCRAPED_AT TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
```

Make a key pair on your computer. The private key stays with you until you paste it into Scrape.ly:

```bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out scrapely_key.p8 -nocrypt
openssl rsa -in scrapely_key.p8 -pubout -out scrapely_key.pub
```

Leave out `-nocrypt` to protect the key with a passphrase; Scrape.ly then asks for it once, to read the key, and does not store it.

Then create a role that may only use this table, and a user for Scrape.ly with the public key (the contents of `scrapely_key.pub` without the `-----BEGIN/END PUBLIC KEY-----` lines):

```sql
CREATE ROLE SCRAPELY_WRITER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE SCRAPELY_WRITER;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE SCRAPELY_WRITER;
GRANT USAGE ON SCHEMA ANALYTICS.PUBLIC TO ROLE SCRAPELY_WRITER;
GRANT SELECT, INSERT, UPDATE ON TABLE ANALYTICS.PUBLIC.RESULTS TO ROLE SCRAPELY_WRITER;

CREATE USER SCRAPELY
  TYPE = SERVICE
  DEFAULT_ROLE = SCRAPELY_WRITER
  DEFAULT_WAREHOUSE = COMPUTE_WH
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...';
GRANT ROLE SCRAPELY_WRITER TO USER SCRAPELY;
```

`UPDATE` is only needed for **Update existing rows**. Run `DESC USER SCRAPELY` to see `RSA_PUBLIC_KEY_FP`; it matches the fingerprint Scrape.ly shows after you save the key.

## Send results to a table

1. Click **New automation** (in **Automations**, or on an Actor's **Automations** tab) and pick **Send results to Snowflake**, or click **Or create manually** and, under **Send**, click **Snowflake**.
2. Enter the **Account identifier** from your account URL, such as `xy12345.us-east-2.aws` or `myorg-myaccount` (pasting the whole account URL works too), and the **User** (`SCRAPELY`).
3. Paste the **Private key**: the whole contents of `scrapely_key.p8`, including the `-----BEGIN PRIVATE KEY-----` and `-----END PRIVATE KEY-----` lines.
4. Optionally enter a **Warehouse** and **Role**. Empty uses the user's `DEFAULT_WAREHOUSE` and `DEFAULT_ROLE`.
5. Enter the **Table** as `DATABASE.SCHEMA.TABLE`, such as `ANALYTICS.PUBLIC.RESULTS`.
6. Choose the **Write mode**: **Add new rows**, or **Update existing rows (upsert)** with a **Key column**.
7. Optionally set **Max rows per run** (1 to 100,000, default 1,000).
8. Use **Save and send test**. The test signs in, checks that the table exists and that the role may write to it, and writes nothing.

**Connection**: Scrape.ly uses Snowflake's SQL API over HTTPS, and only ever sends requests to `<account>.snowflakecomputing.com`. Accounts that are only reachable through Private Link are not supported. If the user has a network policy, it must allow Scrape.ly's requests from the internet.

**Columns**: a result field is written to the column with the same name (upper or lower case doesn't matter). A nested field whose parent has no column of its own goes into the column named parent and child joined with `_`, so `address.city` goes into `ADDRESS_CITY`. Objects and lists go into `VARIANT`, `OBJECT`, or `ARRAY` columns as they are, and into text columns as JSON. Fields without a column are skipped, and the delivery history lists them. Columns without a field get their default value. Snowflake converts each value to the column's type; a value it can't convert, such as `abc` for a `NUMBER` column, skips that row.

**Upsert**: with **Update existing rows**, a row whose key column already holds the item's value is updated instead of added (`MERGE`). Snowflake has no unique indexes, so the key column needs none. Only the columns the item has a field for are updated; the others keep their values. Items without a value for the key column are skipped and counted. When one run has several items with the same key, the last one wins.

**When it writes**: only for succeeded runs, and only when the run returned items. Rows go in up to 1,000 at a time, so a failure partway leaves the earlier rows written, and the delivery history says how many rows are in the table. A row Snowflake refuses is skipped and counted in the delivery history; most other rows are still written.

**Cost**: the rows are written by your warehouse, so each run uses a few seconds of its compute, billed by Snowflake to your account. A suspended warehouse resumes for the write (when `AUTO_RESUME` is on) and Snowflake bills at least a minute each time it resumes.

**Limits**: each Snowflake automation of this kind writes at most 30 times an hour, and an account at most 500 exports a day (Google Sheets, S3, GitHub, Airtable, HubSpot, Salesforce, Notion, Dropbox, OneDrive, Postgres, Azure Blob, BigQuery, OpenAI Vector Store, and Snowflake together). A statement that takes longer than 60 seconds is stopped, and an export stops after 8 minutes. If the key sign-in is refused, there is no warehouse, the table doesn't exist, the role may not write to it, or none of the fields match a column, the delivery history on the automation's page says so.

## API

A webhook with a `snowflake` notification. Only `ACTOR.RUN.SUCCEEDED` is allowed.

```bash
curl -X POST https://api.scrape.ly/v2/webhooks \
  -H "Authorization: Bearer $SCRAPELY_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "eventTypes": ["ACTOR.RUN.SUCCEEDED"],
    "condition": { "actorId": "YOUR_ACTOR_ID" },
    "description": "Results to Snowflake",
    "notification": {
      "channel": "snowflake",
      "account": "xy12345.us-east-2.aws",
      "username": "SCRAPELY",
      "privateKey": "-----BEGIN PRIVATE KEY-----\nMIIEvQ...\n-----END PRIVATE KEY-----",
      "warehouse": "COMPUTE_WH",
      "table": "ANALYTICS.PUBLIC.RESULTS",
      "mode": "upsert",
      "keyColumn": "URL",
      "maxRows": 1000
    }
  }'
```

`mode` is `insert` (default) or `upsert`; `keyColumn` is only used with `upsert`. Add `passphrase` when the key is encrypted, and `role` to use a role other than the user's default. Responses carry `notification.snowflakeSettings` with `account`, `username`, `warehouse`, `role`, `table`, `mode`, `keyColumn`, `maxRows`, and the `keyFingerprint` of the public key; the private key is never returned. On `PUT`, leaving out `privateKey` keeps the saved one. In an automation, use a delivery with `"channel": "snowflake"` and the same fields.

---

- Previous: [BigQuery](https://scrape.ly/docs/bigquery.md)
- Next: [GitHub](https://scrape.ly/docs/github.md)
- All docs: [Docs index](https://scrape.ly/docs.md), [llms.txt](https://scrape.ly/llms.txt)