Asana to Snowflake: 2 Easy Methods

Does your team use Asana to manage its projects? Are you overwhelmed with keeping track of the data that is generated on Asana by your company? If this applies to you then it might make sense to transfer your data from Asana to a data warehouse like Snowflake. This blog will discuss two methods through which you can achieve data transfer from Asana to Snowflake. Thus, enabling you to evaluate and select the one that best suits your needs.

This blog discusses both these approaches, allowing you to evaluate the pros and cons of both methods effectively.

Introduction to Asana

Asana is a Software-as-a-Service tool that enables you to manage, track and organize workflows throughout your organization. Asana has widespread applications in Project Management due to its ability to simplify the management of team-based work. Asana is offered as a freemium service – it is free for up to 15 users, after which you are required to pay a fee in addition to any fees you might pay for additional premium services. 

Key Features of Asana

• Easy Integrations: Asana offers integrations to many widely-used tools like Slack, Gmail, Adobe Creative Cloud, etc. Thus, further simplifying your work management efforts
• Visual Project Mapping: Asana enables you to plan and organize your projects through a visual layout. This makes it easier for you to see how everything develops over time
• Custom Fields: Asana’s custom fields feature enables you to easily track the information that is most important to your organization
• Milestones: This feature enables you to clarify your goals and keep your team motivated. 

Introduction to Snowflake

Snowflake is a fully managed Cloud-based data warehouse that operates under the Software-as-a-Service model. Snowflake users do not have to get involved in any maintenance activities after it is set up as these activities are handled by Snowflake on the backend. Snowflake has become a popular tool on the market owing to its speed, ease of use, and scalability. Snowflake also has separate compute and storage layers. This enables both layers to be scaled and priced independently of each other. 

Key Features of Snowflake

• Low Maintenance: Snowflake requires a low maintenance commitment from your internal team since much of the infrastructure management is handled on the backend. Furthermore, it requires no software or hardware installations.
• Speed: Already running queries in Snowflake are not affected by scaling and thus do not affect running speeds. 
• Cloning Functionality: Snowflake allows for the creation of real-time instant copies of objects which store pointers to your original data. These copies are not deep copies and enable you to save a lot of space as a result. 
• Separate Storage and Compute Layer: Snowflake has separate storage and compute layers. This makes it possible for you to scale one up or down independently of the other. You also only pay for what you use as payment is also separate for each layer. 

Methods to Load Data from Asana to Snowflake

• Method 1: Building a Custom Code to Load Data from Asana to Snowflake
• Method 2: Loading Data from Asana to Snowflake using Hevo

Method 1: Building Custom Code to Load Data

Loading data from Asana to Snowflake by building custom code would broadly involve understanding the following:

1. Prerequisites
2. Step 1: Extracting Data from Asana using REST APIs
3. Step 2: Preparing the Data for Data Load
4. Step 3: Loading Data into Snowflake Data Warehouse

Prerequisites

The following prerequisites must be addressed before proceeding to write ETL scripts in this method:

1. Asana Account
2. HTTP client (to interact with the Asana REST API)
3. Authorization (to enable you to access Asana resources)
4. Snowflake Data Warehouse
5. Business Rules (For data transformation)

Step 1: Extracting Data from Asana using REST APIs

The first step in Asana to Snowflake data transfer is to extract your data from Asana’s rich API, which uses the RESTful architecture. The API provides access to data on resources like tasks, projects etc. You can obtain the data by making calls to the API directly or through tools like Postman or curl.

Here’s an example for your reference:

GET /projects/{project_gid}

{
  "data": {
    "notes": "Document all the things",
    "null": "...",
    "id": 1331,
    "gid": "1331",
    "resource_type": "project",
    "name": "Things to Document"
  }
}

More information on the Asana API can be found here: https://asana.com/guide/help/api/api

Step 2: Preparing the Data for Load

It may be useful to map out a schema for your tables in Snowflake to receive the Asana data. Special care must be taken to create additional tables to accommodate some of the JSON data if it is nested. You also have to ensure that the data types that are in your Asana data match their corresponding types in Snowflake. Snowflake provides support for many widely used data types. Documentation on Snowflake data types can be found here: http://docs.snowflake.com/en/sql-reference/data-types.html

Step 3: Loading Data into Snowflake Data Warehouse

Your data can be loaded into Asana to Snowflake with the help of SnowSQL (Snowflake’s Command Line Interface). Specifically, we can use the COPY INTO SQL statement to achieve this. Your files can be loaded either from Snowflake’s stages or any of Snowflake’s external storage locations. Now, we will dive deep into each of these approaches.

1. Loading the data from Snowflake Stages:

We can ingest the data into a Snowflake stage with the PUT command, after which we will use the COPY INTO command to load it into Snowflake. The following are the commands to load the data from their respective stage types:

Table Stage Type:

COPY INTO TABLE1 file_format=(Type=’JSON’   Strip_outer_array=”TRUE”)

User Stage Type:

COPY INTO TABLE1 FROM @~/staged file_format=(format_name=’json_format’)

Created Internal Stage:

COPY INTO TABLE1 FROM @Stage_Name

2. Loading from External Storage Locations:

You can create an external stage for the location in question and load from it or load directly from the location. The following is the code to load data from the GCS, Amazon S3 and Microsoft Azure storage locations respectively:

GCS:

COPY INTO TABLE1 FROM 'gcs://bucket’
STORAGE_INTEGRATION=(Integration_name)
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

Amazon S3:

COPY INTO TABLE1 FROM s3://bucket
CREDENTIALS= (AWS_KEY_ID='YOUR AWS ACCESS KEY' AWS_SECRET_KEY='YOUR AWS SECRET ACCESS KEY')
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

Microsoft Azure:

COPY INTO TABLE1 FROM azure://your account.blob.core.windows.net/container
STORAGE_INTEGRATION=(Integration_name)
ENCRYPTION= (MASTER_KEY = 'YOUR MASTER KEY')
FILE_FORMAT = (FORMAT_NAME = JSON_FORMAT)

Challenges Involved in Building Custom Code to Load Data from Asana to Snowflake

• Hard to perform Data Transformations: You cannot perform fast transformations like currency conversions, time standardization, etc. under this method. 
• Difficulties with Real-time Data: The manual method transfers a batch of data at a point in time. Thus, you will have to write additional code and configure cron jobs to even have basic real-time functionality.
• Constant Maintenance: You always have to monitor your Asana API as this method will return inaccurate data if there are any issues with the API.
• Time-consuming: You have to write a lot of code with this method. This can be particularly problematic in situations where tight deadlines are being enforced.

Do you want a more stable data transfer process without the drawbacks of the manual method? Try our No-code Data Pipeline, Hevo.

Method 2: Loading Data from Asana to Snowflake using Hevo

Hevo is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss.

It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. It allows you to focus on key business needs and perform insightful analysis using various BI tools such as Power BI, Tableau, etc. 

Check Out What Makes Hevo Amazing:

• Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.
• Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
• Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
• Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
• Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
• Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.

Conclusion

In this blog, you have learned how to connect Asana to Snowflake manually. You also came across the various limitations of connecting Asana to Snowflake manually. So, if you are looking for a fully-automated data pipeline, then try Hevo.

Visit our Website to Explore Hevo

Hevo is a No-code Data Pipeline and has awesome 100+ pre-built integrations that you can choose from. Hevo can help you integrate your data from numerous sources and load them into a destination to analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure. Check out the pricing details here. Try Hevo by signing up for a 14-day free trial and see the difference! Experience the power of Hevo first hand. Watch this short overview video to get a sense of how Hevo works:

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

We are eager to know what you think of this article on Asana to Snowflake! Leave your thoughts in the comments section below!