Seeking financial planning advice can be overwhelming and so can building data warehouses. Facet Wealth, a Financial Services company, caters to customers who seek high-quality financial planning and advice. Facet Wealth uses a subscription-based service model to help clients achieve their financial goals by giving them access to certified financial planners (CFP). By taking advantage of modern and cloud technologies, Facet stays a step above their competition. Video conferencing enables their clients to build a one-on-one relationship with their CFP, while Amazon Web Services (AWS) provides secure cloud storage for sensitive data. Access to data plays a key role in Facet’s ability to provide services to their clients.
Despite utilizing these technologies, Facet realized that they were early on their analytics journey and needed to invest in the right analytics infrastructure. Before this project was initiated, users were logging into individual systems and combining the data, a time-consuming and tedious process that does not scale with the needs to capture business logic over time. As a venture-capital and private-equity backed company facing substantial growth over the next few years, they needed to streamline and refine their processes and stand up the infrastructure needed to support the growth needs to run a data-driven business.
The solution? Facet Wealth needed an Enterprise Data Warehouse — and it needed to be built from scratch using cloud-based technologies. Red Pill Analytics designed a solution to accomplish their goals and more. With the build of a new data warehouse (EDW), Facet Wealth would have a single source for all their data from various systems with consistent business logic on historic records, allowing for higher visibility and valuable insight to make quicker and better-informed decisions.
The success of this project can be summarized in one word: DataOps. A methodological approach to data-engineering that improves speed and efficiency, DataOps was essential to the success of this project. This approach was leveraged from gathering the data, to gathering requirements, to building out the data structures and to creating the analytic reports and dashboards.
When Facet Wealth reached out to Red Pill Analytics for guidance, they began to work with stakeholders to define their requirements. The initial design was inspired by stakeholder requirements which were evolved with continued conversations with Business users. This allowed for useful feedback and a more refined solution. Solutions originally considered included:
For EDW infrastructure, Red Pill considered:
- Google BigQuery
- AWS Redshift
For data replication, Red Pill considered:
ELT was chosen over ETL, as it is more efficient and flexible than ETL for developing.
For ELT, Red Pill considered:
Most times, users do not know what they truly need until they see the process themselves, highlighting the importance of stakeholder involvement. After a few iterations, the final design was selected based on a few driving factors: DataOps, Cost, Scalability, Extendibility, Performance, and Manageability.
After multiple POCs, Red Pill Analytics met all of Facet’s requirements by creating StreamSets and Stitch ELT pipelines for eleven source systems (such as Salesforce and Google Analytics). Stitch, as it was easy to implement and maintain, was utilized for their proprietary platform. StreamSets was used for those sources that were not accessible via Stitch. StreamSets loaded data by tapping into REST APIs. ELT from replication to target in the same database increased overall performance for reporting and analytics functionality.
Next, Red Pill chose dbt (data build tool), a data-engineering tool by Fishtown Analytics. Using dbt, the data was transformed into a Stage database from the Landing database and then transformed into a Star Schema EDW database within BigQuery. As Facet’s needs became more defined, it became apparent that they needed a more comprehensive transformation and built-in regression testing that dbt provided. Utilizing dbt allowed for straightforward and easily implemented SQL code to transform the data into a Star Schema data model in the final data warehouse layer (EDW). Additionally, it allowed seamless built-in functionality for regression and data quality testing each time the hourly loads ran. The EDW database provided various reporting and analytics functionality from Opportunities in Salesforce through Revenue Recognition calculations for Financial planning and analysis (FP&A). Tableau was leveraged by Facet to build out the analytics dashboards and reports.
All work was version controlled–using Git–via dbt, with each piece of code submission reviewed by an architect. This process ensured code lineage and protection using a modern DataOps framework.
The build fulfilled the complex requirements of replicating data from 11 sources to create a data warehouse. Red Pill’s solution was completed in a way that was cleaner and easier to use, which streamlined their processes and allowed them to obtain reporting and valuable insight into their business.
In addition to fulfilling Facet’s requirements for the project, Red Pill kept the monthly spending limit on the technology implemented to less than $500.