Confusion Cluster — Data Warehousing & Business Intelligence
11. Staging vs ODS vs Data Warehouse vs Data Mart
Plain language
Staging prepares. ODS supports integrated current/near-current use. The warehouse preserves integrated analytical history. A mart presents a targeted analytical slice.
Formal distinction
- Staging: transient/intermediate preparation area.
- ODS: integrated current or near-current data, lower latency and more volatility.
- Data Warehouse: durable integrated historical analytical store.
- Data Mart: targeted subject/process/user analytical presentation.
Similarities
All may participate in one analytical architecture and may even be physically co-located.
Differences
Persistence, history, volatility, latency and consumer purpose.
Deciding clue
Prepare → Stage. Current integrated → ODS. Durable history → DW. Targeted presentation → Mart.
Common trap
Physical location does not determine architectural role.
Mini scenario
Temporary cleansing before load → Staging.
Last 30 days, refreshed every few minutes for current operations → ODS.
Five-year reproducible history → DW.
Finance profitability slice → Mart.
Counterexample
A table stored in the same database as the warehouse can still be a staging table if it is transient and exists only to prepare loads.
Retrieval check
❓ A store retains integrated near-current data refreshed every few minutes but is not intended as the durable historical record. What is the closest role?
✅ Answer: ODS.
🧠 Why: near-current integrated, lower-latency and more volatile is the ODS pattern; durable reproducible history points to the warehouse.
📖 Source: Chapter 11 pp. 365–371. See Ch11 Deep Card B.
12. Fact vs Dimension vs Conformed Dimension
Plain language
Facts measure a business process. Dimensions describe the facts. A conformed dimension uses the same governed descriptive meaning across multiple analytical areas.
Formal distinction
- Fact: process measurement.
- Dimension: descriptive context for analysis.
- Conformed Dimension: dimension governed consistently for reuse across fact areas/marts.
Similarities
Facts and dimensions are both dimensional-model components; conformed dimensions are still dimensions.
Differences
Measure vs context vs shared cross-area context.
Deciding clue
What is measured? → Fact. By what? → Dimension. What shared “by what?” connects multiple marts/processes? → Conformed Dimension.
Common trap
Numeric datatype does not automatically make an attribute a fact.
Mini scenario
Revenue and Quantity → facts. Product and Date → dimensions. The exact governed Product dimension reused by Sales and Inventory → conformed dimension.
Counterexample
A numeric Product Weight attribute can live in a Product dimension; “numeric” alone does not make it a business-process fact.
Retrieval check
❓ Sales and Inventory must analyze through the same governed Product definitions. What is the key dimensional concept?
✅ Answer: Conformed Dimension.
🧠 Why: the descriptive meaning is deliberately reused across separate fact/process areas.
📖 Source: Chapter 11 pp. 368–370; also Chapter 5 dimensional-modeling material. See Ch11 Deep Card C.
13. Mapping vs Remediation vs Transformation
Plain language
Mapping explains where data goes. Remediation fixes what is wrong. Transformation deliberately converts or derives data for target/business meaning—even when the source is valid.
Formal distinction
- Mapping: source-target equivalence and connecting rules.
- Remediation: correction of defects/root causes.
- Transformation: intentional conversion, derivation or restructuring into target meaning/format.
Similarities
All may appear in one ETL/ELT pipeline specification.
Differences
Traceability/equivalence vs defect repair vs intentional target conversion.
Deciding clue
Where does it go? → Mapping. Is it wrong and needs fixing? → Remediation. Is it valid but must be converted/derived? → Transformation.
Common trap
Using warehouse transformation forever to hide a correctable source defect.
Mini scenario
source.country_code → dim_country.country_key → mapping.
Malformed U$ → US → remediation.
Valid US → 840 under the approved target standard → transformation.
Counterexample
A transformation can change a perfectly valid source value; “changed” does not automatically mean “remediated.”
Retrieval check
❓ The source value US is valid, but the governed analytical target stores ISO numeric 840. Is US → 840 remediation or transformation?
✅ Answer: Transformation.
🧠 Why: nothing is defective about the source value; it is intentionally converted to the target representation.
📖 Source: Chapter 11 pp. 375–379. See Ch11 Deep Card F.