Snowflake et ClickHouse

Ce document sert de référence aux partenaires qui migrent de Snowflake vers ClickHouse. Il présente les différences d’architecture qui orientent les décisions de conception, ainsi que les six écarts de dialecte SQL rencontrés dans la charge de travail NYC Taxi.

1. Comparaison des architectures

Stockage

Snowflake prend toutes les décisions de stockage physique à votre place. Les données sont enregistrées sous forme de micropartitions en colonnes compressées dans un stockage d’objets cloud. Vous choisissez la taille du warehouse et la structure des tables ; Snowflake gère automatiquement tout le reste, notamment le clustering, la compaction et les fichiers.

ClickHouse vous oblige à prendre explicitement les décisions de stockage physique. Lors de la création d’une table, vous indiquez :

• le moteur, qui détermine la manière dont les données sont stockées, fusionnées et dédupliquées ;
• le ORDER BY, qui devient l’ordre de tri physique et l’index primaire ;
• éventuellement PARTITION BY, TTL et SETTINGS, pour le codec de compression ou le comportement des fusions.

Il s’agit de décisions liées à l’exactitude, et non de simples réglages de performance. Un mauvais moteur peut produire silencieusement des résultats incorrects. Un mauvais ORDER BY peut obliger une requête censée être rapide à parcourir toute la table.

Exécution des requêtes

Snowflake utilise une architecture MPP sans partage avec des warehouses virtuels. Un warehouse est un cluster de nœuds de calcul qui traite les requêtes. Vous payez tant qu’il fonctionne, même s’il est inactif. La suspension automatique atténue ce coût, mais un démarrage à froid ajoute de la latence.

ClickHouse emploie une exécution vectorisée. ClickHouse Cloud dimensionne automatiquement chaque service de calcul de façon indépendante et le ramène à zéro en cas d’inactivité. Plusieurs services de calcul peuvent partager le même stockage grâce à SharedMergeTree : c’est le modèle de séparation calcul-calcul de ClickHouse Cloud, dans lequel chaque service constitue un niveau de calcul indépendant au-dessus d’une couche de données commune.

Modèle de concurrence

Snowflake isole les charges de travail en créant des warehouses distincts. L’ETL utilise TRANSFORM_WH et l’analytique ANALYTICS_WH. Chaque warehouse dispose de son propre calcul ; une tâche ETL lente ne peut pas priver de ressources une requête analytique.

ClickHouse Cloud prend en charge le même modèle grâce à la séparation calcul-calcul : vous pouvez provisionner plusieurs services de calcul qui partagent le même stockage. Chacun forme un niveau de calcul indépendant à dimensionnement automatique. L’ETL s’exécute sur un service et l’analyse interactive sur un autre, sans concurrence pour les ressources. Au sein d’un même service, l’isolation des charges s’obtient avec des quotas souples, comme max_threads, priority et max_memory_usage par utilisateur ou par requête, ainsi qu’avec des profils utilisateur assortis de limites. Pour la plupart des charges analytiques, dont les requêtes s’achèvent en quelques millisecondes, un seul service suffit et les quotas par requête constituent l’option la plus légère.

Modèle de coût

Différence principale : dans Snowflake, vous payez le temps du warehouse, que des requêtes s’exécutent ou non. Dans ClickHouse Cloud, le calcul revient à zéro entre les requêtes. Pour les charges analytiques irrégulières, ClickHouse Cloud coûte généralement trois à huit fois moins cher qu’une configuration Snowflake équivalente.

2. Écarts de dialecte SQL

La charge de travail NYC Taxi contient six constructions à traduire. Elles figurent toutes dans les requêtes Q1 à Q7 du répertoire 01-setup-snowflake/queries/.

Écart 1 : QUALIFY

QUALIFY est une extension Snowflake qui filtre les lignes selon le résultat d’une fonction de fenêtrage, comme HAVING filtre selon le résultat d’un agrégat. Dans cette migration, nous considérons QUALIFY comme un écart de dialecte et le réécrivons avec une sous-requête : ce modèle universellement portable fonctionne sur tous les moteurs SQL.

-- Snowflake
SELECT
    trip_id,
    pickup_at,
    fare_amount,
    ROW_NUMBER() OVER (PARTITION BY pickup_location_id ORDER BY fare_amount DESC) AS fare_rank
FROM fact_trips
WHERE pickup_at >= CURRENT_DATE - 7
QUALIFY fare_rank <= 10;

-- ClickHouse: wrap in a subquery
SELECT trip_id, pickup_at, fare_amount, fare_rank
FROM (
    SELECT
        trip_id,
        pickup_at,
        fare_amount,
        ROW_NUMBER() OVER (PARTITION BY pickup_location_id ORDER BY fare_amount DESC) AS fare_rank
    FROM analytics.fact_trips
    WHERE pickup_at >= today() - 7
)
WHERE fare_rank <= 10;

Pourquoi est-ce important ? QUALIFY apparaît dans Q3. La réécriture avec une sous-requête est sûre et portable : elle fonctionne quel que soit le moteur SQL cible et rend explicite le résultat de la fonction de fenêtrage. Le danger de toute syntaxe propre à Snowflake consiste à supposer qu’elle sera acceptée silencieusement ; testez chaque requête avant d’affirmer que la migration est terminée.

Écart 2 : syntaxe de chemin avec deux-points pour VARIANT

Le type VARIANT de Snowflake emploie la notation de chemin avec deux-points pour accéder aux champs imbriqués : column:field.subfield::TYPE. ClickHouse stocke les données semi-structurées sous forme de String et les extrait à l’exécution avec les fonctions JSONExtract*.

-- Snowflake
SELECT
    trip_metadata:driver.rating::FLOAT  AS driver_rating,
    trip_metadata:app.version::STRING   AS app_version,
    trip_metadata:surge_multiplier::FLOAT AS surge
FROM trips_raw;

-- ClickHouse
SELECT
    JSONExtractFloat(trip_metadata, 'driver', 'rating')   AS driver_rating,
    JSONExtractString(trip_metadata, 'app', 'version')    AS app_version,
    JSONExtractFloat(trip_metadata, 'surge_multiplier')   AS surge
FROM default.trips_raw;

La famille complète JSONExtract* comprend JSONExtractFloat, JSONExtractInt, JSONExtractString, JSONExtractBool, JSONExtractKeys, JSONExtractArrayRaw et JSONExtractRaw. Utilisez JSONExtractRaw lorsqu’un objet ou tableau imbriqué doit rester une chaîne en vue d’un traitement ultérieur.

Pourquoi ne pas utiliser le type JSON de ClickHouse ? Le type JSON, auparavant expérimental, est disponible dans les versions récentes de ClickHouse, mais sa sémantique diffère et il n’est pas encore éprouvé en production pour tous les cas d’utilisation. Dans un laboratoire de migration, l’association de String et de JSONExtract* constitue le choix sûr et bien compris.

Écart 3 : LATERAL FLATTEN

Dans Snowflake, LATERAL FLATTEN transforme en lignes un tableau contenu dans une colonne VARIANT. ClickHouse n’a aucun équivalent direct.

-- Snowflake: explode a VARIANT array into rows
SELECT t.trip_id, f.value:stop_name::STRING AS stop_name
FROM trips_raw t,
LATERAL FLATTEN(input => t.trip_metadata:route_stops) f;

-- ClickHouse Option 1: JSONExtract into Array, then arrayJoin
SELECT
    trip_id,
    arrayJoin(JSONExtract(trip_metadata, 'route_stops', 'Array(String)')) AS stop_name
FROM default.trips_raw;

-- ClickHouse Option 2: Pre-flatten the column during dbt staging
-- In stg_trips.sql, extract all array elements to separate columns
-- or use the dbt model to reshape the data at load time

Le pré-aplatissement, option 2, est préférable lorsque le tableau possède un schéma connu et borné. arrayJoin, option 1, convient mieux aux requêtes ponctuelles ou aux tableaux de longueur variable.

Écart 4 : MERGE INTO

MERGE INTO est le principal mécanisme d’upsert de Snowflake. ClickHouse ne possède pas d’instruction MERGE. L’équivalent approprié dépend du moteur de la table.

-- Snowflake
MERGE INTO fact_trips t
USING staging_trips s ON t.trip_id = s.trip_id
WHEN MATCHED THEN UPDATE SET t.fare_amount = s.fare_amount, t.updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT VALUES (s.trip_id, s.pickup_at, ...);

-- ClickHouse with ReplacingMergeTree: just INSERT
-- RMT deduplicates by the ORDER BY key during background merges.
-- Use FINAL at query time to get the latest version:
INSERT INTO analytics.fact_trips SELECT * FROM staging_trips;

SELECT * FROM analytics.fact_trips FINAL WHERE trip_id = '...';

-- ClickHouse with dbt delete_insert incremental:
-- dbt handles the upsert by: DELETE WHERE key IN (new batch), then INSERT
-- This is the recommended approach for the analytics layer

La stratégie incrémentielle delete_insert de dbt-clickhouse est l’équivalent sémantique le plus proche de MERGE INTO pour les modèles analytiques. Elle supprime les lignes existantes dont la clé figure dans le lot entrant, puis insère toutes les lignes de ce lot, de manière atomique par partition.

Piège essentiel avec ReplacingMergeTree : la déduplication en arrière-plan est asynchrone. Entre deux fusions, l’ancienne et la nouvelle version d’une ligne existent toutes les deux dans la table. Employez toujours FINAL dans les requêtes qui doivent renvoyer exactement une ligne par clé. Consultez Moteurs MergeTree pour une présentation complète de la sémantique de déduplication.

Écart 5 : Streams Snowflake (CDC)

Les Streams Snowflake suivent les modifications au niveau des lignes, à savoir INSERT, UPDATE et DELETE, dans une table. Ils exposent les colonnes système METADATA$ACTION, METADATA$ISUPDATE et METADATA$ROW_ID. ClickHouse n’offre aucun mécanisme interne équivalent.

Équivalent ClickHouse : bascule directe du producteur

ClickHouse ne possède aucun mécanisme CDC interne comparable aux Streams Snowflake. Pour cette migration, le modèle est plus simple qu’un connecteur CDC :

• Commencer par le chargement en masse : scripts/02_migrate_trips.py lit toutes les lignes historiques de Snowflake par lots et les insère dans ClickHouse
• Basculer ensuite le producteur : scripts/03_cutover.sh arrête le producteur Snowflake et lance un producteur ClickHouse qui écrit directement dans ClickHouse Cloud
• Aucune fenêtre CDC n’est nécessaire : le script de migration assure le chargement historique et le producteur prend le relais pour les écritures en direct ; ReplacingMergeTree(_synced_at) sur trips_raw rend idempotente toute nouvelle tentative de migration ou du producteur

Après la bascule, la stratégie dbt delete_insert gère les upserts de la couche analytique. Les Streams et Tasks Snowflake sont entièrement retirés.

Écart 6 : fonctions de date et d’heure

Snowflake et ClickHouse donnent des noms différents aux fonctions de date. La plupart des traductions sont mécaniques.

DateTime et DateTime64 : le type DateTime de ClickHouse possède une précision à la seconde. Utilisez DateTime64(3, 'UTC') pour une précision à la milliseconde, équivalente à TIMESTAMP_NTZ dans Snowflake. Le 3 indique l’échelle inférieure à la seconde et 'UTC' le fuseau horaire.

3. Options de déplacement des données

Pour ce laboratoire, le script de migration Python est le bon choix : il ne requiert aucun service cloud supplémentaire, comme S3 ou Kafka, se débogue entièrement et utilise des paquets, snowflake-connector-python et clickhouse-connect, que les partenaires ont déjà installés pour d’autres étapes.

4. Comparaison des architectures CDC

Après la migration, le producteur écrit directement dans ClickHouse : aucun Stream ni Task n’est nécessaire. La stratégie dbt delete_insert gère les upserts de la couche analytique. Les vues matérialisées actualisables remplacent nativement dans ClickHouse l’agrégation périodique assurée par les Tasks Snowflake. Le projet dbt du laboratoire en fournit une, analytics.mv_live_trip_feed, mais le laboratoire n’active pas son intervalle d’actualisation ; consultez le module 05.

5. Analyse détaillée du modèle de coût

Snowflake : fondé sur les crédits

Un crédit Snowflake coûte environ 3 $ dans l’édition Enterprise. Le coût est égal à la taille du warehouse multipliée par sa durée d’exécution. Un warehouse SMALL consomme un crédit par heure, contre deux pour un MEDIUM. Avec une durée minimale de suspension automatique de 60 secondes, même une seule requête coûte au moins un soixantième d’heure.

Pour le laboratoire NYC Taxi, avec un warehouse X-Small à un crédit par heure :

• préparation de la première partie : environ 2 à 4 crédits, soit environ 6 à 12 $ ;
• chaque session de 8 heures : environ 4 à 8 crédits par jour, soit environ 12 à 24 $ ;
• le moniteur de ressources ANALYTICS_WH limite la consommation à 50 crédits par mois, soit environ 150 $.

ClickHouse Cloud : calcul et stockage séparés

ClickHouse Cloud facture séparément le calcul et le stockage :

• calcul : le niveau Development coûte environ 0,10 $ par heure d’activité et revient à zéro en cas d’inactivité ;
• stockage : environ 0,023 $/Go/mois, nettement moins que les 23 $/To de Snowflake ;
• ClickPipes : inclus dans l’abonnement Cloud pour les sources prises en charge, à savoir Kafka, S3, Kinesis, CDC PostgreSQL et CDC MySQL, mais pas Snowflake.

Pour le laboratoire NYC Taxi :

• 50 millions de lignes à environ 300 octets par ligne non compressée représentent environ 15 Go, ou environ 8 Go compressés dans ClickHouse ;
• coût du stockage : environ 0,18 $ par mois ;
• calcul pendant les quelque deux heures d’activité de la troisième partie : environ 0,20 à 0,40 $.

Coût total de la troisième partie : environ 2 à 4 $, contre environ 6 à 12 $ avec Snowflake pour la même session.

Cette différence de coût explique pourquoi de nombreuses organisations commencent avec Snowflake, plus simple à exploiter, puis migrent vers ClickHouse, moins coûteux et plus performant, à mesure que leur charge analytique augmente.