DABCC cheat sheet

Data governance is the decision-rights and accountability framework for data; stewardship is the execution role that applies those decisions to a domain.

A RACI matrix assigns Responsible, Accountable, Consulted, Informed; exactly one Accountable per data domain or data element group.

A data steward owns definitions, quality rules, and issue resolution within a domain; a data owner holds approval authority and budget accountability.

A data governance council (steering committee) sets policy and arbitrates cross-domain conflicts; domain councils escalate unresolved issues to it.

Policy hierarchy: policy (intent, mandatory) → standard (measurable requirement) → procedure (steps) → guideline (recommended practice).

Data issue escalation tiers typically: steward resolution → domain council → governance council; SLA clocks start at ticket creation, not at triage.

Data governance maturity models commonly progress from ad hoc → defined → managed → measured → optimized; assessment scores map to these stages.

Critical data elements (CDEs) are the subset of elements with regulatory, financial, or strategic impact; CDEs receive the strictest quality controls.

Data ethics principles frequently cited: transparency, fairness, purpose limitation, accountability, and human oversight of automated decisions.

Three-schema architecture: conceptual (business terms) → logical (attributes, keys, relationships, no DBMS specifics) → physical (tables, indexes, partitions, storage).

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

Denormalization trades write consistency and storage for read performance; star schemas denormalize dimensions around a central fact table.

Kimball dimensional model: fact table holds measures plus foreign keys at a declared grain; dimension tables hold descriptive context and conform across facts.

Inmon approach builds a normalized enterprise data warehouse first, then dependent data marts; Kimball builds conformed marts from the outset.

Slowly changing dimension types: Type 1 overwrites, Type 2 adds a new row with version/effective dates, Type 3 adds a prior-value column.

Grain statement fixes what one fact row represents (e.g., one order line per day); all measures must be additive or semi-additive at that grain.

Data Vault modeling uses hubs (business keys), links (relationships), and satellites (descriptive history), favoring auditable, insert-only loads.

CAP theorem: under a network partition a distributed store chooses consistency or availability; PACELC adds latency vs consistency trade-offs when no partition exists.

ETL transforms before loading; ELT loads raw then transforms in the target, relying on target compute (common in cloud warehouses).

Full load replaces the target each run; incremental load moves only changed rows, detected by high-water marks, change data capture, or timestamps.

Change data capture sources: log-based (reads DB transaction logs), trigger-based, timestamp-based, and snapshot-diff comparison.

Idempotent loads can be re-run without duplicating data; achieved via MERGE/upsert on natural keys or delete-then-insert by partition.

Late-arriving dimensions are handled by inferred/placeholder dimension rows later updated, or by Type 2 back-dating with effective dates.

Surrogate keys are system-generated integers decoupled from business keys; they stabilize history when source natural keys are reused or recycled.

Orchestration dependencies are DAG-based; a task runs only when upstream tasks succeed, and reruns must respect partition-level lineage.

Data reconciliation compares source and target control totals (row counts, sum of amounts, hash totals) to prove completeness of a load.

Streaming vs batch: micro-batch uses fixed intervals, true streaming processes event-by-event; exactly-once semantics require idempotent sinks or transactional writes.

Six core data quality dimensions: completeness, accuracy, consistency, timeliness, uniqueness, validity.

Completeness = non-null (or populated) records ÷ total records; report per critical column, not only per table.

Accuracy is measured against a verified reference source; validity is conformance to a defined format, range, or domain of allowed values.

Uniqueness = distinct business keys ÷ total records; duplicates are detected by exact match or fuzzy matching (Levenshtein, phonetic, token-based).

Consistency checks compare the same attribute across systems or across related tables (e.g., order total equals sum of line items).

Timeliness compares data currency to an agreed SLA, e.g., data available within N hours of the source event.

Data profiling outputs: null counts, distinct counts, min/max, frequency distributions, pattern discovery, and key-candidate detection.

Root cause analysis classifies defects as process, system, or human-origin; remediation fixes the source process, not only the downstream record.

A data quality scorecard tracks dimension scores by domain over time; thresholds trigger alerts and block downstream publication when breached.

Data classification tiers commonly: public, internal, confidential, restricted; controls (encryption, access, retention) tighten at each tier.

Encryption at rest protects stored data (AES-256 typical); encryption in transit uses TLS; both are required for regulated data.

Tokenization replaces a value with a non-reversible token mapped in a protected vault; format-preserving encryption keeps the original format.

Masking options: static (permanent, in non-production copies) vs dynamic (applied at query time based on user role).

RBAC grants permissions by role; ABAC evaluates attributes (user, resource, environment) and supports finer, context-aware rules.

Least privilege plus separation of duties: the person who approves access should not be the person who grants it.

GDPR principles: lawfulness, purpose limitation, data minimization, accuracy, storage limitation, integrity/confidentiality, accountability; data subject rights include access, rectification, erasure, portability.

PII categories include direct identifiers (name, SSN) and quasi-identifiers (ZIP, birth date, gender) that can re-identify when combined.

k-anonymity requires each record to be indistinguishable from at least k−1 others on quasi-identifiers; l-diversity and t-closeness address attribute disclosure.

Descriptive answers what happened; diagnostic why; predictive what will happen; prescriptive what action to take.

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

Aggregate ratios must be recomputed from summed numerator and denominator, never averaged from row-level percentages.

Star schema queries join a fact to conformed dimensions; snowflake schema normalizes dimensions into sub-tables, adding joins.

OLAP operations: slice (fix one dimension value), dice (fix ranges on multiple dimensions), drill-down (finer grain), roll-up (coarser grain), pivot (rotate axes).

A report KPI needs an explicit formula, grain, time window, and source system; without these, two teams produce different numbers for the same metric.

Type 2 dimensions require as-of joins so a fact row matches the dimension version effective at the event date.

Dashboard design: one primary question per view, consistent color semantics, and visible data-refresh timestamp to signal currency.

Statistical caution: correlation does not imply causation; small samples and survivorship bias distort conclusions drawn from reporting data.

Metadata types: technical (schemas, data types, lineage), business (definitions, owners, glossary terms), operational (job logs, row counts, refresh times).

A business glossary defines approved terms and synonyms; a data dictionary documents physical structures and is linked to glossary terms.

Data lineage traces data from source to report (and back); column-level lineage is required for impact analysis of a schema change.

Master data domains typically: customer, product, supplier, employee, location; reference data is smaller code sets (country codes, currency codes).

MDM implementation styles: registry (index only), consolidation (aggregate copy for reporting), coexistence (bidirectional sync), central (authoritative hub).

Match and merge: deterministic matching uses exact keys/rules; probabilistic matching scores attribute similarity against thresholds to identify duplicates.

Golden record creation requires survivorship rules specifying which source wins per attribute (e.g., most recent, most trusted system).

A data catalog indexes metadata assets with search, ownership, classification tags, and usage statistics to enable discovery.

Reference data management differs from MDM: it governs shared code lists with controlled change workflow, not entity instances.

Data governance defines decision rights and accountability for data assets; it is distinct from data management, which executes those decisions.

A data owner is accountable for a domain's data (approvals, access, quality targets); a data steward executes definitions, rules, and issue triage.

A RACI chart assigns exactly one Accountable per activity; multiple Responsible parties are allowed, but shared accountability causes escalation failures.

A data governance council sets policy and resolves cross-domain conflicts; domain-level councils escalate unresolved issues upward with documented decisions.

Policy hierarchy: policy (mandatory intent) → standard (measurable requirement) → procedure (step-by-step) → guideline (recommended, non-binding).

Critical data elements (CDEs) are elements with regulatory, financial, or strategic impact and receive the strictest controls and quality thresholds.

Maturity models commonly progress ad hoc → defined → managed → measured → optimized; each stage requires documented evidence, not intent.

Data issue escalation tiers: steward resolution → domain council → governance council; SLA clocks start at ticket creation, not at triage.

Three-schema levels: conceptual (business concepts) → logical (attributes, keys, relationships, no DBMS detail) → physical (tables, indexes, partitions, storage).

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

Denormalization trades write consistency and storage for read performance; star schemas denormalize dimensions around a central fact table.

Kimball dimensional model: fact table holds measures plus foreign keys at a declared grain; conformed dimensions are shared across fact tables.

Inmon builds a normalized enterprise warehouse first, then dependent marts; Kimball builds conformed dimensional marts directly.

SCD Type 1 overwrites; Type 2 adds a row with version and effective dates; Type 3 adds a prior-value column for limited history.

Data Vault uses hubs (business keys), links (relationships), and satellites (descriptive history) with insert-only, auditable loads.

CAP theorem: under a network partition a distributed store chooses consistency or availability; PACELC adds latency vs consistency when no partition occurs.

Grain statement fixes what one fact row represents (e.g., one order line per day); every measure must be additive or semi-additive at that grain.

ETL transforms before loading; ELT loads raw then transforms inside the target, using target compute such as a cloud warehouse.

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

CDC sources: log-based (reads transaction logs), trigger-based, timestamp-based, and snapshot-diff comparison.

Idempotent loads can be re-run without duplicating data, achieved via MERGE/upsert on natural keys or delete-then-insert by partition.

Late-arriving dimensions are handled with inferred placeholder rows later updated, or Type 2 back-dating using effective dates.

Surrogate keys are system-generated integers decoupled from business keys; they stabilize history when natural keys are reused.

Orchestration uses DAG dependencies: a task runs only after upstream success, and reruns must respect partition-level lineage.

Reconciliation compares source and target control totals (row counts, sum of amounts, hash totals) to prove load completeness.

Micro-batch uses fixed intervals; true streaming processes event-by-event; exactly-once needs idempotent sinks or transactional writes.

Six core dimensions: completeness, accuracy, consistency, timeliness, uniqueness, validity; each needs an explicit measurement rule.

Completeness = populated records ÷ total records, measured per critical column rather than only per table.

Accuracy is conformance to a verified reference source; validity is conformance to a defined format, range, or allowed value domain.

Uniqueness = distinct business keys ÷ total records; duplicates are found by exact match or fuzzy matching (Levenshtein, phonetic, token-based).

Consistency compares the same attribute across systems or related tables, e.g., order total equals sum of its line items.

Timeliness compares data currency against an agreed SLA, e.g., data available within N hours of the source event.

Data profiling outputs null counts, distinct counts, min/max, frequency distributions, pattern discovery, and key-candidate detection.

Root cause analysis classifies defects as process, system, or human origin; remediation fixes the source process, not only downstream records.

A quality scorecard tracks dimension scores by domain over time; breached thresholds trigger alerts and can block downstream publication.

Classification tiers commonly public, internal, confidential, restricted; encryption, access, and retention controls tighten at each tier.

Encryption at rest protects stored data (AES-256 typical); encryption in transit uses TLS; regulated data requires both.

Tokenization replaces a value with a non-reversible token mapped in a protected vault; format-preserving encryption keeps the original format.

Masking is static (permanent, applied to non-production copies) or dynamic (applied at query time based on user role).

RBAC grants permissions by role; ABAC evaluates attributes of user, resource, and environment for context-aware rules.

Least privilege plus separation of duties: the person approving access should not be the person granting it.

GDPR principles: lawfulness, purpose limitation, data minimization, accuracy, storage limitation, integrity/confidentiality, accountability.

PII includes direct identifiers (name, SSN) and quasi-identifiers (ZIP, birth date, gender) that can re-identify when combined.

k-anonymity requires each record to be indistinguishable from k−1 others on quasi-identifiers; l-diversity and t-closeness address attribute disclosure.

Descriptive answers what happened; diagnostic why; predictive what will happen; prescriptive what action to take.

Additive measures sum across all dimensions; semi-additive sum across some (balances across time but not accounts); non-additive ratios cannot be summed.

Aggregate ratios must be recomputed from summed numerator and denominator, never averaged from row-level percentages.

Star schema joins a fact to conformed dimensions; snowflake schema normalizes dimensions into sub-tables, adding joins.

OLAP operations: slice (fix one dimension value), dice (fix ranges on multiple dimensions), drill-down (finer grain), roll-up (coarser grain), pivot (rotate axes).

A KPI needs an explicit formula, grain, time window, and source system; without these, two teams produce different numbers for the same metric.

Type 2 dimensions require as-of joins so each fact row matches the dimension version effective at the event date.

Dashboard design: one primary question per view, consistent color semantics, and a visible data-refresh timestamp.

Correlation does not imply causation; small samples and survivorship bias distort conclusions drawn from reporting data.

Metadata types: technical (schemas, data types, lineage), business (definitions, owners, glossary terms), operational (job logs, row counts, refresh times).

A business glossary defines approved terms and synonyms; a data dictionary documents physical structures and links to glossary terms.

Data lineage traces source to report and back; column-level lineage is required for impact analysis of schema changes.

Master data domains typically include customer, product, supplier, employee, location; reference data is smaller code sets such as country or currency codes.

MDM styles: registry (index only), consolidation (aggregate copy for reporting), coexistence (bidirectional sync), central (authoritative hub).

Deterministic matching uses exact keys and rules; probabilistic matching scores attribute similarity against thresholds to identify duplicates.

Golden record creation requires survivorship rules specifying which source wins per attribute (most recent, most trusted system).

A data catalog indexes metadata assets with search, ownership, classification tags, and usage statistics to enable discovery.

Reference data management governs shared code lists with controlled change workflow, distinct from entity-instance MDM.

Data governance defines decision rights and accountability for data; data management executes those decisions operationally.

A data owner is accountable for a domain (approvals, access, quality targets); a data steward executes definitions, rules, and issue triage.

A RACI chart assigns exactly one Accountable per activity; multiple Responsible parties are allowed, but shared accountability causes escalation failures.

A governance council sets policy and resolves cross-domain conflicts; domain councils escalate unresolved issues upward with documented decisions.

Policy hierarchy: policy (mandatory intent) → standard (measurable requirement) → procedure (step-by-step) → guideline (recommended, non-binding).

Critical data elements (CDEs) are elements with regulatory, financial, or strategic impact and receive the strictest controls and thresholds.

Maturity models commonly progress ad hoc → defined → managed → measured → optimized; each stage requires documented evidence, not intent.

Data issue escalation tiers: steward resolution → domain council → governance council; SLA clocks start at ticket creation, not at triage.

Three-schema levels: conceptual (business concepts) → logical (attributes, keys, relationships, no DBMS detail) → physical (tables, indexes, partitions, storage).

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

Denormalization trades write consistency and storage for read performance; star schemas denormalize dimensions around a central fact table.

Kimball dimensional model: fact table holds measures plus foreign keys at a declared grain; conformed dimensions are shared across fact tables.

Inmon builds a normalized enterprise warehouse first, then dependent marts; Kimball builds conformed dimensional marts directly.

SCD Type 1 overwrites; Type 2 adds a row with version and effective dates; Type 3 adds a prior-value column for limited history.

Data Vault uses hubs (business keys), links (relationships), and satellites (descriptive history) with insert-only, auditable loads.

CAP theorem: under a network partition a distributed store chooses consistency or availability; PACELC adds latency vs consistency when no partition occurs.

Grain statement fixes what one fact row represents (e.g., one order line per day); every measure must be additive or semi-additive at that grain.

ETL transforms before loading; ELT loads raw then transforms inside the target, using target compute such as a cloud warehouse.

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

CDC sources: log-based (reads transaction logs), trigger-based, timestamp-based, and snapshot-diff comparison.

Idempotent loads can be re-run without duplicating data, achieved via MERGE/upsert on natural keys or delete-then-insert by partition.

Late-arriving dimensions are handled with inferred placeholder rows later updated, or Type 2 back-dating using effective dates.

Surrogate keys are system-generated integers decoupled from business keys; they stabilize history when natural keys are reused.

Orchestration uses DAG dependencies: a task runs only after upstream success, and reruns must respect partition-level lineage.

Reconciliation compares source and target control totals (row counts, sum of amounts, hash totals) to prove load completeness.

Micro-batch uses fixed intervals; true streaming processes event-by-event; exactly-once needs idempotent sinks or transactional writes.

Six core dimensions: completeness, accuracy, consistency, timeliness, uniqueness, validity; each needs an explicit measurement rule.

Completeness = populated records ÷ total records, measured per critical column rather than only per table.

Accuracy is conformance to a verified reference source; validity is conformance to a defined format, range, or allowed value domain.

Uniqueness = distinct business keys ÷ total records; duplicates are found by exact match or fuzzy matching (Levenshtein, phonetic, token-based).

Consistency compares the same attribute across systems or related tables, e.g., order total equals sum of its line items.

Timeliness compares data currency against an agreed SLA, e.g., data available within N hours of the source event.

Data profiling outputs null counts, distinct counts, min/max, frequency distributions, pattern discovery, and key-candidate detection.

Root cause analysis classifies defects as process, system, or human origin; remediation fixes the source process, not only downstream records.

A quality scorecard tracks dimension scores by domain over time; breached thresholds trigger alerts and can block downstream publication.

Classification tiers commonly public, internal, confidential, restricted; encryption, access, and retention controls tighten at each tier.

Encryption at rest protects stored data (AES-256 typical); encryption in transit uses TLS; regulated data requires both.

Tokenization replaces a value with a non-reversible token mapped in a protected vault; format-preserving encryption keeps the original format.

Masking is static (permanent, applied to non-production copies) or dynamic (applied at query time based on user role).

RBAC grants permissions by role; ABAC evaluates attributes of user, resource, and environment for context-aware rules.

Least privilege plus separation of duties: the person approving access should not be the person granting it.

GDPR principles: lawfulness, purpose limitation, data minimization, accuracy, storage limitation, integrity/confidentiality, accountability.

PII includes direct identifiers (name, SSN) and quasi-identifiers (ZIP, birth date, gender) that can re-identify when combined.

k-anonymity requires each record to be indistinguishable from k−1 others on quasi-identifiers; l-diversity and t-closeness address attribute disclosure.

Descriptive answers what happened; diagnostic why; predictive what will happen; prescriptive what action to take.

Additive measures sum across all dimensions; semi-additive sum across some (balances across time but not accounts); non-additive ratios cannot be summed.

Aggregate ratios must be recomputed from summed numerator and denominator, never averaged from row-level percentages.

Star schema joins a fact to conformed dimensions; snowflake schema normalizes dimensions into sub-tables, adding joins.

OLAP operations: slice (fix one dimension value), dice (fix ranges on multiple dimensions), drill-down (finer grain), roll-up (coarser grain), pivot (rotate axes).

A KPI needs an explicit formula, grain, time window, and source system; without these, two teams produce different numbers for the same metric.

Type 2 dimensions require as-of joins so each fact row matches the dimension version effective at the event date.

Dashboard design: one primary question per view, consistent color semantics, and a visible data-refresh timestamp.

Correlation does not imply causation; small samples and survivorship bias distort conclusions drawn from reporting data.

Metadata types: technical (schemas, data types, lineage), business (definitions, owners, glossary terms), operational (job logs, row counts, refresh times).

A business glossary defines approved terms and synonyms; a data dictionary documents physical structures and links to glossary terms.

Data lineage traces source to report and back; column-level lineage is required for impact analysis of schema changes.

Master data domains typically include customer, product, supplier, employee, location; reference data is smaller code sets such as country or currency codes.

MDM styles: registry (index only), consolidation (aggregate copy for reporting), coexistence (bidirectional sync), central (authoritative hub).

Deterministic matching uses exact keys and rules; probabilistic matching scores attribute similarity against thresholds to identify duplicates.

Golden record creation requires survivorship rules specifying which source wins per attribute (most recent, most trusted system).

A data catalog indexes metadata assets with search, ownership, classification tags, and usage statistics to enable discovery.

Reference data management governs shared code lists with controlled change workflow, distinct from entity-instance MDM.