Step2Study

Microsoft Certified: Fabric Analytics Engineer Associate practice questions

216 free questions with answers and explanations.

Practice test
  1. 51.A data engineer is optimizing a Spark SQL query that involves joining two large tables, `orders` and `customers`, both stored as Delta tables in Microsoft Fabric. The `customers` table is relatively small (under 100MB) compared to the `orders` table (several TBs). To improve join performance, the engineer wants to ensure the smaller `customers` table is broadcast to all worker nodes. Which Spark SQL hint should be used?Explore and analyze data (15-20%)
  2. 52.A data engineering team is working with a large dataset in a Spark Delta Lake table. They need to calculate the average `transaction_amount` for each `product_category` and then filter out categories where the average amount is less than $100. Which Spark SQL clause should be used to filter the grouped results?Explore and analyze data (15-20%)
  3. 53.A data engineering team is designing a Lakehouse solution in Microsoft Fabric for a global e-commerce platform. They need to ingest product catalog data from various sources, including legacy databases and external vendor APIs. The ingested data needs to be validated for schema compliance and data quality rules (e.g., product IDs must be unique, prices must be positive) before being made available for downstream analytics. Where should these validation steps primarily occur within the Medallion Architecture layers?Plan and implement data analytics solutions (10-15%)
  4. 54.A data engineer is working with a PySpark DataFrame representing sensor readings. The DataFrame `sensor_df` has columns `device_id`, `timestamp`, and `temperature`. They need to calculate a 7-day rolling average of `temperature` for each `device_id`. Which PySpark window function concept is most appropriate for this task?Explore and analyze data (15-20%)
  5. 55.A data engineer is working with a Microsoft Fabric Lakehouse. They have ingested raw CSV files into the 'Files' section of the Lakehouse. Now, they need to create a managed Delta table from these CSV files in the 'Tables' section. They want to incrementally load new CSV files that arrive daily into this Delta table, ensuring schema evolution is handled gracefully. Which approach is most suitable for this task?Plan and implement data analytics solutions (10-15%)
  6. 56.A data analyst is building a Power BI report connected to a Lakehouse SQL endpoint in Microsoft Fabric. They need to display the distribution of customer ages across different product categories. Which Power BI visual is most appropriate for visualizing this data?Explore and analyze data (15-20%)
  7. 57.A data analyst is preparing a report on customer demographics. They have a Spark DataFrame named `customer_df` with columns `CustomerID`, `Age`, and `City`. They need to group customers by `City` and then calculate the average `Age` for each city. Which PySpark operation should be used after `groupBy('City')` to compute the average?Explore and analyze data (15-20%)
  8. 58.A data analyst is designing a Power BI report using data from a Lakehouse SQL endpoint in Microsoft Fabric. They want to create a visual that shows the correlation between `marketing_spend` and `sales_revenue` for different product categories. Which Power BI visual type is most effective for displaying the relationship between two numerical variables and identifying potential outliers?Explore and analyze data (15-20%)
  9. 59.A data analyst is querying a large Delta table named `product_sales` in Microsoft Fabric, which contains columns `product_id` (int), `sale_date` (date), and `revenue` (decimal). They need to calculate the running total of revenue for each product, ordered by `sale_date`. Which Spark SQL window function should be used to achieve this?Explore and analyze data (15-20%)
  10. 60.A data engineer is working with a PySpark DataFrame `sensor_data` that contains `device_id`, `reading_time`, and `temperature`. They need to calculate the difference in temperature between the current reading and the previous reading for each device. If there is no previous reading for a device (i.e., it's the first reading), the difference should be NULL. Which PySpark window function combination should be used?Explore and analyze data (15-20%)
  11. 61.A data analyst is building a Power BI report connected to a Lakehouse SQL endpoint in Microsoft Fabric. They want to create a visual that displays the distribution of customer ages, grouped into 5-year bins (e.g., 0-4, 5-9, 10-14, etc.). Which Power BI visual type is most suitable for effectively visualizing this binned numerical data?Explore and analyze data (15-20%)
  12. 62.A data scientist is performing exploratory data analysis on a large dataset stored in a Spark Delta table. They want to calculate the 90th percentile of a `response_time` column to understand typical high-end performance. Which Spark SQL aggregate function should they use?Explore and analyze data (15-20%)
  13. 63.A data engineer is optimizing a PySpark script that processes a large Delta table named `event_logs`. They frequently need to extract the year and month from a `timestamp` column for filtering and grouping. Which PySpark function is the most efficient and idiomatic way to achieve this for both year and month extraction?Explore and analyze data (15-20%)
  14. 64.A retail company is migrating its historical sales data, totaling 50 terabytes, from an on-premises SQL Server database to a Microsoft Fabric Lakehouse. The migration needs to be completed within a strict two-week deadline, and network bandwidth is a significant constraint, making direct online transfer impractical for the entire dataset. What is the most appropriate method for ingesting this data into Microsoft Fabric?Plan and implement data analytics solutions (10-15%)
  15. 65.A data engineer is writing a PySpark script to process a large DataFrame `log_data_df` containing web server logs. The DataFrame has a `timestamp` column (string, in 'YYYY-MM-DD HH:MM:SS' format) and `request_path` (string). The engineer needs to extract the hour of the day from the `timestamp` column as an integer for further analysis. Which PySpark function should be used?Explore and analyze data (15-20%)
  16. 66.A data engineer is working with a PySpark DataFrame named `transactions_df` that contains `transaction_id` (string), `product_category` (string), and `amount` (double). The engineer needs to calculate the total `amount` for each `product_category` and then filter out categories where the total `amount` is less than 1000. Which PySpark operation sequence correctly achieves this?Explore and analyze data (15-20%)
  17. 67.A data engineer is optimizing a Spark SQL query that frequently joins a large `FactSales` table with a smaller `DimProduct` table. Both tables are stored in a Delta Lake. To improve join performance, the engineer wants to ensure the smaller table is broadcasted during the join operation. Which Spark SQL hint should be used for this purpose?Explore and analyze data (15-20%)
  18. 68.A data engineer needs to ingest a large volume of CSV files from an external SFTP server into a Microsoft Fabric Lakehouse. The CSV files contain header rows and are comma-delimited. The ingestion process must be robust, handle potential malformed records by quarantining them, and allow for schema inference. Which Data Pipeline activity should be used for this scenario?Plan and implement data analytics solutions (10-15%)
  19. 69.A data engineering team is planning to ingest data from an on-premises SQL Server database into a Microsoft Fabric Lakehouse. The database contains several tables, and for some tables, only new or modified records need to be ingested daily to minimize data transfer and processing. Which ingestion pattern is best suited for this requirement?Plan and implement data analytics solutions (10-15%)
  20. 70.A global manufacturing company uses Microsoft Fabric to manage its supply chain data. They have a central Lakehouse and several regional Lakehouses, all within the same Fabric workspace. Data from regional Lakehouses needs to be aggregated into the central Lakehouse for global reporting. The company wants to avoid physically copying data and instead allow the central Lakehouse to directly access the regional data. Which feature should be implemented to achieve this?Plan and implement data analytics solutions (10-15%)
  21. 71.A data engineer is designing a solution to ingest data from an external relational database into a Microsoft Fabric Lakehouse. The source table contains a `last_modified_timestamp` column, and only new or updated records since the last ingestion run should be loaded to optimize performance and reduce data volume. Which type of ingestion strategy is most suitable for this scenario?Plan and implement data analytics solutions (10-15%)
  22. 72.A data analyst is querying a large Spark Delta table containing customer order data. They need to find the top 5 customers by total order value. The table has `customer_id` and `order_value` columns. Which combination of Spark SQL clauses will achieve this?Explore and analyze data (15-20%)
  23. 73.A data analyst needs to retrieve all customer records from a `Customers` table where the `Country` column is 'USA' and the `LastPurchaseDate` is after January 1, 2023. Which SQL clause should be used to filter the rows based on these conditions?Explore and analyze data (15-20%)
  24. 74.A company is ingesting real-time financial transaction data into a Microsoft Fabric Lakehouse. The data arrives at a high velocity (thousands of events per second) and needs to be available for near real-time analytics. To handle this volume efficiently and ensure consistent data quality, the data engineering team wants to aggregate these events into small, manageable batches before writing them to the Lakehouse. Which ingestion pattern is being described?Plan and implement data analytics solutions (10-15%)
  25. 75.A data scientist is performing exploratory data analysis on a large dataset of customer feedback stored in a Spark Delta table named `feedback_data` in Microsoft Fabric. They need to find the most frequent keywords mentioned in the feedback for each product. Which Spark SQL function, combined with windowing, is best suited to rank keywords by frequency within each product?Explore and analyze data (15-20%)
  26. 76.A data scientist is performing exploratory data analysis on a large dataset in a Spark Delta table named `sensor_readings`. They need to identify the top 5 most frequent sensor IDs that have reported readings above a certain threshold (e.g., 100 degrees Celsius) in the last 24 hours. Which sequence of Spark SQL clauses should be used to achieve this?Explore and analyze data (15-20%)
  27. 77.A data analytics team is planning to use Microsoft Fabric to analyze customer behavior. They have identified several data sources, including an Azure SQL Database, Salesforce, and flat files on Azure Data Lake Storage Gen2. Before ingestion, they need to define the data requirements, data quality rules, and data retention policies for each source. Which phase of planning a Fabric analytics solution does this activity primarily fall under?Plan and implement data analytics solutions (10-15%)
  28. 78.A company is setting up a Microsoft Fabric Lakehouse to store sensitive customer data. They need to ensure that only authorized personnel can access specific columns containing personally identifiable information (PII) within a Delta table, even when querying through the SQL endpoint. Other users should still be able to query non-PII columns. Which security feature should be implemented?Plan and implement data analytics solutions (10-15%)
  29. 79.A BI developer is creating a Power BI report using data from a Lakehouse in Microsoft Fabric. They need to visualize the weekly sales trends over the last year. The data is stored in a Delta table. Which type of visual is most appropriate for showing trends over time?Explore and analyze data (15-20%)
  30. 80.A data engineer is working with a large dataset in a Microsoft Fabric Lakehouse. They need to count the total number of rows in a Delta table named `customer_transactions` using Spark SQL. Which of the following queries will achieve this efficiently?Explore and analyze data (15-20%)
  31. 81.A data analyst is querying a large Delta table `orders` in Microsoft Fabric. They need to retrieve all orders placed in the year 2023 by customers whose `customer_id` is an even number. Which SQL query correctly combines these conditions?Explore and analyze data (15-20%)
  32. 82.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Date' table which is marked as a date table. The modeler defines several time-intelligence measures, such as 'Year-to-Date Sales' and 'Previous Year Sales'. When users query these measures, they consistently report incorrect values. Upon investigation, the modeler finds that the 'Date' table does not have a continuous date range, with some missing dates. What is the most likely cause of the incorrect time-intelligence calculations?Implement and manage semantic models (30-35%)
  33. 83.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' fact table and a 'Date' dimension table. The 'Date' table contains columns like 'DateKey', 'FullDateAlternateKey', 'DayNumberOfWeek', 'MonthName', 'CalendarYear'. The modeler needs to ensure that all time intelligence functions (e.g., YTD, MTD, QTD) work correctly and efficiently across the model. What is the most critical property to configure for the 'Date' table to enable proper time intelligence?Implement and manage semantic models (30-35%)
  34. 84.A data modeler is optimizing a large semantic model in Microsoft Fabric. The model contains several complex measures that use similar logic but apply different aggregation types (e.g., Sum of Sales, Average of Sales, Count of Sales). To reduce redundancy, improve maintainability, and ensure consistency across these measures, which feature should the data modeler implement?Implement and manage semantic models (30-35%)
  35. 85.A data engineer is configuring a semantic model in Microsoft Fabric. The model sources data from an Azure Data Lake Storage Gen2 account, specifically from Delta Lake tables. The requirement is to provide the fastest possible query performance for reports, without having to load the entire dataset into memory or frequently refresh the data. The data in the Delta Lake tables is updated continuously. Which storage mode is specifically designed for this scenario in Microsoft Fabric?Implement and manage semantic models (30-35%)
  36. 86.A data engineer is designing a semantic model in Microsoft Fabric. The model will contain a 'Product' dimension table that is relatively small and frequently queried. The 'Sales' fact table is very large. The engineer wants to ensure that queries involving the 'Product' dimension are always fast, while still allowing the 'Sales' fact table to be queried directly from the source for the latest data without importing it. Which storage mode should be applied to the 'Product' dimension table?Implement and manage semantic models (30-35%)
  37. 87.A data engineer is designing a semantic model in Microsoft Fabric. The model needs to contain a complete history of sales transactions, which is a very large dataset. However, reports primarily focus on aggregated sales metrics (e.g., total sales by month, average daily sales). Users occasionally need to drill down to individual transactions, but this is less frequent. The goal is to optimize query performance for common aggregated reports while still allowing drill-down to detailed data. Which feature should the data engineer implement?Implement and manage semantic models (30-35%)
  38. 88.A data engineer is integrating a new data source into an existing semantic model in Microsoft Fabric. The new source contains sensitive customer data that must be accessible only to authorized personnel, even at the column level. The engineer needs to ensure that specific columns, such as 'Customer_SSN' and 'Customer_CreditCard', are completely hidden from unauthorized users, regardless of their role or any row-level security applied. Which security feature should be implemented?Implement and manage semantic models (30-35%)
  39. 89.A data engineer is designing a new semantic model in Microsoft Fabric. The model will be used by various departments, each requiring access to different subsets of the data based on their roles. The current plan involves creating multiple versions of the semantic model, each pre-filtered for a specific department. You need to recommend a more efficient and scalable solution to ensure data security and reduce maintenance overhead. Which feature should you implement?Implement and manage semantic models (30-35%)
  40. 90.A data engineer is tasked with optimizing a large semantic model in Microsoft Fabric. The model contains several complex measures that perform calculations over historical data, such as 'Year-to-Date Sales' and 'Previous Quarter Revenue'. These measures are frequently used in reports and dashboards, leading to slow query performance. The underlying data is updated daily. What is the most effective approach to improve the performance of these specific measures without significantly increasing the model's footprint or refresh times for the entire dataset?Implement and manage semantic models (30-35%)
  41. 91.A data modeler is developing a complex semantic model in Microsoft Fabric. The model includes several measures that calculate sales performance across different time periods (e.g., 'Sales YTD', 'Sales MTD', 'Sales QTD'). To improve the reusability and simplify the model, the modeler wants to define a single base measure for sales and then apply different time intelligence calculations to it. Which feature should the data modeler use?Implement and manage semantic models (30-35%)
  42. 92.A data engineer is designing a new semantic model in Microsoft Fabric. The model will consume data from a large Azure Synapse Analytics dedicated SQL pool. The reporting requirements dictate that users need to perform ad-hoc analysis on the full dataset, which is several terabytes in size, with near real-time latency. However, some aggregate reports only require daily snapshots of summarized data. Which storage mode should the data engineer primarily choose for this semantic model to meet these requirements efficiently?Implement and manage semantic models (30-35%)
  43. 93.A data engineer is configuring a semantic model in Microsoft Fabric. The model contains a table with customer information, including a 'CustomerKey' column. The business requires that the 'CustomerKey' column is always unique and never contains blank values, as it is used as the primary key for relationships across the model. How should the data engineer enforce this data quality rule within the semantic model design?Implement and manage semantic models (30-35%)
  44. 94.A data engineer is managing a large semantic model in Microsoft Fabric. The model sources data from an Azure Data Lake Storage Gen2 (ADLS Gen2) account, specifically from Parquet files. The data volume is growing rapidly, and a full refresh takes several hours daily, exceeding the allowable refresh window. The business requires data to be refreshed daily, but only the most recent data (last 30 days) needs to be frequently updated, while older data (beyond 30 days) can be refreshed less often or remain static. Which refresh strategy should be implemented?Implement and manage semantic models (30-35%)
  45. 95.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Customers' table and a 'Product Reviews' table. A customer can submit multiple reviews for different products, and a product can have reviews from multiple customers. The modeler needs to analyze customer demographics alongside product review sentiment. How should the modeler establish the relationship between 'Customers' and 'Product Reviews' to accurately link a customer to their reviews?Implement and manage semantic models (30-35%)
  46. 96.A data modeler is creating a semantic model in Microsoft Fabric. The model includes a 'Sales' table with a 'TransactionDate' column. For reporting purposes, users frequently need to analyze sales by year, quarter, month, and day. To support these time intelligence calculations efficiently without manually creating many calculated columns, what is the best practice for the 'TransactionDate' column?Implement and manage semantic models (30-35%)
  47. 97.A data engineer is working on a Microsoft Fabric semantic model that includes a dimension table named 'DimCustomer'. This table contains sensitive customer contact information that should only be visible to a limited group of users (e.g., Customer Service). For all other users, this sensitive information (specifically the 'EmailAddress' and 'PhoneNumber' columns) should be completely hidden, but they should still be able to see other customer attributes like 'CustomerName' and 'CustomerSegment'. How should the data engineer implement this security requirement?Implement and manage semantic models (30-35%)
  48. 98.A data architect is designing a semantic model in Microsoft Fabric. The model will contain highly sensitive financial data, and there is a strict requirement to prevent unauthorized users from even seeing the names of certain critical columns, such as 'EmployeeSalary' or 'CustomerCreditCardNumber'. Row-level security (RLS) is already implemented for data rows. Which security feature should be used to hide these specific columns from unauthorized users?Implement and manage semantic models (30-35%)
  49. 99.A data engineer is working on a Microsoft Fabric semantic model that consumes data from a large Azure SQL Database. The model has been configured in Import mode for optimal query performance. However, some tables contain sensitive information, and data analysts should only see data from their assigned business unit. The business units are defined in a separate 'BusinessUnit' table and linked to the 'Sales' fact table. How can the data engineer implement this row-level security efficiently in the semantic model?Implement and manage semantic models (30-35%)
  50. 100.A data architect is designing a semantic model in Microsoft Fabric. The model will consume data from a large Azure SQL Database, which is updated hourly. Users require near real-time insights for critical operational dashboards, but also need to perform complex analytical queries that could be slow against the live source. The architect wants to balance query performance for analytical queries with data freshness for operational dashboards. Which storage mode configuration would best meet these requirements?Implement and manage semantic models (30-35%)