I85PREP
⌘ K
DataX study materials
DataX study resources

DataX practice questions

One question at a time. Build your DataX 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 Management and Governance

In a data governance operating model, which body typically holds decision rights over enterprise-wide data definitions and policy exceptions?

  1. The internal audit function acting alone
  2. A cross-functional data governance council with delegated authority from executive leadership Correct answer.
  3. The infrastructure team that administers the primary database servers
  4. An external vendor delivering the reporting platform

Explanation

Decision rights for enterprise data definitions and policy exceptions normally sit with a cross-functional governance council that receives its mandate from executive leadership. Operational IT teams, vendors, and audit each contribute evidence or implementation, but they do not own enterprise policy decisions.

Question 2Data Management and Governance

A data steward discovers that the customer master record defines 'active customer' differently from the finance reporting definition. Which governance artifact should resolve this conflict first?

  1. The approved enterprise data dictionary entry with a designated business owner Correct answer.
  2. The database backup and recovery schedule
  3. The vendor contract for the CRM application
  4. The service level agreement for the reporting platform

Explanation

Conflicting definitions are resolved through the governed data dictionary entry, which records the approved definition and its accountable business owner. Operational documents such as backup schedules, platform SLAs, and vendor contracts do not establish business meaning.

Question 3Data Management and Governance

Which characteristic best distinguishes a data owner from a data steward in a mature governance model?

  1. The owner writes SQL queries while the steward approves the budget
  2. The owner is accountable for the domain and approves decisions; the steward executes and monitors on its behalf Correct answer.
  3. The owner manages servers while the steward manages user passwords
  4. The owner is always an external auditor while the steward is an internal analyst

Explanation

Accountability and approval authority rest with the data owner, while the steward performs the day-to-day work of defining, monitoring, and improving data quality. Technical administration and access control are separate operational roles.

Question 4Data Management and Governance

A retail bank classifies account numbers as restricted. Which control most directly enforces that classification at the point of use?

  1. A disaster recovery exercise for the core banking system
  2. Dynamic data masking applied to the field for users without a justified need Correct answer.
  3. A quarterly newsletter reminding staff about information security
  4. An annual penetration test of the customer web portal

Explanation

Classification is enforced at the point of use through technical controls such as dynamic masking that limits exposure to users lacking a justified need. Awareness campaigns, penetration tests, and DR exercises support security but do not directly restrict access to a specific classified field.

Question 5Data Management and Governance

Which statement correctly describes the relationship between a business glossary and a data catalog?

  1. The glossary defines business terms, while the catalog inventories data assets and links them to those terms Correct answer.
  2. The glossary replaces the catalog once metadata management matures
  3. The glossary is maintained by vendors, while the catalog is maintained by auditors
  4. The glossary stores row-level data values, while the catalog stores only schema diagrams

Explanation

A business glossary captures agreed business terminology and definitions, whereas a data catalog inventories data assets and connects them to glossary terms and technical metadata. They are complementary artifacts rather than substitutes, and neither stores production data values.

Question 6Data Management and Governance

What is the primary purpose of recording data lineage in a governance program?

  1. To encrypt data at rest automatically
  2. To increase the storage capacity of the warehouse
  3. To trace how data moves and transforms from source to consumption to support impact and root-cause analysis Correct answer.
  4. To replace the need for data quality rules

Explanation

Lineage documents the path and transformations of data across systems, enabling impact analysis for changes and root-cause analysis for defects. It is a metadata capability and does not add storage, substitute for quality rules, or perform encryption.

Question 7Data Management and Governance

Which metric is a valid measure of data quality completeness for a customer record set?

  1. The frequency with which the customer table is backed up
  2. The number of users granted read access to the customer table
  3. The average response time of the customer portal
  4. The percentage of required customer attributes that are populated with non-null values Correct answer.

Explanation

Completeness measures whether required attributes are present, commonly expressed as the proportion of mandatory fields populated. Response time, access counts, and backup frequency describe performance, security, and resilience rather than data completeness.

Question 8Data Management and Governance

Under a typical data retention policy, what should happen when a record reaches the end of its approved retention period and no legal hold applies?

  1. The record is moved to a reporting table without further review
  2. The record is retained indefinitely in case it is needed later
  3. The record is copied to a personal drive for safekeeping
  4. The record is disposed of through an approved, documented destruction process Correct answer.

Explanation

Retention policies require defensible disposition once the period ends and no hold applies, carried out through an approved destruction process with evidence. Indefinite retention, personal copies, and uncontrolled reuse all violate the policy.

Question 9Data Management and Governance

A governance team is defining a critical data element for regulatory reporting. Which criterion most supports that designation?

  1. The element directly feeds a report submitted to a regulator and errors would carry sanction risk Correct answer.
  2. The element is stored in the newest database technology
  3. The element has the largest storage footprint in the warehouse
  4. The element is frequently queried by the analytics team

Explanation

Critical data elements are identified by their material impact on regulatory reporting, financial statements, or key decisions. Technology novelty, query popularity, and storage size are not indicators of criticality.

Question 10Data Management and Governance

A data quality dashboard shows 99 percent completeness but repeated complaints about wrong customer addresses. Which dimension should the team investigate next?

  1. Completeness, because the dashboard already proves values are missing
  2. Accuracy, because values may be present but incorrect Correct answer.
  3. Volume, because too many records are stored
  4. Timeliness, because the data arrives too late

Explanation

High completeness only shows that fields are populated; complaints about wrong values point to accuracy, which measures correctness against a trusted source. Timeliness and volume describe latency and quantity, not correctness of content.

Question 11Data Management and Governance

A data sharing agreement allows a partner to use customer data only for fraud detection. Which governance mechanism best ensures the partner honors that restriction?

  1. Increasing the retention period for shared data
  2. Contractual purpose limitation reinforced by periodic access and usage attestation Correct answer.
  3. Publishing the restriction on the company intranet
  4. Encouraging the partner to read the corporate privacy policy

Explanation

Purpose limitation is enforced through contractual terms combined with verification such as access reviews and usage attestations. Awareness artifacts alone are unenforceable, and extending retention works against the restriction rather than supporting it.

Question 12Data Management and Governance

Which practice best supports metadata quality in a data catalog as systems change over time?

  1. Automated harvesting of technical metadata supplemented by owner-reviewed business metadata Correct answer.
  2. Manual entry of all metadata once at project inception
  3. Restricting catalog access to the architecture team
  4. Deleting catalog entries whenever a source system is upgraded

Explanation

Catalog metadata stays accurate when technical metadata is harvested automatically and business metadata is periodically reviewed by accountable owners. One-time manual entry becomes stale, and restricting or deleting entries reduces the catalog's usefulness.

Question 13Data Management and Governance

A regulator requests evidence that reported figures were produced from controlled sources. Which combination best demonstrates this?

  1. A list of analysts who have database accounts
  2. The hardware specification of the reporting server
  3. A screenshot of the final report dashboard
  4. Documented lineage from source systems to the report, with reconciliation controls and sign-off Correct answer.

Explanation

Evidence of controlled sourcing comes from end-to-end lineage, reconciliation checks, and documented approval of the reported figures. Dashboards, account lists, and hardware details do not show how figures were derived or controlled.

Question 14Data Management and Governance

When a data incident exposes personal data, which governance action should occur first to satisfy accountability obligations?

  1. Inform only the technical team that operates the database
  2. Wait for the next quarterly governance meeting to discuss it
  3. Contain the exposure and record the incident in the governance register for assessment and notification decisions Correct answer.
  4. Delete all related logs to limit further exposure

Explanation

Accountability requires prompt containment and formal incident logging so that impact, notification duties, and remediation can be assessed. Delaying, destroying evidence, or limiting communication to technicians undermines both legal obligations and governance oversight.

Question 15Data Management and Governance

A business unit wants to onboard a new SaaS tool that will store regulated data. Which governance gate should be completed before production use?

  1. A marketing review of the vendor's brand reputation
  2. A survey of employee preferences for the tool
  3. An increase in the team's data storage quota
  4. A data risk assessment covering classification, vendor controls, and contractual data protection terms Correct answer.

Explanation

Onboarding regulated data to a third party requires assessing classification, the vendor's security and privacy controls, and contractual protections before use. Brand perception, quota changes, and user preference do not address regulatory and data protection risk.

Question 16Data Management and Governance

Which approach best balances data access for analytics with privacy protection for sensitive attributes?

  1. Granting all analysts full access to speed up delivery
  2. Role-based access with de-identification or aggregation applied to sensitive fields Correct answer.
  3. Removing all access to sensitive data for every user
  4. Storing sensitive data in spreadsheets on shared drives

Explanation

Least-privilege roles combined with de-identification or aggregation let analysts work while limiting exposure of sensitive attributes. Broad access, blanket denial, and uncontrolled spreadsheets either increase risk or block legitimate use.

Question 17Data Management and Governance

A stewardship program finds that issue resolution stalls because no one can approve remediation funding. Which governance gap does this reveal?

  1. Overdocumentation of business glossary terms
  2. Insufficient data storage capacity
  3. Unclear escalation paths and decision rights for funding remediation Correct answer.
  4. Excessive automation of data quality checks

Explanation

Stalled remediation caused by missing funding approval indicates unclear escalation paths and decision rights, which a governance charter should define. Storage, automation, and glossary documentation are unrelated to who can authorize remediation spend.

Question 18Data Management and Governance

Which control most directly supports the accuracy dimension for data replicated from a source system into a reporting warehouse?

  1. Reconciliation of key totals and record counts between source and target after each load Correct answer.
  2. Granting analysts read-only access to the warehouse
  3. Scheduling loads during off-peak hours
  4. Compression of the target tables to save space

Explanation

Accuracy of replicated data is verified by reconciling totals and record counts between source and target. Compression, read access, and load timing affect performance or security but do not confirm that values match the source.

Question 19Data Management and Governance

An organization must demonstrate that access to sensitive data is granted only when justified. Which evidence best satisfies an auditor?

  1. A policy statement declaring least privilege as a principle
  2. A list of database vendors under contract
  3. Periodic access reviews with recorded approver justification and removal of unnecessary entitlements Correct answer.
  4. A count of total user accounts in the system

Explanation

Auditors look for operating evidence that entitlements were reviewed, justified, and pruned, not merely a stated principle. Account counts and vendor lists do not show that individual access was justified.

Question 20Data Management and Governance

A company operates in several jurisdictions with conflicting data localization rules. What is the most defensible governance approach?

  1. Ignore localization rules where data is encrypted
  2. Delegate the decision entirely to individual business units
  3. Apply the rules of the headquarters country to all locations
  4. Map obligations per jurisdiction and apply the strictest applicable requirement to shared data flows Correct answer.

Explanation

Conflicting obligations are managed by mapping requirements per jurisdiction and designing flows to satisfy the strictest applicable rule, which reduces the risk of breaching any one regime. Extending headquarters rules, assuming encryption exempts localization, and fragmenting decisions all create compliance gaps.

Question 21Data Management and Governance

A data quality rule flags 40 percent of records as invalid, but sampling shows most flags are false positives. What is the most appropriate governance response?

  1. Suppress the dashboard so stakeholders do not see the flags
  2. Tune the rule against validated reference data and document the change through change control Correct answer.
  3. Disable the rule permanently to avoid noise
  4. Increase the retention period of the flagged records

Explanation

A high false-positive rate indicates the rule logic or thresholds are miscalibrated, so the rule should be tuned against validated reference data and the change documented. Disabling, hiding, or retaining flagged records longer does not restore trustworthy measurement.

Question 22Data Management and Governance

Two business units claim ownership of a shared customer attribute and publish conflicting definitions to downstream reports. Which governance intervention resolves this most sustainably?

  1. Allow both definitions to persist and let report authors choose
  2. Merge both definitions into a combined field name for all reports
  3. Escalate to the vendor that supplies the reporting tool
  4. Assign a single accountable owner through the governance council and reconcile downstream consumers to one approved definition Correct answer.

Explanation

Sustainable resolution requires assigning one accountable owner with authority to arbitrate and aligning downstream consumers to the approved definition. Letting both persist, blending definitions, or deferring to a tool vendor leaves the conflict unresolved.

Question 23Data Management and Governance

An organization wants to measure whether its governance program is effective rather than merely active. Which indicator best reflects effectiveness?

  1. The count of policy documents published in the repository
  2. A declining trend in data incidents traced to unclear ownership or definitions Correct answer.
  3. The number of staff who attended awareness sessions
  4. The number of governance meetings held during the year

Explanation

Effectiveness is shown by outcomes such as fewer incidents caused by ownership or definition gaps. Meeting counts, document volumes, and attendance measure activity and output, not the reduction of governance-related risk.

Question 24Data Management and Governance

A legacy system slated for decommissioning holds the only copy of records still referenced by an active legal matter. Which action best satisfies both retention and decommissioning objectives?

  1. Proceed with decommissioning and rely on backups if the records are needed
  2. Extend the legacy system's life indefinitely for all data
  3. Preserve the required records in a governed archive with documented lineage before retiring the source Correct answer.
  4. Export the records to an unmanaged local drive and shut the system down

Explanation

Records under legal hold must be preserved with integrity and provenance, so migrating them to a governed archive before decommissioning satisfies both obligations. Relying on backups, using unmanaged drives, or keeping the whole system alive indefinitely are weaker or wasteful alternatives.

Question 25Data Management and Governance

A stewardship team must prioritize remediation across many data issues with limited resources. Which prioritization approach is most defensible?

  1. Rank issues by the order in which they were reported
  2. Rank issues by the seniority of the person who raised them
  3. Rank issues by regulatory exposure, financial impact, and number of downstream consumers affected Correct answer.
  4. Rank issues by the alphabetical order of the affected tables

Explanation

Defensible prioritization weighs materiality factors such as regulatory exposure, financial consequence, and downstream reach. Report order, requester seniority, and table names are arbitrary and can divert effort from the most consequential issues.

Question 26Data Management and Governance

Which artifact in a data governance program defines the acceptable values, format, and business meaning of a critical data element such as customer date of birth?

  1. A network access control list
  2. A data quality scorecard
  3. A business glossary entry with an associated data quality rule Correct answer.
  4. A disaster recovery plan

Explanation

A business glossary entry captures the agreed business definition, while an associated data quality rule encodes the acceptable format and value constraints. Together they give stewards and downstream consumers a single authoritative specification for the element. Scorecards and infrastructure documents describe results or controls but do not define the element itself.

Question 27Data Management and Governance

A governance council must decide who may approve changes to a shared reference dataset used by finance and operations. Which structural element most directly establishes that decision authority?

  1. A documented data ownership and decision-rights model naming accountable owners and approvers Correct answer.
  2. A data modeling notation standard
  3. A retention schedule for the reference dataset
  4. A weekly report listing open data incidents

Explanation

Decision rights are established by explicitly assigning accountability and approval authority to named roles, which is the core of a data ownership model. Without that assignment, escalation paths and approvals remain ambiguous regardless of how much reporting exists. Incident reports, retention schedules, and modeling conventions support governance but do not allocate authority.

Question 28Data Management and Governance

A privacy officer asks how the organization should treat a dataset that contains pseudonymized identifiers where the re-identification key is held by the same team. Which classification decision is most defensible?

  1. Treat it as fully anonymous because direct identifiers were removed
  2. Treat it as public because pseudonyms are not real names
  3. Treat it as non-personal because the key is stored separately
  4. Treat it as sensitive personal data because re-identification remains feasible Correct answer.

Explanation

Pseudonymization reduces exposure but does not make data anonymous when the mapping key remains available to the same team. Because re-identification is feasible, the dataset should retain a sensitive personal classification and its associated controls. Classification must reflect actual re-identification risk, not merely the absence of direct identifiers.

Question 29Data Management and Governance

An organization is adopting a metadata management capability and wants automated technical metadata to stay synchronized with its catalog. Which practice best achieves this?

  1. Requiring analysts to manually re-enter schema details each quarter
  2. Restricting catalog access to the data platform team
  3. Scheduling automated harvesters that scan sources and reconcile changes against the catalog Correct answer.
  4. Publishing a one-time inventory of systems at project kickoff

Explanation

Automated harvesting continuously extracts technical metadata from source systems and reconciles drift, which keeps the catalog current as schemas evolve. Manual re-entry and one-time inventories decay quickly and introduce transcription errors. Restricting access limits consumption without improving synchronization.

Question 30Data Management and Governance

A governance lead must demonstrate that a regulatory report is reproducible. Which combination of controls provides the strongest evidence?

  1. A policy statement declaring that reports are accurate
  2. Version-controlled transformation code, captured source snapshots, and logged approval of the report logic Correct answer.
  3. An annual internal audit of the reporting team's staffing
  4. A dashboard showing the report was viewed by senior management

Explanation

Reproducibility requires that the exact inputs, the exact logic, and the authorization of that logic can all be reconstructed later. Version-controlled code, retained source snapshots, and logged approvals together make a prior result re-creatable on demand. Viewing records, policy declarations, and staffing reviews show activity or intent but cannot reproduce a number.

Question 31Data Architecture and Modeling

In a star schema, what does a fact table primarily store?

  1. The descriptive attributes used for filtering and grouping
  2. The hierarchical roll-up paths between reporting levels
  3. The physical storage mapping for each dimension
  4. Measurable business events with foreign keys to dimension tables Correct answer.

Explanation

A fact table records business events at a declared grain, holding numeric measures plus foreign keys that reference dimension tables. Descriptive context lives in the dimensions, not in the fact table.

Question 32Data Architecture and Modeling

Which characteristic distinguishes a snowflake schema from a star schema?

  1. Measures are stored as text instead of numbers
  2. Fact tables are removed and replaced by views
  3. Dimension tables are normalized into multiple related tables Correct answer.
  4. Each dimension is limited to a single attribute

Explanation

A snowflake schema normalizes dimensions, splitting them into related sub-tables such as product to category to department. A star schema keeps each dimension as one denormalized table.

Question 33Data Architecture and Modeling

A data modeler declares that each row of a sales fact table represents one product on one order line. What concept does this define?

  1. The grain of the fact table Correct answer.
  2. The surrogate key policy
  3. The aggregation level of the cube
  4. The slowly changing dimension type

Explanation

Grain specifies exactly what a single fact row represents, and every measure must be consistent with that level of detail. Declaring grain before choosing measures prevents double counting and mixed-granularity errors.

Question 34Data Architecture and Modeling

What is the purpose of a surrogate key in a dimension table?

  1. To encrypt sensitive attributes stored in the dimension
  2. To store the natural business key as a numeric value
  3. To provide a stable, compact identifier independent of source system keys Correct answer.
  4. To enforce referential integrity between two fact tables

Explanation

A surrogate key is a system-generated identifier that insulates the warehouse from changes in source system keys and supports tracking multiple versions of a record. It is not derived from business data and carries no meaning.

Question 35Data Architecture and Modeling

Which modeling approach stores each attribute change as a new row with effective dates in the dimension?

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

Explanation

A Type 2 dimension preserves full history by inserting a new row per change, with effective and expiration dates plus a current-record flag. Type 1 overwrites and Type 3 adds limited prior-value columns.

Question 36Data Architecture and Modeling

In dimensional modeling terminology, what is a degenerate dimension?

  1. A dimension shared by two different fact tables
  2. A dimension that has been fully normalized
  3. A dimension whose attributes are all null
  4. A dimension key stored in the fact table with no separate dimension table Correct answer.

Explanation

A degenerate dimension is a business identifier such as an order number that sits in the fact table because it has no descriptive attributes worth a separate table. It still supports grouping and drill-through.

Question 37Data Architecture and Modeling

Which schema organizes data as entities with attributes and explicit relationships, typically for OLTP systems?

  1. Star schema
  2. Data vault model
  3. Third normal form entity-relationship model Correct answer.
  4. Dimensional model

Explanation

Third normal form entity-relationship modeling minimizes redundancy and supports transactional integrity, making it the standard for operational systems. Star, data vault, and dimensional models are analytical patterns.

Question 38Data Architecture and Modeling

What does a conformed dimension enable across multiple fact tables?

  1. Removal of the need for foreign keys
  2. Automatic partitioning of the fact tables
  3. Guaranteed real-time data freshness
  4. Consistent filtering and drill-across using shared attribute definitions Correct answer.

Explanation

A conformed dimension uses identical keys, attributes, and definitions wherever it appears, so separate facts can be compared and drilled across consistently. Without conformance, cross-process analysis produces inconsistent results.

Question 39Data Architecture and Modeling

A retail warehouse must report revenue by product category and by store region, and analysts frequently slice by both. Which design best supports these queries?

  1. A key-value store with revenue stored as opaque blobs
  2. A single wide table containing every attribute with no keys
  3. A fully normalized model with product, category, store, and region in separate chains
  4. A star schema with product and store dimensions linked to a sales fact table Correct answer.

Explanation

A star schema places each dimension directly against the fact table, giving simple joins and predictable performance for slice-and-dice queries. Normalizing dimensions adds join depth without analytical benefit here.

Question 40Data Architecture and Modeling

A customer dimension must retain the full history of address changes while reporting always uses the current address for operational lookups. Which approach meets both needs?

  1. Type 2 rows with effective dates plus a current-flag view Correct answer.
  2. Type 1 overwrite of the address column
  3. Type 3 with one previous-address column
  4. Dropping the address from the dimension and storing it in the fact table

Explanation

Type 2 rows preserve every version with validity dates, and a current-record flag or view lets lookups resolve to the active row. This satisfies historical analysis and current-state reporting simultaneously.

Question 41Data Architecture and Modeling

Two source systems identify the same customer with different IDs. What is the most appropriate modeling technique to unify them in a dimension?

  1. Assign a surrogate key and cross-reference both source IDs in a mapping table Correct answer.
  2. Concatenate both source IDs into a composite primary key
  3. Store both IDs in the fact table and skip the dimension
  4. Use the first source system ID as the primary key

Explanation

A surrogate key gives one warehouse identity while a cross-reference or bridge table records each source identifier. This decouples the warehouse from source key changes and supports integration.

Question 42Data Architecture and Modeling

A fact table records individual order lines, but a report needs monthly totals by region. What is the correct approach?

  1. Change the fact table grain to monthly region totals and discard line detail
  2. Add a month attribute to the fact table and remove the order line key
  3. Store monthly totals as additional columns on each line row
  4. Aggregate the line-level facts to the monthly region grain in a derived table or materialized view Correct answer.

Explanation

Aggregation should be derived from the finest grain so detail remains available and totals reconcile. Changing the base grain or embedding totals per row breaks additive measure semantics.

Question 43Data Architecture and Modeling

A dimension contains many low-cardinality flags such as is_gift, is_promo, and is_return. Which technique reduces dimension clutter?

  1. Move the flags into the fact table as measures
  2. Convert each flag into its own dimension table
  3. Combine the flags into a junk dimension Correct answer.
  4. Replace the flags with surrogate keys

Explanation

A junk dimension groups unrelated low-cardinality flags and indicators into a single dimension with a generated key. This keeps the star tidy and avoids many tiny dimension tables.

Question 44Data Architecture and Modeling

Which pattern handles a many-to-many relationship between a fact and a dimension, such as one account belonging to several sales reps?

  1. A degenerate dimension holding the rep list
  2. A snowflaked dimension with the reps normalized
  3. A bridge table between the fact and the dimension with weighting factors Correct answer.
  4. A single foreign key in the fact table pointing to one rep

Explanation

A bridge table resolves many-to-many relationships and can carry allocation weights so measures are not double counted. A single foreign key cannot represent multiple simultaneous memberships.

Question 45Data Architecture and Modeling

A team wants an auditable warehouse model that loads raw source data quickly and separates business rules from ingestion. Which modeling style fits best?

  1. Star schema with Type 1 dimensions
  2. Entity-attribute-value tables
  3. Data vault with hubs, links, and satellites Correct answer.
  4. Wide denormalized reporting tables

Explanation

Data vault isolates raw ingestion into hubs, links, and satellites, keeping business logic in later layers and providing auditability. Star schemas are better suited to presentation than to raw integration.

Question 46Data Architecture and Modeling

What is the main trade-off when choosing a snowflaked dimension over a star dimension?

  1. Stronger referential integrity but no support for history
  2. Less storage redundancy but more joins and complexity for consumers Correct answer.
  3. Faster queries with simpler SQL but greater redundancy
  4. Better real-time loading but weaker security

Explanation

Snowflaking saves space by normalizing repeated attribute values but requires additional joins and complicates the model for business users. Star schemas accept redundancy in exchange for simplicity and speed.

Question 47Data Architecture and Modeling

Why does dimensional modeling recommend integer surrogate keys over natural business keys in fact tables?

  1. They eliminate the need for indexes on the fact table
  2. They allow measures to be stored as text
  3. They reduce storage and join cost while isolating the warehouse from source key changes Correct answer.
  4. They automatically enforce uniqueness across all source systems

Explanation

Compact integer keys shrink fact tables and speed joins, and they remain stable when source systems renumber their identifiers. Uniqueness across sources still requires deliberate integration logic.

Question 48Data Architecture and Modeling

A modeler adds a date dimension with attributes for fiscal period, holiday flag, and prior-year date. What primary benefit does this provide?

  1. Automatic generation of surrogate keys
  2. Reduced need for fact table partitioning
  3. Consistent time-based filtering and calculations across all fact tables Correct answer.
  4. Elimination of slowly changing dimensions

Explanation

A rich date dimension centralizes calendar and fiscal definitions so every fact uses the same time logic. It also supports relative-period calculations via attributes such as prior-year date.

Question 49Data Architecture and Modeling

A fact table mixes revenue at order-line grain with a budget amount stored only at monthly region grain. What problem does this create?

  1. The fact table will violate third normal form
  2. Surrogate keys will collide between the two measures
  3. The date dimension can no longer be joined
  4. Mixed grain causes incorrect aggregation and double counting Correct answer.

Explanation

Measures at different grains must not share a fact table, because summing or joining them produces inflated results. Budget at monthly region grain belongs in its own fact table sharing conformed dimensions.

Question 50Data Architecture and Modeling

Which statement best describes a role-playing dimension?

  1. A dimension shared between two independent warehouses
  2. A dimension with no attributes other than its key
  3. One physical dimension referenced multiple times in a fact table under different roles Correct answer.
  4. A dimension that changes its attributes on every load

Explanation

A role-playing dimension is a single table used for several purposes, such as order date and ship date both referencing the date dimension. Each role uses a distinct foreign key and view alias.