Home / Data Analyst practice test / Data Modeling with Databricks SQL

Free · 4 questions with explanations

Data Modeling with Databricks SQL: Databricks Data Analyst Associate Practice Questions

Exam-style questions on Data Modeling with Databricks SQL. Pick your answer, then open the explanation to see why it's right — and why the other options are wrong.

Take a full timed test free → All Data Analyst topics
1 Data Modeling with Databricks SQL

A data engineering team is implementing Medallion Architecture on Databricks. A business analyst asks which layer of the architecture is most appropriate to apply star or snowflake dimensional modeling for direct use by BI tools and reporting dashboards. Which layer should the analyst be directed to?

  1. AThe Bronze layer, because raw source data ingested without transformation preserves the original OLTP schema structure, which already reflects the entity-relationship model that star schemas are designed to approximate for analytical query workloads
  2. BThe Silver layer, because cleansed and deduplicated records produced at the Silver stage represent the earliest point where dimensional attributes are stable enough to be organized into confirmed fact and dimension table structures
  3. CBoth the Bronze and Silver layers equally, because Medallion Architecture explicitly requires that dimensional modeling conventions be enforced uniformly from ingestion onward so that downstream consumers never encounter raw or partially-processed data
  4. DNo specific layer, because the Medallion Architecture is a compute orchestration pattern that manages pipeline dependencies and schedules transformations but does not define or recommend any particular data modeling convention for any given layer
  5. EThe Gold layer, which holds curated, aggregated, business-ready data organized as star or snowflake schemas for BI consumption
Show answer & explanation

Correct answer: E

WHY E: In Medallion Architecture, the Gold layer is where data is transformed into curated, aggregated, and business-ready form. This is the layer where dimensional modeling patterns — star schemas (fact + denormalized dimension tables) or snowflake schemas (fact + normalized dimension hierarchies) — are applied to support BI tools, dashboards, and reporting. WHY NOT A: The Bronze layer stores raw, unprocessed data exactly as received from source systems, including schema imperfections, duplicates, and nulls. It does not apply dimensional modeling. WHY NOT B: The Silver layer applies cleansing, deduplication, and schema enforcement, but it generates cleansed operational entities, not business-aggregated dimensional models optimized for BI. WHY NOT C: Medallion Architecture explicitly reserves each layer for a specific transformation purpose; Bronze and Silver are not designed for dimensional modeling — that belongs in Gold. WHY NOT D: Medallion Architecture does define progressive transformation conventions for each layer, including that the Gold layer is the appropriate home for consumption-ready dimensional models; it is not purely a scheduling/orchestration abstraction.

2 Data Modeling with Databricks SQL

A data modeler explains to a junior analyst the key structural difference between a star schema and a snowflake schema. Which statement correctly describes what distinguishes a snowflake schema from a star schema?

  1. ADimension tables in a snowflake schema are normalized into multiple sub-dimension tables, reducing redundancy at the cost of additional JOIN operations
  2. BA snowflake schema places its fact table at the center and fully denormalizes every dimension attribute into a single flat table per dimension, trading storage efficiency for the maximum possible read performance on large-scale analytical query workloads across all BI platforms
  3. CA snowflake schema adds slowly-changing-dimension columns to every dimension table so that historical versions of each attribute are preserved in separate versioned rows alongside the current record, enabling point-in-time lookups without modifying the base fact table
  4. DA snowflake schema eliminates the use of fact tables altogether, stores transactional metrics directly inside dimension records as computed aggregate columns, and compresses the total table count to enable simpler single-table analytical queries without any explicit JOIN steps
  5. EA snowflake schema is designed exclusively for real-time streaming ingestion use cases and stores each event as an immutable append-only record partitioned by arrival timestamp, while a star schema only supports batch-loaded snapshots of slowly changing historical dimension state
Show answer & explanation

Correct answer: A

WHY A: The defining characteristic of a snowflake schema is that dimension tables are normalized into sub-dimension tables (e.g., a Date dimension broken into Year, Quarter, Month tables). This reduces storage redundancy compared to a star schema but requires more JOINs to traverse the normalized hierarchy. WHY NOT B: Describing a schema that fully denormalizes dimension attributes into flat tables is the definition of a STAR schema, not a snowflake schema. The two are confused here. WHY NOT C: Adding slowly-changing-dimension (SCD Type 2) versioning is a dimension management technique that can be applied to either star or snowflake schemas; it is not a defining structural difference between the two schema types. WHY NOT D: Eliminating fact tables is not a characteristic of any recognized dimensional schema. Both star and snowflake schemas use fact tables as the central repository of transactional metrics. WHY NOT E: Neither star nor snowflake schemas are restricted to specific ingestion modes (batch vs. streaming). Both can be populated via batch or streaming pipelines and are independent of how the underlying data arrives.

3 Data Modeling with Databricks SQL

A data architect is designing a warehouse schema to support fast ad hoc BI queries on sales transactions. They want minimal JOIN complexity and are willing to accept some data redundancy in dimension tables. Which schema pattern best fits this requirement?

  1. AA central fact table connected to multiple denormalized dimension tables, forming a star-shaped join pattern optimized for fast analytical queries
  2. BA schema where each dimension is fully normalized into a hierarchy of sub-dimension tables linked by foreign keys, reducing redundancy at the cost of requiring several additional JOIN operations per analytical query
  3. CA design built around hub entities storing only business keys, satellite tables holding descriptive attributes with full change history, and link tables capturing relationships between hubs with auditable timestamps on every transaction record
  4. DA multi-tiered staging pipeline where raw data progresses through ingestion, cleansing, and aggregation transformation zones, with schema enforcement applied at each stage before the data is loaded into the final presentation layer
  5. EA network-style entity-relationship model where join tables handle every many-to-many relationship, no single table is designated as a central fact or measure store, and every node in the graph connects symmetrically to its neighbors
Show answer & explanation

Correct answer: A

WHY A: A star schema places a single fact table at the center, surrounded by flat, denormalized dimension tables. This minimizes JOIN depth and enables fast aggregation queries, making it the standard choice when query simplicity is prioritized over storage efficiency. WHY NOT B: Normalizing dimension tables into sub-dimension hierarchies describes a snowflake schema, which trades JOIN simplicity for reduced redundancy — which is the opposite of what the requirement asks for. WHY NOT C: Hubs, satellites, and links describe a data vault schema, which is optimized for auditable, scalable historical tracking, not for minimal-JOIN BI query performance. WHY NOT D: A multi-tiered staging pipeline with transformation zones describes the Medallion Architecture (Bronze/Silver/Gold), an ingestion and processing pattern, not a dimensional schema design. WHY NOT E: A symmetric network-style graph with join tables for many-to-many relationships describes an entity-relationship (ER) or OLTP model, not an analytical dimensional schema. It is unsuitable for BI-focused analytical workloads.

4 Data Modeling with Databricks SQL

An enterprise data team needs a schema that tracks the full change history of business entities over time, handles data from multiple source systems with different keys for the same real-world entity, and supports auditable lineage. Which schema best meets these requirements?

  1. AStaging tables that buffer raw source extracts, followed by core dimensional tables built with surrogate keys, and outbound data mart tables aggregated and shaped specifically for individual consumer teams and reporting groups
  2. BA star schema with slowly-changing dimension (SCD) Type 2 applied to every dimension table, adding start and end date columns to each dimension row so that historical attribute states are fully preserved alongside the current version
  3. CA flat denormalized wide table where every attribute of every entity is stored in a single record per business object, enabling the simplest possible analytical query with no joins required while tolerating high column sparsity
  4. DA data vault schema using Hubs to store business keys, Links to capture relationships between Hubs, and Satellites to hold descriptive attributes and historical changes with full auditability
  5. EA snowflake schema where every dimension is normalized into a strict third-normal-form hierarchy, foreign key relationships enforce referential integrity at the database level, and all updates are applied in place without preserving prior attribute values
Show answer & explanation

Correct answer: D

WHY D: Data vault schema is purpose-built for enterprise environments with multiple source systems. Hubs hold business keys (one per real-world entity regardless of source), Links model relationships between hubs, and Satellites store all descriptive attributes plus full change history with load timestamps. This directly satisfies auditable lineage, multi-source key integration, and historical tracking requirements. WHY NOT A: A staging-plus-mart architecture describes an ETL pipeline pattern, not a schema design that natively addresses multi-source key integration or change history tracking at the entity level. WHY NOT B: SCD Type 2 on a star schema does preserve history but does not address multi-source key integration across systems or provide the structured auditability and decoupled loading that data vault offers. WHY NOT C: A flat wide denormalized table collapses all attributes into a single record and does not preserve historical changes or support multi-source business key reconciliation. WHY NOT E: A third-normal-form snowflake schema applies in-place updates, which destroys history. It also requires complex JOINs and does not have the built-in multi-source business key isolation that data vault hubs provide.

Take the full Data Analyst practice test →

Keep going

Other Data Analyst topics


Executing queries using Databricks SQL and Databricks SQL Warehouses 8 practice questions → Working with Dashboards and Visualizations in Databricks 8 practice questions → Analyzing Queries 8 practice questions → Developing, Sharing, and Maintaining AI/BI Genie spaces 7 practice questions → Managing Data 5 practice questions → Securing Data 5 practice questions → Understanding of Databricks Data Intelligence Platform 5 practice questions → Importing Data 4 practice questions →