“I appreciate how Coalesce just picks up new Snowflake features. Snowflake comes out with an improvement on a function and boom—I can start using it right away.”
Too many systems and sources
Challenges
Business data siloed in many disparate locations, making it difficult to get a holistic view of it
Data team’s time eaten up from constantly running reports for other departments, who weren’t able to make sense of the data themselves
Lack of insight into loan applications made it harder to provide the best possible customer service to members
One key person at WSECU who is tasked with the organization’s data strategy is Data Architect Joe Horton. His team of seven focuses on data and reporting, with a few team members also working on data science projects. The team has differing levels of expertise and varied skill sets, from dashboarding with Tableau to deep programming knowledge and expertise in Snowflake.
One of the team’s biggest challenges was that its data was stored in many different locations, which made it difficult to get a holistic view for reporting purposes. When someone in the organization would ask the data team a seemingly simple question such as “Can you give me mortgage information?”, Horton would have to collect and compile data from several different databases: commercial mortgages, standard mortgages, and more.
The data also came in from many different sources. For example, Horton explains, there could be five different ways an application—such as an application for a credit card, car loan, or house mortgage—could come into the credit union. “We would spend a lot of time managing it all,” he says. “It felt like we kept redoing and rebuilding the same things over and over.”
This siloed data led to inaccuracies in reporting, with different team members pulling data from various sources and interpreting it in different ways, or making common errors that would skew the results. “One person would, for example, count the credit union members one way and another would count them a different way, so we were getting different counts at times,” he explains. “Or maybe someone would forget to look at a flag that might be expired—they would pull in information but forget to say, ‘expired date must be null or blank,’ and they would get the wrong results.”
This problem was compounded by the fact that their banking core processing platform is account-centric rather than people-centric, so everything is at the account level. As Horton says, “This makes it hard for us to say, ‘Here’s a member and here are all the products and services this member has at our credit union,’ because they’re all in disparate systems.”
Another challenge the team had was the cost just to maintain the system, and Horton recalls that it was a lot of work to get Microsoft SQL Server stabilized. In addition, Horton and team were always tasked with running reports for other teams, which ate up a considerable amount of time. “Everyone came to us because we were the subject matter experts who knew the data well, but we wanted to enable our customers to do it themselves,” he says.
The previous system affected the credit union’s customer service as well. When members applied for a loan, the lag time between the application and the decision was sometimes longer than the applicant expected, and the data team struggled to determine why. “When you apply for an RV loan, you probably want an answer within hours or a day. To help the member, we needed an easy way to see the complete picture: When was the application submitted? When did an underwriter look at it? What was their decision? When will the money be approved? Because so much of this information was in different systems, it was hard to do this quickly,” he explains.
Gaining a clearer view
Solution
Migrated data from on-prem SQL Server to Snowflake
Adopted Coalesce as the data transformation solution for the new data stack
Used Snowflake and Coalesce to build out data marts to ensure everyone has access to accurate data
The team had already decided to move off of SQL Server more than a year earlier and begin migrating their roughly 20 to 30 on-prem databases to Snowflake. They built their own ETL engine, which they use to compress data into Parquet files to send to Snowflake. Today, they still load their daily jobs into both SQL Server and Snowflake, but soon plan to turn off their on-prem solution and just load data directly into Snowflake.
Once he had gotten comfortable with Snowflake, Horton began evaluating data transformation solutions that could work seamlessly with that platform. After consulting with some of his peers in the industry, he learned about Coalesce and decided it was the best fit given that it was purpose-built for Snowflake and the cloud. He was able to get up and running with Coalesce quickly: “After getting past being a total newbie, things came together and flowed well. Within weeks we were reviewing the first cuts of slim data marts.”
Horton wanted to create a single source of truth for WSECU’s data, and was pleased to find that Snowflake and Coalesce made it easy to do that. “We needed to build out streamlined ways to tie everything together, such as with data marts,” he explains. “Where Coalesce shines is that it lets us bring everything together and build a dimensional model of all those data sources. We call these ‘portfolios’ or ‘portfolio views,’ and they could be for anything: loans, mortgages, credit cards. We give these to our internal customers, who no longer have to worry about joining data across all those disparate systems.”
A more perfect union of data
Results
Dimensional modeling allows for creation of portfolios, where any employee can have access to consistent, accurate data
Data team’s internal customers can now self-serve their own reports, freeing the data team from time-consuming ad hoc work
360-degree view of loan application pipeline greatly improves customer service
Today, the data team’s newfound ability to do dimensional modeling has resolved the problem of inconsistent reporting. In addition, Horton’s internal customers are more self-sufficient when it comes to running their own reports thanks to Coalesce. While he has yet to get the whole credit union using the new system, he reports that those who are seem very pleased by how easy it is to use. “We’re trying to enable them so they can drag and drop, use pivot tables—whatever they want,” says Horton. “The stuff we’ve done with Coalesce is looking great, and the people who are using it are just raving about how cool it is.”
WSECU’s business analysts are among those benefiting from the new system. Even though many have experience with SQL and Tableau, given how they are spread out throughout the organization, there were a variety of ways each person approached reporting and analytics. “The collections department might have an analyst whose job is to focus on collections-type reports and analysis. They might be working on a different floor or even from home, and this has created more opportunities to write bad queries,” explains Horton. “The previous approach involved pointing to one of our 30 on-prem databases—the collections database, accounts database, and so on—and then doing the join in SQL or Tableau, and that’s when things could go awry. Today, all they need to do is simply point at the view and everything’s in there.”
Because WSECU is a financial institution, it is required by law to store its data so that it is available for long-term use; that is, the credit union’s data must be “persisted” and permanently available to its clients. This enables the organization to conduct “as of” reporting, meaning that all financial data must be regularly snapshotted with a date stamp to indicate exactly when it was pulled. Using Coalesce now allows Horton and team to have a much more streamlined process for looking at changes to data that are the result of snapshots taken over different days. “It takes time for that history to load, but once it does, we can use Coalesce to quickly analyze and evaluate it,” he says. “We didn’t have that ability before, so that was a big win.”
When it comes to assessing the status of loan applications, Horton’s team now has a 360-degree view of the loan pipeline thanks to Snowflake and Coalesce, and can take steps to improve the bottlenecks they are finding. This has greatly improved the credit union’s customer service. Says Horton, “We’re now able to capture application details really cleanly in our Coalesce views, and we’re finding a bunch of ways to improve those lag times.”
Overall, Horton emphasizes that the most important thing his team has achieved with Coalesce so far has been its newfound ability to create those data marts for everyone at WSECU to benefit from. “We really appreciate being able to point people to one location, where we can be confident everyone will get the same answer,” says Horton. “Coalesce is working extremely well and we’re really super happy.”
