Data Engineering Guide: Build Reliable Pipelines, Streaming Systems, and Lakehouses

A typical architecture has five responsibilities: capture, durable storage, transformation, serving, and operational control. For our retailer, an order database records purchases, change data capture publishes committed changes, and object storage retains a replayable history. Transformations produce order-level and daily-sales datasets, which an analytical data warehouse serves to reporting tools. Monitoring, access controls, and lineage span every layer. Separate these responsibilities conceptually even when one product implements several of them; that makes bottlenecks and ownership easier to identify.

Start with measurable requirements. Must revenue appear within seconds, or is tomorrow morning sufficient? Can reports temporarily omit delayed payments? How far back must corrections propagate? These answers determine whether data pipelines should run in batches, continuously, or both. The ETL vs ELT distinction describes where transformation occurs: before loading into a target system, or afterward using its compute engine. Neither pattern is universally better. Sensitive fields may require filtering before ingestion, while warehouse-native transformations can simplify analytical development.

Data modeling begins by stating exactly what one row represents. An operational design might separate orders, order_items, customers, and payments, using primary and foreign keys to express relationships. For analytics, define fact_order_item as one row per order line, with dimensions for customer, product, and purchase date. Measures must match that grain: quantity and line revenue belong there, but an order-level shipping charge cannot simply be repeated and summed across every line. Either allocate it by a documented rule or keep it in a separate order-grain fact table.

Suppose order 501 contains two units at $30 and one unit at $40. The lines contribute $60 and $40, so merchandise revenue is $100. Joining those two lines directly to two payment attempts creates four rows and can incorrectly report $200. Aggregate or deduplicate payments to the required grain before joining. Use fixed-precision decimal types for monetary values and document currency, refund, discount, and tax treatment. For historical customer attributes, a type-2 dimension records validity intervals; facts join to the version valid at the relevant business time, not automatically to today’s customer record.

Distributed systems divide work across machines, but communication and partial failure make that division expensive. Apache Spark represents a computation as a graph of transformations and executes tasks over partitions. Operations such as filtering can usually run independently within each partition. Grouping or joining by a key often requires a shuffle, which redistributes records across the network. A job reading substantial input can still perform well when it scans efficiently; a smaller job may struggle if one popular key sends most records to a single task.

For a daily-sales batch, Spark SQL could run: SELECT sale_date, SUM(quantity * unit_price) AS revenue FROM order_lines GROUP BY sale_date. Before this aggregation, establish unique order-line records, exclude invalid business states, and define how sale_date is derived from timestamps and time zones. Inspect the execution plan, shuffle volume, task duration distribution, and spill metrics. Broadcast a dimension only when it safely fits the relevant memory limits, and investigate skew before adding workers. Spark can retry failed tasks, so external side effects inside task code must tolerate repetition; writing an email or issuing a payment from each task is unsafe without additional coordination.

Apache Kafka stores events in partitioned logs. Ordering is guaranteed within a partition, not across an entire multi-partition topic. Keying order events by order_id lets related records reach the same partition under a consistent partitioning scheme. Within a conventional consumer group, each partition is assigned to at most one consumer at a time, so useful consumer parallelism is bounded by partition count. Retention preserves events for replay, while consumer offsets record progress. Crucially, an offset says what was consumed; it does not independently prove that an external database update succeeded.

Consider a payment with event time 10:03 that arrives at 10:11. Stream processing based only on arrival time can place it in the wrong reporting window. Event-time windows and watermarks let the engine track progress and bound how long it retains state, although exact late-data behavior depends on the engine and configuration. Define whether late records update prior results, enter a correction path, or are rejected. Deduplicate using stable event identifiers with an explicit retention horizon. Kafka transactions support atomic processing patterns within Kafka, but end-to-end exactly-once effects require compatible source, processing, and sink behavior. For external sinks, idempotent writes are often the practical foundation.

An analytical data warehouse is designed for large scans, joins, aggregations, and concurrent analytical queries. Columnar storage reads selected columns efficiently, while pruning can skip irrelevant storage regions. A data lakehouse combines object-storage data files with a table layer that manages metadata and transactional changes. Formats such as Apache Iceberg and Delta Lake provide capabilities including snapshots and schema evolution, but support differs by engine and version. A folder of Parquet files alone is not equivalent to a transactionally managed table. Choose based on query patterns, governance, interoperability, and operational effort rather than labels.

For the retailer, keep an access-controlled raw layer for replay, a validated layer with normalized types and deduplicated records, and curated tables with agreed business definitions. Partition large order tables by an appropriate date field when queries commonly filter by date; avoid partitions per customer when that creates excessive fragmentation. Streaming writes can generate many small files, increasing planning and read overhead, so schedule compaction where supported. Snapshot expiration and file cleanup require retention policies that preserve active readers and recovery needs. Neither a warehouse nor a lakehouse eliminates the need to model data carefully or test results.

Workflow orchestration coordinates dependencies, schedules, retries, and backfills. An orchestrator such as Apache Airflow can launch Spark jobs, run warehouse SQL, and wait for upstream completion; it does not replace those execution engines. Model a daily pipeline as explicit stages: capture completion, raw validation, transformation, reconciliation, and publication. Each run should identify its logical data interval rather than infer inputs from the current clock. That distinction allows the same code to rebuild a historical day without accidentally reading today’s files or overwriting unrelated outputs.

Make retries safe by designing idempotent outputs. For example, calculate the complete sales aggregate for a date into staging, validate it, then publish using an atomic operation supported by the destination. For mutable orders, a merge keyed by order-line identifier should also compare source versions so an old replay cannot overwrite newer state. Test schema compatibility, required fields, key uniqueness, and business invariants, such as refunded quantity not exceeding purchased quantity under the agreed rules. Reconcile totals against trusted sources. A successful task exit is not evidence of correct data, and quarantined records need an owner and a resolution process.

Petabyte-scale infrastructure depends on controlling bytes scanned, network movement, metadata growth, and recovery work—not just provisioning more machines. Estimate throughput from workload assumptions: at 20,000 events per second and an average payload of 1 kilobyte, incoming payload is about 20 megabytes per second, or 1.728 terabytes per day in decimal units. That excludes compression, replication, protocol overhead, and derived datasets. Use estimates to plan tests, then measure actual workloads, including bursts and downstream slowdowns. Size retention and replay capacity so recovery can catch up rather than merely match incoming traffic.

Track freshness, consumer lag, throughput, error rates, rejected records, and end-to-end reconciliation, with alerts tied to user impact. Test worker loss, duplicate delivery, unavailable sinks, and incompatible schema changes. Apply least-privilege access, encryption, secret management, and retention rules throughout the architecture; raw storage should not become an uncontrolled archive of personal data. A strong course project integrates these practices into one reproducible system: ingest orders, transform them with Spark, serve curated results, and demonstrate recovery after a deliberately introduced failure. That provides stronger evidence of engineering competence than a collection of disconnected tool demos.