PL-300 Prepare the data Practice Question
You are a Power BI developer for a financial services company. You are preparing data from multiple sources: a CSV file containing daily stock prices (ticker, date, close_price), a SQL Server database with company information (ticker, company_name, sector), and an Excel file with quarterly earnings data (ticker, quarter, earnings_per_share). The CSV file has 5 years of daily data (approx 1.3 million rows). The SQL Server table has 5000 rows. The Excel file has 20,000 rows. You need to create a data model that allows users to filter by sector, company, and date range, and to calculate moving averages of stock prices and compare earnings over time. Performance is critical. You must decide the best approach to combine and model this data. What should you do?
⚠ Common exam trap
A common mix-up: candidates choose Option D (flat table) thinking it simplifies the model, but they overlook the severe performance hit from data duplication and the inability to use star schema optimizations for time intelligence and filtering.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.
Importing all tables into the in-memory VertiPaq engine ensures optimal performance for large datasets (1.3M rows) and complex calculations like moving averages. Creating a separate date table with CALENDAR enables proper time intelligence, while the bridge table resolves the many-to-many relationship between quarterly earnings and daily dates, allowing accurate filtering by sector, company, and date range without performance degradation.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use DirectQuery for StockPrices (CSV) and import the other tables.
Why it's wrong here
DirectQuery is not supported for CSV files because CSV is a flat file with no underlying query engine for Power BI to push queries to; CSV data must be imported into the in-memory model. Even if a connector existed, DirectQuery on a large financial dataset would send a query to the source for every visual interaction, causing severe performance degradation and poor user experience. This approach also prevents the use of Power Query transformations that require data to be loaded and staged in the model.
- ✗
Import only StockPrices and Company, and use the auto date/time feature; ignore Earnings data.
Why it's wrong here
Excluding Earnings data removes critical financial metrics (e.g., EPS, revenue surprises) that analysts need to correlate with stock price movements, leaving the model unable to answer core business questions. Relying on the auto date/time feature creates hidden system-generated date tables that bloat the model and can lead to ambiguous relationships between date fields, whereas a user-defined date table with CALENDAR offers consistent, controlled time intelligence. This option also fails to establish relationships between Earnings and other tables, making it impossible to integrate earnings data even if it were added later.
- ✓
Import all tables, create a date table with CALENDAR, and establish relationships: StockPrices[Date] -> DateTable[Date], StockPrices[Ticker] -> Company[Ticker], Earnings[Ticker] -> Company[Ticker], and create a many-to-many relationship between Earnings and DateTable using a bridge table.
Why this is correct
Importing all tables into memory leverages Power BI's high-performance columnar compression and DAX evaluation, making queries fast even on large volumes. A dedicated date table created with CALENDAR (or CALENDARAUTO) and marked as the date table ensures accurate time intelligence and avoids the performance penalties of auto date/time. The stated relationships form a star schema: StockPrices joins to DateTable and Company, while Earnings joins to Company, creating clean filter paths. Since Earnings can have multiple records per date (across companies), a bridge table enables a many-to-many relationship between Earnings and DateTable, allowing both facts to be filtered correctly without data duplication or ambiguity.
- ✗
Import all tables, then in Power Query merge StockPrices with Company and Earnings into a single flat table using left outer joins.
Why it's wrong here
Merging StockPrices, Company, and Earnings into a single flat table in Power Query creates a denormalized structure with massive redundancy: company attributes and earnings fields are repeated for every stock price row, inflating model size and degrading compression efficiency. This design destroys the relational star schema, making DAX context transition and filter propagation slower and more cumbersome to reason about. It also complicates incremental refresh and model maintenance, as any change to source data requires reprocessing the entire merged table. In contrast, a normalized model with separate fact and dimension tables produces smaller, faster, and more maintainable Power BI solutions.
Visual reference
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.