Case Study
Snowflake Implementation for a Leading Real Estate Firm
The Need
A leading real estate firm specializing in the multi-family residential sector needed a platform to consolidate data across multiple source systems. The firm is currently leveraging the Yardi Property Management software to manage the aspect of all real estate operations, as well as Salesforce CRM, Argus Enterprise for valuation and asset management, and data from an internally built HR application.
The firm was facing several challenges associated with data not matching between multiple reports, questions on how certain metrics were being calculated, and consolidating data for reporting and analytics.
An opportunity to introduce a consolidated data warehouse was identified by the firm in order to streamline data processes and automate the firm’s reporting process.
Proposed Implementation / Architecture
Nivis Solutions identified an architecture that would eliminate these challenges and allow the firm’s resources to focus on the more important duties in their role. The solution consisted of leveraging the Microsoft Azure Platform to connect and harvest data from each of the key source systems, transform and load into a Snowflake Data Model, and allow for the presentation of the data in the Microsoft Power BI reporting tool. The illustration below depicts the data from the source systems through to the Power BI outputs utilized to visualize the data.
Data Sources
The Data Sources consisted of a cloud-based Yardi Property Management System, Argus Enterprise, Salesforce, and an on-prem custom built application consisting of the firm’s HR Data.
Each source presented a different method of connecting and extracting the data needed for the ultimate data warehouse. Yardi was a secure direct connection to a cloud database, Argus connectivity utilized API’s, Salesforce consisted of a cloud connection to the firm’s Salesforce instance, and the HR data consisted of On-Prem .csv files.
Data Extraction
The Microsoft Azure Data Factory service was utilized for the purpose of extracting raw data from each of the source systems and stored into a single Microsoft Azure Blob Storage account. A series of Delta processes needed to be developed for a number of result sets due to the volume of data.
Data Storage / Data Model
The Snowflake database technology was selected due to a number of reasons including
- Ability to auto scale with business needs
- Ability to introduce additional data sources in the future
- The security of the Snowflake platform
- Performance and Optimization capabilities of the Snowflake database
- Data sharing with internal users as well as external partners and customers
Nivis Solutions designed a fact dimension data model that supported multiple departments within the firms organization. Finance, Operations, Marketing, and HR groups were all identified as the consumers for the Snowflake database.
As the organization continues to grow and introduce additional sources, Snowflake will scale to support the volume and compute resources required to provide data for analytics. The security features within Snowflake allowed for securing the data at multiple levels (schema level, table level, and column level) to ensure users were only provided data access to results sets needed for their role.
Data Storage/Model
The data is stored in an Azure Blob Storage account and keeps Snowflake updated leveraging the Snowpipe service. Multiple data models were created to support reporting across each of the key departments within the organization.
Outputs
The Microsoft Power BI visualization tool is being to provide dashboards to the individual analytics teams across the firm.
Start Your
Snowflake Journey
Ready to make the switch to Snowflake? Contact us to get started.