Skip to main content

Snowflake is a cloud-based data warehouse storage technology with automatic usage-based scaling of compute and storage resources. Additionally, Snowflake offers multi-cloud compatibility, allowing for greater flexibility in data management across different platforms.

Snowflake Storage is available as part of the Standard, Premium or Enterprise Package

The following functionality is currently supported when using Snowflake storage when using the TimeXtender Data Integration 6675.1 release:

  • Ingest instance using Azure Data Lake Storage with SAS authentication
  • Simple Mode tables
  • Incremental Load
  • Field Transformations
  • Field Validations
  • Conditional Look-ups
  • History tables
  • Supernatural Keys
  • Custom Views
  • Aggregate tables
  • Table inserts
  • Custom table inserts

Instructions

Create a Snowflake Database

  1. Log into the snowflake portal app.snowflake.com, click your name> Switch Role > select the SYSADMIN role
  2. Click Data > Databases
  3. Click + Database
  4. Give your Database a name and click Create

Get Snowflake Instance Details

Log into the snowflake portal app.snowflake.com

Find the Snowflake Account Identifier

  1. The Snowflake Account Identifier should look like this: <OrganzationID>-<AccountID>
  2. To find the Account Identifier in the snowflake portal click 
    1. Admin >
    2. Accounts >
    3. Next to your account ID, click the link
    4. Copy the subdomain of the URL, the portion between “https://” and the first “.”

Find the Snowflake Warehouse Name

  1. Admin > Warehouses
  2. Copy the name of the Warehouse you wish to use

Configure Storage for Prepare Instance

In TimeXtender Portal > Data estate > InstancesAdd prepare instance

 

  1. In the Server Storage Type, Select Snowflake
  2. Enter your Account Identifier
  3. Enter your Warehouse Name
  4. Enter your Database Name
  5. Enter your Port (Default is 443)
  6. Enter Username: Created when you signed up for Snowflake
  7. Enter Password: Created when you signed up for Snowflake
  8. Connection Timeout: number of seconds to wait before terminating the attempt to connect to the server. Set it to 0 to wait indefinitely
  9. Command Timeout: number of seconds to wait before terminating a command. Set it to 0 to wait indefinitely

 Configure SAS token for Ingest ADLS storage

  1. In the Azure Portal portal.azure.com> go to your ADLS resource, go to Shared access signature
  2. Check mark Service, Container and Object boxes
  3. Specify an expiry date
  4. Generate & Copy the SAS token 
  1. Go to TimeXtender portal > Data estate > instances > Edit Ingest instance > Azure Data Lake Storage
  2. Enter the full SAS token in the Azure SAS Token box

Connecting to Tabular, Tableau and Power BI Endpoints from a Snowflake Prepare instance

  1. Download and install the Snowflake ODBC driver https://developers.snowflake.com/odbc/
  2. Open the ODBC Data Source Administrator Application and go to the System DSN tab
  3. Press Add and select SnowflakeDSIIDriver. Click Finish.
  4. In the Snowflake Configuration Dialog, provide a name for the data source, and enter the user and password that were created when you signed up for Snowflake. Specify the database, schema, warehouse and set Tracing to 0. Click Test to ensure the connection to the data source is successful. Press OK.
  5. Select the Prepare instance with Snowflake storage in the TimeXtender Portal in order to edit its details.
  6. Type the Data Source name, that was entered into the Snowflake Configuration Dialog, into the DSN field. Press Save to confirm.
  7. If using the Power BI endpoint, the ODBC Data source credentials must also be updated. After deploying the Power BI endpoint within TimeXtender Data Integration. Navigate to the dataset in app.powerbi.com, and click on the three dots, and select Settings.
  8. Expand the gateway connection menu and press the action button.
  9. Click Add to gateway on the ODBC data source.
  10. Provide a name for the gateway and the data source. Select basic authentication method and enter the Snowflake username and password. Press Create.
  11. Expand the Gateway connection again and map the ODBC connection. Click Apply.

     

Troubleshooting

Unable to Connect

Error: Snowflake Internal Error: Unable to connect

Cause: The account name is incorrect.

Solution: Edit your Prepare instance in TimeXtender Portal to review and correct the account name, as described above.

Failed to authenticate request

Error: Server failed to authenticate the request

 

Cause: Snowflake is unable to access the Ingest instance storage

Solution: Verify you using Azure Data Lake Ingest Storage and the SAS token is configured with access to the folder in Azure Data Lake that you're pulling the data from.

Execution Fails on Transfer task in Ingest instance

Error: SQL compilation error:
syntax error line 1 at position 7 unexpected 'R'.
syntax error line 3 at position 18 unexpected '('.

Cause: This error occurs if Incremental Load is used. 

Solution: Set Table Settings to Full Load or Disable Incremental Rules on Data source in your Ingest instance and Synchronize your Ingest instance against your Prepare instance. 

Locked User

Error: ‘User temporarily locked’

Cause:  This error may occur if the user is testing the connection 5 or more times with an incorrect password

https://community.snowflake.com/s/article/FAQ-User-and-Password-Management 

Be the first to reply!

Reply