I85PREP
⌘ K
CDDP study materials
CDDP study resources

CDDP practice questions

One question at a time. Build your CDDP understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1Data Governance and Ethics

A privacy program maps each data element to a lawful basis before any processing begins. Which data governance artifact most directly records that mapping?

  1. A service level agreement
  2. A capacity plan
  3. A disaster recovery runbook
  4. A record of processing activities Correct answer.

Explanation

A record of processing activities documents, for each processing operation, the purposes, categories of data, recipients, and the lawful basis relied upon. It is the standard accountability artifact that ties data elements to legal justification. Runbooks, SLAs, and capacity plans address operations and performance rather than lawful basis.

Question 2Data Governance and Ethics

An organization wants a single accountable role for its data ethics program. Which arrangement best reflects the principle of accountability?

  1. Outsource all ethical judgments to the vendor
  2. Assign named ownership with defined duties and reporting lines Correct answer.
  3. Distribute decisions informally across all staff
  4. Rely on periodic audits without assigned owners

Explanation

Accountability requires that responsibility be assigned to identifiable persons with clear duties and escalation paths. Named ownership makes decisions traceable and enforceable. Informal distribution, vendor delegation, and audit-only models leave no one answerable for outcomes.

Question 3Data Governance and Ethics

Which principle limits collection to what is necessary for a specified purpose?

  1. Data archiving
  2. Data residency
  3. Data portability
  4. Data minimization Correct answer.

Explanation

Data minimization requires that only data adequate, relevant, and necessary for the stated purpose be collected and retained. Portability concerns transferring data to another provider, residency concerns location, and archiving concerns retention for historical purposes.

Question 4Data Governance and Ethics

A team proposes a new analytics feature. Under privacy by design, at what point should a data protection impact assessment be considered?

  1. Only after a complaint is filed
  2. Before the processing begins, during design Correct answer.
  3. Only after the system is decommissioned
  4. Only when a regulator requests it

Explanation

Privacy by design places assessment upstream of processing so that risks can shape the design rather than be remediated later. Impact assessments are therefore triggered during planning for high-risk processing. Waiting for complaints, decommissioning, or regulator requests defeats the preventive purpose.

Question 5Data Governance and Ethics

Which role typically provides independent oversight of whether a data governance policy is actually followed?

  1. The marketing intern
  2. The data entry clerk
  3. The hardware vendor
  4. Internal audit Correct answer.

Explanation

Internal audit provides independent assurance that controls and policies operate as intended, reporting outside the operational chain. Operational staff, vendors, and interns either execute or supply services and lack the independence required for oversight.

Question 6Data Governance and Ethics

What is the primary purpose of a data classification scheme in a governance program?

  1. To increase storage capacity
  2. To determine handling requirements based on sensitivity Correct answer.
  3. To shorten software release cycles
  4. To replace encryption controls

Explanation

Classification assigns sensitivity levels so that handling, access, and retention controls can be matched to risk. It does not add capacity, accelerate releases, or substitute for technical safeguards such as encryption.

Question 7Data Governance and Ethics

Which statement best describes the role of informed consent in data processing?

  1. It transfers ownership of the data to the processor
  2. It provides a lawful basis when freely given, specific, and revocable Correct answer.
  3. It is required for every processing activity
  4. It permanently waives all future privacy rights

Explanation

Consent functions as a lawful basis only when it meets quality conditions such as being freely given, specific, informed, and withdrawable. It does not waive future rights, is not the only available basis, and does not transfer ownership.

Question 8Data Governance and Ethics

A governance charter states that data stewards resolve data quality issues within their domain. Which concept does this illustrate?

  1. Unrestricted open access to all records
  2. Federated ownership of data domains Correct answer.
  3. Centralized sole authorship of all data
  4. Elimination of data quality metrics

Explanation

Assigning stewards to resolve issues within domains is federated ownership, balancing central policy with local expertise. It is not centralization of authorship, open access, or abandonment of metrics; each steward remains accountable for quality measures.

Question 9Data Governance and Ethics

A retailer plans to use purchase history to infer health conditions for targeted advertising. Which governance response is most appropriate before launch?

  1. Proceed only if storage costs decrease
  2. Conduct a risk assessment and apply purpose limitation review Correct answer.
  3. Proceed because purchase data is already collected
  4. Proceed if the marketing team approves internally

Explanation

Inferring sensitive attributes such as health from purchase data is a secondary use that requires assessment against purpose limitation and heightened risk. Prior collection does not authorize new incompatible purposes. Internal marketing approval and cost considerations are not adequate safeguards.

Question 10Data Governance and Ethics

A company receives an erasure request but must retain certain records for tax law. What is the soundest governance approach?

  1. Refuse the entire request citing tax law
  2. Delete what is not legally required and document the retained scope Correct answer.
  3. Delete all records including statutory ones
  4. Ignore the request until the retention period ends

Explanation

Erasure obligations are balanced against legal retention duties, so the organization should erase non-required data and document why the remainder is retained. Blanket refusal, over-deletion, and silent inaction all misapply the balance.

Question 11Data Governance and Ethics

A vendor processes personal data on behalf of a controller. Which governance control most directly addresses the controller's accountability for the vendor?

  1. A verbal assurance from the sales representative
  2. A general marketing brochure
  3. An informal email chain about pricing
  4. A written processing agreement with audit and security terms Correct answer.

Explanation

Controllers remain accountable for processors, so the relationship must be governed by a binding written agreement specifying instructions, security, sub-processing, and audit rights. Verbal assurances, brochures, and pricing emails create no enforceable obligations.

Question 12Data Governance and Ethics

An analyst wants to share a dataset for research. Which technique best reduces re-identification risk while preserving analytical utility?

  1. De-identification with generalization and suppression Correct answer.
  2. Encrypting only the file name
  3. Publishing raw identifiers with a warning
  4. Restricting access to a single afternoon

Explanation

De-identification through techniques such as generalization, suppression, and aggregation lowers re-identification risk while retaining useful patterns. Raw identifiers, cosmetic filename encryption, and brief access windows do not meaningfully protect the data.

Question 13Data Governance and Ethics

A model trained on historical hiring data systematically disadvantages a group. Which governance action addresses this most directly?

  1. Deploy faster to gather more feedback
  2. Remove all documentation to avoid bias claims
  3. Audit training data and outcomes for disparate impact, then remediate Correct answer.
  4. Increase model complexity to improve accuracy

Explanation

Algorithmic bias is addressed by measuring disparate impact across groups in both training data and outputs, then correcting the sources of disparity. Added complexity, documentation removal, and speed do not remedy discriminatory patterns and may worsen them.

Question 14Data Governance and Ethics

A cross-border transfer is proposed to a country without an adequacy decision. Which mechanism most appropriately supports the transfer?

  1. An oral promise of protection from the recipient
  2. A press release announcing compliance
  3. A discount on the vendor's service fee
  4. Standard contractual clauses with a transfer impact assessment Correct answer.

Explanation

Where no adequacy decision exists, transfers rely on safeguards such as standard contractual clauses, supplemented by an assessment of the destination's legal environment. Oral promises, press releases, and pricing concessions provide no legally recognized safeguard.

Question 15Data Governance and Ethics

A data retention schedule is being designed. Which approach best reflects governance good practice?

  1. Set retention periods by purpose and legal requirement, then dispose Correct answer.
  2. Retain all data indefinitely in case it is useful
  3. Delete all data after thirty days regardless of duty
  4. Let each employee choose a retention period

Explanation

Retention should be purpose-driven and tied to legal or operational requirements, with disposal at the end of the period. Indefinite retention violates storage limitation, individual discretion produces inconsistency, and a fixed blanket period ignores differing obligations.

Question 16Data Governance and Ethics

Which practice best supports transparency toward data subjects?

  1. Burying key terms in dense legal language
  2. Clear layered notices describing purposes, recipients, and rights Correct answer.
  3. Disclosing purposes only after a complaint
  4. Withholding all information about processing

Explanation

Transparency requires that individuals understand what happens to their data, so layered notices present purposes, recipients, and rights clearly. Obscured terms, post-complaint disclosure, and withholding information all defeat informed understanding.

Question 17Data Governance and Ethics

A governance team discovers that a business unit maintains a shadow spreadsheet of customer data outside the approved system. What is the most effective first response?

  1. Publicly shame the responsible employees
  2. Assess the data, migrate it into governed storage, and close the gap Correct answer.
  3. Ignore it because the unit is productive
  4. Delete the spreadsheet without review

Explanation

Shadow data must first be assessed to understand sensitivity and risk, then moved into governed storage with controls, and the root cause addressed. Shaming, unreviewed deletion, and ignoring the issue either destroy evidence or leave risk unmanaged.

Question 18Data Governance and Ethics

When evaluating whether a data use is ethical, which consideration goes beyond mere legal compliance?

  1. Whether the logo meets brand guidelines
  2. Whether the server has sufficient disk space
  3. Whether the use matches reasonable expectations and avoids foreseeable harm Correct answer.
  4. Whether the marketing budget is approved

Explanation

Ethics asks whether a use is fair and consistent with reasonable expectations, even where it is technically lawful. Infrastructure capacity, budget approval, and branding are operational matters that say nothing about fairness or harm.

Question 19Data Governance and Ethics

A privacy notice states that data may be used for 'any future purpose'. Why is this problematic under purpose limitation?

  1. Purposes must be specified and legitimate at collection time Correct answer.
  2. Consent is never a valid basis
  3. Future purposes are always prohibited by law
  4. Notices must be shorter than one page

Explanation

Purpose limitation requires that purposes be specified, explicit, and legitimate when data is collected, so open-ended language fails the standard. Future uses are not categorically banned, notice length is irrelevant, and consent remains a valid basis under conditions.

Question 20Data Governance and Ethics

Which control best enforces least privilege for access to sensitive data?

  1. Emailing credentials to the team
  2. Shared administrator accounts
  3. Permanent access granted at onboarding
  4. Role-based access with periodic recertification Correct answer.

Explanation

Least privilege is enforced by granting role-based rights and periodically recertifying them so access stays aligned with duties. Shared accounts, permanent grants, and credential sharing all expand access beyond what is needed.

Question 21Data Governance and Ethics

A company wants to demonstrate accountability to regulators. Which combination is most convincing?

  1. Documented policies, processing records, and evidence of control testing Correct answer.
  2. A single signed statement with no supporting records
  3. A list of planned future improvements
  4. Verbal assurances from senior managers

Explanation

Accountability is demonstrated through evidence that policies exist, processing is recorded, and controls are actually tested. Unsigned statements, verbal assurances, and future plans lack the documentary proof regulators expect.

Question 22Data Governance and Ethics

A dataset is anonymized, but an attacker can combine it with public records to re-identify individuals. Which governance conclusion is correct?

  1. Only names count as identifying information
  2. Anonymization is permanent once applied
  3. Anonymization must be assessed against re-identification risk, not assumed Correct answer.
  4. Public records may be ignored in risk assessment

Explanation

Anonymization is a risk-based judgment, and linkage with auxiliary data can defeat it, so assessments must consider reasonably available sources. It is not automatically permanent, public records are relevant, and identifiers extend beyond names to quasi-identifiers.

Question 23Data Governance and Ethics

A governance board must decide between two analytics projects with different privacy risks. Which approach best supports a defensible decision?

  1. Compare assessed risks against criteria and document the rationale Correct answer.
  2. Delay until the decision is unavoidable
  3. Defer to the loudest stakeholder
  4. Choose the cheaper project without analysis

Explanation

Defensible decisions rest on explicit criteria, assessed risks, and a recorded rationale that can be reviewed later. Cost-only choices, deference to influence, and delay substitute politics or avoidance for reasoned governance.

Question 24Data Governance and Ethics

An organization operates in several jurisdictions with conflicting data protection rules. Which strategy is most sound?

  1. Apply whichever rule is cheapest to implement
  2. Map obligations per jurisdiction and apply the strictest consistent baseline Correct answer.
  3. Apply the rules of the smallest market everywhere
  4. Ignore conflicts until enforcement occurs

Explanation

Conflicting regimes are managed by mapping each jurisdiction's obligations and adopting a baseline that satisfies the strictest applicable requirements where feasible. Choosing by market size, awaiting enforcement, or minimizing cost leaves legal exposure unaddressed.

Question 25Data Governance and Ethics

A vendor's sub-processor is changed without notice. Which governance failure does this most directly illustrate?

  1. Inadequate control over sub-processing and change notification Correct answer.
  2. Insufficient marketing reach
  3. Excessive data minimization
  4. Overly strict retention limits

Explanation

Sub-processor changes require prior authorization and notification so the controller can assess new risks, so silent substitution is a third-party control failure. Data minimization, retention limits, and marketing reach are unrelated to this event.

Question 26Data Governance and Ethics

Which principle of data ethics holds that an organization should be able to demonstrate, not merely claim, that its processing is lawful and fair?

  1. Purpose limitation
  2. Data minimization
  3. Accountability Correct answer.
  4. Storage limitation

Explanation

Accountability requires the organization to evidence how it complies, for example through records of processing, audits, and documented decisions, rather than simply asserting good intent. The other principles constrain what data is collected and how long it is kept, but they do not by themselves establish demonstrability.

Question 27Data Governance and Ethics

A governance policy requires that every dataset have a named individual responsible for its accuracy and fitness for use within a business domain. Which role does this policy define?

  1. Records manager
  2. Data custodian
  3. Data owner
  4. Data steward Correct answer.

Explanation

A data steward is the role accountable for the quality and fitness of data within a defined domain, resolving issues and applying standards day to day. Ownership typically sits at a more senior level with decision rights over the data asset, while custodians implement technical controls.

Question 28Data Governance and Ethics

An organization wants to reuse customer data collected for service delivery in a new machine-learning product. Which governance step best applies the purpose-limitation principle before development begins?

  1. Add a clause to the privacy notice stating that all future uses are permitted
  2. Anonymize the dataset after the model has been trained and deployed
  3. Obtain a fresh legal opinion on whether the new use is profitable
  4. Assess whether the new use is compatible with the original purpose and document the determination Correct answer.

Explanation

Purpose limitation requires that a new use be assessed for compatibility with the purpose for which the data was originally collected, and that the reasoning be recorded so it can be defended later. Documentation made before development supports accountability and lets the organization adjust or seek consent if compatibility fails.

Question 29Data Governance and Ethics

Two departments want access to the same sensitive customer table. One needs full records for fraud investigation; the other needs only aggregated counts for reporting. Which governance approach best reflects data minimization?

  1. Deny both requests until the data owner retires and the table is archived
  2. Provide the reporting team an aggregated view and reserve record-level access for fraud investigation Correct answer.
  3. Grant the reporting team full access but require it to sign an additional confidentiality agreement
  4. Grant both departments full access and rely on audit logs to detect misuse

Explanation

Data minimization means giving each function access only to the data it actually needs, so the reporting team receives aggregates while record-level access stays with the team that requires it. This reduces exposure of sensitive records without blocking legitimate work.

Question 30Data Governance and Ethics

A governance committee must approve an analytics initiative whose benefits are uncertain and whose harms would fall on a vulnerable group. Which decision approach is most defensible ethically?

  1. Weigh foreseeable benefits against risks to the affected group, including their views, and record the rationale Correct answer.
  2. Proceed because the affected group has not raised objections and silence implies acceptance
  3. Rely on the vendor's assurance that its model is fair and skip independent review
  4. Defer to the legal team, since conduct that is lawful cannot be ethically objectionable

Explanation

Ethical review weighs benefits against foreseeable harms, gives particular attention to vulnerable groups, and documents the reasoning so the decision can be scrutinized. Legality sets a floor rather than a ceiling, and silence from an affected group is not meaningful consent.

Question 31Data Architecture and Modeling

In dimensional modeling, which schema places a central fact table joined to fully normalized dimension tables, reducing redundancy at the cost of more joins?

  1. Wide denormalized table
  2. Star schema
  3. Data Vault
  4. Snowflake schema Correct answer.

Explanation

A snowflake schema normalizes dimension tables into hierarchies, so a dimension such as product is split into product, category, and department tables linked by foreign keys. This removes redundancy but requires additional joins compared with a star schema, where each dimension is a single denormalized table.

Question 32Data Architecture and Modeling

A fact table stores a measure that can be summed across every dimension, such as total sales amount. Which term describes this measure?

  1. Additive fact Correct answer.
  2. Degenerate dimension
  3. Non-additive fact
  4. Semi-additive fact

Explanation

Additive facts can be summed across all dimensions without distortion, and sales amount is the classic example. Semi-additive facts can be summed across some but not all dimensions, as with a month-end balance, while non-additive facts such as ratios cannot be summed at all.

Question 33Data Architecture and Modeling

Which key is a system-generated, meaningless integer assigned to each row of a dimension table so that history and source changes can be tracked?

  1. Composite key
  2. Natural key
  3. Surrogate key Correct answer.
  4. Foreign key

Explanation

A surrogate key is an artificial identifier generated by the warehouse, independent of source-system values, which lets the same natural key appear in multiple dimension rows for different versions. Natural keys come from the source system and a foreign key references another table rather than identifying the dimension row itself.

Question 34Data Architecture and Modeling

Which modeling approach stores all historical states of an entity by adding effective dates and a current-record flag to the dimension table?

  1. Type 1 slowly changing dimension
  2. Type 2 slowly changing dimension Correct answer.
  3. Rapidly changing dimension
  4. Type 3 slowly changing dimension

Explanation

A Type 2 slowly changing dimension inserts a new row for each change and marks validity with effective and expiration dates plus a current flag, preserving full history. Type 1 overwrites the old value and Type 3 adds a previous-value column, so neither retains every historical state.

Question 35Data Architecture and Modeling

In a star schema, which table holds the numeric measurements and foreign keys for a business process at a declared grain?

  1. Fact table Correct answer.
  2. Dimension table
  3. Staging table
  4. Bridge table

Explanation

The fact table declares the grain of the business process and stores numeric measures plus foreign keys to each dimension. Dimension tables hold descriptive attributes, bridge tables resolve many-to-many relationships, and staging tables are transient landing areas.

Question 36Data Architecture and Modeling

Which concept defines the level of detail at which rows in a fact table are recorded, such as one row per order line?

  1. Granularity hierarchy
  2. Conformed dimension
  3. Grain Correct answer.
  4. Cardinality

Explanation

The grain declares exactly what a single fact row represents, and every measure must be consistent with that declared level of detail. Cardinality counts distinct values, granularity hierarchy describes drill paths, and a conformed dimension is shared across fact tables.

Question 37Data Architecture and Modeling

Which normalization form requires that every non-key attribute depend on the whole composite primary key rather than part of it?

  1. Boyce-Codd normal form
  2. Third normal form
  3. First normal form
  4. Second normal form Correct answer.

Explanation

Second normal form eliminates partial dependencies, so a non-key attribute must depend on the entire composite key. First normal form only requires atomic values, while third normal form and Boyce-Codd remove transitive dependencies.

Question 38Data Architecture and Modeling

Which data modeling level describes the business concepts and their relationships without technology or implementation detail?

  1. Logical data model
  2. Conceptual data model Correct answer.
  3. Canonical data model
  4. Physical data model

Explanation

A conceptual model captures business entities and relationships at a high level, independent of any database technology. The logical model adds attributes and keys while remaining technology-neutral, and the physical model specifies tables, data types, and indexes for a target platform.

Question 39Data Architecture and Modeling

A retail fact table records one row per order line, but the business also needs daily store-level totals. Which design choice best preserves drill-through to order-line detail while serving the aggregate?

  1. Replace the atomic fact table with a daily store-level summary table
  2. Keep the atomic-grain fact table and build a derived aggregate fact table at the store-day grain Correct answer.
  3. Store daily totals in a separate dimension table
  4. Add store-day totals as extra columns on each order-line row

Explanation

Preserving the atomic fact table keeps the most detailed data available for drill-through, and a derived aggregate table at the store-day grain speeds common queries. Replacing atomic data loses detail, duplicating totals on each line causes double counting, and totals do not belong in a dimension.

Question 40Data Architecture and Modeling

A source system reuses the same customer identifier after a record is deleted, and the warehouse must keep both historical versions distinguishable. Which design element addresses this?

  1. Store the natural key only in the fact table
  2. Overwrite the existing dimension row with the new values
  3. Assign a new surrogate key for each version while retaining the natural key Correct answer.
  4. Use the natural key as the primary key of the dimension

Explanation

A surrogate key gives each version its own unique identity, so reused natural keys remain distinguishable and fact rows point to the correct version. Using the natural key as the primary key would collide, overwriting loses history, and keeping the key only in the fact table removes dimension context.

Question 41Data Architecture and Modeling

Two fact tables, sales and returns, must be analyzed together by product and date. Which modeling technique enables this consistent cross-process analysis?

  1. Conformed dimensions shared by both fact tables Correct answer.
  2. Denormalizing both fact tables into one wide table
  3. Separate unrelated dimension tables for each fact table
  4. A single fact table combining sales and returns rows

Explanation

Conformed dimensions use identical keys and attributes across fact tables, so sales and returns can be drilled and compared consistently by product and date. Separate dimensions break comparability, and merging facts or denormalizing into one table confuses grains and measures.

Question 42Data Architecture and Modeling

A dimension contains a multivalued attribute where one product belongs to several categories. Which structure best resolves this many-to-many relationship in a dimensional model?

  1. A factless fact table recording only product keys
  2. A bridge table between the product dimension and a category dimension Correct answer.
  3. A single category column holding comma-separated values
  4. A junk dimension containing all category combinations

Explanation

A bridge table holds one row per product-category pairing and can carry weighting factors to avoid double counting when aggregating. Delimited lists violate atomicity, a junk dimension is for low-cardinality flags, and a factless fact table records event coverage rather than resolving multivalued attributes.

Question 43Data Architecture and Modeling

You must record the fact that a student was eligible for a course on a given date, with no numeric measure involved. Which structure fits?

  1. A snowflaked course dimension
  2. A Type 2 change on the student dimension
  3. A factless fact table with foreign keys to student, course, and date Correct answer.
  4. A dimension table with one row per eligibility event

Explanation

A factless fact table records the occurrence of a relationship or event using only foreign keys, which is ideal for coverage questions such as eligibility. Storing events in a dimension, versioning the student, or snowflaking the course does not capture the dated event relationship.

Question 44Data Architecture and Modeling

A date dimension needs a fiscal calendar where the year begins in July. Which approach keeps the model flexible?

  1. Replace the date dimension with a fiscal-only dimension
  2. Store fiscal periods in the fact table
  3. Add fiscal period attributes as columns in the date dimension alongside calendar attributes Correct answer.
  4. Compute fiscal periods at query time from the date key

Explanation

The date dimension can carry both calendar and fiscal attributes, so queries filter and group by either without recalculation. A fiscal-only dimension loses calendar context, computing at query time harms performance and consistency, and fiscal periods are descriptive attributes rather than measures.

Question 45Data Architecture and Modeling

A team wants full auditability of source loads, with insert-only raw tables and separately tracked relationships and descriptive attributes. Which modeling pattern matches this requirement?

  1. Wide denormalized reporting table
  2. Third normal form operational database
  3. Data Vault with hubs, links, and satellites Correct answer.
  4. Star schema with Type 1 dimensions

Explanation

Data Vault separates business keys into hubs, relationships into links, and descriptive history into satellites, giving insert-only, auditable loads. Star schemas favor query simplicity, third normal form targets operational integrity, and wide tables sacrifice auditability.

Question 46Data Architecture and Modeling

A fact table has a very large number of rows and most queries filter by date and product. Which physical design choice most directly improves those queries?

  1. Remove foreign keys to reduce storage
  2. Partition the fact table by date and add indexes or sort keys on product Correct answer.
  3. Convert the fact table to third normal form
  4. Add more descriptive columns to the fact table

Explanation

Partitioning by date enables partition pruning, and indexes or sort keys on product speed the common filter pattern. Adding descriptive columns bloats the fact table, normalizing it away from dimensional design hurts scan performance, and removing foreign keys breaks dimensional joins.

Question 47Data Architecture and Modeling

A dimension attribute changes frequently, and users only need the current value with no history. Which slowly changing dimension technique is appropriate?

  1. Type 3 previous-value column
  2. Type 2 versioned rows
  3. Type 6 hybrid
  4. Type 1 overwrite Correct answer.

Explanation

When history is not required, a Type 1 overwrite updates the attribute in place and keeps the dimension simple. Type 2 and Type 6 preserve history, and Type 3 keeps only a limited prior value, all of which add complexity that the requirement does not justify.

Question 48Data Architecture and Modeling

Two source systems identify the same customer with different codes and inconsistent attribute values. What should the warehouse do before loading the customer dimension?

  1. Store both source codes in the fact table
  2. Match records to a master customer and assign one surrogate key Correct answer.
  3. Use each source code as the dimension primary key
  4. Load both records as separate dimension rows permanently

Explanation

Identity resolution matches records representing the same customer and assigns a single surrogate key, so facts from both sources align to one dimension member. Keeping duplicates, storing codes only in facts, or using source codes as primary keys prevents consistent integration.

Question 49Data Architecture and Modeling

A designer proposes storing a customer's full address, city, and postal code as one column to simplify loading. What is the main modeling concern?

  1. The column cannot be indexed at all
  2. The column prevents use of surrogate keys
  3. The column will consume too much storage
  4. The composite value violates atomicity and blocks joins and grouping by city Correct answer.

Explanation

First normal form requires atomic attribute values; packing address, city, and postal code into one field prevents reliable joins to geography and grouping by city. Storage, indexing, and surrogate keys are not the core issue, since the fundamental problem is loss of atomic, separately addressable attributes.

Question 50Data Architecture and Modeling

A fact table records monthly account balances per customer. Analysts sum the balance column across all twelve months and report an inflated yearly figure. What is the root cause?

  1. The balance should have been stored in a dimension
  2. The balance is a semi-additive fact and must not be summed across time Correct answer.
  3. The customer dimension is not conformed
  4. The fact table has the wrong surrogate keys

Explanation

Account balances are semi-additive: they can be summed across customers but not across time, where the correct operation is to take the period-end value or an average. The error comes from summing over the time dimension, not from key design or dimension conformance.