Transforming Data with Fivetran


By offering an impressive and ever-growing library of connectors, Fivetran has become a leading choice for extracting data from source applications and loading it in a cloud data warehouse. With reusability as a primary requirement, Fivetran has taken on the challenge of building out source system connectivity and established a core pattern of applying inserts, updates, and deletes in your target data warehouse. In a nutshell, Fivetran makes data more accessible by automating the data engineering workflow. The resulting platform is a point-and-click interface with a few buttons to push and levers to pull.

Where does Fivetran fit?

While there are several versions of the Data Hierarchy of Needs (depending who you talk to or which search engine result you click on), it is fair to say that Fivetran has focused on the foundational layers up to this point. Since inception, Fivetran has been refining the collection, movement, and storage of data in order to make it available for consumption in an analytic context.

In the latest release, Fivetran takes an exciting step into another level of the hierarchy by announcing ‘Fivetran Transformations’. The new transformation layer allows creating and saving custom SQL statements to be run against the data warehouse. No longer solely focused on extracting and loading data, adding the transformation functionality rounds out a full extract-load-transform (ELT) offering.

Fivetran currently supports over 125 sources as well as 14 cloud data warehouses; included in the sources are serverless cloud functions (AWS Lambda, Google Cloud Functions, and Azure Cloud Functions) that can be used to fill in the gaps for sources that are not officially supported. With the new transformation functionality, Fivetran adds data processing scenarios to its list as well. Simply put, Fivetran fits in any scenario where data needs to be replicated or transformed in a cloud data warehouse.

How do Fivetran Transformations work?

The first release (June 3, 2019) is available for customers using Snowflake Data Warehouse with other cloud data warehouses soon to follow.

The new transformation functionality integrates seamlessly with the existing product by allowing users to create, view, and manage transformations within their Fivetran dashboard. Once created, transformations can be scheduled to run at specific intervals or triggered by events.

SQL statements in Fivetran are executed using a “push-down” technique, sending SQL to the target warehouse and allowing the statement to be processed using compute provided by the target warehouse. By following the modern ELT paradigm, Fivetran takes advantage of their customer’s investment in cloud data warehousing.

Why is this important?

The addition of a transformation layer adds tremendous value because it removes the need for a companion tool to manipulate data after it has been loaded. With that in mind, Fivetran is directly reducing a customer’s technology footprint and limiting total cost of ownership for the data platform.

Fivetran transformations use the same SQL dialect that is native to the target data warehouse without any additional scripting required. Malformed SQL statements passing straight from Fivetran to the data warehouse also produce(get this): SQL errors. There aren’t any additional skill sets required to step through proprietary data processing on a server in order to diagnose and fix issues, further reducing an organization’s technical debt.

Fivetran’s transformation offering can be leveraged for a variety of use cases including summary tables, historical snapshots, slowly changing dimensions, federating data sets across sources, and other last-mile processing that needs to occur prior to surfacing data to consumers.

At Red Pill Analytics, we’re not only applauding our partners at Fivetran but also genuinely excited for our customers using Fivetran to start getting value out of the new functionality.

Need help?

Red Pill Analytics is a Fivetran partner and maintains partnerships with industry-leading data visualization and cloud data warehouse providers. From proof-of-concept to implementation to training your users, we can help. If you are interested in guidance while working with Fivetran or any of your data or analytics projects, feel free to reach out to us any time on our website or find us on Twitter, Facebook, and LinkedIn.