Tauseef FayyazA to Z
Glossary
Every concept in this sheet, plus the smaller terms it uses. Select a concept to open its card.
A
- API ingestion
- Pulling data from an HTTP API while handling pagination, rate limits (HTTP 429), authentication and retries with exponential backoff.
B
- Batch and streaming
- Batch processes data in chunks on a schedule, such as yesterday's orders every night.
C
- Change data capture
- Reading 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.
- Common table expression
- A named subquery defined with WITH at the start of a query, so a long transformation reads as a series of steps.
D
- Data engineer
- An 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 scientists and products.
- Data engineering lifecycle
- The stages data passes through: generation in source systems, ingestion, storage, transformation and serving, with security, quality, orchestration and cost underneath all of them.
- Deduplication
- Keeping one row per business key, usually the latest version, by numbering rows per key and keeping row 1.
- Dimension table
- A table describing the context of facts: who, what, where and when, such as customer, product, store and date, with descriptive attributes.
- DuckDB
- A 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.
E
- ETL and ELT
- ETL transforms data before loading it into the warehouse.
F
- Fact table
- A table of events or measurements at a declared grain, holding foreign keys to dimensions and numeric measures such as quantity and amount.
- Full and incremental loads
- A full load copies the whole source table each run.
G
- Grain
- Exactly what one row of a fact table represents, stated in one sentence before any columns are chosen.
I
- Idempotent pipeline
- A 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 INSERT.
J
- Join fan-out
- When a join matches one row to several rows on the other side, multiplying rows and inflating sums.
K
- Kimball, Inmon and data vault
- Modelling styles: Kimball builds star schemas per business process; Inmon builds a normalised enterprise warehouse first; data vault splits data into hubs, links and satellites; one big table is a wide denormalised table per use case.
O
- OLTP and OLAP
- OLTP systems run applications with many small reads and writes on normalised tables.
P
- Python for pipelines
- Python written to run unattended: small functions, configuration from environment variables, logging, clear exceptions, pinned dependencies and a non-zero exit code on failure.
S
- Slowly changing dimension
- Ways to handle dimension attributes that change.
- Star and snowflake schemas
- A star schema has one fact table joined directly to denormalised dimension tables.
- Surrogate key
- A meaningless key, an integer or a hash, created in the warehouse to identify a dimension row, separate from the source system's natural key.
W
- Window functions
- Functions that compute a value for each row from a related set of rows, defined with OVER (PARTITION BY ...