Contents x
    Snowflake
    • 4 Minutes to read
    • Dark
      Light
    Contents

    Snowflake

    • 4 Minutes to read
    • Dark
      Light
    Article Summary

    Snowflake is a cloud-based data warehousing platform that provides a fully managed, scalable, and high-performance solution for storing and analyzing data. It separates compute from storage, allowing users to scale resources independently and enabling organizations to efficiently manage and query their data for analytics and business insights.

    Prerequisites

    • You have create a Snowflake user with USAGE, SELECT, INSERT, UPDATE, CREATE TABLE, and DELETE permissions.

    Authorize Connection to Snowflake

    In Snowflake

    To insert the data to Snowflake, the user account provided to Dataddo will need the following permissions: USAGE, SELECT, INSERT, UPDATE, CREATE TABLE, and DELETE.

    The easiest way is to create a new user account specifically for Dataddo.

    CREATE ROLE "DATADDO_ROLE";

    As Dataddo automatically creates the tables in the schema when a new flow is provisioned, make sure that the CREATE TABLE permission is assigned to the newly created role.

    GRANT CREATE TABLE ON SCHEMA "YOUR_SCHEMA" TO ROLE "DATADDO_ROLE";

    Please also use FUTURE GRANT to ensure that any table created by Dataddo will automatically have the right permissions.

    GRANT SELECT, INSERT, UPDATE, DELETE ON FUTURE TABLES IN SCHEMA "YOUR_SCHEMA" TO ROLE "DATADDO_ROLE";

    In Dataddo

    1. In the Authorizers tab, click on Authorize New Service and select Snowflake.
    2. You will be asked to fill the following fields:
      1. Account identifier: The account identifier assigned to your account by Snowflake. Example value is SW123456.eu-west-1 or SW12345.us-central1.gcp or SW123456.ap-south-1.aws. You can find more in Snowflake documentation.
      2. Database: Name of your database. Please write in uppercase.
      3. Username: Username for authentication.
      4. Password: Password for authentication.
      5. Schema: The schema belonging to the chosen database. The default value is PUBLIC. Make sure to use uppercase.
      6. Warehouse: Specifies the default virtual warehouse for queries. If unspecified, the default data warehouse will be used.
      7. Region: DEPRECATED. 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. For details, see the description of the account parameter.
      8. Role: Can be left empty, unless you want to specify the default role to use for sessions initiated by the connection from Dataddo.
    3. Click on Save.
    Case Sensitivity

    Dataddo is using case-sensitive mode when querying the Snowflake. As such, since Snowflake stores all the indetifiers (e.g. table names, schemas) by default in uppercase, please use uppercase for any identifier to avoid errors.

    Create a New Snowflake Destination

    1. On the Destinations page, click on the Create Destination button and select the destination from the list.
    2. Select your authorizer from the drop-down menu.
    3. Name your destination and click on Save.
    Need to authorize another connection?

    Click on Add new Account in drop-down menu during authorizer selection and follow the on-screen prompts. You can also go to the Authorizers tab and click on Add New Service.

    Create a Flow to Snowflake

    1. Navigate to Flows and click on Create Flow.
    2. Click on Connect Your Data to add your source(s).
    3. Click on Connect Your Data Destination to add the destination.
    4. Choose the write mode and fill in the other required information.
    5. Check the Data Preview to see if your configuration is correct.
    6. Name your flow and click on Create Flow to finish the setup.

    Table Naming Convention

    When naming your table, please make sure the table name:

    • Is in all upper case letters
    • Starts with a letter or an underscore
    • Contains only
      • Letters
      • Numbers
      • Underscores
      • Dollar signs

    Troubleshooting

    Case Sensitive Error

    ERROR CODE

    Failed to prepare table: reading current structure: verifying table name: table named "..." doesn't match "...", you use case-insensitive format most probably.

    This issue is caused by a table in the flow configuration containing lower-case letters. To solve the issue, please follow these steps:

    1. In the Flows tab, click on your flow.
    2. Rewrite your table name in all uppercase.
    3. Go to your Snowflake account and delete the table.
    4. Restart the flow.

    No Active Warehouse Error

    ERROR CODE

    Failed to prepare table: reading current structure: verifying table name: ...: No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

    This error is caused by your authorizer not specifying the warehouse. To solve the issue, please follow these steps:

    1. Reauthorize your Snowflake account with the correct warehouse name.
    2. Restart the flow.

    Current Role has no Privileges Error

    ERROR CODE

    Table '...' already exists, but current role has no privileges on it. If this is unexpected and you cannot resolve this problem, contact your system administrator.

    This error is caused by the fact that newly (and automatically) created table has no privileges. This is due to the fact that no FUTURE GRANT has been made.

    1. Make sure to set correct permissions. Pay special attention to the FUTURE GRANT part.
    2. Delete the newly created table in the Snowflake.
    3. Restart the flow.

    Was this article helpful?
    What's Next