Skip to content
EncryptTechBD
Home Industries Our Work Blog Pricing About Contact Discuss Your Project
বাংলা (BN)
Data & Analytics 5 min read

ETL pipeline architecture: a buying guide

How to buy an ETL pipeline: sources, latency, transformation, orchestration, vendor scoring, pricing questions, a pilot and an exit plan.

E
EncryptTechBD · 0 comments
Laptop with charts
Photo: Negative Space / StockSnap

An ETL pipeline is the plumbing between the systems that record work and the systems that report on it. When it works, no one notices. When it fails, two reports disagree, a dashboard shows yesterday's stock, or finance closes the month with a manual spreadsheet.

This buying guide explains the requirements to settle, the architecture patterns to compare, a vendor scorecard, pricing questions, a pilot and an exit plan. It uses ETL in the broad sense: extraction, transformation and loading, whether the work happens before or after the data reaches the warehouse.

Start with the decisions the data must support

List the reports, dashboards and operational decisions that depend on the pipeline, and for each one define the source, the grain, the refresh requirement and the owner. "Daily sales by branch by 8 a.m." is a requirement. "Real-time analytics" is usually a wish.

Requirements to settle

  • Sources: ERP, ecommerce, POS, CRM, bank statements, mobile money, spreadsheets, supplier files and APIs.
  • Volume and growth: rows per day, history to load, peak periods.
  • Latency: batch overnight, hourly, near-real-time or streaming.
  • Data quality: completeness, duplicates, late-arriving records, currency and unit conversions.
  • Governance: ownership, lineage, access control, retention and personal data.
  • Consumers: BI tool, finance model, operational dashboard, data science or reverse ETL.
  • Service level: freshness, availability, recovery time and who is called when a job fails.

Architecture patterns

ETL versus ELT

ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse using SQL. ELT is common with modern cloud warehouses because storage is cheap and SQL is easier to test and version. ETL still fits when data must be masked or reduced before it leaves a source system.

Batch, micro-batch or streaming

Most SME reporting needs batch. A nightly load is simpler to operate and easier to reconcile. Micro-batch reduces latency at the cost of more moving parts. Streaming is appropriate for fraud checks, live operations or event-driven products, not for a monthly sales report.

Managed connectors versus custom code

Managed connectors are faster for common SaaS sources, but they can be expensive at volume and limited in how they handle custom objects. Custom code gives control and creates maintenance work. A hybrid is normal: connectors for standard sources, custom extractors for the ERP and local systems.

Orchestration and transformation

An orchestrator such as Airflow or Dagster schedules tasks, manages dependencies and retries failures. A transformation layer such as dbt version-controls SQL models and tests. The split matters: orchestration should not hide business logic inside scripts that no one can review.

Vendor scorecard

  • Connector coverage: the actual sources you use, including the local ERP and marketplaces.
  • Incremental loading: how changes are detected, and whether deletes are captured.
  • Idempotency: can a failed job be rerun without duplicating rows?
  • Schema drift: what happens when a source adds or renames a column?
  • Testing and lineage: can you see where a number came from and test the model?
  • Security: secrets management, least-privilege access, encryption and audit logs.
  • Operations: alerting, retries, backfills, runbooks and support hours.
  • Exit: are pipeline definitions exportable, and is the transformation logic yours?

Pricing questions

Pipeline pricing can be based on connectors, rows, data volume, compute, seats or a managed service. Ask for a unit rate and a worked example using your volumes.

  • What counts as a row or a gigabyte, and how are reruns charged?
  • Are development, staging and production environments included?
  • What happens when a source exceeds the plan: overage rate or hard stop?
  • Is warehouse compute included, or billed separately?
  • What professional services are needed for the first three pipelines?
  • What does support cost after the warranty period?

Run a pilot

Pick two or three sources and one report that matters. Build the pipeline, reconcile the output to the source, and test failure cases: a missing file, a late record, a duplicated row, a schema change and a full rerun. A pilot that only demonstrates the happy path has not tested the operating model.

Plan the exit

Keep transformation logic in version control, document the source-to-target mapping, and make sure credentials and cloud accounts belong to you. Ask for a data export and a runbook. If the vendor's platform is the only place the logic exists, migration will be a rebuild.

The Bangladesh context

Data usually sits in a local ERP, a set of Excel files, one or two marketplaces and a bank or mobile money portal. Bank statements arrive as PDFs, suppliers send inconsistent spreadsheets, and Bangla text can break encoding if the pipeline assumes one character set. A good design starts with a landing zone that preserves raw files, then adds validation and a reconciliation report that finance can sign.

Metrics

  • Freshness: the time between source update and warehouse availability.
  • Completeness: rows received versus rows expected.
  • Reconciliation variance against the source system.
  • Failed runs, reruns and manual interventions per week.
  • Mean time to detect and repair a data incident.
  • Cost per pipeline and cost per million rows processed.

Where this fits in our work

If you need a pipeline that finance and operations can trust, start with Operations & Business Software. The pricing page explains how scope is built. For the foundation, read AI-ready data foundations, year by year.

Sources

Services that deliver this

Want this implemented properly?

Pricing →

Every engagement starts with a written scope. If a requirement is outside it, we quote it separately before starting.

Responses (0)

No comments yet. Add the first one below.

Leave a response

Your email address will not be published. Required fields are marked *.

Keep reading

More field notes