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?
AData Vault hubs, links and satellites in silver, and star-schema data marts in gold
BStar-schema data marts in silver, and Data Vault hubs, links and satellites in gold
CData Vault hubs, links and satellites in bronze, and star-schema data marts in silver
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.
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?
ACast the two columns in the bronze table, since schema enforcement and type casting belong to bronze
BKeep the bronze fields as they are, and cast the two columns when the silver table is built
CKeep bronze as it is, and go on casting the columns in each gold report query that reads bronze
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.
According to the Databricks data modeling page, why do models like the star schema or snowflake schema perform well on Databricks?
ABecause Databricks stores star and snowflake schemas in a special file format
BBecause they require no joins at all
CStandard queries need fewer joins, with fewer keys to keep in sync
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.
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?
APre-joining stores, cities and countries in SQL into one dataset first
BNothing more: country is reachable from sales through the many-to-one chain
CFlattening cities and countries into stores, as one level is the limit
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.
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?
AAs streaming reads
BAs full batch reads of the whole bronze table on every run
CAs reads from the gold layer instead of bronze
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.