Documentation Index

Fetch the complete documentation index at: https://docs.dataddo.com/llms.txt

Use this file to discover all available pages before exploring further.

Snowflake

  • 3 minute(s) read
Prev Next

Snowflake is a cloud data warehouse that separates compute from storage. The Snowflake destination lets you load data from any Dataddo source into your Snowflake tables and keep it up to date on a schedule.

Authorize Connection to Snowflake

Prerequisites

  • A running Snowflake account reachable by Dataddo. If your account uses a network policy, allow the Dataddo IPs in it.
  • A Snowflake user (or role) with USAGE, SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE on the target schema. Dataddo creates tables in the schema when a flow first runs, so the role needs CREATE TABLE.
  • To keep permissions on tables that Dataddo creates later, apply a future grant:
GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA your_schema TO ROLE dataddo_role;

Create the authorizer

In Authorizers, click Authorize New Service and select Snowflake, then fill in the fields:

Field Description
A name for this authorizer in Dataddo A label so you can recognize the connection later.
Account identifier The identifier Snowflake assigned to your account, such as myorg-account123 or xy12345.us-east-2.
Database Name of the database to write to, in uppercase.
Authentication Method Password-based or Key pair. Default: Password-based.
Username The Snowflake user Dataddo connects as.
Password The user's password. Required for password-based authentication.
Certificate The certificate used for key-pair authentication. Upload or generate it under Settings, Security. Required for key-pair authentication.
Schema The schema inside the database. Use uppercase. Default: PUBLIC.
Warehouse The virtual warehouse used for queries. If left blank, the account's default warehouse is used.
Region Usually left blank. Deprecated: prefer putting the region in the account identifier.
Session Role Usually left blank. The default role for sessions Dataddo opens.

Click Save. Dataddo validates the connection.

Create a Snowflake Destination

Go to Destinations, click Create Destination, and select Snowflake. Give the destination a name, choose the authorizer you created, and click Save.

Write Modes

The write mode decides how each flow run changes the target table. Snowflake offers the standard set below, and the default is insert. Which modes are available, and whether you can switch modes on an existing flow, can depend on the table's current state.

Mode Meaning Composite Key
insert Appends new rows on every run. No
insert_ignore Appends rows, skipping rows that already exist. Yes
truncate_insert Empties the table, then writes the current data. No
upsert Inserts new rows and updates existing rows matched by the Composite Key. Yes
update Updates existing rows matched by the Composite Key; fails rows with no match. Yes
update_ignore Updates matched rows, skipping rows with no match. Yes
delete Deletes rows matched by the Composite Key. Yes

Composite Key

The Composite Key is the set of columns whose combined values identify each row. Every mode except insert and truncate_insert needs one. You can select up to 4 columns.

Table Naming

Snowflake stores identifiers in uppercase, so Dataddo works in case-sensitive mode. Use uppercase for the schema and table name.

  • Schema: uppercase letters, underscores, and hyphens only.
  • Table name: uppercase, starts with a letter or an underscore, and contains only letters, numbers, underscores, and dollar signs.
  • You can put date-range patterns such as {{1d1}} in the table name.

Dataddo creates the table on the first write if it does not exist.

How to Create a Flow to Snowflake

  1. Go to Flows and click Create Flow.
  2. Add one or more sources.
  3. Add Snowflake as the destination and pick the authorizer.
  4. Choose a write mode, and a Composite Key for modes that need one.
  5. Set the schema and the table name in uppercase.
  6. Set the schedule and click Save.

Troubleshooting

Table name is not in uppercase

The flow's table name contains lowercase letters. Rewrite the table name in uppercase, delete the table in Snowflake, and restart the flow.

No active warehouse

The authorizer has no warehouse set. Reauthorize the Snowflake connection with the correct warehouse name and restart the flow.

Current role has no privileges on the table

A future grant was not applied, so a table Dataddo created has no privileges. Set the grants, including the future grant shown in Prerequisites, delete the table in Snowflake, and restart the flow.

Dataddo IP is not allowed to access Snowflake

Snowflake's network policy blocks Dataddo. Add the Dataddo IPs to the allowed list and restart the flow.

Related Articles