BIDA cheat sheet

ETL extracts from sources, transforms (clean, aggregate, join), and loads to a target; ELT loads raw data first and transforms in the warehouse using its compute.

Idempotency: a transformation is idempotent if re-running it on the same input produces the same output; use MERGE/upsert or partition overwrite to achieve it.

Incremental load watermark: store the max timestamp or ID processed; next run selects rows > watermark. Late-arriving records require a lookback window or dedup key.

SCD Type 1 overwrites the attribute (no history); Type 2 adds a new row with valid_from/valid_to and is_current flag; Type 3 adds a previous_value column.

Data quality dimensions: completeness (nulls), uniqueness (duplicates), validity (domain/format), consistency (cross-field), timeliness (freshness), accuracy (vs source of truth).

Orchestration DAG: tasks with dependencies; a task runs only after all upstream tasks succeed. Use retries with backoff and alert on SLA miss, not on every failure.

Partition pruning: filter on the partition column (e.g., date) in the WHERE clause so the engine reads only relevant files; avoid functions on the partition column.

Automation rule: separate extraction (source-specific) from transformation (business logic) so source changes do not ripple through the pipeline.

Chart selection: comparison → bar; trend over time → line; distribution → histogram/box; relationship → scatter; part-to-whole → stacked bar or treemap (pie only for ≤5 slices).

Lie factor = (size of effect shown in graphic) / (size of effect in data); values far from 1.0 indicate distortion.

Data-ink ratio: maximize ink used for data, minimize non-data ink (gridlines, borders, 3D effects); remove chartjunk.

Color: use sequential palettes for ordered data, diverging for data with a meaningful midpoint, categorical for unordered groups; never encode more than ~7 categories by color alone.

Axis rules: bar charts must start at zero; line charts may truncate the y-axis if clearly labeled, because they encode position not length.

Small multiples (faceting) compare groups without overplotting; sort categories by value, not alphabetically, when ranking is the message.

Accessibility: contrast ratio ≥ 4.5:1 for text; pair color with shape/label; avoid red-green only encoding.

Normalization: 1NF (atomic values, no repeating groups), 2NF (1NF + no partial dependency on part of a composite key), 3NF (2NF + no transitive dependency on non-key attributes).

Star schema: one fact table (measures, foreign keys) surrounded by denormalized dimension tables; snowflake schema normalizes dimensions into sub-dimensions.

Fact table grain: the level of detail of one row (e.g., one row per order line). Declare grain before choosing dimensions or measures.

Additive measures sum across all dimensions; semi-additive (e.g., balance) sum across some dimensions but not time; non-additive (e.g., ratios) cannot be summed.

Slowly changing dimensions: Type 2 keeps history with surrogate keys; the fact table joins on the surrogate key valid at event time.

Bridge table resolves many-to-many relationships between dimensions and facts; it contains foreign keys to both sides plus optional weighting.

Surrogate key vs natural key: surrogate is a system-generated integer with no business meaning; natural key is the business identifier and may change.

SQL logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT; aliases from SELECT are not visible in WHERE in standard SQL.

NULL handling: NULL = NULL is UNKNOWN; use IS NULL / IS NOT NULL; COUNT(col) ignores NULLs, COUNT(*) does not; COALESCE returns first non-null.

JOIN semantics: INNER returns matches; LEFT returns all left rows plus matches (NULLs where none); FULL OUTER returns all rows from both sides; CROSS JOIN is Cartesian product.

Window functions: ROW_NUMBER() assigns unique sequential numbers; RANK() leaves gaps after ties; DENSE_RANK() does not; PARTITION BY resets the window per group.

Python: list comprehension [f(x) for x in xs if cond(x)]; dict.get(key, default) avoids KeyError; pandas merge(how='left') mirrors SQL LEFT JOIN.

Pandas: df.groupby('k').agg({'v':'sum'}) aggregates; df.pivot_table(index, columns, values, aggfunc) reshapes; df.merge(on='key', how='inner') joins.

Idempotent code: avoid mutating shared state; prefer pure functions and explicit inputs/outputs so re-runs are safe.

Central limit theorem: the sampling distribution of the mean approaches normal with mean μ and standard error σ/√n as n grows, regardless of population shape (n ≳ 30 rule of thumb).

Confidence interval for a mean: x̄ ± z* · (s/√n) for large n; use t* when σ is unknown and n is small.

Hypothesis test: p-value is the probability of data at least as extreme as observed, assuming H0 is true; reject H0 when p < α. It is not the probability H0 is true.

Type I error (α) = false positive; Type II error (β) = false negative; power = 1 − β. Increasing n increases power and narrows CIs.

Correlation r ranges −1 to 1 and measures linear association only; correlation does not imply causation; r² is the proportion of variance explained in simple linear regression.

Bayes' theorem: P(A|B) = P(B|A)·P(A) / P(B); base rates matter-rare events yield many false positives even with accurate tests.

Regression assumptions: linearity, independence of errors, homoscedasticity (constant error variance), normality of residuals (for inference).

A/B test: randomize units, pre-register metric, compute required sample size from baseline rate, minimum detectable effect, α, and power before launching.