A company has a 'customer' dimension that is used in both sales and support fact tables. The dimension has a 'customer_region' attribute that is the same in both contexts. To ensure consistent reporting across the two business processes, what should the team do?

Create separate customer dimension tables for sales and support to avoid coupling.

Use the same customer dimension table in both fact tables, ensuring the attributes and keys are identical.

Use a degenerate dimension for customer in both fact tables.

Store the customer_region attribute directly in each fact table.

Which design characteristic is a key advantage of a star schema for analytical queries?

It automatically creates aggregate tables for common queries.

It reduces the number of joins required to query fact data with dimension attributes.

It minimizes storage space by normalizing all dimension attributes.

It enforces strict referential integrity through cascading foreign keys.

A data engineer is designing aggregate tables for a fact table with 1 billion rows. The most common queries are 'total sales by month by product' and 'total sales by quarter by region'. The team also needs to support ad-hoc queries that drill down to individual transactions. Which approach best balances performance and flexibility?

Create two aggregate tables (month-product and quarter-region) and keep the base fact table for ad-hoc queries.

Create only one aggregate table at the month-product grain and drop the base fact table.

Create a single aggregate table at the month-region grain and drop the base fact table.

Create only one aggregate table at the quarter-region grain and keep the base fact table.

A data engineer is optimizing a large fact table that is partitioned by date and bucketed by customer_id. Queries that filter on a specific customer and a date range are still slow. The table has 10,000 partitions and each partition is 1 GB. What is the most likely cause of the slow performance?

The fact table should be bucketed by date instead of customer_id.

Too many partitions cause small file overhead and slow metadata operations.

The query engine is not using the partition pruning because the filter is on a bucketed column.

Bucketing by customer_id is not effective because customer_id is not used in the filter.

DatabricksCertified Data Engineer Professional

Domain 10Objective 4

Design Dimensional Models for Analytical Workloads, Ensuring Efficient Querying and Aggregation. DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 2)

Part of the Data Modeling domain, which accounts for 6% of the DATA-ENGINEER-PROFESSIONAL exam. Databricks does not publish an official question count, but from its 120-minute exam (~50–80 total, ~3–5 in this domain), expect 1–1 from this objective — we provide 24 practice questions to prepare you well beyond it. (estimate)

24questions here

5free pages

7concepts

6%of the exam

Questions 6–10

6application · medium
A company has a 'customer' dimension that is used in both sales and support fact tables. The dimension has a 'customer_region' attribute that is the same in both contexts. To ensure consistent reporting across the two business processes, what should the team do?
Select an answer first
7foundation · easy
Which design characteristic is a key advantage of a star schema for analytical queries?
Select an answer first
8expert · hard
A data engineer is designing aggregate tables for a fact table with 1 billion rows. The most common queries are 'total sales by month by product' and 'total sales by quarter by region'. The team also needs to support ad-hoc queries that drill down to individual transactions. Which approach best balances performance and flexibility?
Select an answer first
9expert · hard
A data engineer is optimizing a large fact table that is partitioned by date and bucketed by customer_id. Queries that filter on a specific customer and a date range are still slow. The table has 10,000 partitions and each partition is 1 GB. What is the most likely cause of the slow performance?
Select an answer first
10application · medium
A company is designing a dimensional model for analyzing website clickstream data. Each click event includes a timestamp, user ID, page URL, and session ID. The team wants to support queries like 'number of clicks per page per day'. What should be the grain of the fact table?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DATA-ENGINEER-PROFESSIONAL

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Databricks. “DATA-ENGINEER-PROFESSIONAL” is a trademark of its owner, used for identification only.

Design Dimensional Models for Analytical Workloads, Ensuring Efficient Querying and Aggregation. DATA-ENGINEER-PROFESSIONAL Practice Questions (Page 2)

Questions 6–10

A company has a 'customer' dimension that is used in both sales and support fact tables. The dimension has a 'customer_region' attribute that is the same in both contexts. To ensure consistent reporting across the two business processes, what should the team do?

Which design characteristic is a key advantage of a star schema for analytical queries?

A data engineer is optimizing a large fact table that is partitioned by date and bucketed by customer_id. Queries that filter on a specific customer and a date range are still slow. The table has 10,000 partitions and each partition is 1 GB. What is the most likely cause of the slow performance?

A company is designing a dimensional model for analyzing website clickstream data. Each click event includes a timestamp, user ID, page URL, and session ID. The team wants to support queries like 'number of clicks per page per day'. What should be the grain of the fact table?

Keep studying — Data Modeling