SQL Academy · Lekcja

Schematy gwiazdy i płatka śniegu

Modeluj dane na potrzeby szybkiej analityki

Schematy gwiazdy i płatka śniegu to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 3 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.

Czym jest schemat hurtowni danych?

W transakcyjnej bazie danych (OLTP) normalizują Państwo dane, aby uniknąć redundancji. W hurtowni danych często celowo stosuje się denormalizację — wymieniając większe zużycie przestrzeni na szybkość zapytań. Dwa klasyczne wzorce organizowania tabel hurtowni to schemat gwiazdy i schemat płatka śniegu.

Oba opierają się na centralnej tabeli faktów otoczonej przez tabele wymiarów. Różnią się stopniem normalizacji tych wymiarów.

Tabele faktów i tabele wymiarów

Tabela faktów przechowuje mierzalne zdarzenia — sprzedaż, kliknięcia i wysyłki. Jest długa (ma wiele wierszy) i zawiera miary liczbowe oraz klucze obce wskazujące wymiary.

Tabela wymiarów opisuje kontekst każdego zdarzenia: kto, co, kiedy i gdzie. Wymiary są krótsze (mają mniej wierszy), ale zawierają więcej opisowych kolumn.

CREATE TABLE fact_sales (
  sale_id      SERIAL PRIMARY KEY,
  date_key     INT NOT NULL,
  product_key  INT NOT NULL,
  customer_key INT NOT NULL,
  store_key    INT NOT NULL,
  quantity     INT NOT NULL,
  revenue      NUMERIC(12, 2) NOT NULL
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category     VARCHAR(100),
  brand        VARCHAR(100),
  unit_price   NUMERIC(10, 2)
);

Schemat gwiazdy

W schemacie gwiazdy każda tabela wymiarów łączy się bezpośrednio z tabelą faktów. Po narysowaniu relacji na papierze całość przypomina gwiazdę — tabela faktów znajduje się w centrum, a wymiary tworzą jej ramiona.

Tabele wymiarów są w pełni zdenormalizowane: wszystkie atrybuty opisowe znajdują się w jednej tabeli, nawet jeśli niektóre z nich powtarzają się w wielu wierszach.

-- Star schema: all product info in one flat dimension table
CREATE TABLE dim_product (
  product_key    SERIAL PRIMARY KEY,
  product_name   VARCHAR(200),
  category_name  VARCHAR(100),   -- denormalized
  subcategory    VARCHAR(100),   -- denormalized
  brand_name     VARCHAR(100),   -- denormalized
  brand_country  VARCHAR(100),   -- denormalized
  unit_price     NUMERIC(10, 2)
);

CREATE TABLE dim_date (
  date_key   INT PRIMARY KEY,   -- e.g. 20240315
  full_date  DATE,
  year       INT,
  quarter    INT,
  month      INT,
  month_name VARCHAR(20),
  week       INT,
  day_of_week VARCHAR(10)
);

Zapytanie w schemacie gwiazdy

Płaskie tabele wymiarów upraszczają zapytania. Tabelę faktów łączy się z jednym lub większą liczbą wymiarów, a następnie wykonuje agregację. Nie ma dodatkowych złączeń przez łańcuchy znormalizowanych tabel.

Dlatego schematy gwiazdy zapewniają szybkie zapytania analityczne — graf złączeń jest płytki.

SELECT
  d.year,
  d.quarter,
  p.category_name,
  SUM(f.revenue)   AS total_revenue,
  SUM(f.quantity)  AS units_sold
FROM fact_sales f
JOIN dim_date    d ON d.date_key    = f.date_key
JOIN dim_product p ON p.product_key = f.product_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category_name
ORDER BY d.quarter, total_revenue DESC;

Schemat płatka śniegu

Schemat płatka śniegu jeszcze bardziej normalizuje tabele wymiarów, dzieląc je na podwymiary. Na przykład zamiast przechowywać category_name i brand_name w tabeli dim_product, tworzy się osobne tabele dim_category i dim_brand.

Powstały diagram przypomina płatek śniegu — rozgałęzione ramiona powiązanych tabel.

-- Snowflake schema: product dimension is normalized
CREATE TABLE dim_brand (
  brand_key     SERIAL PRIMARY KEY,
  brand_name    VARCHAR(100),
  brand_country VARCHAR(100)
);

CREATE TABLE dim_category (
  category_key   SERIAL PRIMARY KEY,
  category_name  VARCHAR(100),
  subcategory    VARCHAR(100)
);

CREATE TABLE dim_product (
  product_key  SERIAL PRIMARY KEY,
  product_name VARCHAR(200),
  category_key INT REFERENCES dim_category(category_key),
  brand_key    INT REFERENCES dim_brand(brand_key),
  unit_price   NUMERIC(10, 2)
);

Zapytanie w schemacie płatka śniegu

Wykonywanie zapytań w schemacie płatka śniegu wymaga większej liczby złączeń, aby ponownie połączyć dane wymiarów rozdzielone między tabele. Optymalizator zapytań musi przechodzić przez dodatkowe poziomy, co może zwiększać opóźnienie w porównaniu ze schematem gwiazdy.

Znormalizowane wymiary są jednak mniejsze i spójne — aktualizacja nazwy marki w jednym wierszu tabeli dim_brand automatycznie obowiązuje wszędzie.

SELECT
  d.year,
  c.category_name,
  b.brand_name,
  SUM(f.revenue) AS total_revenue
FROM fact_sales    f
JOIN dim_date      d ON d.date_key    = f.date_key
JOIN dim_product   p ON p.product_key = f.product_key
JOIN dim_category  c ON c.category_key = p.category_key
JOIN dim_brand     b ON b.brand_key    = p.brand_key
WHERE d.year = 2024
GROUP BY d.year, c.category_name, b.brand_name
ORDER BY total_revenue DESC;

Klucze zastępcze a klucze naturalne

Tabele wymiarów zazwyczaj używają klucza zastępczego — liczby całkowitej generowanej przez hurtownię danych (np. SERIAL) — zamiast klucza naturalnego pochodzącego z systemu źródłowego.

Klucze zastępcze pozostają stabilne nawet po zmianach w źródle, zajmują mało miejsca w dużych tabelach faktów i obsługują wolno zmieniające się wymiary, w przypadku których należy śledzić historię.

-- Surrogate key (product_key) vs natural key (sku)
INSERT INTO dim_product (product_name, category_key, brand_key, unit_price)
VALUES ('Wireless Headphones', 3, 7, 89.99);
-- product_key is assigned by SERIAL -- the natural key (SKU) lives elsewhere

-- Natural key would be:
-- INSERT INTO dim_product (sku, product_name, ...)
-- VALUES ('WH-1000XM5', 'Wireless Headphones', ...);
-- Risky: SKU can be reused or reassigned by the source system

Wymiar daty

Wymiar daty jest szczególny — niemal zawsze występuje i zazwyczaj jest wstępnie wypełniony datami z wielu lat. Przechowywanie atrybutów pochodnych (roku, kwartału, nazwy miesiąca, okresu obrachunkowego, flagi święta) w tabeli wymiaru pozwala uniknąć ich ponownego obliczania podczas wykonywania zapytania.

-- Populate dim_date for one year using generate_series
INSERT INTO dim_date (date_key, full_date, year, quarter, month, month_name, week, day_of_week)
SELECT
  TO_CHAR(d, 'YYYYMMDD')::INT  AS date_key,
  d                             AS full_date,
  EXTRACT(YEAR    FROM d)::INT  AS year,
  EXTRACT(QUARTER FROM d)::INT  AS quarter,
  EXTRACT(MONTH   FROM d)::INT  AS month,
  TO_CHAR(d, 'Month')           AS month_name,
  EXTRACT(WEEK    FROM d)::INT  AS week,
  TO_CHAR(d, 'Day')             AS day_of_week
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

Wolno zmieniające się wymiary (SCD Type 2)

Co się dzieje, gdy klient zmienia miasto albo produkt zmienia kategorię? Należy śledzić historię. SCD Type 2 wstawia nowy wiersz wymiaru po każdej zmianie, zamykając poprzedni wiersz datą końcową. Wiersz tabeli faktów nadal wskazuje stary klucz wymiaru, co pozwala zachować poprawność historyczną.

-- SCD Type 2 customer dimension
CREATE TABLE dim_customer (
  customer_key  SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  customer_name VARCHAR(200),
  city          VARCHAR(100),
  country       VARCHAR(100),
  valid_from    DATE NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN DEFAULT TRUE
);

-- When a customer moves, close old row and insert new one:
UPDATE dim_customer
   SET valid_to = CURRENT_DATE - 1, is_current = FALSE
 WHERE customer_id = 42 AND is_current = TRUE;

INSERT INTO dim_customer (customer_id, customer_name, city, country, valid_from, is_current)
VALUES (42, 'Alice Muller', 'Berlin', 'Germany', CURRENT_DATE, TRUE);

Schemat gwiazdy a schemat płatka śniegu — kompromisy

Żaden z tych schematów nie jest uniwersalnie lepszy. Wybór zależy od Państwa priorytetów:

Schemat gwiazdy — mniej złączeń, szybsze zapytania, prostszy ETL, większy koszt przechowywania. Najlepszy w narzędziach analitycznych, w których dominuje odczyt (Tableau, Power BI).
Schemat płatka śniegu — znormalizowane wymiary, mniej nadmiarowości, łatwiejsze aktualizowanie wymiarów, ale więcej złączeń. Lepszy, gdy wymiary są duże lub współdzielone przez wiele tabel faktów.

-- Checking how much storage the denormalized category column costs
-- in a large dim_product (star schema) vs a separate dim_category (snowflake)
SELECT
  COUNT(*)                               AS total_products,
  COUNT(DISTINCT category_name)          AS unique_categories,
  pg_size_pretty(
    SUM(pg_column_size(category_name))
  )                                      AS category_storage
FROM dim_product;

Schemat galaktyki (konstelacja faktów)

Gdy hurtownia danych zawiera wiele tabel faktów współdzielących tabele wymiarów, powstały układ nazywa się schematem galaktyki (lub konstelacją faktów). Na przykład hurtownia danych dla handlu detalicznego może zawierać osobne tabele faktów sprzedaży i zwrotów, z których obie odwołują się do tych samych tabel dim_product i dim_date.

Współdzielone wymiary zapewniają spójne filtrowanie i ułatwiają bezpośrednie porównywanie danych z różnych tabel faktów.

CREATE TABLE fact_returns (
  return_id     SERIAL PRIMARY KEY,
  date_key      INT NOT NULL REFERENCES dim_date(date_key),
  product_key   INT NOT NULL REFERENCES dim_product(product_key),
  customer_key  INT NOT NULL,
  quantity      INT NOT NULL,
  refund_amount NUMERIC(12, 2) NOT NULL
);

-- Cross-fact query: net revenue = sales - refunds
SELECT
  d.year,
  d.month,
  SUM(s.revenue)       AS gross_revenue,
  SUM(r.refund_amount) AS total_refunds,
  SUM(s.revenue) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM dim_date d
LEFT JOIN fact_sales   s ON s.date_key = d.date_key
LEFT JOIN fact_returns r ON r.date_key = d.date_key
WHERE d.year = 2024
GROUP BY d.year, d.month
ORDER BY d.month;

Schemat gwiazdy a schemat płatka śniegu

Sprawdź swoją wiedzę na temat schematów gwiazdy i płatka śniegu.

Podsumowanie lekcji

W tej lekcji poznali Państwo dwa podstawowe wzorce projektowe stosowane przy projektowaniu hurtowni danych:

Schemat gwiazdy — centralna tabela faktów otoczona płaskimi, zdenormalizowanymi tabelami wymiarów. Mniej złączeń, szybsze zapytania i nieco większe wymagania dotyczące miejsca.
Schemat płatka śniegu — tabele wymiarów są dodatkowo normalizowane do podwymiarów. Mniej nadmiarowości i łatwiejsze aktualizacje, ale konieczne jest wykonanie większej liczby złączeń.
Tabele faktów przechowują mierzalne zdarzenia, a tabele wymiarów dostarczają kontekstu (kto, co, kiedy i gdzie).
Klucze zastępcze chronią poprawność historyczną i oddzielają hurtownię danych od zmian w systemie źródłowym.
SCD Type 2 śledzi historię wymiarów, dodając nowe wiersze z datami obowiązywania zamiast nadpisywać stare.
Gdy wiele tabel faktów współdzieli wymiary, powstaje schemat galaktyki (konstelacja faktów).

Wybierz schemat gwiazdy, gdy liczy się prostota i szybkość; wybierz schemat płatka śniegu, gdy wymiary są duże, często aktualizowane lub współdzielone przez wiele tabel faktów.

Często zadawane pytania

Czy lekcja „Schematy gwiazdy i płatka śniegu” jest bezpłatna?

Tak — pełny tekst „Schematy gwiazdy i płatka śniegu” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.

Co nauczysz się w „Schematy gwiazdy i płatka śniegu”?

Modeluj dane na potrzeby szybkiej analityki Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.

Czy potrzebuję doświadczenia, aby zacząć SQL Academy?

Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 3 z 4.

Ile czasu zajmuje lekcja „Schematy gwiazdy i płatka śniegu”?

Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.

Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?

Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.

Wszystkie lekcje w tym kursie

← Powrót do SQL Academy