At onepoint, an international technology consulting company, we were employed for the task of restructuring the Data Architecture of one of Australia’s leading health care companies. At the core of this project, we chose to use Snowflake, a world-leader in a cloud-based data warehousing. The technology is built as the next generation for data storage and management. Snowflake was selected as it best suited the needs of the project: security, scalability, reliability, cost management and manageability. Thus, onepoint viewed it as the most sustainable long-term option for the client.

Within this project, we recognised that the medical centre would benefit greatly from an application that allowed the viewing, creating, editing, and deleting of metadata records, in an easy-to-use and more visually understandable way. Without the want for a lengthy process of developing an app from scratch, onepoint explored all the options available for low-code, application building platforms. Many of these platforms just did not have the same flexibility or capabilities that the task required. Yet, one site stood head and shoulders above the rest.

Microsoft PowerApps is at the forefront of developing low-code applications, particularly for businesses. It provides an easy way to link your company’s data and manipulate it in almost any way you like, according to your business’s needs. Due to this central focus that PowerApps places on data, it goes without saying that databases are at the foundation of nearly every app created on the platform.

So, Microsoft provide native access to an extremely wide range of data sources from PowerApps: from the basics of Excel, to more complicated Flows allowing you automate business processes. It allows native connection to services like MySQL, Amazon S3, Oracle, alongside many others. You can read more about the connectors to Data Sources that PowerApps offers here.

Problem

After extensive research, the team at onepoint realised one fundamental problem that arose from choosing PowerApps to work with a Snowflake database: there is no native connection between Microsoft PowerApps and Snowflake. While many users have been calling out for Microsoft to create one, there is no apparent action.

Currently, there is only third-party solutions such as CData, that provide connection services between the two platforms. However, these can be quite costly, and at onepoint we determined that it would be better for security and financial reasons that we would explore what internal options we had, using the services already incorporated in the Architectural design.

Solution

At onepoint, we identified that you could make custom connectors within the Dataverse on the Microsoft PowerApps Platform. These can be used to connect to an API, and subsequently connected to from your custom-made app on the platform. So, it was concluded that if we were to make a custom API, able to connect to the Snowflake Data Warehouse, it would be possible to execute queries on our data. Together, this custom connector and API would essentially create a link between Snowflake and PowerApps.

To build the API required, we used Azure Functions. A logical decision given we were already using this within the Data Architecture framework. Snowflake provides a range of connectors to choose from, and our solution utilised the Python connector.

Visual Studio Code was chosen to be the text editor, as the Azure extension allows for easy cooperation between the services to create, write and then deploy the function.

Prerequisites

1. Python
2. Azure Account
3. Snowflake Account

Written by Oliver Burke under Adrien’s mentorships.