I spent three months building a data pipeline last year and another two months fixing the things I got wrong. What follows is the architecture I ended up with, and more importantly the decisions that shaped it.
Where the data comes from
We pull from three kinds of sources. Internal databases, mostly PostgreSQL instances running on our own infrastructure. Third-party APIs for things we don’t control, like payment providers and analytics platforms. And CSV files that land in an S3 bucket whenever someone exports a report from a legacy system that nobody knows how to update.
The CSV files are the annoying ones. They arrive on irregular schedules, with inconsistent column names and encoding issues that only show up after you’ve already processed half the file. I built a validation step early on because the alternative was spending every Monday morning figuring out why last week’s numbers looked wrong.
The processing layer
Airflow handles the orchestration. I chose it because it’s Python-native, which means the people maintaining the pipelines already know the language, and because the visual DAG editor makes it easier to spot when a dependency is wrong.
The pipeline does three things in sequence: it cleans the raw data, transforms it into a consistent schema, and enriches it by joining against reference tables we maintain separately. The enrichment step is where most of the bugs hide, because it involves matching records across sources that don’t always agree on identifiers.
I used to run everything as one big Airflow DAG. That worked until the pipeline took four hours to complete and a single failed task meant re-running the whole thing. Now each stage is its own DAG with explicit dependencies between them, so a failure in the enrichment step doesn’t force us to re-extract data from the APIs.
Storage decisions
We store data in three places, and each one has a different purpose.
PostgreSQL holds the transactional data we query directly for reports and dashboards. It’s fast for point queries and simple aggregations, but it doesn’t scale well when you start joining large tables.
S3 is where the raw data lands before it gets processed, and where we keep historical snapshots for auditing. It’s cheap, it holds everything, and it doesn’t care about schema changes.
BigQuery is where the transformed data lives for analysis. We load it from S3 on a schedule, and the BI tools query it directly. BigQuery handles the heavy aggregation work that would choke PostgreSQL.
The awkward part is keeping these three systems in sync. Data flows from the raw bucket into BigQuery, and PostgreSQL gets updated separately through the pipeline. Sometimes they drift apart, usually because a pipeline ran late or skipped a step. I built a reconciliation job that runs every morning and flags discrepancies, which has saved us from presenting inconsistent numbers to stakeholders more times than I care to admit.
The analysis layer
Tableau and Power BI both connect to BigQuery for dashboards. We use Tableau for the executive-facing reports because the drag-and-drop interface lets non-technical people build their own views, and Power BI for the operational dashboards where the data model is more complex and the SQL-based calculations are necessary.
The machine learning side runs in Python, mostly scikit-learn for the simpler models and TensorFlow for the ones that need it. The models live in BigQuery as stored procedures so they can be called from the same queries that power the dashboards, which means the predictions are always based on the latest data.
What I got wrong
The biggest mistake was treating data governance as an afterthought. We had no access control policies for the first six weeks, which meant anyone with database credentials could query anything. I set up row-level security in PostgreSQL and role-based access in BigQuery after an incident where a junior developer accidentally exposed customer records in a dashboard that was shared externally.
I also underestimated how much time data quality monitoring would take. We had no alerts for missing data or unexpected value distributions, so problems went unnoticed until someone asked a question the numbers couldn’t answer. Now we have automated checks that run after every pipeline execution and send Slack messages when something looks off.
The diagram
Here’s what the whole thing looks like:
It’s simpler than the reality, obviously. The arrows hide a lot of error handling, retry logic, and the occasional manual intervention when something breaks at 11pm on a Friday. But it’s accurate enough for a first draft, which is more than I can say for most architecture diagrams I’ve seen.