Step2Study

Microsoft Certified: Fabric Analytics Engineer Associate practice questions

216 free questions with answers and explanations.

Practice test
  1. 201.A data engineer is designing a semantic model in Microsoft Fabric. The model needs to track daily inventory levels and sales. The inventory data is updated once a day, and sales data is updated every hour. The model should provide the most up-to-date sales figures while maintaining daily inventory snapshots. What is the most efficient way to configure the refresh strategy for the semantic model components?Implement and manage semantic models (30-35%)
  2. 202.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Transactions' table, which is very large and contains historical data dating back 10 years. Only the last 3 years of data are frequently accessed by users for detailed analysis, while older data is rarely queried but must remain available for compliance. The modeler wants to optimize refresh performance and storage by refreshing only the most recent data, while efficiently handling the older, less-accessed data. Which feature should be configured?Implement and manage semantic models (30-35%)
  3. 203.A data engineer is optimizing a large semantic model in Microsoft Fabric. The model sources data from a Synapse Data Warehouse and contains several large fact tables. Users frequently run reports that aggregate data from these fact tables, but the queries are slow. The engineer notices that the same aggregations are repeatedly calculated. Which feature should the engineer implement to improve query performance by pre-calculating and storing these frequently used aggregations?Implement and manage semantic models (30-35%)
  4. 204.A data engineer is working on an existing semantic model in Microsoft Fabric. The model contains a 'Sales' table and a 'Products' table. Currently, there is a one-to-many relationship from 'Products[ProductID]' to 'Sales[ProductID]'. The requirement is to filter sales data when a product is selected and also to allow filtering of products based on sales activity (e.g., show only products that have sales). Which cross-filter direction should be configured for the relationship?Implement and manage semantic models (30-35%)
  5. 205.A data analyst is querying a large Delta table named `product_sales` in Microsoft Fabric. The table contains `sale_id`, `product_id`, `sale_date`, and `revenue` columns. The analyst needs to retrieve the first `product_id` sold on each `sale_date` for each `product_id`, based on the `sale_id` (assuming `sale_id` indicates order of sale within a date). If multiple sales occur at the exact same `sale_id` (which is highly unlikely but possible in data), any of them is acceptable. Which Spark SQL function should the analyst use to achieve this?Explore and analyze data (15-20%)
  6. 206.A data modeler is building a semantic model in Microsoft Fabric. The model needs to include a measure that calculates the 'Total Sales' for the current month, but only for products that had sales in the *previous* month. The 'Sales' table contains 'SaleAmount' and 'SaleDate'. A 'Date' dimension table is also available. Which DAX pattern should be used to achieve this calculation?Implement and manage semantic models (30-35%)
  7. 207.A data analyst is working with a large Spark Delta table named `customer_transactions` in Microsoft Fabric. The table contains columns like `transaction_id`, `customer_id`, `transaction_date`, and `amount`. The analyst needs to calculate the cumulative sum of `amount` for each `customer_id`, ordered by `transaction_date`. Which Spark SQL window function should the analyst use to achieve this?Explore and analyze data (15-20%)
  8. 208.A data engineer is working with a Spark Delta table named `product_reviews` in Microsoft Fabric. The table contains a `review_text` column and a `sentiment_score` column. The engineer needs to add a new column named `review_category` based on the `sentiment_score`. If the `sentiment_score` is greater than 0.75, the `review_category` should be 'Positive'. If it's less than 0.25, it should be 'Negative'. Otherwise, it should be 'Neutral'. Which PySpark function or method should the data engineer use to achieve this?Explore and analyze data (15-20%)
  9. 209.A data engineer is designing a semantic model in Microsoft Fabric. The model will contain sensitive customer information that should only be visible to specific sales regions. The requirement is that users from Region A should only see data for Region A, and users from Region B should only see data for Region B. There is no overlap in regional data access. Which security mechanism should the data engineer implement to achieve this granular control efficiently?Implement and manage semantic models (30-35%)
  10. 210.A data engineer is working with a large PySpark DataFrame `sensor_data_df` in Microsoft Fabric. The DataFrame contains `device_id`, `timestamp`, and `temperature` columns. The engineer needs to calculate the average temperature for each `device_id` over a 30-minute rolling window, based on the `timestamp`. Which PySpark window function definition correctly specifies the rolling window for this requirement?Explore and analyze data (15-20%)
  11. 211.A data modeler is developing a semantic model in Microsoft Fabric. The model includes a 'Sales' table with 'OrderDate' and a 'Returns' table with 'ReturnDate'. The model also has a 'Date' dimension table. The requirement is to enable efficient time intelligence calculations (e.g., Year-to-Date sales, Month-over-Month returns) across both sales and returns data using the single 'Date' dimension. How should the 'Date' dimension table be configured to support this?Implement and manage semantic models (30-35%)
  12. 212.A data engineer is designing a semantic model in Microsoft Fabric. The model will consume data from various sources, including a SQL Server database, a CSV file from a SharePoint folder, and a REST API. To ensure data consistency and enable advanced calculations, the engineer needs to apply several transformation steps to the data, such as merging tables, creating custom columns, and pivoting data, before it is loaded into the model. Which tool or feature within Microsoft Fabric is primarily used for these data preparation and transformation tasks?Implement and manage semantic models (30-35%)
  13. 213.A data engineering team is planning to ingest data from an external relational database into a Microsoft Fabric Lakehouse. The source database contains several large tables, and the team needs to ensure that only new or updated records are ingested during subsequent runs to optimize performance and resource usage. Which ingestion strategy should the team implement?Plan and implement data analytics solutions (10-15%)
  14. 214.An organization uses Microsoft Fabric and has multiple departments, each with its own workspace. The Fabric administrator needs to ensure that all workspace administrators across the organization can effectively manage their workspaces without granting them tenant-level administrative privileges. The administrator also wants to delegate the ability to assign users to workspace roles. Which Fabric tenant setting should be configured?Govern and administer Fabric (10-15%)
  15. 215.A company is designing a new data analytics solution in Microsoft Fabric. They want to ensure that sensitive customer information, such as social security numbers and credit card details, is protected while still allowing analysts to work with other non-sensitive customer data. The solution must provide granular access control based on user roles. Which security feature should be implemented?Plan and implement data analytics solutions (10-15%)
  16. 216.A data analytics team is developing several critical reports and dashboards in a Microsoft Fabric workspace. They need to ensure that specific sensitive columns within a table are not visible to users with a 'Viewer' role, even if those users have access to the table itself. The solution must be implemented directly within the Lakehouse data model. Which security measure should the team implement?Govern and administer Fabric (10-15%)