Designing an ETL Application: Why I Started with a Modular Monolith Before Microservices

What I learned while building the first version of an ETL application, planning the project structure, and thinking about future scalability with Redis, RabbitMQ, and Go workers.

While building the first version of an ETL application, I learned that the hardest part was not only reading CSV files, parsing Excel sheets, or preparing data for a database.

The harder part was designing the application in a way that could grow without turning the codebase into a mess.

At first, an ETL system looks simple:

Extract data Transform data Load data

But in a real project, the requirements become more complex very quickly.

The system may need to support different input types such as CSV, Excel, JSON, and APIs. It may need custom field mappings, validation rules, transformation rules, database-specific query formats, background jobs, progress tracking, and eventually large-scale data processing.

Because of that, project structure becomes one of the most important decisions.

A poor structure can make the first version easy to build but painful to maintain. A good structure allows the first version to stay simple while still leaving room for future improvements.

This article explains how I approached the architecture of an ETL application, why I would start with a modular monolith, and how the system can later evolve using background workers, Redis, RabbitMQ, and even Go-based worker services when performance becomes a real requirement.

Current Version of the Application

The first version of the application focuses on building a clean ETL workflow instead of solving every scaling problem immediately.

At this stage, the system focuses on:

• File/API input preparation
• CSV, Excel, JSON, and API-based input sources
• Field mapping between uploaded data and system data keys
• Basic transformation rule planning
• Database plan generation
• A React-based wizard interface
• A FastAPI backend with separated modules
• Configuration-driven behavior

The goal of Version 1 is not to build a fully distributed ETL platform from day one.

The goal is to build a clean foundation.

If the foundation is clean, the system can later support background workers, larger files, job tracking, queue-based execution, and scalable processing without rewriting everything from scratch.

What the ETL Application Needs to Do

The basic purpose of an ETL application is to take data from one or more sources, prepare it, and make it ready for a target system.

The high-level flow looks like this:

Input Source ↓ Extraction ↓ Mapping ↓ Transformation ↓ Validation ↓ Database Plan Generation / Load Preparation ↓ Load

The input source can be:

• CSV
• Excel
• JSON
• API

The target system can be:

• MySQL
• PostgreSQL
• MongoDB
• MSSQL
• Another internal or external service

The transformation phase may include:

• Date formatting
• Currency formatting
• Text cleanup
• Required field validation
• Type conversion
• Column renaming
• Business rule validation
• Default value handling

At a small scale, this can run inside a single backend application.

But as the system grows, some parts may need to become background workers or separate services.

That leads to one of the first major architecture decisions.

Monolith or Microservices?

One of the first questions in this kind of project is:

Should this be a monolith or a microservices-based architecture?

For the first version, I would not start with microservices.

I would start with a modular monolith.

A modular monolith is not the same as a messy monolith. The application is deployed as one backend, but the internal code is separated into clear modules with specific responsibilities.

The goal is to get the simplicity of one deployment while still keeping the codebase organized.

Instead of creating many small services immediately, we can create one backend with clean internal boundaries:

• Configuration Modul
• Extraction Module
• Mapping Module
• Transformation Module
• Validation Module
• Database Planning Module
• Job Module

This gives the project clean internal structure without adding unnecessary deployment complexity.

Why I Would Not Start with Microservices Immediately

Microservices sound professional, but they are not automatically better.

They introduce real complexity:

• Multiple deployments
• Network communication between services
• Service discovery
• Distributed logging
• Retry handling
• Message queues
• Versioning between services
• More DevOps work
• More testing complexity
• More failure points

For an early-stage ETL application, the requirements are still changing.

The mapping logic may change. The transformation rules may change. The supported file types may change. The frontend flow may change. The database targets may change.

If the system is split into microservices too early, every change becomes harder.

For example, if extraction, transformation, validation, and loading are already separate services, then a small change in the data structure may require changes across multiple services.

That is why a modular monolith is usually the better first step.

It lets the team move faster while still keeping the codebase clean enough to split later if needed.

Recommended Version 1 Architecture

The first version can use this architecture:

React Frontend ↓ FastAPI Backend ↓ ETL Modules ├── Configuration ├── Input Extraction ├── Field Mapping ├── Transformation ├── Validation └── Database Plan Builder

The backend is still one application, but each responsibility is separated.

This is important because the first version should not be overengineered, but it also should not be carelessly structured.

A good Version 1 should be simple, but not messy.

Suggested Project Structure

A clean first version could use a structure like this:

etl-application/ ├── backend/ │ ├── api/ │ │ ├── server.py │ │ ├── routers/ │ │ │ ├── health.py │ │ │ ├── extraction.py │ │ │ ├── mapping.py │ │ │ ├── transformation.py │ │ │ └── jobs.py │ │ └── middleware/ │ │ └── error_handler.py │ │ │ ├── src/ │ │ ├── config/ │ │ │ ├── app_config.json │ │ │ ├── config_loader.py │ │ │ └── database_profiles.py │ │ │ │ │ ├── input_sources/ │ │ │ ├── input_source.py │ │ │ ├── csv_extractor.py │ │ │ ├── excel_extractor.py │ │ │ ├── json_extractor.py │ │ │ └── api_extractor.py │ │ │ │ │ ├── mapping/ │ │ │ ├── mapping_service.py │ │ │ ├── field_matcher.py │ │ │ └── mapping_models.py │ │ │ │ │ ├── transformation/ │ │ │ ├── transformation_service.py │ │ │ ├── transformation_models.py │ │ │ └── rules/ │ │ │ ├── date_rule.py │ │ │ ├── currency_rule.py │ │ │ ├── text_rule.py │ │ │ └── validation_rule.py │ │ │ │ │ ├── database/ │ │ │ ├── database_plan_builder.py │ │ │ ├── sql_plan_builder.py │ │ │ ├── mongo_plan_builder.py │ │ │ └── database_models.py │ │ │ │ │ ├── jobs/ │ │ │ ├── job_service.py │ │ │ ├── job_status.py │ │ │ └── job_models.py │ │ │ │ │ ├── common/ │ │ │ ├── exceptions.py │ │ │ ├── logger.py │ │ │ └── constants.py │ │ │ │ │ └── tests/ │ │ ├── test_csv_extractor.py │ │ ├── test_mapping_service.py │ │ └── test_transformation_service.py │ │ │ ├── requirements.txt │ └── Dockerfile │ ├── frontend/ │ ├── src/ │ │ ├── app/ │ │ │ └── App.tsx │ │ │ │ │ ├── features/ │ │ │ └── etl-wizard/ │ │ │ ├── components/ │ │ │ │ ├── FileUploadStep.tsx │ │ │ │ ├── MappingStep.tsx │ │ │ │ ├── TransformationStep.tsx │ │ │ │ └── ReviewStep.tsx │ │ │ │ │ │ │ ├── services/ │ │ │ │ └── etlApi.ts │ │ │ │ │ │ │ ├── types/ │ │ │ │ └── etlTypes.ts │ │ │ │ │ │ │ └── hooks/ │ │ │ └── useEtlWizard.ts │ │ │ │ │ ├── components/ │ │ │ ├── Button.tsx │ │ │ ├── Card.tsx │ │ │ └── Table.tsx │ │ │ │ │ └── services/ │ │ └── apiClient.ts │ │ │ ├── package.json │ └── Dockerfile │ ├── docker-compose.yml └── README.md

This structure keeps the responsibilities clear.

The frontend handles the user experience.

The backend handles the ETL logic.

The ETL logic is separated into focused modules.

The configuration is not mixed with extraction, transformation, or loading.

That separation is important because ETL systems usually grow in complexity over time.

Why Configuration Should Be Separate

In an ETL system, hardcoding becomes dangerous very quickly.

If the system only supports one file format, one database, and one mapping style, hardcoding may work temporarily.

But once the system needs to support multiple input types and multiple databases, hardcoding becomes a problem.

For example, this is not flexible:

• If database is MySQL, write this query.
• If database is PostgreSQL, write another query.
• If field is contractNo, map it manually.

A better approach is to make parts of the system configuration-driven.

The config can define:

• Supported input types
• Supported file extensions
• Supported database types
• Database query format
• Identifier quotes
• Mapping options
• Validation options
• Transformation options

The goal is:

Change behavior through configuration when possible. Change code only when new logic is required.

However, configuration-driven design also has a limit.

If every small rule becomes configuration, the config files can become difficult to understand and debug. The goal is not to move all logic into JSON. The goal is to keep stable behavior in code and make frequently changing behavior configurable.

This balance is important.

Too much hardcoding makes the system rigid.

Too much configuration makes the system confusing.

Why Extraction, Transformation, and Loading Should Be Separate

ETL has three main phases, and each phase has a different responsibility.

Extraction

Extraction is responsible for reading data.

It should answer:

• Where is the data coming from?
• How do we read it?
• What fields or columns are available?

Extraction should not contain business transformation logic.

For example, a CSV extractor should not decide how a date should be converted for the database. It should only read the CSV and return structured data.

Transformation

Transformation is responsible for changing the data into the required format.

It should answer:

• Should this field be converted to a date?
• Should this value be cleaned?
• Should this field be renamed?
• Should this currency be normalized?
• Should this field be validated?

Transformation should not care too much about whether the original data came from CSV, Excel, JSON, or an API.

It should work with structured data.

Loading

Loading is responsible for preparing or sending the final data to the target system.

It should answer:

• Where should this data go?
• What database format is required?
• What query or document structure should be generated?
• How should failed records be handled?

When these responsibilities are separated, the system becomes easier to test, debug, and extend.

Frontend Structure: The Wizard Pattern

For an ETL application, the frontend should guide the user step by step.

A good flow is:

Step 1: Select input type Step 2: Upload file or enter API details Step 3: Preview extracted fields Step 4: Map fields to system data keys Step 5: Add transformation rules Step 6: Review configuration Step 7: Run or save ETL profile

This is better than showing everything on one screen.

The frontend should not contain the core ETL logic. It should collect user decisions and send them to the backend.

The frontend can own:

• Wizard state
• User input
• UI validation
• Preview tables
• Progress display
• API calls

The backend should own:

• File parsing
• Mapping logic
• Transformation logic
• Database planning
• Job execution
• Final validation

This keeps the frontend clean and prevents business logic from being duplicated.

Code Design Principles

For this type of ETL application, I would follow a few important design principles.

1. Keep Business Logic Out of API Routes

API routes should be thin.

A route should not do everything.

Bad structure:

Route receives file Route parses file Route maps fields Route transforms data Route builds database plan Route returns response

Better structure:

Route receives request Route calls service Service handles business logic Route returns response

This makes the API layer easier to understand and the business logic easier to test.

1. Keep Extractors Simple

Each extractor should only know how to read one input type.

• CSV extractor reads CSV.
• Excel extractor reads Excel.
• JSON extractor reads JSON.
• API extractor reads API response.

An extractor should not know about database loading or business rules.

2. Use Clear Models

Data passed between modules should have clear models.

For example:

• InputSource
• ExtractedField
• MappingResult
• TransformationRule
• ValidationResult
• DatabasePlan
• EtlJobStatus

Clear models make the system easier to understand because each module knows what kind of data it receives and returns.

3. Make Transformation Rules Extensible

Instead of putting all transformations inside one large function, use separate rule handlers.

For example:

• DateRule
• CurrencyRule
• TextCleanupRule
• RequiredFieldRule
• TypeConversionRule

This makes it easier to add new transformations later without breaking existing logic.

4. Use Config for Supported Types

Supported input types, databases, and options should come from configuration where possible.

That makes the application easier to change without editing core logic every time.

5. Add Tests Early

ETL systems need tests because small bugs can damage or corrupt data.

Important test areas include:

• CSV extraction
• Excel extraction
• JSON extraction
• Field mapping
• Date transformation
• Currency transformation
• Required field validation
• Database plan generation
• Error handling

In ETL systems, correctness matters more than just making the code run.

Transform Phase: Parallel Processing and Worker-Based Execution

The transformation phase can become expensive when the data file is large.

For example, imagine a CSV file with 500,000 rows.

Each row may need:

• Date conversion
• Currency cleanup
• String normalization
• Required field validation
• Type conversion
• Custom rule execution

Doing this row by row in a single process can become slow.

A better approach is to split the dataset into chunks.

Input Data ↓ Split into chunks ↓ Worker 1 transforms rows 1–10,000 Worker 2 transforms rows 10,001–20,000 Worker 3 transforms rows 20,001–30,000 Worker 4 transforms rows 30,001–40,000 ↓ Merge results ↓ Continue to load phase

This allows the transformation phase to run in parallel.

However, this does not always mean traditional multithreading.

The correct parallelism model depends on the language and workload.

In Python, normal threads are useful for I/O-heavy work, such as reading files, calling APIs, or waiting for database responses.

But for CPU-heavy transformations, Python threads can be limited because of the Global Interpreter Lock, commonly known as the GIL.

For CPU-heavy transformation work, Python multiprocessing or separate worker processes are usually better.

In Go, concurrency is one of the language’s strengths. Goroutines make it easier to process chunks of data concurrently with less overhead.

However, this does not mean the whole ETL application must be rewritten in Go.

A practical approach is:

Use Python for the main API and flexible ETL logic. Use Go later only for high-performance worker services if needed.

Rewriting the whole application too early can waste time.

The better engineering decision is to measure the bottleneck first.

If transformation becomes the bottleneck, move only that part to a Go worker instead of rewriting the entire system.

Where Redis Fits

Redis is useful for fast temporary data.

In an ETL application, Redis can be used for:

• Job progress tracking
• Temporary status storage
• Caching configuration
• Locking jobs
• Rate limiting
• Short-lived metadata

For example, when a large ETL job is running, the frontend needs to show progress.

The worker can update Redis with information such as:

Job ID: etl_123 Status: transforming Progress: 65% Processed rows: 65,000 Total rows: 100,000

The frontend can call the backend to check the job status, and the backend can read the status from Redis.

This is useful for high-frequency temporary updates because Redis is fast and works well for short-lived job status data.

However, important final job results should still be stored in a persistent database.

Redis can also help prevent the same ETL job from running twice by using locks.

But if Redis is used for job locking, the lock should have an expiry time.

Otherwise, a failed worker could leave a permanent lock and block the job forever.

This is an example of why adding infrastructure also adds responsibility.

Where RabbitMQ Fits

RabbitMQ is useful for background job processing.

In the first version, the backend may process the ETL request directly.

Frontend ↓ Backend ↓ Process ETL ↓ Return response

This works for small files.

But it becomes risky for large files because:

• The HTTP request may timeout.
• The backend may become blocked by long-running jobs.
• The user cannot track progress properly.
• A failed job may need to be restarted manually.
• The server may not handle multiple large jobs well.

A better architecture is to use RabbitMQ.

Frontend ↓ Backend creates job ↓ Backend sends message to RabbitMQ ↓ Worker receives job ↓ Worker runs ETL process ↓ Worker updates Redis ↓ Frontend checks progress

RabbitMQ acts as a queue.

The backend does not need to process the entire ETL job inside the request. It only creates the job and sends it to the queue.

The worker handles the long-running ETL task outside the request-response cycle.

This is cleaner and more scalable.

However, RabbitMQ also introduces new responsibilities.

Workers must be designed carefully because a message can be retried. Failed jobs should go to a failure state or a dead-letter queue. The system also needs retry rules, timeout handling, and clear job status updates.

RabbitMQ makes the system more scalable, but it does not remove complexity. It moves complexity into the job processing layer.

Redis vs RabbitMQ

Redis and RabbitMQ are not the same.

They solve different problems.

RabbitMQ = job queue Redis = fast temporary state

In an ETL system:

RabbitMQ holds job messages in a queue until workers consume them. Redis stores temporary job progress, cache data, and locks.

Example:

RabbitMQ: “Process job etl_123” Redis: “etl_123 is 65% complete”

They work well together, but they should not be added before the system actually needs them.

When to Move from Monolith to Microservices

The application should not become microservices just because microservices sound modern.

It should move toward microservices only when there is a real reason.

Good reasons include:

• The transform phase needs to scale separately.
• Extraction and loading have different resource needs.
• Large files require background workers.
• Different teams own different modules.
• Some services need independent deployment.
• Some parts need a different language like Go.
• The API server must remain fast while ETL jobs run separately.

A practical evolution path is:

Stage 1: Modular monolith Stage 2: Add background jobs Stage 3: Add Redis for progress tracking Stage 4: Add RabbitMQ for worker queue Stage 5: Split heavy workers from the API Stage 6: Move selected workers to Go if performance requires it Stage 7: Split into microservices only when the boundaries are stable

This approach avoids premature complexity.

Possible Future Architecture

A future version could look like this:

React Frontend ↓ FastAPI API Service ↓ RabbitMQ ↓ Worker Service ↓ Redis for Progress ↓ Database / Storage

A more advanced microservices version could look like this:

services/ ├── api-gateway/ │ └── Handles frontend requests │ ├── config-service/ │ └── Manages ETL profiles and configurations │ ├── extraction-service/ │ └── Reads CSV, Excel, JSON, and API data │ ├── mapping-service/ │ └── Maps input fields to system data keys │ ├── transformation-service/ │ └── Applies transformation and validation rules │ ├── load-service/ │ └── Prepares or loads data into target databases │ ├── worker-service/ │ └── Runs background ETL jobs │ ├── redis/ │ └── Stores progress, cache, and locks │ └── rabbitmq/ └── Handles job queues

This architecture is more scalable, but it is also more complex.

That is why it should be introduced gradually.

Problems and Limitations

Every architecture has tradeoffs.

The modular monolith has limitations, and the future distributed architecture has limitations too.

1. Scaling

If the backend is one application, scaling one part means scaling the whole backend.

If only transformation is slow, we still need to scale the entire backend unless we extract workers.

This is one reason background workers may become useful later.

2. Long-Running Jobs

ETL jobs can take time.

Running large jobs inside a normal HTTP request is not ideal.

This can cause:

• Timeouts
• Memory pressure
• Poor user experience
• Failed uploads
• Backend slowdowns

This is why background jobs become important once file sizes grow.

3. File Size Problems

Large CSV or Excel files can create memory issues if the system loads the entire file into memory.

A better approach is streaming or chunk-based processing.

Instead of loading the full file into memory, the system should try to:

Read file in chunks Process chunk Store result Continue

This helps reduce memory pressure and makes large files easier to handle.

4. Excel Complexity

Excel files are harder than CSV files.

They can have:

• Multiple sheets
• Merged cells
• Empty rows
• Hidden columns
• Unexpected formatting
• Formula cells
• Different date formats

The system must handle these carefully.

CSV files are usually simpler. Excel support requires more defensive handling.

5. Mapping Accuracy

Automatic mapping is difficult.

For example:

contract_no contractNumber Contract ID Agreement No

These may all refer to similar business concepts.

A mapping service can suggest matches, but the user should still be able to review and correct them.

ETL systems should not blindly trust automatic mapping when the data is important.

6. Transformation Rule Complexity

Transformation rules can become complicated.

For example:

• If currency is LKR, convert to USD.
• If date format is DD/MM/YYYY, convert to ISO format.
• If value is empty, use a default value.
• If field is required, reject the row.

If this logic is hardcoded, the system becomes difficult to maintain.

A rule-based transformation engine is better, but it also needs careful design so that rules do not become too difficult to debug.

7. Observability

When ETL jobs fail, the user needs to know why.

The system should provide:

• Job status
• Error messages
• Failed row count
• Validation report
• Logs
• Retry options
• Downloadable error report

Without observability, debugging becomes difficult.

This becomes even more important when background workers and queues are introduced.

8. Idempotency

ETL jobs must be designed carefully so that retrying a failed job does not duplicate data.

For example, if a job fails halfway through loading data and then runs again, the system should know whether to resume, replace, skip existing records, or rollback.

This is especially important when using queues like RabbitMQ because failed messages may be retried.

A good ETL system should define how retries behave before processing production data.

9. Security and Data Privacy

ETL systems often handle sensitive data, so security cannot be ignored.

Important areas include:

• File size limits
• File type validation
• Secure storage of uploaded files
• Environment variables for credentials
• Avoiding hardcoded database passwords
• Access control
• Audit logs
• Removing temporary files after processing
• Protecting personally identifiable information

Even in an early version, the system should avoid exposing credentials, internal URLs, or sensitive data in logs.

Security should not be treated as a final step. It should be considered from the beginning.

10. Microservices Complexity

Microservices solve some scaling problems, but they introduce new problems:

• Network failures
• Message duplication
• Retry handling
• Distributed logs
• Data consistency
• Service versioning
• Deployment complexity
• Monitoring requirements

That is why microservices should be introduced only when the system boundaries are clear.

If the modules are not clean inside the monolith, splitting them into microservices will not solve the problem. It will only distribute the mess across multiple services.

Recommended Roadmap

A realistic roadmap would be:

Version 1: Modular Monolith

• React frontend
• FastAPI backend
• CSV / Excel / JSON / API support
• Mapping service
• Basic transformation rules
• Database plan builder
• Config-driven design

Version 2: Background Job Processing

• Create ETL job
• Return job ID
• Process in background
• Track job status
• Show progress in frontend

Version 3: Redis and RabbitMQ

• RabbitMQ for ETL job queue
• Redis for job progress and cache
• Worker process for long-running jobs
• Retry handling
• Job failure reports

Version 4: Performance Optimization

• Chunk-based processing
• Streaming large files
• Parallel transformation
• Batch database loading
• Better memory management

Version 5: Selective Go Workers

• Keep FastAPI for main API
• Move heavy transformation worker to Go if needed
• Use RabbitMQ for communication
• Use Redis for progress tracking

Version 6: Microservices

• Split services only when boundaries are stable
• Separate extraction, transformation, and loading if needed
• Add service monitoring
• Add distributed logging
• Add deployment pipelines

This roadmap keeps the system realistic.

It does not jump into microservices too early, but it also does not ignore future scalability.

Conclusion

Building an ETL application is not only about reading files and loading data.

The bigger challenge is designing the system so it can support new input types, new databases, new transformation rules, and larger workloads without becoming messy.

For the first version, a modular monolith is a practical choice because it keeps development simple while still enforcing clean boundaries.

Redis, RabbitMQ, Go workers, and microservices can be useful later, but they should be introduced when the project actually needs them.

A good architecture does not mean choosing the most complex option from the beginning.

A good architecture means starting simple, keeping responsibilities separate, and designing the system so it can evolve safely.

That is the main lesson I learned while building the first version of an ETL application.