Data Modeling with Databricks SQL

What this chapter covers

Data Modeling with Databricks SQL is 5% of the Databricks Data Analyst Associate exam. It covers the industry-standard ways of organizing analytical tables and where each one sits in the lakehouse.

The chapter has one topic: star, snowflake and data vault schemas, and how they line up with the bronze, silver and gold layers of the Medallion Architecture.

Questions are mostly short scenarios in which the stated requirement (query speed for BI, storage of history, flexibility as sources change, audience) decides the answer. They are single-answer multiple choice, and short SQL is shown as text. There are no exhibits.

How the topics fit together

Each modeling style answers a different need. Data vault keeps integrated history flexible as sources change, while star and snowflake schemas make business reporting fast and simple.

• Model questions ask which schema fits a requirement, or what a fact, dimension, hub, link or satellite table holds.
• Layer questions ask in which Medallion layer a model normally lives.
• SQL questions show a join between a fact and its dimensions or a key constraint.

How to use these pages

Each topic page has the same shape, so you can skim for what you need.

• What it is: the idea in plain words.
• How it works: the mechanism, with tables and examples.
• What to remember for the exam: the facts to memorize and how questions are phrased.
• Common mistakes: the classic traps.
• Check yourself: three questions to test your understanding.