Create a Materialized View, Including Knowing When to Use Streaming Tables and Materialized Views, and Differentiate Between Dynamic and Materialized Views. DATA-ANALYST-ASSOCIATE Practice Questions (Page 2)

Questions 6–10

1. 6foundation · easy

   What is the correct SQL syntax to create a materialized view named daily_sales_summary in Databricks SQL?

   • ACREATE MATERIALIZED TABLE daily_sales_summary AS SELECT ...
   • BCREATE MATERIALIZED VIEW daily_sales_summary AS SELECT ...
   • CCREATE VIEW daily_sales_summary MATERIALIZED AS SELECT ...
   • DCREATE VIEW daily_sales_summary AS SELECT ... MATERIALIZED
   Reveal Solution Discussion Select an answer first
2. 7application · medium

   A logistics company has a stream of IoT sensor data from delivery vehicles arriving in a Delta table. A data analyst needs to create a table that continuously updates with the latest vehicle locations and status, and this table will be used by a real-time tracking dashboard. The analyst wants to define this using Databricks SQL. Which statement should the analyst use?

   • ACREATE STREAMING TABLE vehicle_tracking AS SELECT ... FROM sensor_data;
   • BCREATE MATERIALIZED VIEW vehicle_tracking AS SELECT ... FROM sensor_data;
   • CCREATE VIEW vehicle_tracking AS SELECT ... FROM sensor_data;
   • DCREATE TABLE vehicle_tracking AS SELECT ... FROM sensor_data;
   Reveal Solution Discussion Select an answer first
4. 8application · easy

   A data analyst has a materialized view that is refreshed on a schedule. The analyst needs to manually trigger a refresh immediately to incorporate recent changes to the source data. Which SQL statement should the analyst use?

   • AREFRESH MATERIALIZED VIEW sales_summary;
   • BEXECUTE REFRESH ON MATERIALIZED VIEW sales_summary;
   • CUPDATE MATERIALIZED VIEW sales_summary;
   • DALTER MATERIALIZED VIEW sales_summary REFRESH;
   Reveal Solution Discussion Select an answer first
5. 9application · easy

   A data analyst needs to provide a simplified interface for business users to query customer data. The underlying customer table is large and changes frequently. The analyst wants to hide the complexity of a multi-table join and ensure users always see the latest data, but performance is not a primary concern. Which option should the analyst choose?

   • ACreate a materialized view that refreshes every hour.
   • BCreate a new static table that contains the joined data and refresh it manually.
   • CCreate a dynamic view that joins the tables.
   • DCreate a streaming table that continuously updates from the customer table.
   Reveal Solution Discussion Select an answer first
6. 10expert · hard

   A data analyst is designing a solution for a dashboard that shows hourly aggregated metrics. The source data arrives in near-real-time, but the dashboard only needs to be accurate to the hour. The analyst is considering using a materialized view with an hourly refresh. However, the underlying source table is very large, and the aggregation query is complex. What is the primary trade-off the analyst should consider when choosing between a materialized view and a streaming table for this use case?

   • AA materialized view provides lower latency than a streaming table because it stores the results.
   • BA streaming table cannot be used with a Delta table source, so a materialized view is the only option.
   • CA materialized view always provides the most up-to-date data, while a streaming table can be stale.
   • DA materialized view recomputes the entire aggregation on each refresh, which can be expensive on a large table, while a streaming table processes only new data incrementally.
   Reveal Solution Discussion Select an answer first