Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Sometimes it pays to be pushy.
By “pushy,” in this case, I mean pushdown optimization, which is admittedly less catchy but still very cool.
Source: GIFER
If you’ve ever had to move massive sets of data back and forth between a database and a separate analytics environment, you know what it’s like to bang your head against the wall as you spend untold hours transferring data from platform to platform to platform – all while dealing with access roadblocks, data quality issues, and computing limitations.
That’s where pushdown optimization comes in. With the in-database processing capabilities on Alteryx, you can “push down” into another data environment (like a data warehouse) and run workflows directly in that environment. You’re accessing, prepping, and analyzing your data right where the data lives.
In other words: instead of bringing the data to Alteryx, we can also bring Alteryx to the data.
This makes the data happy, because big data doesn’t like to be moved around, and you save both processing time and personal sanity.
For pushy people like me (here, I mean ”pushy” as in impatient, assertive), getting a runtime from minutes to seconds is a game-changer. Patience is not a virtue when it comes to solving critical business problems, and being data pushers saves us all from being pencil pushers.
So if you’re not getting pushy and taking advantage of in-DB with Alteryx, you’re missing out! Whether you use Designer on your desktop or Designer Cloud, it’s fast and fun to do your analysis directly in the data source.
I’m still brand-new to using Alteryx, with a month of learning Designer under my belt. I’m not exactly a power user yet. But you don’t have to be a power user to connect Alteryx with powerful data sources like Snowflake, Amazon Redshift, and Azure Synapse Analytics.
As a newbie getting to know the ins and outs of in-DB workflow processing, I started with Snowflake because it’s ridiculously easy. With Snowflake, I can securely access my organization’s data across departments and scale computing resources up and down as I go (which means I’m saving $$).
There are multiple ways to connect directly to Snowflake from Designer. For a standard workflow, you can use the Input Data Tool (select Connect a File or Database) to start reaping the benefits of Snowflake’s scalability and performance within Alteryx.
To make that in-DB magic happen, start with the Connect In-DB Tool. If you don’t already have Snowflake connected, add a new in-DB connection by selecting Snowflake as a data source.
From there, you can build a workflow using the in-DB versions of the tools you know and love, from Filter and Formula, to Union and Summarize. A pro tip to get the most out of Snowflake: Use those in-DB tools as early in your workflow as possible. If you need to use Standard tools that aren’t available in-DB, design your workflow so you can prep, blend and transform as much as you can in Snowflake first.
Here’s what a standard workflow runtime might look like compared to an in-database workflow in Snowflake:
From minutes to seconds. Like I said...it pays to be pushy!
The problem-solving potential is quite literally limitless with Alteryx and Snowflake. If you need some inspiration to get started, check out ready-to-use templates for practical business cases like customer lifetime value and segmentation.
Happy pushing!
Helpful resources:
- Snowflake + Alteryx + Tableau Video Walkthrough
- Best Practices for Using Alteryx with Snowflake
- Snowflake Help & Documentation
- Database-ics Interactive Lesson
