Dupliquer une table en SQL sur BigQuery : CTAS, CLONE et snapshots expliqués

Dupliquer une table sur BigQuery est une opération fréquente pour les Data Engineers.

Que ce soit pour créer une copie SQL complète, cloner une table existante ou réaliser un snapshot à un instant précis, BigQuery offre plusieurs méthodes simples, puissantes et économiques.

Cet article vous guide à travers les approches standards et spécifiques à BigQuery, avec des exemples concrets et reproductibles.

À retenir

Pourquoi dupliquer une table sur BigQuery

La duplication intervient dans plusieurs situations clés :

• Expérimenter sans risque : tester un pipeline ou une transformation sans impacter la production.
• Migration ou refonte : modifier un schéma ou déplacer une table entre datasets.
• A/B testing analytique : comparer des modèles ou des calculs sur un même échantillon.
• Formation ou démonstration : créer des environnements pédagogiques sans données sensibles.
• Sauvegarde / PRA (Plan de Reprise d’Activité) : assurer la résilience en cas de suppression ou de corruption.

Dupliquer une table en SQL standard

La méthode CTAS (CREATE TABLE AS SELECT) est universelle.

Elle crée une table physique à partir d’un SELECT.

CREATE TABLE analytics.salesCopy AS
SELECT *
FROM analytics.sales;

Copie du schéma sans données :

CREATE TABLE analytics.salesEmpty AS
SELECT *
FROM analytics.sales
WHERE 1 = 0;

Dupliquer une table sur BigQuery

1. CLONE : copie logique instantanée

CREATE TABLE `project.analytics.salesClone`
CLONE `project.analytics.sales`;

Pourquoi ça marche

BigQuery utilise le copy-on-write : la table clonée partage les mêmes blocs de données que la source.

Les données ne sont réellement copiées que lorsqu’elles sont modifiées.

2. CTAS BigQuery : copie complète et transformable

CREATE TABLE `project.analytics.salesCopy` AS
SELECT *
FROM `project.analytics.sales`;

• Crée une copie physique isolée.
• Permet d’appliquer des filtres ou calculs directement dans le SELECT.

Exemple filtré :

CREATE TABLE `project.analytics.salesRecent` AS
SELECT *
FROM `project.analytics.sales`
WHERE orderDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

3. Snapshot lecture seule

CREATE SNAPSHOT TABLE `project.analytics.salesSnapshot`
CLONE `project.analytics.sales`;

• Table en lecture seule figée à un instant donné.
• Idéale pour audit ou versionnement.

4. Copier via Time Travel

CREATE TABLE `project.analytics.salesBeforeChanges` AS
SELECT * FROM `project.analytics.sales`
FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY);

• Permet de restaurer une table à un état antérieur.
• Rétention configurée par défaut à 7 jours.

5. Copie vide via LIKE

CREATE TABLE `project.analytics.salesStructure`
LIKE `project.analytics.sales`;

• Copie uniquement la structure.
• Ne reproduit ni la partition ni le clustering.

6. Copie temporaire

Dans un script :

BEGIN
  CREATE TEMP TABLE salesTemp AS
  SELECT * FROM `project.analytics.sales`;

  SELECT COUNT(*) FROM salesTemp;
END;

Ou avec expiration automatique :

CREATE TABLE `project.analytics.salesTemp`
OPTIONS (
  expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR)
) AS
SELECT * FROM `project.analytics.sales`;

Cas pratique complet : scénario retail

Une enseigne veut tester un nouveau modèle de segmentation sur 6 mois de ventes.

1. Créer une copie filtrée

CREATE TABLE `project.analytics.salesWork` AS
SELECT *
FROM `project.analytics.sales`
WHERE orderDate >= DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH);

2. Ajouter des colonnes dérivées

CREATE TABLE `project.analytics.salesWorkEnriched` AS
SELECT
  orderId,
  customerId,
  orderDate,
  totalAmount,
  totalAmount >= 100 AS isHighBasket,
  CASE WHEN EXTRACT(DAYOFWEEK FROM orderDate) IN (1,7)
       THEN 'weekend' ELSE 'weekday' END AS dayType
FROM `project.analytics.salesWork`;

3. Optimiser la table finale

CREATE TABLE `project.analytics.salesWorkPartitioned`
PARTITION BY orderDate
CLUSTER BY customerId AS
SELECT *
FROM `project.analytics.salesWorkEnriched`;

Bonnes pratiques

• Utiliser CLONE pour la rapidité, CTAS pour l’isolation complète.
• Toujours documenter la finalité et la rétention de chaque copie.
• Supprimer ou expirer les copies temporaires.
• Adopter une nomenclature claire : par exemple, Work, Copy, Snapshot, Enriched, etc.
• Surveiller les coûts de stockage : CLONE ne facture que les blocs modifiés.

FAQ : duplication et CLONE sur BigQuery

Quelle différence entre CLONE et CTAS ?

→ CLONE crée une copie logique (rapide, économique), CTAS matérialise une copie physique (isolée).

Le CLONE est-il facturé ?

→ Seules les différences de données entre le clone et la source sont facturées.

Puis-je modifier un snapshot ?

→ Non, un snapshot est en lecture seule.

Combien de temps dure le Time Travel ?

→ 7 jours par défaut et qui est ajustable.

Pour finir

Bien utilisée, la duplication de tables sur BigQuery permet d’expérimenter, sauvegarder et sécuriser vos environnements de production.

CLONE pour la rapidité, CTAS pour la transformation, SNAPSHOT pour la traçabilité : trois leviers à maîtriser pour tout Data Engineer moderne.

📚 Ressources utiles

• Documentation officielle BigQuery — CREATE TABLE
• Formation SQL BigQuery — Présentiel
• Formation SQL BigQuery — Distanciel
• S’inscrire à la newsletter Datayoka

À propos de l’auteur

Je suis Bertrand (LinkedIn), Data Engineer Freelance spécialisé sur BigQuery.

J’aide les équipes à concevoir, industrialiser et optimiser leurs pipelines SQL sur BigQuery.

Formateur de plus d’une trentaine d’apprenants, je transmets une approche concrète et opérationnelle du SQL moderne.

Produits

PrestationsFormationsNewsletter

Découvrir

BlogPodcast

Ressources

ContactA propos