GBDS cheat sheet

Requirement types: business (why), stakeholder (who needs what), solution (what the system does), and transition (how to move from current to future state).

MoSCoW prioritization ranks scope as Must, Should, Could, Won't; Must items must all fit or the delivery date slips.

A use case describes actor, preconditions, main success scenario, alternate flows, and postconditions; it is behavior-focused, not a data definition.

Traceability matrix maps requirement ID to design element, code module, and test case; orphan requirements and untested requirements are both defects.

INVEST criteria for user stories: Independent, Negotiable, Valuable, Estimable, Small, Testable.

Acceptance criteria written as Given/When/Then (Gherkin) are testable and unambiguous; avoid subjective words like 'fast' or 'user-friendly'.

Elicitation techniques include interviews, workshops, observation, document analysis, surveys, and prototyping; each trades depth against breadth.

A business rule that constrains data (e.g., an order must have a customer) is enforced in the model, the application, or both - decide and document where.

Conceptual model = entities and relationships; logical model adds attributes, keys, and normalization; physical model adds tables, indexes, and data types.

Normal forms: 1NF removes repeating groups, 2NF removes partial dependencies on part of a composite key, 3NF removes transitive dependencies on non-key attributes.

A candidate key is any minimal unique identifier; the primary key is the chosen candidate; a foreign key references a primary or unique key.

Cardinality (1:1, 1:M, M:N) and optionality (mandatory/optional) are separate decisions; an M:N relationship resolves into an associative entity.

A subtype/supertype model (generalization) shares common attributes in the parent and puts type-specific attributes in children.

Star schema: one fact table of measures and foreign keys surrounded by denormalized dimension tables; snowflake schema normalizes dimensions.

Slowly changing dimension Type 1 overwrites, Type 2 adds a new row with effective dates and a current flag, Type 3 adds a previous-value column.

Surrogate keys are system-generated integers independent of business data; natural keys are business-meaningful and may change over time.

Logical SQL clause evaluation order: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT/OFFSET.

WHERE filters rows before aggregation; HAVING filters groups after aggregation; aggregate functions cannot appear in WHERE.

INNER JOIN returns matching rows only; LEFT JOIN keeps all left rows with NULLs where unmatched; a WHERE predicate on the right table turns a LEFT JOIN into an inner join.

NULL means unknown: comparisons with NULL yield UNKNOWN, so use IS NULL / IS NOT NULL; COUNT(*) counts rows, COUNT(column) skips NULLs.

ACID: Atomicity (all or nothing), Consistency (constraints hold), Isolation (concurrent transactions do not interfere), Durability (committed data survives failure).

Isolation levels READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE trade concurrency for protection against dirty reads, non-repeatable reads, and phantom reads.

A clustered index defines physical row order (one per table); nonclustered indexes are separate structures pointing to rows.

UNION removes duplicates and sorts; UNION ALL concatenates without deduplication and is generally faster.

Data quality dimensions: completeness, accuracy, consistency, timeliness, uniqueness, validity; each needs a measurable rule and threshold.

A data quality rule is expressed as a testable assertion (e.g., 'no more than 0.5% of customer rows have a null email') and monitored over time.

Data governance assigns decision rights: a data owner is accountable for a domain, a steward executes quality and definitions, a custodian operates the systems.

A data dictionary and business glossary define technical metadata and business terms respectively; a data catalog indexes both plus lineage.

PII is any data that can identify a person directly or in combination; masking, tokenization, and encryption reduce exposure but differ in reversibility.

Principle of least privilege: grant the minimum access needed for the role; separate duties so no single user both defines and approves access.

Regulatory regimes (GDPR, HIPAA, PCI DSS) impose retention, consent, breach-notification, and protection requirements that must map to controls.

Data lineage records where data came from and where it flows, supporting impact analysis, root-cause analysis, and audit.

ETL extracts, transforms, then loads into the warehouse; ELT loads raw first and transforms inside the target, common with cloud columnar warehouses.

Full load replaces the target each run; incremental load moves only changed rows using a high-water mark, change data capture, or timestamps.

Change data capture (CDC) reads insert/update/delete events from logs or triggers; it captures hard deletes that timestamp-based loads miss.

Idempotent loads can be rerun without duplicating data; implement via MERGE/upsert on a natural or surrogate key, or partition overwrite.

Late-arriving dimensions require inferred members or a placeholder row so fact rows can still reference a valid key.

Data profiling precedes transformation: measure null counts, distinct counts, min/max, and pattern distribution to design rules.

Orchestration dependencies should be data- or event-driven, not fixed clock times, to avoid loading incomplete source extracts.

Reconciliation controls compare source and target row counts and control totals; a mismatch halts the pipeline before downstream consumption.

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

A KPI needs a definition, formula, target, and owner; a metric without a stated denominator is ambiguous.

Grain of a fact table is the level of detail of one row; declare it before choosing dimensions or measures.

Conformed dimensions are shared across fact tables with identical keys and attributes, enabling drill-across queries.

Aggregate tables and materialized views precompute sums at a coarser grain; they trade storage and refresh cost for query speed.

Dashboard design: put the most decision-relevant metric first, show context (target, prior period), and avoid chart types that distort (e.g., truncated axes).

Descriptive analytics reports what happened; diagnostic explains why; predictive forecasts; prescriptive recommends actions.

A/B testing compares a treatment and control group; statistical significance depends on sample size and effect size, not just the observed difference.

Triple constraint: scope, schedule, and cost are interdependent; a change in one forces adjustment in at least one other.

Critical path is the longest sequence of dependent tasks; it sets the minimum project duration, and any delay on it delays the project.

A milestone has zero duration and marks a significant event; a task has duration and consumes effort.

Agile ceremonies: sprint planning, daily standup, sprint review (product), and retrospective (process); they serve different purposes.

Velocity is completed story points per sprint, used for forecasting, not for comparing teams.

A RAID log tracks Risks, Assumptions, Issues, and Dependencies; risks are future, issues are current.

Earned value: CV = EV - AC, SV = EV - PV, CPI = EV/AC, SPI = EV/PV; CPI or SPI below 1.0 indicates over budget or behind schedule.

A RACI matrix assigns Responsible, Accountable, Consulted, Informed; exactly one person should be Accountable per deliverable.