Data Engineering Basics for Developers: Pipelines, Warehouses, and Streaming

The Report That Ran for 14 Hours

A data analyst at an e-commerce company spent a week building a report that joined customer, order, and product data across three databases. On Monday, it worked — for 10,000 customers. By Friday, the company had a flash sale, and the customer table grew to 2 million rows. The query that used to take 20 minutes now took 14 hours. The analyst's colleagues asked why the report was so slow. The analyst said: "the data's bigger now."

The data was bigger — but the real problem was that no one had built a pipeline. Every query was hammering the production databases directly, joining across transactional systems that were designed for writes, not analysis. The data lived in silos with no staging area, no aggregation, and no warehouse. The report was slow not because there was more data, but because the data engineering was missing[fowler-data-mesh].

This guide explains what every developer needs to know about data engineering: how data flows from source systems into analytics, the difference between ETL and ELT, warehouses versus lakes, batch versus streaming, and the modeling patterns that make analytical queries fast. The goal is not to make you a data engineer — it is to make you a developer who understands why the analytics are slow and what infrastructure would fix it.

The Data Pipeline: Where Data Comes From and Where It Goes

Every organization has data sources — transactional databases, API logs, clickstream events, IoT sensors, payment processors. And every organization has data consumers — dashboards, machine learning models, business analysts, reporting tools. A data pipeline is the plumbing that moves data from sources to consumers[wikipedia-etl].

A naive approach is to let consumers query the source systems directly. This fails for three reasons:

1. Performance. Analytical queries (aggregations, joins across millions of rows) compete with transactional queries (inserts, updates) for the same database resources. Production systems slow down.
2. Coupling. Every consumer queries the source schema directly. When the schema changes, every report breaks.
3. History. Transactional databases keep only current state. When an order is updated, the previous version is gone. Analytics needs history.

The pipeline solves all three: it extracts data from sources, transforms it into an analytical shape, and loads it into a dedicated store designed for queries[wikipedia-etl].

ETL vs ELT: Where Transformation Happens

The two classic pipeline architectures differ in where the transformation step runs.

ETL (Extract, Transform, Load) transforms data before loading it into the target store. Data is extracted from sources, cleaned and reshaped in a staging area, then loaded into the warehouse in its final analytical form[wikipedia-etl].

ELT (Extract, Load, Transform) loads raw data into the target store first, then transforms it in place. This became popular with cloud warehouses (BigQuery, Snowflake, Redshift) that can handle massive transformations with elastic compute[google-bigquery].

The trend is toward ELT because cloud warehouses make in-warehouse transformation cheap and flexible. But ETL still wins when you need to clean data before storing it — for example, masking PII or dropping malformed records before they enter the warehouse[wikipedia-etl].

Warehouses vs Lakes: The Storage Decision

Data warehouses store structured, transformed data optimized for analytical queries. They use columnar storage (data stored by column, not row) which makes aggregations over millions of rows fast. Snowflake, BigQuery, Amazon Redshift, and ClickHouse are warehouses[google-bigquery].

Data lakes store raw data in its native format — JSON, CSV, Parquet, images, even video — without transformation. They're cheap (object storage like S3) and flexible (you can store anything and figure out the schema later). The tradeoff: querying raw data is slower and messier[aws-data-lake].

A common modern architecture is the lakehouse: a data lake with warehouse-like query performance. Tools like Databricks and Dremio add a query engine on top of the lake, giving you the flexibility of a lake with the query speed of a warehouse[fowler-data-mesh].

The rule of thumb: store processed, queried data in a warehouse. Store raw, exploratory data in a lake. If you need both, consider a lakehouse.

Batch vs Streaming: When Data Arrives

Batch processing handles data in chunks — hourly, daily, or weekly. A nightly job that extracts the day's orders, transforms them, and loads them into the warehouse is a batch pipeline. Batch is simple, cost-efficient, and works well when data doesn't need to be current[wikipedia-etl].

Streaming processes data continuously as it arrives — a click on the website, a sensor reading, a payment. Tools like Apache Kafka and Amazon Kinesis handle streams of events. Streaming is complex but enables real-time dashboards, fraud detection, and recommendation systems that react to events in milliseconds[kafka-intro].

The classic pattern — the lambda architecture — runs both: a batch layer processes historical data (correct but slow), a speed layer processes real-time data (fast but approximate), and the results are merged. The more modern kappa architecture runs everything through streaming, using the stream as the single source of truth[fowler-data-mesh].

When to use each:

• Batch: daily reports, monthly analytics, anything where 24-hour freshness is acceptable
• Streaming: fraud detection, live dashboards, alerting, anything where latency matters
• Lambda (both): systems that need both correctness (batch) and freshness (streaming)

Dimensional Modeling: Making Queries Fast

Once data is in a warehouse, the schema design determines whether queries are fast. Dimensional modeling (Kimball) is the dominant approach: separate facts from dimensions[kimball-dimensional].

A fact table stores measurements — events, transactions, quantities. It's typically huge (millions of rows) and sparse: it has foreign keys to dimensions and a few numeric measures. A dimension table stores descriptive attributes — customers, products, dates, locations. It's smaller and denormalized: one row per entity with all its attributes[kimball-dimensional].

Example — a sales fact table:

The date_key, customer_key, and product_key link to dimension tables. The quantity and revenue are measures. This design makes the classic analytical query — "total revenue by product for July" — a simple JOIN + GROUP BY over a few tables, which columnar storage executes in milliseconds[kimball-dimensional].

Star schema (the most common dimensional model) has one fact table in the center connected to multiple dimension tables — shaped like a star. It's denormalized for query speed, trading storage efficiency for fast reads. The SQL Index Selectivity Calculator helps you reason about whether the indexes on these join columns will actually be used as the fact table grows to millions of rows.

Slowly changing dimensions add a subtle but critical detail: customer attributes change over time, and analytics often needs to know what a customer's region was at the time of the sale, not today. The standard solutions are Type 1 (overwrite the attribute, losing history), Type 2 (add a new row with start/end dates, preserving history), and Type 3 (keep both current and original values). Most analytical teams converge on Type 2 for the dimensions that matter — the extra storage is worth the historical accuracy, and it is what makes "revenue by region as of the sale date" queries return correct answers months later when the sales team reorganizes territories[kimball-dimensional].

The Data Pipeline in Practice

Let's trace a real pipeline for an e-commerce company:

1. Sources: the orders database (PostgreSQL), the website clickstream (Kafka events), the payment processor (API).
2. Extract: a nightly job reads new orders from PostgreSQL (batch); a Kafka consumer reads clickstream events in real time (streaming)[kafka-intro].
3. Load: raw order data lands in the lake as Parquet; clickstream events land in a raw Kafka topic.
4. Transform: the lake data is cleaned (remove test orders, normalize country codes), joined with dimension tables, and loaded into the warehouse.
5. Model: the warehouse schema uses dimensional modeling — a fact_sales table joined to dim_customer, dim_product, dim_date.
6. Consume: dashboards query the warehouse; ML models read aggregates from a feature store.

Where each calculator fits:

• The Bandwidth Calculator sizes the network: if you're moving 50 GB of Parquet per night over a 1 Gbps connection, that's ~7 minutes of pure transfer — but with Kafka streaming at 10,000 events/second, the sustained throughput matters more.
• The SQL Index Selectivity Calculator tells you whether the join columns in your warehouse queries are selective enough for indexes to help.
• The Token Counter Calculator estimates the token cost if you're feeding warehouse data to an LLM for analysis — a growing pattern in modern data stacks.
• The Big Number Calculator handles the astronomical counts in event volumes (billions of events per day) without overflow.
• The Mean Median Mode Range Calculator and Standard Deviation Calculator power the basic descriptive analytics that dashboards show.

Practical Tips for Data Engineering

1. Never let consumers query source systems directly. Every analytical query against a production database is a slow query stealing resources from real users. Always stage data into a warehouse or lake.
2. Idempotent pipelines. A pipeline should be re-runnable without duplicating data. Use a watermark (last processed timestamp) or a key-based dedup (unique constraint on the natural key).
3. Backfill is inevitable. You will need to reprocess old data when a transformation bug is fixed or a new dimension is added. Design pipelines to be backfillable from raw source data — that's why storing raw data (lake/ELT) matters.
4. Monitor pipeline health. Track lag (how far behind the source the pipeline is), error rates per stage, and data volume. A silent pipeline that stops extracting for a week is worse than a pipeline that fails loudly.
5. Schema evolution is a contract. When you change a data schema, downstream consumers break. Version your schemas (Avro/Protobuf with compatibility rules) and communicate changes before shipping.
6. Store raw data forever. Even if you don't use it today, raw data is the insurance policy for future questions you can't yet imagine. Processed/aggregated data can be derived; raw data cannot be reconstructed.
7. Test with realistic data volumes. A pipeline that works with 1,000 rows may fail with 10 million. Test with a large sample and monitor the query plan with tools like BigQuery's query plan or EXPLAIN.

Limitations and When Data Engineering Is Overkill

Data engineering adds complexity: pipelines to run, storage to pay for, schemas to maintain. It's not always the right answer:

• Small data. If your company has 100,000 rows and two analysts, a warehouse is overkill. A well-indexed database and a few SQL views handle it.
• One-off analysis. A single report that needs data from two tables doesn't justify a pipeline. Export, join, analyze, and move on.
• Low query frequency. If a report runs once a month, nightly batch is fine — you don't need streaming.
• Tight coupling to a single source. If all your data comes from one system and one team consumes it, a direct connection with caching may suffice.

The tipping point is when multiple teams need analytical access to data or data volume exceeds what transactional databases can handle for queries. That's when the pipeline, warehouse, and modeling investment pays off.

Frequently Asked Questions

❓ What is the difference between ETL and ELT?

✅ ETL transforms data before loading it into the target store. ELT loads raw data first and transforms it inside the warehouse. ELT is more popular with cloud warehouses (BigQuery, Snowflake) because elastic compute makes in-warehouse transformation cheap and flexible.

❓ What is a data warehouse vs a data lake?

✅ A warehouse stores structured, transformed data optimized for analytical queries (columnar storage, fast aggregations). A lake stores raw data in any format (JSON, Parquet, video) cheaply. A lakehouse adds warehouse-style query performance on top of a lake.

❓ When should I use streaming instead of batch?

✅ Use batch for anything where 24-hour freshness is acceptable (daily reports, monthly analytics). Use streaming for real-time needs: fraud detection, live dashboards, alerting. Kafka is the most common streaming platform.

❓ What is a star schema?

✅ A dimensional model with one central fact table (measurements/events) connected to dimension tables (customers, products, dates) — shaped like a star. It's denormalized for query speed, making JOIN + GROUP BY queries fast on columnar warehouses.

❓ Why can't I query the production database directly?

✅ Analytical queries compete with transactional queries for the same resources, slowing down production. Direct queries also couple consumers to source schemas (breaking on schema changes) and lose history (transactional databases keep only current state).

❓ What is Kafka?

✅ Apache Kafka is a distributed event streaming platform. Applications publish events to topics; consumers subscribe and process them in real time. It's the standard for streaming data — clickstreams, logs, IoT events, and as the backbone of kappa architectures.

❓ How do I backfill a data pipeline?

✅ Store raw source data permanently, then reprocess from the raw data when you need to fix a transformation bug or add a dimension. Design pipelines to be idempotent (re-runnable without duplicating data) using watermarks or key-based dedup.

❓ Do I need a data warehouse?

✅ Not for small data or single-team analytics. The investment pays off when multiple teams need analytical access to data, or when query volume exceeds what transactional databases can handle. Start with well-indexed databases and SQL views; add a warehouse when queries slow down.