Star Schemas and Flattened Data Sets for BI in Snowflake

1.Star schema: facts in the middle, dimensions around them

A dimensional model splits data into two kinds of table. Fact tables hold the measurable events, such as sales lines, and grow very large. Dimension tables hold the descriptive context those events are analysed by: customer, item, store, date. In a star schema, each fact table sits in the centre and joins directly to its dimensions. Snowflake's guidance on modeling for BI and AI consumption treats this as the place to begin: start with a simple star schema, then extend it as the use case grows.

Snowflake's own sample data shows the pattern at scale. The TPC-DS benchmark data set, which models a retail supplier's decision support system, is a dimensional model with 7 fact tables and 17 dimensions. In the 100 TB version, the largest fact table, STORE_SALES, holds nearly 300 billion rows. Dimensions are a fraction of that size. Facts and dimensions are linked through surrogate keys, which are system-assigned keys rather than the business identifiers from the source.

Sources12

2.Declare keys so BI tools join correctly

On standard tables Snowflake enforces only NOT NULL and CHECK constraints. Primary keys, foreign keys and unique constraints are recorded but not enforced. Hybrid tables are the exception: their constraints are enforced. That means your load process, not the database, has to keep the star consistent.

You should still declare the keys. They document the design, so the team can see how tables relate. They also do practical work in the consumption layer. Most BI and visualization tools import foreign-key definitions along with the tables and build the join conditions from them, so analysts don't have to guess how to join. Some tools go further and use the constraint information to rewrite queries more efficiently, for example with join elimination. Because the joins come from declared keys, each developer doesn't interpret them differently. You declare a constraint with CREATE TABLE … CONSTRAINT or ALTER TABLE … CONSTRAINT, and GET_DDL returns the DDL with the constraints currently set on the table.

There is one more property to know: RELY. Snowflake's own join elimination is performed only when you set the RELY property on the UNIQUE, PRIMARY KEY or FOREIGN KEY constraints, which tells Snowflake your data complies with them. On standard tables you are responsible for that compliance, and if the constraints are not actually maintained, results can differ between RELY and NORELY. The same property appears on a dimension's primary key (PRIMARY KEY RELY) in the dynamic-table example later in this lesson.

CREATE OR REPLACE TABLE salespeople (
  sp_id INT NOT NULL UNIQUE,
  name VARCHAR DEFAULT NULL,
  region VARCHAR,
  constraint pk_sp_id PRIMARY KEY (sp_id)
);
CREATE OR REPLACE TABLE salesorders (
  order_id INT NOT NULL UNIQUE,
  quantity INT DEFAULT NULL,
  description VARCHAR,
  sp_id INT NOT NULL UNIQUE,
  constraint pk_order_id PRIMARY KEY (order_id),
  constraint fk_sp_id FOREIGN KEY (sp_id)  ?  salespeople(sp_id)
);

Sources34

3.Relationships, bridges and metrics in a semantic layer

Semantic views apply the same idea to AI-driven BI. A semantic view sits on top of your tables and tells consumers such as Cortex Agents how the tables relate and what the business measures mean. Snowflake's modeling guidance is concrete:

- Start with a star schema. Begin with 5–10 tables for a proof of concept, and include only business-relevant columns. - Define every relationship. Cortex Agents won't join two tables unless the semantic view explicitly defines the relationship between them. - Bridge many-to-many relationships. These aren't directly supported. Add a shared dimension (bridge) table, so that two many-to-one relationships simulate the many-to-many relationship. - Define metrics and filters. Metrics are predefined calculations, and filters are reusable WHERE-clause logic. The guidance calls both critical for accuracy and consistency.

metrics:
  - name: total_revenue
    description: "Sum of all order revenue, calculated as unit price × quantity"
    expr: SUM(unit_price * quantity)
  - name: average_order_value
    description: "Average revenue per order"
    expr: SUM(revenue) / COUNT(DISTINCT order_id)

Sources2

4.Modeled schema or flattened data set?

The alternative to a star is a flattened data set: one table where the dimension attributes are already joined onto the facts. Snowflake's dynamic-table documentation builds exactly this. It joins a fact_orders table to a product dimension and produces an enriched table that carries product name, category, price and a computed order total on every row. The same page compares refreshing that pipeline with and without a primary key on the dimension, so even a flattened output still depends on a well-keyed model upstream.

CREATE OR REPLACE DYNAMIC TABLE dt_enriched_with_pk
  TARGET_LAG = DOWNSTREAM
  WAREHOUSE = transform_wh
  REFRESH_MODE = INCREMENTAL
AS
  SELECT
    f.order_id, f.product_id, d.product_name, d.category,
    f.quantity, d.price, f.quantity * d.price AS order_total, f.order_date
  FROM fact_orders f
  JOIN dim_products_with_pk d ON f.product_id = d.product_id;

So which do you serve? The sources give signals in each direction rather than one rule.

Keep the model when consumers need to work out joins for themselves. BI tools build joins from declared foreign keys, semantic views need explicit relationships, and many-to-many cases need a bridge. All of that depends on separate, keyed tables.

Flatten or pre-parse when the consumer can't cope with the raw shape. Most BI tools handle only structured content, so nested data has to be parsed and typed before front-end tools can use it. When you type columns, use proper DATE and TIMESTAMP types: Snowflake stores them more efficiently than VARCHAR, which improves query performance.

Flattening has costs to weigh. Maintenance and update cost: a flattened table must be refreshed when a dimension changes. In the dynamic-table example, rewriting 10% of the dimension rows meant the pipeline with a primary key reprocessed only the 1 million fact rows that referenced them, while the pipeline without one refreshed all 10 million. Load latency: if the time from business event to analytical value is critical, the Data Vault guidance is to avoid flattening nested content in staging and let the dashboard flatten at query time. Query speed: semi-structured content is slower to query than typed columns, which is the reason to pre-parse the essential attributes.

The sources do not compare storage cost between wide tables and star schemas, and they give no single rule for every case. Choose by the BI requirement in front of you.

Sources56

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.Declaring a FOREIGN KEY on a standard Snowflake table stops orphan fact rows from loading.Why is that wrong?

   On standard tables, PK/FK constraints are informational. Only NOT NULL and CHECK are enforced, and full enforcement applies only to hybrid tables.

   Covered in Declare keys so BI tools join correctly

2. 2.A semantic view can model a many-to-many relationship directly between two tables.Why is that wrong?

   Many-to-many isn't directly supported. You add a bridge (shared dimension) table and two many-to-one relationships.

   Covered in Relationships, bridges and metrics in a semantic layer

3. 3.Cortex Agents will infer joins from matching column names in a semantic view.Why is that wrong?

   Agents join tables only through relationships that the semantic view explicitly defines.

   Covered in Relationships, bridges and metrics in a semantic layer