Data Warehouse Schema Design

What this prompt does

This prompt makes the AI design a dimensional data warehouse schema starting from the grain, which is the decision everything else hangs on. You set [business_context], [warehouse_platform], the [business_process] to model, and the [grain_definition], and the model builds a fact table at that exact grain with your [measures], degenerate dimensions, and foreign keys, partitioned by [partition_column] and clustered by [cluster_columns].

The structure works because it makes you commit to grain and slowly-changing-dimension handling before writing any DDL — getting the grain wrong is the mistake that forces a rebuild later. It generates a dim_date with fiscal support for [fiscal_year_start], an SCD Type 2 dim_customer, your [additional_dimensions], a bridge table for [many_to_many_relationship], and an accumulating snapshot for [process_milestones]. It then emits platform-tuned DDL, sample analytical queries including [sample_query_requirement], and a data dictionary. Every measure and column is documented up front, which heads off downstream arguments about what a number means.

When to use it

• You're modeling a new warehouse and want the grain nailed down before anyone writes SQL.
• You need fact and dimension tables with SCD Type 2 history on the dimensions that change.
• You have a genuine many-to-many relationship that needs a clean bridge table.
• You want to track time between process milestones with an accumulating snapshot.
• You need DDL tuned for a specific platform's compression, distribution, and sort keys.
• You want a data dictionary so business definitions are agreed before reporting starts.

Example output

Expect a schema design: DDL for the fct_ fact table at the stated grain with partitioning and clustering, dimension tables including a fiscal-aware dim_date and an SCD2 dim_customer, a bridge table, an accumulating snapshot fact, and platform-specific optimizations. It closes with sample queries (year-over-year, cohort, and your custom [sample_query_requirement]) and a data dictionary documenting each table and column.

Pro tips

• Spend the most effort on [grain_definition] — state it as one row per what, per when. A fuzzy grain is what later forces a full rebuild.
• List [measures] precisely and document each; ambiguous measures (is MRR net or gross?) cause the downstream arguments this prompt is meant to prevent.
• Set [fiscal_year_start] correctly if the business doesn't run on a calendar year — a wrong fiscal boundary quietly breaks every period comparison.
• Match [partition_column] and [cluster_columns] to how the table is queried; partitioning on a column nobody filters on adds cost without speeding queries.
• Only add a bridge table when the relationship is truly many-to-many; forcing one onto a one-to-many relationship over-complicates joins.
• Treat the SCD2 dim_customer effective dating as load-bearing — confirm the generated DDL has effective_from, effective_to, and is_current before building facts against it.