Google Cloud Dataform w zarządzaniu danymi e-commerce

Niniejszy artykuł jest przeznaczony dla analityków i inżynierów danych pracujących z BigQuery, którzy chcą uporządkować chaos w transformacjach SQL i wprowadzić do swojej pracy praktyki znane z rozwoju oprogramowania. Jeśli piszesz skrypty SQL ręcznie, nie masz pojęcia, kto i kiedy ostatnio zmienił krytyczną tabelę, albo każda zmiana w produkcji wywołuje u ciebie lekki zawał serca - Dataform może być rozwiązaniem twoich problemów.

Pokażę, jak wykorzystać Dataform w kontekście e-commerce, gdzie mamy do czynienia z danymi z wielu źródeł, które trzeba integrować, transformować i testować, zanim trafią do raportów czy dashboardów. Będą konkretne przykłady kodu i rzeczywiste przypadki użycia, a nie teoretyczne rozważania o tym, jak fajnie jest mieć kontrolę wersji.

Czym właściwie jest Dataform

Dataform to w pełni zarządzana usługa w Google Cloud Platform, która pozwala zarządzać transformacjami danych w BigQuery używając SQL z dodatkowymi funkcjami. W praktyce oznacza to, że piszesz pliki z rozszerzeniem .sqlx, które zawierają zarówno kod SQL, jak i konfigurację określającą, jak ten kod ma być wykonany.

Kluczowa różnica w porównaniu do tradycyjnych skryptów SQL polega na tym, że Dataform automatycznie rozwiązuje zależności między tabelami. Jeśli tabela A używa danych z tabeli B, Dataform sam wie, że musi najpierw wykonać transformację dla B, a dopiero potem dla A. Brzmi banalnie, ale w praktyce eliminuje całą klasę błędów związanych z niepoprawną kolejnością wykonywania skryptów.

Drugi istotny aspekt to to, że kod Dataform to zwykłe pliki tekstowe w repozytorium Git. Oznacza to pełną historię zmian, możliwość code review przed wrzuceniem na produkcję i łatwe cofanie zmian, jeśli coś pójdzie nie tak. W moim doświadczeniu to właśnie te aspekty są największą wartością Dataform, nawet bardziej niż sama automatyzacja transformacji.

Podstawowa konfiguracja projektu

Zanim zaczniemy pisać kod, musimy skonfigurować projekt. W katalogu głównego repozytorium tworzymy plik dataform.json, który wygląda mniej więcej tak:

{
  "defaultSchema": "analytics",
  "assertionSchema": "dataform_assertions",
  "warehouse": "bigquery",
  "defaultDatabase": "moj-projekt-ecommerce",
  "defaultLocation": "EU"
}

Ten plik mówi Dataform, gdzie mają trafiać nasze tabele i gdzie znajdują się źródła danych. Parametr defaultSchema określa domyślny dataset w BigQuery, w którym będą tworzone nasze transformacje. W kontekście e-commerce dobrą praktyką jest rozdzielenie danych na warstwy, więc możesz mieć schematy takie jak staging, intermediate i marts. Ja najczęściej zaczynam od jednego schematu analytics i dopiero z czasem wprowadzam bardziej złożoną strukturę, gdy projekt rośnie.

Struktura folderów w projekcie Dataform wygląda zwykle tak, że masz główny folder definitions, w którym umieszczasz wszystkie pliki .sqlx z transformacjami. Możesz organizować je w podfoldery według warstw danych lub obszarów biznesowych. Dla e-commerce sensowne jest na przykład mieć foldery sales, inventory, customers i marketing.

Integracja danych sprzedażowych - praktyczny przykład

W typowym serwisie e-commerce masz dane rozproszone po kilku systemach. Transakcje są w jednej tabeli, produkty w drugiej, klienci w trzeciej. Często pochodzą one z różnych źródeł - może to być PostgreSQL z aplikacji, tabele załadowane przez Fivetran z Shopify, albo surowe eventy z Google Analytics 4 zapisywane do BigQuery.

Stwórzmy transformację, która integruje te dane w jedną tabelę analityczną. Tworzymy plik definitions/sales_summary.sqlx:

config {
  type: "table",
  schema: "analytics",
  description: "Tabela analityczna łącząca dane o transakcjach z informacjami o produktach i klientach. Podstawa dla większości raportów sprzedażowych.",
  columns: {
    transaction_id: "Unikalny identyfikator transakcji z systemu kasowego",
    product_id: "ID produktu",
    product_name: "Nazwa produktu",
    product_category: "Kategoria produktu według klasyfikacji marketingowej",
    quantity: "Liczba sztuk w transakcji",
    unit_price: "Cena jednostkowa w PLN bez VAT",
    total_value: "Całkowita wartość pozycji (quantity * unit_price)",
    transaction_date: "Timestamp transakcji w UTC",
    customer_id: "ID klienta, NULL dla gości",
    customer_segment: "Segment klienta (VIP, Regular, New, Guest)"
  },
  bigquery: {
    partitionBy: "DATE(transaction_date)",
    clusterBy: ["product_category", "customer_segment"]
  }
}

SELECT
  s.transaction_id,
  s.product_id,
  p.product_name,
  p.category AS product_category,
  s.quantity,
  s.unit_price,
  s.quantity * s.unit_price AS total_value,
  s.transaction_date,
  s.customer_id,
  COALESCE(c.segment, 'Guest') AS customer_segment
FROM
  ${ref("raw_sales")} s
LEFT JOIN
  ${ref("raw_products")} p ON s.product_id = p.product_id
LEFT JOIN
  ${ref("raw_customers")} c ON s.customer_id = c.customer_id
WHERE
  s.transaction_date >= '2024-01-01'
  AND s.is_test_transaction = FALSE

Kilka rzeczy wymaga komentarza, bo robię tu kilka świadomych wyborów projektowych. Po pierwsze, używam typu table zamiast view, ponieważ ta tabela będzie często odpytywana przez dashboardy i raporty. Materialization jako tabela oznacza, że zapytania będą szybsze, ale zajmie to miejsce w BigQuery. To trade-off, który musisz świadomie zrobić.

Funkcja ref() jest sercem Dataform. Zamiast hardcodować pełną nazwę tabeli w stylu projekt.dataset.tabela, używasz ref("nazwa_tabeli"). Dataform automatycznie rozwiązuje tę referencję i wie, że ta transformacja zależy od tabel raw_sales, raw_products i raw_customers. Jeśli któraś z nich się zmieni, Dataform wie, że musi przebudować również sales_summary.

Konfiguracja partitionBy i clusterBy to optymalizacje specyficzne dla BigQuery, które w praktyce mogą zmniejszyć koszty zapytań nawet dziesięciokrotnie. Partycjonowanie po dacie transakcji oznacza, że zapytania zawierające filtr po dacie będą skanować tylko odpowiednie partycje, nie całą tabelę. Clustering po kategoriach produktu i segmentach klienta dodatkowo przyspiesza zapytania, które filtrują lub grupują po tych kolumnach.

W praktyce zauważyłem, że warto dodać warunek WHERE wykluczający bardzo stare dane (tu ustawiam na 2024) i transakcje testowe. To redukuje rozmiar tabeli i przyspiesza przetwarzanie. Jeśli potrzebujesz danych historycznych, lepiej jest mieć osobną tabelę archiwizacyjną.

Transformacje przyrostowe dla dużych wolumenów

Problem pojawia się, gdy masz miliony transakcji dziennie. Przebudowywanie całej tabeli sales_summary od zera każdego dnia staje się kosztowne i wolne. Tutaj wchodzą transformacje przyrostowe, które są moim zdaniem jedną z najlepszych funkcji Dataform.

Zmieniamy typ z table na incremental i dodajemy logikę, która przetwarza tylko nowe dane:

config {
  type: "incremental",
  schema: "analytics",
  uniqueKey: ["transaction_id"],
  bigquery: {
    partitionBy: "DATE(transaction_date)",
    clusterBy: ["product_category", "customer_segment"],
    updatePartitionFilter: "transaction_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)"
  }
}

SELECT
  s.transaction_id,
  s.product_id,
  p.product_name,
  p.category AS product_category,
  s.quantity,
  s.unit_price,
  s.quantity * s.unit_price AS total_value,
  s.transaction_date,
  s.customer_id,
  COALESCE(c.segment, 'Guest') AS customer_segment
FROM
  ${ref("raw_sales")} s
LEFT JOIN
  ${ref("raw_products")} p ON s.product_id = p.product_id
LEFT JOIN
  ${ref("raw_customers")} c ON s.customer_id = c.customer_id
WHERE
  s.transaction_date >= '2024-01-01'
  AND s.is_test_transaction = FALSE

  -- Ten warunek dodaje się tylko przy aktualizacji przyrostowej
  ${when(incremental(), `
    AND s.transaction_date > (
      SELECT MAX(transaction_date)
      FROM ${self()}
    )
  `)}

Klucz uniqueKey mówi Dataform, jak identyfikować duplikaty. Jeśli transakcja o danym transaction_id już istnieje w tabeli, zostanie zaktualizowana zamiast dodana ponownie. To jest istotne, bo w e-commerce często mamy późne aktualizacje transakcji, na przykład gdy klient zwraca towar albo płatność zostaje odrzucona.

Parametr updatePartitionFilter to optymalizacja specyficzna dla BigQuery, która mówi, żeby aktualizować tylko ostatnie siedem dni danych. W praktyce oznacza to, że jeśli masz transakcję sprzed trzech miesięcy, która się zmieniła, nie zostanie zaktualizowana. To świadomy wybór - w większości przypadków e-commerce interesują nas tylko niedawne zmiany, a to ustawienie dramatycznie redukuje koszty.

Funkcja when(incremental(), ...) dodaje dodatkowy warunek WHERE tylko podczas aktualizacji przyrostowej. Przy pierwszym uruchomieniu, gdy tabela jeszcze nie istnieje, ten warunek jest pomijany i przetwarzamy wszystkie dane. Przy kolejnych uruchomieniach przetwarzamy tylko transakcje nowsze niż najnowsza transakcja już obecna w tabeli.

Monitorowanie stanów magazynowych z logiką biznesową

Przejdźmy do bardziej złożonego przypadku. W e-commerce kluczowe jest monitorowanie stanów magazynowych, ale nie chcemy prostych alertów "produkt ma mało sztuk". Chcemy inteligentnego systemu, który uwzględnia kategorie produktów, sezonowość i historię sprzedaży.

config {
  type: "incremental",
  schema: "analytics",
  description: "System alertów magazynowych z dynamicznymi progami zależnymi od kategorii i historii sprzedaży",
  uniqueKey: ["product_id", "check_timestamp"]
}

pre_operations {
  -- Czyścimy stare alerty przed dodaniem nowych
  DELETE FROM ${self()}
  WHERE DATE(check_timestamp) < DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
}

WITH daily_sales AS (
  -- Obliczamy średnią dzienną sprzedaż dla każdego produktu z ostatnich 30 dni
  SELECT
    product_id,
    AVG(daily_quantity) AS avg_daily_sales
  FROM (
    SELECT
      product_id,
      DATE(transaction_date) AS sale_date,
      SUM(quantity) AS daily_quantity
    FROM ${ref("sales_summary")}
    WHERE transaction_date >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
    GROUP BY product_id, DATE(transaction_date)
  )
  GROUP BY product_id
),

dynamic_thresholds AS (
  -- Ustalamy dynamiczne progi alertowe
  SELECT
    p.product_id,
    p.product_name,
    p.category,
    ds.avg_daily_sales,
    CASE
      WHEN p.category IN ('Bestsellery', 'Nowości') THEN ds.avg_daily_sales * 7
      WHEN p.category = 'Sezonowe' THEN ds.avg_daily_sales * 14
      WHEN p.is_slow_mover THEN ds.avg_daily_sales * 3
      ELSE ds.avg_daily_sales * 5
    END AS alert_threshold
  FROM ${ref("raw_products")} p
  LEFT JOIN daily_sales ds ON p.product_id = ds.product_id
)

SELECT
  dt.product_id,
  dt.product_name,
  dt.category,
  i.quantity AS current_quantity,
  dt.avg_daily_sales,
  dt.alert_threshold,
  ROUND(i.quantity / NULLIF(dt.avg_daily_sales, 0), 1) AS days_of_stock,
  i.last_updated AS inventory_updated_at,
  CURRENT_TIMESTAMP() AS check_timestamp,
  CASE
    WHEN i.quantity = 0 THEN 'CRITICAL'
    WHEN i.quantity < dt.avg_daily_sales * 2 THEN 'HIGH'
    WHEN i.quantity < dt.alert_threshold THEN 'MEDIUM'
    ELSE 'LOW'
  END AS severity,
  CASE
    WHEN i.quantity = 0 THEN 'Produkt niedostępny - natychmiastowe działanie wymagane'
    WHEN i.quantity < dt.avg_daily_sales * 2 THEN 'Zapas wystarczy na mniej niż 2 dni sprzedaży'
    WHEN i.quantity < dt.alert_threshold THEN 'Zapas poniżej progu - rozważ uzupełnienie'
    ELSE 'Zapas w normie'
  END AS alert_message
FROM dynamic_thresholds dt
JOIN ${ref("raw_inventory")} i ON dt.product_id = i.product_id
WHERE i.quantity < dt.alert_threshold

${when(incremental(), `
  AND i.last_updated > (SELECT MAX(inventory_updated_at) FROM ${self()})
`)}

Ten przykład pokazuje kilka zaawansowanych technik. Po pierwsze, używam Common Table Expressions do strukturyzowania logiki. CTE daily_sales oblicza średnią dzienną sprzedaż dla każdego produktu, co daje nam znacznie lepszy kontekst niż sztywne progi typu "alert przy 10 sztukach".

CTE dynamic_thresholds implementuje logikę biznesową, która różnicuje progi w zależności od kategorii produktu. Dla bestsellerów chcemy mieć siedmiodniowy zapas, dla produktów sezonowych czternastodniowy, a dla wolno rotujących wystarczy trzydniowy. To odzwierciedla rzeczywiste potrzeby biznesowe i eliminuje fałszywe alarmy.

Kolumna days_of_stock to metryka, którą cenią zespoły operacyjne - mówi wprost, na ile dni wystarczy obecny zapas przy obecnym tempie sprzedaży. To jest znacznie bardziej użyteczna informacja niż sama liczba sztuk.

Sekcja pre_operations automatycznie usuwa stare alerty przed dodaniem nowych. W praktyce oznacza to, że tabela nie rośnie w nieskończoność, a jednocześnie mamy historię alertów z ostatnich 90 dni, co przydaje się do analizy wzorców problemów z dostępnością.

Testowanie jakości danych - assertions

W mojej praktyce największą wartością Dataform są assertions, czyli automatyczne testy jakości danych. W e-commerce błędne dane mogą kosztować firmę setki tysięcy złotych - od źle naliczonych rabatów po błędne raporty dla zarządu. Assertions pozwalają wychwycić problemy, zanim dotrą do użytkowników końcowych.

Tworzymy folder definitions/assertions i umieszczamy w nim testy. Zacznijmy od podstawowych testów dla tabeli sales_summary:

-- definitions/assertions/sales_unique_transactions.sqlx
config {
  type: "assertion",
  description: "Sprawdzenie unikalności transaction_id - duplikaty wskazują na problem w ETL",
  tags: ["critical", "sales"]
}

SELECT
  transaction_id,
  COUNT(*) AS occurrences,
  STRING_AGG(DISTINCT CAST(transaction_date AS STRING), ', ') AS dates_found
FROM ${ref("sales_summary")}
GROUP BY transaction_id
HAVING COUNT(*) > 1

Ten test zwróci wiersze tylko wtedy, gdy znajdzie duplikaty transaction_id. Jeśli test zwróci jakiekolwiek wiersze, Dataform oznacza go jako failed i może zatrzymać dalsze wykonanie workflow. Dodaję kolumnę dates_found, żeby łatwiej debugować problem - często duplikaty powstają, bo ta sama transakcja trafia do systemu z różnymi datami.

Kolejny test sprawdza poprawność wartości finansowych:

-- definitions/assertions/sales_valid_amounts.sqlx
config {
  type: "assertion",
  description: "Walidacja kwot - ceny muszą być dodatnie, wartości muszą się zgadzać",
  tags: ["critical", "sales", "financial"]
}

SELECT
  transaction_id,
  product_name,
  quantity,
  unit_price,
  total_value,
  quantity * unit_price AS calculated_value,
  ABS(total_value - (quantity * unit_price)) AS difference
FROM ${ref("sales_summary")}
WHERE
  unit_price <= 0
  OR unit_price IS NULL
  OR total_value <= 0
  OR total_value IS NULL
  OR quantity <= 0
  OR ABS(total_value - (quantity * unit_price)) > 0.01

Ten test łapie kilka różnych problemów jednocześnie. Sprawdza, czy ceny są dodatnie i nie-null, czy wartość totalna jest poprawnie obliczona (z tolerancją 1 grosza na zaokrąglenia), i czy ilość jest sensowna. W praktyce ten test kilkakrotnie uratował mnie przed wrzuceniem do produkcji danych z błędami w obliczeniach.

Dodajmy test biznesowy, który sprawdza, czy dane są świeże:

-- definitions/assertions/sales_data_freshness.sqlx
config {
  type: "assertion",
  description: "Sprawdzenie czy dane są świeże - najnowsza transakcja nie może być starsza niż 2 godziny",
  tags: ["monitoring", "sales"]
}

SELECT
  MAX(transaction_date) AS latest_transaction,
  CURRENT_TIMESTAMP() AS check_time,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(transaction_date), HOUR) AS hours_old
FROM ${ref("sales_summary")}
HAVING TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), MAX(transaction_date), HOUR) > 2

Ten test to rodzaj monitoringu "czy pipeline w ogóle działa". Jeśli najnowsza transakcja w tabeli jest starsza niż dwie godziny, coś jest nie tak z procesem ładowania danych. W praktyce taki test często wyłapuje problemy z upstream systemami, zanim ktokolwiek zauważy brak danych w dashboardach.

Dla tabeli z alertami magazynowymi możemy dodać test sprawdzający sensowność alertów:

-- definitions/assertions/inventory_alerts_sanity.sqlx
config {
  type: "assertion",
  description: "Sprawdzenie czy alerty magazynowe mają sens - nie powinno być alertów dla produktów wycofanych",
  tags: ["inventory"]
}

SELECT
  ia.product_id,
  ia.product_name,
  ia.severity,
  p.status AS product_status
FROM ${ref("inventory_alerts")} ia
JOIN ${ref("raw_products")} p ON ia.product_id = p.product_id
WHERE
  p.status IN ('discontinued', 'archived')
  OR p.is_active = FALSE

Ten test wyłapuje sytuacje, gdy generujemy alerty dla produktów, które już nie są sprzedawane. To może się zdarzyć, jeśli ktoś zapomniał zaktualizować statusu produktu w systemie źródłowym.

Organizacja kodu i reużywalność

W miarę jak projekt rośnie, zauważysz, że pewne fragmenty kodu powtarzają się w wielu miejscach. Na przykład, może masz standardowy sposób obliczania segmentów klientów lub klasyfikacji produktów. Dataform pozwala definiować funkcje JavaScript w folderze includes, które możesz później używać w wielu transformacjach.

Tworzymy plik includes/common.js:

// Funkcja do generowania standardowej klauzuli WHERE dla dat
function dateRangeFilter(dateColumn, daysBack) {
  return `${dateColumn} >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL ${daysBack} DAY)`;
}

// Funkcja do standaryzacji nazw klientów
function customerSegmentLogic() {
  return `
    CASE
      WHEN total_lifetime_value > 10000 THEN 'VIP'
      WHEN total_lifetime_value > 5000 THEN 'High Value'
      WHEN total_orders > 10 THEN 'Regular'
      WHEN total_orders > 1 THEN 'Repeat'
      ELSE 'New'
    END
  `;
}

// Funkcja do wykluczania transakcji testowych
function excludeTestData() {
  return `
    is_test_transaction = FALSE
    AND customer_email NOT LIKE '%@test.com'
    AND customer_email NOT LIKE '%@example.com'
  `;
}

module.exports = {
  dateRangeFilter,
  customerSegmentLogic,
  excludeTestData
};

Teraz możesz używać tych funkcji w swoich transformacjach:

config {
  type: "table",
  schema: "analytics"
}

js {
  const { dateRangeFilter, customerSegmentLogic, excludeTestData } = require("includes/common");
}

SELECT
  customer_id,
  ${customerSegmentLogic()} AS segment,
  COUNT(*) AS total_orders,
  SUM(total_value) AS total_lifetime_value
FROM ${ref("sales_summary")}
WHERE ${dateRangeFilter("transaction_date", 365)}
  AND ${excludeTestData()}
GROUP BY customer_id

To znacznie ułatwia utrzymanie spójności logiki biznesowej w całym projekcie. Jeśli zmieni się definicja segmentu VIP, zmieniasz to w jednym miejscu, a nie w dziesięciu różnych tabelach.

Środowiska i deployment

W profesjonalnym projekcie musisz mieć rozdzielenie środowisk. Nie chcesz testować zmian bezpośrednio na produkcji. Dataform wspiera to przez workspaces i zmienne środowiskowe.

Możesz mieć różne wartości zmiennych w zależności od środowiska. W pliku dataform.json definiujesz bazowe wartości:

{
  "defaultSchema": "analytics",
  "warehouse": "bigquery",
  "defaultDatabase": "projekt-dev",
  "defaultLocation": "EU",
  "vars": {
    "data_retention_days": 30,
    "min_transaction_value": 0,
    "alert_email": "[email protected]"
  }
}

Na produkcji nadpisujesz te wartości przez ustawienia w Google Cloud Console lub przez API. W praktyce oznacza to, że ten sam kod działa zarówno w środowisku deweloperskim, jak i produkcyjnym, ale z różnymi parametrami.

Możesz odwoływać się do zmiennych w kodzie:

WHERE
  total_value >= ${dataform.projectConfig.vars.min_transaction_value}
  AND ${dateRangeFilter("transaction_date", dataform.projectConfig.vars.data_retention_days)}

W deweloperskim środowisku możesz mieć min_transaction_value ustawione na zero, żeby widzieć wszystkie transakcje, a na produkcji na przykład na 10 PLN, żeby odfiltrować transakcje testowe za drobne kwoty.

Uruchamianie i automatyzacja

Dataform możesz uruchomić na kilka sposobów. Do lokalnego developmentu używam Dataform CLI, który instalujesz przez npm:

npm install -g @dataform/cli
dataform run --dry-run

Flaga --dry-run pokazuje, jakie zapytania SQL zostaną wykonane, bez faktycznego ich uruchamiania. To świetne narzędzie do debugowania, bo widzisz dokładnie, jaki SQL wygeneruje Dataform.

Do automatyzacji na produkcji używam Cloud Scheduler, który wywołuje Dataform workflow o określonych porach. Typowy setup wygląda tak, że mam trzy oddzielne workflow:

Pierwszy workflow działa co godzinę i aktualizuje tylko transformacje przyrostowe z danymi operacyjnymi, takimi jak stany magazynowe czy alerty. To są dane, które muszą być świeże.

Drugi workflow działa codziennie o drugiej w nocy i przetwarza wszystkie dane sprzedażowe z poprzedniego dnia. To jest główny proces ETL, który przygotowuje dane na poranek dla zespołów biznesowych.

Trzeci workflow działa w weekend i przebudowuje od zera wybrane tabele agregacyjne, które są używane rzadko, ale muszą być kompletne. To jest też dobry moment na uruchomienie wszystkich assertions, żeby sprawdzić jakość całego pipeline'u.

W praktyce konfiguruję to przez Terraform, co daje mi infrastrukturę jako kod i łatwość replikacji setupu między środowiskami.

Monitoring i alertowanie

Dataform sam w sobie nie ma wbudowanego systemu alertowania, więc integruję go z innymi narzędziami. Najbardziej straightforward sposób to używać Cloud Monitoring do śledzenia statusu wykonania workflow.

Możesz też dodać na końcu swojego workflow specjalną transformację, która sprawdza, czy wszystkie assertions przeszły i wysyła powiadomienie, jeśli coś się nie powiodło. Robię to przez prostą Cloud Function, która dostaje trigger po każdym wykonaniu Dataform workflow i sprawdza logi.

Alternatywnie możesz używać metadanych z Information Schema w BigQuery, żeby monitorować, kiedy ostatnio tabele były aktualizowane:

SELECT
  table_name,
  TIMESTAMP_MILLIS(last_modified_time) AS last_modified,
  TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_modified_time), HOUR) AS hours_since_update
FROM `projekt.analytics.__TABLES__`
WHERE TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), TIMESTAMP_MILLIS(last_modified_time), HOUR) > 24

To zapytanie pokazuje tabele, które nie były aktualizowane przez ponad 24 godziny, co może wskazywać na problem z pipeline.

Kosztowa optymalizacja

BigQuery płaci się za skanowane dane, więc optymalizacja może znacząco obniżyć koszty. Kilka praktyk, które stosuję w każdym projekcie:

• Zawsze używaj partycjonowania dla tabel z danymi czasowymi. Zapytanie skanujące dane z jednego dnia zamiast całej tabeli może być stokrotnie tańsze.
• Używaj klastrowania dla kolumn, po których często filtujesz. W e-commerce to zazwyczaj kategorie produktów, segmenty klientów czy statusy zamówień.
• Dla dużych tabel używaj typu incremental zamiast przebudowywać je od zera. Transformacja przyrostowa, która przetwarza tylko nowe dane, może kosztować dziesiątą część pełnej transformacji.
• Unikaj SELECT * w transformacjach końcowych. Wybieraj tylko potrzebne kolumny. Jeśli masz tabelę z 50 kolumnami, a używasz tylko 10, to skanowanie będzie pięć razy tańsze.
• Używaj partycyjnych filtrów wszędzie, gdzie to możliwe. Zamiast WHERE transaction_date >= '2024-01-01' użyj partycyjnego filtra w konfiguracji BigQuery.

Podsumowanie

Google Cloud Dataform to narzędzie do zarządzania transformacjami SQL w BigQuery, które wprowadza do pracy z danymi praktyki znane z rozwoju oprogramowania - kontrolę wersji, testy, dokumentację i automatyzację.

Kluczowe zalety Dataform w kontekście e-commerce:

• Zarządzanie zależnościami między tabelami - Dataform automatycznie określa kolejność wykonywania transformacji na podstawie referencji między nimi
• Testowanie jakości danych - assertions pozwalają wykrywać problemy z danymi, zanim trafią one do raportów
• Praca zespołowa - Git i pull requesty umożliwiają code review i śledzenie zmian w logice biznesowej
• Transformacje przyrostowe - oszczędzają czas i koszty przetwarzania przez aktualizowanie tylko nowych danych
• Reużywalność kodu - includes i JavaScript pozwalają na współdzielenie logiki między transformacjami
• Środowiska - możliwość testowania zmian przed wdrożeniem na produkcję

Dataform wymaga inwestycji czasu w naukę i zmianę sposobu pracy, ale dla zespołów zarządzających złożonymi pipeline'ami danych w BigQuery jest to narzędzie, które znacząco podnosi jakość i stabilność infrastruktury analitycznej.