Skip to content

Data Analyst Associate · Domain 8: Data Modeling with Databricks SQL · 5% of the exam

Task 8.1: Apply industry-standard data modeling techniques, such as star, snowflake, and data vault schemas, to analytical workloads; Understand how industry-standard models align with the Medallion Architecture

Star, snowflake and data vault models as Databricks describes them, informational keys and joins on Databricks, and where each model sits in the bronze, silver and gold layers.

Study it

  • Star, snowflake and data vault models on Databricks and the medallion layers

    Lesson coming

Sample questions

Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.

Question 1 · choose 1

A company integrates customer and order data from six source systems whose schemas change often. It needs history and auditability in its integration layer, and business users want simple, query-friendly marts. Following Databricks' guidance on Data Vault and star schemas in the lakehouse, how should the models be placed?

  1. AData Vault hubs, links and satellites in silver, and star-schema data marts in gold
  2. BStar-schema data marts in silver, and Data Vault hubs, links and satellites in gold
  3. CData Vault hubs, links and satellites in bronze, and star-schema data marts in silver
  4. DA snowflake schema in bronze, and Data Vault satellites in gold for business users
Show the answer and why
  • AData Vault hubs, links and satellites in silver, and star-schema data marts in gold

    Correct

    The landing zone, Vault zone and data mart zone of a Data Vault design correspond to bronze, silver and gold, with dimensional marts in gold.

  • BStar-schema data marts in silver, and Data Vault hubs, links and satellites in gold

    Incorrect

    The guidance places the Vault in silver; gold holds the dimensional modeling and aggregation that business users query.

  • CData Vault hubs, links and satellites in bronze, and star-schema data marts in silver

    Incorrect

    Bronze is the landing zone for raw data; the Vault zone corresponds to silver and the marts to gold.

  • DA snowflake schema in bronze, and Data Vault satellites in gold for business users

    Incorrect

    Bronze keeps raw source data with minimal validation, and Data Vault structures belong to the integration layer, not the business marts.

Data Vault organizes data into hubs (business keys), links (relationships) and satellites (attributes and history). Databricks maps a Data Vault design's landing zone, Vault zone and data mart zone to the bronze, silver and gold layers; gold is where dimensional modeling and aggregation happen, for example star-schema marts.

Question 2 · choose 1

The bronze table of a supplier feed keeps every field as STRING or VARIANT, including price and delivery_date. An analyst who builds gold reports asks the data engineers to cast price to DECIMAL and delivery_date to DATE in the bronze table itself, so that she no longer has to cast them in her queries. Following Databricks' medallion architecture guidance, what should the team do?

  1. ACast the two columns in the bronze table, since schema enforcement and type casting belong to bronze
  2. BKeep the bronze fields as they are, and cast the two columns when the silver table is built
  3. CKeep bronze as it is, and go on casting the columns in each gold report query that reads bronze
  4. DLoad the feed straight into a typed silver table at ingestion, and drop the bronze table
Show the answer and why
  • ACast the two columns in the bronze table, since schema enforcement and type casting belong to bronze

    Incorrect

    Bronze gets only minimal validation; schema enforcement and type casting are listed among the operations performed in silver tables.

  • BKeep the bronze fields as they are, and cast the two columns when the silver table is built

    Correct

    Databricks recommends storing most bronze fields as string, VARIANT or binary to protect against unexpected schema changes, and type casting is done in silver.

  • CKeep bronze as it is, and go on casting the columns in each gold report query that reads bronze

    Incorrect

    Bronze is meant for workloads that enrich data for silver tables, not for access by analysts.

  • DLoad the feed straight into a typed silver table at ingestion, and drop the bronze table

    Incorrect

    Databricks does not recommend writing to silver tables directly from ingestion, because schema changes or corrupt records in the source then cause failures.

In the medallion architecture, bronze keeps the raw state of the source with minimal validation, and Databricks recommends storing most bronze fields as string, VARIANT or binary so that unexpected schema changes do not drop data. Silver tables are built by reading bronze, and silver is where schema enforcement, handling of missing values, type casting and deduplication take place.

Question 3 · choose 1

According to the Databricks data modeling page, why do models like the star schema or snowflake schema perform well on Databricks?

  1. ABecause Databricks stores star and snowflake schemas in a special file format
  2. BBecause they require no joins at all
  3. CStandard queries need fewer joins, with fewer keys to keep in sync
  4. DBecause they store every table in a separate catalog
Show the answer and why
  • ABecause Databricks stores star and snowflake schemas in a special file format

    Incorrect

    The data modeling page attributes the performance to fewer joins and fewer keys, not to a file format.

  • BBecause they require no joins at all

    Incorrect

    They reduce joins rather than removing them.

  • CStandard queries need fewer joins, with fewer keys to keep in sync

    Correct

    The page says these models perform well as there are fewer joins in standard queries and fewer keys to keep in sync.

  • DBecause they store every table in a separate catalog

    Incorrect

    Catalog layout is not the reason given.

Databricks notes that models like the star schema or snowflake schema perform well because standard queries have fewer joins and fewer keys to keep in sync, and wider tables let the optimizer skip data using file-level statistics.

Databricks documentation

Question 4 · choose 1

An AI/BI dashboard model relates the fact dataset sales to stores, stores to cities and cities to countries, each relationship many-to-one, so the store dimension is snowflaked. A chart must show total sales by country. What do dashboard relationships need for this?

  1. APre-joining stores, cities and countries in SQL into one dataset first
  2. BNothing more: country is reachable from sales through the many-to-one chain
  3. CFlattening cities and countries into stores, as one level is the limit
  4. DAn extra relationship from sales straight to countries for the chart
Show the answer and why
  • APre-joining stores, cities and countries in SQL into one dataset first

    Incorrect

    Relationships resolve the joins a visualization needs at query time, so no pre-join in SQL is needed.

  • BNothing more: country is reachable from sales through the many-to-one chain

    Correct

    A snowflake schema is a supported pattern, and dimension fields are reachable through any many-to-one chain, however many hops.

  • CFlattening cities and countries into stores, as one level is the limit

    Incorrect

    Dimension fields stay reachable through any number of many-to-one hops.

  • DAn extra relationship from sales straight to countries for the chart

    Incorrect

    The chain of many-to-one relationships already reaches countries; a snowflake schema needs no extra relationship.

Dashboard relationships perform query-time joins based on the cardinality set for each relationship. A snowflake schema, where a fact table joins a chain of dimensions, is a supported pattern, and a dimension field is reachable through any many-to-one chain, however many hops, so country can group sales without pre-joining or flattening the dimension.

Question 5 · choose 1

In Databricks' example of a medallion architecture for an operations team, how are the bronze, silver and gold layers stored?

  1. AAll three layers in one table, with a column for the layer
  2. BEach layer in a separate metastore
  3. CEach layer in a separate workspace
  4. DEach layer in a different schema of the ops catalog
Show the answer and why
  • AAll three layers in one table, with a column for the layer

    Incorrect

    Each layer holds its own tables.

  • BEach layer in a separate metastore

    Incorrect

    The layers are schemas in one catalog.

  • CEach layer in a separate workspace

    Incorrect

    The example uses schemas of one catalog, not workspaces.

  • DEach layer in a different schema of the ops catalog

    Correct

    In the example, each layer is stored in a different schema of the ops catalog: ops.bronze, ops.silver and ops.gold.

The medallion architecture page's example stores each layer in a different schema of the ops catalog (ops.bronze, ops.silver, ops.gold).

Question 6 · choose 1

Following the medallion guidance, an analyst builds silver tables from append-only bronze tables of large size. How does Databricks recommend configuring most of these reads from bronze?

  1. AAs streaming reads
  2. BAs full batch reads of the whole bronze table on every run
  3. CAs reads from the gold layer instead of bronze
  4. DAs direct writes from ingestion into silver, skipping bronze
Show the answer and why
  • AAs streaming reads

    Correct

    Assuming all sources are append-only, Databricks recommends configuring most reads from bronze as streaming reads.

  • BAs full batch reads of the whole bronze table on every run

    Incorrect

    Batch reads should be reserved for small datasets.

  • CAs reads from the gold layer instead of bronze

    Incorrect

    Silver tables are built from bronze or other silver tables.

  • DAs direct writes from ingestion into silver, skipping bronze

    Incorrect

    Databricks does not recommend writing to silver tables directly from ingestion.

To build the silver layer, read from bronze or silver tables and write to silver tables. Assuming append-only sources, configure most reads from bronze as streaming reads and reserve batch reads for small datasets.

Practice domain 8 →Practice all domains →