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.

Updated November 10, 2026

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, which keeps raw source data without transformation and so preserves the original OLTP schema that star and snowflake models are meant to mirror for BI tools
  2. BThe Silver layer, which holds cleansed and deduplicated records and is the earliest point where attributes are stable enough to be arranged into fact and dimension tables for BI
  3. CThe Bronze and Silver layers together, which apply dimensional modeling conventions from ingestion onward to keep BI consumers from encountering raw or partly processed data
  4. DNo specific layer, as the Medallion Architecture is a compute orchestration pattern that schedules pipeline dependencies and leaves data modeling conventions to each BI tool
  5. EThe Gold layer, which holds curated, aggregated, business-ready data and is where star or snowflake schemas are built for consumption by BI tools and reporting dashboards
Show answer & explanation

Correct answer: E

WHY E: In the Medallion Architecture, the Gold layer is where data is transformed into curated, aggregated, business-ready form. Dimensional modeling patterns are applied there: star schemas (fact plus denormalized dimension tables) or snowflake schemas (fact plus normalized dimension hierarchies) that 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 produces cleansed operational entities, not business-aggregated dimensional models optimized for BI. WHY NOT C: The Medallion Architecture reserves each layer for a specific transformation purpose; Bronze and Silver are not designed for dimensional modeling, which belongs in Gold. WHY NOT D: The Medallion Architecture does define progressive transformation conventions for each layer, including Gold as the home of consumption-ready dimensional models; it is not purely a scheduling or 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 joined directly to a set of denormalized dimension tables, each keeping its descriptive attributes in one flat table, so most analytical queries need a single join per dimension
  2. BA central fact table whose dimensions are normalized into hierarchies of sub-dimension tables linked by foreign keys, which reduces redundancy and adds several joins to most analytical queries
  3. CA design built around hub tables storing business keys, link tables capturing relationships between hubs, and satellite tables holding descriptive attributes with their change history and load timestamps
  4. DA multi-tiered pipeline in which raw data moves through ingestion, cleansing, and aggregation zones, with schema enforcement applied at each stage before data reaches the final presentation layer
  5. EA network-style entity-relationship model in which join tables resolve many-to-many relationships between entities and no table is designated as the central fact or measure store for analysis
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, which matches a team willing to accept redundancy in dimensions. WHY NOT B: Normalizing dimension tables into sub-dimension hierarchies describes a snowflake schema, which trades JOIN simplicity for reduced redundancy. That is the opposite of what the requirement asks for. WHY NOT C: Hubs, links, and satellites 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 pipeline with ingestion, cleansing, and aggregation zones describes the Medallion Architecture (Bronze/Silver/Gold), an ingestion and processing pattern, not a dimensional schema design. WHY NOT E: A network-style model with join tables for many-to-many relationships and no central fact table 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. AA layered warehouse with staging tables that buffer raw source extracts, core dimensional tables keyed by surrogate keys, and data mart tables aggregated and shaped for individual consumer teams
  2. BA star schema with slowly changing dimension (SCD) Type 2 on each dimension table, adding start and end date columns to keep earlier attribute states alongside the current version of each row
  3. CA flat denormalized wide table that stores the attributes of each business object in a single record, giving the simplest analytical queries with no joins while tolerating high column sparsity
  4. DA data vault schema with Hubs that store business keys, Links that capture relationships between Hubs, and Satellites that hold descriptive attributes and their historical changes with load timestamps
  5. EA snowflake schema with dimensions normalized into a third-normal-form hierarchy, foreign keys enforcing referential integrity, and updates applied in place over the prior attribute values
Show answer & explanation

Correct answer: D

WHY D: The 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 the descriptive attributes plus full change history with load timestamps. This directly satisfies auditable lineage, multi-source key integration, and historical tracking. 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 it 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 the 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 with in-place updates destroys history. It also requires complex JOINs and lacks the built-in multi-source business key isolation that data vault hubs provide.

Take the full Data Analyst practice test →