Analytics data big business intelligence background bi
Image: Stock

Having a deep insight into customer behavioral data and product experience is a strategic way companies edge out their competitors. Product and marketing teams can use customer data to gain insight into consumer behavior for improved business decision-making.

To automate the aggregation of behavioral data, developers came up with business intelligence and data warehousing tools like Amplitude and Snowflake to help businesses access actionable customer insights.

SEE: Hiring Kit: Database engineer (TechRepublic Premium)

However, with the recent partnership and integration between Amplitude and Snowflake, many users have been caught up with how to integrate both services for improved data insights. Here is how.

What is Snowflake?

Snowflake is a SaaS tool designed to support business enterprises with data aggregation services such as data warehousing, data science, data lakes, data application development and data sharing.

The Snowflake architecture runs on the cloud provisions of Amazon Web Services, Microsoft Azure and Google cloud platforms for large data storage. In addition, this data warehousing tool is the ideal option for businesses that do not want to allocate resources to the setup, management and support of internal servers because it does not require any hardware to be installed or configured.

What is Amplitude Analytics?

Amplitude Analytics is a powerful analytics solution with cross-platform monitoring, real-time analytics, strong behavioral analytics, enterprise-level security measures and dependable customer support designed to meet the needs of contemporary product and growth teams.

Many business organizations and innovative teams use Amplitude to analyze and customize their digital goods and increase the commercial value of their product innovation.

Benefits of using Amplitude Analytics with Snowflake

The main purpose of using Snowflake data in Amplitude is to unlock valuable insights into consumer behavior and product experiences easily. With Snowflake housing all the necessary data needed to gain enough insight into your customers, connecting the Snowflake tool directly to Amplitude eliminates unnecessary bottlenecks where you’d have to upload your data to Amplitude for data analytics manually.

With this connection, Amplitude and Snowflake clients can rapidly acquire, process and extract value from their product data using the Snowflake Data Share interface to understand their customers better.

How to connect Snowflake to Amplitude

Method one: Adding Snowflake to Amplitude as a data source

The first step you should take when integrating Snowflake into Amplitude is to add Snowflake as a data source in your Amplitude project.

To do this, go to your Amplitude dashboard and select the project from the project list tab.

Once this is done, select I Want to Import Data into Amplitude. This action lists Snowflake as one of the sources from which you want to import your data.

Figure A

Image: Amplitude

The next step is to key in the credentials for the Snowflake instance you wish to connect. The credentials that’ll pop up include:

  • Account Name: This is the account name of your Snowflake account. This is case sensitive.
  • Database: Choose a database name in which Amplitude may locate the data.
  • Warehouse: the warehouse is used to conduct SQL queries.
  • Username: Your Amplitude’s username is needed for authentication.
  • Password: Your password will be used to authenticate users.

Figure B

Image: Amplitude

The next step is to provide Amplitude with the necessary rights, which can be done by copying the auto-generated SQL code as shown in the figure above and executing it in Snowflake.

Once the SQL code has been executed, click on the Next button at the top right corner of your screen to test the connection. After the test is complete and is successful, click the Next button to navigate to the data selection page.

Figure C

Image: Amplitude

The next stage is to choose your configuring options. The configuration options will include:

  • Type of Data: This lets Amplitude know whether you’re using group property data, user property data or event data.
  • Import Type: You can select one of the options below under this configuration.
  • Full Sync: If you choose this option, Amplitude periodically imports the full dataset whether it has already been imported or not. This is helpful for data sets when the row data is changing, but there is no easy way to tell which rows have changed.
  • Time-based: The Timestamp column in this option indicates that Amplitude frequently ingests the most current rows of data. Any data with timestamps after the most recent import is included in subsequent imports, and the initial import contains any data that is accessible. For this to work, you must include the timestamp of the data’s entry into Snowflake.
  • Frequency: Under this option, you can schedule choices ranging from five minutes to 4 weeks.

After entering your configuration option, the last phase under this method is to test the Snowflake to Amplitude connection.

To test your connection, click on Test SQL as shown in the figure below to examine how the data flows.

Figure D

Image: Amplitude

If there is no error message, click Finish. Once this is done, you’d get a notification that your Snowflake source has been activated successfully.

Method two: Setting up a recurring data export to Snowflake

Here is another method you can use for the Amplitude Snowflake Integration process.

Follow the steps below to set up a recurring export of your Amplitude data to Snowflake. Users are expected to sign up for a premium account on Amplitude for this process to work.

On the Amplitude dashboard, navigate to the Data Destination tab. Select Snowflake under the Add More Destination. This tab will take you to Export Data to the Snowflake page.

On the Export Data to Snowflake tab, choose the data you wish to export to Snowflake.

Once this is done, carefully review the Event Table and Merge IDs table schemas and click Next. This will take you to the Setup Export tab.

To set up the export, you will need to enter some credentials, which include the account name on Snowflake, Warehouse, Database, Username and Password. These credentials are case-sensitive and must be entered carefully.

Once completed, Amplitude will create a query that will generate Snowflake objects at the right corner of the Snowflake credentials section. Copy this query and paste it to your Snowflake account

Click Next to allow Amplitude to upload the data from the Snowflake credentials you entered earlier. Once this is successful, click on Finish. With this, your Snowflakes and Amplitude are automatically connected to handle future analytics events.

Method three: Snowflake Data Share

There is also a third and newer method: Snowflake Data Share. With this feature, customers can now share chosen database items from one or more databases with other Snowflake accounts and vice-versa. Sharing data does not copy or transfer data between accounts – all sharing is accomplished through Snowflake’s unique services layer and metadata store.

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays

Subscribe to the Developer Insider Newsletter

From the hottest programming languages to commentary on the Linux OS, get the developer and open source news and tips you need to know. Delivered Tuesdays and Thursdays