Primary key
KeysA column or set of columns whose values uniquely identify each row in a table. A primary key cannot contain null values and must be unique across all rows.
Data & Database Glossary
Plain-language definitions of databases and data engineering: SQL, data modeling, pipelines, warehouses, data quality, and migration, across the systems a conversion or integration touches.
Terms
Vendor-neutral definitions, grouped by topic. Jump to a section, or browse the whole list. For the rest of the collection, see the glossary hub.
A database that organizes data into tables of rows and columns, where tables can be related to one another through shared key values. It is accessed and manipulated using SQL and typically enforces a predefined schema.
A structured collection of data organized into rows and columns within a relational database. Each table usually represents a single entity type, such as customers or orders.
A single horizontal entry in a table that holds one complete set of related values, one per column. A row is also called a record or tuple.
A vertical element of a table that defines one attribute shared by every row, along with its data type. A column is also called a field or attribute.
The formal definition of how data is structured in a database, including tables, columns, data types, keys and constraints. The term can also refer to a named namespace that groups related database objects.
A column or set of columns whose values uniquely identify each row in a table. A primary key cannot contain null values and must be unique across all rows.
A column or set of columns in one table that references the primary key of another table, enforcing a link between the two. Foreign keys support referential integrity by ensuring referenced rows exist.
A key made up of two or more columns that together uniquely identify a row when no single column is sufficient. Each individual column may contain duplicate values on its own.
Any column or combination of columns that could serve as a unique identifier for rows in a table. One candidate key is chosen as the primary key and the rest become alternate keys.
A set of guarantees for reliable transactions: Atomicity, Consistency, Isolation and Durability. Together they ensure that transactions complete fully or not at all and that committed data survives failures.
A unit of work containing one or more operations that must all succeed or all fail together. Transactions are committed to make changes permanent or rolled back to undo them.
The ACID property guaranteeing that all operations in a transaction are treated as a single indivisible unit. If any part fails, the entire transaction is rolled back and no partial changes remain.
Settings that control how and when the changes made by one transaction become visible to others, trading consistency against concurrency. Common levels include read committed, repeatable read and serializable.
Commit finalizes a transaction and makes its changes permanent, while rollback discards all changes made since the transaction began. They are the two ways a transaction can end.
A rule enforced by the database to maintain data integrity, such as NOT NULL, UNIQUE, CHECK, primary key and foreign key constraints. Constraints reject data that violates the defined rules.
The guarantee that foreign key values always reference existing rows in the related table. It prevents orphaned records and is enforced through foreign key constraints.
A broad category of databases that do not use the traditional relational table model, often trading strict schemas and joins for flexibility and horizontal scale. Types include document, key-value, graph and columnar stores.
A NoSQL database that stores data as self-contained documents, typically in JSON or BSON, where each document can have its own structure. It suits data that varies in shape or is naturally nested.
A NoSQL database that stores data as simple pairs of a unique key and an associated value. It offers very fast lookups by key and is common for caching and session storage.
A database that models data as nodes and the edges connecting them, making relationships first-class and efficient to traverse. It excels at highly connected data such as social networks and recommendation engines.
A NoSQL database that organizes data into rows and dynamic columns grouped into column families, where each row can have different columns. It scales horizontally for very large datasets.
A database optimized for data points indexed by time, such as metrics, sensor readings and logs. It supports efficient time-ranged queries, retention policies and downsampling.
A class of relational databases that aim to provide the horizontal scalability of NoSQL systems while keeping ACID transactions and SQL. They are often used for distributed transactional workloads.
A workload pattern characterized by many short, concurrent read and write transactions, such as order entry or banking. OLTP systems prioritize fast, reliable single-record operations and are usually highly normalized.
OLTP handles frequent, small transactions against current operational data, while OLAP runs large analytical queries over historical data. The two are usually kept in separate systems because they optimize for opposite access patterns.
A copy of data stored separately so it can be recovered after loss, corruption or disaster. Backups can be full, incremental or differential and should be tested by periodic restores.
The process of recovering data from a backup to return a database to a previous usable state. A backup is only as good as a verified ability to restore from it.
The ability to restore a database to its exact state at a specific moment, typically by combining a backup with transaction logs. It limits data loss by letting recovery stop just before an error occurred.
A sequential record of all changes made to a database, used for recovery, replication and point-in-time restore. Writing changes to the log before applying them is known as write-ahead logging.
A classification that defines the kind of values a column or field can hold and the operations allowed on it, such as integer, decimal, text, boolean or date. Choosing correct types enforces integrity and controls storage.
A special marker indicating the absence of a value, distinct from zero or an empty string. Null means unknown or not applicable, and comparisons involving null follow three-valued logic.
A value automatically assigned to a column when a row is inserted without specifying one. Defaults reduce nulls and ensure columns start with sensible values.
Data organized into a fixed, predefined format such as rows and columns, making it easy to query with SQL. Examples include relational tables and spreadsheets.
Data that has some organizational markers, such as tags or keys, but does not fit a rigid tabular schema. JSON, XML and log files are common examples.
Data without a predefined model or organization, such as free text, images, audio and video. It requires specialized processing to extract structure and meaning.
A rule on a foreign key that automatically propagates changes to related rows, such as deleting child rows when a parent is deleted or updating keys. Options include CASCADE, SET NULL and RESTRICT.
A rule that requires all values in a column or set of columns to be distinct across rows, while allowing nulls unless otherwise restricted. It prevents duplicate entries where uniqueness is required but the column is not the primary key.
A rule that limits the values allowed in a column by evaluating a boolean expression for each row. Rows that fail the condition are rejected, enforcing domain rules such as positive amounts.
A column setting that automatically generates a new, increasing numeric value for each inserted row. It is a common way to produce surrogate primary keys without manual assignment.
A 128-bit identifier designed to be unique across systems without central coordination. UUIDs are often used as surrogate keys in distributed systems where sequential integers are impractical.
The core software component of a database management system that handles storing, retrieving, indexing and updating data. Different engines make different trade-offs in performance, durability and features.
Software that defines, stores, manages and controls access to a database. It handles concurrency, security, integrity and recovery so applications do not have to.
Reading data that another transaction has modified but not yet committed, which may later be rolled back. It is a concurrency anomaly prevented by higher isolation levels.
A durability technique in which changes are recorded in a log before being applied to the database files. If a crash occurs, the log is replayed to recover committed changes.
A set of rules that determines how text is sorted and compared, including case and accent sensitivity and language-specific ordering. Collation affects query results, uniqueness and joins on text columns.
A database object that generates a series of unique numeric values on request, independent of any table. Sequences are commonly used to supply surrogate key values.
A named point within a transaction that allows a partial rollback to that point without aborting the entire transaction. Savepoints give finer control over error handling in long transactions.
The plans and processes for restoring data and systems after a major failure or catastrophic event. Key targets are the recovery time objective and recovery point objective.
Recovery point objective is the maximum acceptable amount of data loss measured in time, while recovery time objective is the maximum acceptable time to restore service. Together they define disaster recovery requirements.
For numeric types, precision is the total number of significant digits and scale is the number of digits after the decimal point. Choosing them correctly prevents rounding errors in financial and measured data.
A group of related columns stored together in a wide-column NoSQL database, read and written as a unit. Organizing columns into families optimizes access for common query patterns.
A consistency model for distributed systems standing for Basically Available, Soft state and Eventual consistency, contrasted with ACID. It favors availability and scalability over immediate consistency.
Permanently removing a row from a table so it no longer exists in storage. It frees space and simplifies queries but loses history unless captured elsewhere.
Large object data types where a BLOB stores binary data such as images or files and a CLOB stores large text. They let databases hold content too big for ordinary column types.
A SQL operation that combines rows from two or more tables based on a related column between them. Joins are how normalized data is recombined into result sets.
A join that returns only the rows where the join condition is satisfied in both tables. Rows without a match in either table are excluded from the result.
A join that returns all rows from the left table and the matching rows from the right table, filling unmatched right-side columns with nulls. It is used to keep every left-side row regardless of matches.
A join that returns all rows from both tables, matching them where possible and filling in nulls where no match exists. It combines the behavior of left and right outer joins.
A join that produces the Cartesian product of two tables, pairing every row of the first with every row of the second. It is rarely intended and often signals a missing join condition.
A join in which a table is joined to itself, using aliases to treat it as two separate tables. It is used to compare rows within the same table, such as finding an employee's manager.
A named, stored query that presents data as if it were a table but does not itself hold data. Views simplify complex queries, restrict access to columns and provide a stable interface over changing tables.
A precompiled set of SQL statements stored in the database and executed by name, often with parameters. Stored procedures encapsulate business logic and can improve performance and security.
A procedure that automatically runs in response to events such as inserts, updates or deletes on a table. Triggers are used to enforce rules, maintain audit trails or cascade changes.
A request to retrieve or manipulate data in a database, usually written in SQL. Queries can read, insert, update or delete data and can combine data from multiple tables.
The SQL command used to retrieve rows from one or more tables, optionally filtering, grouping, sorting and limiting the results. It is the most common operation in querying a database.
The part of a SQL statement that filters rows by specifying conditions each returned row must satisfy. It is evaluated before grouping and aggregation.
A SQL clause that collapses rows sharing the same values in specified columns into summary rows, typically used with aggregate functions. It produces one output row per group.
A SQL clause that filters groups produced by GROUP BY based on aggregate conditions. Unlike WHERE, it is applied after grouping and can reference aggregate values.
A function such as COUNT, SUM, AVG, MIN or MAX that computes a single value from a set of rows. Aggregate functions are commonly used with GROUP BY to summarize data.
A function that computes a value across a set of rows related to the current row without collapsing them into a single output row. Examples include running totals, rankings and moving averages.
A query nested inside another query, used to produce a value, a set of values or a derived table for the outer query. Subqueries can appear in SELECT, FROM, WHERE and other clauses.
A named temporary result set defined with a WITH clause that can be referenced within a single query. CTEs improve readability and can be recursive to handle hierarchical data.
The subset of SQL used to define and modify database structure, including CREATE, ALTER and DROP statements. DDL changes the schema rather than the data itself.
The subset of SQL used to work with the data in tables, including INSERT, UPDATE, DELETE and SELECT. DML operates on rows rather than on structure.
The subset of SQL used to manage permissions and access rights, mainly the GRANT and REVOKE statements. DCL governs who may perform which operations on database objects.
The subset of SQL used to manage transactions, including COMMIT, ROLLBACK and SAVEPOINT. TCL controls when changes become permanent or are undone.
A SQL command that combines insert, update and delete logic in one operation by matching source rows against a target. It is commonly used to implement upserts and synchronize tables.
A technique and set of libraries that map database tables to objects in application code, so developers work with objects instead of writing SQL directly. It speeds development but can obscure query performance.
A security vulnerability where untrusted input is inserted into a SQL statement, letting an attacker alter the query's logic. It is prevented by using parameterized queries rather than concatenating input.
A query in which user-supplied values are passed as separate parameters rather than embedded in the SQL text. It prevents SQL injection and lets the database reuse execution plans.
A database object that lets a program iterate over the rows of a result set one at a time. Cursors are useful for row-by-row processing but are often slower than set-based operations.
Pivot rotates rows into columns to summarize data across categories, while unpivot turns columns back into rows. These reshaping operations are common in reporting and preparing data for analysis.
The logic SQL uses for conditions involving null, where a comparison can be true, false or unknown. It explains why expressions like a column equals null do not behave as intuitively expected and require IS NULL.
A SQL command that quickly removes all rows from a table without logging each row deletion individually. It is faster than DELETE for emptying a table but cannot be filtered and usually cannot be rolled back in all systems.
SQL grouping extensions that generate subtotals and grand totals across combinations of grouping columns in a single query. Rollup produces hierarchical subtotals while cube produces subtotals for all combinations.
Converting a value from one data type to another, either implicitly by the database or explicitly in a query. Incorrect casting can cause errors, lost precision or unexpected results.
Combining many data points into summary values such as totals, averages or counts over groups. It is fundamental to reporting and reduces detailed data into meaningful measures.
A query syntax for selecting and extracting values from within a JSON document by navigating its structure. Databases and tools use it to work with fields inside semi-structured JSON columns.
A key derived from data that already has meaning in the real world, such as an email address or a social security number. Natural keys can change over time, which is a common reason to prefer surrogate keys.
An artificial identifier, often an auto-incrementing integer or UUID, assigned to each row with no business meaning. Surrogate keys stay stable even when business attributes change.
The process of organizing columns and tables to reduce redundancy and improve integrity by splitting data into related tables. It follows a series of normal forms that each remove a class of duplication or anomaly.
The deliberate introduction of redundancy by combining tables or duplicating columns to speed up reads. It trades storage and update complexity for faster queries, common in analytical and reporting systems.
A table is in first normal form when every column holds a single atomic value and there are no repeating groups or arrays within a cell. It is the baseline requirement for relational structure.
A table is in second normal form when it is in 1NF and every non-key column depends on the whole primary key, not just part of it. It removes partial dependencies on composite keys.
A table is in third normal form when it is in 2NF and no non-key column depends on another non-key column. It removes transitive dependencies to further reduce redundancy.
In data modeling, the number of relationships between rows of two tables, such as one-to-one, one-to-many or many-to-many. In query tuning, it also refers to the number of distinct values in a column, which influences index effectiveness.
A relationship in which a single row in one table can be associated with many rows in another, but each of those rows relates back to only one. It is the most common relationship type, implemented with a foreign key.
A relationship in which rows in one table can relate to many rows in another and vice versa. It is implemented with a junction table that holds foreign keys to both sides.
A table that resolves a many-to-many relationship by holding pairs of foreign keys that link two other tables. It is also called a bridge, associative or join table.
A visual representation of the entities in a data model and the relationships between them. ERDs help design and communicate database schemas before implementation.
A high-level, technology-independent model that identifies the main entities and their relationships without specifying columns or types. It captures business concepts before physical design.
A model that defines entities, attributes, keys and relationships in detail without tying them to a specific database technology. It sits between the conceptual and physical models.
A model that specifies how data is actually implemented in a particular database, including tables, column types, indexes and storage details. It is derived from the logical model.
A dimensional modeling design with a central fact table connected directly to several denormalized dimension tables, resembling a star. It is simple to query and common in data warehouses.
A dimensional design in which dimension tables are normalized into multiple related tables, forming a branching shape. It reduces redundancy at the cost of more joins than a star schema.
The central table in a dimensional model that stores measurable business events, such as sales or shipments, along with foreign keys to dimensions. It typically holds numeric measures and grows large over time.
A table that stores descriptive context for the facts, such as customers, products, dates or locations. Dimensions provide the attributes used to filter, group and label analytical results.
A dimension whose attribute values change gradually over time, requiring a strategy to track history. Type 1 overwrites the old value, Type 2 keeps history by adding new versioned rows, and Type 3 stores limited prior values in extra columns.
The level of detail represented by a single row in a fact table, such as one row per order line or one row per day. Defining the grain clearly is a foundational step in dimensional modeling.
A numeric value in a fact table that can be aggregated, such as revenue, quantity or duration. Measures are the quantities analysts sum, average or otherwise summarize.
A data modeling methodology for warehouses that separates data into hubs for business keys, links for relationships and satellites for descriptive attributes. It is designed for auditability, flexibility and loading from many sources.
The practice of defining how data is structured, related and constrained to meet an application's or organization's needs. It produces conceptual, logical and physical models that guide database design.
An inconsistency that arises when redundant data is stored in multiple places and only some copies are updated. Normalization removes such anomalies by storing each fact once.
A table that automatically keeps a history of its row versions with the time periods during which each was valid. It enables querying data as it existed at any past point in time.
A warehouse design technique that organizes data into fact and dimension tables to make analytical queries intuitive and fast. It favors understandability and query performance over strict normalization.
A dimension attribute, such as an invoice or order number, that is stored in the fact table itself because it has no other attributes. It provides a useful grouping key without a separate dimension table.
A dimension that is shared consistently across multiple fact tables or data marts with the same meaning and keys. Conformed dimensions let analysts combine and compare facts across subject areas.
A single wide table that combines data which would otherwise be spread across several normalized tables. Flat tables simplify and speed up analytical queries by avoiding joins.
A fact table measure that can be meaningfully summed across all dimensions, such as sales amount. Measures that can be summed only across some dimensions are semi-additive, and those that cannot be summed at all are non-additive.
A table used in dimensional models to handle many-to-many relationships between a fact and a dimension or between dimensions. It stores the associations, sometimes with weighting factors for allocation.
The set of all valid values that a given attribute or column is allowed to take. Defining domains, such as a status limited to a fixed list, enforces meaning and prevents invalid entries.
An entity that cannot be uniquely identified by its own attributes alone and depends on a related owner entity for identity. Its key combines a partial key with the owner's key.
An entity that represents a relationship between two or more other entities, often carrying its own attributes. It is the modeling counterpart of a junction table resolving a many-to-many relationship.
A nested document stored inside another document in a document database, keeping related data together for fast retrieval. It is the document model's alternative to a join across separate collections.
A dimension that consolidates several low-cardinality flags and indicators into a single table to avoid cluttering the fact table. It keeps the model tidy by grouping miscellaneous attributes.
A single dimension table referenced multiple times in one fact table under different roles, such as an order date, ship date and delivery date all drawn from a date dimension. Views or aliases give each role a distinct name.
A fact table that records the occurrence of events or relationships without any numeric measures, capturing that something happened. It is used for tracking events like attendance or coverage.
A fact table design that tracks the progress of a process with defined milestones, updating the same row as each step completes. It suits pipelines like order fulfillment with a clear start and end.
An abstraction that defines business metrics and entities in consistent terms over the underlying tables, so reporting tools share the same definitions. It decouples business meaning from physical structure.
Standard columns added to tables to record provenance and timing, such as created-at, updated-at and source identifiers. They support debugging, incremental loads and lineage.
Marking a row as deleted with a flag or timestamp instead of physically removing it. It preserves history and allows recovery, at the cost of needing to filter out deleted rows in queries.
A data integration process that extracts data from sources, transforms it into a target structure, and then loads it into a destination. Transformation happens before loading, often on a dedicated processing engine.
A data integration process that loads raw data into the destination first and performs transformations there using its compute power. ELT is common with cloud data warehouses that can scale transformation workloads.
In ETL the data is transformed before it reaches the target, while in ELT it is loaded raw and transformed inside the target system. ELT takes advantage of scalable warehouse compute and keeps raw data available, whereas ETL can reduce the data landed and apply logic before storage.
A series of connected processing steps that move data from one or more sources to one or more destinations, often transforming it along the way. Pipelines can run on schedules, in response to events, or continuously.
Processing data in large grouped sets on a schedule or when a volume threshold is reached, rather than one record at a time. It is efficient for high throughput but introduces latency between data arrival and availability.
Processing data continuously as individual events or small micro-batches arrive, enabling near real-time results. It suits use cases like fraud detection and live dashboards where low latency matters.
Batch processing handles data in scheduled chunks with higher latency and simpler logic, while streaming processes events continuously for low-latency results. The choice depends on how fresh the results must be and how the data arrives.
A technique that identifies and captures changes made to source data so that only the inserts, updates and deletes are propagated downstream. It keeps targets in sync efficiently without reloading entire datasets.
The process of collecting data from sources and bringing it into a storage or processing system. Ingestion can be done in batch or in real time and is the first stage of most pipelines.
The step of converting data from its source form into a structure, format or set of values suitable for the target system. It includes cleaning, filtering, joining, aggregating and reshaping data.
The coordination of the tasks in a data pipeline, controlling their order, dependencies, scheduling, retries and monitoring. Orchestration tools ensure steps run in the right sequence and handle failures.
A graph of tasks connected by dependencies with no cycles, used to model the order in which pipeline steps run. Because it has no loops, every task can be reached and completed in a finite sequence.
The property that running an operation multiple times produces the same result as running it once. Idempotent pipeline steps can be safely retried after failures without creating duplicates or corrupting data.
The process of running a pipeline over historical data to populate or correct records for past periods. Backfills are common when adding a new data source or fixing a logic error.
A marker tracking how far a pipeline has processed, often a timestamp or event position, so that only new data is handled on the next run. Watermarks support incremental processing and late-data handling in streams.
A holding location for messages or records that could not be processed successfully after retries. It isolates problematic data so the main pipeline keeps running while failures are investigated.
A buffer that holds messages between producers and consumers so they can operate independently and at different speeds. Queues decouple components and smooth out spikes in load.
Loading only the data that has changed since the last run instead of reloading everything. It reduces processing time and resource use compared with full loads.
Loading the entire dataset from source to target, replacing existing data. It is simple and ensures completeness but is costly for large or frequently updated datasets.
An intermediate storage location where raw extracted data lands before it is cleaned, transformed and loaded into the final target. It isolates source extraction from transformation and allows reprocessing.
Software that helps build, run and manage extract, transform and load processes, often with visual pipelines, connectors and scheduling. These tools reduce the custom code needed to move and reshape data.
A system that triggers pipeline tasks at defined times or on events and manages their dependencies and retries. It ensures jobs run in order and alerts operators when they fail.
Writing the results of a computation to storage so they can be reused instead of recomputed on each request. It trades storage and refresh effort for faster reads.
The part of an ELT setup where raw loaded data is modeled into cleaned, business-ready tables inside the warehouse, often using SQL. It separates raw ingestion from analytics-ready outputs.
A pipeline operation that enriches or validates incoming records by matching them against a reference dataset to retrieve related values. It is commonly used to resolve codes to descriptions or to attach keys.
Data that reaches a pipeline after the time window it logically belongs to has already been processed. Handling it requires strategies such as reprocessing, watermarks or dedicated update logic.
A guarantee that each record affects the result precisely once despite retries or failures, avoiding both loss and duplication. It is harder to achieve than at-least-once or at-most-once semantics and usually relies on idempotency and transactional sinks.
A messaging guarantee that every record is delivered one or more times, so none are lost but duplicates are possible. Consumers must handle duplicates, often by making processing idempotent.
The hands-on work of cleaning, structuring and enriching raw data into a form suitable for analysis. It is often iterative and exploratory, overlapping with cleansing and transformation.
A relationship specifying that one pipeline task must complete before another can start. Dependencies form the structure of a pipeline's directed acyclic graph.
The scheduled time period during which a batch load or refresh is allowed to run, often chosen for low system usage. Jobs that exceed their window can delay availability and downstream processing.
The lookup step in a warehouse load that translates natural keys from source data into the surrogate keys used by dimensions. It resolves the correct dimension version, which matters for slowly changing dimensions.
The destination where a data pipeline writes its output, such as a database table, file store or message topic. The counterpart to a source, it is the endpoint of the data flow.
A named channel in a publish-subscribe messaging system to which producers send records and from which consumers read. Topics organize streams of events by category or source.
A continuous, ordered sequence of records representing things that happened over time. Event streams are the backbone of real-time pipelines and event-driven architectures.
Grouping streaming events into bounded time intervals, such as five-minute tumbling windows, so aggregates can be computed over otherwise unbounded streams. Window types include tumbling, sliding and session windows.
A unique token attached to a request or record so that repeated submissions are recognized and processed only once. It is a practical mechanism for achieving safe retries in pipelines and APIs.
Designing a pipeline to accept inputs such as dates or source names so one definition can be reused across many runs and datasets. It reduces duplication and supports backfills and multi-tenant loads.
In modern ELT workflows, a reusable SQL definition that produces a derived table or view from source tables inside the warehouse. These models are versioned, tested and chained to build analytics-ready data.
The rules governing how a failed task is reattempted, including the number of retries and the delay between them. A sound retry policy combined with idempotency makes pipelines resilient to transient errors.
A retry strategy that progressively increases the wait between attempts, often with randomized jitter. It reduces load on a struggling system and avoids synchronized retry storms.
A database that stores data by column rather than by row, which speeds up analytical queries that scan many rows but few columns. Column storage also enables strong compression.
A central repository of integrated, structured data optimized for analytical queries and reporting across an organization. Data is cleaned and modeled before loading, and the schema is defined on write.
A storage repository that holds large volumes of raw data in its native format, including structured, semi-structured and unstructured data. Structure is applied on read, offering flexibility at the cost of governance effort.
An architecture that combines the low-cost, flexible storage of a data lake with the management features and performance of a data warehouse. It adds schema enforcement, transactions and governance on top of open file formats.
A subset of a data warehouse focused on a single subject area, department or line of business. It gives a specific group faster, simpler access to the data that matters to them.
A workload pattern focused on complex queries that aggregate large volumes of data for analysis and reporting. OLAP systems are optimized for reads over many rows and often use denormalized or columnar structures.
A plain-text format that stores tabular data with rows on separate lines and fields separated by commas. It is simple and widely supported but lacks a standard for data types, encoding and escaping.
A text-based format that represents structured data as nested objects and arrays of key-value pairs. It is human-readable, language-independent and widely used for APIs and semi-structured storage.
A text format that encodes data in nested, tagged elements and supports schemas for validation. It is verbose compared to JSON but remains common in enterprise and document-oriented systems.
An open columnar storage format designed for efficient analytical processing of large datasets. By storing data by column, it enables strong compression and lets queries read only the columns they need.
An open row-based data serialization format that stores the schema alongside the data and supports schema evolution. It is compact, fast to serialize and common in streaming and messaging systems.
An open columnar file format optimized for large-scale analytics, offering high compression and built-in indexes and statistics. It is widely used in the Hadoop and big data ecosystem.
CSV is a simple, human-readable row format with no types or compression, while Parquet is a typed, compressed columnar format built for analytics. Parquet is far faster and cheaper for large analytical scans, whereas CSV is easier to produce and inspect.
The scheme that maps characters to the bytes used to store and transmit text, such as UTF-8 or Latin-1. Mismatched encodings cause garbled characters, so consistent encoding is essential in data exchange.
A variable-width character encoding that can represent every Unicode character while remaining backward compatible with ASCII. It is the dominant encoding for the web and modern data interchange.
The process of converting in-memory data structures into a format that can be stored or transmitted, such as JSON, Avro or a binary blob. Deserialization is the reverse process of reconstructing the structure.
Datasets so large, fast-moving or varied that traditional tools struggle to store and process them, often described by the dimensions of volume, velocity and variety. It drove the adoption of distributed storage and processing frameworks.
An approach where raw data is stored as-is and structure is applied only when it is queried. It offers flexibility and is typical of data lakes, at the cost of pushing interpretation to read time.
An approach where data must conform to a defined schema before it is stored. It enforces structure and quality up front and is typical of relational databases and warehouses.
Object storage designed to hold large unstructured binary objects such as files, images and backups, each addressed by a key. It is scalable and cost-effective and often backs data lakes.
A storage architecture that manages data as objects, each bundling the data with metadata and a unique identifier, in a flat namespace. It scales massively and is the foundation of cloud data lakes.
Encoding data to use less storage space and reduce transfer time, using algorithms that may be lossless or lossy. Columnar formats achieve high lossless compression because similar values are stored together.
A layered data design that refines data through bronze (raw), silver (cleaned and conformed) and gold (business-level aggregated) stages. Each layer increases quality and usability for consumers.
An organizational and architectural approach that treats data as a product owned by decentralized domain teams rather than a central platform team. It emphasizes domain ownership, self-service infrastructure and federated governance.
Moving older, infrequently accessed data to cheaper, long-term storage while keeping it available if needed. Archiving reduces the cost and size of active systems without discarding data.
A warehouse stores cleaned, structured data modeled for analytics with schema-on-write, while a lake stores raw data of any type cheaply with schema-on-read. Warehouses optimize for fast, governed reporting, whereas lakes optimize for flexible, low-cost storage of all data.
Row storage keeps all of a row's values together, which suits transactional reads and writes of whole records, while columnar storage groups each column's values, which suits analytical scans and compression. The choice reflects whether the workload is transactional or analytical.
A text file format in which each field occupies a predetermined number of character positions rather than being separated by delimiters. It is common in legacy and mainframe data exchanges.
A character or sequence that separates fields or records in a text file, such as a comma, tab or pipe. Correctly handling delimiters, quoting and escaping is essential to parse files reliably.
An optional sequence of bytes at the start of a text file that signals its Unicode encoding and byte order. An unexpected BOM can corrupt the first field when parsing files such as CSV.
A way of representing a point in time as the number of seconds or milliseconds elapsed since a fixed reference, commonly 1970-01-01 UTC. It is compact and unambiguous but must be converted for human readability.
A read-consistent copy of data captured at a specific point in time. Snapshots support reporting, backups and comparisons without being affected by ongoing changes.
A marker that records a row has been deleted, used in distributed and log-based systems so the deletion propagates to all replicas. Tombstones are later purged during compaction.
Logical areas within a data lake that separate data by processing stage, such as raw, cleansed and curated. Zones bring order and governance to otherwise unstructured lake storage.
A specification such as Apache Iceberg, Delta Lake or Apache Hudi that adds table semantics, transactions and schema management on top of files in a data lake. It enables reliable updates and time travel over open formats like Parquet.
A feature of some table formats and warehouses that lets queries read data as it existed at an earlier version or timestamp. It supports auditing, debugging and recovering from bad writes.
The stage in a pipeline or lakehouse that stores source data in its original, untransformed form. Keeping a faithful raw copy allows reprocessing and auditing when logic changes.
The stage holding cleaned, conformed and business-ready data that analysts and applications consume. It sits above the raw and intermediate layers and reflects agreed definitions and quality rules.
A data structure that speeds up lookups and sorting by maintaining an ordered reference to values in one or more columns. Indexes improve read performance at the cost of extra storage and slower writes.
An index that determines the physical order in which rows are stored on disk, so the table data is kept sorted by the index key. A table can have only one clustered index.
A separate structure that stores index keys with pointers to the underlying rows, leaving the table order unchanged. A table can have many non-clustered indexes.
An index built on two or more columns, useful when queries filter or sort by that combination. The column order matters because the index is most effective for leading-column predicates.
An index that includes all the columns a query needs, allowing the database to answer the query from the index alone without reading the table. This avoids extra lookups and speeds up reads.
A situation where two or more transactions each hold a lock the other needs, so none can proceed. Databases detect deadlocks and resolve them by aborting one transaction as a victim.
A concurrency mechanism that temporarily restricts access to data so transactions do not interfere with each other. Locks vary in scope, from a single row to an entire table, and in mode, such as shared or exclusive.
A technique that lets readers see a consistent snapshot of data while writers make changes by keeping multiple versions of rows. It reduces blocking because reads do not wait for writes.
A view whose results are physically stored and periodically refreshed, unlike a regular view that computes results on demand. It speeds up expensive queries at the cost of potentially stale data.
Dividing a table or dataset into smaller segments based on a key such as date or region, stored and queried separately. Partitioning improves performance by letting queries scan only relevant segments.
Partitioning splits data into segments that usually live within one database instance, while sharding distributes those segments across separate servers. Sharding adds horizontal scale and complexity that plain partitioning does not.
Maintaining copies of data on multiple servers to improve availability, fault tolerance and read performance. Replication can be synchronous or asynchronous and typically follows a primary-replica or multi-primary model.
A replication model in which one primary node accepts writes and propagates changes to one or more read-only replicas. It scales read traffic and provides failover candidates if the primary fails.
Increasing capacity by adding more machines and distributing the workload across them. It is also called scaling out and is the basis for sharding and distributed databases.
Increasing capacity by adding more resources such as CPU, memory or storage to a single machine. It is also called scaling up and is simpler but limited by the largest available hardware.
Storing frequently accessed data in a fast layer, such as memory, so repeated requests avoid slower lookups. Caching improves performance but requires strategies to keep cached data fresh.
Reusing a managed set of open database connections across many requests instead of opening a new connection each time. It reduces overhead and protects the database from being overwhelmed by connection churn.
The database component that analyzes a query and chooses an efficient execution plan among the possible ways to run it. It estimates costs using statistics about table sizes and data distribution.
The sequence of operations the database uses to run a query, such as scans, joins and sorts. Examining the plan helps diagnose slow queries and guide indexing decisions.
An operation that reads every row of a table to satisfy a query, usually because no suitable index exists. It can be slow on large tables and is a common target for optimization.
A database whose data is stored and processed across multiple networked machines that appear as a single system. Distribution improves scalability and fault tolerance but adds consistency and coordination challenges.
A principle stating that a distributed system can guarantee at most two of consistency, availability and partition tolerance at the same time. Because network partitions are unavoidable, designs typically trade consistency against availability.
A consistency model in which replicas may temporarily differ but converge to the same state if no new updates occur. It favors availability and performance over immediate consistency across nodes.
A model guaranteeing that every read returns the most recent committed write, so all clients see the same data at the same time. It simplifies reasoning but can reduce availability and increase latency in distributed systems.
A query optimization that skips partitions which cannot contain matching rows, so only relevant segments are scanned. It dramatically reduces the data read for queries that filter on the partition key.
A compact probabilistic structure that can quickly test whether an element is definitely not in a set or possibly in it. Databases use it to skip data blocks that cannot contain a sought value, with no false negatives.
An index that stores and compresses data by column to accelerate analytical queries over large tables. It contrasts with traditional rowstore indexes optimized for transactional access.
An uneven distribution of data across partitions or nodes, where some hold far more than others. Skew creates hotspots that slow distributed processing and unbalance load.
A node, partition or key range that receives a disproportionate share of traffic or data, becoming a bottleneck. Choosing good partition and shard keys helps avoid hotspots.
The time between issuing a query and receiving its results. Reducing latency is a primary goal of indexing, caching, partitioning and query optimization.
The amount of work a system completes per unit of time, such as transactions or rows processed per second. It measures capacity and is distinct from latency, which measures per-request speed.
The column or expression whose values determine how rows are distributed among partitions. Choosing an effective partition key is essential for balanced load and efficient query pruning.
A protocol that coordinates a transaction across multiple systems by first asking all to prepare and then telling them all to commit or abort. It ensures atomicity in distributed transactions at the cost of latency and blocking.
A copy of a database kept in sync with the primary that serves read-only queries to offload the primary. It improves read scalability but may lag slightly behind due to replication delay.
The automatic or manual switch to a standby system when the active database fails, to maintain availability. A well-designed failover minimizes downtime and data loss.
A design goal of keeping a database operational and accessible with minimal downtime, typically through redundancy and automatic failover. It is measured as a percentage of uptime over a period.
A measure of how well a column's values narrow down rows, where high selectivity means few rows share each value. Highly selective columns make the most effective indexes.
Metadata the database keeps about data distribution, such as value counts and ranges, used by the optimizer to estimate costs. Stale statistics can lead to poor execution plans.
The condition where an index's logical order no longer matches its physical storage order, reducing efficiency after many changes. Rebuilding or reorganizing the index restores performance.
A maintenance operation that reclaims storage occupied by deleted or obsolete row versions and updates internal bookkeeping. It keeps tables from bloating in systems that use multiversion concurrency control.
A background process that merges and rewrites storage files to remove obsolete versions and reclaim space. It is common in log-structured and columnar storage systems to maintain read performance.
An optimization that uses per-block metadata such as min and max values to avoid reading blocks that cannot satisfy a query. It reduces I/O for selective queries over large datasets.
Applying a function that maps data of any size to a fixed-size value, used for integrity checks, deduplication, partitioning and indexing. A good hash spreads values evenly and makes collisions unlikely.
An index that uses a hash of the key to locate rows, giving very fast exact-match lookups. It does not support range queries because hashing does not preserve order.
The most common index structure, a balanced tree that keeps keys sorted to support fast equality and range lookups. It underlies default indexing in most relational databases.
A query execution that satisfies a request entirely from an index without touching the table rows. It is possible when the index contains every column the query needs and improves performance.
The optimizer's prediction of how many rows an operation will produce, used to choose an execution plan. Bad estimates, often from stale statistics, lead to inefficient plans.
The process of identifying and removing duplicate records so that each real-world entity is represented once. It often relies on matching rules or keys to decide which records refer to the same thing.
The process of detecting and correcting inaccurate, incomplete or inconsistent data, such as fixing typos, standardizing formats and filling or removing bad values. It improves the reliability of downstream analysis.
The degree to which data is accurate, complete, consistent, timely and fit for its intended use. It is measured against defined dimensions and maintained through validation, monitoring and cleansing.
The analysis of data to summarize its structure, content and quality, including value distributions, patterns, nulls and uniqueness. Profiling reveals issues and informs mapping and cleansing decisions.
Checking that data meets defined rules and expectations, such as correct types, allowed ranges, required fields and referential consistency. Validation catches bad data before it propagates downstream.
The framework of policies, roles, standards and processes that control how data is managed, secured and used across an organization. It defines accountability for data quality, access and compliance.
A person responsible for the quality, definitions and proper use of a specific set of data within an organization. Stewards act as the point of accountability between data producers and consumers.
The core, shared business entities that are referenced across systems, such as customers, products, suppliers and accounts. Keeping master data consistent is the goal of master data management.
The discipline and tooling for creating a single, trusted, consistent view of an organization's core entities across systems. It resolves duplicates and conflicting values to produce authoritative golden records.
The single authoritative, deduplicated version of an entity assembled from multiple sources. It represents the best available truth for a customer, product or other master data entity.
Standardized sets of permitted values used to classify or constrain other data, such as country codes, currency codes or status lists. Reference data changes slowly and is shared widely to ensure consistency.
Data that describes other data, such as its structure, meaning, origin, format and ownership. Metadata makes data discoverable, understandable and governable.
A centralized reference that documents the definitions, data types, allowed values and relationships of the fields in a dataset or database. It helps users understand and use data consistently.
An organized, searchable inventory of an organization's data assets enriched with metadata, descriptions and ownership. It helps people discover, understand and trust available data.
A record of where data originates and how it moves and transforms through systems to its destination. Lineage supports debugging, impact analysis, compliance and trust in reported numbers.
Any data that can identify a specific individual, either alone or combined with other data, such as name, address, email or government ID. Handling PII carries legal and security obligations.
Replacing sensitive values with realistic but fictitious ones so data can be used safely in testing, development or analytics. Masking preserves format and usability while protecting the real values.
Irreversibly transforming data so that individuals can no longer be identified from it, even when combined with other data. Unlike pseudonymization, properly anonymized data cannot be re-linked to a person.
Replacing identifying fields with artificial identifiers while keeping a separate mapping that can restore the original values. It reduces exposure but remains reversible for authorized parties.
Protecting stored data by encrypting it so it is unreadable without the proper keys. It guards against unauthorized access to disks, backups and files.
Protecting data as it moves across networks by encrypting the connection, commonly with TLS. It prevents interception and tampering between systems.
Comparing records that are similar but not identical to decide whether they refer to the same entity, tolerating typos, abbreviations and formatting differences. It is central to deduplication and entity resolution.
The process of determining which records across one or more datasets refer to the same real-world entity and linking or merging them. It combines matching rules, fuzzy comparison and sometimes machine learning.
A measure of how up to date data is relative to the real-world events it represents. It depends on pipeline latency and is a key service-level expectation for analytics and operations.
The practice of monitoring the health of data and pipelines across dimensions like freshness, volume, schema and distribution to detect issues early. It aims to catch broken or anomalous data before it reaches consumers.
Selecting a representative subset of a dataset to analyze or test against, instead of processing all of it. Sampling speeds up exploration and validation when full processing is costly.
A rule defining how long data is kept before it is archived or deleted. Retention policies balance analytical and legal needs against storage cost and privacy obligations.
A design principle in which each data element is mastered in exactly one authoritative place that all systems rely on. It prevents conflicting versions of the same information.
Automatically identifying data points or patterns that deviate significantly from expected behavior. In data pipelines it flags unexpected volumes, values or distributions that may indicate quality problems.
A database feature that restricts which rows a user can see or modify based on policies tied to their identity or attributes. It enforces access control within a shared table rather than through separate copies.
Access control that restricts which columns a user can read or modify, often to protect sensitive fields. It lets different users share a table while hiding certain attributes.
A chronological record of who accessed or changed data and when, used for security, compliance and troubleshooting. Audit trails support accountability and investigation of incidents.
The practice of storing and converting timestamps with awareness of time zones, often by keeping values in UTC and converting for display. Mishandling time zones causes off-by-hours errors and incorrect ordering.
A defined, testable expectation about data, such as a field being non-null, unique or within a range. Rules are evaluated during validation to accept, flag or reject data.
A data quality dimension measuring whether all required data is present, with no missing records or fields. Low completeness leads to gaps and biased analysis.
A data quality dimension measuring how closely data reflects the real-world values it represents. Inaccurate data leads to wrong conclusions even when it is complete and consistent.
A data quality dimension measuring whether data agrees with itself across records and systems, with no contradictions. Consistent data uses the same values and formats for the same facts everywhere.
Converting data into a common, consistent format, such as uniform date formats, casing or units of measure. It is a key cleansing step that makes data comparable and joinable.
Transforming differing representations of the same value into one canonical form, such as mapping state names and abbreviations to a single code. It is distinct from database normalization and supports matching and deduplication.
A profiling metric expressing the fraction of distinct values in a column relative to its total rows. It helps identify candidate keys and detect unexpected duplication.
A gradual change over time in the statistical properties or distribution of data values, as opposed to structural schema drift. It can silently degrade models and analytics that assumed earlier patterns.
Setting aside records that fail quality or validation checks so they do not enter the main dataset while they are reviewed or corrected. It keeps bad data from contaminating trusted outputs.
Lineage captured at the granularity of individual columns, showing how each target column is derived from specific source columns. It enables precise impact analysis when a field changes.
A service-level agreement specifying commitments for data delivery, such as freshness, availability and quality thresholds. It sets expectations between data producers and consumers.
A file extension commonly used for backup files, notably for native Microsoft SQL Server database backups. A .bak file can be restored to recreate the database it was taken from.
The process of defining how fields in a source system correspond to fields in a target system. Accurate mapping is essential for correct integration, migration and transformation.
The process of moving data from one system, format or storage location to another, often as part of a system replacement or upgrade. It typically involves extraction, mapping, transformation, loading and validation.
Combining data from multiple sources into a unified, consistent view for analysis or operations. It addresses differences in structure, format, identifiers and semantics across systems.
Unexpected or unmanaged changes to the structure of source data over time, such as added, removed or renamed columns. It can break pipelines that assume a fixed schema if not detected and handled.
The managed process of changing a schema over time while keeping compatibility with existing data and consumers. Formats like Avro and Parquet support rules for adding or changing fields safely.
Defining correspondences between the structures of two different schemas so data can be translated from one to the other. It is a core step when integrating or migrating between systems with different models.
An operation that inserts a new row if it does not exist and updates the existing row if it does, based on a key. It simplifies keeping a target in sync without separate insert and update logic.
Any system, file, feed or service from which data originates for ingestion or integration, such as a database, API, log or spreadsheet. Understanding each source's structure and reliability is key to building pipelines.
Connecting systems by exchanging data through application programming interfaces, often over HTTP using REST or GraphQL. APIs provide a controlled, programmatic way to read from and write to external services.
A formal, agreed specification of the structure, meaning, quality and delivery guarantees of data shared between a producer and consumers. It reduces breakage by making expectations explicit and enforceable.
Comparing data between source and target, or across systems, to confirm it matches and nothing was lost or altered. It is a key validation step in migrations and integrations.
Enhancing existing records by adding related information from internal or external sources, such as appending demographics or geocoding addresses. It increases the value and completeness of the data.
A centralized service that stores and manages the schemas used by messages in a streaming or messaging system. It enforces compatibility rules so producers and consumers can evolve independently.
Querying data that remains in multiple separate sources as if it were a single database, without physically moving it. A virtual layer translates and distributes the query to each source.
An integration approach that provides a unified, real-time view of data across sources through an abstraction layer, leaving the data in place. It avoids copying but depends on source availability and performance.
A text string that specifies the information needed to connect to a database, such as host, port, database name, credentials and options. Applications use it to establish a session with the DBMS.
Standard APIs that let applications connect to and query databases in a vendor-neutral way, with ODBC used broadly and JDBC used from Java. Drivers implement these standards for each specific database.
Inserting large volumes of data into a database using an optimized path that bypasses much of the per-row overhead. Bulk loading is far faster than individual inserts for initial loads and migrations.
Data that is isolated within a team, system or department and not easily accessible to the rest of the organization. Silos hinder integration and lead to inconsistent, duplicated information.
A versioned, repeatable change to a database's structure, such as adding a column or table, applied through migration scripts. Migrations keep schema changes consistent across environments and track their history.
A schema change script written so that running it more than once has the same effect as running it once, for example by checking whether an object already exists. It makes deployments safer to retry.
A script that reverses a schema change, restoring the database to its previous structure. Paired with the forward change, it allows a migration to be undone if problems arise.
Converting a textual address or place name into geographic coordinates such as latitude and longitude. It enriches data for mapping, proximity queries and spatial analysis.
A property of a schema or interface change that allows existing consumers to keep working without modification. Maintaining it, for example by only adding optional fields, prevents breaking downstream systems.
Analyzing two schemas to identify differences in tables, columns, types and constraints. It is used to synchronize environments and generate migration scripts.
An error or data corruption that occurs when a value does not conform to the expected data type of its target column or variable. It is a frequent source of failures in migrations and integrations.
The loss of data that happens when a value is too large for its target column and the excess is cut off. It commonly occurs when target text fields are shorter than source values during migration.
The agreed approach for dealing with missing values during integration, such as substituting defaults, propagating nulls or rejecting rows. Consistent null handling prevents surprises in downstream calculations.
A reconciliation check comparing the number of rows between source and target to confirm none were lost or duplicated during a move. It is a simple, quick first line of defense in migrations.
A computed value derived from data used to detect accidental changes or corruption during storage or transfer. Comparing checksums before and after a move confirms the data is intact.
A reusable component that knows how to read from or write to a specific system, handling its protocol, authentication and data format. Connectors let pipelines integrate many sources and targets without custom code each time.
A rule applied during integration that derives a target field from one or more source fields, such as concatenating names or converting units. Transformations are the active part of a data mapping.
A lookup table that maps identifiers or codes from one system to their equivalents in another. Crosswalks are essential when integrating or migrating between systems that use different coding schemes.
Keep Reading
Whether you need overflow capacity for one acquisition or a repeatable conversion partner across an active pipeline, we'll help you define the scope, inspect the source data, and build a practical path into Applied Epic.
Built for insurance agencies, brokerages, aggregators, and acquisition teams that need experienced conversion capacity without building another internal department.