Skip to main content

Glossary

Terms used throughout The Data Foundry, defined once here for reference. Where a term is covered in depth in a specific chapter, that chapter is noted.

A​

Aggregation The process of computing a summary value (count, sum, average, minimum, maximum) across a group of rows. Covered in Chapter 7.

Airflow Apache Airflow. An open-source workflow orchestration platform for scheduling, monitoring, and managing data pipelines as directed acyclic graphs (DAGs). Covered in Part 3.

Anti-join A join pattern that returns rows from one table with no matching rows in another. Implemented with LEFT JOIN ... WHERE key IS NULL or NOT EXISTS. Covered in Chapter 4.

Artifact Any file or object produced by a pipeline step: a cleaned CSV, a trained model, a dashboard snapshot.

B​

Batch processing Processing data in scheduled, finite groups rather than continuously. Contrasts with streaming.

BikeStores The sample database used throughout Part 1 of this book. A retail database for a fictional bicycle shop chain, available from sqlservertutorial.net.

C​

CTE (Common Table Expression) A named, temporary result set defined at the start of a SQL query with WITH expression_name AS (...). Readable and reusable within a single statement. Covered in Chapter 9.

Change Data Capture (CDC) A pattern for detecting and propagating changes (inserts, updates, deletes) from a source system to a downstream system in near real-time.

Columnar storage A database storage format that stores each column's data contiguously rather than row by row. Used by Snowflake, BigQuery, Redshift. Enables faster analytical queries and better compression.

Connection In Airflow, a named set of credentials for connecting to an external service (database, API, cloud storage), stored in the metadata database and referenced by operators by ID.

Correlated subquery A subquery that references columns from the outer query and is re-executed for each row the outer query processes. Covered in Chapter 8.

D​

DAG (Directed Acyclic Graph) The structure Airflow uses to represent a workflow. Tasks are nodes; dependencies between tasks are directed edges. Acyclic means no task can transitively depend on itself.

Data lake A storage system that holds raw data in its native format at scale. Often S3 or Azure Data Lake Storage. No schema enforced at write time.

Data warehouse A structured, analytics-optimized storage system. Schema enforced. Query-optimized. Examples: Snowflake, BigQuery, Redshift, Synapse Analytics.

dbt (data build tool) A transformation framework that lets data engineers define SQL models, test them, document them, and run them as part of a managed transformation pipeline. Covered in Part 6.

Denormalization Deliberately combining data that would be split across multiple tables in a normalized schema into fewer, wider tables to reduce join complexity for analytical queries.

Derived table A subquery in the FROM clause of a SQL statement, treated as a temporary virtual table. Must be given an alias. Covered in Chapter 8.

Dimension table In dimensional modeling, a table that stores descriptive attributes (who, what, where, when) that provide context for facts. Covered in Chapter 29.

Docker A platform for packaging applications and their dependencies into portable containers that run consistently across environments. Covered in Part 4.

E​

ELT (Extract, Load, Transform) A data pipeline pattern where raw data is loaded into the target system first, then transformed inside it. Contrasts with ETL. Common in cloud warehouses.

ETL (Extract, Transform, Load) A data pipeline pattern where data is extracted from a source, transformed before loading, then loaded into the target. Contrasts with ELT.

Executor The Airflow component that determines how tasks are run: sequentially, in parallel on one machine, or distributed across multiple workers.

F​

Fact table In dimensional modeling, a table that stores measurable events (sales, transactions, page views) with foreign keys to dimension tables. Covered in Chapter 29.

Fan-out The row multiplication that occurs when joining a one-to-many relationship. A single order row joined to its ten order items produces ten rows for that order. Covered in Chapter 4.

G​

GROUP BY A SQL clause that groups rows sharing the same value in one or more columns so that aggregate functions can compute per-group results. Covered in Chapter 7.

H​

HAVING A SQL clause that filters groups after aggregation. Contrasts with WHERE, which filters rows before grouping. Covered in Chapter 7.

I​

IDENTITY column A SQL Server column that auto-generates sequential integer values on insert. Equivalent to SERIAL in PostgreSQL or AUTO_INCREMENT in MySQL.

Index A database object that speeds up data retrieval at the cost of additional storage and slower writes. Created on columns that are frequently searched or joined.

J​

JOIN A SQL operation that combines rows from two tables based on a matching condition. Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS. Covered in Chapter 4.

K​

Kafka Apache Kafka. A distributed event streaming platform used for real-time data pipelines. Covered in Part 7.

L​

LAG / LEAD SQL window functions that access the value of a previous or following row within a result set without a self-join. Covered in Chapter 10.

M​

Medallion Architecture A data organization pattern with three layers: Bronze (raw ingestion), Silver (cleaned and conformed), Gold (aggregated, business-ready). Common in Databricks and Azure environments.

MERGE A SQL statement that combines INSERT, UPDATE, and DELETE against a target table based on matching conditions with a source. The standard upsert operation for ETL loads. Covered in Chapter 12.

Metadata database The central database Airflow uses to store all state: DAG definitions, run history, task states, logs, connections, and variables.

N​

Normalization Organizing a relational database to reduce redundancy by splitting data into related tables and using foreign keys. Contrasts with denormalization.

NULL A SQL value representing unknown or missing data. Not equal to zero, empty string, or false. Requires IS NULL or IS NOT NULL for comparison because standard comparisons always evaluate to UNKNOWN when one operand is NULL.

O​

OLAP (Online Analytical Processing) Database systems optimized for complex analytical queries across large volumes of historical data. Examples: Snowflake, BigQuery, Redshift.

OLTP (Online Transaction Processing) Database systems optimized for fast, concurrent reads and writes on small numbers of rows. Examples: PostgreSQL, MySQL, SQL Server.

Operator In Airflow, the class that defines what a task does. PythonOperator runs a Python function. BashOperator runs a shell command. PostgresOperator runs a SQL query.

P​

Partition In databases: dividing a large table into smaller physical segments for performance. In Kafka: a division of a topic that enables parallel processing. In window functions: a grouping of rows within which the window function resets.

Pipeline A sequence of data processing steps connected so the output of one becomes the input of the next.

R​

ROW_NUMBER() A SQL window function that assigns a sequential integer to each row within a partition. Covered in Chapter 10.

S​

SCD (Slowly Changing Dimension) A set of strategies for handling changes to dimension attributes over time in a data warehouse. Types 0 through 4 covered in Chapter 30.

Schema In SQL: a namespace that groups related database objects. In data modeling: the structure of a table or database (column names, types, constraints).

Scheduler The Airflow component that monitors DAGs, determines which tasks are due to run, and queues them for execution by the Executor.

Snowflake A cloud data warehousing platform. Also a schema design pattern where dimension tables are normalized into hierarchies. Covered in Part 6.

Star schema A dimensional modeling pattern with a central fact table surrounded by denormalized dimension tables. Covered in Chapter 29.

Stored procedure A named, compiled collection of SQL statements stored in the database and executed on demand with EXEC. Covered in Chapter 13.

Stream In Kafka: an ordered, replayable sequence of events. In Snowflake: a change tracking object that captures inserts, updates, and deletes on a table since the last time the stream was consumed.

Subquery A SELECT statement nested inside another SQL statement. Can appear in WHERE, FROM, or SELECT. Covered in Chapter 8.

T​

Task A single unit of work in an Airflow DAG. Each task is an instance of an Operator with specific configuration.

Trigger A mechanism that starts an action automatically when a condition is met. In Airflow: an event that starts a DAG run. In SQL: a stored procedure that fires automatically in response to an INSERT, UPDATE, or DELETE.

V​

Variable In Airflow: a key-value pair stored in the metadata database and accessible from DAG code. In SQL: a named storage location declared with DECLARE inside a procedure or batch.

View A saved SQL SELECT statement that can be queried like a table. Stores no data; re-executes the underlying query each time it is accessed. Covered in Chapter 5.

W​

Window function A SQL function that computes a value for each row based on a set of related rows (the window) without collapsing them into a single result. Defined with OVER(). Covered in Chapter 10.

Worker The Airflow process that executes task code. Workers receive tasks from the Executor and write results back to the metadata database.