EXIL-CBI cheat sheet

DIKW chain: data → information (context added) → knowledge (synthesis) → wisdom (judgement); each step reduces volume and increases value.

OLTP: many short read/write transactions, normalised schema, current-state data. OLAP: few long read-only scans, denormalised schema, historical data.

A data warehouse is subject-oriented, integrated, time-variant and non-volatile - Inmon's four defining characteristics.

A data mart is a warehouse subset scoped to one business area; dependent marts read from the warehouse, independent marts read from operational sources.

Data lake = raw data in native format, schema-on-read. Data warehouse = modelled data, schema-on-write.

Metric = any quantifiable measure. KPI = metric bound to a strategic objective with a target, threshold and named owner.

ETL transforms before loading; ELT loads raw first and transforms inside the target platform using its compute.

A dimension supplies context (who, what, when, where); a fact stores numeric measures at a declared grain.

Metadata categories: technical (schema, data types), business (definitions, owners, rules), operational (run times, row counts, errors).

Kimball: bottom-up dimensional marts on a conformed bus. Inmon: top-down normalised enterprise warehouse, marts derived from it. Data Vault: hubs, links and satellites for auditable, source-agnostic integration.

Grain is declared before dimensions and facts; one fact row equals exactly one instance of the grain.

SCD Type 1 overwrites; Type 2 inserts a new versioned row with surrogate key, start/end dates and current flag; Type 3 adds a prior-value column; Type 6 combines 1, 2 and 3.

Additive facts sum across all dimensions; semi-additive facts (balances) sum across some but not time; non-additive facts (ratios, percentages) never sum - recompute from base components.

Conformed dimensions share identical meaning, keys and attributes across fact tables and are what make drill-across possible.

A factless fact table holds only dimension keys, recording events (attendance) or coverage conditions (what could have happened).

Normal forms: 1NF atomic values; 2NF no partial dependency on part of a composite key; 3NF no transitive dependency on non-key attributes.

Star schema keeps dimensions denormalised and joined directly to the fact table (fewer joins); snowflake normalises dimension hierarchies (less redundancy, more joins).

Surrogate keys are warehouse-generated integers; natural/business keys are retained as attributes for traceability back to source.

Kimball's four design steps, in order: select the business process, declare the grain, identify the dimensions, identify the facts.

Bus matrix: rows are business processes, columns are conformed dimensions; a populated cell indicates a required fact table.

Hierarchies live inside the dimension; ragged or variable-depth hierarchies require bridge tables or path columns.

Many-to-many dimension relationships need a bridge table with weighting factors, otherwise fact measures double-count on aggregation.

Surrogate keys are assigned at load time, never reused, with a reserved unknown/inferred member (commonly key 0 or -1).

Data profiling precedes design: measure null rate, distinct count, min/max, pattern conformance and orphan foreign-key rate per column.

Dimension cardinality is the distinct member count at the grain and drives storage sizing and aggregation strategy.

A junk dimension collects low-cardinality flags and indicators into one table to keep the fact table narrow.

Distinguish business questions from requested reports; every report requirement must map to a fact grain or it cannot be built.

Incremental loads key on a stored high-water mark (timestamp or sequence); full reloads are reserved for small or non-delta-able sources.

CDC options: log-based capture (low source impact, captures deletes), timestamp-based (simple, misses hard deletes), trigger-based (complete but adds source overhead).

Idempotency is achieved with MERGE/upsert on the business key or partition overwrite, so a re-run yields an identical result.

Pipeline DAG tasks run only when all upstream dependencies succeed; fan-in nodes wait on every parent.

Late-arriving data: use inferred dimension members or restate affected partitions; SCD2 lookups must resolve the version valid at event time, not current.

Test layers: unit tests on transformation logic, data-quality assertions (nulls, duplicates, row counts), source-to-target reconciliation, regression suites.

Performance levers: partition on date, cluster/sort on common filters, columnstore for large scans, pre-aggregate only when query patterns are stable.

Promotion path dev → test → production uses the same version-controlled artefact; all DDL and pipeline code is in version control.

Publish metric definitions once in a semantic layer so all BI tools query the same governed logic.

CIA triad: confidentiality, integrity and availability - every control should map to at least one of the three.

Least privilege and need-to-know: grant minimum object rights through roles rather than to individuals, and review entitlements on a fixed cycle.

RBAC assigns permissions to roles; ABAC evaluates attributes of user, resource and environment - finer-grained but harder to audit.

Risk = likelihood × impact, each on a defined scale; residual risk is the remaining exposure after existing controls.

Encrypt in transit with TLS and at rest via disk, column or transparent data encryption; tokenisation or pseudonymisation reduces PII exposure in analytical copies.

Privacy principles: lawfulness, purpose limitation, data minimisation, accuracy, storage limitation, integrity and confidentiality, accountability.

Row-level security filters results per user entitlement; column-level security and masking hide sensitive fields from unprivileged roles.

Retention schedules drive automated purge jobs; archival to cold storage must preserve auditability for the retention period.

Audit logs record who accessed which data and when; they are append-only and retained according to policy.

SLI = the measured indicator; SLO = internal target for that indicator; SLA = contractual commitment with defined consequences.

Pipeline SLO examples: freshness (maximum lag), completeness (row-count variance), availability (percentage of scheduled runs succeeding).

Six data-quality dimensions - accuracy, completeness, consistency, timeliness, uniqueness, validity - each needs a measurable rule and threshold.

RPO is the maximum tolerable data loss in time; RTO is the maximum tolerable outage; backup frequency must be at least as often as the RPO.

MTTR = total downtime ÷ number of incidents; MTBF = operating time between failures.

Incident flow: detect → triage and assign severity → mitigate → root cause (5 Whys, fishbone) → corrective action → verify effectiveness.

Improvement frameworks: PDCA (plan, do, check, act) and DMAIC (define, measure, analyse, improve, control).

Capacity monitoring tracks storage growth, query concurrency and compute/credit spend against forecast thresholds.

Change management requires peer review, passing automated tests, a rollback plan and stakeholder notification before production release.