How We Scaled Data Tables Into A Medallion Architecture To Solidify Clip’s Loan Data Management

About the Company

Clip is Mexico’s leading digital payments and commerce enablement platform. Founded with the mission to empower businesses through digital payment solutions, Clip transforms mobile devices into card terminals, facilitating seamless transactions for companies and consumers alike. Committed to promoting financial inclusion, Clip offers innovative and trusted technological solutions that are accessible, transparent, and user-friendly.

Introduction

Clip recently partnered with Prestaclip to provide clients with access to loan services. This collaboration necessitated the development of a robust data platform to monitor loan performance and address the requirements of finance, risk, and accounting teams. Key deliverables included data models designed to manage loan-related data, encompassing attributes, collections, and amortization schedules.

The Challenge

As Clip expanded its financial product offerings to include loans through partnerships, the company faced significant challenges in effectively managing and utilizing loan data. Without a centralized, structured data platform, information was dispersed across multiple sources, making loan performance monitoring time-consuming and delaying actionable insights for internal teams.

Additionally, Clip's teams needed to expedite the tracking of key metrics such as payment schedules, outstanding balances, and loan statuses. The lack of clarity hindered their ability to quickly identify risks, enhance operations, and deliver value to customers.

To address these issues, Clip agreed to a discovery phase with Mutt Data, during which Mutt Data's teams conducted an in-depth assessment of data quality and history. This analysis revealed the necessity of transitioning to a Data Lake architecture within a Databricks environment.

The benefits of Databrick’s lakehouse environments include:

• A single platform integrating data, analytics, and AI.
• Reliable, secure, and high-performance data handling.
• Open and secure data sharing without duplication.
• Robust support for data warehousing.
• Centralized governance for all data, analytics, and AI resources.
• Simplified real-time analytics, AI, and application development.
• Tools for building and deploying ML and Generative AI applications.

Consequently, Clip decided to migrate to Databricks. This endeavor required strategic guidance for planning and implementation, including the evaluation of optimal tools and components for each infrastructure segment. The migration spanned approximately four months and involved close collaboration between both teams.

An action plan was established, encompassing the creation of a series of tables to capture essential information for reporting and analysis. This plan was collaboratively designed by members of Mutt Data and Clip’s teams. Mutt Data adopted a Medallion Architecture a structured approach to organizing data in a lakehouse that enhances data quality and structure incrementally.

Data flows through three layers—Bronze, Silver, and Gold—each adding refinement and value. This "multi-hop" architecture ensures data is progressively prepared for analysis and business use.

The successful implementation of this solution was also facilitated by prior collaborative efforts between Mutt Data and Clip, which involved th the migration of Data Warehouse to Data Lakehouse.

Testimonial

The Solution

Mutt Data designed an advanced data platform utilizing Databricks’ Medallion Architecture, which organizes loan data into three structured layers: Bronze (raw data), Silver (refined data), and Gold (business-ready insights).

Mutt Data also built key models to capture essential loan details, expected and real payment schedules, and collection records.

Key Models Built

1. Loan Attributes: Captures all relevant details of loan creation and enrichment for analysis.
2. Original Amortization Schedule: unifies loans into a common system of automation to ensure consistent payment tracking. This also allows Clip to monitor Prestaclip success within clients.
3. Collections: Tracks payment histories, including refunds and chargebacks, with plans for bank reconciliation.
4. Operative Amortization Schedule: Provides a detailed financial history of loan amortization.

Leveraging tools like Airflow and Databricks, Mutt Data automated the entire data processing pipeline. This automation ensured reliable insights for Clip’s teams, enabling them to monitor loan performance and alignment with strategic goals. Moreover, the solution significantly reduced the time required by multiple teams to complete these tasks, effectively addressing the challenges of scaling analyses as the business expanded.

Avoiding recursivity

A notable challenge encountered was computing the necessary metrics for the operative amortization schedule model, as the formulas involved interdependencies (recursivity). This approach aims to optimize resource demands and processing times, ensuring that the system operates efficiently and meets performance expectations. Utilizing SQL window functions and reformulating certain metrics, the team successfully achieved the desired outcomes.

Impact

Despite being a new service, Clip’s and Prestaclip’s loan offerings experienced rapid and exponential growth, reaching a point where tracking was unfeasible without a data platform providing real-time analytics and process automation.

From a process perspective, the accounting team previously calculated everything manually, which took days or weeks. With this solution, those numbers are now available in less than one day.

One of the most significant breakthroughs was refactoring their collections processes to maximize profits. Additionally, automating previously manual processes resulted in substantial time savings. The Modern Data Stack provided Clip with a level of granularity in loan information that was previously unattainable.

Results

Wrapping Up

Following the successful migration to Databricks, Clip’s financial team collaborated with Mutt Data to develop a data platform leveraging Databricks’ multi-layer architecture. This platform organizes loan data into three layers: Bronze (raw data), Silver (refined data), and Gold (business-ready insights), equipping Clip and Prestaclip with the tools to monitor loan performance, optimize collections, and support strategic growth in the lending market.

This solution not only centralized Clip’s data but also enabled them to monitor loan performance, optimize collections, and support strategic growth in the lending market.

Ready to unlock the full potential of your company? Schedule a call with us to discover how our expertise can elevate your capabilities.