Data Pipelines & Integration

A report on revenue by channel needs data from several systems: tracking events, orders in the ERP, contacts in the CRM, orders in the shop, plus files and interfaces from external providers. Often they are combined through manual exports or individual scripts that only one person knows. When one of them fails, it is noticed only when numbers are missing from a report.

The rebuild is worth it at the latest when someone has to work out laboriously which data is missing or duplicated after an error, when an interface changes and several reports break at once, or when load times no longer fit the time window. How the destination of the data is built, with layers and history, is described under data warehouse. This page is about the way there.

What happens between source and destination

A pipeline fetches data from the sources, stores it in the destination, cleans and standardizes it and prepares it for analysis. The destination is usually a data warehouse or lakehouse, sometimes an application that uses the data directly. An orchestrator controls order, schedules and retries, and automatic checks make sure on every run that the data is correct and on time. Tracking data is one source among several. The same pipelines can also send prepared data back into operational systems such as the CRM (reverse ETL).

ETL or ELT, batch or streaming

ETL and ELT

With ETL, data is transformed before loading. With ELT it is loaded raw first and transformed in the destination. In cloud platforms ELT is the norm, because the compute is there and the raw data stays available for later questions. ETL remains sensible when data has to be cleaned or masked before loading.

Batch and streaming

Batch loads data at intervals, such as daily or hourly. It is simple to run and enough for most reports. Streaming processes events continuously, so they are available within seconds to minutes. That pays off where freshness is decisive, for example in monitoring or operational dashboards, and it costs noticeably more to operate. So we first ask how often the report is read. If it is looked at once a day, real time is wasted.

Streaming needs a platform for event streams. Apache Kafka is widely used for this, also as a managed service, and the large cloud providers have their own services.

Changes instead of full extracts

Instead of reading a whole table again and again, change data capture transfers only the changes from the database’s log. That relieves the source system and also makes deletions visible. Debezium is a common open-source tool for it. A full extract on every run often burdens the source and the bill needlessly, where an incremental method would do.

APIs and files

With interfaces and files, page limits, query limits, authentication and structural changes are the typical hurdles. A pipeline has to cope with them without a run silently staying incomplete.

Orchestration and tools

An orchestrator starts runs on a schedule or on an event, keeps dependencies in order, retries failed steps, allows past periods to be reloaded and reports errors. Platform-independent open-source orchestrators describe workflows as Python code. Apache Airflow is the standard here.

The large data platforms come with their own orchestrator, Microsoft Fabric with Data Factory, for example. If you already work on one of them, you often need no additional tool.

Ready-made connectors exist for common sources, managed or open source. For sources without a connector, pipelines can be written as code with a Python library such as dlt. Many teams run the transformation in the destination with dbt. Because vendors increasingly bundle ingestion, transformation and orchestration into one product, we build so that individual tools can be swapped.

Securing quality: checks and data contracts

A pipeline that runs fast but delivers wrong numbers does more harm than a slow one. So on every run we automatically check whether columns and data types are right and mandatory fields are filled, whether keys occur twice and every order has a matching customer, whether quantities and totals are in the expected range and whether the data arrived on time. On top of that comes reconciliation with the source system: do the totals in the destination match those of the source?

Data contracts help as well: a written agreement between whoever supplies data and whoever uses it, covering structure, meaning, quality rules, freshness, ownership and how changes are handled. Open formats exist for describing them, such as the Open Data Contract Standard of the Bitol project under the Linux Foundation. When the contract is checked automatically, a changed interface is noticed before reports break. On a violation the pipeline stops, or the affected data is stored separately and reported.

What makes the difference in operation

Whether a pipeline is reliable shows after the first error. A run has to deliver the same result when repeated (idempotency). That works when data is written by matching on keys or by replacing whole periods, rather than simply appended. Past periods must be reloadable on demand, for example after an error or a change in logic. Without that, data is duplicated or missing after an outage, and the correction is done by hand.

Short-lived disruptions are retried automatically after a wait. Data that could not be processed lands in a holding area for review, instead of stopping the whole run or disappearing unnoticed. Run times, data volumes, freshness and errors are recorded, and alerts go to the responsible person and not to a mailbox nobody reads. Alerts without an owner are one of the most common reasons errors sit for days.

A source that renames a column is the classic case of an error that only shows up in the report. Structure checks at the entry point catch it. On cost, which is often billed by data volume, compute time or number of runs, incremental loading, sensible intervals and a look at consumption help.