Data pipelines look deceptively simple in prototypes. You pull data from an API, transform it in pandas, and load it into a database. The whole thing runs in 30 seconds and the results look correct. Then you hand it to operations and within two weeks it is broken, running four hours late, or producing numbers that nobody trusts.
The Gap Between Prototype and Production
The prototype worked because the conditions were ideal. The source API was available every time you tested it. The data was clean. You ran it manually and could see when something went wrong. Production is different: APIs go down, rate limits hit at 3am, schema changes happen without warning, and nobody is watching.
A production-grade pipeline needs error handling at every stage, not just at the end. It needs idempotency, meaning you can run it twice and get the same result. It needs observability, so you know immediately when something fails and can understand why. And it needs documentation that a new team member can follow six months from now.
The Architecture That Holds Up
For most business data pipelines at the SME scale, a task orchestration tool like Apache Airflow or Prefect running on a managed cloud environment handles scheduling, retries, and alerting. Extract is separated from transform, which is separated from load. Data quality checks run after extraction and block the pipeline if critical validations fail.
The transformation layer is where most complexity lives. dbt (data build tool) has become the standard here because it brings software engineering practices to SQL transformation: version control, testing, documentation, and lineage tracking. A pipeline where transformations are undocumented SQL scripts is a pipeline that will fail silently and produce wrong numbers that get into board reports.
The Single Most Important Design Decision
Make every step in the pipeline auditable. Store raw data before transformation. Log every run with row counts and checksums. This makes debugging fast, makes data quality investigations tractable, and builds the trust with business stakeholders that the numbers are real. Without auditability, data quality issues become political problems rather than engineering problems.