Transforming data in Snowflake with Fivetran

Transforming data in Snowflake with Fivetran


Snowflake has emerged at the forefront of a new era in enterprise data warehousing. Among its most game-changing features is its decoupling of compute resources from storage. This enables speed, scalability, and operational efficiency we data engineers could only dream of in the era of on-prem analytics. With on-demand compute in the cloud, the sky is the limit.


Snowflake solves a great many old data engineering problems, but others remain. The "T" in ELT brings its own set of issues. In particular, scheduling, alerting, and resource provisioning for heavy-duty data transformations are not easily accomplished with SQL alone. The cloud puts a great deal of power at our fingertips. How do we make use of it?


Looking for your next career adventure?


Check out our current opportunities.


Enter Fivetran. While primarily billing itself as a turn-key solution for data integration from third-party sources, Fivetran also has a handy feature under the hood, simply (and appropriately) called "Transformations." We should note that Fivetran Transformations support other database technologies too, but for our purposes here, we'll focus on Snowflake.


In Fivetran, a Transformation is a custom SQL statement coupled with a triggering and alerting mechanism.


If that sounds mind-numbingly simple, that's because, well, it is. But that's the beauty of it. Consider the implications: If you can encapsulate a data transformation as a SQL statement, you can harness the database's compute resources without having to write nor maintain any code for scheduling and fault alerting. With Snowflake, this also means you can dynamically provision the compute horsepower your transformation requires, then idle it when it's not needed. Fivetran simply acts as a SQL client. Conveniently, Fivetran can automatically trigger a transformation to run upon completion of loading raw data via one of its own connectors to a third-party app. It can also run transformations on scheduled intervals, which means it can operate upon any data in your database, whether or not you used Fivetran to collect it.


Take the following use case: Let's say you work for Widgets, Inc., manufacturer of IoT widgets that phone home with data on their locations and health. You already have a process in place landing the raw data in your Snowflake instance. Your team of analysts needs reporting the status of these widgets by region; perhaps differences in climate can affect their performance. Millions of these widgets update their status frequently, so these telemetry records pile up in the billions.


This is one of those situations where the operation is simple, but making it perform at scale can get hairy. This is where columnar databases like Snowflake really show their strength: Crunching numbers from big data sets while neatly abstracting away the parallel processing infrastructure required. We might as well let Snowflake do the heavy lifting—because that's what it's for. The only ingredient we need to code is the SQL. Something like this:

        , STATUS_ID
) AS src


In Fivetran, we'll need to configure our Snowflake database as a destination, using a Snowflake service account. Within Snowflake, we can configure this account to leverage the compute resources we'll need to perform the operation within our SLA.

Configuring an account in Snowflake

(Click image for a larger version)


Then, we can add a Transformation to the Fivetran destination, add our SQL statement, and set it on a schedule.

Adding a transformation to Fivetran

(Click image for a larger version)


And voilà! We're done. Fivetran will track the execution history of our transformation and can be configured to send email alerts in case of failure.


Of course, the simplicity of this approach comes with constraints. At this time, Fivetran does not officially provide any mechanism for injecting dynamic parameters into our SQL (although a Data Build Tool transformation feature currently in beta certainly has our attention). Airflow is another popular, open-source alternative where more complex transformation logic is needed.


Sometimes you need a samurai sword; other times, you need a scalpel. If it's the former, it's nice to have a tool that introduces only the complexity you need, and for that, Fivetran is great.





Looking for your next career adventure?


See our current opportunities




Paul Anderson

Paul Anderson is a senior data engineer at Logic20/20 as well as a business intelligence developer and tech industry veteran.


Paul Anderson on LinkedIn

Follow Paul on LinkedIn