Expertflow ETL - Data Platform

Introduces a custom Python-based ETL (Extract, Transform, Load) tool enabling users to extract data from various sources, perform transformations, and load the results into different targets. This is designed for easy deployment, customization, and orchestration, making it ideal for scalable data workflows.

Key Features

EF ETL allows defining custom pipelines that incorporate multiple sources, targets, and transformation logic. Customization is achieved through configuration-based connections and transformation modules.

1. Flexible Pipelines
   Enables users to define custom pipelines that can incorporate various sources, targets, and transformation logics. It is designed for ease of customization, allowing for the addition of specific transformations, filtering, or aggregations as needed.

2. Multiple Data Sources
   This ETL supports a variety of sources, including:

   • MongoDB

   • MySQL

   • Informatica

   • SQL Server

   • APIs

   Users can quickly connect to these sources using configuration-based connections, ensuring compatibility and ease of setup.

3. Target Support
   EF ETL can load transformed data to several data destinations:

   • Snowflake

   • MySQL

   • SQL Server

   • ClickHouse

   • BigQuery

   • Additional targets can be added as needed for specific data pipelines or new use cases.

4. Airflow Orchestration
   This integrates with Apache Airflow for task orchestration, scheduling, and management. For more details, refer to the Airflow documentation.

5. Kubernetes Deployment
   Optimized for Kubernetes deployment, EF ETL leverages containerization for resource management and scalability. For Helm-based deployment of EF ETL, refer to this guide: Expertflow ETL Deployment

6. Transition from Talend Open Studio

   Previously, our ETL jobs were developed using Talend Open Studio (Learn more about existing ETL jobs: Reporting Connector. The transition to EF ETL represents a strategic decision to modernize and optimize our data platform. This change is driven by several key factors:

   1. End of Open Source Support: Talend Open Studio is no longer open-source, with no updates or support available beyond version 8.0. This limits its viability as a long-term solution for our evolving ETL needs.

   2. MongoDB Query Challenges: Many existing Talend-based ETL jobs relied on querying MongoDB for data retrieval. This approach frequently caused memory spikes and degraded MongoDB’s performance, as it is not recommended for intensive data processing workloads.

   3. Limited Customization: Talend’s reliance on pre-built components imposed significant restrictions on the customization and extensibility of ETL workflows. Adapting to new use cases or integrating with additional data sources often proved cumbersome.

   4. Scalability and Resource Management: Talend’s architecture lacked the scalability required for modern, containerized environments, making it less suited for high-volume or distributed data processing.

   5. Integration with Modern Tools: EF ETL leverages state-of-the-art tools like Apache Airflow for orchestration and Kubernetes for deployment, enabling streamlined workflows, enhanced resource management, and better observability.

   By transitioning to EF ETL, we have addressed these limitations and laid the foundation for a future-ready data platform that can accommodate complex ETL pipelines, scale seamlessly, and ensure efficient resource utilization.

7. Existing ETL Architecture

   EF ETL implements a modular and scalable ETL architecture designed to ensure seamless data extraction, transformation, and loading across diverse data sources and targets. The architecture includes:

   1. Source Agnostic Framework: The system supports multiple data sources such as MongoDB, MySQL, Informatica, SQL Server, and APIs. Each source is configured through a flexible and reusable connection module, enabling rapid integration and minimizing setup complexity.

   2. Transformation Layer: Data transformation logic is fully customizable, allowing for operations such as filtering, aggregations, schema restructuring, and enrichment. Transformation modules are configuration-driven, ensuring adaptability to diverse use cases.

   3. Target Agnostic Data Loading: EF ETL supports a variety of data destinations, including Snowflake, MySQL, SQL Server, ClickHouse, and BigQuery. The modular design also allows easy extension to additional targets based on emerging requirements.

   4. Batch Processing and Fault Tolerance: The ETL architecture incorporates batch processing capabilities to handle large datasets efficiently. Fault tolerance mechanisms ensure that pipeline failures are gracefully handled and logged for debugging and recovery.

By adopting this architecture, EF ETL addresses the limitations of legacy systems while enabling efficient, scalable, and adaptable ETL workflows to support modern data engineering needs.

Reporting Database Schema:

This document outlines the schema for the SQL-based reporting database Reporting Database Schema

Data Pipelines

EF ETL consists of several data pipelines, each designed to handle specific data extraction, transformation, and loading tasks. Below are the primary data pipelines included in EF ETL:

1. Forms Data Pipeline

2. Teams Data Pipeline