Data Engineering Glossary

A/B testing is a controlled experiment method that randomly splits users into two (or more) groups shown different variants of a product or page, then compares a target metric to determine which variant performs better.

ACID is an acronym describing four properties — Atomicity, Consistency, Isolation, and Durability — that guarantee database transactions are processed reliably.

Aggregate functions are SQL operations that perform calculations across multiple rows of data to produce a single summary value.

Aggregation refers to the process of combining multiple individual data values into a single summary value.

Artificial Intelligence (AI) refers to computer systems designed to perform tasks that typically require human intelligence.

Airbyte is an open-source data integration platform, available self-hosted or as a managed cloud service, that moves data from hundreds of sources into a destination using a large catalog of connectors.

The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.

Amazon Redshift is AWS's cloud data warehouse, using massively parallel processing (MPP) to run analytical SQL over large datasets, available as provisioned clusters or serverless.

Amazon S3 (Simple Storage Service) is a highly scalable, durable, and secure object storage service provided by Amazon Web Services (AWS).

An analytical database, also known as an Online Analytical Processing (OLAP) database, is designed to efficiently handle complex queries and data analysis…

Analyze is an SQL statement used in various database systems, including DuckDB, to gather statistics about tables and columns.

Apache Airflow is an open-source platform for programmatically authoring, scheduling, and monitoring workflows, where pipelines are defined as directed acyclic graphs (DAGs) in Python.

Apache Arrow is an open-source, cross-language development platform for in-memory columnar data.

Apache Flink is an open-source distributed engine for stateful stream processing, treating data as continuous streams with low-latency, event-at-a-time computation.

Apache Hadoop is an open-source framework for distributed storage and processing of very large datasets across clusters of commodity servers, built around HDFS for storage and MapReduce (or later, YARN-managed engines) for computation.

Apache Hive is a data warehouse system built on Hadoop that lets users query large datasets stored in HDFS or object storage using HiveQL, a SQL-like language, which Hive translates into distributed jobs.

Apache Iceberg is an open table format that adds ACID transactions, schema evolution, and time travel to large analytic tables stored as files in a data lake.

Apache Kafka is an open-source distributed event streaming platform built around a durable, partitioned log for publish-subscribe messaging at scale.

Apache Spark is an open-source distributed processing engine for large-scale data workloads, using in-memory computation across a cluster of machines.

Apache Superset is an open-source data exploration and visualization platform that empowers users to create interactive dashboards and perform ad-hoc…

An API - Application Programming Interface - is a set of rules and protocols that allows different software applications to communicate with each other.

Approximate aggregation refers to computing statistics like distinct counts, quantiles, or samples using algorithms that trade a small, bounded amount of accuracy for large gains in speed and memory efficiency.

Arrow Flight is a high-performance, gRPC-based protocol from the Apache Arrow project for transferring large columnar datasets between systems with minimal serialization overhead.

An ASOF JOIN matches each row in one table to the closest preceding (or following) row in another table based on an ordering column, commonly a timestamp -- a core building block for time-series analysis.

ATTACH and DETACH let a database session connect to (or disconnect from) additional database files at runtime, enabling cross-database queries without a separate connection.

Auto inference, in the context of data analytics and engineering, refers to the automatic detection and assignment of data types and structures by a database…

Apache Avro is a row-based binary data serialization format that stores a data schema alongside the data, widely used for message serialization in streaming systems like Kafka and for schema evolution in Hadoop-era pipelines.

A backfill is the process of re-running a pipeline over a range of historical data—typically to populate a new table, fix a bug, or apply changed logic to past periods.

Batch processing is the execution of data processing jobs on accumulated groups (batches) of records at scheduled intervals, rather than processing each record as it arrives.

The BETWEEN operator tests whether a value falls within an inclusive range, equivalent to combining two comparisons with AND.

A Bloom filter is a space-efficient probabilistic data structure used to test whether a value might be in a set, with no false negatives but a small, tunable rate of false positives.

A bridge table resolves a many-to-many relationship between a fact table and a dimension (or between two dimensions) by holding pairs of keys, such as multiple salespeople credited on a single deal.

Business Intelligence (BI) refers to the technologies, applications, and practices used to collect, integrate, analyze, and present business information.

DuckDB provides a C/C++ API, allowing developers to embed the database directly into C and C++ applications.

Cardinality refers to the number of distinct values in a column or dataset, or, in data modeling, the nature of a relationship between two tables (such as one-to-many).

The CASE expression evaluates a list of conditions and returns a corresponding value for the first one that's true, functioning as SQL's if/else logic inside a query.

CAST converts a value from one data type to another, such as turning a string into an integer or a timestamp into a date.

Change Data Capture (CDC) is a technique for identifying and streaming row-level inserts, updates, and deletes from a source database as they happen, rather than repeatedly re-reading the whole table.

A chart is a visual representation of data that allows for quick interpretation and analysis.

A CHECK constraint is a rule attached to a table column (or the table as a whole) that requires every row to satisfy a given boolean expression.

The CLI, or Command-Line Interface, is a text-based interface used to interact with computer programs and operating systems through typed commands.

ClickHouse is an open-source, column-oriented OLAP database built for fast analytical queries over very large datasets.

Cloud storage refers to a model of data storage where digital information is kept on remote servers accessed through the internet, rather than on local hard…

COALESCE returns the first non-NULL value from a list of expressions, commonly used to supply default values or merge multiple possibly-NULL columns.

Cohort analysis groups users or entities by a shared starting event — typically signup date — and tracks how their behavior (retention, spend, activity) evolves over time relative to that starting point.

A column represents a single field or attribute in a database table or DataFrame that contains values of the same data type.

Columnar storage is a data layout that groups values from the same column together on disk, rather than storing complete rows contiguously, which speeds up analytical scans and compression.

The command line, also known as the terminal or shell, is a text-based interface for interacting with a computer's operating system.

A Common Table Expression (CTE) is a temporary named result set within a SQL query. Learn basic CTEs, chaining multiple CTEs, and recursive CTEs for hierarchical data.

A composite key is a primary key made up of two or more columns whose combined values uniquely identify a row, used when no single column is unique on its own.

Compression codecs are algorithms used to reduce the size of data on disk or over the network — common examples include Snappy, Gzip, Zstandard (zstd), and LZ4 — trading off compression ratio against CPU cost and speed.

Concurrency control is the set of techniques a database uses to let multiple transactions execute at the same time without violating data consistency or isolation guarantees.

A conformed dimension is a dimension table — like a shared date or customer dimension — that is defined identically and reused across multiple fact tables or data marts, so metrics stay comparable across the warehouse.

connectorx is a Rust-based Python library for loading data from SQL databases into DataFrames (pandas, Arrow, Polars) as fast as possible by parallelizing extraction and avoiding unnecessary data copies.

The COPY statement bulk-transfers data between a table and an external file, exporting query results to formats like CSV or Parquet, or loading files directly into a table.

A cost-based optimizer chooses among logically equivalent query plans by estimating the execution cost of each one, using statistics about table sizes and data distributions, and picking the cheapest estimate.

CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.

The CREATE TABLE statement is a fundamental SQL command that defines a new table in a database, specifying its structure including column names, data types,…

A CROSS JOIN produces the Cartesian product of two tables, pairing every row from the first table with every row from the second.

CSV (Comma-Separated Values) is a simple, text-based file format used to store tabular data.

CUBE is a GROUP BY extension that computes aggregates for every possible combination of a set of grouping columns, including subtotals for each subset and a grand total.

Dagster is an open-source data orchestration platform designed to help data engineers and scientists build, test, and monitor data pipelines.

A dashboard is a visual display of key metrics, data points, and analytics that provides a quick overview of an organization's performance or a specific…

Dask is a Python library for parallel and distributed computing that scales pandas-like DataFrame, NumPy-like array, and general task-graph workloads across multiple cores or a cluster.

Data refers to raw facts, figures, or information that can be collected, stored, and processed for analysis or decision-making purposes.

A data app is an interactive software application that allows users to explore, analyze, and visualize data without requiring extensive coding skills.

dbt is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively.

A data catalog is a centralized inventory of an organization's datasets, tracking metadata like schema, location, lineage, and ownership so people and systems can discover, understand, and access data.

Data cleansing (or data cleaning) is the process of detecting and correcting inaccurate, incomplete, inconsistent, or malformed data so it's reliable for analysis and downstream systems.

Data compression encodes data using fewer bits than its original representation, reducing storage footprint and the amount of I/O needed to read it.

A data contract is a formal, agreed-upon specification between a data producer and its consumers that defines the schema, format, semantics, and guarantees (like update frequency or backward-compatibility rules) of a dataset.

Data engineering is the practice of designing, building, and maintaining the infrastructure and systems that enable the collection, storage, processing, and…

Data fabric is an architecture that uses metadata, automation, and integration technology to connect and provide unified access to data across disparate systems, without necessarily moving it all into one place.

Data freshness measures how up to date a dataset is relative to when the underlying real-world events actually occurred, and is a core signal for whether downstream data and dashboards can be trusted.

Data governance is the set of policies, roles, and processes an organization uses to manage the availability, quality, security, and proper use of its data.

Data ingestion is the process of importing raw data from various sources into a system where it can be stored and analyzed.

A data lake is a centralized repository that stores raw structured, semi-structured, and unstructured data at any scale, in its native format, until it's needed for analysis.

A data lakehouse is an architecture that combines the low-cost, flexible storage of a data lake with the ACID transactions, schema enforcement, and performance features of a data warehouse.

Data lineage is the traceable record of where a piece of data came from, what transformations it passed through, and where it's used downstream.

Data Load Tool (dlt) is an open-source Python library that automates extracting data from sources and loading it into destinations like DuckDB and MotherDuck, with automatic schema inference and incremental loading.

A data mart is a subject- or department-specific subset of a data warehouse, scoped to the needs of one business area like sales, marketing, or finance.

Data mesh is a decentralized approach to data architecture in which individual business domains own and serve their own data as a product, rather than a central team owning all data pipelines.

A data model is a conceptual representation of how data is structured, organized, and related within a system or database.

Data normalization is the process of structuring relational tables to reduce data redundancy and prevent update anomalies, typically by applying a sequence of rules called normal forms.

Data observability is the continuous, automated monitoring of data pipelines and datasets to detect freshness, volume, schema, and quality problems before they reach downstream users.

Data orchestration is the automated coordination, scheduling, and monitoring of interdependent data tasks—such as extracts, transformations, and loads—so they run in the correct order and recover cleanly from failure.

A data pipeline is a series of interconnected processes that extract data from various sources, transform it into a usable format, and load it into a…

Data profiling is the process of examining a dataset to understand its structure, content, and quality — things like data types, value distributions, null rates, and cardinality — before using it for analysis or building pipelines on top of it.

Data quality is the degree to which data is accurate, complete, consistent, timely, and fit for the purposes it's used for—dashboards, models, and operational decisions.

Data serialization is the process of converting in-memory data structures into a format that can be stored on disk or transmitted over a network, and later reconstructed through deserialization.

Data skew is an uneven distribution of data across partitions or keys in a distributed system, causing some worker nodes to process far more data than others and become bottlenecks.

Data sources are the origin points of information in a data pipeline or analytics workflow.

Data transformation is the process of converting data from its raw, source format into a structured form suited to analysis — through operations like cleaning, joining, aggregating, and reshaping.

In the context of databases and programming, a data type defines the nature of data that can be stored in a specific field or variable.

Data Vault is a data warehouse modeling methodology, created by Dan Linstedt, that separates data into Hubs (business keys), Links (relationships), and Satellites (descriptive, time-stamped attributes) to make a warehouse resilient to source-system change.

Data visualization is the graphical representation of information and data, using visual elements like charts, graphs, and maps to communicate complex…

A data warehouse is a centralized system designed to store structured, cleaned data and support fast, complex analytical queries (OLAP), as opposed to the high-volume transactional workloads of an operational database.

Data wrangling is the broader process of taking raw, messy data and manually or programmatically converting it into a structured, usable format for analysis — encompassing cleaning, reshaping, joining, and enriching.

A database is a structured collection of data organized for efficient storage, retrieval, and management.

A database index is a data structure that stores a subset of a table's data in an order optimized for fast lookups, letting queries find matching rows without scanning the whole table.

Database replication is the process of copying and synchronizing data from one database (the primary) to one or more other databases (replicas), used to improve availability, read scalability, and disaster recovery.

Database sharding is a horizontal partitioning technique that splits a large database into smaller, independent pieces called shards, each hosted on a separate server, to scale storage and throughput beyond a single machine.

Databricks is a cloud data and AI platform built by the original creators of Apache Spark, centered on the lakehouse architecture that unifies data lakes and warehousing.

A DataFrame is a two-dimensional data structure that organizes data into rows and columns, similar to a spreadsheet or database table.

A dataset is a collection of related data points or records, typically organized in a structured format for analysis or processing.

DATE_TRUNC(unit, value) rounds a date or timestamp down to the start of a specified unit — such as day, month, or year — commonly used to bucket time-series data.

DB-API is a standardized interface for Python to interact with relational databases.

DB-API 2.0 is a standardized Python interface for accessing relational databases.

dbt (data build tool) is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouses more effectively.

Debezium is an open-source distributed platform for change data capture (CDC), which streams row-level insert, update, and delete events out of databases in real time.

Deduplication is the process of identifying and removing duplicate records from a dataset, keeping only one representative row per logical entity.

A DEFAULT value is a value a database column automatically takes on when an INSERT statement doesn't explicitly specify a value for it.

A degenerate dimension is a dimension attribute — like an invoice or order number — that's stored directly as a column in the fact table, with no corresponding dimension table, because it has no other descriptive attributes.

The DELETE statement removes rows from a table that match an optional WHERE condition, without dropping the table itself.

Delta Lake is an open table format, originally developed by Databricks, that adds ACID transactions, schema enforcement, and time travel to Parquet data stored in a data lake.

delta-rs is a native Rust implementation of the Delta Lake protocol, exposed to Python as the `deltalake` package, that lets engines read and write Delta tables without depending on Spark or the JVM.

Denormalization is the deliberate process of combining or duplicating normalized data — for example, flattening related tables together — to reduce joins and speed up read-heavy analytical queries.

A denormalized table is a table that intentionally stores redundant, pre-joined data from multiple normalized sources, trading storage and update simplicity for faster, simpler reads.

Dictionary encoding is a compression technique that replaces repeated column values with small integer codes, storing the distinct values once in a separate lookup table.

A dimension table stores the descriptive, mostly-textual attributes — like customer name, product category, or region — that you use to filter, group, and label the measures in a fact table.

Dimensional modeling is a data warehouse design methodology, developed by Ralph Kimball, that organizes data into fact tables (measures) and dimension tables (descriptive context) optimized for business-user querying.

A directed acyclic graph (DAG) is a graph of nodes connected by one-way edges with no cycles, used to represent task dependencies so that execution order is unambiguous.

DISTINCT removes duplicate rows from a query's result set, returning only unique combinations of the selected columns.

dlt is an open-source Python library designed to simplify the process of building data pipelines.

This guide dives into the realm of dot commands, which are special commands that enhance the functionality of the DuckDB CLI, making it even more versatile…

DuckDB is an embeddable SQL database management system designed for analytical workloads.

The DuckDB Command Line Interface (CLI) is an interactive terminal program that lets you directly interact with DuckDB databases using SQL commands.

DuckDB's secrets manager is the built-in mechanism for storing and reusing credentials, such as S3 keys or Azure connection strings, needed to authenticate with cloud storage and remote services.

DuckLake is an open table format, created by the DuckDB team, that stores lakehouse metadata in a transactional SQL database instead of files — while keeping the actual data as Parquet files in object storage.

ELT (Extract, Load, Transform) is a modern data integration process that reverses the order of traditional ETL (Extract, Transform, Load) workflows.

Embeddings are dense numeric vector representations of data — text, images, audio, or other objects — learned so that semantically similar inputs end up close together in vector space.

An entity-relationship diagram (ERD) is a visual representation of a database's entities (tables), their attributes, and the relationships between them, used to design and document relational schemas.

An ENUM, short for enumeration, is a data type in DuckDB and many other database systems that allows you to define a fixed set of named values.

ETL (Extract, Transform, Load) is a data integration process that combines data from multiple sources into a single destination, typically a data warehouse…

Event-driven architecture is a software design pattern in which services communicate by producing and reacting to events, rather than calling each other directly, enabling loosely coupled, asynchronous systems.

Evidence is an open-source "BI-as-code" framework for building data reports and dashboards using just SQL queries and Markdown, compiled into a static website.

EXCEPT returns the rows produced by the first query that do not appear in the result of a second query, effectively computing a set difference.

An execution plan is the tree of operators a database will run, in a specific order, to produce the result of a query — the concrete strategy chosen by the query planner and optimizer.

EXISTS tests whether a subquery returns at least one row, commonly used to check for the presence of related records without caring about their actual values.

The EXPLAIN clause is a powerful diagnostic tool that shows how DuckDB plans to execute your SQL query.

Exploratory Data Analysis (EDA) is a crucial approach in data science that involves examining and visualizing datasets to uncover patterns, anomalies, and…

An extension in DuckDB is a modular component that adds functionality to the core database system.

EXTRACT(field FROM value) pulls a single numeric component — such as year, month, or hour — out of a date, time, or timestamp value.

A fact constellation schema (also called a galaxy schema) is a dimensional model with multiple fact tables that share one or more common dimension tables.

A fact table is the central table in a dimensional model that stores numeric measures (like revenue or quantity) alongside foreign keys linking to descriptive dimension tables.

Feather is a fast, lightweight binary file format for storing columnar data frames on disk, based on the Apache Arrow columnar memory format, commonly used for quick interchange between pandas, R, and other Arrow-compatible tools.

A filter operation selectively includes or excludes records from a dataset based on one or more conditions.

The FILTER clause restricts which rows an aggregate function processes, letting you compute multiple conditional aggregates in a single GROUP BY query without CASE expressions.

Fivetran is a managed, fully automated data integration (ELT) platform that uses pre-built connectors to replicate data from source systems into a data warehouse or lake.

A foreign key links one table to another by referencing its primary key. Learn how foreign keys enforce referential integrity, with SQL syntax, composite key examples, and data warehousing use cases.

fsspec (Filesystem Spec) is a Python library that provides a single, consistent interface for reading and writing files across local disks, cloud object stores, HTTP, and many other storage backends.

Full-text search is a technique for searching text content by relevance to a query — matching keywords, handling stemming and ranking results — rather than requiring an exact substring or equality match.

Funnel analysis measures how users progress through an ordered sequence of steps toward a goal — such as viewing a product, adding it to cart, and checking out — identifying where the largest drop-offs occur.

GENERATE_SERIES(start, stop, step) produces a sequence of numeric, date, or timestamp values, commonly used to build calendars or fill gaps in time-series data.

A generated column is a table column whose value is automatically computed from an expression involving other columns, rather than being stored directly by INSERT or UPDATE statements.

Geospatial data represents locations and shapes on the Earth's surface — points, lines, and polygons with coordinates — and is queried with specialized spatial functions like distance, containment, and intersection.

GitHub is a web-based platform for version control and collaboration, widely used in software development and data engineering.

A glob pattern is a wildcard-based string pattern, using characters like `*` and `?`, used to match multiple filenames or paths at once instead of listing them individually.

The Go programming language, often referred to as Golang, is a statically typed, compiled language designed by Google engineers Robert Griesemer, Rob Pike,…

Google BigQuery is a serverless, fully managed cloud data warehouse that runs SQL queries at scale without provisioning or managing infrastructure.

Grain is the level of detail that a single row in a fact table represents — for example, one row per order line item versus one row per order per day.

A graph database stores data as nodes and relationships (edges) rather than rows and tables, optimized for traversing and querying densely connected data such as social networks, fraud graphs, or recommendation graphs.

Great Expectations (GX) is an open-source Python framework for defining, running, and documenting automated data quality checks called Expectations.

GREATEST and LEAST return the largest or smallest value, respectively, among a list of expressions passed as arguments.

GROUP BY organizes rows into groups for aggregate calculations like SUM, COUNT, and AVG. Learn single and multi-column grouping, HAVING filters, and GROUP BY ALL.

GROUPING SETS lets a single GROUP BY compute multiple different grouping combinations at once, producing subtotal rows for each specified combination in one query.

Hamilton is an open-source Python micro-framework for defining data and feature transformation pipelines as plain functions, which it automatically assembles into a dependency graph.

The HAVING clause filters grouped results after aggregation, letting you keep or discard groups based on conditions over aggregate values like SUM() or COUNT().

HDFS (Hadoop Distributed File System) is a distributed, block-based file system designed to store very large files reliably across clusters of commodity hardware, historically the primary storage layer for Hadoop-based big data systems.

Hex is a cloud-based notebook platform that combines SQL, Python, R, and no-code cells for data analysis, and for publishing the results as interactive apps and dashboards.

Hive partitioning is a directory-naming convention, originally from Apache Hive, that encodes column values in folder paths (like year=2026/month=01/) so query engines can skip irrelevant files without reading them.

HTAP (Hybrid Transactional/Analytical Processing) describes database systems designed to handle both transactional (OLTP) and analytical (OLAP) workloads on the same data, without a separate ETL pipeline moving data between two systems.

The httpfs extension is DuckDB's built-in module for reading and writing files directly over HTTP(S) and from S3-compatible object storage, Azure, and Google Cloud Storage, without downloading them first.

HyperLogLog is a probabilistic algorithm for estimating the number of distinct elements in a large dataset (cardinality) using a small, fixed amount of memory, at the cost of a small, predictable error margin.

Ibis is a Python dataframe library that lets you write a single portable API and execute it against 20+ backends, with DuckDB as its default local engine.

Idempotency is the property of an operation that produces the same end result no matter how many times it's applied, which makes retries after a failure safe rather than risky.

The IN operator tests whether a value matches any value in a given list or subquery, providing a concise alternative to multiple OR conditions.

An in-memory database is a type of database management system that primarily relies on a computer's main memory (RAM) for data storage and processing, as…

An incremental model is a transformation that processes only new or changed source data on each run and merges it into an existing table, instead of rebuilding the whole table from scratch every time.

The INSERT statement is a fundamental SQL command used to add new rows of data into a table.

INTERSECT returns only the rows that appear in the result sets of both of two queries, effectively computing the common rows between them.

INTERVAL is a SQL data type and literal syntax representing a span of time — such as '3 days' or '1 month' — used for date/timestamp arithmetic.

An isolation level defines how much one transaction's in-progress changes are visible to other concurrently running transactions, trading off consistency against concurrency.

Java is a widely-used, object-oriented programming language designed for portability and cross-platform compatibility.

JavaScript is a versatile, high-level programming language primarily used for web development.

How do SQL JOINs work? Learn INNER, LEFT, RIGHT, FULL OUTER, CROSS, and ASOF joins with syntax examples — plus how to join directly against CSV and Parquet files.

JSON (JavaScript Object Notation) is a lightweight, text-based data interchange format that's easy for humans to read and write, and simple for machines to…

Julia is a high-performance, dynamic programming language designed for technical and scientific computing.

A junk dimension is a single dimension table that groups together several low-cardinality, otherwise-unrelated flags and indicators, to avoid cluttering a fact table with many tiny separate dimension keys.

Jupyter notebooks are interactive, web-based computing environments that allow data analysts and scientists to create and share documents containing live…

Kappa architecture is a data processing design that uses a single stream-processing pipeline for both real-time and historical data, avoiding the separate batch layer required by Lambda architecture.

Kestra is an open-source, event-driven orchestration platform where workflows are defined declaratively in YAML rather than a general-purpose programming language.

A key-value store is a simple database model that maps unique keys to values, optimized for very fast lookups, writes, and updates by key, without the fixed schema or query flexibility of a relational database.

LAG() and LEAD() are window functions that return a value from a previous or following row within the same result set, commonly used for period-over-period comparisons.

Lambda architecture is a data processing design that runs a batch layer and a speed layer in parallel to balance completeness and accuracy against low-latency results, merging both into a serving layer.

Lance is a modern, open-source columnar file format designed for machine learning and vector-search workloads, offering fast random access, versioning, and native support for embeddings alongside tabular data.

A Large Language Model (LLM) is an artificial intelligence system trained on massive amounts of text data that can understand, generate, and manipulate human…

Larger-than-memory processing is a query engine's ability to correctly and efficiently process datasets or intermediate results that exceed the amount of RAM available, typically by spilling data to disk.

Latency is the time delay between initiating a request or operation and receiving its result — for example, the time from sending a database query to getting the first byte of its response back.

A LATERAL join lets a subquery in the FROM clause reference columns from earlier tables in the same FROM clause, enabling per-row correlated logic without a scalar subquery.

The LIKE operator performs pattern matching on string values, using % as a wildcard for any number of characters and _ for a single character.

The LIMIT clause restricts a query's result set to a maximum number of rows, often used with ORDER BY to fetch a top-N subset.

Linux is an open-source operating system kernel that forms the foundation of many popular distributions used in data engineering and analytics.

LIST and ARRAY are nested SQL data types for storing an ordered collection of values in a single column — LIST is variable-length, ARRAY is fixed-length.

Looker is Google Cloud's enterprise BI platform built around LookML, a version-controlled semantic modeling layer that sits between a database and its dashboards.

Mage (mage-ai) is an open-source data pipeline tool that combines a notebook-style development experience with production-grade orchestration for ETL, ELT, and AI/ML workflows.

MAP is a nested SQL data type storing an ordered collection of unique key-value pairs, useful for representing dynamic or sparse attributes within a single column.

Marimo is an open-source reactive Python notebook, stored as plain .py files, with native SQL cells backed by DuckDB by default.

Massively Parallel Processing (MPP) is a database architecture in which many independent nodes, each with their own CPU, memory, and storage, cooperate to execute a single query in parallel across a cluster.

Master data management (MDM) is the discipline of creating and maintaining a single, consistent, authoritative record for core business entities—like customers, products, or vendors—across all the systems that use them.

A materialized view stores the physical, precomputed result of a query as if it were a table, so reads are fast, at the cost of needing an explicit refresh when the underlying data changes.

Matplotlib is Python's foundational 2D plotting library, used to create static, animated, and interactive charts from arrays, lists, and DataFrames.

Medallion architecture is a data design pattern that organizes a lakehouse into progressive layers — Bronze (raw), Silver (cleaned), and Gold (business-level aggregates) — improving data quality and structure as it moves through each stage.

Meltano is an open-source ELT platform built on the Singer specification, letting teams declare data pipelines as code using YAML configuration and a CLI.

MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.

Metabase is an open-source business intelligence tool that lets people explore data, build charts, and share dashboards through a simple query builder or raw SQL.

Metadata is information that describes other data.

MotherDuck is a cloud-based analytics platform built on top of DuckDB that enables teams to analyze and share data without managing complex infrastructure.

The MotherDuck extension is a component of DuckDB that enables seamless integration with the MotherDuck cloud service.

A NATURAL JOIN automatically joins two tables on all columns that share the same name, without an explicit ON or USING clause.

A natural key is an identifier that comes from real-world business data — like an email address, SSN, or product SKU — as opposed to a surrogate key generated purely for database use.

NTILE(n) is a window function that divides the rows in a partition into n roughly equal-sized buckets and returns the bucket number for each row.

NULL represents a missing, unknown, or inapplicable value in SQL -- it is not the same as zero, an empty string, or false, and requires special comparison operators like IS NULL.

NULLIF returns NULL if two expressions are equal, and otherwise returns the first expression -- commonly used to avoid divide-by-zero errors or convert sentinel values into NULL.

NumPy is the core Python library for numerical computing, providing a fast, memory-efficient N-dimensional array type and vectorized math operations that most of the Python data-science stack is built on.

Object storage is a data storage architecture that manages data as discrete objects — each with data, metadata, and a unique identifier — in a flat namespace, accessed over HTTP APIs rather than a traditional file system hierarchy.

The OFFSET clause skips a specified number of rows in a query's result set before returning the remaining rows, commonly used for pagination alongside LIMIT.

An OLAP cube is a multidimensional data structure that pre-aggregates measures across combinations of dimensions, letting users slice, dice, drill down, and roll up data quickly without recomputing aggregations each time.

OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two workload categories: OLTP handles many small, concurrent transactional writes on normalized schemas, while OLAP handles large read-heavy analytical queries on denormalized, columnar data.

OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.

A complete guide to Online Analytical Processing (OLAP). Learn about OLAP cubes, the differences between OLAP and OLTP, and the types of OLAP systems (MOLAP, ROLAP, HOLAP).

ORC (Optimized Row Columnar) is a columnar file format originally built for Hadoop and Hive, designed for fast reads, high compression, and efficient predicate pushdown on large analytical datasets.

The ORDER BY clause is a fundamental SQL command that lets you control the sequence in which your query results are returned.

The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.

pandas is a powerful, open-source data manipulation and analysis library for Python.

Pandas DataFrames are versatile, two-dimensional labeled data structures in Python that can hold various types of data.

Parameterized queries use placeholders instead of embedded values to prevent SQL injection and improve performance. Learn named vs positional parameters with Python examples.

Apache Parquet is a columnar storage file format designed for efficient data processing and analytics.

Parquet is a compressed, columnar binary file format optimized for analytics, while CSV is a plain-text, row-based format with no built-in schema or compression — Parquet is generally faster and smaller for analytical workloads, while CSV is simpler and universally readable.

PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.

Partition pruning is a query optimization that skips reading entire partitions of a dataset when a query's filters make it impossible for those partitions to contain matching rows.

Partitions in data systems refer to the logical or physical division of large datasets into smaller, more manageable segments.

A percentile is the value below which a given percentage of a dataset falls — for example, the 95th percentile (p95) of response times is the value that 95% of requests were faster than.

Personally identifiable information (PII) is any data that can be used, alone or combined with other data, to identify a specific individual—names, email addresses, government ID numbers, and similar fields.

Data pipelines are automated workflows that move and transform data from various sources to one or more destinations.

The PIVOT clause is a SQL feature that transforms rows into columns, making it easier to create summary tables and cross-tabulations of your data.

Plot.ly is a powerful data visualization library and platform that enables users to create interactive, publication-quality graphs and dashboards.

Polars is a high-performance data manipulation library written in Rust, designed to handle large datasets efficiently.

PostgreSQL, often referred to as Postgres, is a powerful open-source relational database management system.

Power BI is Microsoft's business intelligence suite for connecting to data, modeling it, and building interactive reports and dashboards.

PRAGMA is a SQL command for reading or setting database engine configuration and inspecting internal metadata, using syntax outside standard SQL DML/DDL.

Predicate pushdown is a query optimization that moves filter conditions as close as possible to the data source, so fewer rows (or bytes) are read before filtering happens.

Prefect is a Python-native workflow orchestration framework that lets engineers turn ordinary functions into scheduled, observable, and fault-tolerant data pipelines.

Presto is an open-source distributed SQL query engine, created at Facebook, for running interactive analytical queries across large datasets and multiple data sources.

A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.

Protocol Buffers (Protobuf) is Google's language-neutral, binary serialization format that defines message structures in .proto schema files, compiled into strongly typed code for many programming languages, widely used in gRPC APIs and event streaming.

PyArrow is a Python library that provides a high-performance interface for working with columnar data structures, particularly those defined by the Apache…

PyIceberg is the official Python implementation of Apache Iceberg — a pure-Python library for reading, writing, and managing Iceberg tables and catalogs without requiring Spark or a JVM.

PyPI, short for Python Package Index, is the official repository for third-party Python software packages.

Python is a high-level, interpreted programming language known for its simplicity and readability.

QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery.

Quarto is an open-source scientific and technical publishing system from Posit that renders documents combining prose with R, Python, Julia, or SQL code into reports, websites, and books.

A query is a request made to a database to retrieve, modify, or analyze data.

A query engine parses, optimizes, and executes SQL queries against data sources. Learn its core architecture, how it differs from a database, and see examples like DuckDB and Presto.

Query optimization is the process by which a database transforms a SQL query into an efficient execution plan, choosing among logically equivalent strategies for the one expected to run fastest.

The query planner is the database component that translates a parsed SQL statement into an execution plan — a tree of operators describing how the query will actually be run.

RANK() and DENSE_RANK() are window functions that assign ranking numbers to rows within a partition, differing in how they handle ties and the gaps left afterward.

Ray is an open-source framework for scaling Python applications, including distributed compute, machine learning training, and hyperparameter tuning, across clusters of machines.

DuckDB can read CSV, Parquet, JSON, and other file formats directly with SQL table functions, whether the files are local, remote over HTTP/S3, or matched in bulk with a glob pattern.

Referential integrity is the guarantee that a reference from one table to another — typically a foreign key — always points to a row that actually exists, preventing orphaned records.

SQL engines support regular expressions through functions and operators like REGEXP_MATCHES, REGEXP_REPLACE, and SIMILAR TO for pattern-based text matching and extraction.

The relational API in DuckDB provides a fluent, Pythonic interface for constructing and executing SQL queries programmatically.

A relational database is a structured collection of data organized into tables with rows and columns.

A relational object is a fundamental concept in relational databases, representing a structured collection of data organized into rows and columns.

The result keyword in SQL is not commonly used across all database systems, but in DuckDB, it has a specific meaning within the context of recursive queries.

Reverse ETL is the process of syncing curated data from a data warehouse back out into operational tools like a CRM, ad platform, or support desk, so business teams can act on it.

Rill is an open-source BI tool built on DuckDB (and optionally ClickHouse) that turns SQL models and YAML metrics definitions into fast, interactive dashboards.

A role-playing dimension is a single physical dimension table, most often a date dimension, that is joined into a fact table multiple times to represent different roles, such as order date, ship date, and delivery date.

ROLLUP is a GROUP BY extension that produces hierarchical subtotals -- aggregating at each level of a column list, from the most detailed grouping down to a grand total.

A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…

ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.

Row-oriented storage is a data layout that stores each record's fields contiguously on disk, so a whole row can be read or written in a single operation.

Run-length encoding (RLE) is a compression technique that replaces consecutive repeated values with a single (value, count) pair, shrinking runs of identical data.

Rust is a modern, high-performance programming language designed for systems programming and safe concurrency.

An S3 bucket is a fundamental storage container in Amazon Web Services' Simple Storage Service (S3).

A schema is a logical container or blueprint that defines how data is organized within a database.

Schema evolution is the ability to change a table's structure — adding, removing, renaming, or reordering columns, or widening types — over time without needing to rewrite all existing data or break existing readers.

A schema registry is a centralized service that stores, versions, and validates data schemas — typically for Avro, Protobuf, or JSON Schema — used to keep producers and consumers in streaming systems like Kafka compatible as schemas change.

scikit-learn is a widely used Python library for classical machine learning, providing a consistent API for classification, regression, clustering, and preprocessing built on NumPy and SciPy.

The SELECT statement is the workhorse of SQL, used to retrieve and transform data from tables, views, or other data sources.

A self join joins a table to itself, typically using table aliases to compare rows within the same table to each other.

A semantic layer is a layer between raw data and end users that defines business metrics, dimensions, and relationships once, so that different tools and teams query consistent, agreed-upon definitions instead of re-deriving them independently.

A sequence is a database object that generates a series of unique numeric values, most commonly used to auto-generate primary key IDs.

Serverless is a cloud computing model where the provider automatically provisions, scales, and bills compute on demand — so you run code, queries, and analytics without managing servers or a fixed cluster.

Singer is an open-source specification that defines how independent scripts, called taps and targets, communicate data and state using a simple JSON format over stdout and stdin.

Slowly changing dimensions (SCD) are techniques for handling changes to dimension attributes over time — such as a customer moving to a new region — while preserving or overwriting historical values as needed.

Snowflake is a cloud data platform that separates storage, compute, and services into independent layers, letting you scale query power without moving the underlying data.

A snowflake schema is a dimensional model where dimension tables are normalized into multiple related sub-tables instead of one flat table, reducing redundancy at the cost of extra joins.

SQL (Structured Query Language) is the standard language for working with relational databases.

SQL analytics refers to using SQL queries to analyze data and derive insights, typically working with large datasets stored in databases or data warehouses.

A SQL function is a reusable piece of code that performs a specific operation and returns a result.

A SQL query is a structured request written in Structured Query Language (SQL) that allows you to retrieve, analyze, or manipulate data stored in a database.

SQLAlchemy is a popular Python library that provides a flexible way to interact with databases without writing raw SQL code.

SQLGlot is a Python SQL parser, transpiler, and optimizer that can parse and translate SQL between more than 30 dialects, including DuckDB, Snowflake, BigQuery, and Spark.

SQLite is a lightweight, serverless database engine that runs as an embedded part of an application rather than as a separate server process.

A star schema is a dimensional data warehouse design where a central fact table of numeric measures connects directly to denormalized dimension tables, forming a shape like a star.

DuckDB uses an efficient columnar storage format optimized for analytical queries.

The storage layer refers to the component of a data system responsible for persistently storing and managing data.

Stream processing is the continuous computation of data as individual events arrive, rather than waiting to collect them into a batch. It powers use cases that need results within seconds or milliseconds of an event occurring.

Streamlit is an open-source Python library that simplifies the process of creating interactive web applications for data science and machine learning…

String functions manipulate text values in SQL — concatenating, trimming, searching, splitting, and reformatting strings as part of a query.

A struct in DuckDB is a composite data type that allows you to group multiple fields of different types into a single unit.

A sub-query (also called a nested query) is a SQL query embedded within another SQL query.

SUMMARIZE is a powerful DuckDB command that automatically generates descriptive statistics and metadata about your data.

A surrogate key is a system-generated identifier — typically a sequential integer or UUID — assigned to a row that has no business meaning of its own, used as the primary key in dimension and fact tables.

A table is a fundamental structure in a relational database that organizes data into rows and columns, similar to a spreadsheet.

A table format is a metadata layer on top of data files in a lake or object storage that defines what constitutes a table — its schema, partitioning, and file list — enabling ACID transactions, schema evolution, and time travel across multiple query engines.

Tableau is a data visualization and business intelligence platform, owned by Salesforce, known for interactive drag-and-drop dashboards built on its VizQL engine.

TABLESAMPLE (or a SAMPLE clause) returns a random subset of a table's rows, letting queries run faster on a representative fraction of the data instead of the full table.

A temporary table is a table scoped to a single session (or transaction) that is automatically dropped when the session ends, used for intermediate results that don't need to persist.

Third normal form (3NF) is a database design rule requiring a table to already be in second normal form and to have no transitive dependencies — every non-key column must depend only on the primary key.

Throughput is the amount of work a system completes per unit of time — such as queries per second, rows processed per second, or requests handled per minute — measuring capacity rather than the speed of any single operation.

A time series is a sequence of data points ordered chronologically. Time-series analysis uncovers trends, seasonality, and forecasts in data like metrics, sensor readings, and financial ticks — and DuckDB handles most of these patterns in plain SQL.

A time-series database is a database optimized for storing and querying data points indexed by time, such as metrics, sensor readings, or logs, with efficient time-range scans, downsampling, and retention.

Timestamps are a fundamental data type in databases and programming languages that represent a specific point in time, typically including both the date and…

A database transaction is a sequence of one or more operations that are executed as a single logical unit of work, either fully applied or fully rolled back.

Trino is an open-source distributed SQL query engine for federated analytics, querying data across many sources without moving it. It is the project formerly known as PrestoSQL.

TRUNCATE removes all rows from a table in a single fast operation, without logging individual row deletions the way DELETE does.

TRY_CAST attempts to convert a value to another data type and returns NULL instead of raising an error if the conversion fails.

UNION combines the result sets of two or more SELECT queries into one, removing duplicate rows, while UNION ALL does the same but keeps all duplicates.

A UNIQUE constraint ensures that all values in a column, or combination of columns, are distinct across every row in a table.

UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape.

UNPIVOT reshapes wide data by turning multiple columns into rows, converting a set of columns into name/value pairs -- the inverse of PIVOT.

The UPDATE statement modifies existing rows in a table, setting one or more column values for rows that match an optional WHERE condition.

UPSERT is a database operation that inserts a new row or updates an existing one if a matching row already exists, typically expressed with an ON CONFLICT clause.

The USING clause specifies join columns by name when both tables share identically named columns, joining on equality and returning a single copy of each shared column.

VACUUM is a SQL statement, used by databases like PostgreSQL and DuckDB, that performs maintenance such as reclaiming space from deleted rows or refreshing planner statistics.

A vector database stores high-dimensional numeric vectors (typically embeddings) and provides fast similarity search over them, powering use cases like semantic search and retrieval-augmented generation.

Vectorized execution is a query processing model where each operator processes a batch (a "vector") of values at once, instead of one row at a time, to reduce interpretation overhead and better use modern CPUs.

A view is a virtual table based on the result of a SQL query.

A webhook is a way for one system to notify another in real time by sending an HTTP POST request to a pre-configured URL whenever a specific event happens, rather than the receiving system having to poll for updates.

The WHERE clause filters rows in a SQL query based on a boolean condition, keeping only rows for which the condition evaluates to true.

A wide table is a table with a large number of columns, typically produced by denormalizing and flattening related data together, common in analytics and columnar data warehouses.

A window frame is the subset of rows within a window function's partition that a calculation is applied to, defined with ROWS, RANGE, or GROUPS relative to the current row.

Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…

A write-ahead log (WAL) is a durability mechanism where every change is recorded in an append-only log before it's applied to the main data files, enabling crash recovery.

A zone map, or min-max index, is a lightweight index that stores the minimum and maximum value of a column for each block of data, letting queries skip blocks that can't match a filter.