Snowflake Connector¶

The Snowflake Connector allows you to load data from your existing Snowflake account into Tinybird so that you can quickly turn them into high-concurrency, low-latency REST APIs.

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.

The Snowflake Connector is:

• Easy to use. Start ingesting instantly with our first-party Snowflake Connector. Just point, click, and capture the Snowflake data you need.
• SQL-based. Use SQL to ingest the data you need from Snowflake. Then use SQL to query, shape, and join your Snowflake data with other sources.
• Secure. Use Auth tokens to control access to API endpoints. Implement access policies as you need. Support for row-level security.

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.

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 inspect 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).

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).

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.

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.

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 (UTC) 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.

For example:

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.

Iterating a Snowflake Data Source¶

Prerequisites¶

We recommend the use of the CLI and the version control integration to handle your resources.

To use the advantages of version control, connect your Tinybird workspace with your repository, and set the CI/CD configuration.

Check the use case examples repository where you can find basic instructions and examples to handle Snowflake Data Sources iteration using git integration, under the iterate_snowflake section.

To use the Tinybird CLI tool check its documentation.

For instance to create the connections in the main branch/workspace using the CLI:

tb auth # use the main Workspace admin Token
tb connection create snowflake
# these prompts are interactive and will ask you to insert the necessary information

For testing purposes it's recommended to use different connections from main/branches workspaces.

Add a new Snowflake Data Source¶

You can add a new Data Source directly with the UI or the CLI tool, following the load of a Snowflake table section.

To add a new Data Source using the recommended version control workflow check the instructions in the examples repository.

Update a Data Source¶

Snowflake Data Sources can be modified from the CLI tool:

tb auth
# modify the .datasource Datafile with your editor
tb push --force {datafile}
# check the command output for errors 

To update it using the recommended version control workflow check the instructions in the examples repository.

Delete a Data Source¶

Snowflake Data Sources can be deleted directly from UI or CLI like any other Data Source.

To delete it using the recommended version control workflow check the instructions in the examples repository.

Limits¶

Logs¶

Job executions are logged in the datasources_ops_log Service Data Source. This log can be checked directly in the Data Source view page in the UI. Filter by datasource_id to monitor ingestion through the Snowflake Connector from the datasources_ops_log:

SELECT
  timestamp,
  event_type,
  result,
  error,
  job_id
FROM
  tinybird.datasources_ops_log
WHERE
  datasource_id = 't_1234'
AND
  event_type = 'replace'
ORDER BY timestamp DESC