This project addressed the challenge of fragmented financial data at Caliza, a company operating digital dollar accounts and stablecoin transfers. Caliza's data resided in various systems, including transactional databases, partner data, and contractor agreements initially managed in Google Sheets. This lack of a unified data structure hindered the ability to calculate Profit and Loss (P&L) with the necessary granularity and the flexibility to filter by different criteria, impeding informed strategic decision-making.
To overcome these challenges, Datakimia developed a robust data pipeline leveraging Fivetran for efficient ETL from Caliza's diverse databases into BigQuery. DBT was utilized for data transformation and modeling, integrating ledger data with contract information from providers (Effectiv, Jumio, Fortress) and integrators (Juno) to create a comprehensive data model for P&L calculation. The resulting insights were then presented through intuitive dashboards built with Apache Superset, providing a clear view of revenue and expenses with filtering capabilities and near real-time updates.
Before Implementation
Fragmented financial data across multiple sources (transactional databases, partner and contractor data).
There was no single, integrated system to effectively calculate Profit and Loss (P&L).
Previous data structure made it challenging to perform detailed analysis of revenue and expenses and to filter this information.
Key Improvements
Make better-informed decisions based on a clear understanding of financial performance.
Established an automated and accurate pipeline using Fivetran and DBT.
Ensured near real-time updates of financial data through an Insights Portal.
Project Overview
1.
Data Integration
Analyzed Caliza's transactional databases (ledger, Effectiv DB, Kyx DB), partner and contract data, using Fivetran for seamless and secure ETL from these sources into BigQuery via a proxy agent.
2.
Data Modeling
Utilized DBT to model and transform the raw data in BigQuery, creating structured tables and applying business logic for P&L calculation, including integrating ledger and contract data.
3.
P&L analysis
Developed a unified data model combining transactional data with provider and integrator contract details to enable comprehensive P&L analysis.
4.
Insights Portal
Created interactive and intuitive P&L dashboards, allowing users to visualize key financial metrics, filter by various dimensions, and gain near real-time insights.
BigQuery
Superset
Fivetran
DBT
Technologies used
Marcus McLaughlin
CTO at Caliza
"Datakimia expertly unified our complex and disparate data sources into insightful and user-friendly Profit and Loss dashboards. Their robust data pipeline and meticulous modeling have provided us with the granular financial visibility needed to make better strategic decisions. We highly recommend their services for anyone seeking to transform their data into actionable business intelligence"
