Lesson 6 — Requirements, Mapping, Data Quality & Population
The six Chapter 11 activities
Memorize this sequence:
1. Understand Requirements → 2. Define/Maintain Architecture → 3. Develop DW & Data Marts → 4. Populate DW → 5. Implement BI Portfolio → 6. Maintain Data Products
The sequence is a reasoning aid. It prevents teams from starting with a source table, tool, or load mechanism before establishing the business need.
Requirements start with decisions
A strong interview asks: - Which business goal or decision is being supported? - Which process/subject is involved? - Which current and future questions matter? - Which KPIs/measures/calculations are required? - By which dimensions/categories will users analyze them? - What grain is needed? - How much history and point-in-time reproducibility? - What freshness/latency? - What DQ, security/privacy/access, performance, availability, lineage, and support expectations?
A source inventory is important—but after the business questions establish what must be represented.
Three concurrent development tracks
Warehouse development is not “database first, reports later.” Three lanes progress together:
Data track: sources, profiling, mappings, semantics, Data Quality, transformation rules, models, grain/history.
Technology track: integration platform, storage, orchestration, scheduling, security, operations, performance, recovery.
BI/Delivery track: semantic/presentation layer, reports, dashboards, analytical experiences, user access.
They meet at coordinated releases.
Source-to-target mapping
A mapping explains how each analytical target is populated from source data and which rules are applied. It becomes core Metadata and lineage evidence.
A common logical taxonomy/model helps reconcile differently named source elements. cust_id, client_no, and party_key may or may not represent the same concept; the model plus SME/governance decisions resolve the semantics.
Mapping vs remediation vs transformation
Mapping: documents where it goes and the connecting rule.
Remediation: fixes a defect or root cause. Example: malformed U$ corrected to a valid country code.
Transformation: intentionally converts/derives/restructures valid data. Example: a valid source currency amount converted into reporting currency under an approved business rule.
A transformed value was not necessarily dirty. Conversely, transformation should not permanently conceal a correctable source defect.
Population and late-arriving dimensions
Population is usually a large share of the effort because it operationalizes mappings, DQ controls, sequence, change capture, load windows, exceptions, restart/recovery, and auditability.
Optimistic handling: create a controlled provisional/unknown dimension member, load the fact, then reconcile later. Use only when temporary inconsistency is acceptable and explicitly controlled.
Pessimistic handling: place the unresolved fact in a recycle/reject queue, alert/correct, and reload after the dimension exists. Use when policy requires referential validity before loading.
Audit and restartability
A production load should expose useful evidence: start/end, duration, record counts, rejected/recycled records, errors, dependencies, freshness, restart status, DQ checks, and service-window attainment.
Stop and check
Valid value converted to target code: transformation, documented by mapping.
Malformed invalid value: remediation.
Fact before dimension + provisional allowed: optimistic.
Fact before dimension + provisional prohibited: pessimistic recycle.
Source anchor: pp. 374–379.