Databricks Data Analyst: analysing and securing
Analyzing Queries (15%), Securing Data (8%) and Data Modeling with Databricks SQL (5%) are 28% of the Data Analyst Associate exam between them — around 12 of the 45 scored questions.
Analysing is the largest of the three and the most distinctly analytical content on the exam: not writing a query, but judging whether its output is right.
Analyzing queries (15%)
Is this number plausible? The habit the section tests. An analyst who publishes a figure without sanity-checking it is the risk being examined.
Common logical errors to recognise:
| Error | Symptom |
|---|---|
| Join multiplying rows | Totals higher than the source; counts inflated |
| Filtering after aggregation | Groups missing or totals wrong |
| Inner join dropping records | Totals lower than expected; a category vanished |
| Null handling in aggregates | Averages computed over fewer rows than assumed |
| Duplicate source rows | Everything inflated by a consistent factor |
Reading a query profile. Where time went, how much data was scanned, and whether the query did far more work than the result justified.
Diagnosing slow queries. Scanning a whole table to return one month. An expensive join that could have been filtered first. Repeating work that could be computed once.
Comparing results across versions. When a number changes, determining whether the data changed or the query did.
Data modelling with Databricks SQL (5%)
About two questions, and one distinction carries most of them.
Views are saved logic. They run every time they are queried, so they are always current and cost compute per query.
Materialised views store computed results. They are fast and cheap to read, and they are only as current as their last refresh.
The rule: identical logic, run often, tolerant of slight staleness → materialised view. Needs to be current to the second, or run rarely → view.
Also here: choosing an appropriate grain for a reporting table, and basic denormalisation for analytical convenience.
Securing data (8%)
About four questions, from an analyst’s perspective rather than an administrator’s.
- Unity Catalog permissions — what you can see, and why a table might be invisible to you.
- Row-level and column-level restrictions, and masking of sensitive values.
- The rule that matters: restrict in place, never copy. A filtered copy of a table drifts from source, doubles storage and creates an ungoverned object. Column masking solves it once, correctly.
- Dashboards and Genie spaces respect underlying permissions — publishing a dashboard does not grant access to data the viewer could not otherwise query.
- Sharing responsibly: who can view, who can edit.
Sample questions
Question 1. A revenue report suddenly shows totals roughly triple the expected value. The underlying source data is unchanged. What should the analyst check first?
- A. Whether the SQL warehouse size was changed
- B. Whether a join is matching multiple rows per fact record
- C. Whether the dashboard’s visualisation settings changed
- D. Whether the analyst’s permissions were broadened
Show answer
Answer: B
A consistent multiplication of totals points to a join producing multiple matching rows per fact record, which is the classic cause of inflated aggregates. A warehouse size change affects speed not values, dashboard settings do not alter query results, and permissions would restrict rather than inflate data.
Question 2. Forty analysts run the same complex aggregation dozens of times daily. The source updates once overnight. What is the most efficient structure?
- A. A materialised view refreshed after the nightly load
- B. A standard view over the aggregation logic
- C. A larger SQL warehouse
- D. A manually maintained copy of the aggregated results
Show answer
Answer: A
A materialised view refreshed after the nightly load computes the aggregation once and serves cheap reads for all subsequent queries, matching the overnight update cadence. A standard view recomputes on every query, a larger warehouse pays more for repeated work, and a copied table must be maintained manually.
Question 3. An analyst publishes a dashboard to a wide audience. Some viewers cannot see any data, though the dashboard works for the analyst. What is the most likely reason?
- A. The SQL warehouse is stopped
- B. The dashboard’s refresh schedule has not run yet
- C. Those viewers lack permissions on the underlying tables
- D. The visualisations were configured with a user filter
Show answer
Answer: C
Dashboards respect the underlying data permissions, so viewers without access to the source tables see nothing even though the dashboard itself is shared. The warehouse being stopped would affect everyone including the analyst, visualisation settings do not filter by user, and the refresh schedule does not control visibility.
What to practise
Take a report you trust and deliberately break it: introduce a duplicating join and see the totals move. Then build the same aggregation as a view and as a materialised view, and compare the cost of querying each twenty times.
Finally, ask a colleague without access to your tables to open one of your dashboards. What they see — nothing — is the answer to a question the exam reliably asks.