BIDA flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Idempotent transformation

A transformation that produces the same output when run multiple times on the same input, enabling safe retries and reruns in automated pipelines.

Watermark

A stored maximum timestamp or ID marking the last processed record, used to select only new or changed rows on the next incremental load.

SCD Type 2

A slowly changing dimension technique that preserves history by inserting a new row with valid_from, valid_to, and is_current flags instead of overwriting.

DAG orchestration

A directed acyclic graph of tasks with dependencies; each task runs only after all upstream tasks succeed, preventing cycles and enabling parallel branches.

Partition pruning

An optimization where filtering on a partition column lets the query engine skip irrelevant files or partitions, reducing scanned data and runtime.

Data completeness

A data quality dimension measuring the proportion of required fields that are populated, i.e., the absence of missing or null values.

ELT vs ETL

ETL transforms data before loading into the target; ELT loads raw data first and transforms it inside the warehouse using the warehouse's compute.

Lie factor

The ratio of the size of an effect shown in a graphic to the size of the effect in the data; values far from 1.0 indicate visual distortion.

Data-ink ratio

The proportion of a chart's ink devoted to displaying data rather than decoration; maximizing it means removing gridlines, borders, and chartjunk.

Sequential color palette

A color scheme that varies in lightness or hue to encode ordered data, appropriate for numeric values that progress from low to high.

Small multiples

A visualization technique that repeats the same chart type across facets for different groups, enabling comparison without overplotting.

Bar chart zero baseline

A rule that bar charts must start the value axis at zero because bars encode magnitude by length; truncating exaggerates differences.

Diverging palette

A color scheme with two contrasting hues meeting at a neutral midpoint, used when data has a meaningful center such as zero or a target.

Third normal form

A table is in 3NF if it is in 2NF and has no transitive dependency, meaning non-key attributes depend only on the primary key.

Star schema

A dimensional model with one central fact table joined to denormalized dimension tables, optimizing query simplicity and performance.

Fact table grain

The level of detail represented by one row in a fact table, such as one row per order line, declared before choosing dimensions or measures.

Semi-additive measure

A fact measure that can be summed across some dimensions but not others, such as an account balance that cannot be summed over time.

Surrogate key

A system-generated integer with no business meaning used as a primary key, stable even when natural business identifiers change.

Bridge table

A table that resolves a many-to-many relationship between dimensions and facts by holding foreign keys to both sides, optionally with weights.

Snowflake schema

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

SQL logical order

The evaluation order FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT; SELECT aliases are not visible in WHERE.

COUNT and NULLs

COUNT(column) ignores NULL values, while COUNT(*) counts all rows, so the two differ whenever the column contains nulls.

LEFT JOIN

A join returning all rows from the left table plus matching rows from the right, with NULLs in right-side columns where no match exists.

DENSE_RANK

A window function that assigns ranks with no gaps after ties, so tied values share a rank and the next distinct value gets the next integer.

Pandas groupby

A pandas operation that splits a DataFrame by key, applies an aggregation such as sum or mean, and combines results into a new DataFrame.

COALESCE

A SQL function returning the first non-null argument, commonly used to replace nulls with a default value in transformations.

Central limit theorem

The sampling distribution of the mean approaches normality with mean μ and standard error σ/√n as sample size grows, regardless of population shape.

p-value

The probability of observing data at least as extreme as the sample, assuming the null hypothesis is true; reject H0 when p < α.

Type I error

Rejecting a true null hypothesis, a false positive, with probability α set by the significance level before the test.

Statistical power

The probability of correctly rejecting a false null hypothesis, equal to 1 minus the Type II error rate; it increases with sample size.

Bayes' theorem

P(A|B) = P(B|A)·P(A)/P(B); it updates a prior probability with evidence and shows why base rates matter for rare events.

Homoscedasticity

A regression assumption that the variance of residuals is constant across predicted values; violation is heteroscedasticity and distorts standard errors.