BIDA key terms

A pipeline pattern that pulls data from source systems, cleans and reshapes it in a staging layer, then writes the finished data into a target warehouse or store.

A pipeline pattern that loads raw source data into the target system first and performs transformation there, relying on the warehouse's own compute rather than a separate processing server.

An automated sequence of steps that moves data between systems, applying validation and transformation along the way, usually on a recurring schedule or triggered by events.

A property of an operation that produces the same end state whether it runs once or many times, which lets failed pipeline runs be safely retried without duplicating records.

Coordinating the order, dependencies, retries, and monitoring of many data tasks so that downstream steps start only after their upstream inputs succeed.

Detecting and correcting inaccurate, incomplete, duplicated, or malformed values so that analysis rests on trustworthy records rather than raw source noise.

Rules that check incoming records against expected formats, ranges, and referential constraints, flagging or rejecting rows that fail before they reach consumers.

Deciding how missing values are represented, detected, and treated in transformations, since nulls propagate through arithmetic and comparisons differently from ordinary values.

Converting a value from one data type to another, such as text to number or timestamp to date, which can silently fail or truncate if the source values are not well formed.

Unplanned changes in a source system's column names, types, or ordering that can break downstream loads and transformations if not detected and handled.

Structuring tables so each fact is stored once, removing redundancy and update anomalies by separating entities into related tables linked by keys.

Deliberately combining related tables or repeating values to reduce joins at query time, trading some redundancy and update risk for faster reads.

A column or set of columns whose values uniquely identify each row in a table and cannot be null, enforcing entity integrity.

A column that references the primary key of another table, enforcing that related records exist and preserving referential integrity across tables.

A system-generated identifier, often an integer or hash, assigned to each row independent of business data so that keys stay stable when source values change.

A dimensional design with one central fact table holding measures and keys, surrounded by denormalized dimension tables that describe the business entities.

A dimensional design in which dimension tables are normalized into related sub-dimensions, reducing redundancy at the cost of more joins in queries.

The central table in a dimensional model that stores quantitative measures and foreign keys pointing to the dimensions that give those measures context.

A table of descriptive attributes, such as customer or product details, used to filter, group, and label the measures stored in a fact table.

The level of detail represented by one row of a fact table, such as one order line or one daily snapshot, which determines what questions the table can answer.

A technique for tracking how dimension attributes change over time, either by overwriting old values or by keeping versioned rows with effective dates.

The number of distinct values a column contains, or the relationship pattern between two tables such as one-to-many, which guides indexing and join strategy.

An operation that combines rows from two tables based on a matching condition, with inner, left, right, and full variants controlling which unmatched rows survive.

Collapsing many rows into summary values using functions such as COUNT, SUM, AVG, MIN, and MAX, typically combined with GROUP BY to produce one result per group.

A SQL function that computes a value across a set of related rows while keeping each row in the output, useful for running totals, ranks, and row comparisons.

A named temporary result set defined with WITH at the start of a query, letting complex logic be broken into readable, reusable steps.

A query nested inside another query, used to filter rows, compute a scalar value, or build a derived table that the outer query then reads.

Language constructs such as conditionals, loops, and early exits that decide which statements execute and in what order within a program.

A named, reusable block of code that takes inputs, performs a task, and optionally returns a value, helping avoid duplication and isolate logic.

Catching and responding to runtime errors so a program can log the problem, clean up resources, or recover instead of crashing outright.

The mean is the arithmetic average and is sensitive to extreme values, while the median is the middle ordered value and resists skew from outliers.

A measure of how far values typically spread from their mean, expressed in the same units as the data and equal to the square root of variance.

The average squared deviation of values from their mean, quantifying dispersion but in squared units, which makes it harder to interpret directly.

An observation lying far from the rest of the distribution, which may signal data error or a genuine rare event and can distort averages and models.

A number between minus one and one summarizing the strength and direction of a linear association between two variables, without implying causation.

A model that fits a straight line relating one or more predictors to a continuous outcome by minimizing squared differences between observed and predicted values.

The probability of observing a result at least as extreme as the sample result if the null hypothesis were true, used as evidence against that hypothesis.

A range of plausible values for a population parameter, built from sample data at a stated confidence level such as ninety-five percent.

The share of a chart's ink devoted to actual data rather than decoration, gridlines, or borders; higher ratios generally yield clearer, less cluttered graphics.

Choosing a visual form that matches the question and data, such as bars for comparison, lines for trend over time, and scatter plots for relationships.

Using hue, lightness, or saturation to represent values, with sequential, diverging, and categorical palettes chosen to match the data's structure and remain accessible.

A grid of similar charts sharing scales and style, letting viewers compare patterns across categories without the clutter of overlapping series.