Data engineering rests on a small set of foundational patterns for moving and storing data. The two dominant integration approaches are ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform). ETL transforms data in a staging area before loading it into a target warehouse, which suited earlier systems with limited compute. ELT reverses the order: raw data lands in the warehouse first and transformations run inside it, taking advantage of the elastic compute of modern cloud warehouses like Snowflake, BigQuery, and Redshift. A data pipeline is an automated series of steps that moves data from sources to a destination through ingestion, transformation, validation, and loading stages. Pipelines can run in batches on a schedule, in real time as events arrive, or in a hybrid of both, and are commonly orchestrated with tools like Apache Airflow or Prefect.
The choice of storage architecture underlies every pipeline. A data warehouse is a centralized, structured repository optimized for analytical querying and reporting, using a schema-on-write approach that enforces structure as data is loaded. A data lake stores vast amounts of raw data in its native format—structured, semi-structured, or unstructured—on systems like HDFS, Amazon S3, or Azure Data Lake Storage, applying structure only when data is read (schema-on-read). The lakehouse architecture combines the strengths of both by storing open-format data on cheap object storage while adding warehouse-like features such as ACID transactions, schema enforcement, and SQL analytics through technologies like Delta Lake, Apache Iceberg, and Apache Hudi. The distinction between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems reflects this division: OLTP systems optimize for many short, write-heavy operations, while OLAP systems are tuned for complex read-heavy analytical queries over historical data.
The choice between batch and stream processing depends on latency and data characteristics. Batch processing handles data in large, discrete chunks at scheduled intervals, suited for historical analytics and reporting. Stream processing handles data continuously in near-real-time as it arrives, suited for real-time dashboards, fraud detection, and event-driven systems. Distributed data systems face trade-offs governed by the CAP theorem, which states that a system can guarantee at most two of consistency, availability, and partition tolerance. Since partitions are unavoidable in practice, real systems usually choose between strong consistency (every read sees the latest write) and eventual consistency (replicas converge over time), with NoSQL databases like DynamoDB and Cassandra favoring availability and partition tolerance. The main NoSQL types are key-value stores (Redis, DynamoDB) for simple lookups, document stores (MongoDB) for flexible JSON-like documents, column-family stores (Cassandra, HBase) for wide-column reads, and graph databases (Neo4j) for relationship-heavy traversals. ACID—Atomicity, Consistency, Isolation, and Durability—remains the cornerstone of transactional guarantees, and primary keys uniquely identify rows while foreign keys enforce referential integrity across tables.