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 as a Source

  • 5 minute(s) read
Prev Next

Snowflake is a cloud-based data warehouse platform for storing and analyzing large volumes of data.

Authorize Connection to Snowflake

Before you can create a data source, authorize Dataddo to connect to your Snowflake database.

Prerequisites

  • A running Snowflake instance reachable from the internet via a public IP or hostname.
  • A database user with read access to the tables you want to extract.
  • Your firewall configured to allow Dataddo's IP addresses. See Network ACL.

Create the authorizer

  1. In Dataddo, go to Authorizers and click Authorize New Service.
  2. Select Snowflake.
  3. Fill in the connection details:
Field Details
Account identifier The account identifier assigned to your account by Snowflake. Example values are myorg-account123 or xy12345.us-east-2.
Database Name of the database in uppercase you will use for writing or reading the data.
Authentication Method Method for authenticating the connection to Snowflake. Default: password.
Username Username for authentication.
Password Password for authentication.
Certificate Certificate for Key pair-based authentication. You can upload or generate certificate in Security settings.
Schema The schema belonging to the chosen database. The default value is PUBLIC. Make sure to use uppercase.
Warehouse Specifies the virtual warehouse to use by default for queries. If left blank the default data warehouse will be used.
Region Mostly leave blank. You may specify a region, such as "eu-central-1", with this parameter. However, since this parameter is deprecated, it is best to specify the region as part of the account parameter.
Session Role Mostly leave blank. You can specify the default role to use for sessions initiated by the connection from Dataddo.
  1. Click Save. Dataddo validates the connection before the authorizer is created.

Capabilities

Snowflake supports the data extraction methods below. Each method differs in which row changes it captures and how much load it places on the source. Choose the one that matches your table and use case; for a full explanation and setup detail, see Database Replication.

Method New rows Updated rows Deleted rows Details
Table Replication by Timestamp Yes Yes No Tracks a datetime column (such as updated_at) and re-extracts a row whenever that timestamp advances. Low to medium load on the source.
Table Replication by Row Sequence Yes No No Tracks a continuously increasing numeric column (such as an auto-increment ID) to capture inserts only. Low load on the source, and supports an optional per-run row limit for very large tables.
Log-based Replication (CDC) - - - Not available for this connector.
Custom SQL Query Depends on query Depends on query Depends on query Runs your own SQL query, so you control exactly which rows and columns are returned, including joins, filters, and aggregation.

How the Incremental Methods Track Changes

The two incremental methods track progress differently:

  • Table Replication by Timestamp extracts the rows whose Change Tracking Column falls inside the source's relative date range (for example "last 24 hours"), and that window moves forward with the current date. A row re-enters the window whenever its timestamp is refreshed, which is how updates are captured. Two practical consequences: the tracking column must be set on insert and refreshed on every update (such as updated_at), and the window must be at least as wide as the gap between two runs, otherwise rows changed in between are missed.
  • Table Replication by Row Sequence remembers the highest value of the Sequence Tracking Column(s) extracted so far, and each run continues from that value. The first run starts from the beginning of the table. The optional Row limit per run caps one run's size, so the initial load of a very large table can be split across several scheduled runs.
  • In both methods, your optional WHERE clause is combined with the automatic tracking filter, so do not repeat the time or sequence condition in it.

How to Create a Snowflake Data Source

  1. In Dataddo, go to Sources > Create Source and select the Snowflake connector.
  2. Choose the Authorizer you created above.
  3. Select the extraction method that fits your use case (see the Capabilities table above).
  4. Fill in the required fields for the selected method, such as the schema, table, and columns.
  5. For the incremental methods, select the tracking column: the Change Tracking Column (a datetime column such as updated_at) for Table Replication by Timestamp, or the Sequence Tracking Column(s) (a strictly increasing numeric column such as an auto-increment ID) for Table Replication by Row Sequence.
  6. (Optional) Add a WHERE clause to filter the extracted rows. Do not repeat the time or sequence condition in it; Dataddo adds the tracking filter automatically.
  7. Click Test Data to preview the result, then click Save.

Troubleshooting

A custom query references a missing object. The run reports 002003 (42S02): SQL compilation error: Object 'DB.SCHEMA.VIEW_NAME' does not exist or not authorized.

  • Cause: the table or view does not exist or was renamed, or the user's role lacks SELECT on it.
  • Fix: check with SHOW TABLES IN SCHEMA db.schema or SHOW VIEWS IN SCHEMA db.schema, then grant access, for example GRANT SELECT ON VIEW db.schema.view_name TO ROLE dataddo_role.

A custom query has a syntax error. The run reports 001003 (42000): SQL compilation error: syntax error ... unexpected '.'.

  • Cause: malformed SQL, for example a missing space before a fully qualified name, or incorrectly quoted identifiers.
  • Fix: run the query in a Snowflake worksheet to find the exact position. Quote identifiers with double quotes and remove stray characters.

Key-pair authentication is rejected. The run reports that the JWT token is invalid.

  • Cause: the Snowflake key-pair private key was rotated.
  • Fix: re-upload the current private key in the Dataddo connector settings.

A schema is not authorized. The run reports Schema does not exist or not authorized.

  • Cause: the schema name is wrong, or the role lacks USAGE on the schema.
  • Fix: correct the name, or run GRANT USAGE ON SCHEMA x TO ROLE y.

Warehouses are suspended. The run reports that the free trial has ended, or that a resource monitor quota was exceeded.

  • Cause: Snowflake billing is not set up, or the monthly credit quota was reached and the warehouse was suspended.
  • Fix: add a payment method in Snowflake, or raise or reset the resource monitor.

A column is missing or the source fails on an unsupported data type. Cast the column to a supported type using a Custom SQL Query, for example SELECT CAST(my_column AS VARCHAR) AS my_column FROM my_table.

The data preview is empty. This is usually caused by one of the following:

  • the selected date range contains no data;
  • the database user does not have permission to read the selected table;
  • the selected table, columns, or tracking column are no longer valid;
  • an incompatible combination of options was selected.

Related Articles