Connecting your Harvest data to Snowflake can help you automate reporting and unlock new business insights. As much as the internal reporting of Harvest provides you with a view into your operational data, it’s simply not enough when you want to conduct deeper analysis. This is where moving data from Harvest to Snowflake, a cloud data warehouse, is needed.
This article breaks down the options and steps to integrate Harvest and Snowflake.
Bring all your data together. Integrate data from anywhere
Why sync data from Harvest to Snowflake?
- Automate and scale manual and time-consuming Harvest reporting processes currently conducted in spreadsheets.
- Provide Harvest performance metrics visibility to a wider team of users in a centralized view, using your preferred reporting tool.
- Collect and store historical results of your Harvest data in Snowflake for period-over-period trend analysis and data snapshots backup.
- Perform analytical queries and conduct advanced analytics to extract valuable insights beyond Harvest native reporting abilities.
- Replicating data from Harvest to Snowflake along with other data sources provides cross-system analysis for complete insights and optimization.
How to migrate data from Harvest to Snowflake?
There are three main options to replicate data from Harvest to Snowflake:
- Using Rivery’s no-code data pipeline
- Coding against Harvest API
- Extracting Harvest data to CSV Files
ELT Harvest to Snowflake using Rivery
If you’re looking for a solution that is reliable and quick to set up to your exact needs, using an ELT tool like Rivery is a recommended choice. Rivery is an easy-to-use, modern data integration platform. With Rivery, you can build your Harvest to Snowflake data pipeline in minutes and let the platform manage it for you so you don’t need to worry about learning how to connect, maintain your solution and infrastructure, or set up additional processes to monitor your data pipeline when running at scale.
Here are the steps to create your Harvest to Snowflake data pipeline using Rivery:
Step 1: To configure your Harvest connection in Rivery, create a free Rivery account and log in.
Step 2: In Rivery, click on the Create Source to Target River button (River is the Rivery term for data pipeline).
Step 3: Search for Harvest in the search box and click on it.
Step 4: Provide a Connection Name and authenticate to Harvest using the options listed on the right-hand side of the connection page. Click on Test Connection and once successful, save it.
Note: If you don’t have the right account to authenticate to Harvest, you can also click on Share External Connection Link and send the link to a person who does have the right Harvest account (even if that person doesn’t have a Rivery user). That person can then create the connection for you and you can proceed with the steps below.
Step 5: Choose to replicate data from a predefined report or a custom report where you control the specific Harvest data fields you would like to replicate to Snowflake. For detailed information on Harvest reports, refer to the Rivery Harvest documentation.
Step 6: Select the time period for replicating your Harvest data. You can choose a specific date range or pick from preset options (e.g., Yesterday, Last week, etc.). If you opt for a date range, it’s recommended to leave the End date blank. This allows Rivery to automatically adjust the Start date for future runs, pulling data incrementally from where the last successful run left off (i.e., creating a dynamic incremental extract of your Harvest data), ensuring a seamless data extraction process.
Step 7: Once your Harvest source is configured, move to configure your Snowflake target. If you don’t have a user with the right Snowflake permissions, create such a user by following the instructions on the right-hand side of the connection page. Using the Snowflake user, authenticate to Snowflake using one of two methods:
Enter the name of your Snowflake Warehouse (mandatory), Role (optional), and Account Name.
Before you test and save your connection, choose if you want to enable a custom file zone to stage your data within your own cloud data lake before loading it to Snowflake (optional).
Step 8: Define your data loading mode. Choose between Upsert-Merge for incremental loads that will update and insert new records, Append to only insert new records, and Overwrite to replace the entire dataset within your Snowflake target tables. For more details on the different loading mode options, please refer to Rivery’s Snowflake documentation.
Step 9: With your Snowflake target connection configured, you can move to the Schema configuration to control your data replication at the table level. Here you can select and define how to map the data between Harvest and Snowflake. You are free to leave the defaults detected by Rivery or change them to your specific needs to avoid additional downstream data preparation. For example, you can change column names, and data types, define table keys, create a Calculated Column using a Snowflake SQL expression, enforce Snowflake’s masking policy, and more.
Note: there is no need to create tables or columns in advance in Snowflake. Rivery will automatically create those for you.
Step 10: Now, the last step is to configure the data pipeline general settings. You can set the run schedule and select your desired notifications. Click on Run Now and Rivery will start replicating your Harvest data to Snowflake tables. You can track your data pipeline run under the Rivery activities view. Upon completion, you will see your Harvest data in the Snowflake tables.
Why is Rivery a recommended choice?
Rivery is a recommended choice for Harvest data replication for several reasons:
- Fastest Time to Value: Rivery’s wide range of prebuilt no-code integrations and low-code connections for niche sources enables the delivery of your data pipeline in hours instead of spending weeks or even months on extensive coding.
- Zero Maintenance: Rivery’s managed integrations ensure that you don’t need to spend time tuning your infrastructure, tracking source API changes, and updating your code.
- Full Workflow Control: Rivery’s pipelines customizability, orchestration abilities, and templated starter kits solutions provide the necessary to deliver data as part of your entire data workflows.
- Automated DataOps: Rivery’s notifications, activities management, environments, deployments, and variables enable deploying your pipelines across your dev and production environments without manual intervention.
- Best Practice Security: Benefit from certified and secured ways to extract and load your data without having to become a security expert.
Move data from Harvest to Snowflake by coding against the API
With the Harvest API, you can programmatically interact with your data. It is an HTTP-based API that can be used to perform a range of tasks including getting your data.
Extracting Harvest data via the API, requires:
- Learning the API to identify which API calls are necessary to get your desired data.
- Setting up the infrastructure to run your code (i.e. Python) on.
- Code your data pipeline to extract the data and load it into Snowflake. This will require handling the creation of the target tables and their columns data types and Snowflake incremental data loads.
- Test the solution and debug it.
- Once stable, schedule it and set up monitoring processes so you can be alerted in case of failure.
- Budget for additional maintenance time each year as the Harvest API is likely to change over time.
Extracting data from Harvest and loading it to Snowflake using CSV files
Manually copying Harvest data to Snowflake involves a series of detailed steps:
- Export your Harvest to a CSV file(s).
- Prep and clean your CSV file(s) so it is ready to be loaded into Snowflake.
- Use a Snowflake PUT command to load the file into a Snowflake staging area.
- Use a Snowflake COPY INTO command to load the staged data into a Snowflake Table.
- Repeat the process every time you need fresh data in Snowflake.
Transform and model your Harvest data
Congratulations, now that you have your Harvest data in Snowflake, you can further transform and model your Harvest data so it’s ready for analytics and other applications. The most common way to model your data in Snowflake is by pushing down SQL queries into Snowflake. This can be done with Rivery’s logic rivers for transformation and orchestration, with tools like dbt, or directly in Snowflake using Dynamic Tables.
In Summary
You have learned three different options to integrate Harvest data with Snowflake. The first option leverages the efficiency of Rivery, offering an easy-to-use and scalable solution. The second option involves using Harvest API, allowing you to achieve solutions with programming skills. Lastly, the CSV-based option provides a straightforward option for those seeking a one-off data transfer. Some options require more technical expertise or effort to align with business requirements. However, you should choose the one that aligns well with your needs to unlock the full potential of your Harvest data.
Rivery is a preferred solution for replicating Harvest data to Snowflake. This will not only enhance your data integration process but also save you precious time. Try Rivery today and make your data flow.
Build your integration now
