Delivering data in a relational star schema

We had learned about star schema in Chapter 4, Designing the Serving Layer. We will take the same example here and show how to implement a star schema in Synapse SQL and deliver data from it.

Star schemas have two types of tables, fact tables and dimensional tables. Fact tables are usually much higher in volume than the dimension tables and hence would benefit from using a hash distribution with clustered columnstore indexing. On the other hand, dimension tables are smaller and can benefit from using replicated tables.

Important Note

Synapse dedicated SQL pools didn't support foreign key constraints at the time of writing this book. Hence, the responsibility of maintaining data integrity falls on the applications.

Let's consider the same Imaginary Airport Cabs (IAC) cab rides example for our star schema from Chapter 4, Designing the Serving Layer. We had the following tables in that design:

• FactTrips
• DimDriver...