How to store data in Snowflake with Tray.ai: A step-by-step guide
Disconnected data slows your business down
Sales, finance, and customer teams operate in silos. Marketing data is locked in one system, customer records in another, and finance is working off spreadsheets.
In just a few hours, you can sync everything with Snowflake and Tray.ai. Without writing complex code or committing to a long-term setup.
This guide walks through how to store data in Snowflake using Tray.ai, covering setup, connection details, and batch data ingestion to make your workflows seamless.
Snowflake is a powerful and elastically scalable cloud-based data platform. A data warehouse that enables efficient storage and querying of structured data. Tray.ai, on the other hand, is an advanced automation platform that simplifies integrations between various applications and services.
One of the hardest parts and the first step of working with integration platforms like Tray.ai is successfully connecting to a new system.
Once the connection is established, performing standard CRUD (Create, Read, Update, Delete) operations is relatively straightforward. In this guide, we will walk through setting up Snowflake, finding the correct parameters, and ensuring data is pushed successfully to Snowflake in batches, using Tray.ai.
Snowflake data pipeline with Tray.ai
A data warehouse like Snowflake can help unify your data, but investing in a full data pipeline can feel like a big step. What if you could test it with minimal effort and minimal risk?
I recently joined Snowflake Discover, got my hands on a free trial, and used Tray.ai’s low-code automation to connect my SEO data in minutes.
- Sync marketing, sales, finance, and customer data into Snowflake
- Automate reporting and dashboards without spreadsheets
- Start with one use case, expand as you go
This guide walks through exactly how to set it up step by step, so you can try it yourself.
Do You Want A Tray.ai Trial?
Step 1: Setup Snowflake for Tray.ai integration
To integrate Tray.ai with Snowflake, we first need to configure Snowflake to accept incoming data.
1.1 Create a user for Tray.ai in Snowflake
For security and access control, it is best to create a dedicated Snowflake user for Tray.ai, with limited scope. Run the necessary SQL commands to create the user, assign a role, and grant the required permissions.
CREATE USER TRAY_USER PASSWORD=' < your strong password goes here > ';
CREATE ROLE TRAY_ROLE;
GRANT ROLE TRAY_ROLE TO USER TRAY_USER;
1.2 Define the Snowflake database, schema, tables and warehouse
Snowflake requires a database, schema and tables for storing and processing data. If they don’t already exist, create them using SQL commands.
CREATE DATABASE SEO_DATA;
CREATE SCHEMA SEO_SCHEMA;
CREATE TABLE IF NOT EXISTS SEO_SCHEMA.KEYWORD_PERFORMANCE (
id VARCHAR(255) PRIMARY KEY,
search_term VARCHAR(255) NOT NULL,
country VARCHAR(10),
device_type VARCHAR(50),
clicks INT DEFAULT 0,
impressions INT DEFAULT 0,
ctr FLOAT DEFAULT 0,
position FLOAT DEFAULT 0,
date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
You’d also need a warehouse to query the information from the tables.
The following is an extra small warehouse that automatically suspends after 60 seconds of inactivity and automatically resumes, to save on costs.
CREATE OR REPLACE WAREHOUSE SEO_WAREHOUSE WITH
WAREHOUSE_TYPE = 'standard'
WAREHOUSE_SIZE = 'xsmall'
MIN_CLUSTER_COUNT = 1
MAX_CLUSTER_COUNT = 1
SCALING_POLICY = 'standard'
AUTO_SUSPEND = 60
AUTO_RESUME = true
INITIALLY_SUSPENDED = true;
Then, grant necessary permissions to the Tray.ai user’s role to allow access to these resources.
GRANT USAGE ON DATABASE SEO_DATA TO ROLE TRAY_ROLE;
GRANT USAGE ON SCHEMA SEO_DATA.SEO_SCHEMA TO ROLE TRAY_ROLE;
GRANT USAGE ON WAREHOUSE SEO_WAREHOUSE TO ROLE TRAY_ROLE;
GRANT OPERATE ON WAREHOUSE SEO_WAREHOUSE TO ROLE TRAY_ROLE;
GRANT INSERT ON ALL TABLES IN SCHEMA SEO_DATA.SEO_SCHEMA TO ROLE TRAY_ROLE;
If you need temporarily more extensive permissions for Tray’s role and user you can execute the following:
GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA SEO_DATA.SEO_SCHEMA TO ROLE TRAY_ROLE;
1.3 Retrieve Snowflake connection details
You will need to collect and format the following details to authenticate in Tray.ai:
- Account - from Snowflake’s URL, the first portion until the first “.”, in my case is something like “aaaaaa-aaa11111”
- Username - as defined on 1.1
- Password - as defined on 1.1
- Warehouse - as defined on 1.2
- Database - as defined on 1.2
- Schema - as defined on 1.2
- Role assigned to the user - as defined on 1.1
- Access URL - from Snowflake’s full url (including https:// and the final /)
Note that Snowflake by default interprets the resources you define in uppercase. Every setting aside from the Account and the Password and the Access URL should be uppercase.
With this setup, we are now ready to connect Snowflake with Tray.ai.
This Snowflake API integration with Tray.ai ensures seamless data flow, allowing the automation of data pipelines without manual intervention.
If you are wondering how to send data from Tray.io to Snowflake, this section walks through the entire process, from authentication to batch data transfer.
Once Snowflake is ready, we move to Tray.ai to set up the data ingestion process.
Explore Our Tray.ai Expertise
2.1 Authenticate Tray.ai with Snowflake
- In Tray.ai, add a Snowflake Connector to your workflow.
- Enter the connection details retrieved in Step 1.3, under the step’s authentication section.
Once connected, Tray.ai should be able to interact with Snowflake. My recommendation is to verify the connection by testing a simple operation such as “List databases” at this point, as there could be other permission issues for other operations.
Tray.ai Snowflake operation to insert rows
Tray.ai will retrieve data from an external source (e.g., API, database, spreadsheet). To ensure proper storage in Snowflake, we need to format the data correctly, to match the destination database schema.
An example of structured data Tray.ai might process includes fields like:
- Search term
- Country
- Device type
- Clicks, impressions, CTR, position
- Date
- Etc.
To store this in Snowflake, Tray.ai must map each field to a corresponding column in the database.
Tray.ai Snowflake payload format
Tray.ai Snowflake connector peculiarities for the operation “Insert rows in table”
- Tray’s connector requires an array of objects with properties “column” and “value”
- Tray’s connector requires every field value (under the “value” property) to be enclosed between single quotes.
My recommendation is to escape all pre-existing data single quotes or there will be failures if any value contains a single quote.
For example, if a value is: naonis we need to transform it to: ‘naonis’, and if a value is naonis’ (with the single quote at the end) we need to transform it to: ‘naonis'’ (with the escaped single quote).
Feel free to look at the example in the image below.
Ask For Tray.ai Help
2.3 Insert data into Snowflake in batches to increase throughput
To optimize performance, we push data in batches rather than one record at a time. This approach minimizes API calls and improves efficiency.
Tray’s operation “Insert rows in table” already supports records batching.
We can easily read N records from the source, say 100, and push a batch of 100 records into Snowflake.
Step 3: Validating and optimizing the data flow
Once the workflow is running, we need to validate the data and optimize performance.
3.1 Verify Snowflake data ingestion
Run a SQL query in Snowflake to check if the data has been stored correctly. If the records appear as expected, the integration is working properly.
3.2 Improve Snowflake pipeline setup by optimizing the batch size
To improve throughput, it is possible to increase the batch size retrieved from the source and pushed to Snowflake.
The challenge is to make sure that the batch of data (depending on the number of fields and data length) fits into what Snowflake with Tray.ai can handle. In my case, I could easily reach 10,000 records per batch.
Be aware that Tray.ai will replace long log outputs with the message “Data is too large to be displayed”, but not to worry, the data will still be transferred, as long as the step execution is successful.
Tray.ai performant batch insert with Snowflake
Benchmarks
The Tray.ai and Snowflake combination was reading data from Google Search Console API with 10,000 records per page, and pushing those 10,000 records to Snowflake. It took only 3 minutes and 23 seconds to process 130,000 records!
The Snowflake call to insert 10,000 records was completed in about 5 seconds consistently.
Way to go Google Search Console API, Snowflake and Tray.ai!
Potential improvements and recommendations
I am not a Snowflake expert (at least not yet!). I completed this setup within a few hours of getting a trial.
My recommendation is to make sure you complete your own due diligence regarding Snowflake’s best practices and the most secure setup or leverage external expertise.
This is a working example to connect Tray with Snowflake.
An additional possible improvement is to leverage parallel writes (fire and forget instead of fire and wait for response) to Snowflake so that the workflow does not have to wait 5 seconds for the batch write to complete. The downside of this approach is that a page failure is no longer easy to handle.
Leveraging no-code data automation with Tray.ai reduces setup complexity, making Snowflake integration accessible even for teams without deep technical expertise.
Need Tray.ai Help?
Conclusions of the Tray.ai Snowflake data pipeline tutorial
Integrating Snowflake with Tray.ai allows performant and seamless data ingestion. By following this guide, you can:
- Successfully set up Snowflake for external data input.
- Configure Tray.ai to process and transform data.
- Optimize batch processing to improve efficiency.
- Overcome some of the technicalities and hurdles that you might otherwise have run into.
Once the connection is established, CRUD operations like inserting, updating, deleting and querying data become straightforward. As you scale, consider bulk loading strategies and performance tuning to maximize efficiency.
📍 Specialized in SaaS Companies in California leveraging Tray.ai
Are you a SaaS business looking to for additional help to optimize integrations, automate workflows, and enhance customer experience? Our Tray.ai fractional consulting and development expert services for California-based tech companies provide tailored automation and integration solutions.
Learn more →
