Snowflake Connector

The Snowflake Connector allows you to load data from your existing Snowflake account into Tinybird.

The Snowflake Connector is fully managed and requires no additional tooling. You can define a sync schedule inside Tinybird and execution is taken care of for you.

Load a Snowflake table

Load a Snowflake table in the UI

Open the Tinybird UI and add a new Data Source by selecting the + icon next to the Data Sources section on the left hand side navigation bar (see Mark 1 below).

In the Data Sources modal, select the Snowflake connector.

Enter your Snowflake Account Identifier. To find this, log into Snowflake, find the account info in the bottom left of the screen (see Mark 1 below) and then copy the Account Identifier (see Mark 2 below).

In the Connection details modal, configure authentication with your Snowflake account. Enter your User, Password and Account Identifier(see Mark 1 below).

The next screen allows you to configure access to your Snowflake resources. First, select the role (see Mark 1 below) and Warehouse (see Mark 2 below) that Tinybird should use to access your data. Next, copy the SQL snippet from the text box (see Mark 3 below).

This SQL snippet creates a new Snowflake Storage Integration linking your Snowflake account with a Tinybird staging area for your workspace.

It also grants permission to the given role to create new Stages to unload data from your Snowflake Account into Tinybird.

With the SQL query copied, open a new SQL Worksheet inside Snowflake. Paste the SQL into the Worksheet query editor (see Mark 1 below). Note that you must edit the query and replace the <your_database> fragment with the name of your Snowflake database. Finally, select the Run button (see Mark 2 below). This statement must be executed with a Snowflake ACCOUNTADMIN role, since Snowflake Integrations operate at Account level and usually need admin permissions.

Note: you must edit the query and replace the <your_database> fragment with the name of your Snowflake database.

You should see the response Statement executed successfully.

Return to your Tinybird tab to resume configuring the Snowflake connector. Set a name for the Snowflake connection (see Mark 4 below). Complete this step by selecting Next (see Mark 5 below).

At this point, the Snowflake Connector should have enough permissions to inpsect your Snowflake objects available to the given role. The next screen lets you browse the tables available in Snowflake, and select the table you wish to load.

Start by selecting the database to which the table belongs (see Mark 1 below), then the schema (see Mark 2 below) and finally the table (see Mark 3 below). Finish by selecting Next (see Mark 4 below).

Note: the maximum allowed table size is 50 million rows. The result will be truncated if it exceeds that limit.

You can now configure the schedule on which you wish to load data. By default, the frequency is set to One-off which performs a one-time sync of the table. You can change this by selecting a different option from the dropdown menu (see Mark 1 below).

To configure a schedule that runs a regular sync, select the Interval option. You can configure a schedule in minutes, hours, or days by using the drop down selector, and set the value for the schedule in the text field. The screenshot below shows a schedule of 5 minutes.

Next, you can choose whether the sync should run immediately, or if it should wait until the first scheduled sync. You can also configure the Import Strategy. The strategy Replace data is selected by default. Finish by selecting Next (see Mark 1 below).

Note: the maximum allowed frequency is 5 minutes.

The final screen of the modal shows you the interpreted schema of the table, which you can change as needed. You can also modify what the Data Source in Tinybird will be called by changing the name at the top. To finish, click Create Data Source (see Mark 1 below).

Your Data Source has been created! The Data Source page displays a status chart showing executions of the loading schedule (see Mark 1 below). The Data Source takes a moment to create the resources required to perform the first sync.

When the first sync has completed, you'll see a green bar indicating the status (see Mark 1 below). Details about the data, such as storage size and number of rows, is shown above (see Mark 2 below). You can see a preview of the data below this (see Mark 3 below).

Load a Snowflake table in the CLI

You need to create a connection before you can load a table from Snowflake into Tinybird using the CLI. Creating a connection grants your Tinybird Workspace the appropriate permissions to view data from Snowflake.

Authenticate your CLI and switch to the desired Workspace. Then run:

tb connection create snowflake

The output of this command includes instructions to configure read-only access to your data in Snowflake.

Enter your user, password, account identifier, role, warehouse, and a name for the connection.

Lastly, copy the SQL block that is displayed.

** Creating a new Snowflake connection at the xxxx workspace.

User (must have create stage and create integration in Snowflake):
Password:
Account identifier:
Role (optional):
Warehouse (optional):
Connection name (optional, current xxxx):

Enter this SQL statement in Snowflake using your admin account to create the connection:

------
create storage integration if not exists "tinybird_integration_role"
    type = external_stage
    storage_provider = 'GCS'
    enabled = true
    comment = 'Tinybird Snowflake Connector Integration'
    storage_allowed_locations = ('gcs://tinybird-cdk-production-europe-west3/id');

grant create stage on all schemas in database <your_database> to role ACCOUNTADMIN;

grant ownership on integration "tinybird_integration_ACCOUNTADMIN" to role ACCOUNTADMIN;
------

Ready?  (y, N):

** Validating connection...
** xxxx.connection created successfully! Connection details saved into the .env file and referenced automatically in your connection file.

With the SQL query copied, open a new SQL Worksheet inside Snowflake. Paste the SQL into the Worksheet query editor (see Mark 1 below). Note that you must edit the query and replace the <your_database> fragment with the name of your Snowflake database. Finally, select the Run button (see Mark 2 below). This statement must be executed with a Snowflake ACCOUNTADMIN role, since Snowflake Integrations operate at Account level and usually need admin permissions.

Note: you must edit the query and replace the <your_database> fragment with the name of your Snowflake database.

You should see the response Statement executed successfully.

Once done, return to your terminal. Select yes (y) option and the connection will be created.

A new snowflake.connection file will be created in your project files.

Note: At the moment, the .connection file is not used and cannot be pushed to Tinybird. It is safe to delete this file. A future release will allow you to push this file to Tinybird to automate creation of connections, similar to Kafka connection.

Now that your connection is created, you can create a Data Source and configure the schedule to import data from Snowflake.

The Snowflake import is configured using the following options, which can be added at the end of your .datasource file:

  • IMPORT_SERVICE: Name of the import service to use, in this case, snowflake.
  • IMPORT_CONNECTION_NAME: The name given to the Snowflake connection inside Tinybird, e.g. 'my_connection'.
  • IMPORT_EXTERNAL_DATASOURCE: The fully qualified name of the source table in Snowflake, e.g. database.schema.table.
  • IMPORT_SCHEDULE: A cron expression with the frequency to run imports, must be higher than 5 minutes, e.g. */5 * * * *.
  • IMPORT_STRATEGY: The strategy to use when inserting data, either REPLACE or APPEND.
  • IMPORT_QUERY: (Optional) The SELECT query to extract your data from Snowflake when you don't need all the columns or want to make a transformation before ingestion. The FROM must reference a table using the full scope: database.schema.table.

Note: For IMPORT_STRATEGY only REPLACE is supported today. The APPEND strategy will be enabled in a future release.

For example:

snowflake.datasource file
DESCRIPTION >
    Snowflake demo data source

SCHEMA >
    `timestamp` DateTime `json:$.timestamp`,
    `id` Integer `json:$.id`,
    `orderid` LowCardinality(String) `json:$.orderid`,
    `status` LowCardinality(String) `json:$.status`,
    `amount` Integer `json:$.amount`

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp"
ENGINE_TTL "timestamp + toIntervalDay(60)"

IMPORT_SERVICE snowflake
IMPORT_CONNECTION_NAME my_snowflake_connection
IMPORT_EXTERNAL_DATASOURCE mydb.raw.events
IMPORT_SCHEDULE */5 * * * *
IMPORT_STRATEGY REPLACE
IMPORT_QUERY >
    select
    timestamp,
    id,
    orderid,
    status,
    amount
        from
        mydb.raw.events

The columns you select in the IMPORT_QUERY must match the columns defined in the Data Source schema. For example, if you Data Source has columns ColumnA, ColumnB then your IMPORT_QUERY must contain SELECT ColumnA, ColumnB FROM .... A mismatch of columns causes data to arrive in the quarantine Data Source.

With your connection created and Data Source defined, you can now push your project to Tinybird using:

tb push

The first run of the import begins on the next lapse of the CRON expression.

Schema evolution

The Snowflake Connector supports backwards compatible changes made in the source table. This means that, if you add a new column in Snowflake, the next sync job will automatically add it to the Tinybird Data Source.

Non-backwards compatible changes, such as dropping or renaming columns, are not supported and will cause the next sync to fail.

Limits

The import jobs are run in a pool, with capacity for up to 2 concurrent jobs. If more scheluded jobs overlap, they are queued.

Snowflake Connector usage limits

DescriptionLimit and time window
Maximum frequency for the scheduled jobs5 minutes
Maximum rows per append or replace50 million rows