Alteryx Designer Desktop is Alteryx’s flagship product, and they recently released Alteryx Designer Cloud! Understanding how these software products are similar, yet completely different, is vital to making an informed decision about which one best fits your company’s needs.
The Snowflake Data Cloud is a powerful and industry-leading cloud data platform. Integrating Alteryx Designer with Snowflake unlocks the full potential of Snowflake’s cloud data platform, allowing users to harness its scalability, performance, and elastic computing power for data processing, analysis, and collaboration.
In this blog, we will compare how to leverage Snowflake with Alteryx Designer Desktop vs. Alteryx Designer Cloud over three categories:
Connection
Performance
Usability
Before we begin, it is important to highlight that Designer Cloud is a different product from Designer Desktop. An easier way to look at it is to imagine the Cloud version as the main tools and features of Designer Desktop (almost like a Lite version of Designer), with other functionalities spread out into other Alteryx Cloud products like AutoML or Alteryx Location Intelligence.
Connecting to Snowflake
Designer Desktop
Setting up Snowflake in Designer Desktop involves multiple steps. Because this is local, an ODBC driver must be installed first on your computer, either as a system or user.
The ODBC setup will require the following credential information: Account Name (Server), Database, Schema, Warehouse, and Role.
In Designer Desktop, you can use either the Data Input or Connect In-DB tool to connect to Snowflake. Select the Snowflake ODBC driver you want to use (enter your credentials again), and the Query Window will open. From there, you can choose a table or write a custom query. The SQL editor is quite helpful as it allows you to customize your query as needed.
When using the Input Data tool, it is crucial to leverage the Pre SQL and Post SQL statements to define the context for the query you want to run. For instance, if you need to use a specific warehouse or role, you can set it in the Pre SQL statement. Similarly, if you need to delete a temporary table or suspend a warehouse, you can specify those actions in the Post SQL statement.
Designer Cloud
Setting up Snowflake in Designer Cloud is easier, although the steps involved are quite different. Due to the cloud-based nature of the product and other cloud products offered by Alteryx, you need to establish connections outside of the Designer Cloud interface. You can create datasets and utilize them in Designer Cloud.
In the Connections tab, add a connection by searching for Snowflake and providing the following information::
Account Name: {identifier}.{region}.{provider} (e.g., sw81696.sa-east-1.aws).
Warehouse: I recommend using a dedicated virtual warehouse.
Database: We will create separate connections for each database we want to connect to. Schema-level information is not necessary at this point. We can access any of the schemas within the selected database.
Credentials: Currently, they support Basic and OAuth 2.0 authentication.
In the Data tab, we need to import a data connection. Select the schema and a table to create a dataset. This dataset will be available in Designer Cloud and other cloud products offered by Alteryx.
To use the dataset in Designer Cloud, open a workflow, drag the Data Input tool, select the dataset, and you’re ready to go. You are now connected to Snowflake.
Summary
There are significant differences when setting up Snowflake in Designer Cloud compared to the Desktop version. In Designer Cloud, only the dataset selection is available, and options like Pre SQL and Post SQL statements are not supported.
Designer Cloud currently only supports the use of the ACCOUNTADMIN role, which must be enabled as the default role in Snowflake. This provides access to desired connections and underlying data.Â
While Designer Cloud offers easier Snowflake connectivity, it’s important to note its limitations as a relatively new product. In contrast, Designer Desktop provides greater flexibility, control, and governance over data connections.Â
Designer Desktop allows for extensive customization and configuration, catering to specific needs with advanced features. It also offers enhanced control and governance mechanisms, crucial for organizations prioritizing compliance and data governance.Â
While Designer Cloud may be a convenient option for quick connectivity, organizations valuing flexibility, control, and governance prefer Designer Desktop.
Performance
Designer Desktop
When using Snowflake in Desktop, utilizing the In-DB tools will significantly enhance performance compared to regular tools. Try to use them as much as possible when connecting to Snowflake. We want to avoid transferring data from Snowflake to Alteryx.Â
By using In-DB tools, we push the execution to Snowflake, allowing us to leverage various performance improvement features such as the Query Result Cache.
When writing data back into Snowflake, we recommend using the Bulk Loader to expedite and optimize the process. You can enable this setting when creating the data connection in the Output Data Tool or the Connection tab for the In-DB tools.
In DB Tool:
Designer Cloud
In Designer Cloud, the execution is pushed down to the Snowflake level, making the functionality similar to the In-DB tools in Designer Desktop. When comparing Designer Cloud to Designer Desktop, consider the performance benefits of the In-DB tools in terms of how quickly data can be processed.
Summary
Running a simple test using the same process reveals significant differences among the three methods. The process involves reading 9 million records from one dataset and 8,000 records from another. We perform filtering, data cleansing, and joins before writing the data to a new Snowflake table.
Several factors contribute to the varying execution times for each method. These include the transfer of data from Snowflake to Alteryx, the volume of data being manipulated, and the processing time of each platform’s query execution through their respective logical layers.Â
We are specifically evaluating the Alteryx runtime, which refers to the time taken by each platform and method to complete their own execution process, ultimately affecting the overall result. Here are the results:
- In DB tools ran for 4 seconds.
- Cloud Workflow ran for 18 seconds.
- Regular tools ran for 70 seconds.
Usability
Designer Desktop
In the Desktop version, you have two options for querying data in Snowflake: using In-DB tools or regular tools. The main difference lies in where the data manipulation occurs, either within the database (In-DB tools) or in memory using regular tools. It is generally recommended to leverage In-DB tools, although there may be situations where traditional tools are necessary due to limitations.Â
Regardless of the method used, executing the workflow is necessary to view the data and ensure that the data manipulation steps are correct. With regular tools, the data will appear as usual, but with In-DB tools, you need to use the In-DB Browse tool to view the data.
Designer Cloud
In Designer Cloud, you can preview your data at each step of the workflow. It uses a small sample (based on a percentage) of your total data, keeping it in memory throughout the workflow. However, when filtering the data, the sample data may appear to have no remaining data, even if the complete dataset still contains data.
In Designer Cloud, the preview functionality allows you to visualize the entire process using the sample data. To see the complete dataset, you need to execute the job, which runs the workflow on the full dataset rather than just the preview. Currently, in Designer Cloud, you can only execute the workflow once there is an output to write, such as a database table or file.
Summary
When it comes to consistent querying and data execution, the Desktop version can impact workflow development. In contrast, Designer Cloud’s preview option provides a faster approach to building workflows. The central distinction thereafter lies in whether you need to output or visualize the data.
Choosing the Right Option
So, which should you use? Like many other consultants’ answers to client questions, the answer will be, “it depends.”
While many companies are transitioning their data and execution to the cloud, it is important to consider that only some things need to be moved to the cloud immediately. It is worth noting that Designer Cloud is a new product expected to undergo several improvements in future iterations. Therefore, companies will gradually migrate from Designer Desktop to the Cloud version based on their needs and timelines for establishing production-ready environments.
In terms of product preference, Designer Desktop stands out due to its superior connection capabilities and usability, particularly when combined with Alteryx Server for advanced analytics and governance features. Companies operating in highly regulated environments may require additional time for migrating to the Alteryx Cloud environment, thus picking Designer Desktop.
For organizations seeking to leverage the cloud and other cutting-edge technologies, the Cloud option holds great appeal. Alteryx has made significant strides in migrating and introducing new cloud-based offerings. For instance, if a user wants to cleanse their data, apply an automatically selected machine learning model to predict inventory storage costs for the next month, and maintain all storage and processing in the Cloud, the Designer Cloud product is the ideal solution.
Closing
Alteryx Desktop remains a well-known and reliable product with some limitations and performance issues. It excels in areas where the Cloud version currently lags behind, such as usability and connection.Â
For companies not bound by strict regulations and without heavy reliance on Alteryx, Designer Cloud becomes an appealing option for migrating to the cloud. As updates for the Cloud product continue, we can expect an increase in migrations from Desktop to Cloud.
In summary, the choice between Designer Desktop and Designer Cloud depends on specific needs, regulatory considerations, and the desire to leverage the power of the cloud. Both options offer unique benefits, and as the Cloud product evolves, we anticipate a rise in Desktop to Cloud migrations.
If you’re looking for professional help on how to choose the right version of Alteryx or simply how to get the most value out of the platform and its connection to Snowflake, phData would love to help! Reach out to us today for any questions, advice, and best practices.
A free trial can be found here.
