Picture this Data Engineering Context:

Just like me, as an Enterprise Data Architect, you are at the heart of a bustling data engineering project that requires orchestrating data flow between on-premise agents collecting the data from the source systems and pushing it in near real-time to a server (microservice) deployed in the AWS cloud.

Sounds exhilarating, right? That’s precisely what I have been up to in our latest data engineering project.

The Quest and the Conundrum (CPU Resources, Throttling):

Our mission? To seamlessly collect and process data from our fleet of on-premise agents in near real-time. It sounds like a simple or common data engineering solution, right? Not quite.

We grappled with the intricate puzzle of optimizing server deployment resources, particularly CPU usage. Balancing between on-premise and cloud solutions added another layer of complexity, throttling our progress.

The Ingenious Solution:

Enter PySpark and Django, our dynamic duo in data orchestration.

With PySpark’s prowess in handling diverse data types and Django’s scripting magic, we devised this duo as a standardized approach to tackle all our ETL (Extract, Transform, Load) requirements.

Fun Fact: Did you know Django has a nifty trick up its sleeve? Say hello to “python manage.py runscript <python_file_name>,” your trusty sidekick for executing command-line business processing use cases.

The Epic Ensemble: Apache Airflow, Django, PySpark, and MongoDB

Here’s where the magic unfolds:

1. Apache Airflow sets the rhythm, orchestrating Django’s “runscript” at 1-minute intervals.
2. Django takes the stage, looking into ETL log tables to determine the business start and end dates.
3. PySpark steps into the spotlight, lifting source data based on the above business dates and weaving its transformative magic and the results into MongoDB’s tapestry.

Riding the Data Collection and DB Persistence Waves: Ingestion Rate and Beyond

With 45 files per minute and a 15-second buffer for ETL completion, we ride the data collection waves of data ingestion. But amidst the frenzy, we encountered a familiar foe: the overlap of PySpark ETL jobs, causing CPU chaos.

As the product evolved with more data feeds added as sources to the above design, sometimes PySpark ETL overlapped into the next 1-minute run. This scenario caused PySpark to kickstart a new 1-minute ETL job and consume CPU resources

The above scenario caused AWS EC2 T1/T2 type instances to hang, and we had to restart the AWS EC2 instance. Though we upgraded the AWS EC2 instances to M4-2xlarge, the same ETL job overlapping needed to be prevented because other ETL jobs are executed on the same ETL EC2 instance.

Enter the Hero: Redis Cache

Faced with the challenge of preventing concurrent ETL jobs, we enlisted the help of Redis Cache. Like a faithful guardian, it stands vigilant, marking the start of each ETL journey with the current timestamp in the Redis cache and ensuring no job steps on another’s toes.

Pro Tip: Redis Cache doesn’t just prevent chaos; it fosters harmony, ensuring our CPU resources are utilized effectively. This means that when the next 1-minute ETL job finds that an ETL job is already running and the ETC cache data timestamp is within 5 minutes (an arbitrary value), the ETL job will not continue. The ETL job overwrites the cache if the latest cache data is more than 5 minutes old to support any ETL exception scenarios.

Join the Adventure! (Other ETL Jobs)

And there you have it: our journey into real-time ETL, where data flows like a symphony, orchestrated with precision and powered by innovation.

We continued to adapt the above approach to solve similar data orchestration challenges for other ETLs.