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. The view-versus-materialised-view distinction comes up often, and the outline also names the classic schema designs below.
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.
Star, snowflake and data vault schemas
The section’s own objectives go beyond views: apply industry-standard modelling techniques (star, snowflake and data vault schemas) to analytical workloads, and understand how those models line up with the medallion architecture. Expect a scenario and a choice of model or layer, not a design exercise.
| Model | Shape | Strength | Cost |
|---|---|---|---|
| Star | One fact table joined directly to denormalised dimension tables | Simple SQL, one join per dimension, easy for dashboards and Genie | Repeated attribute values in dimensions |
| Snowflake | Dimensions split into further normalised tables, such as product → category → department | Less redundancy, one place to change a shared attribute | More joins in every query |
| Data vault | Hubs hold business keys, links hold relationships, satellites hold descriptive attributes with load history | Integrates many sources, insert-only, keeps full history for audit | Many joins; awkward to query directly |
Fit to the medallion layers. Databricks describes the silver layer as the place for integrated, write-friendly models such as data vault, and the gold layer as the place for consumption models such as Kimball star schemas. An analyst mostly queries gold, so a star schema is the default answer for reporting; data vault tables are what engineers build behind it.
Reading a star schema in SQL. Facts hold measures at a stated grain (one row per order line, say) plus foreign keys. Dimensions hold the attributes you filter and group by. Aggregate the fact, group by dimension columns, and check that each join is many-to-one from fact to dimension, otherwise totals inflate, the same error described under analysing queries above.
The pitfall: choosing snowflake “because it is more normalised and therefore faster”. Normalising dimensions reduces storage and update effort, but each extra join costs query time, which is why gold-layer reporting models are usually star schemas.
Primary and foreign key constraints can be declared on Unity Catalog tables to document these relationships; Databricks treats them as informational and does not enforce them, so they do not stop duplicate keys. The wider modelling ideas, including grain and relationship direction, are covered in star schema and modelling.
Question 1. A team ingests customer data from four source systems and must keep the full change history for audit, while analysts need simple, fast dashboard queries. Which arrangement matches the medallion guidance?
- A. A data vault in gold that analysts query directly
- B. A data vault in silver feeding a star schema in gold
- C. A snowflake schema in bronze feeding views in gold
- D. One wide denormalised table in bronze for every use
Show answer
Answer: B
A data vault suits integrating many sources with insert-only history, which fits the silver layer, and a star schema gives analysts simple, fast queries in gold. Querying a data vault directly means many joins, a snowflake in bronze puts modelling in the raw layer, and one wide table in bronze loses both history structure and governance.
Want more questions like this? Full Data Analyst practice tests →
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 2. 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.
Want more questions like this? Full Data Analyst practice tests →
Question 3. 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.
Want more questions like this? Full Data Analyst practice tests →
Question 4. 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.
Want more questions like this? Full Data Analyst practice tests →
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.