Data Integration Tools and Techniques: How to Find the Right Solution

The landscape of data integration is going through an overhaul right now; it is a long overdue shift towards the flexibility and scalability that cloud-based architecture and tools have brought us. But with this shift comes confusion and fatigue. What is the best tool for my use case? How should I ingest my data? Where should I store my data? Which cloud provider is best for what I want to do? Should I even ingest my data into a cloud data warehouse?

Let’s start at the bottom and work our way up.

Should you ingest your data into a cloud data warehouse?

There are countless reasons to remain on-premise for your transactional applications – stability, sunk infrastructure cost, familiarity, etc. But there are significantly fewer reasons to remain on-premise for your analytics stack. The cloud brings advantages to your analytics stack that are difficult, or even impossible, to perform in an on-premise environment, including the ability to scale compute or storage at the click of a button, ingest new data sources in minutes, and take advantage of SaaS offerings. It’s never been easier to create a unified view into all of your data.

So should you ingest your data into a cloud data warehouse? Our opinion is a resounding YES but ask yourself these questions.

• Do you ever worry about hitting storage limits in your current analytics stack?
• Do your queries take way too long? Do they frequently strain the limits of the compute that you can throw at them?
• Do you have multiple disparate data sources that are hard to wrangle together into a single cohesive analytics platform?
• Wouldn’t it be nice to not have to worry about ingestion?

If you answered yes to any of the questions above, it might be time to move to a modern data stack.

Which cloud provider is best for what I want to do?

This depends on your business needs, current environment, and overall organizational goals. But, each cloud provider has certain high level benefits that we’ve identified below.

• Amazon Web Services (AWS): The market leader, AWS has the largest number of services across storage, compute, database, analytics, IoT, security, etc. Their biggest advantage is their ability to do almost everything.
• Microsoft Azure: Azure is popular with those already in the Microsoft ecosystem thanks to its seamless integration of Office 365 and Teams. While it doesn’t have the same breadth of infrastructure services, its SaaS offerings, such as Azure Data Factory, are emerging as key strengths to their enterprise focused model.
• Google Cloud Platform (GCP): GCP is a fierce challenger to Azure and AWS. They excel at all things open-source and have some of the best functionality around containers. Their cloud DWH, Big Query, is also leading the way when it comes to serverless cloud data warehouses, only beaten by Snowflake.

Want more information on a specific technology? Our consultants are highly trained to help review, select, and implement the best business intelligence technologies to meet your needs.

Where should I store my data?

In today’s ecosystem, there’s really no reason to not use a cloud data warehouse. The elasticity and the near-limitless potential of compute that a cloud data warehouse brings is unparalleled by any other approach. This can help democratize data access across your entire organization. In terms of which cloud data warehouse you should use, again, this depends on your needs and goals. Below are some of our favorites and why you might use them.

• Snowflake: With uncoupled storage and compute, you’re able to scale up or down at the click of button, which makes running parallel workstreams incredibly easy. It has a robust security environment, great integrations with other tools, and runs on top of AWS, GCP, and Azure, so it works on your cloud provider of choice.
• Redshift (AWS’s cloud data warehouse offering): It requires more management than Snowflake but the trade off is that you’re able to fine tune your environment to match your exact needs. If you have skilled DBAs or cloud infrastructure specialists that want to get into the weeds, this might be your choice.
• Big Query (GCP’s offering in the cloud data warehouse space): Their model is entirely serverless, which means that you’re charged based on the compute run by each query (i.e. by how much data each query scans). It integrates really well with Looker and Google has a series of machine learning tools that are incredibly easy to plug into their environment.
• Azure Synapse: This service lacks the feature richness of Big Query or Snowflake and isn’t as well optimized as Redshift, but that isn’t to say it won’t improve in the future. If Azure is your cloud provider of choice, Snowflake is a good option for your cloud data warehouse.

How should I ingest my data?

There are a couple different ways you may want to pull your data into your data warehouse. The method you choose depends on the cloud architecture that you are implementing. Are you going to use a data lake? What is your cost sensitivity? How frequently does your data need to be ingested? Each of these answers will impact the data ingestion techniques and tools that are best for you.

Here are some data ingestion methods that we frequently use with our clients:

• Fivetran: Automated data ingestion. I like to describe Fivetran as “the world’s best data engineer for half the price and no hassle”. It is easy to use and kickstarts analytics projects by clearing the ingestion hurdle.
• Stitch: Automated data ingestion. Under the Talend umbrella, Stitch makes ingesting your data incredibly easy. The main differences between it and Fivetran are the type of connectors and the pricing models. One interesting consideration is its alignment with Singer, an open source way of building out your own connectors and destinations. While relatively young, the open source community is excited.
• Singer: You can also use singer on its own, though you have to manage and scale the infrastructure. A lot more manual than Fivetran or Stitch but if you’ve got the staff and the expertise, it is an interesting option.
• Serverless Python: Serverless computing (defined here) as an execution framework with the ability to dynamically allocate compute, is a key feature of cloud infrastructure. Here at Analytics8, we developed our own method of ingesting data into a cloud data warehouse using Python and taking advantage of the serverless compute offered by AWS and Azure.
• Spark: If you need to move massive data sets over distributed architecture, Spark is one of the key players. Designed for big data, it is a lot more manual than the options above but has a flexibility and richness of feature that could interest the more technically inclined.

What is the best tool for my use case?

A lot needs to be taken into account to answer this question – your current cloud provider, what other technology you use, how custom you need your environment to be, how feature-rich you want your tool, and more. If you are still unsure about which route to go, or if you have a specific use case question, we offer a complimentary data strategy session, where one of our analytics experts can consult with your company about your data integration needs and how it fits into your overall data strategy.

Callum McCann

Former accountant turned consultant, Callum utilizes his experience and skillset to help clients better use their data. From creating serverless ETL pipelines to designing efficient data warehouses in multiple cloud providers, he builds, designs, and implements modern data architectures that promote the usage of large volumes of data. Outside of work, Callum enjoys reading fantasy/science fiction, rock climbing, and biking around the city of Chicago.

• Best Practices
• How Tos
• Data & Analytics Industry
• Data Warehouse
• Data Strategy
• Data Architecture
• Cloud