ETL Pipelines and Data Connectors

The problem a data connector solves

In many companies the sales, margin or stock report is assembled from several exports pasted into a spreadsheet. It works as long as the person who does it is available and never picks the wrong file version. The other common scenario is a script written in a hurry years ago: it runs on a schedule, and when the vendor changes its response format it quietly stops loading part of the data, and nobody notices for weeks.

An ETL (extract, transform, load) connector solves both. It pulls data from the source automatically, transforms it according to rules written in the specification, loads it into the target and, most importantly, detects and reports discrepancies. The service is for operations, finance and analytics teams that need figures matching the source.

Typical sources and targets

• Sources: ERP and accounting systems, CRM, e-commerce platforms and marketplaces, advertising and analytics tools, application databases, vendor APIs, CSV, XML or Excel files on SFTP or cloud storage, mailboxes with attachments.
• Targets: data warehouses (PostgreSQL, BigQuery, Snowflake, Azure SQL, ClickHouse and similar), Parquet file stores, business systems (ERP, CRM, PIM) and BI tools.

Warehouse data most often feeds BI dashboards or financial reporting. A connector into a business system can instead replace manual imports of price lists, orders or master data, for example into an ERP system.

What a reliable connector is made of

Full and incremental extraction

The first load moves the history; later runs fetch only changes, by modification timestamp, sequence number or change data capture (CDC) where the database supports it. A checkpoint is saved after each successful step, so an interrupted job resumes from the point of failure rather than starting over.

Mapping and transformation

Data types, currencies, units, time zones, status dictionaries and product codes are normalised according to a mapping table you approve before the build. Business rules, such as how margin is calculated on returns, are explicit and tested instead of hiding in spreadsheet formulas.

Deduplication and consistency

Natural or surrogate keys prevent the same records from being loaded twice, and operations are idempotent: re-running the same period produces the same result.

Error queue and reconciliation

Records that cannot be processed are quarantined with a rejection reason. A reconciliation report compares record counts and control totals between source and target, and exceeding a threshold triggers an alert.

Schema changes

New or removed fields at the source are detected at run time. Depending on what was agreed, the connector stops with an alert or loads the known fields and flags the change for review.

Off-the-shelf platform or a custom connector

Popular sources have ready-made connectors in tools such as Airbyte, Fivetran or Azure Data Factory. If one of them covers your need and its pricing works at your volume, we will recommend it and build only the missing transformations. A custom connector makes sense when:

• the source is a niche or industry-specific system, or an old database with no existing connector;
• the transformation rules are too specific for a generic tool;
• per-row or volume-based fees exceed the cost of your own solution;
• data must not leave your infrastructure or the EU.

Architecture and tooling

We usually write connectors in Python (with libraries such as SQLAlchemy, pandas or Polars) and transformations in SQL, often in dbt, which brings versioning and tests for business rules. Orchestration is handled by Airflow, Dagster or Prefect, or by cloud services (AWS Step Functions and Lambda, Azure Data Factory and Functions, Google Cloud Run and Workflows) when your team already uses them. Low-latency event flows run on queues or streams. If your team works in .NET or Node.js, the connector can be built in that stack.

The operating mode, batch (hourly or nightly, for example) or near real time, follows from how fresh the data really needs to be. More frequent syncs raise build, cloud and API-quota costs, so they should be backed by a business reason.

Security and GDPR in data pipelines

• read-only credentials at the sources and minimal permissions at the target, kept in a secrets manager;
• data minimisation: only the columns the analytical purpose needs, with personal data pseudonymised or left out where possible;
• encryption in transit and at rest, and storage in an agreed region such as the EU;
• no personal data in logs or alert messages;
• warehouse retention and deletion periods aligned with your retention policy and GDPR obligations.

How delivery works

1. Source inventory: test access, data samples, API limits, volumes and change frequency.
2. Mapping specification: a field-by-field table, transformation rules, metric definitions and reconciliation thresholds. This is the basis of the binding quote.
3. First source end to end: extraction, transformation, loading, reporting and alerts for one source before adding the next.
4. History load and reconciliation: results compared with existing reports, with every discrepancy explained together with the data owner.
5. Parallel run: for an agreed period the connector runs alongside the manual process.
6. Cutover and handover: the old process is switched off, the operator receives a runbook, and monitoring goes live.

A single connector is usually delivered within weeks of receiving source access and samples. Poor source data quality, vague metric definitions and waiting for permissions are what stretch the schedule.

What drives the cost of a connector

The indicative starting point is $1,900 excluding VAT for one connector with a clearly defined source and target. The price rises with:

• the number of sources and targets and the number of objects (tables, API resources) in each;
• data volume, frequency and required freshness;
• source API limits and pagination, or the lack of a change feed;
• data quality issues: duplicates, inconsistent formats, hand-typed codes;
• the complexity of transformations and business rules;
• the depth of the history load and audit requirements.

Integration-platform licences, warehouse costs and cloud consumption are excluded. Maintenance, meaning monitoring and reacting to source changes, is available from $249 per month. Other anchors are on the pricing page.

What we need from you

• read access to each source (ideally a test environment first) and to the target system;
• data samples and the current reports we will reconcile against;
• metric definitions and business rules, including who decides when two sources disagree;
• a data owner who approves the mapping and helps explain discrepancies.

Describe your data sources and we will propose a discovery scope. If you need an interface for other systems rather than data movement, see API development; if people will edit the data, see database applications. All services in this group are listed under data and APIs.

Related services

Frequently asked questions