60 Practice Questions & Answers
Which of the following best describes the purpose of data normalization in relational databases?
-
A
To increase query execution speed
-
B
To create backup copies of database tables
-
C
To encrypt sensitive data fields
-
D
To reduce data redundancy and improve data integrity
✓ Correct
Explanation
Data normalization organizes data to reduce redundancy and dependency, improving data integrity and consistency across the database.
In a data warehouse architecture, what is the primary function of the staging area?
-
A
To serve as the final destination for archived data
-
B
To cache frequently accessed dimension tables
-
C
To temporarily hold extracted data before transformation and loading into the data warehouse
✓ Correct
-
D
To perform real-time analytics on incoming data streams
Explanation
The staging area acts as an intermediate storage location where extracted data is held, validated, and prepared before being transformed and loaded into the data warehouse.
Which normal form eliminates transitive dependencies in a relational table?
-
A
Second Normal Form (2NF)
-
B
First Normal Form (1NF)
-
C
Third Normal Form (3NF)
✓ Correct
-
D
Boyce-Codd Normal Form (BCNF)
Explanation
Third Normal Form (3NF) requires that non-key attributes depend only on the primary key, eliminating transitive dependencies where one non-key attribute depends on another.
A data analyst needs to combine data from multiple sources with different schemas. Which technique is most appropriate?
-
A
Data lineage tracking
-
B
Index optimization and partitioning
-
C
Data reconciliation and schema mapping
✓ Correct
-
D
Data compression and archival
Explanation
Schema mapping and data reconciliation techniques align different data structures and resolve semantic differences when integrating data from heterogeneous sources.
What is the primary advantage of implementing slowly changing dimensions (SCDs) in a data warehouse?
-
A
Improving query performance on dimension tables
-
B
Automating the extraction process from source systems
-
C
Maintaining historical accuracy of dimension attributes while tracking changes over time
✓ Correct
-
D
Reducing storage requirements for historical fact tables
Explanation
SCDs enable data warehouses to capture and preserve historical changes to dimension attributes, allowing accurate historical analysis and reporting.
In data quality management, which metric measures the percentage of records without critical missing values?
-
A
Uniqueness
-
B
Consistency
-
C
Completeness
✓ Correct
-
D
Accuracy
Explanation
Completeness measures the extent to which required data is present in a dataset, typically calculated as the percentage of non-null or non-missing values.
A data governance framework requires implementing role-based access controls (RBAC) across multiple databases. Which implementation approach best ensures consistency?
-
A
Relying on application-level security controls only
-
B
Configuring identical permissions in each database separately
-
C
Using a centralized directory service with federated authentication and synchronized role definitions
✓ Correct
-
D
Creating local user accounts on every database server
Explanation
Centralized directory services with federation ensure consistent, synchronized role definitions across all databases, reducing administrative overhead and security gaps.
Which of the following best describes the relationship between a fact table and dimension tables in a star schema?
-
A
Dimension tables reference multiple fact tables through composite primary keys
-
B
Fact tables contain aggregated metrics while dimension tables contain detailed transactions
-
C
Dimension tables contain business context and descriptive attributes while fact tables store quantifiable metrics and foreign keys to dimensions
✓ Correct
-
D
Fact tables are updated monthly while dimension tables are updated in real-time
Explanation
In a star schema, fact tables store measurable events and metrics with foreign key references to dimension tables, which provide descriptive context and business attributes.
A data pipeline fails intermittently due to connection timeouts. Which monitoring strategy would best identify the root cause?
-
A
Removing error handling code to see raw error messages
-
B
Implementing log aggregation with pattern matching for timeout errors and correlating with system resource metrics
✓ Correct
-
C
Increasing the timeout threshold values across all pipeline stages
-
D
Scheduling all data loads during off-peak hours
Explanation
Log aggregation combined with resource monitoring helps identify whether timeouts result from network issues, insufficient resources, or problematic data sources.
In ETL processes, what is the primary purpose of data validation rules?
-
A
To encrypt sensitive data during transit
-
B
To automatically generate SQL queries for transformation logic
-
C
To compress data files for storage efficiency
-
D
To ensure only data meeting defined quality standards enters the target system
✓ Correct
Explanation
Data validation rules enforce quality standards by checking data against business rules, format requirements, and constraints before loading into target systems.
Which approach to master data management (MDM) maintains a single authoritative source of truth while allowing operational systems to cache copies?
-
A
Manual reconciliation of conflicting records quarterly
-
B
Registry pattern with local caching and periodic synchronization
✓ Correct
-
C
Complete data duplication across all systems
-
D
Decentralized data ownership with independent validation rules
Explanation
The registry pattern establishes a central MDM repository as the authoritative source while allowing operational systems to maintain synchronized local copies for performance.
When designing a data mart for a specific business unit, which factor should primarily influence the dimensional model?
-
A
The underlying database management system vendor
-
B
The specific business processes and analytical questions the unit needs to answer
✓ Correct
-
C
The total storage capacity available in the data center
-
D
The programming languages used by the development team
Explanation
Data mart design should be driven by business requirements and analytical needs, ensuring the dimensional model directly supports decision-making for that business unit.
A data integration project requires combining real-time streaming data with batch historical data. Which architecture pattern is most suitable?
-
A
Micro-services architecture with independent microservices per data source
-
B
Kappa architecture exclusively using stream processing
-
C
Batch-only architecture with delayed reporting
-
D
Lambda architecture combining real-time and batch processing paths that merge at the serving layer
✓ Correct
Explanation
Lambda architecture effectively handles both real-time and batch data by maintaining separate processing paths that converge in a serving layer, providing both low-latency and accurate results.
In a data catalog system, what metadata should be captured for data lineage tracking?
-
A
Only the creation date and last modification date of datasets
-
B
Employee names and approval dates for dataset access
-
C
The physical file paths and server locations exclusively
-
D
Source systems, transformation logic, intermediate steps, and target destinations throughout the data flow
✓ Correct
Explanation
Effective data lineage captures the complete flow of data from sources through transformations to destinations, enabling impact analysis and regulatory compliance.
Which data quality issue occurs when similar entities are represented with inconsistent naming conventions or formatting?
-
A
Schema mismatch or incompatibility
-
B
Referential integrity violation
-
C
Timeliness delay or staleness
-
D
Duplicate records or fuzzy duplicates
✓ Correct
Explanation
Fuzzy duplicates result from inconsistent formatting, spelling, or naming conventions for the same entity (e.g., 'John Smith' vs 'J. Smith'), creating data quality challenges.
A organization implements data classification requiring sensitive personal information to be encrypted at rest. Which implementation consideration is most critical?
-
A
Choosing the fastest encryption algorithm available
-
B
Using the same encryption key for all data classification levels
-
C
Encrypting only the fields identified through the classification process and managing encryption keys securely with proper rotation and access controls
✓ Correct
-
D
Storing encryption keys in the same database as encrypted data
Explanation
Proper encryption implementation requires identifying sensitive fields through classification, using appropriate algorithms, and implementing robust key management with rotation and access controls.
What is the primary difference between operational databases and analytical databases in terms of query patterns?
-
A
Operational databases use only SELECT statements while analytical databases use UPDATE statements
-
B
Operational databases handle frequent small transactions while analytical databases process complex queries scanning large datasets
✓ Correct
-
C
Operational databases are always larger than analytical databases
-
D
Analytical databases do not support join operations
Explanation
Operational databases are optimized for frequent transactional operations (OLTP), while analytical databases are optimized for complex aggregations over large datasets (OLAP).
In a data governance structure, what is the primary responsibility of a Data Steward?
-
A
Writing and executing SQL queries for data extraction
-
B
Managing all company finances and budget allocation
-
C
Purchasing hardware and infrastructure for data storage
-
D
Owning specific data domains, ensuring quality, documenting business rules, and maintaining data definitions
✓ Correct
Explanation
Data Stewards are accountable for their assigned data domains, responsible for documentation, quality standards, business rule enforcement, and metadata management.
Which approach to handling missing data in analytical datasets best preserves statistical validity in most scenarios?
-
A
Using domain-appropriate imputation methods or excluding specific analyses from incomplete records, documented transparently
✓ Correct
-
B
Filling all missing values with zeros
-
C
Deleting all rows containing any missing values
-
D
Replacing missing values with average values from unrelated datasets
Explanation
Proper missing data handling employs domain-specific imputation or documented exclusion rather than simplistic approaches that can introduce bias or distort statistical results.
A data warehouse receives updates from multiple source systems at different times. Which approach best handles potential reconciliation issues?
-
A
Implementing change data capture (CDC) mechanisms that track modifications and applying updates with proper sequencing and reconciliation checks
✓ Correct
-
B
Requiring all source systems to update simultaneously
-
C
Accepting data inconsistencies as unavoidable in multi-source environments
-
D
Performing monthly full database refreshes to overwrite all data
Explanation
Change Data Capture tracks incremental changes from source systems, enabling proper sequencing and reconciliation of updates to maintain data consistency.
In database partitioning strategies, when is horizontal partitioning (sharding) most beneficial?
-
A
When normalizing denormalized data structures
-
B
When reducing the number of columns in a table
-
C
When distributing large datasets across multiple servers to improve query performance and scalability for high-volume data
✓ Correct
-
D
When creating backups of database tables
Explanation
Horizontal partitioning distributes rows across multiple physical locations based on partition keys, enabling parallel processing and improved scalability for large datasets.
Which metric in data quality frameworks measures whether data values conform to defined formats, patterns, and expected ranges?
-
A
Timeliness
-
B
Validity
✓ Correct
-
C
Conformity
-
D
Lineage
Explanation
Validity measures whether data conforms to defined business rules, formats, data types, and acceptable value ranges for its intended use.
A data integration platform must support real-time synchronization of customer data across multiple operational systems. Which technical pattern best addresses this requirement with minimal latency?
-
A
Annual data synchronization events with IT coordination
-
B
Message-oriented middleware with publish-subscribe architecture and event-driven processing enabling near-real-time data propagation
✓ Correct
-
C
Manual data entry procedures with supervisor approval
-
D
Scheduled batch jobs running every hour
Explanation
Message-oriented middleware with publish-subscribe patterns enables event-driven, near-real-time data propagation, making it ideal for systems requiring minimal latency.
In data warehouse design, what is the primary purpose of using conformed dimensions across multiple fact tables?
-
A
To reduce the physical size of dimension tables
-
B
To eliminate the need for primary keys in dimension tables
-
C
To ensure consistent business logic and enable drill-across analysis between different business processes
✓ Correct
-
D
To automate all data transformation processes
Explanation
Conformed dimensions use identical keys and attributes across multiple fact tables, ensuring consistency and enabling seamless drill-across analysis across different business areas.
Which data profiling technique is most effective for identifying patterns, distributions, and potential anomalies in large datasets?
-
A
Automated statistical analysis with visualizations, cardinality assessment, and null value detection across complete datasets
✓ Correct
-
B
Manual inspection of random sample rows
-
C
Reviewing database table sizes only
-
D
Counting the total number of records
Explanation
Automated data profiling uses statistical methods and visualization to analyze complete datasets efficiently, identifying patterns, distributions, and anomalies that manual inspection would miss.
Which of the following best describes the primary purpose of data normalization in relational databases?
-
A
To convert unstructured data into structured formats only
-
B
To eliminate data redundancy and improve data integrity
✓ Correct
-
C
To reduce the number of tables in a database schema
-
D
To increase query performance by denormalizing tables
Explanation
Data normalization removes redundancy and ensures consistency, maintaining referential integrity through a structured approach to database design.
In a distributed data system, what is the primary challenge addressed by the CAP theorem?
-
A
Optimizing cache coherency across network nodes
-
B
Managing compression and archival processes effectively
-
C
Ensuring all nodes have identical processing capabilities
-
D
Balancing consistency, availability, and partition tolerance simultaneously
✓ Correct
Explanation
The CAP theorem states that distributed systems can guarantee at most two of three properties: consistency, availability, and partition tolerance—forcing architects to make trade-offs.
Which indexing strategy would be most appropriate for a column with low cardinality that is frequently used in WHERE clauses?
-
A
Full-text index
-
B
Bitmap index
✓ Correct
-
C
No index, as low cardinality columns don't benefit from indexing
-
D
Hash index for all queries
Explanation
Bitmap indexes are highly efficient for low-cardinality columns, using minimal space and providing fast lookups compared to traditional B-tree indexes.
What is the primary advantage of using a columnar storage format like Parquet in data warehouse environments?
-
A
It automatically handles schema evolution without manual intervention
-
B
It stores only the columns needed for a query, improving I/O efficiency and compression
✓ Correct
-
C
It eliminates the need for indexing strategies entirely
-
D
It allows unlimited row-level security without performance degradation
Explanation
Columnar formats like Parquet store data by column rather than row, enabling selective column retrieval and superior compression ratios for analytical workloads.
In the context of ETL processes, what does 'slowly changing dimension' (SCD) Type 2 accomplish?
-
A
It rejects any updates that would alter the dimension and logs them as errors
-
B
It maintains historical records by creating new rows with version timestamps when dimension attributes change
✓ Correct
-
C
It overwrites old records with new values, keeping only current state
-
D
It stores changes in a separate audit table without modifying the dimension table
Explanation
SCD Type 2 preserves historical data by inserting new rows with version numbers and effective dates, allowing queries to see both current and past values.
Which data quality issue occurs when a dataset contains multiple representations of the same entity?
-
A
Data type mismatch
-
B
Referential integrity violation
-
C
Schema inconsistency
-
D
Duplicate records
✓ Correct
Explanation
Duplicate records represent the same real-world entity multiple times with different or identical values, requiring deduplication techniques for accurate analysis.
What is the primary purpose of implementing a data lake architecture rather than a traditional data warehouse?
-
A
To store raw, unprocessed data at scale while maintaining schema flexibility for diverse analytical needs
✓ Correct
-
B
To restrict data access and reduce compliance requirements compared to warehouses
-
C
To guarantee faster query performance through pre-aggregated dimensional tables
-
D
To eliminate the need for any data transformation or cleansing processes
Explanation
Data lakes enable storage of raw data in multiple formats with flexible schemas, supporting exploration and analytics on diverse data types without predefined structure.
In a multi-tenant SaaS data architecture, which isolation approach provides the strongest data security at the potential cost of resource efficiency?
-
A
In-memory caching without persistent separation
-
B
Separate databases per tenant
✓ Correct
-
C
Shared database with row-level security filtering
-
D
Shared schema with discriminator columns
Explanation
Separate databases per tenant provide complete logical and physical isolation, preventing accidental cross-tenant data access, though requiring more storage and maintenance overhead.
What is the primary challenge when implementing eventual consistency in distributed database systems?
-
A
Applications must tolerate temporary inconsistency and handle conflicts that may arise from concurrent updates across nodes
✓ Correct
-
B
It requires synchronous replication which increases latency uniformly across all operations
-
C
It eliminates the ability to perform transactions across multiple data sources entirely
-
D
The system cannot support read operations until all writes are fully propagated globally
Explanation
Eventual consistency trades immediate consistency for availability, requiring applications to handle stale reads and implement conflict resolution strategies.
Which normalization form eliminates transitive dependencies where non-key attributes depend on other non-key attributes?
-
A
Second Normal Form (2NF)
-
B
Boyce-Codd Normal Form (BCNF)
-
C
Third Normal Form (3NF)
✓ Correct
-
D
First Normal Form (1NF)
Explanation
3NF ensures that all non-key attributes depend directly on the primary key, not on other non-key attributes, eliminating transitive dependencies.
In data governance frameworks, what is the primary role of a data steward?
-
A
To ensure data quality, manage metadata, and enforce policies within their assigned domain
✓ Correct
-
B
To design and implement all database schemas and technical infrastructure
-
C
To make final decisions on all data-related budgets and vendor selections
-
D
To perform all backup and disaster recovery operations independently
Explanation
Data stewards are domain experts responsible for data quality, documentation, policy compliance, and business rule enforcement within their area of responsibility.
What is the primary advantage of implementing a lambda architecture in real-time analytics systems?
-
A
It combines batch processing and real-time stream processing to provide both accuracy and low-latency insights
✓ Correct
-
B
It eliminates the need for data validation and quality checks in both streams and batch layers
-
C
It guarantees that all data is processed exactly once without any reprocessing overhead
-
D
It reduces storage requirements by automatically deleting historical data after 90 days
Explanation
Lambda architecture uses a batch layer for comprehensive accuracy and a speed layer for real-time results, merging both views to provide complete analytics.
Which SQL operation would be most efficient for identifying records that exist in one table but not in another?
-
A
UNION ALL combined with DISTINCT filtering
-
B
INNER JOIN filtered by aggregate functions
-
C
LEFT JOIN with NULL check on the right table's key column
✓ Correct
-
D
CROSS JOIN followed by GROUP BY aggregation
Explanation
A LEFT JOIN with IS NULL on the right table's key identifies non-matching records efficiently, avoiding the overhead of other approaches.
In the context of master data management (MDM), what does the 'golden record' concept represent?
-
A
The oldest historical record of an entity retained for compliance purposes
-
B
The most frequently accessed record in a table based on query logs
-
C
The record with the highest monetary value or transaction amount
-
D
The single, authoritative version of an entity created by consolidating and validating data from multiple sources
✓ Correct
Explanation
The golden record is the unified, validated version of truth for an entity, created by matching, merging, and deduplicating data from disparate systems.
What is the primary purpose of implementing a data virtualization layer in an enterprise architecture?
-
A
To provide unified logical access to data distributed across heterogeneous physical sources without centralizing storage
✓ Correct
-
B
To guarantee that all queries execute in less than one second regardless of data volume
-
C
To automatically encrypt all data at rest and in transit using quantum algorithms
-
D
To completely eliminate the need for any extract, transform, and load processes
Explanation
Data virtualization creates a virtual abstraction layer over multiple data sources, enabling unified querying without physically moving data or rebuilding warehouses.
Which data profiling technique would be most suitable for identifying patterns in customer purchase behavior?
-
A
Statistical profiling with frequency analysis and distribution examination across transaction attributes
✓ Correct
-
B
Schema validation to ensure all columns have proper data types
-
C
Dependency mapping to track foreign key relationships only
-
D
Encryption strength assessment to verify password standards
Explanation
Statistical profiling examines value distributions and patterns, revealing behavioral trends and outliers essential for customer behavior analysis.
In a dimensional model, what is the primary distinction between a fact table and a dimension table?
-
A
Fact tables are updated daily while dimension tables remain static indefinitely
-
B
Fact tables use surrogate keys and dimension tables use only natural keys
-
C
Fact tables store measured events with foreign keys to dimensions, while dimension tables store descriptive attributes that provide context
✓ Correct
-
D
Fact tables are denormalized and dimension tables are always normalized to 3NF
Explanation
Fact tables contain quantifiable measures and foreign keys, while dimensions contain descriptive information that enables slicing and dicing analysis.
What is the primary challenge of maintaining consistency in a sharded database where data is partitioned across multiple servers?
-
A
Each shard automatically maintains identical copies requiring significant bandwidth overhead
-
B
Executing transactions and queries that span multiple shards requires distributed coordination, increasing complexity and potential for consistency violations
✓ Correct
-
C
Sharding prevents the use of indexes entirely, requiring only full table scans
-
D
Queries cannot filter data and must scan all shards sequentially regardless of shard key usage
Explanation
Cross-shard queries and distributed transactions require coordination mechanisms, while shard-local operations remain simpler and more consistent.
Which approach best mitigates the risk of bias in machine learning models trained on historical data?
-
A
Removing all categorical variables from the training dataset to prevent group-based predictions
-
B
Applying uniform weights to all data points regardless of their accuracy or recency
-
C
Analyzing training data for underrepresented populations, applying sampling techniques, and monitoring model predictions across demographic groups
✓ Correct
-
D
Using only the largest datasets available to ensure statistical significance
Explanation
Bias mitigation requires identifying imbalances, applying stratified sampling, and continuous monitoring across population segments to ensure fair model behavior.
In data pipeline orchestration, what is the primary advantage of implementing idempotent operations?
-
A
Retrying failed steps or replaying the entire pipeline produces identical results without duplication or data corruption
✓ Correct
-
B
It eliminates the requirement for transaction logs or audit trails in the pipeline
-
C
It allows data processing to occur in any arbitrary order regardless of logical dependencies
-
D
It automatically adjusts processing speed based on system load and available resources only
Explanation
Idempotent operations ensure that repeated execution with the same input always produces the same output, enabling safe retries and recovery strategies.
What does the principle of 'data minimization' in privacy regulations such as GDPR require organizations to do?
-
A
Collect and retain only the personal data necessary to achieve specified, legitimate purposes, deleting data when no longer needed
✓ Correct
-
B
Store all customer data indefinitely to provide comprehensive historical analysis
-
C
Eliminate all personal identifiers from datasets to create completely anonymous records
-
D
Encrypt all databases using the strongest available algorithms to prevent any unauthorized access
Explanation
Data minimization requires organizations to justify every data element collected, retain only what's necessary, and implement deletion policies when data becomes obsolete.
Which scenario best demonstrates the need for implementing a data catalog in an enterprise environment?
-
A
A small business wants to reduce database licensing costs by consolidating all data into one system
-
B
A single department with fewer than 50 tables needs to optimize query performance for monthly reports
-
C
Multiple teams struggle to discover, understand, and track data ownership when thousands of datasets exist across distributed systems
✓ Correct
-
D
An organization requires immediate compliance with a new regulatory requirement without process changes
Explanation
Data catalogs provide metadata management, lineage tracking, and discovery capabilities essential for large enterprises managing numerous datasets and stakeholders.
In time-series data analysis, what is the primary purpose of applying a smoothing technique such as exponential smoothing?
-
A
To increase the granularity of data points by interpolating values between observations
-
B
To eliminate the need for seasonal adjustment and stationarity testing in advanced models
-
C
To reduce noise and volatility in raw data while preserving underlying trends for more accurate forecasting and anomaly detection
✓ Correct
-
D
To convert time-series data into a categorical format suitable for classification algorithms
Explanation
Smoothing techniques filter out short-term fluctuations and highlight trends, improving the signal-to-noise ratio for downstream analysis and forecasting.
What is the primary risk when merging datasets with different levels of data quality without performing quality assessment?
-
A
All merged records automatically become duplicates requiring extensive deduplication processing
-
B
The merged dataset loses all indexing capabilities and requires complete reconstruction
-
C
Low-quality data from one source can contaminate the entire merged dataset, propagating errors through downstream analysis and decision-making
✓ Correct
-
D
Merging datasets always increases storage requirements exponentially regardless of their sizes
Explanation
Without quality assessment, poor data from any source can compromise the entire merged dataset's integrity, affecting all downstream applications and insights.
Which architectural pattern is most suitable for organizations requiring real-time analytics on streaming data with historical context?
-
A
Traditional star schema optimized for batch reporting only without any streaming capabilities
-
B
Graph database storing only relationships without dimensional analysis support
-
C
Microservices with REST APIs that query a read-only snapshot updated quarterly
-
D
Kappa architecture with a unified stream processing platform that maintains state for historical queries while processing new events continuously
✓ Correct
Explanation
Kappa architecture uses stream processing as the primary compute model, maintaining state and enabling both real-time and historical querying from the same infrastructure.
When designing a data warehouse schema, which normalization form is typically AVOIDED to optimize query performance?
-
A
First Normal Form (1NF)
-
B
Second Normal Form (2NF)
-
C
Third Normal Form (3NF)
✓ Correct
-
D
Boyce-Codd Normal Form (BCNF)
Explanation
Data warehouses intentionally denormalize data to Third Normal Form and beyond, using dimensional models like star schemas to improve query performance and reduce join operations.
What is the primary purpose of a fact table in a dimensional data model?
-
A
To store slowly changing dimensions and historical attribute values
-
B
To contain foreign keys linking to dimension tables and numeric measurements for analysis
✓ Correct
-
C
To cache aggregated results for real-time reporting dashboards
-
D
To maintain referential integrity constraints across all data sources
Explanation
Fact tables store the core business metrics (measures) and foreign keys to dimension tables, forming the center of a star or snowflake schema.
In data governance, which framework primarily addresses data quality, metadata management, and risk mitigation?
-
A
COBIT (Control Objectives for Information and Related Technology)
-
B
TOGAF (The Open Group Architecture Framework)
-
C
DMBOK (Data Management Body of Knowledge) and similar governance frameworks
✓ Correct
-
D
ITIL (Information Technology Infrastructure Library)
Explanation
DMBOK provides comprehensive guidance on data governance domains including data quality, metadata management, and organizational data stewardship practices.
Which ETL pattern is most appropriate when source data arrives in real-time streams and must be available within seconds for operational dashboards?
-
A
Full table scans with incremental delta detection on weekly cycles
-
B
Change Data Capture (CDC) with asynchronous batch consolidation every six hours
-
C
Micro-batch processing that groups incoming records into small windows and processes continuously
✓ Correct
-
D
Batch processing with scheduled nightly loads and morning data availability
Explanation
Micro-batch processing balances the needs of near-real-time availability with the scalability of batch-oriented frameworks, making it ideal for operational dashboards.
A data analyst reports that monthly revenue reports show different totals depending on which database view is queried. What is the MOST LIKELY cause?
-
A
Query optimization causing the database engine to apply different join orders
-
B
The views reference different underlying tables with conflicting primary keys
-
C
Inconsistent data quality remediation rules applied across views or different aggregation logic
✓ Correct
-
D
Network latency causing partial record retrieval during peak business hours
Explanation
Different aggregation logic, filtering rules, or data quality standards applied across views commonly cause metric inconsistencies; this signals a need for improved data governance and metric definitions.
What does a slowly changing dimension (SCD) Type 2 approach accomplish that Type 1 cannot?
-
A
It automatically compresses dimension tables to reduce storage overhead significantly
-
B
It preserves historical changes by creating new rows with effective dating while maintaining referential integrity
✓ Correct
-
C
It completely eliminates the need for surrogate keys in dimension tables
-
D
It enables real-time synchronization without requiring any staging tables or transformation logic
Explanation
SCD Type 2 adds new rows with version control and effective dates (e.g., effective_date, end_date) to preserve complete historical change records, while Type 1 simply overwrites old values.
In a multi-source data integration scenario, how should business rule conflicts between source systems BEST be resolved during the data modeling phase?
-
A
Always prioritize the source system with the longest operating history and assume it has the most accurate business logic
-
B
Document conflicting rules explicitly, engage business stakeholders to define a single golden standard, and implement that consistently
✓ Correct
-
C
Implement all conflicting rules in the target system and allow end users to choose which version they prefer at query time
-
D
Create separate isolated data marts for each source system to avoid merging conflicting definitions altogether
Explanation
Resolving business rule conflicts requires explicit documentation and stakeholder engagement to define a single source of truth; this is fundamental to data governance and prevents analytics inconsistencies.
Which data profiling activity is MOST critical to perform before implementing a data warehouse?
-
A
Documenting all hardware specifications of the source system infrastructure
-
B
Measuring network bandwidth utilization during peak extract operations
-
C
Analyzing query execution plans to identify missing indexes
-
D
Assessing the completeness, accuracy, and consistency of source data to understand data quality baselines and constraints
✓ Correct
Explanation
Data profiling reveals data quality issues, patterns, and cardinality that must be understood before design; it informs data cleansing rules and transformation logic.
An organization implements a data lake using cloud object storage but analysts report slow query performance on large analytical queries. Which architectural decision is MOST LIKELY responsible?
-
A
Storing all data in a single flat directory structure without partitioning or organizing by domain and time periods
✓ Correct
-
B
Choosing cloud object storage instead of a traditional data warehouse database which is inherently faster
-
C
Excessive use of columnar file formats such as Parquet, which are known to cause performance degradation
-
D
Failure to implement a metadata catalog and query optimization layer between the data lake and analytical tools
Explanation
Poor data lake organization without partitioning and hierarchical structure forces tools to scan entire datasets; proper partitioning by date, domain, and business unit significantly improves query performance.
What is the PRIMARY advantage of implementing master data management (MDM) in an organization?
-
A
It automatically detects and removes all duplicate records without requiring any human review or business logic configuration
-
B
It reduces the total cost of ownership by consolidating all data storage into a single proprietary vendor platform
-
C
It eliminates the need for data integration and allows each department to maintain independent data silos
-
D
It creates a single authoritative reference point for critical business entities, enabling consistent analytics and operational decisions across the enterprise
✓ Correct
Explanation
MDM establishes a unified, authoritative view of critical entities (customers, products, suppliers) across the organization, enabling consistent reporting and reducing data-driven decision conflicts.