August 8, 2022

Leveraging Snowflake to Make HVAC Analytics and Fault Detection Simpler

By Jason Kimmerling

HVAC data ingestion and analysis tend to be both tricky and tedious. Inconsistent data formatting and access methods across many brands mean that multiple custom solutions often become necessary. 

The Snowflake Data Cloud, however, greatly simplifies the process with many of its built-in features. This article will illustrate how Snowflake’s features can help with disentangling the complexities of ingestion and analysis of HVAC data.

Why is Snowflake Ideal for HVAC Analytics?

Building portfolio management (or even singular building management) can be grueling, particularly when it comes to analyzing HVAC costs or inefficiencies and detecting faults/ failures.  

What many do not realize is that a lot of the major headaches arise due to a very common issue: having mixed HVAC brands installed in your building(s). Every vendor has its own way of operating a Building Management System (BMS), and this includes the way trend logs are handled. Not only do the interfaces (which are employed to gather data) vary between most brands, but the following differences may be observed: 

  1. Data exports may differ in format
  2. The data itself may differ in structure

At some point, one may find oneself working with a vendor which has cloud solutions with API endpoints that return JSON formatted data. At a different time, one may come across a legacy installation in which the only data outputs are from the brand’s proprietary export software. This would mean that data could only be exported in formats like CSV and tab-delimited text files (or another format which the brand has specified). 

Snowflake can easily store any format type using many of its built-in types, such as VARIANT, OBJECT, or ARRAY. For example, the VARIANT type is used to store semi-structured data into a table row. This data can then be transformed and inserted into normalized or fact/dimension tables, or it can be queried in-place using various Snowflake custom SQL commands. 

Snowflake’s ability to accommodate any format or data type makes cross-brand variations a non-issue.

A screenshot illustrating Snowflake's ability to accommodate any format or data type

Once Snowflake has ingested the data, analytics can be handled in the following ways:

  • A bulk of the analytics can be managed inside Snowflake itself by using any of its various built-in functions, including many linear regression functions. This set of linear regression functions can be useful when calculating energy consumption in regards to HDD (heating degree days) or detecting possible equipment issues. 
  • The other option would be to use third-party tools or languages. Snowflake’s tight integration with the main cloud providers (Azure, AWS, and GCP) makes this pretty streamlined. These integrations allow for effortless use of tools such as AWS Quicksight, Azure Data Factory, etc. In addition to the cloud provider’s tools, Snowflake provides easy access to popular tools such as Tableau, PowerBI, Dataiku, Sigma, etc.

Examples of Real-World Applications

Below are some examples of real-world applications. AWS will be utilized in these examples, but they would work fundamentally the same with Azure or GCP.

Use Case 1: A Large Building Portfolio with 100% Cloud-based BMS

An easy way to procure data would be to use Cloudwatch events to trigger a Lambda function that would access the API endpoint and save the data as a JSON file to s3. A Snowflake integration would be triggered each time a new JSON was saved to s3. 

The integration would load the JSON file as a VARIANT data type into a new row in a staging table. The data is then available to be queried in place or it could easily be flattened into normalized table(s).

Use Case 2: A Large Building with a Tridium & Legacy HVAC Install

In the case of a fully integrated Niagara-based BMS, one could access all of the data via the oBIX protocol using the same AWS components (referenced in the previous example) and then send that data to an s3 bucket.

If the legacy equipment had its own separate BMS (which exports data to local CSV files), one could use the CSV files from the export software to save the data locally and then send it to a specific folder in the s3 Bucket. Snowflake could utilize two different integrations to monitor both s3 locations for new files and bring in data from each folder using its JSON and CSV file formats, respectively.

In each of the aforementioned examples, the HVAC data is ushered into one location, ready to be utilized for any of one’s analytics or compliance needs. While I’ve only provided two examples, they illustrate just how effectively Snowflake can be leveraged to work with any combination of HVAC brands.

Closing

As evidenced above, Snowflake greatly simplifies the process of centralizing, analyzing, and presenting HVAC data.

Snowflake puts forward the right tools for this typically complex job. From the special data types (VARIANT, OBJECT, etc) to the integrations with the major cloud providers, Snowflake makes it easier to gain insight into your HVAC systems. 

Whether your goals are data consolidation for compliance purposes (ex. hospital ORs and Cath Labs) or getting full building energy calculations, Snowflake makes them easily achievable. 

Interested in getting started with Snowflake? Our experts can help! As the 2022 Snowflake Partner of the Year, phData thrives at helping organizations succeed with Snowflake. 

FAQs

phData has a lot of articles detailing Snowflake’s usefulness in varying industries, please check them out on our blog.

Data Coach is our premium analytics training program with one-on-one coaching from renowned experts.

Accelerate and automate your data projects with the phData Toolkit