Data Modeling Certification (DMC) — Questions and Answers

Question 1: In physical data modeling, when is it appropriate to choose a CHAR data type over VARCHAR?

When the column will store very long strings
When the column is used as a foreign key
When the column stores fixed-length values that are always the same length, such as a 2-character state code (Correct answer)
When the column allows NULL values

Correct answer: When the column stores fixed-length values that are always the same length, such as a 2-character state code

CHAR is appropriate for truly fixed-length data (state codes, ISO country codes) because it stores exactly N characters without overhead, while VARCHAR varies in storage.

Question 2: What does 'data type alignment' mean in physical data modeling?

Using the same data type for all columns in a table
Choosing DBMS data types that accurately represent the logical domain while optimizing storage (Correct answer)
Aligning table columns alphabetically
Matching column widths to screen display width

Correct answer: Choosing DBMS data types that accurately represent the logical domain while optimizing storage

Data type alignment means selecting the most appropriate DBMS-specific data type that matches the attribute's domain, constraints, and expected value range while minimizing storage.

Question 3: What is a business glossary and why is it important in data modeling?

A SQL keyword reference manual
A glossary of data modeling notation symbols
A list of approved database vendor products
An authoritative list of business terms with agreed definitions used consistently across the organization (Correct answer)

Correct answer: An authoritative list of business terms with agreed definitions used consistently across the organization

A business glossary provides standard definitions for business terms, ensuring that data modelers, developers, and business users all mean the same thing when they use key terms.

Question 4: What is a degenerate dimension in data warehouse modeling?

A dimension attribute that lives in the fact table rather than in a separate dimension table because it has no associated descriptive attributes (Correct answer)
A dimension that has degraded over time due to poor data quality
A dimension with very few rows and low cardinality
A deprecated dimension no longer used in queries

Correct answer: A dimension attribute that lives in the fact table rather than in a separate dimension table because it has no associated descriptive attributes

A degenerate dimension is a dimensional attribute — such as an invoice number or order number — stored directly in the fact table because it has no associated descriptive data to justify its own table.

Question 5: In physical data design, what is the purpose of a column constraint CHECK?

To create a unique index on the column
To verify the column exists in the schema
To restrict the range or set of valid values that can be inserted into a column (Correct answer)
To cascade deletes to related tables

Correct answer: To restrict the range or set of valid values that can be inserted into a column

A CHECK constraint enforces a condition on column values at the database level, rejecting any insert or update that violates the rule.

Question 6: What is the purpose of data modeling tools?

To automate the ETL process
To define and manage data relationships (Correct answer)
To design database schemas visually (Correct answer)
To enforce data governance policies

Correct answer: To define and manage data relationships

Data modeling tools are specialized software applications designed to help users create, visualize, and manage database schemas. They allow designers to graphically represent entities, attributes, and the relationships between them, facilitating the definition of the logical and physical structure of a database. These tools are crucial for ensuring a well-organized, efficient, and consistent database design.

Question 7: Which of the following is a key responsibility of a data steward in maintaining data quality within a data model?

Writing SQL queries for reporting
Designing the network infrastructure for data storage
Defining, monitoring, and enforcing data quality rules for assigned data domains (Correct answer)
Optimizing database indexes for performance

Correct answer: Defining, monitoring, and enforcing data quality rules for assigned data domains

Data stewards own the quality of specific data domains, setting quality rules, monitoring compliance, and coordinating remediation of data quality issues.

Question 8: Which process involves systematically examining source data to understand its structure, content, and quality before designing a data model or ETL process?

Data masking
Data normalization
Data profiling (Correct answer)
Data archiving

Correct answer: Data profiling

Data profiling analyzes source data to discover patterns, anomalies, and quality issues, informing both data model design and the transformations needed in the integration layer.

Question 9: What does GDPR require of data models that store personal data of EU residents?

Personal data columns must use numeric surrogate keys only
All personal data must be stored unencrypted for audit purposes
All personal data must be replicated to US servers
Models must accommodate data subject rights including access, correction, and erasure of personal data (Correct answer)

Correct answer: Models must accommodate data subject rights including access, correction, and erasure of personal data

GDPR requires that data systems support the right to access, rectify, port, and erase personal data — data models must be designed to accommodate these rights technically.

Question 10: Which technique is commonly used in data modeling to detect and merge duplicate entity records that represent the same real-world object?

Horizontal scaling
Data partitioning
Schema migration
Master data management (MDM) (Correct answer)

Correct answer: Master data management (MDM)

Master Data Management (MDM) provides processes and tools to identify, merge, and maintain a single authoritative record for duplicate entities across systems.

Question 11: What is metadata in the context of data management?

Descriptive information about data's structure, context, and lineage (Correct answer)
Data about data (Correct answer)
Detailed records of transactions
Visualization of data trends

Correct answer: Descriptive information about data's structure, context, and lineage

Metadata is essentially 'data about data,' providing descriptive information that helps understand the characteristics, context, and usage of other data. This includes details about data's structure, its context (e.g., source, creation date), and its lineage (how it was transformed). Effective metadata is crucial for data discovery, governance, and ensuring data quality.

Question 12: What is an abstract entity in a supertype/subtype model?

A supertype entity that cannot have direct instances — all instances must belong to a subtype (Correct answer)
An entity used only in conceptual models
An entity with no attributes
An entity representing an external system

Correct answer: A supertype entity that cannot have direct instances — all instances must belong to a subtype

An abstract supertype has no instances of its own; every instance must be classified as one of its subtypes, such as EMPLOYEE being abstract with subtypes FULL_TIME and PART_TIME.

Question 13: What is schema-on-read, and how does it differ from schema-on-write?

Schema-on-read enforces structure when data is written; schema-on-write enforces it at query time
Schema-on-read applies structure at query time; schema-on-write enforces structure when data is stored (Correct answer)
Both are identical but used in different DBMS platforms
Schema-on-read is only used in relational databases

Correct answer: Schema-on-read applies structure at query time; schema-on-write enforces structure when data is stored

Schema-on-write (traditional RDBMS) enforces a defined schema when data is stored, while schema-on-read (data lakes) applies interpretation at query time, allowing raw data ingestion.

Question 14: Which of the following best distinguishes a logical data model from a conceptual data model?

A logical model only documents relationships, not entities
A logical model adds attributes, primary keys, and data types without specifying a DBMS (Correct answer)
A logical model includes physical storage parameters
A logical model is less detailed than a conceptual model

Correct answer: A logical model adds attributes, primary keys, and data types without specifying a DBMS

A logical data model refines the conceptual model by adding detailed attributes, primary and foreign keys, and data types while remaining DBMS-independent.

Question 15: What is the primary goal of a conceptual data model?

To specify SQL DDL scripts for deployment
To capture high-level business concepts and their relationships independent of technology (Correct answer)
To define indexes and storage parameters
To document ETL transformation rules

Correct answer: To capture high-level business concepts and their relationships independent of technology

A conceptual data model captures major business entities and relationships at a high level, focusing on what the business needs to track rather than how data will be stored.

Question 16: Which normal form eliminates partial dependencies on a composite primary key?

First Normal Form (1NF)
Second Normal Form (2NF) (Correct answer)
Boyce-Codd Normal Form (BCNF)
Third Normal Form (3NF)

Correct answer: Second Normal Form (2NF)

2NF requires that every non-key attribute is fully functionally dependent on the entire primary key, eliminating partial dependencies.

Question 17: Which physical modeling concept involves grouping related tables into named containers within a database?

Filegroup
Tablespace
Schema (namespace) (Correct answer)
Partition

Correct answer: Schema (namespace)

A schema (namespace) groups related database objects — tables, views, procedures — into a logical container, enabling organization, access control, and naming isolation.

Question 18: What does 'data sovereignty' mean and why does it affect physical data modeling decisions?

The legal concept that data is subject to the laws of the country where it is stored, affecting where data can physically reside (Correct answer)
The rule that production data must not leave the data center
The principle that the data owner controls all access
The right of developers to define their own schemas

Correct answer: The legal concept that data is subject to the laws of the country where it is stored, affecting where data can physically reside

Data sovereignty means data is governed by the laws of the country it is stored in, requiring physical models to implement region-specific data stores for compliance with local regulations.

Question 19: Which of the following best describes an associative entity (also known as an intersection or bridge entity)?

An entity representing a lookup or code table
An entity that links two or more entities in a many-to-many relationship and may contain its own attributes (Correct answer)
An entity that has no attributes other than its primary key
An entity used only in physical data models

Correct answer: An entity that links two or more entities in a many-to-many relationship and may contain its own attributes

An associative entity resolves a many-to-many relationship by becoming its own entity with a composite key and potentially additional attributes.

Question 20: Which DMC concept refers to a consistent definition of a business entity agreed upon across the enterprise?

Operational data
Master data (Correct answer)
Derived data
Transactional data

Correct answer: Master data

Master data represents the core business entities — customers, products, employees, locations — that are shared and used consistently across multiple business processes and systems.

Question 21: In an ERD, what does a crow's foot notation symbol at the end of a relationship line represent?

Primary key
Zero or one
One and only one
Many (zero or more) (Correct answer)

Correct answer: Many (zero or more)

In crow's foot notation, the multi-pronged symbol (crow's foot) indicates the 'many' side of a one-to-many or many-to-many relationship.

Question 22: Which concept represents a property or characteristic of an entity in a data model?

Attribute (Correct answer)
Cardinality
Schema
Relationship

Correct answer: Attribute

An attribute is a property or characteristic that describes an entity, such as a customer's name or birth date.

Question 23: What is the term for the process of converting data from one format or structure to another while maintaining its integrity during data integration?

Data archiving
Data transformation (Correct answer)
Data partitioning
Data replication

Correct answer: Data transformation

Data transformation converts data from source formats to target formats during ETL or integration, and maintaining integrity requires applying data quality rules throughout this process.

Question 24: What anomaly occurs when inserting new data requires entering dummy or NULL values for unrelated attributes?

Deletion anomaly
Insertion anomaly (Correct answer)
Update anomaly
Referential anomaly

Correct answer: Insertion anomaly

An insertion anomaly occurs when you can't add new data without also supplying values for unrelated attributes that have no current meaning.

Question 25: In the context of DMC, what does 'domain' mean when applied to an attribute?

The set of all valid values an attribute can hold (Correct answer)
The geographic region of data storage
The network domain hosting the database
The business unit that owns the data

Correct answer: The set of all valid values an attribute can hold

A domain is the complete set of allowable values for an attribute, defined by data type, length, format, and business constraints.

Question 26: When a data quality issue is discovered in production data, which document should be updated to capture the root cause, business impact, and remediation steps?

Data quality issue log (Correct answer)
Network topology diagram
Entity-relationship diagram
Index usage report

Correct answer: Data quality issue log

A data quality issue log tracks discovered issues, their root causes, business impact assessments, and remediation actions, forming an audit trail for governance purposes.

Question 27: What is a fact table in dimensional modeling?

A table containing master data like customers and products
A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables (Correct answer)
A table containing only lookup codes and their descriptions
A table that stores audit logs of all data changes

Correct answer: A central table in a star or snowflake schema that stores measurable business events with foreign keys to dimension tables

A fact table stores measurable, quantitative data about business events — such as sales amounts, quantities, or counts — along with foreign keys linking to the relevant dimensions.

Question 28: What is a multi-valued attribute?

A foreign key that references two parent tables
An attribute with a composite data type
An attribute that maps to multiple physical columns
An attribute that can hold multiple independent values for a single entity instance (Correct answer)

Correct answer: An attribute that can hold multiple independent values for a single entity instance

A multi-valued attribute is one that can hold more than one value simultaneously for the same entity, such as a person having multiple phone numbers.

Question 29: What is the purpose of a conformed dimension in data warehouse design?

A dimension table that is shared and used identically across multiple fact tables and subject areas, enabling consistent cross-subject analysis (Correct answer)
A dimension containing only validated, cleansed values
A dimension that auto-updates when source system data changes
A dimension that conforms to ISO data standards

Correct answer: A dimension table that is shared and used identically across multiple fact tables and subject areas, enabling consistent cross-subject analysis

A conformed dimension is defined once and used consistently across multiple fact tables and data marts, enabling drill-across queries that compare results across subject areas.

Question 30: Which of the following best describes data accuracy as a data quality dimension?

Data conforms to the correct format
Data is available within the agreed service window
Data has no duplicate records
Data correctly represents the real-world entity or event it models (Correct answer)

Correct answer: Data correctly represents the real-world entity or event it models

Accuracy measures whether data values truthfully reflect the real-world objects or events they represent, which is distinct from format conformity or completeness.

Question 31: What does cardinality describe in a data model?

The indexing method used in storage
The number of instances of one entity related to instances of another (Correct answer)
The primary key selection strategy
The data type of an attribute

Correct answer: The number of instances of one entity related to instances of another

Cardinality defines how many instances of one entity can be associated with instances of another entity in a relationship.

Question 32: In a Data Vault model, what does a Hub contain?

The unique business keys of a core business entity along with metadata about its source and load date (Correct answer)
Descriptive attributes that change over time
Only the relationships between two entities
All historical records for a business entity

Correct answer: The unique business keys of a core business entity along with metadata about its source and load date

A Hub in Data Vault modeling stores the unique business keys for a core business concept, along with a surrogate key, load date, and record source — no descriptive attributes.

Question 33: Which normal form specifically addresses multi-valued dependencies?

3NF
2NF
BCNF
4NF (Correct answer)

Correct answer: 4NF

Fourth Normal Form (4NF) eliminates non-trivial multi-valued dependencies, ensuring a table doesn't independently store two or more multi-valued facts about an entity.

Question 34: Which of the following are common elements of an Entity-Relationship Diagram (ERD)?

Entities (Correct answer)
Attributes (Correct answer)
Data Pipelines
Keys (Primary and Foreign) (Correct answer)

Correct answer: Entities

An Entity-Relationship Diagram (ERD) is a visual tool used to model the structure of a database. Its common elements include entities, which represent real-world objects; attributes, which describe the properties of an entity; and relationships, which show how entities are connected. Keys, such as primary and foreign keys, are essential attributes that define these relationships and ensure data integrity.

Question 35: What is the key difference between a strong entity and a weak entity?

A weak entity cannot be uniquely identified without referencing its parent entity (Correct answer)
Strong entities have more attributes than weak entities
Weak entities cannot have relationships
Strong entities use surrogate keys; weak entities use natural keys

Correct answer: A weak entity cannot be uniquely identified without referencing its parent entity

A weak entity depends on a parent (owner) entity for its identification and cannot be uniquely identified by its own attributes alone.

Question 36: What does 'data lineage' document?

The genealogy of database schema versions
The list of users who accessed specific data
The age of data records from creation to deletion
The origin of data and how it flows and transforms through systems over time (Correct answer)

Correct answer: The origin of data and how it flows and transforms through systems over time

Data lineage tracks the complete journey of data from its source through every transformation and system it passes through to its final destination.

Question 37: Which of the following are key objectives of normalization in data modeling?

Enhancing database security
Improving data integrity (Correct answer)
Reducing data redundancy (Correct answer)
Increasing query execution speed

Correct answer: Improving data integrity

Normalization in data modeling aims to organize database tables to minimize data redundancy and improve data integrity. By breaking down large tables into smaller, related ones, it ensures that data is stored only once, reducing storage space and preventing inconsistencies. This structured approach helps maintain the accuracy and reliability of the data.

Question 38: What is a data standard in the context of enterprise data governance?

A minimum row count requirement for tables
A benchmark for query performance
A database vendor certification
An agreed-upon rule or format that data must conform to — such as date formats, code values, or naming patterns — enforced across systems (Correct answer)

Correct answer: An agreed-upon rule or format that data must conform to — such as date formats, code values, or naming patterns — enforced across systems

Data standards define mandatory formats, code sets, naming rules, and quality thresholds that all data systems must conform to, enabling integration and consistent interpretation.

Question 39: In data modeling, which approach involves defining rules that data must satisfy and automatically rejecting records that violate those rules at the point of entry?

Proactive data quality enforcement (Correct answer)
Reactive data cleansing
Retrospective data profiling
Batch data correction

Correct answer: Proactive data quality enforcement

Proactive data quality enforcement uses constraints, validation rules, and business logic at the point of data entry or ingestion to prevent low-quality data from entering the system.

Question 40: Which data quality concept refers to each entity instance being represented only once in a dataset, with no duplicate records?

Accuracy
Uniqueness (Correct answer)
Completeness
Consistency

Correct answer: Uniqueness

Uniqueness ensures that each real-world entity appears exactly once in the dataset, which is typically enforced through primary keys and deduplication processes.

Question 41: What is the purpose of a data catalog in metadata management?

To automate the process of data cleaning
To analyze business trends
To provide a central repository for metadata (Correct answer)
To store and query large volumes of data

Correct answer: To provide a central repository for metadata

A data catalog serves as a central repository for an organization's metadata, providing a comprehensive inventory of all available data assets. Its purpose is to make data discoverable, understandable, and trustworthy by collecting, organizing, and enriching metadata from various sources. This enables users to easily find, comprehend, and utilize the data they need, improving data governance and collaboration.

Question 42: In a relational data model, a CHECK constraint is used to enforce which of the following?

Foreign key cascades
Primary key uniqueness
Domain-level validity rules on column values (Correct answer)
Referential integrity between tables

Correct answer: Domain-level validity rules on column values

A CHECK constraint enforces domain-level validity by restricting what values can be inserted into a column based on a logical expression.

Question 43: What is the difference between data classification and data categorization in governance?

Classification is physical; categorization is logical
They are synonymous terms used interchangeably
Classification assigns sensitivity/security labels (PII, confidential); categorization groups data by business subject area (Finance, HR) (Correct answer)
Classification applies only to structured data; categorization to unstructured

Correct answer: Classification assigns sensitivity/security labels (PII, confidential); categorization groups data by business subject area (Finance, HR)

Data classification assigns security/sensitivity labels (public, internal, confidential, restricted) while data categorization organizes data by business domain or subject area.

Question 44: What is the purpose of a data quality dimension in governance?

To measure storage consumption by data domain
To segment data by geographic region
To define measurable characteristics — such as completeness, accuracy, and timeliness — used to assess data quality (Correct answer)
To classify data by sensitivity level

Correct answer: To define measurable characteristics — such as completeness, accuracy, and timeliness — used to assess data quality

Data quality dimensions — including completeness, accuracy, consistency, timeliness, validity, and uniqueness — provide measurable criteria for evaluating how fit data is for its intended use.

Question 45: Which physical design technique is used to handle very large binary objects like documents or images stored in a database?

Vertical partitioning
Row-level compression
BLOB (Binary Large Object) or CLOB (Character Large Object) data types with external storage pointers (Correct answer)
Column encryption

Correct answer: BLOB (Binary Large Object) or CLOB (Character Large Object) data types with external storage pointers

BLOB and CLOB data types store large binary or text objects, and databases often support external file storage with pointers to keep table rows compact.

Question 46: What is table partitioning in physical data modeling?

Splitting a table's columns into multiple tables
Encrypting specific columns for security
Creating read-only replicas of a table
Dividing a large table's rows into segments based on a partition key to improve performance and manageability (Correct answer)

Correct answer: Dividing a large table's rows into segments based on a partition key to improve performance and manageability

Table partitioning divides a large table into smaller, manageable segments (partitions) based on a key such as date range or region, improving query performance and data maintenance.

Question 47: What is a data catalog and how does it relate to data governance?

A list of all ETL jobs scheduled in the pipeline
An organized inventory of data assets with metadata, definitions, and ownership that enables data discovery and governance (Correct answer)
A list of approved SQL query templates
A read-only backup of the production database

Correct answer: An organized inventory of data assets with metadata, definitions, and ownership that enables data discovery and governance

A data catalog provides a searchable inventory of data assets enriched with business definitions, ownership, lineage, quality metrics, and usage statistics to enable governance and self-service analytics.

Question 48: What is data masking and when is it applied in a governed data environment?

Replacing sensitive data values with realistic but fictitious substitutes to protect privacy in non-production environments (Correct answer)
Encrypting the entire database at rest
Removing deleted rows from audit logs
Hiding table names from non-admin users

Correct answer: Replacing sensitive data values with realistic but fictitious substitutes to protect privacy in non-production environments

Data masking substitutes sensitive production data (like SSNs or credit card numbers) with realistic fictitious values, allowing development and testing without exposing real personal data.

Question 49: What is a data steward's primary responsibility?

Writing SQL queries for data extraction
Accountable for the quality, definition, and appropriate use of specific data elements within a business domain (Correct answer)
Administering database server hardware
Developing ETL pipelines

Correct answer: Accountable for the quality, definition, and appropriate use of specific data elements within a business domain

A data steward is the business owner accountable for defining, maintaining, and ensuring the quality and correct usage of specific data assets within their domain.

Question 50: What is a materialized view and when is it typically used in physical design?

A temporary table used during ETL processing
A precomputed, physically stored query result refreshed on a schedule to speed up complex queries (Correct answer)
A view that spans multiple databases
A view that enforces row-level security

Correct answer: A precomputed, physically stored query result refreshed on a schedule to speed up complex queries

A materialized view stores the results of a query physically and refreshes them periodically, significantly speeding up expensive aggregations and joins in read-heavy environments.

Question 51: What makes Boyce-Codd Normal Form (BCNF) stricter than 3NF?

BCNF requires every determinant to be a candidate key, including in cases 3NF misses (Correct answer)
BCNF eliminates all repeating groups
BCNF also eliminates multi-valued dependencies
BCNF requires surrogate keys instead of natural keys

Correct answer: BCNF requires every determinant to be a candidate key, including in cases 3NF misses

BCNF strengthens 3NF by requiring that every determinant in the relation is a candidate key, handling edge cases where 3NF still allows anomalies.

Question 52: Which of the following is a characteristic of a natural key?

It is always numeric
It never appears in reports
It derives its value from business attributes that already exist (Correct answer)
It is generated by the database system

Correct answer: It derives its value from business attributes that already exist

A natural key is derived from existing business data — such as a Social Security number or product barcode — that already has meaning in the real world.

Question 53: A data steward discovers that customer records have phone numbers stored as both '555-1234' and '5551234'. Which data quality dimension is primarily affected?

Consistency
Accuracy
Uniqueness
Conformity (Correct answer)

Correct answer: Conformity

Conformity measures whether data follows specified formats and standards; inconsistent phone number formats indicate a conformity failure.

Question 54: What is a surrogate key?

A key borrowed from another table
A composite key made of multiple columns
A system-generated identifier with no business meaning (Correct answer)
A natural identifier from the real world

Correct answer: A system-generated identifier with no business meaning

A surrogate key is a system-generated, meaningless identifier — typically an integer sequence — used solely to uniquely identify a row.

Question 55: What does DDL stand for and what does it contain?

Database Design Level — the physical design tier
Data Definition Language — SQL statements that create, alter, and drop database objects (Correct answer)
Data Deployment Language — scripts used to deploy ETL pipelines
Data Dictionary Language — the language of the data catalog

Correct answer: Data Definition Language — SQL statements that create, alter, and drop database objects

DDL (Data Definition Language) is the subset of SQL containing CREATE, ALTER, DROP, TRUNCATE, and RENAME statements that define and modify database structure.

Question 56: What is data profiling in the context of data governance and modeling?

Documenting which teams access which tables
Analyzing existing data to understand its structure, content, quality, and patterns (Correct answer)
Benchmarking query performance across tables
Creating user profiles for database access control

Correct answer: Analyzing existing data to understand its structure, content, quality, and patterns

Data profiling examines source data to discover its structure, value ranges, null rates, uniqueness, and patterns — essential input for building accurate data models and governance policies.

Question 57: When profiling a dataset to assess data quality, which activity involves analyzing the distribution of values in a column to detect anomalies?

Lineage tracing
Index optimization
Schema mapping
Column analysis (Correct answer)

Correct answer: Column analysis

Column analysis during data profiling examines the distribution, min/max, frequency, and null rates of values to surface quality anomalies.

Question 58: What is a functional dependency in the context of normalization?

A dependency between two database servers
A referential integrity constraint between tables
A trigger that enforces a business rule
A situation where the value of one attribute determines the value of another (Correct answer)

Correct answer: A situation where the value of one attribute determines the value of another

A functional dependency (A → B) means that knowing the value of attribute A uniquely determines the value of attribute B.

Question 59: Which data modeling artifact is most useful for documenting data quality rules alongside the attributes they govern?

Data dictionary (Correct answer)
Entity-relationship diagram
Star schema diagram
Index definition script

Correct answer: Data dictionary

A data dictionary captures attribute definitions, data types, allowable values, and associated data quality rules, making it the authoritative reference for quality governance.

Question 60: Which of the following tools are commonly used to integrate data modeling into data pipelines?

ER/Studio
SQL Server Integration Services (SSIS) (Correct answer)
Apache NiFi (Correct answer)
Talend (Correct answer)

Correct answer: SQL Server Integration Services (SSIS)

Apache NiFi, Talend, and SQL Server Integration Services (SSIS) are all powerful tools commonly used in data pipelines for Extract, Transform, Load (ETL) processes. They facilitate the movement and transformation of data between various sources and destinations, including databases designed with data modeling. These tools are essential for integrating and preparing data for various analytical and operational purposes.

Question 61: What is a composite primary key, and what should you consider when using one?

A primary key generated by combining two hash values
A primary key that uses both a surrogate and natural key
A primary key made of two or more columns that together uniquely identify a row (Correct answer)
A primary key shared across multiple tables

Correct answer: A primary key made of two or more columns that together uniquely identify a row

A composite primary key uses multiple columns together to uniquely identify rows, which is common in associative entities but adds complexity to foreign key relationships.

Question 62: Which concept in data modeling defines the set of allowable values, formats, and constraints for a particular attribute, directly supporting data validity?

Identifier
Domain (Correct answer)
Cardinality
Relationship

Correct answer: Domain

A domain specifies the full range of valid values and formats for an attribute, and enforcing domains is the foundational mechanism for ensuring data validity in a model.

Question 63: What is the Star Schema pattern used in data warehouse modeling?

A schema using only one large table with all data
A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance (Correct answer)
A fully normalized OLTP schema shaped like a star in the ERD
A schema where all tables connect in a circular chain

Correct answer: A central fact table surrounded by denormalized dimension tables, optimized for analytical query performance

The Star Schema places a central fact table containing measures and foreign keys surrounded by denormalized dimension tables, enabling fast analytical queries with simple joins.

Question 64: In data governance, what is the role of a data governance council?

To conduct database performance tuning
To serve as a cross-functional decision-making body that sets data policies, resolves conflicts, and prioritizes data initiatives (Correct answer)
To operate the backup and recovery systems
To write SQL for data transformation projects

Correct answer: To serve as a cross-functional decision-making body that sets data policies, resolves conflicts, and prioritizes data initiatives

A data governance council brings together senior stakeholders from business and IT to establish data policies, resolve disputes about data ownership, and prioritize governance initiatives.

Question 65: What physical design technique is most appropriate for implementing a slowly changing dimension (SCD) Type 2 in a data warehouse?

Use a separate audit table for all changes
Delete old rows and insert new ones
Overwrite the existing row with new values
Add effective date and expiration date columns along with a current flag to track historical versions (Correct answer)

Correct answer: Add effective date and expiration date columns along with a current flag to track historical versions

SCD Type 2 preserves history by inserting a new row for each change, with effective_date, expiration_date, and is_current columns to track each version.

Question 66: What is a subtype in data modeling?

A lookup table containing code values
A data type smaller than INTEGER
A specialized category of a supertype entity that inherits its attributes (Correct answer)
A secondary index on a table

Correct answer: A specialized category of a supertype entity that inherits its attributes

A subtype is a specialized version of a supertype entity that inherits all supertype attributes and relationships while adding its own distinct ones.

Question 67: Which of the following is a real-world use case for data modeling?

Structuring a data warehouse for financial reporting (Correct answer)
Creating a machine learning pipeline to clean and preprocess data
Developing a backup and disaster recovery system
Designing a database for a hospital to manage patient records (Correct answer)

Correct answer: Structuring a data warehouse for financial reporting

Data modeling is fundamentally about designing the structure of data storage to meet specific business requirements. Designing a database for a hospital to manage patient records involves defining entities like patients, doctors, and appointments, along with their relationships. Similarly, structuring a data warehouse for financial reporting requires modeling facts (e.g., sales figures) and dimensions (e.g., time, product) to support analytical queries. Both are direct applications of data modeling principles.

Question 68: What type of database schema is typically used for reporting and business intelligence systems?

Normalized schema with multiple joins
Flat file structure
Key-value storage
Star or snowflake schema (Correct answer)

Correct answer: Star or snowflake schema

Star and snowflake schemas are specifically designed for data warehousing and business intelligence (BI) systems, which prioritize fast query performance for reporting and analytical purposes. These dimensional models denormalize data to reduce the number of joins required for queries, making it much quicker to retrieve aggregated data compared to highly normalized transactional schemas. They are optimized for read-heavy workloads typical of BI.

Question 69: A data model uses surrogate keys as primary keys. Which data integrity benefit does this provide over using natural keys?

Surrogate keys automatically enforce domain constraints
Surrogate keys reduce storage requirements significantly
Surrogate keys are immune to changes in business attributes, preserving referential integrity (Correct answer)
Surrogate keys store more business meaning

Correct answer: Surrogate keys are immune to changes in business attributes, preserving referential integrity

Surrogate keys are system-generated and never change, so when natural key attributes (like SSN or email) are updated, referential integrity is preserved across all related tables.

Question 70: What is an update anomaly in an unnormalized table?

When a primary key value must be updated in every related table
When an index is outdated after a bulk insert
When a stored procedure fails during a transaction
When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency (Correct answer)

Correct answer: When the same fact is stored in multiple rows and updating one row without updating all causes inconsistency

An update anomaly occurs when a single logical fact is stored in multiple rows, so updating it in one place without updating all copies creates inconsistent data.

Question 71: What is an anchor modeling pattern?

A pattern where every table is anchored to a central hub table
A highly normalized temporal modeling technique where each attribute is stored in its own separate table for maximum flexibility and historization (Correct answer)
A technique for anchoring foreign keys to prevent cascading deletes
A pattern that anchors surrogate keys to natural keys

Correct answer: A highly normalized temporal modeling technique where each attribute is stored in its own separate table for maximum flexibility and historization

Anchor modeling stores each attribute in its own table with full historization, enabling maximum schema evolution flexibility at the cost of many joins for queries.

Question 72: What principle ensures that every fact in a data model is stored in exactly one place?

Single Source of Truth (SSOT) (Correct answer)
Entity completeness
Domain constraint
Referential integrity

Correct answer: Single Source of Truth (SSOT)

The Single Source of Truth principle ensures each fact is stored once and only once, eliminating redundancy and reducing inconsistency risk.

Question 73: Which modeling artifact is used to document the meaning and origin of a single data element across an enterprise?

Data element definition record (Correct answer)
Entity-Relationship Diagram
Conceptual schema
Physical table script

Correct answer: Data element definition record

A data element definition record captures the business name, definition, data type, source, and steward of a single atomic piece of data.

Question 74: How does data modeling improve machine learning workflows?

By optimizing machine learning algorithms
By enhancing models deployment pipelines
By structuring data for efficient feature extraction (Correct answer)
By training models directly on ERD diagrams

Correct answer: By structuring data for efficient feature extraction

Data modeling significantly improves machine learning workflows by providing a structured and organized foundation for data. A well-designed data model ensures that data is clean, consistent, and logically related, which is crucial for efficient feature extraction and engineering. This structured approach simplifies the process of preparing data for model training, leading to more accurate and robust machine learning models.

Question 75: Which of the following best describes the star schema used in dimensional modeling?

It contains normalized tables with minimal redundancy.
It uses a central fact table surrounded by dimension tables. (Correct answer)
It has no foreign key relationships between tables.
It is used exclusively for transactional systems.

Correct answer: It uses a central fact table surrounded by dimension tables.

The star schema is a foundational structure in dimensional modeling, commonly used in data warehouses for analytical purposes. It consists of a central 'fact' table, which contains quantitative measures, surrounded by multiple 'dimension' tables that provide descriptive context for those measures. This simple, denormalized structure optimizes query performance for reporting and business intelligence.

Question 76: What does referential integrity enforce in a relational data model?

That every table has at least one index
That queries return consistent results under concurrent access
That all attributes have non-NULL values
That a foreign key value must match an existing primary key value in the referenced table (Correct answer)

Correct answer: That a foreign key value must match an existing primary key value in the referenced table

Referential integrity ensures that foreign key values always correspond to an existing primary key in the referenced table, preventing orphaned records.

Question 77: What is the lossless decomposition property in normalization?

The rule that normalization never reduces the number of tables
The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly (Correct answer)
The ability to compress tables without losing row data
The property that all foreign keys retain their referenced values after splitting

Correct answer: The guarantee that decomposing a table into smaller tables and then rejoining them reproduces the original table exactly

Lossless decomposition guarantees that when you split a table into two or more tables, joining them back together returns exactly the original data with no extra or missing rows.

Question 78: What does the term 'optionality' refer to in a data model?

Whether an attribute can store NULL values
The choice between surrogate and natural keys
Whether a relationship participation is mandatory or optional (Correct answer)
Optional indexes on non-key columns

Correct answer: Whether a relationship participation is mandatory or optional

Optionality describes whether an entity's participation in a relationship is mandatory (must exist) or optional (may or may not exist).

Question 79: What is the purpose of a data dictionary in data modeling?

To document the definitions, formats, and usage of data elements (Correct answer)
To map network traffic routes
To store compressed database backups
To translate SQL queries automatically

Correct answer: To document the definitions, formats, and usage of data elements

A data dictionary provides authoritative documentation of data element definitions, formats, relationships, and business rules.

Question 80: What is a slowly changing dimension (SCD) Type 1?

A dimension updated on a slow nightly schedule
A dimension that stores one record per change with full history
A method of handling attribute changes by simply overwriting the old value, preserving no history (Correct answer)
A dimension that is never updated once created

Correct answer: A method of handling attribute changes by simply overwriting the old value, preserving no history

SCD Type 1 handles changes by overwriting the old attribute value with the new one, which is simple but destroys historical data — appropriate when history is not needed.

Question 81: In logical data modeling, what is the purpose of a business rule?

To define the physical storage engine
To capture constraints and policies that govern data and must be enforced by the model (Correct answer)
To document server backup procedures
To specify the ETL schedule

Correct answer: To capture constraints and policies that govern data and must be enforced by the model

Business rules define constraints, policies, and conditions that data must satisfy, and they guide the structure and constraints built into the logical data model.

Question 82: What is First Normal Form (1NF) and what does it eliminate?

Eliminates partial dependencies on composite keys
Eliminates join dependencies across tables
Eliminates transitive dependencies by splitting tables
Eliminates repeating groups and multi-valued attributes, ensuring each column holds atomic (single) values (Correct answer)

Correct answer: Eliminates repeating groups and multi-valued attributes, ensuring each column holds atomic (single) values

1NF requires that every column holds a single, indivisible atomic value, eliminating repeating groups, arrays, and nested collections from table columns.

Question 83: What is a data owner in a governance framework?

The analyst who most frequently queries the data
The developer who wrote the ETL script
A senior business executive accountable for the strategic use and protection of a data domain (Correct answer)
The DBA who created the table

Correct answer: A senior business executive accountable for the strategic use and protection of a data domain

A data owner is a senior business stakeholder with ultimate accountability for the strategic direction, access policies, and quality of a data domain.

Question 84: What is vertical partitioning (also called column splitting) in physical data modeling?

Dividing a table by business unit
Partitioning rows by date range across multiple tables
Creating column-level access controls
Splitting a wide table into two narrower tables sharing the same primary key, separating frequently and rarely accessed columns (Correct answer)

Correct answer: Splitting a wide table into two narrower tables sharing the same primary key, separating frequently and rarely accessed columns

Vertical partitioning splits a table's columns into two or more tables with the same primary key, improving performance by keeping frequently accessed columns together.

Question 85: What is the purpose of a data naming convention standard?

To restrict who can create new tables
To compress data by using shorter names
To speed up query execution
To ensure consistent, meaningful, and unambiguous naming of data elements across the organization (Correct answer)

Correct answer: To ensure consistent, meaningful, and unambiguous naming of data elements across the organization

Naming convention standards ensure that all data objects — tables, columns, entities — follow consistent patterns, reducing ambiguity and making models easier to understand and maintain.

Question 86: What is a recursive (or self-referencing) relationship in data modeling?

A relationship that loops through three tables
A circular foreign key between two tables
A stored procedure that calls itself
A relationship where an entity is associated with instances of itself (Correct answer)

Correct answer: A relationship where an entity is associated with instances of itself

A recursive relationship occurs when instances of a single entity type are related to other instances of the same entity type, such as an employee managing other employees.

Question 87: In logical data modeling, what is normalization primarily used to achieve?

Add indexes to frequently queried columns
Improve query execution speed
Eliminate data redundancy and update anomalies (Correct answer)
Partition large tables across multiple servers

Correct answer: Eliminate data redundancy and update anomalies

Normalization is the process of organizing data to eliminate redundancy and dependency anomalies, ensuring each fact is stored in one place.

Question 88: Which of the following best defines a data model?

An abstract representation of data structures and their relationships (Correct answer)
A physical server configuration plan
A software deployment pipeline
A diagram showing network topology

Correct answer: An abstract representation of data structures and their relationships

A data model is an abstract representation that defines data structures, relationships, and constraints within a system.

Question 89: Which data quality dimension measures the degree to which data values fall within acceptable ranges and adhere to defined business rules?

Validity (Correct answer)
Consistency
Completeness
Timeliness

Correct answer: Validity

Validity measures whether data conforms to defined formats, ranges, and business rules, ensuring values are acceptable within the data model.

Question 90: What is the purpose of a data model review process in a governed organization?

To review ETL job scheduling
To ensure proposed data model changes conform to standards, don't break integrations, and are documented before implementation (Correct answer)
To audit SQL query execution plans
To approve changes to server hardware configurations

Correct answer: To ensure proposed data model changes conform to standards, don't break integrations, and are documented before implementation

A formal data model review process validates that changes meet naming standards, maintain data quality constraints, don't break downstream systems, and are properly documented in the data dictionary.

Question 91: In data modeling, what is an entity?

A foreign key constraint
A database index
A person, place, object, event, or concept about which data is stored (Correct answer)
A stored procedure

Correct answer: A person, place, object, event, or concept about which data is stored

An entity is a real-world object or concept — such as a customer, product, or order — about which data is collected.

Question 92: What is the purpose of a data retention policy in a data model?

To prevent database indexes from growing too large
To control how many backups are stored
To set limits on query execution time
To define how long data must be kept and when it should be archived or deleted based on legal and business requirements (Correct answer)

Correct answer: To define how long data must be kept and when it should be archived or deleted based on legal and business requirements

A data retention policy specifies the required storage duration for data categories, driving physical design decisions about archiving, partitioning, and purging strategies.

Question 93: What is data governance in the context of data modeling?

A backup and recovery strategy
The framework of policies, processes, roles, and standards that ensure data is managed consistently and responsibly (Correct answer)
A method for encrypting sensitive columns
A software tool for automating database deployments

Correct answer: The framework of policies, processes, roles, and standards that ensure data is managed consistently and responsibly

Data governance establishes the policies, processes, accountability structures, and standards that define how data is created, stored, maintained, used, and retired across the organization.

Question 94: Which of the following features are provided by SQL Server Management Studio (SSMS)?

Creating and managing database schemas (Correct answer)
Data visualization and dashboards
Writing and executing SQL queries (Correct answer)
Debugging database applications (Correct answer)

Correct answer: Creating and managing database schemas

SQL Server Management Studio (SSMS) is a comprehensive integrated environment for managing any SQL infrastructure, including SQL Server and Azure SQL Database. It provides a robust set of tools for writing and executing SQL queries, creating and managing database objects like tables and views (schemas), and debugging database applications. SSMS is essential for database administrators and developers working with Microsoft SQL Server.

Question 95: In Data Vault modeling, what is the purpose of a Link?

To store descriptive attributes about an entity
To connect the data vault to the presentation layer
To capture the relationships and associations between two or more Hubs (Correct answer)
To record the historical values of Hub business keys

Correct answer: To capture the relationships and associations between two or more Hubs

A Link in Data Vault modeling captures the many-to-many or associative relationships between Hubs, storing only the foreign keys to the related Hubs plus metadata.

Question 96: What is a join dependency, and which normal form addresses it?

A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF) (Correct answer)
A dependency that requires a JOIN operation to retrieve a value — addressed by BCNF
A constraint that two foreign keys must reference the same parent — addressed by 3NF
A circular reference between two tables — addressed by 4NF

Correct answer: A dependency where a table can only be reconstructed losslessly by joining three or more projections — addressed by 5NF (PJNF)

A join dependency exists when a table can only be decomposed into more than two tables and still be reconstructed losslessly, which Fifth Normal Form (5NF/PJNF) eliminates.

Question 97: Which ERD notation style uses rectangles for entities, diamonds for relationships, and ovals for attributes?

Crow's Foot (IE) notation
Chen notation (Correct answer)
Barker notation
IDEF1X notation

Correct answer: Chen notation

Chen notation, developed by Peter Chen in 1976, uses rectangles for entities, diamonds for relationships, and ovals for attributes in its ERD representation.

Question 98: Which of the following are benefits of effective metadata management?

Better compliance with regulations (Correct answer)
Improved data discovery and accessibility (Correct answer)
Enhanced data quality and governance (Correct answer)
Faster query execution in relational databases

Correct answer: Better compliance with regulations

Effective metadata management offers several significant benefits, including improved data discovery and accessibility by making it easier to find and understand relevant data assets. It also enhances data quality and governance by providing context, lineage, and definitions, which are crucial for maintaining data accuracy and consistency. Furthermore, robust metadata management aids in better compliance with regulations by documenting data origins, transformations, and usage.

Question 99: What is the role of a tablespace in physical database design?

A logical grouping of related tables by business domain
An in-memory cache for frequently accessed tables
A temporary workspace used for sorting operations
A storage container that maps database objects to physical disk files or storage devices (Correct answer)

Correct answer: A storage container that maps database objects to physical disk files or storage devices

A tablespace is a physical storage unit that maps database objects to one or more underlying data files on disk, enabling storage management and I/O optimization.

Question 100: What are the three levels of data abstraction in the ANSI/SPARC architecture?

Raw, Processed, Stored
External, Conceptual, Internal (Correct answer)
Operational, Analytical, Strategic
Physical, Logical, Virtual

Correct answer: External, Conceptual, Internal

The ANSI/SPARC architecture defines three levels: External (user views), Conceptual (logical whole), and Internal (physical storage).

Data Modeling Certification (DMC)

The DMC is an industry-recognized certification for data modelers offered by Technics Publications, testing proficiency across 10 areas of data modeling expertise including conceptual/logical/physical design, normalization, notations, naming standards, and best practices. The exam draws from over 350 questions with randomized selection to ensure each test is unique.

100

Questions

90 min

Time Limit

85%

To Pass

Checking for saved progress...

Exam Rules

You can skip questions and return to them later
Flag questions for review before submitting
No feedback shown until you submit the entire exam
Unanswered questions count as wrong — answer everything
10 pretest questions are mixed in and don't affect your score
Timer auto-submits when time runs out
Your progress is auto-saved every 30 seconds