Accueil›Blog›Test technique Looker avancé : LookML, explores, PDTs, déploiement

Guide recrutement data

Test technique Looker avancé : LookML, explores, PDTs, déploiement

Looker est l outil BI le plus technique du marché. Sa valeur vient du LookML, le langage de modélisation sémantique. En entretien, on évalue la capacité à architecturer un modèle LookML maintenable.

Data Builder·Juin 2025·7 min de lecture·Analytics Engineer

Sommaire

Modèle sémantique LookML
Explores et joins
Persistent Derived Tables
Dimensions et mesures avancées
Git et déploiement
Performance et optimisation
Grille

1Qu est-ce que le modèle sémantique LookML

Question discriminante

Pourquoi le modèle sémantique LookML est-il la principale valeur de Looker ?

# view : définit les champs disponibles
view: orders {
  sql_table_name: analytics.fct_orders ;;

  dimension: order_id {
    type: string
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension_group: order_date {
    type: time
    timeframes: [date, week, month, quarter, year]
    sql: ${TABLE}.order_date ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: eur
    description: 'Revenu total HT'
  }

  measure: avg_basket {
    type: average
    sql: ${TABLE}.amount ;;
  }
}

Source de vérité unique — la définition de 'chiffre d affaires' est dans le LookML, pas dans chaque dashboard
Self-service — les métiers explorent les données en combinant des dimensions et mesures définies par l équipe data
Séparation — l équipe data maintient le modèle, les métiers créent leurs propres explorations

2Explores et gestion des jointures

Question discriminante

Comment organisez-vous vos explores pour qu ils restent performants ?

# model : assemble les explores
explore: orders {
  label: 'Commandes et revenus'
  description: 'Analyse des commandes, clients et produits'

  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
    relationship: many_to_one
  }

  join: products {
    type: left_outer
    sql_on: ${orders.product_id} = ${products.product_id} ;;
    relationship: many_to_one
  }

  # Restriction d accès selon le rôle
  access_filter: {
    field: customers.region
    user_attribute: user_region
  }
}

Fanout problem — joindre deux tables de faits dans un explore peut créer des doublons. Toujours joindre via la dimension
relationship — toujours définir la cardinalité (many_to_one, one_to_many). Looker optimise la requête SQL en fonction

3Persistent Derived Tables : optimiser les calculs lourds

Question discriminante

Qu est-ce qu une PDT ? Dans quel cas l utilisez-vous ?

# PDT : table précalculée stockée dans le warehouse
view: revenue_by_cohort {
  derived_table: {
    sql:
      SELECT
        DATE_TRUNC('month', first_order_date) AS cohort_month,
        DATE_TRUNC('month', order_date) AS activity_month,
        COUNT(DISTINCT user_id) AS active_users
      FROM ${orders.SQL_TABLE_NAME}
      GROUP BY 1, 2 ;;

    # Rebuild chaque nuit à 3h
    persist_for: '24 hours'
    # Ou via datagroup (déclenché par Airflow)
    # datagroup_trigger: nightly_refresh
  }
}

PDT — requête SQL complexe dont le résultat est stocké physiquement dans le warehouse. Évite de recalculer à chaque requête
Quand utiliser — calculs d agrégation lourds, cohortes, métriques complexes consultées fréquemment
Incremental PDT — ne rebuilde que les nouvelles lignes. Économise du compute sur les grosses tables

4Dimensions et mesures avancées

Question discriminante

Comment implémentez-vous une métrique de rétention à 30 jours dans LookML ?

view: orders {
  # Dimension de période relative
  dimension: days_since_last_order {
    type: number
    sql: DATE_DIFF(CURRENT_DATE, ${last_order_date}, DAY) ;;
  }

  # Mesure conditionnelle : clients actifs dans les 30 derniers jours
  measure: active_customers_30d {
    type: count_distinct
    sql: CASE WHEN ${days_since_last_order} <= 30
              THEN ${customer_id} END ;;
    drill_fields: [customer_id, last_order_date]
  }

  # Mesure de ratio
  measure: retention_rate_30d {
    type: number
    sql: ${active_customers_30d} * 1.0 / NULLIF(${total_customers}, 0) ;;
    value_format_name: percent_2
  }
}

5Git et déploiement : workflow LookML

Question discriminante

Comment gérez-vous le cycle de vie d un projet LookML en équipe ?

Git intégré — Looker est nativement connecté à Git (GitHub/GitLab). Chaque modification est un commit
Branches de développement — chaque développeur travaille sur sa branche. L UI Looker permet de switcher de branche
PR et review — revue de code sur les changements LookML avant merge sur main
lookml-validator — outil CLI pour valider le LookML en CI/CD sans l UI Looker
Environnements — pointer vers différents schémas (dev/prod) selon la branche

6Performance et optimisation Looker

Question discriminante

Comment optimisez-vous les performances d un explore Looker lent ?

Aggregate Awareness — définir des rollup tables que Looker utilise automatiquement quand la requête le permet
Caching — configurer des datagroups pour invalider le cache intelligemment
PDT pour les calculs lourds — sortir les calculs complexes du runtime
Limiter les explores — un explore trop large avec 20 vues jointes sera lent. Créer des explores spécialisés par cas d usage

# LookML : modélisation sémantique
# views/orders.view.lkml
view: orders {
  sql_table_name: analytics.fct_orders ;;
  
  dimension: order_id {
    type: string
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }
  
  dimension_group: order_date {
    type: time
    timeframes: [date, week, month, quarter, year]
    sql: ${TABLE}.order_date ;;
  }
  
  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: usd
    drill_fields: [order_id, customer_id, order_date_date, amount]
  }
  
  measure: average_order_value {
    type: average
    sql: ${TABLE}.amount ;;
    value_format_name: usd
  }
  
  # Derived table : SQL dans LookML
  derived_table: {
    sql: SELECT customer_id, COUNT(*) as order_count, SUM(amount) as ltv
         FROM analytics.fct_orders
         GROUP BY customer_id ;;
    persist_for: "24 hours"
  }
}

Explore = point d'entrée — définir quelles views un utilisateur peut joindre et comment. Un explore bien conçu = une question business précise (orders with customers, events with sessions)
PDT (Persistent Derived Tables) — matérialiser des requêtes SQL lourdes dans Looker. Rafraîchissement CRON ou trigger. Gain de performance pour les analyses récurrentes
LookML vs dbt — les deux font de la modélisation mais à des niveaux différents. dbt : transformations SQL dans le warehouse (source of truth). LookML : couche sémantique pour le BI (métriques, labels, formatage)
Git integration — tout le LookML est versionné dans Git. Développement sur branches, review en Pull Request, déploiement sur production via merge
Content Validator — outil Looker qui détecte les Looks/Dashboards cassés quand un champ LookML est renommé ou supprimé. Indispensable avant chaque déploiement

7Grille par niveau

Niveau	Maitrise	Signal GO	NO-GO
Confirmé	LookML basics, views/explores/models, dimensions/mesures	A créé un explore avec jointures, sait la différence dimension/mesure	N a fait que cliquer dans l UI Looker sans toucher au LookML
Senior	PDTs, aggregate awareness, Git workflow, access filters	A créé des PDTs, gère le cache avec datagroups, travaille en branches Git	Ne sait pas ce qu est une PDT, n a jamais utilisé Git dans Looker

1What is the LookML Semantic Model

Discriminating question

Why is the LookML semantic model Looker's primary value?

# view : defines available fields
view: orders {
  sql_table_name: analytics.fct_orders ;;

  dimension: order_id {
    type: string
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension_group: order_date {
    type: time
    timeframes: [date, week, month, quarter, year]
    sql: ${TABLE}.order_date ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: eur
    description: 'Total revenue excl. tax'
  }

  measure: avg_basket {
    type: average
    sql: ${TABLE}.amount ;;
  }
}

Single source of truth — the definition of 'revenue' lives in LookML, not in every dashboard
Self-service — business teams explore data by combining dimensions and measures defined by the data team
Separation of concerns — the data team maintains the model, business teams create their own explorations

2Explores and join management

Discriminating question

How do you organize your explores to keep them performant?

# model : assembles explores
explore: orders {
  label: 'Orders and revenue'
  description: 'Analysis of orders, customers and products'

  join: customers {
    type: left_outer
    sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
    relationship: many_to_one
  }

  join: products {
    type: left_outer
    sql_on: ${orders.product_id} = ${products.product_id} ;;
    relationship: many_to_one
  }

  # Access restriction based on role
  access_filter: {
    field: customers.region
    user_attribute: user_region
  }
}

Fanout problem — joining two fact tables in an explore can create duplicates. Always join through the dimension
relationship — always define cardinality (many_to_one, one_to_many). Looker optimizes the SQL query accordingly

3Persistent Derived Tables: optimizing heavy computations

Discriminating question

What is a PDT? In what cases do you use it?

# PDT : pre-computed table stored in the warehouse
view: revenue_by_cohort {
  derived_table: {
    sql:
      SELECT
        DATE_TRUNC('month', first_order_date) AS cohort_month,
        DATE_TRUNC('month', order_date) AS activity_month,
        COUNT(DISTINCT user_id) AS active_users
      FROM ${orders.SQL_TABLE_NAME}
      GROUP BY 1, 2 ;;

    # Rebuild every night at 3am
    persist_for: '24 hours'
    # Or via datagroup (triggered by Airflow)
    # datagroup_trigger: nightly_refresh
  }
}

PDT — complex SQL query whose result is physically stored in the warehouse. Avoids recomputing on every query
When to use — heavy aggregation computations, cohorts, complex metrics queried frequently
Incremental PDT — only rebuilds new rows. Saves compute on large tables

4Advanced dimensions and measures

Discriminating question

How do you implement a 30-day retention metric in LookML?

view: orders {
  # Relative period dimension
  dimension: days_since_last_order {
    type: number
    sql: DATE_DIFF(CURRENT_DATE, ${last_order_date}, DAY) ;;
  }

  # Conditional measure : active customers in the last 30 days
  measure: active_customers_30d {
    type: count_distinct
    sql: CASE WHEN ${days_since_last_order} <= 30
              THEN ${customer_id} END ;;
    drill_fields: [customer_id, last_order_date]
  }

  # Ratio measure
  measure: retention_rate_30d {
    type: number
    sql: ${active_customers_30d} * 1.0 / NULLIF(${total_customers}, 0) ;;
    value_format_name: percent_2
  }
}

5Git and deployment: LookML workflow

Discriminating question

How do you manage the lifecycle of a LookML project as a team?

Built-in Git — Looker is natively connected to Git (GitHub/GitLab). Every change is a commit
Development branches — each developer works on their own branch. The Looker UI allows switching branches
PR and review — code review on LookML changes before merging to main
lookml-validator — CLI tool to validate LookML in CI/CD without the Looker UI
Environments — point to different schemas (dev/prod) depending on the branch

6Looker performance and optimization

Discriminating question

How do you optimize the performance of a slow Looker explore?

Aggregate Awareness — define rollup tables that Looker uses automatically when the query allows it
Caching — configure datagroups to invalidate cache intelligently
PDT for heavy computations — move complex calculations out of runtime
Limit explores — an overly broad explore with 20 joined views will be slow. Create specialized explores per use case

# LookML : semantic modeling
# views/orders.view.lkml
view: orders {
  sql_table_name: analytics.fct_orders ;;
  
  dimension: order_id {
    type: string
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }
  
  dimension_group: order_date {
    type: time
    timeframes: [date, week, month, quarter, year]
    sql: ${TABLE}.order_date ;;
  }
  
  measure: total_revenue {
    type: sum
    sql: ${TABLE}.amount ;;
    value_format_name: usd
    drill_fields: [order_id, customer_id, order_date_date, amount]
  }
  
  measure: average_order_value {
    type: average
    sql: ${TABLE}.amount ;;
    value_format_name: usd
  }
  
  # Derived table : SQL in LookML
  derived_table: {
    sql: SELECT customer_id, COUNT(*) as order_count, SUM(amount) as ltv
         FROM analytics.fct_orders
         GROUP BY customer_id ;;
    persist_for: "24 hours"
  }
}

Explore = entry point — defines which views a user can join and how. A well-designed explore = one precise business question (orders with customers, events with sessions)
PDT (Persistent Derived Tables) — materialize heavy SQL queries in Looker. CRON refresh or trigger-based. Performance gain for recurring analyses
LookML vs dbt — both do modeling but at different levels. dbt: SQL transformations in the warehouse (source of truth). LookML: semantic layer for BI (metrics, labels, formatting)
Git integration — all LookML is versioned in Git. Development on branches, review via Pull Request, deployment to production via merge
Content Validator — Looker tool that detects broken Looks/Dashboards when a LookML field is renamed or deleted. Essential before every deployment

7Level grid

Level	Mastery	GO signal	NO-GO
Mid-level	LookML basics, views/explores/models, dimensions/measures	Has created an explore with joins, knows the difference between dimension/measure	Has only clicked in the Looker UI without touching LookML
Senior	PDTs, aggregate awareness, Git workflow, access filters	Has created PDTs, manages cache with datagroups, works on Git branches	Does not know what a PDT is, has never used Git in Looker

Vous recrutez un Analytics Engineer Looker ?

Premier entretien gratuit. Rapport GO/NO-GO sous 48h.

Tester gratuitement Reserver un appel