Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
AlexG
Alteryx
Alteryx

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. 

 

AlexG_1-1663605855430.png

 

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:

 

AlexG_2-1663605855432.png

 

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: 

 

Alex Gnibus

Hi there! I'm a solutions marketing manager on the Alteryx team, which means I love talking about all the awesome things you can do with Alteryx and its technology partners like Snowflake, AWS, Tableau and more. I'm passionate about making technical information fun and accessible so more people can learn about it!

Hi there! I'm a solutions marketing manager on the Alteryx team, which means I love talking about all the awesome things you can do with Alteryx and its technology partners like Snowflake, AWS, Tableau and more. I'm passionate about making technical information fun and accessible so more people can learn about it!

Comments
Pawel_Paleczny
8 - Asteroid

Wow, great improvement! Thanks for being unstopable with that enhancement :D

JoshuaB
Alteryx Product Evangelist
Alteryx Product Evangelist

Great article @AlexG

 

One more feature with many databases that you can really benefit from is bulk-loading! If you are wanting to load a ton of data and fast then make sure you look and see if there is a bulk loading option available! 

simonaubert_bd
12 - Quasar

Hello @AlexG 


Nice. I'm a big user of indb since the beginning on Hive. However, the feature has not been improved for years. After your article, can we expect some changes ? (such as choosing the db/schema of temp table, pre sql for in db, managing constraints such as PK/FK, partitionning, etc). I still don't understand why IN DB doesn't have more traction at Alteryx while it's a HUGE differentiator with competitors.

Best regards,

Simon

JoshuaB
Alteryx Product Evangelist
Alteryx Product Evangelist

@NicoleJ FYI 

AlexG
Alteryx
Alteryx

Hey @simonaubert_bd! Thanks for reading, and it's AWESOME to hear you've been using in-DB. I'm featuring in-DB so more people can learn about it and take advantage of it. Alteryx is actively expanding capabilities with data sources like Snowflake, and you'll continue to see more innovation there. The team really appreciates your feedback on in-DB and we will take this into consideration!