Red Pill Analytics Builds a Modern Enterprise Data Warehouse From Scratch at Facet Wealth

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.

Rising to the Challenge with DataOps

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.

Stakeholder Involvement is Key

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:

  • Matillion
  • Stitch
  • Alooma
  • StreamSets

 

ELT was chosen over ETL, as it is more efficient and flexible than ETL for developing.

For ELT, Red Pill considered: 

  • Rivery
  • Informatica
  • Segment
  • dbt

 

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.

Stitch, Streamsets & dbt Pipelines for 11 Source Systems

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.

Insightful Data, Delivered

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.

One More Thing

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.

WHAT DO THEY THINK?

"With the transformation of our company to be a data-driven business, it’s crucial to implement the right infrastructure that is scalable, flexible and dependable. Having one place in which we implement business logic across various systems and having the consistency of reporting and analytics that utilize the same data and logic across the board is going to help us scale our business and data/analytics needs in the correct manner."
Gorkem Sevinc, Chief Architect

THE GOALS

  • Data Warehouse built from scratch
  • Better insight into their data 
  • Time saving strategies
  • Scalable infrastructure
  • Affordable & modern solution

THE PRODUCTS

THE APPROACH

  • Design a Star Schema data model optimized to answer the analytic questions
  • Use Stitch and StreamSets to replicate all data into a “replication layer” into Google BigQuery
  • Use dbt to load and lightly transform required data from the replication layer into a Stage database and into the data warehouse layer (EDW).  
  • Regression and Data Quality Testing was developed using built-in functionality in db

THE RESULTS

  • Data Warehouse with one source of data
  • Increased overall performance for reporting and analytics functionality
  • Streamlined and refined processes
  • Scalable solution that grows by consumption
  • Modern DataOps approach

Related Links