Amazon QuickSight のマルチデータセット関係におけるデータモデリングパターン

In Part 1 of this series, we introduced Amazon Quick Sight Multi-Dataset Relationships and covered the foundational concepts of dimensional modeling, best practices for designing clean data models, and a decision framework for when to use runtime joins versus pre-joined datasets. If you haven’t read Part 1 yet, we recommend starting there.

In this post, we shift from concepts to patterns. For each schema, you’ll find a table structure, use cases, implementation steps, and sample SQL queries. We also cover workarounds for advanced scenarios that require extra modeling steps, and close with a summary of current limitations.

Note: All Multi-Dataset relationships in the current release use inner join. Only rows with matching keys in both datasets appear in query results. Design your data model accordingly.

Supported patterns

The following seven scenarios are natively supported by Quick Sight Multi-Dataset Relationships. Each scenario maps to a common data modeling pattern, with concrete implementation guidance and sample SQL.

Scenario 1: Simple star schema

The most common and recommended pattern. A central fact dataset is related to multiple dimension datasets.

Table

Type

Cardinality

Key Columns

Attributes/Measures

SALES_FACT

Fact

High: Millions to billions of rows

sale_id (PK) customer_id (FK) product_id (FK) time_id (FK) store_id (FK)

quantity revenue cost

CUSTOMER_DIM

Dimension

Medium: Thousands to millions of rows

customer_id (PK)

name, email, city, state, country, segment

PRODUCT_DIM

Dimension

Low: Hundreds to thousands of rows

product_id (PK)

product_name, category, brand, unit_price

TIME_DIM

Dimension

Low

time_id (PK)

date, month, quarter, year, day_of_week

STORE_DIM

Dimension

Low to Medium

store_id (PK)

store_name, region, manager, sqft

Use cases

• Total sales by customer segment and region.

• Monthly revenue trend by product category.

• Top 10 stores by average order value.

Implementation

• Create separate datasets for each fact and dimension table.

• Define relationships via matching keys:

SALES_FACT.CUSTOMER_ID → CUSTOMER_DIM.CUSTOMER_ID
SALES_FACT.PRODUCT_ID → PRODUCT_DIM.PRODUCT_ID
SALES_FACT.TIME_ID → TIME_DIM.TIME_ID
SALES_FACT.STORE_ID → STORE_DIM.STORE_ID

• All joins are single-hop (fact to dimension), with no chaining required.

• Denormalized dimensions support fast GROUP BY operations without extra joins.

Sample queries

Total sales by customer segment and region:

SELECT c.segment, c.region,
    SUM(f.revenue) AS total_revenue,
    COUNT(DISTINCT f.sale_id) AS order_count
FROM sales_fact f
JOIN customer_dim c ON f.customer_id = c.customer_id
GROUP BY c.segment, c.region
ORDER BY total_revenue DESC;

Scenario 2: Snowflake schema

A snowflake schema extends the star by normalizing dimension tables into chains. For example, a Customer dimension might link to a Geography table, which links to a Region table. Each table stays at its own grain.

Dimension tables are normalized into multi-level chains.

Table

Type

Key Columns

Attributes/Measures

SALES_FACT

Fact

sale_id (PK) customer_id (FK) product_id (FK) time_id (FK) store_id (FK)

quantity revenue

CUSTOMER_DIM

Dimension

customer_id (PK) geo_id (FK) segment_id (FK)

name, email

GEOGRAPHY

Sub-Dimension

geo_id (PK)

city, state country

SEGMENT

Sub-Dimension

segment_id (PK)

segment_name

PRODUCT_DIM

Dimension

product_id (PK) category_id (FK) brand_id (FK)

product_name unit_price

CATEGORY

Sub-Dimension

category_id (PK)

category_name

BRAND

Sub-Dimension

brand_id (PK)

brand_name

TIME_DIM

Dimension

time_id (PK) quarter_id (FK)

date day_of_week

QUARTER

Sub-Dimension

quarter_id (PK)

quarter, year

Use cases

• Sales breakdown by geographic hierarchy (country → state → city).

• Product performance by brand and category independently.

Key consideration

The multi-hop join (fact → customer → geography) increases query complexity slightly. Pre-join snowflake chains into a single flat dimension dataset unless the dimension is very large (>1M rows) and storage reduction justifies the added join hop.

Sample query

Sales by geographic hierarchy:

SELECT g.country, g.state, g.city,
    SUM(f.revenue) AS total_revenue
FROM sales_fact f
JOIN customer_dim c ON f.customer_id = c.customer_id
JOIN geography g ON c.geo_id = g.geo_id
GROUP BY g.country, g.state, g.city
ORDER BY total_revenue DESC;

Scenario 3: Galaxy / constellation schema (multi-fact with shared/conformed dimensions)

Multiple fact tables share common (conformed) dimension tables. This supports cross-process analytics. For example, you can compare sales versus returns using shared product and customer dimensions.

Multiple fact tables share common conformed dimensions.

Table

Type

Key Columns

Attributes/Measures

SALES_FACT

Fact

sale_id (PK) product_id (FK) customer_id (FK) promo_id (FK) channel_id (FK)

quantity, revenue, cost

RETURNS_FACT

Fact

return_id (PK) product_id (FK) customer_id (FK) reason_id (FK) status_id (FK)

refund_amount

PRODUCT_DIM

Shared Dim

product_id (PK)

product_name, category brand, unit_price

CUSTOMER_DIM

Shared Dim

customer_id (PK)

name, email city, state

PROMOTION_DIM

Sales-only

promo_id (PK)

promo_name, discount_pct

CHANNEL_DIM

Sales-only

channel_id (PK)

channel_name, channel_type

REASON_DIM

Returns-only

reason_id (PK)

reason_desc, reason_category

STATUS_DIM

Returns-only

status_id (PK)

status_name, is_final

Use cases

• Return rate by product (Total Returns / Total Sales).

• Period-over-period comparison of returns versus sales.

• Cross-process analysis: which promotions drive the most returns?

Key consideration

Conformed dimensions must use identical grain and keys across both fact tables. Querying across facts uses shared dimensions as the “bridge” for the join.

Sample query

Which promotions drive the most returns?

SELECT pr.promo_name, pr.discount_pct,
    COUNT(DISTINCT rf.return_id) AS returns_count,
    SUM(rf.refund_amount) AS total_refunded
FROM sales_fact sf
JOIN promotion_dim pr ON sf.promo_id = pr.promo_id
JOIN returns_fact rf
    ON sf.customer_id = rf.customer_id
    AND sf.product_id = rf.product_id
GROUP BY pr.promo_name, pr.discount_pct
ORDER BY returns_count DESC LIMIT 10;

Scenario 4: Role-playing dimensions

A single dimension table (for example, Date) is referenced multiple times by the same fact table, each time in a different analytical role. In Quick Sight, create three separate datasets all based on the same underlying DATE_DIM source table.

A single date dimension serves multiple analytical roles.

Table

Type

Key Columns

Attributes

ORDERS_FACT

Fact

order_id (PK) order_date_id (FK) ship_date_id (FK) delivery_date_id (FK) customer_id (FK) product_id (FK)

quantity revenue

DATE_DIM

Role-Playing Dim (1 physical table)

date_id (PK)

date month, quarter, year day_of_week is_weekend, is_holiday

CUSTOMER_DIM

Dimension

customer_id (PK)

name, email city, state, segment

PRODUCT_DIM

Dimension

product_id (PK)

product_name category, brand

Use cases

• Seasonal demand: orders placed in December versus items delivered in January.

• Average days between order and shipment (ship lag analysis).

• Delivery performance by month: how many orders were delivered on time?

Implementation

• Create one physical table: DATE_DIM with all date attributes.

• In the fact table, add one FK per role:

ORDERS_FACT.order_date_id → DATE_DIM.date_id (alias: OrderDate)
ORDERS_FACT.ship_date_id → DATE_DIM.date_id (alias: ShipDate)
ORDERS_FACT.delivery_date_id → DATE_DIM.date_id (alias: DeliveryDate)

• In Quick Sight, create three separate datasets for the roles of the date dimension, all based on the same underlying source Date_Dim table.

• Note: In SQL, the engine uses table aliases in JOINs to differentiate each role.

• Do not duplicate the physical table in the underlying data source.

The role mapping is defined in the following table.

FK in Fact Table

Role / Alias

Business Question

order_date_id

OrderDate

When was the order placed?

ship_date_id

ShipDate

When was it shipped?

delivery_date_id

DeliveryDate

When was it delivered?

Sample query

Average ship lag by product category:

-- Days between order and shipment by category
SELECT p.category,
    AVG(sd.date - od.date) AS avg_ship_days,
    MAX(sd.date - od.date) AS max_ship_days
FROM orders_fact f
JOIN date_dim od ON f.order_date_id = od.date_id
JOIN date_dim sd ON f.ship_date_id = sd.date_id
JOIN product_dim p ON f.product_id = p.product_id
GROUP BY p.category
ORDER BY avg_ship_days DESC;

Scenario 5: Multi-fact with different grain

Two or more fact tables at different levels of detail (grain) share common dimension tables. Quick Sight Multi-Dataset runtime joins automatically aggregate the finer-grained fact up to the coarser grain before joining. This eliminates the need for manual pre-aggregation in extract, transform, and load (ETL) pipelines.

Daily sales and monthly forecasts share dimensions at different grain levels.

Table

Type / Grain

Key Columns

Attributes/Measures

DAILY_SALES_FACT

Fact Grain: 1 row per store/product/day

sale_id (PK) store_id (FK) product_id (FK) sale_date

quantity revenue

MONTHLY_FORECAST_FACT

Fact Grain: 1 row per store/product/month

forecast_id (PK) store_id (FK) product_id (FK) forecast_month

forecast_revenue forecast_quantity

STORE_DIM

Shared Dim

store_id (PK)

store_name, region manager, sqft

PRODUCT_DIM

Shared Dim

product_id (PK)

product_name category, brand unit_price

Sample query

Actual versus forecast by store (monthly):

-- Aggregate daily sales to monthly, then compare with forecast
WITH monthly_actuals AS (
    SELECT store_id, product_id,
        DATE_TRUNC('month', sale_date) AS month,
        SUM(revenue) AS actual_revenue
    FROM daily_sales_fact
    GROUP BY store_id, product_id, DATE_TRUNC('month', sale_date)
)
SELECT s.store_name, s.region,
    a.month,
    a.actual_revenue,
    f.forecast_revenue,
    a.actual_revenue - f.forecast_revenue AS variance,
    ROUND(100.0 * (a.actual_revenue - f.forecast_revenue)
        / NULLIF(f.forecast_revenue, 0), 1) AS variance_pct
FROM monthly_actuals a
JOIN monthly_forecast_fact f
    ON a.store_id = f.store_id
    AND a.product_id = f.product_id
    AND a.month = f.forecast_month
JOIN store_dim s ON a.store_id = s.store_id
ORDER BY ABS(variance_pct) DESC;

Scenario 6: Independent refresh schedules

The preceding scenarios demonstrate how different schema patterns map to Multi-Dataset Relationships. The next two scenarios shift focus from data modeling structure to operational capabilities of the multi-dataset architecture: independent refresh schedules and runtime row-level security.

Because each dataset in a Multi-Dataset Topic is an independent entity, datasets can be refreshed on separate schedules tailored to their data volatility. High-velocity fact tables can refresh hourly, and slowly changing dimensions can refresh daily or weekly.

Dataset Type

Suggested Cadence

Example

Transaction facts (orders, clicks)

Every hour

ORDERS_FACT, PAGEVIEWS_FACT

Aggregated/summary facts

Daily

DAILY_SALES_SUMMARY

Dimension tables

Weekly or on-change

CUSTOMER_DIM, PRODUCT_DIM

Reference/lookup tables

Monthly or ad-hoc

REGION_DIM, CATEGORY_DIM

• Configure each dataset with its own SPICE refresh schedule independently.

• Use incremental refresh on fact tables where supported to minimize SPICE costs.

Monitor SP