How to Build a Modern Data Pipeline: From Raw Data to Business Dashboards

A data pipeline is the set of systems that move raw data from wherever it originates — databases, APIs, event streams, files — into a form where someone can actually make decisions from it. If that sounds simple, the gap between "sounds simple" and "works reliably at production scale" is where most data projects find their real costs. Right now, the economics of cloud compute and open-source tooling have dropped the barrier to building pipelines that would have cost seven figures a decade ago — but the architectural thinking required to get it right hasn't changed. This article walks through that thinking layer by layer.

The Four Layers of a Modern Data Pipeline

It helps to think of a data pipeline as four distinct concerns, each with its own set of tools and failure modes:

1. Ingestion — pulling or receiving raw data from source systems
2. Transformation — cleaning, structuring, and modeling that data into something queryable
3. Storage — warehousing the data in a system optimized for analytical queries
4. Presentation — exposing the right data to the right people in a dashboard or reporting tool

Each layer can be simple or complex depending on your data volume, latency requirements, and the number of source systems involved. A startup reporting on one PostgreSQL database is a different problem than an enterprise aggregating 40 SaaS tools with real-time alerting requirements. The same four layers apply — the implementation choices at each layer are what vary.

Ingestion: Getting Data Out of Where It Lives

The ingestion layer is often the most underestimated. Source systems are frequently not designed with data extraction in mind. APIs have rate limits. Databases have schemas that change without notice. Event streams need consumers. File exports from legacy systems arrive in unpredictable formats.

The two primary ingestion patterns are:

• Batch ingestion: Extract data on a schedule — hourly, nightly, weekly. Suitable when near-real-time is not required. Tools like Airbyte, Fivetran, or custom Python ETL scripts handle this well. Airbyte in particular has 300+ pre-built connectors for common SaaS tools (Salesforce, HubSpot, Stripe, PostgreSQL, MySQL) and can self-hosted or used as a cloud service.
• Streaming ingestion: Events are ingested as they occur. Required for real-time dashboards, fraud detection, or operational alerting. Kafka, AWS Kinesis, and Google Pub/Sub are the common streaming platforms. Streaming adds significant infrastructure complexity and is only worth it when batch latency genuinely blocks a business need.

Regardless of method, build for idempotency from day one. Your pipeline will fail and be retried. If re-running an ingestion job creates duplicate records rather than safely updating existing ones, you'll spend engineering time debugging data quality rather than adding value.

One thing to nail early: which column defines a record's identity? In CRM data it might be an email address or account ID. In event logs it's an event UUID. In transactional databases it's a primary key. Without a clear identity column, deduplication during ingestion retries becomes guesswork.

Transformation: From Raw to Usable

Raw data from source systems is almost never ready for analysis. Column names are cryptic, timestamps are in different timezones, NULL values have different semantics in different systems, currencies aren't normalized, and business concepts (what counts as a "customer," what qualifies as "revenue") need to be encoded explicitly.

The tool that has become standard for this layer is dbt (data build tool). dbt lets you write SQL transformation logic as models — individual SQL SELECT statements — that it compiles and runs against your data warehouse. The advantages:

• Transformations are version-controlled SQL, not buried in a GUI or a tangle of stored procedures
• dbt handles dependency ordering between models automatically — if model B reads from model A, dbt builds them in the right order
• Built-in testing (uniqueness, not-null, referential integrity) catches data quality issues before they hit dashboards
• dbt generates documentation from your models, making the transformation logic discoverable for non-engineers

The standard layering pattern in dbt is:

• Staging models: One-to-one with source tables, doing only renaming and light type casting. No business logic.
• Intermediate models: Joins and aggregations that build toward business concepts but aren't yet the final metric.
• Mart models: The final tables that dashboards and analysts query. Named for business concepts (fct_orders, dim_customers), not source system names.

This layering sounds like overhead when you start — it pays for itself the moment a source schema changes and you only need to update one staging model rather than 15 downstream queries.

Storage: Choosing the Right Warehouse

A data warehouse is a columnar, analytics-optimized database. It's designed for the kind of query that analytical workloads run: full table scans, GROUP BY aggregations, joins across large datasets. Row-oriented databases like PostgreSQL or MySQL are not designed for this and become painfully slow at the data volumes where analytical queries become interesting.

For teams just starting out, BigQuery's serverless model with on-demand pricing is usually the easiest entry point — you pay only for what you query, there's no cluster management, and the free tier (1 TB of query processing per month) covers a lot of experimentation. DuckDB is worth knowing about for smaller-scale or embedded analytics scenarios — it runs entirely in-process and can query Parquet files directly, eliminating the warehouse layer entirely for some use cases.

Orchestration: Making It Run Reliably

Something has to schedule ingestion jobs, trigger dbt runs after ingestion completes, retry failed tasks, and alert when things break. This orchestration layer is often absent in early pipeline implementations — replaced with a tangle of cron jobs and hope.

Apache Airflow is the most widely deployed option, with workflows defined as Directed Acyclic Graphs (DAGs) in Python. Managed versions (Google Cloud Composer, Astronomer, MWAA on AWS) remove the infrastructure burden. Prefect and Dagster are modern alternatives with better local development ergonomics. The minimum viable setup: one DAG per data source, dbt triggered after ingestion completes, Slack alerts for failures. That's achievable in a week and immediately eliminates silent failures causing stale dashboards.

The Presentation Layer: Dashboards That Actually Get Used

A common failure mode is a perfectly engineered pipeline feeding dashboards nobody opens. The presentation layer deserves as much attention as the technical layers — the reason dashboards go unused is almost always one of: the data is stale (people learn not to trust it), the metrics shown aren't the ones people make decisions from, or the dashboard is too complex to answer a specific question quickly.

Common BI tools and their fit: Metabase is open-source and accessible to non-technical users — a strong default for teams that want self-serve exploration without writing SQL. Looker / Looker Studio enforces consistent metric definitions across all dashboards, which matters in organizations where "revenue" can mean different things to different teams. Superset covers most commercial BI use cases at zero license cost if you're willing to self-host. Tableau is the strongest for complex executive reporting but carries the highest per-user cost.

The specific tool matters less than establishing metric definitions before building dashboards. Document what each metric means, how it's calculated, and which dbt model it comes from. When two people argue about a number in a meeting, the answer should be findable in 30 seconds.

Frequently Asked Questions

What's the difference between ETL and ELT?

ETL (Extract, Transform, Load) transforms data before loading it into the warehouse — common when warehouses were expensive and storage was constrained. ELT (Extract, Load, Transform) loads raw data first and transforms it inside the warehouse using dbt or similar tools. ELT is now the dominant pattern because modern cloud warehouses make storage cheap and compute powerful enough to transform at scale inside the warehouse, while keeping raw data available for reprocessing.

Do I need a data lake as well as a data warehouse?

Not for most companies starting out. A data lake becomes valuable when storing very large raw volumes before processing, working with unstructured data (documents, images, logs), or building ML pipelines. For business intelligence and reporting, a warehouse-only architecture is simpler and sufficient until data volume or use case complexity demands more.

How long does it take to build a production-ready data pipeline?

A focused team with clear requirements can have ingestion from 2–3 sources, basic dbt transformations, a managed warehouse, and 5–10 key metrics in a dashboard within 4–6 weeks. The timeline expands with the number of source systems and the degree of data quality problems in those sources. The first iteration is never the last — expect ongoing refinement as the business asks new questions.

What skills does my team need to build and maintain a data pipeline?

Core skills: SQL for dbt, Python for ingestion scripts and orchestration, basic cloud IAM and storage knowledge, and familiarity with one orchestration tool. Data engineering as a full specialty is valuable at scale, but many mid-size companies run their pipelines successfully with a backend engineer interested in data. Outsourcing the initial build to a team like Mexilet Technologies and then handing off maintenance internally is a pattern that works well for companies without dedicated data engineering resources.

Need a partner for this? Mexilet offers data engineering services and AI & analytics.

If you're at the "we have data everywhere but can't actually answer basic business questions from it" stage, the most de-risked way to move forward is a small paid pilot: pick one to two source systems, define three to five specific metrics the business actually cares about, and build the pipeline end-to-end for those. You'll learn more from four weeks of real implementation than from months of planning. Reach out to the Mexilet data engineering team to scope a trial sprint — we'll define the deliverables, timeline, and cost upfront so there are no surprises.