Querying Cross-System Analytics by Joining Data from a Delta Table and a Federated Data Source. DATA-ANALYST-ASSOCIATE Practice Questions (Page 2)

Questions 6–10

1. 6application · medium

   A Delta table has a column `status` as STRING with values like 'ACTIVE', 'INACTIVE'. A federated table has a column `status_code` as INTEGER where 1=ACTIVE, 0=INACTIVE. An analyst needs to join these tables on status. What is the best way to handle this schema difference?

   • AUse a CASE statement in the join condition to translate the integer to a string.
   • BChange the Delta table's status column to INTEGER and use the codes.
   • CCast the integer column to STRING and join on the string values.
   • DCreate a mapping table in Databricks that translates status codes to strings and join through it.
   Reveal Solution Discussion Select an answer first
2. 7application · easy

   A data analyst is new to Databricks SQL and needs to understand what a federated data source is. Which statement accurately describes a federated data source in Databricks SQL?

   • AA data source that is replicated to Databricks using Delta Sharing.
   • BA data source that is only accessible through the Databricks REST API.
   • CA data source that is stored in Databricks but managed by an external system.
   • DAn external system that Databricks SQL can query directly without copying data.
   Reveal Solution Discussion Select an answer first
4. 8expert · hard

   A company has a Delta table `events` with 1 billion rows and a federated PostgreSQL table `users` with 1 million rows. The analyst needs to join them on `user_id`. The query is slow because the PostgreSQL table is large and the join causes a shuffle. What is the best strategy to improve performance?

   • AFilter the Delta table to reduce its size before the join.
   • BUse a broadcast join hint on the `users` table.
   • CUse a sort-merge join hint to optimize the join.
   • DIncrease the SQL warehouse size to handle the shuffle.
   Reveal Solution Discussion Select an answer first
5. 9expert · hard

   A Delta table has a column `order_date` stored as a DATE type. A federated PostgreSQL table has a column `order_ts` stored as TIMESTAMP. An analyst needs to join these tables on the date portion of the timestamp. The analyst writes: `ON d.order_date = CAST(p.order_ts AS DATE)`. The query is slow. What is the most effective way to improve performance?

   • AUse a broadcast join to send the Delta table to the PostgreSQL side.
   • BCreate a new column in the Delta table that stores the timestamp as a string and join on that.
   • CUse a range join: `ON p.order_ts >= d.order_date AND p.order_ts < DATE_ADD(d.order_date, 1)`.
   • DPush the cast to the federated side by writing `ON CAST(d.order_date AS TIMESTAMP) = p.order_ts`.
   Reveal Solution Discussion Select an answer first
6. 10application · medium

   A retail company stores daily sales transactions in a Delta table on Databricks. The marketing team maintains customer segments in a PostgreSQL database hosted on-premises. An analyst needs to run a daily report that shows sales by customer segment for the last 30 days. The analyst has already created a federated connection to the PostgreSQL database. Which query approach should the analyst use?

   • AUse a three-part name (catalog.schema.table) to reference the PostgreSQL table directly in a SELECT statement that joins it with the Delta table.
   • BUse the COPY INTO command to load the PostgreSQL table into a Delta table before performing the join.
   • CExport the PostgreSQL customer segments to a CSV file, upload it to DBFS, and then join it with the Delta table in Databricks SQL.
   • DCreate a view in PostgreSQL that materializes the sales data from Databricks, then query that view from Databricks SQL.
   Reveal Solution Discussion Select an answer first