ETL Tool Recommendations

[aaron_bostick]

Most of the tools listed here only support Redshift as the destination warehouse.

Being a Google customer, can you recommend a vendor who supports BigQuery as the warehouse?

I'm sourcing from MySQL and am interested in "snapshotting" certain data in daily intervals.

[lloydtabb]

Hey @aaron_bostick, good to see you.

I've been spending lots of time with BigQuery and it is pretty awesome. Here is a simple script to move files from mysql to BigQuery.

https://gist.github.com/shantanuo/4466298

Data load in BigQuery is pretty easy and straight-forward.

https://discourse.looker.com/t/loading-data-into-bigquery/2279

[jim]

We also faced the same issue.. and we choose Rivery.
Very easy to use in order to load and transform data on BigQuery.

[etai]

Hey guys! @segahm is right when he says that there’s a lot more to consider when it comes to choosing an ETL solution.

Some other thoughts: you also want to make sure that you find a tool with some flexibility. A big problem with many of the options available today is that the user can’t customize their data delivery schedule, extract specific relevant data or connect all of their sources.

In other words, they can’t choose what data they want to move, where they want it to go or how they want it to look and this can be a really frustrating limitation.

@zhill @kevin, Xplenty lets you easily integrate, transform and process data from more than 100 data stores and SaaS applications. Their platform is also very straightforward and user friendly - no code and no developers. www.xplenty.com

[George_Fraser]

Appreciate the kind words in this thread about Fivetran (of which I am CEO); I just want to chime in to say we do now support Oracle as a source and BigQuery as a destination, to agree with @segahm general formulation, and to share a couple general observations we've made after seeing many company's projects:

First, the key questions you should ask about any ETL connector are:

• What happens when a column is added in the source?
• What happens when the TYPE of a column is changed in the source?
• What happens when a row is deleted in the source?

Second, nearly everyone should use SOME commercial ETL tool rather than build it themselves. It's not that hard to write some scripts that sync your data; the hard part is keeping up with schema changes, and the REALLY hard part is dealing with turnover on your data team. For whatever reason, data engineering teams experience a lot of turnover. In my experience, there are only two scenarios where it CAN make sense to write your own ETL:

1. Your source schema is extremely stable so you rarely have to update your scripts.
2. ETL is part of your core business and you have a dedicated team that will maintain and monitor your ETL.

Lastly, as has been pointed out Fivetran does not support transformation, because we believe this to be an anti-feature: in the long run, it's a lot easier to maintain looker derived-tables. But not everyone agrees, and this is an important difference between the no-transformation vendors (us, Stitch) and our pro-transformation friends (Alooma, ETLeap, Boomi, ...).

[John_Norman]

For RedShift, Matillion has been pretty sweet . . .

[Jake_Stein]

This is a great discussion. Here’s one more aspect that may be helpful as you’re evaluating options:

The trend we’re seeing at Stitch (where I’m co-founder & CEO) is that, in addition to popular data sources like MySQL and Salesforce, more and more people have a few data sources that are much less common. There’s been an explosion in the number of tools that companies use to run their business, with a 2017 survey of the marketing technology landscape finding over 5,000 different products (up from 150 in 2011).

This is obviously important if your current ETL needs include a data source that no commercial ETL vendors support. However, it’s also important to choose a tool that will enable you to incorporate whatever new sources you will need in the future.

To do this, make sure that you choose a vendor that has a great API for importing data (which is true of many of the folks mentioned on this thread). With that, you can always write a script that pushes data to their API to be consolidated with your other data sources.

There are some drawbacks to this approach that you should keep in mind:

• If you’re reading this thread, you’re trying get away from building ETL scripts in the first place
• You’re on the hook for maintaining these scripts, which is a much bigger job than the initial build
• You then have to do the operational work to make sure that these scripts run indefinitely and notify the right people when there is a problem with authorization, data volume, API quotas, etc. This is the biggest job of all.

This is why we launched Singer.io, an open source project for ETL integrations. There are more than 20 open source integrations to data sources (or “taps” as we call them), and more are being built all of the time. If you’re building a new one, you can reuse code from the existing taps and helper utilities. We also have partners who can build custom taps.

These taps are maintained by Stitch and the community, so you don’t have to bear that burden on your own. Finally, we can incorporate any integration written to the Singer specification directly into our system, so you can run Singer taps on your own hardware or from within Stitch.