Alles, was Sie über Snowflake Dynamic Tables wissen müssen

In Snowflake ist eine Dynamic Table eine Tabelle, die das Ergebnis einer SQL-Abfrage materialisiert und automatisch nach einem festgelegten Zeitplan aktualisiert wird. Ähnlich wie bei einer View kann das SQL zur Erstellung einer Dynamic Table erhebliche Komplexität enthalten: das Verknüpfen mehrerer Tabellen, verschiedene Join-Typen (Left Join, Full Join, Cartesian), Unions, Berechnungen usw. Im Gegensatz zu einer View, die keine Daten persistiert, wird das Ergebnis einer Dynamic Table jedoch als physische Tabelle gespeichert.

Dynamic Tables werden in einer von Ihnen festgelegten Frequenz aktualisiert, dem sogenannten "Target Lag" bzw. target_lag. Daher ist das Lesen (Select) aus einer Dynamic Table oft deutlich performanter als das Lesen aus einer View mit vielen komplexen Joins.

Dynamic Tables sind eine einfache und kosteneffiziente Möglichkeit, performante Datenpipelines zu erstellen, die stets aktuell bleiben.

In Snowflake ist eine Materialized View eine View auf einer einzelnen Tabelle. Sie wird als physische Tabelle gespeichert, sodass Abfragen schnell sind, wird aber wie eine View in Echtzeit aktuell gehalten.

Nachfolgend die wichtigsten Unterschiede zwischen Materialized Views und Dynamic Tables.

Joins

In Snowflake kann eine Materialized View keine Joins enthalten. Diese gravierende Einschränkung begrenzt den Nutzen von Materialized Views erheblich.

Eine Dynamic Table in Snowflake ähnelt viel eher einer Materialized View in anderen Datenbanken wie PostgreSQL, wo es kaum Einschränkungen für das SQL gibt, das Sie schreiben können. In Postgres muss eine Materialized View jedoch manuell mit dem Befehl refresh materialized view aktualisiert werden, während eine Dynamic Table in Snowflake automatisch basierend auf dem Target Lag aktualisiert werden kann.

Aktualisierungsfrequenz

Materialized Views in Snowflake haben den Vorteil, dass sie immer aktuell gehalten werden. Sie arbeiten in Echtzeit, ohne dass Sie etwas zur Aktualisierung tun müssen.

Dynamic Tables hingegen werden nach einem Zeitplan aktualisiert. Der Endnutzer muss möglicherweise berücksichtigen, wie aktuell die Daten sind.

Query Rewrite

Beim Abfragen der Basistabellen einer Materialized View kann der Query Optimizer von Snowflake Ihre Abfrage so umschreiben, dass stattdessen die Materialized View verwendet wird.

Beim Abfragen der Basisdaten einer Dynamic Table schreibt Snowflake die Abfrage hingegen nicht so um, dass die Dynamic Table verwendet wird.

Meine Einschätzung zu den Unterschieden

Materialized Views haben zwar zwei Vorteile (immer aktuell und Query Rewrite), aber die Tatsache, dass sie nur auf einer einzigen Tabelle basieren können, schränkt ihren Nutzen so stark ein, dass der Griff zur Dynamic Table deutlich häufiger erfolgt.

Verwenden Sie eine Materialized View, wenn Sie lediglich aggregieren, Berechnungen hinzufügen oder eine einzelne Tabelle transformieren.

Verwenden Sie eine Dynamic Table für komplexere Anwendungsfälle.

Eine Dynamic Table wird ähnlich wie das vertraute CTAS erstellt, allerdings mit einigen zusätzlichen Parametern: target_lag, warehouse sowie weiteren optionalen Parametern, die unten gezeigt werden.

Vollständige Syntax, übernommen aus der Snowflake-Dokumentation:

CREATE [ OR REPLACE ] [ TRANSIENT ] DYNAMIC TABLE [ IF NOT EXISTS ] <name> (
    -- Column definition
    <col_name> <col_type>
      [ [ WITH ] MASKING POLICY <policy_name> [ USING ( <col_name> , <cond_col1> , ... ) ] ]
      [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
      [ COMMENT '<string_literal>' ]

    -- Additional column definitions
    [ , <col_name> <col_type> [ ... ] ]

  )
  TARGET_LAG = { '<num> { seconds | minutes | hours | days }' | DOWNSTREAM }
  WAREHOUSE = <warehouse_name>
  [ REFRESH_MODE = { AUTO | FULL | INCREMENTAL } ]
  [ INITIALIZE = { ON_CREATE | ON_SCHEDULE } ]

Code erweitern

create or replace dynamic table my_table
target_lag = '1 Day'
warehouse = 'TRANSFORMING'
refresh_mode = 'incremental'
as
select
     customers.name,
     count(*) as total_orders
from orders
inner join customers
   using (customer_id)
group by 1

In der Praxis könnte Ihr create-Statement so aussehen:

Die Aktualisierung wird sofort initialisiert, da der Standardwert für initialize on_create lautet.

Sehen wir uns die beiden Argumente, die Sie am häufigsten verwenden werden, genauer an: target_lag und refresh_mode.

Der Target Lag ist die maximale Zeitspanne, um die die Daten den Änderungen in den Quelldaten hinterherhinken dürfen. Er wird als Ganzzahl und Zeiteinheit (Sekunden, Minuten, Stunden, Tage) angegeben. Beispielsweise ist 5 minutes ein gültiger Target Lag.

Wenn Sie eine Dynamic Table auf eine andere Dynamic Table aufsetzen, können Sie für alle Dynamic Tables in der Kette target_lag = downstream festlegen – mit Ausnahme der letzten, die einen Zeitplan haben sollte. Betrachten Sie das folgende Beispiel:

Im obigen Bild sind zwei Dynamic Tables miteinander verkettet. Die erste (orders_joined) hat einen Target Lag von downstream. Die zweite (orders_aggregated) hat einen Target Lag von 1 Tag. In diesem Fall wird der gesamte DAG einmal täglich aktualisiert. Sie müssen sich nicht darum kümmern, für jede Tabelle einen separaten Zeitplan festzulegen.

• Wenn die letzte Dynamic Table in Ihrem DAG einen Target Lag von downstream hat, werden Ihre Daten nie aktualisiert!
• Snowflake weist in der Dokumentation darauf hin: Target Lag ist keine Garantie, sondern ein Zielwert, den Snowflake zu erreichen versucht. Die Daten in Dynamic Tables werden so zeitnah wie möglich innerhalb des Target Lag aktualisiert. Der Target Lag kann jedoch durch Faktoren wie Warehouse-Größe, Datenmenge, Abfragekomplexität und ähnliche Faktoren überschritten werden.

Der Refresh Mode kann auf auto, full oder incremental gesetzt werden. Der Standard ist auto, der versucht, inkrementell zu aktualisieren, und auf Full zurückfällt, wenn dies nicht möglich ist.

Dynamic Tables werden inkrementell basierend auf changes in den Quelldaten aktualisiert. Sie müssen keine Informationen über einen Primärschlüssel oder die Erkennung von Änderungen angeben – Snowflake erledigt das alles für Sie, wie von Zauberhand! Weitere Informationen zu Changes finden Sie in den Snowflake Docs sowie in unserem Blogbeitrag zu Streams.

Erwähnenswert ist, dass Dynamic Tables eng mit Streams in Snowflake verwandt sind, da sie unter der Haube dieselbe Change-Tracking-Technologie nutzen: changes. Tatsächlich werden beide Features vom selben Team bei Snowflake betreut!

Dynamic Tables sind eine deklarative, benutzerfreundliche Möglichkeit, eine Pipeline aufzubauen.

Streams sind imperativ und erfordern deutlich mehr Anpassungsaufwand, damit die Pipeline funktioniert.

Einschränkungen des Inkremental-Modus

Eine Dynamic Table kann nicht inkrementell aktualisiert werden, wenn:

• eine nicht unterstützte SQL-Funktion wie current_timestamp oder random verwendet wird.
• ein nicht unterstütztes SQL-Konstrukt verwendet wird: pivot, unpivot, union, minus, intersect, except.
  • union all wird für inkrementelle Aktualisierungen unterstützt! Außer in einigen Edge Cases.
• nicht identische partion_by-Klauseln in verschiedenen Window Functions verwendet werden.
• mehr als 5 % der Daten verändert wurden. Darauf sollten Sie besonders achten!
• Subquery-Operatoren wie in, any, all, exists verwendet werden.

Es gibt noch einige weitere Einschränkungen, auf die Sie achten sollten! Oben habe ich nur die Einschränkungen aufgeführt, die Ihnen meiner Meinung nach am häufigsten begegnen werden. Eine vollständige Erklärung der Einschränkungen beim inkrementellen Refresh für Dynamic Tables finden Sie auf dieser Seite.

Auto-Refresh klingt verlockend … aber …

Wenn Sie erfolgreich eine Dynamic Table mit incremental erstellen und Snowflake sie eines Tages aufgrund einer der dokumentierten Einschränkungen nicht mehr inkrementell aktualisieren kann, schlägt der Refresh fehl. Er scheitert dabei lautlos, sofern Sie keinen Alerting-Mechanismus zur Überwachung der Refreshes eingerichtet haben. Um sich diesen Ärger zu ersparen, können Sie auto verwenden, damit Snowflake Ihre Tabelle bei Bedarf vollständig aktualisiert. Überwachen Sie dann Ihre Dynamic Tables, um zu beobachten, wie Snowflake die Aktualisierungen handhabt.

Snowflake gibt jedoch eine Warnung zur Verwendung des auto-Refresh-Modus aus:

Für konsistentes Verhalten setzen Sie den Refresh Mode bei allen produktiven Tabellen explizit. Das Verhalten von AUTO kann sich zwischen Snowflake-Releases ändern, was in produktiven Pipelines zu unerwarteten Performance-Änderungen führen kann.

Der Data-Tab in Snowsight

Der einfachste Weg, Dynamic Tables zu überwachen, ist die Snowsight-Oberfläche.

Navigieren Sie im Data-Tab in der linken Seitenleiste von Snowsight zu Ihrer Dynamic Table. Klicken Sie sie an und dann auf den Tab "Refresh History".

Ein paar besonders nützliche Aspekte:

• Der Bereich "Lag Metrics" ist äußerst hilfreich. Hier können Sie nachvollziehen, ob der Lag jemals den Target Lag überschritten hat, wie aktuell die Daten gerade sind und welcher maximale Lag auf der Tabelle jemals aufgetreten ist.
• Im Bereich Refresh-History-Daten finden Sie auf der rechten Seite einen Hyperlink zum Query Profile des Refreshes (das Blitz-im-Haus-Symbol). Dies ist beim Troubleshooting langer Refreshes ausgesprochen nützlich!

Für ein automatisiertes Monitoring von Refresh-Fehlern lesen Sie unsere Artikel, wie Sie Alerts an Slack und Microsoft Teams senden.

Der Monitoring-Tab in Snowsight

Sie können nicht nur einzelne DAGs überwachen, indem Sie eine beliebige Dynamic Table im Data-Tab auswählen, sondern auch den Status aller Dynamic Tables zentral im "Monitoring"-Tab einsehen.

Hier können Sie nach "Refresh Status" sortieren, um fehlgeschlagene Aktualisierungen zu finden.

Snowflake bietet mehrere Eigenschaften, die per alter dynamic table aktualisiert werden können. Hier die vollständige Liste aus der Snowflake-Dokumentation:

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { SUSPEND | RESUME }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> RENAME TO <new_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> SWAP WITH <target_dynamic_table_name>

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> REFRESH [ COPY SESSION ]

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { clusteringAction }

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> { tableColumnCommentAction }

ALTER DYNAMIC TABLE <name> { SET | UNSET } COMMENT = '<string_literal>'

ALTER DYNAMIC TABLE [ IF EXISTS ] <name> dataGovnPolicyTagAction

Code erweitern

Beispiele: So ändern Sie eine Dynamic Table

-- Suspend the dynamic table / turn of automatic updates
alter dynamic table my_dynamic_table suspend

-- turn auto updates back on
alter dynamic table my_dynamic_table resume

-- update target lag
alter dynamic table my_dynamic_table set target_lag = '2 Days'

-- change target_lag from time interval to downstream
alter dynamic table my_dynamic_table set target_lag = 'downstream'

-- change refresh_mode from incremental to full
alter dynamic table my_dynamic_table set refresh_mode = 'full'

Code erweitern

Das SQL einer Dynamic Table bearbeiten

Bemerkenswert ist, dass Sie weder eine Spalte hinzufügen noch das SQL per alter-Befehl ändern können; Sie müssen die Tabelle vollständig ersetzen. Das geht entweder mit drop table my_dynamic_table und anschließendem Neuanlegen oder mit create or replace im DDL: create or replace dynamic table my_dynamic_table...

Erstellen wir ein einfaches Beispiel, das jeder nachvollziehen kann. Für dieses Beispiel kopieren wir die Beispieldaten aus der Datenbank snowflake_sample_data. (Da Sie Dynamic Tables leider weder klonen noch auf Daten aus einem Share aufbauen können, duplizieren wir die Daten einfach und legen darauf die Dynamic Tables an.)

Hier ein Bild des DAG, den wir erstellen:

Setup

Legen wir einige neue Objekte an, damit wir alle vom gleichen Ausgangspunkt arbeiten.

use role sysadmin;

create warehouse example_wh_xs
warehouse_size = xsmall
auto_suspend = 60
auto_resume = true;

use role securityadmin;

create role example_role;
grant role example_role to user jeff; -- swap in your user
grant all on warehouse example_wh_xs to role example_role;

use role sysadmin;
create database dynamic_demo;

Code erweitern

Nun kopieren wir einige Beispieldaten in unsere neue Datenbank. (Nochmals: Clone funktioniert nicht!)

use schema dynamic_demo.sample_data;

create or replace table orders as
select * from
snowflake_sample_data.tpch_sf10.orders;

create or replace table customer as
select * from
snowflake_sample_data.tpch_sf10.customer;

Jetzt erstellen wir zwei Dynamic Tables: eine Tabelle, die unsere beiden neuen Tabellen verknüpft, und eine Tabelle, die die Daten aggregiert. Die erste Dynamic Table im DAG hat einen Target Lag von downstream. Die zweite Dynamic Table hat einen Target Lag von 1 Tag. Damit wird der Lag für den gesamten DAG gesteuert.

create or replace dynamic table obt_orders
target_lag = 'downstream'
warehouse = 'EXAMPLE_WH_XS'
refresh_mode = 'incremental'
as
select *
from orders join customer
on orders.o_custkey = customer.c_custkey;

create or replace dynamic table current_month_fulfillments
target_lag = '1 day'
warehouse = 'EXAMPLE_WH_XS'
refresh_mode = 'incremental'
as
with current_month as (

Code erweitern

Quelldaten ändern und Pipeline ausführen

Die Tabelle current_month_fulfillments sollte keine Daten enthalten, da keine der Bestellungen in diesem Monat den Status F oder P hat. (Siehe die where-Klausel in unserer obigen Abfrage.)

Aktualisieren wir nun die Rohdaten und führen ein manuelles Refresh der Tabelle current_month_fulfillments aus:

update orders set o_orderstatus = 'F'
where date_trunc('month', o_orderdate) = '1998-08-01';
-- yes, the max order month in the data is august 1998!
-- 12,466 rows updated

-- manually refresh the last table in the DAG so we don't have to wait:
alter dynamic table current_month_fulfillments refresh;

Wir sehen, dass die erste Tabelle automatisch aktualisiert wurde, weil sie auf target_lag='downstream' gesetzt war:

Wir sehen, dass 12,5K Zeilen gelöscht und neu eingefügt wurden.

Bei der zweiten Tabelle im DAG sehen wir, dass 5 Zeilen eingefügt wurden:

Klicken Sie auf den Tab "Graph", der eine hervorragende Übersicht über alle Tabellen im DAG bietet. Die Dynamic Tables zeigen einen Status an: "Succeeded" in Grün oder "Failed" in Rot.

Aufräumen

drop database dynamic_demo;
use role sysadmin;
drop warehouse example_wh_xs;
use role securityadmin;
drop role example_role;

Eine Dynamic Table in dbt zu erstellen ist so einfach wie das Hinzufügen dieser Konfiguration in Ihre normale .sql-Datei:

{{ config(
    materialized="dynamic_table",
    on_configuration_change="apply" | "continue" | "fail",
    target_lag="downstream" | "<integer> seconds | minutes | hours | days",
    snowflake_warehouse="<warehouse-name>",
    refresh_mode="AUTO" | "FULL" | "INCREMENTAL",
    initialize="ON_CREATE" | "ON_SCHEDULE",

) }}

Oder in einer Property-Datei:

version: 2

models:
  - name: [<model-name>]
    config:
      materialized: dynamic_table
      on_configuration_change: apply | continue | fail
      target_lag: downstream | <time-delta>
      snowflake_warehouse: <warehouse-name>
      refresh_mode: AUTO | FULL | INCREMENTAL
      initialize: ON_CREATE | ON_SCHEDULE

So funktioniert es mit dbt

Beim ersten dbt-Lauf wird die Dynamic Table erstellt. Bei nachfolgenden Läufen erkennt dbt, dass die Tabelle bereits existiert, und überspringt sie. Die Tabelle wird ausschließlich über den target_lag aktualisiert, nicht durch das Ausführen von dbt.

Wie bereits erwähnt, unterstützt Snowflake das Ändern des SQL einer Dynamic Table nicht. Daher erfordert jede Änderung an Ihrer Modelldefinition einen --full-refresh.

Das Ausführen von dbt mit --full-refresh verwirft die Dynamic Table und legt sie neu an.

Dynamic Tables in dbt vs. dbt Incremental im Vergleich

Dynamic Tables:

• Der Refresh-Mechanismus wird von Snowflake verwaltet, nicht von dbt.
• Deklarativ: Sie definieren lediglich das Select-Statement, nicht die inkrementelle Logik.

Incremental Models:

• Der Refresh wird von dbt oder dem Orchestrierungs-Tool für dbt verwaltet.
• Imperativ: Sie müssen eine eigene inkrementelle Logik definieren.
• Verwenden Sie diese, wenn Sie feinere Kontrolle darüber benötigen, wie eine Tabelle inkrementell aktualisiert wird.

Dynamic Tables haben einige Einschränkungen. Für eine vollständige Liste empfehle ich die Dokumentation. Hier sind die Einschränkungen, die Ihnen meiner Meinung nach am häufigsten begegnen werden:

• Dynamic Tables können nicht nachgelagert (downstream) von Materialized Views, External Tables oder Streams liegen.
• Sie können keine temporäre Dynamic Table erstellen.
• Die Snowflake-Dokumentation erwähnt, dass Sie eine Dynamic Table nicht truncaten können. Sie versäumt jedoch zu erwähnen, dass keine DML-Operationen möglich sind. Insert, Update und Delete schlagen auf einer Dynamic Table allesamt fehl. Das ist nachvollziehbar, da eine Dynamic Table die zugrunde liegenden Quellen und die SQL-Definition abbilden muss.
• Sie können den Parameter DATA_RETENTION_TIME_IN_DAYS Ihrer Quelltabellen nicht auf null setzen. Der Grund: changes in Snowflake nutzt Time Travel. Time Travel muss aktiviert sein.
• Der Target Lag muss kleiner sein als der data_retention_time_in_days-Wert der vorgelagerten Tabellen.
• Sie können kein dynamisches SQL (Session-Variablen) in Dynamic Tables verwenden.
• Operationen auf Dynamic Tables werden nicht von Snowflakes access_history-View erfasst.
• Sie können keine Sequenzen verwenden. Beispielsweise darf die SQL-Definition einer Dynamic Table nicht select my_sequence.nextval enthalten.
• Sie können sample oder tablesample nicht in der Definition einer Dynamic Table verwenden.
• Geklonte inkrementelle Dynamic Tables können beim Initialisieren einen Full Refresh durchführen.

Hier meine wichtigsten Empfehlungen für die Arbeit mit Dynamic Tables:

• Wählen Sie den längstmöglichen Target Lag für Ihren Anwendungsfall. So senken Sie die Compute-Kosten, indem Sie die Häufigkeit der Neuberechnungen (Refreshes) Ihrer Tabellen minimieren.
• Das Verketten von Dynamic Tables ist empfehlenswert. Damit können Sie Pipelines aufbauen, die vollständig aus Dynamic Tables und Views bestehen, sodass Snowflake die Aktualisierungen übernimmt.
• Setzen Sie downstream als Target Lag für alle Tabellen außer der letzten Tabelle im DAG.
  • Wenn Sie mehrere Endknoten (Leaf Nodes) haben, können Sie eine Controller-Tabelle verwenden, um Target-Lag-Zeit (und andere Eigenschaften) zentral für Ihr Account zu pflegen. Beispiel hier.
• Verwenden Sie Transient Dynamic Tables, um Speicherkosten zu reduzieren.
  • Setzen Sie dafür eine höhere Time-Travel-Dauer auf den Quellen.
• Weitere Best Practices vom Snowflake-Team finden Sie hier.

Bei Dynamic Tables fallen Kosten in drei Hauptbereichen an:

1. Compute-Kosten für die Aktualisierung der Tabellen
2. Speicherkosten für die Dynamic Tables selbst
3. Cloud-Services-Kosten im Zusammenhang mit Refreshes, jedoch nur, wenn sie 10 % Ihrer täglichen Compute-Kosten übersteigen

Wie überwacht man die Kosten von Dynamic Tables?

Snowflake empfiehlt, ein dediziertes Warehouse zur Kostenüberwachung von Dynamic Tables zu verwenden; ich würde jedoch davon abraten, Warehouses unnötig zu vervielfachen.

Stattdessen können Sie ein Tool wie SELECT nutzen, das Ihnen die Kosten jeder einzelnen Dynamic Table sowie deren zeitliche Entwicklung automatisch aufzeigt.

Sie sehen außerdem die Kosten Ihres gesamten Dynamic-Table-DAG, wodurch Sie häufig größere Probleme entdecken können – etwa Tabellen, die zu häufig laufen.

Dynamic Tables sind ein hervorragendes Werkzeug im Werkzeugkasten Ihrer Datenpipeline. Die automatisierten Refreshes und der Wegfall der manuellen Inkremental-Logik machen Dynamic Tables zu einer sehr attraktiven Option. Behalten Sie nur die zahlreichen Einschränkungen im Blick – insbesondere die rund um den inkrementellen Refresh besprochenen, die durchaus erheblich sind.

Hoffentlich fühlen Sie sich nun bereit, Dynamic Tables in Ihren Datenpipelines einzusetzen! Ich freue mich, von Ihren Erfahrungen mit Dynamic Tables zu hören.

Jeff ist Data- und Analytics-Consultant mit über 15 Jahren Erfahrung in der Automatisierung von Insights und der Steuerung von Geschäftsprozessen durch Daten. Technologisch spezialisiert er sich auf Snowflake + dbt + Tableau. Geschäftlich bringt er Erfahrung aus den Bereichen Versorgungswirtschaft, klinische Studien, Verlagswesen, CPG und Fertigung mit. Kontaktieren Sie ihn jederzeit unter [email protected].