ETL Pipeline Development for Messy Data

Most companies have data scattered across spreadsheets, exports from old systems, supplier files, CRM and ERP databases and the occasional shared mailbox full of attachments. Getting it into one place, cleaned and consistent, is the job of ETL: extract, transform and load. ETL pipeline development turns one-off clean-up scripts into dependable, scheduled processes that pull data from its sources, fix and standardise it, and load it where reporting tools and other systems can use it. This guide explains how ETL pipelines are structured, how to handle messy corporate data, how to load safely and incrementally, how to schedule and monitor pipelines and how to decide build versus buy, with a worked example.

ETL or ELT?

In classic ETL, data is transformed before it is loaded into the destination. In ELT, raw data is loaded first and transformed inside the destination, often a cloud data warehouse with plenty of processing power. Both are common. ELT keeps raw data available for reprocessing and suits warehouses; ETL suits destinations that should only receive clean data, such as an operational database or CRM. Many pipelines mix the two: land raw extracts in a staging area, transform, then load the clean result.

The stages of an ETL pipeline

StageWhat it doesKey concernsExtractRead data from files, databases and APIsAccess, formats, incremental extraction, API limitsStageStore raw extracts unchangedTraceability, reprocessingTransformClean, standardise, deduplicate, join and enrichRules, data quality, documentationValidateCheck results against rules and expectationsQuarantining bad records, alertingLoadWrite to the destinationIdempotency, incremental loads, performanceMonitorTrack runs, volumes and failuresOwnership, alerts

Keeping raw extracts is as valuable here as in any data pipeline: when a transformation rule changes or a bug is found, you can rerun from staging without going back to the sources.

Handling messy corporate data

Corporate data is messy in predictable ways:

• Spreadsheets with merged cells, header rows in odd places, totals mixed with data and columns renamed between versions
• Inconsistent formats for dates, numbers, currencies, phone numbers and country names
• Duplicates across systems with slightly different spellings
• Codes that mean different things in different sources
• Missing values and placeholder text such as "N/A" or "TBC"

Write transformation rules explicitly and keep them in code or configuration under version control, not in someone's head. Standardise values to agreed formats, map codes through reference tables and flag records that cannot be cleaned automatically for review rather than guessing. Our guide to CRM data standardization covers format choices for common fields.

Loading safely

Loads should be idempotent, so rerunning a pipeline does not create duplicates. Use stable keys from the source systems and upsert: insert new records and update existing ones. Most databases support this directly; in PostgreSQL, for example, INSERT ... ON CONFLICT updates a row when a key already exists. Load incrementally where you can, extracting only records changed since the last successful run, and record that checkpoint only after the load succeeds.

Worked example: consolidating supplier price files

Suppose a building supplies distributor receives monthly price files from 30 suppliers as spreadsheets in different layouts, and staff spend days copying prices into the ERP. Its team builds an ETL pipeline: each supplier's file is extracted with a small layout definition, transformed to a common structure, validated and upserted into a staging price table that the ERP imports from. The core transform and load step looks like this simplified, untested illustration using pandas and PostgreSQL:

import pandas as pd

def transform(raw: pd.DataFrame, supplier: str, layout: dict) -> pd.DataFrame:
    df = raw.rename(columns=layout["columns"])[["supplier_sku", "description", "unit", "price"]]
    df["supplier"] = supplier
    df["supplier_sku"] = df["supplier_sku"].astype(str).str.strip().str.upper()
    df["unit"] = df["unit"].str.strip().str.lower().map(layout["unit_map"])
    df["price"] = pd.to_numeric(df["price"].astype(str).str.replace(",", ""), errors="coerce")
    return df

def load(conn, df: pd.DataFrame):
    sql = """
        INSERT INTO supplier_prices (supplier, supplier_sku, description, unit, price)
        VALUES (%s, %s, %s, %s, %s)
        ON CONFLICT (supplier, supplier_sku)
        DO UPDATE SET description = EXCLUDED.description,
                      unit = EXCLUDED.unit, price = EXCLUDED.price
    """
    rows = df[["supplier", "supplier_sku", "description", "unit", "price"]].itertuples(index=False)
    with conn.cursor() as cur:
        cur.executemany(sql, list(rows))
    conn.commit()

Between transform and load, a validation step quarantines rows with missing prices, unknown units or price changes of more than a third compared with last month, and emails each supplier's buyer a short exceptions report. The threshold is the team's choice. Raw files are kept in staging, so when a supplier changes its layout the team updates that supplier's layout definition and reruns the month.

Documenting the pipeline

Every pipeline should have a short description that a new team member can follow: its sources and owners, what each transformation rule does and why, the destination tables, the schedule, the checks it runs and who to contact when it fails. Keep reference tables, such as code mappings and unit conversions, alongside the code so changes are reviewed and versioned. When business users ask why a number looks the way it does, documentation and stored raw extracts let you trace it back to its source in minutes rather than days.

Scheduling and orchestration

Simple pipelines can run on a scheduler such as cron. As the number of pipelines and dependencies grows, an orchestration tool helps: it runs tasks in the right order, retries failures, records history and shows what ran when. Apache Airflow is a widely used open-source example. Whatever the tool, define dependencies explicitly, so a load never runs on a half-finished transform.

Monitoring and data quality

• Run status, duration and failures, with alerts to a named owner
• Row counts extracted, quarantined and loaded, compared with normal ranges
• Freshness: when each destination table was last successfully updated
• Data quality checks on key fields after each load

Silent partial failures are the main risk, because downstream reports keep working and simply show wrong numbers. A pipeline that runs successfully but loads half the usual rows should raise an alert just as a crash would.

Custom ETL pipeline development or managed tools?

Managed ELT services with prebuilt connectors handle common sources, such as popular SaaS applications and databases, with little engineering. Custom ETL pipeline development makes sense for messy files, unusual systems, complex business rules or when data must be loaded into operational systems with careful validation. Many organisations combine both: managed connectors for standard sources, custom code for the rest. Tools such as pandas make custom transformations in Python practical for small and mid-sized data volumes.

Common mistakes

• Transforming without keeping raw extracts
• Non-idempotent loads that duplicate data on rerun
• Rules buried in spreadsheets or undocumented scripts
• Guessing at values that cannot be cleaned instead of flagging them
• Checkpoints saved before loads succeed
• Monitoring that checks only whether a job ran

For extracting data from invoices and other documents before it enters a pipeline, see our guide to Python invoice parsing.

Frequently Asked Questions

What is ETL pipeline development?

Building automated processes that extract data from sources, transform it by cleaning and standardising it, and load it into a destination such as a database, warehouse or business system.

What is the difference between ETL and ELT?

ETL transforms data before loading it; ELT loads raw data first and transforms it inside the destination, typically a data warehouse.

How do ETL pipelines avoid duplicate data?

By loading idempotently, using stable source keys and upserts, and saving incremental checkpoints only after a load succeeds.

When should we build custom ETL pipelines?

When sources are messy or unusual, business rules are complex, or data must be validated carefully before loading into operational systems.

Turn messy data into reliable data

We build ETL pipelines that clean, standardise and load data from spreadsheets, legacy systems and SaaS tools. See our CRM and API integration services, or talk to us about your data.

About the Author

Manav Sharma is the founder of Insyrge and a CRM solutions architect. He works across Zoho, Salesforce, HubSpot, Odoo and more, designing the CRM setups, integrations and automations that keep sales and operations teams working from the same data.