Traditional data warehouses on relational databases served as the backbone of analytics for decades, providing a structured environment for large volumes of structured data. But as data volume and variety grew semi-structured JSON, unstructured images and video, and the sheer scale ML use cases demand traditional warehouses began to show real limitations. This is where the Data Lakehouse emerged: an architecture that combines the flexibility and scale of a Data Lake with the performance and analytics capability of a data warehouse.

Here's how Bajaj Tech.AI implemented a Data Lakehouse for one of our financial marketplace customers, unifying internal transactional data with large external datasets to support a range of business use cases.

A data lakehouse isn't a bigger data warehouse, it's an architecture built to hold whatever shape of data shows up next, without a redesign every time.

What Data Sources Does the Lakehouse Bring Together?

The lakehouse was designed to unify two very different categories of data.

Internal Data Sources

• Applications and disbursal data: Customer transaction data spanning loan, card, and investment applications, approval data, and disbursal details.
• Financial aggregation data: Semi-structured data from financial services like Account Aggregator and Perfios, containing customers' bank statement details.

External Data Sources

• Data from partners like Adobe Analytics, call centre systems, and campaign response platforms, mostly in semi-structured or structured format.

Bringing internal transactional data and external partner data into one place is what makes cross-domain analytics like connecting a marketing click to a loan disbursal possible in the first place.

What Does the Data Lakehouse Architecture Look Like?

The lakehouse was built on a medallion architecture, which organizes data into three distinct layers: bronze, silver, and gold.

• Bronze layer: Built on AWS S3, the bronze layer ingests raw, unprocessed data, accommodating a variety of formats and structures without requiring upfront transformation.
• Silver layer: Also built on AWS S3, the silver layer refines raw data into a more structured format suitable for analysis, using our custom ETL framework, DRIFT, which leverages AWS Glue for efficient processing of high-volume datasets.
• Gold layer: Built on Amazon Redshift, using Redshift Spectrum to seamlessly integrate refined data from the silver layer, enabling advanced analytics and complex queries across both structured and semi-structured data.

The end-to-end data flow moves through several stages: continuous replication from the transaction system to an Operational Data Store (ODS) on PostgreSQL; ingestion of external sources and unstructured internal data into the bronze layer; processing by DRIFT, our highly configurable ETL framework; and finally, loading into Redshift's MPP (Massively Parallel Processing) database across both the silver and gold layers.

Each layer has a distinct job: bronze preserves the raw truth, silver makes it usable, and gold makes it fast to query at scale.

Why Use the Hudi Table Format?

The lakehouse adopted the Apache Hudi table format with Parquet files to enhance data management and performance. Hudi provides incremental data processing, transaction support, ACID compatibility, and time travel the ability to query data as it existed at a previous point in time. Parquet, meanwhile, enables efficient storage and retrieval of large datasets and is supported by most big data technologies, making it a natural fit for the silver and gold layers.

Hudi's ACID and time-travel capabilities matter most in a financial context, where being able to reconstruct exactly what the data looked like at a given moment is often a compliance requirement, not just a convenience.

How Does the DRIFT ETL Framework Work?

DRIFT is a configurable ETL (Extract, Transform, Load) framework developed in-house at Bajaj Tech.AI using Python, built for quickly creating new data pipelines to load data into the lakehouse. Setting up a job in DRIFT involves simple configuration changes across four layers:

1. Column Mapping Layer: Maps data from source to target layer for transformations and basic schema validation.
2. SQL Layer: Contains predefined SQL queries for filtering, joining, and aggregating data, applied in sequence to structure data for the silver layer and ensure consistency across datasets.
3. TQL Layer (Transformation Query Language): Adds flexibility for business logic and specific transformation rules not covered by SQL, ensuring scripts execute in the right order under the right conditions.
4. YAML File: Acts as the job configuration file, specifying input/output paths, transformation logic, SQL scripts, and metadata like partitioning and schema versions.

The point of a configuration-driven framework like DRIFT is that a new data pipeline becomes a configuration change, not a new codebase which is what keeps pipeline sprawl manageable as the number of data sources grows.

How Is the Gold Layer Structured for Analytics?

The gold layer is built on AWS Redshift as a combination of views on top of Redshift external tables (via Redshift Spectrum) and physical summary or aggregated tables on Redshift itself. Aggregated summary tables store frequently queried and frequently aggregated data drawn from multiple base gold tables, keeping common queries fast without repeatedly recomputing them from raw data. The gold layer is used for data consumption, whether for direct analytics or for building BI reports in Tableau.

What Business Use Cases Does the Lakehouse Enable?

The lakehouse supports several use cases that require integrating application data with large external datasets exactly the kind of cross-domain analysis that was difficult or impossible before.

• SEO analytics: Integrating large Adobe clickstream datasets with disbursement data from the application database to identify which URLs visited by organic traffic led to the highest number of conversions.
• Campaign effectiveness and governance: Combining campaign response data with disbursal data to track campaign effectiveness, while maintaining governance over campaigns run against targeted customers.
• Customer 360: Combining internal and external customer data demographic data, transaction data, product purchase history, offers data, campaign response data, and clickstream data to provide a single 360-degree view of the customer for better service, more personalized campaigns, and stronger propensity-scoring models.

None of these use cases were technically impossible before they were just prohibitively slow and manual without a unified data layer connecting application and external data.

Key Takeaways

• A medallion architecture (bronze, silver, gold) gives raw, refined, and analytics-ready data each their own clearly-scoped layer
• A configuration-driven ETL framework like DRIFT turns new pipeline creation into a config change rather than a new codebase
• The Hudi table format's ACID and time-travel capabilities matter especially for regulated, financial data
• Redshift Spectrum lets the gold layer query across both structured Redshift tables and semi-structured S3 data without duplicating it
• Real business use cases SEO analytics, campaign governance, Customer 360 only became practical once internal and external data were unified in one lakehouse

Conclusion

Our Data Lakehouse implementation for Bajaj Markets leverages AWS services and our custom DRIFT ETL framework to integrate application data with external partner data and large, unstructured internal datasets ensuring seamless access for advanced analytics. Organizations facing similar challenges unifying internal and external data at scale can draw a direct lesson from this build: a medallion architecture paired with a configuration-driven ETL layer scales far better than one-off pipelines built per data source.

This lakehouse also underpins the kind of analytics described in our companion piece on deriving insights from Adobe Data Feed, and reflects the broader data engineering discipline behind projects like the OBT Data Warehouse we built for a leading NBFC.

References

• Databricks: What is a Medallion Architecture?
• AWS Documentation: Getting started with Amazon Redshift Spectrum
• Apache Hudi: Spark Quick Start Guide

Looking to unify your organization's internal and external data at scale? Connect with our experts to explore the right data lakehouse approach for your business.