Data engineering cheat sheet (Azure)

1. 1Foundations and ingestionWhat data engineers do, the lifecycle every platform follows, the main pipeline patterns and how to pull data from sources safely.10 concepts

   1. 1Data engineerAn engineer who builds and runs the systems that collect data from sources, store it, transform it into reliable tables and serve it to analysts, data…
   2. 2Data engineering lifecycleThe stages data passes through: generation in source systems, ingestion, storage, transformation and serving, with security, quality, orchestration and cost…
   3. 3Batch and streamingBatch processes data in chunks on a schedule, such as yesterday's orders every night.
   4. 4ETL and ELTETL transforms data before loading it into the warehouse.
   5. 5Full and incremental loadsA full load copies the whole source table each run.
   6. 6Change data captureReading a database's own change log, such as the PostgreSQL write-ahead log or MySQL binlog, to capture every insert, update and delete in order.
   7. 7API ingestionPulling data from an HTTP API while handling pagination, rate limits (HTTP 429), authentication and retries with exponential backoff.
   8. 8Python for pipelinesPython written to run unattended: small functions, configuration from environment variables, logging, clear exceptions, pinned dependencies and a non-zero exit…
   9. 9DuckDBA small, in-process analytical database that runs SQL directly over CSV, JSON and Parquet files on your laptop, much faster than pandas on larger data.
   10. 10Idempotent pipelineA job that produces the same result whether it runs once or many times for the same input, for example by overwriting a date partition or using MERGE instead of…

   10 in this chapterOpen this chapter
2. 2Analytical SQL and data modellingThe SQL patterns pipelines rely on and the dimensional modelling ideas that turn raw tables into a warehouse people can trust.12 concepts

   1. 1Common table expressionA named subquery defined with WITH at the start of a query, so a long transformation reads as a series of steps.
   2. 2Join fan-outWhen a join matches one row to several rows on the other side, multiplying rows and inflating sums.
   3. 3Window functionsFunctions that compute a value for each row from a related set of rows, defined with OVER (PARTITION BY ...
   4. 4DeduplicationKeeping one row per business key, usually the latest version, by numbering rows per key and keeping row 1.
   5. 5OLTP and OLAPOLTP systems run applications with many small reads and writes on normalised tables.
   6. 6Fact tableA table of events or measurements at a declared grain, holding foreign keys to dimensions and numeric measures such as quantity and amount.
   7. 7Dimension tableA table describing the context of facts: who, what, where and when, such as customer, product, store and date, with descriptive attributes.
   8. 8GrainExactly what one row of a fact table represents, stated in one sentence before any columns are chosen.
   9. 9Star and snowflake schemasA star schema has one fact table joined directly to denormalised dimension tables.
   10. 10Surrogate keyA meaningless key, an integer or a hash, created in the warehouse to identify a dimension row, separate from the source system's natural key.
   11. 11Slowly changing dimensionWays to handle dimension attributes that change.
   12. 12Kimball, Inmon and data vaultModelling styles: Kimball builds star schemas per business process; Inmon builds a normalised enterprise warehouse first; data vault splits data into hubs…

   12 in this chapterOpen this chapter
3. File formats, lakes and lakehousesHow analytical data is laid out on disk and in object storage, and the difference between lakes, warehouses and lakehouses.11 conceptsOpens with credits
4. Batch processing with SparkHow Apache Spark splits work across a cluster, the PySpark API, the few performance ideas that matter and how to write batch output safely.6 conceptsOpens with credits
5. Orchestration with AirflowRunning pipeline steps in order, on schedule, with retries, backfills and alerts, using Apache Airflow as the example.6 conceptsOpens with credits
6. Transformation with dbtBuilding warehouse models as tested, documented SQL with dbt: models, materialisations, tests, incremental models and snapshots.5 conceptsOpens with credits
7. Your cloud data platformThe building blocks every cloud offers for data: object storage, a catalogue, a warehouse, managed Spark, access control, secrets and cost control.7 conceptsOpens with credits
8. Streaming basicsEvent logs, Kafka's core ideas, delivery semantics, event time and windows, and the managed streaming service on your cloud.10 conceptsOpens with credits
9. Data quality and governanceKeeping data trustworthy and safe: quality checks, contracts, observability, lineage and handling personal data.6 conceptsOpens with credits
10. Infrastructure, CI and careerMaking pipelines repeatable with containers, Terraform and CI, and the cloud certifications that data engineering roles mention.4 conceptsOpens with credits
11. 11Glossary, A to ZEvery concept in the sheet and the smaller terms it uses, in one list.197 terms

Pipelines, warehouses, Spark, dbt.