Skip to content
Quizra

Data-Analyst-Associate Data Analyst Associate Practice Questions

Prepare for Data-Analyst-Associate with more than an answer.

204 questions in the full set19 sample questionsUpdated Jan 29, 2026
Study as flashcards
Exam fee
$200 USD
Level
Associate
Valid for
2 years
Domains covered on the exam 5
  1. Databricks SQL22%
  2. Data Management20%
  3. SQL in the Lakehouse29%
  4. Data Visualization and Dashboarding18%
  5. Analytics Applications11%
  1. 1

    Case Study:

    Company Background:
    BioSynth Therapeutics, a pharmaceutical research company, stores its clinical trial data in Databricks. Data integrity and auditability are of utmost importance. The primary dataset is a patient_vitals Delta table that receives hourly updates from monitoring devices. This table is used for real-time monitoring and long-term research.

    Current Situation:
    An automated data entry process incorrectly updated thousands of patient records on May 10th, 2023, at 4:00 PM UTC, corrupting the heart rate data for a specific trial group. The data science team discovered the error two days later, on May 12th. The incorrect data has already been used in preliminary reports, and the team needs to immediately revert the table to its state just before the corruption occurred, while preserving the ability to audit the erroneous transaction.

    Requirements:

    1. The patient_vitals table must be restored to its exact state as of May 10th, 2023, 3:59 PM UTC.
    2. The restoration process must be a single, atomic operation.
    3. The history of the table, including the bad transaction and the restoration itself, must be preserved in the transaction log.
    4. Analysts must be able to query the corrected, restored version of the table immediately after the operation.

    Problem:
    Which Databricks SQL command should the data analyst use to fix the corrupted data while meeting all compliance and auditability requirements?

    Show answer details

    Correct answer: C

    The RESTORE command is specifically designed for this scenario. It reverts a Delta table to a previous version or timestamp in a single, atomic transaction. Crucially, it does not erase the table's history; instead, it commits a new transaction that performs the restoration. This preserves the full audit trail, including the bad data and the RESTORE operation itself, meeting all the requirements. Manual DELETE/INSERT operations are complex and error-prone, while CREATE OR REPLACE would erase the original table's history.

  2. 2

    You are creating a dashboard with several visualizations that all depend on a single parameter: country_code. You want to allow users to select a country from a dropdown list, and have all visualizations on the dashboard update automatically. What is the most efficient way to achieve this?

    Show answer details

    Correct answer: C

    Databricks dashboards allow you to link parameters. The best practice is to create a single, authoritative parameter (like a query-based dropdown listing all countries) in one visualization's query. Then, for all other visualizations on the dashboard, you can configure their corresponding parameters to use the value from this single dashboard-level parameter. This creates a single point of control for the user.

  3. 3

    Which of the following SQL functions is used to combine strings from multiple rows into a single string?

    Show answer details

    Correct answer: B

    In Databricks SQL, there isn't a single direct function like STRING_AGG in some other SQL dialects. The standard pattern is to first use the aggregate function COLLECT_LIST() to gather all the string values from multiple rows into an array. Then, you wrap this with the ARRAY_JOIN() function to concatenate the elements of that array into a single string with a specified delimiter.

  4. 4

    A serverless SQL warehouse has been configured with auto-stop set to 10 minutes. At 9:00 AM, the warehouse starts up to run a query. The query finishes at 9:02 AM. No other queries are submitted. What will be the state of the warehouse at 9:15 AM?

    Show answer details

    Correct answer: B

    The auto-stop timer begins after the last query finishes. The last query finished at 9:02 AM. With a 10-minute auto-stop setting, the warehouse will remain idle until 9:12 AM (9:02 + 10 minutes). At 9:12 AM, it will automatically stop to save costs. Therefore, at 9:15 AM, the warehouse will be in a stopped state.

  5. 5

    Case Study:

    Company Background:
    RetailPulse Analytics is an e-commerce intelligence firm that processes massive volumes of clickstream data for its clients. The data engineering team has established a medallion architecture. Raw event data (clicks, page views, add-to-cart) lands in the bronze_events table as semi-structured JSON strings. A nightly job processes this data into a silver_sessionized_events table, which cleans, validates, and structures the events, assigning a unique session_id to each user session.

    Current Situation:
    The analytics team is tasked with creating a critical gold_user_sessions aggregate table. This table must summarize each user session, calculating metrics like session_start_time, session_end_time, total_page_views, items_added_to_cart, and whether a purchase was made (made_purchase_flag). The silver_sessionized_events table contains columns: user_id, session_id, event_timestamp, and event_type (e.g., 'page_view', 'add_to_cart', 'purchase').

    Requirements:

    • The solution must be a single, idempotent SQL query that can be re-run for a given day's data without creating duplicates.
    • The query must be highly performant, as it processes billions of events daily.
    • The final gold table should have one row per session_id.

    Which SQL query best fulfills these requirements to populate the gold_user_sessions table?

    graph TD subgraph Bronze Layer B[bronze_events (raw JSON)] end subgraph Silver Layer S[silver_sessionized_events (parsed, sessionized)] end subgraph Gold Layer G[gold_user_sessions (aggregated metrics)] end B --> S --> G
    Show answer details

    Correct answer: B

    This is the most robust and performant solution. Using a GROUP BY with conditional aggregation (e.g., COUNT(CASE WHEN event_type = 'add_to_cart' THEN 1 END)) is the standard, efficient way to pivot event types into columns. Wrapping this logic in a MERGE INTO statement on session_id makes the entire operation idempotent, satisfying the key requirement to prevent duplicates on re-runs.

  6. 6

    A financial services company is analyzing streaming transaction data stored in a bronze Delta table. An analyst needs to create a silver table that includes a new column, is_flagged, which is set to true if a transaction amount exceeds $10,000. The process must be idempotent and handle late-arriving data. Which SQL command is most appropriate for this continuous transformation?

    Show answer details

    Correct answer: B

    The MERGE command is the correct choice because it is designed for idempotent upsert (update/insert) operations. It can match records on a key (transaction_id) and either update existing records or insert new ones. This handles new and late-arriving data gracefully. CREATE OR REPLACE TABLE would reprocess the entire dataset each time, which is inefficient. INSERT INTO would create duplicates, and a simple UPDATE would not handle new records.

  7. 7

    An analyst is building a dashboard to monitor daily user engagement. A key visualization needs to show the count of active users. The underlying query for this visualization is computationally expensive. The dashboard is viewed frequently by executives, and fast load times are critical. Which feature should the analyst enable for this specific query to improve dashboard performance for all users?

    Show answer details

    Correct answer: C

    Databricks SQL provides a Query Cache that stores the results of a query. When the same query is executed again, Databricks returns the result from the cache, which is significantly faster than re-executing it. This is ideal for frequently accessed dashboards with expensive underlying queries. While increasing cluster size or using serverless compute can help with initial query execution, caching provides the most significant performance boost for repeated views of the same data.

  8. 8

    True or False: In Databricks SQL, a VIEW always stores a physical copy of the data derived from its defining query, similar to a materialized view in other database systems.

    Show answer details

    Correct answer: B

    This statement is false. A standard VIEW in Databricks SQL is a logical object that stores only the query definition. The query is re-executed each time the view is accessed. It does not store a physical copy of the data. Databricks does support MATERIALIZED VIEWs, which pre-compute and store the result set, but a standard VIEW does not.

  9. 9

    An analyst at a logistics company needs to create a report on late shipments. The shipments table contains shipment_id, estimated_delivery_date, and actual_delivery_date. The analyst needs to add a column delivery_status with three possible values: 'On-Time', 'Late', or 'In-Transit'. Which of the following SQL constructs is the most appropriate and readable way to implement this logic?

    Show answer details

    Correct answer: B

    A CASE statement is the standard and most readable SQL construct for handling multi-condition logic. It allows for clear evaluation of each condition (WHEN actual_delivery_date IS NULL THEN 'In-Transit', WHEN actual_delivery_date > estimated_delivery_date THEN 'Late') with a final ELSE clause. While nested IIF() functions can achieve this, they become very difficult to read and maintain as the number of conditions increases.

Create an account to continue.

Create accountSign in

Keep going

Related exams