Correction détaillée - Exercices d’entraînement avancés DW
Exercice 1 : Data Warehouse bancaire - Correction
A. Architecture et composants
1. Architecture complète
Sources de données :
- Systèmes core banking (comptes, transactions)
- Systèmes de crédit (prêts, garanties)
- CRM (données clients)
- Systèmes de paiement (TPE, cartes)
- Sources externes (taux de change, données économiques)
Zone de staging :
- Landing zone : Réception des données brutes des 5 systèmes
- Transformation zone :
- Conversion des formats legacy vers standards
- Nettoyage des données (doublons, valeurs manquantes)
- Validation des contraintes métier
- Archive zone : Conservation des données brutes pour audit
Couche d’intégration :
- Master Data Management : Référentiel unique clients/produits
- Harmonisation des devises : Conversion vers devise de référence
- Application des règles métier : Classification clients, calcul des risques
- Data Quality Management : Contrôles de cohérence inter-systèmes
Core Data Warehouse :
- Modèle normalisé (approche Inmon) :
- Tables clients, comptes, produits, transactions
- Historisation complète (10 ans requis)
- Gestion des versions de données pour audit
- Métadonnées : Lignage des données, règles de calcul, définitions métier
Data Marts :
- Mart Retail Banking : Analyse des comptes particuliers
- Mart Corporate Banking : Analyse des comptes entreprises
- Mart Risk Management : Calculs de risque et provisioning
- Mart Regulatory : Reporting réglementaire par pays
2. Défis ETL spécifiques
Extraction :
- Systèmes legacy : Formats mainframe, COBOL copybooks
- Fréquences différentes : Real-time pour fraude, batch nightly pour reporting
- Volumes importants : Millions de transactions/jour
- Disponibilité : Extraction sans impact sur systèmes opérationnels
Transformation :
- Standardisation : Codes produits différents entre systèmes
- Dédoublonnage : Client présent dans plusieurs systèmes
- Enrichissement : Géocodage des adresses, scoring client
- Validation : Contrôles de cohérence comptable (débits = crédits)
Load :
- Gestion des SCD : Historisation des changements clients/produits
- Performance : Window de batch limitée (2h max)
- Rollback : Mécanisme de retour arrière en cas d’échec
3. Stratégie taux de change
Architecture multi-temporelle :
Table EXCHANGE_RATES
- date_effective
- currency_from
- currency_to
- rate
- rate_type (closing, average, buying, selling)
- source_system
Stratégies de conversion :
- Transaction date : Taux au moment de la transaction (historique)
- Reporting date : Taux à la date du rapport (comparaison)
- Average rate : Taux moyen mensuel (lissage des variations)
Gestion dans les faits :
- Montants stockés en devise locale ET devise de référence
- Métadonnée du taux utilisé pour audit
- Recalcul possible avec nouveaux taux
B. Modélisation dimensionnelle
1. Application des 4 étapes de Kimball
Étape 1 - Processus métier : Analyse des transactions bancaires
Étape 2 - Granularité : Une ligne par transaction individuelle
- Transaction ID unique
- Horodatage précis (seconde)
- Montant exact
- Comptes débiteur/créditeur
Étape 3 - Dimensions :
- DIM_DATE : Jour, mois, trimestre, année, jour ouvré
- DIM_CUSTOMER : Données client avec historique
- DIM_ACCOUNT : Type compte, statut, devise
- DIM_PRODUCT : Famille produit, caractéristiques
- DIM_CHANNEL : Agence, web, mobile, TPE
- DIM_GEOGRAPHY : Pays, région, ville (client et transaction)
Étape 4 - Faits/Mesures :
- Montant_devise_locale (additive)
- Montant_devise_reference (additive)
- Nombre_transactions (additive)
- Solde_compte (semi-additive - pas de somme sur temps)
- Taux_marge (non-additive - ratio)
2. Schéma en étoile
FACT_TRANSACTION
├── transaction_key (PK)
├── date_key (FK)
├── customer_key (FK)
├── account_debit_key (FK)
├── account_credit_key (FK)
├── product_key (FK)
├── channel_key (FK)
├── geography_key (FK)
├── amount_local_currency (mesure additive)
├── amount_reference_currency (mesure additive)
├── exchange_rate_used (mesure non-additive)
├── account_balance_after (mesure semi-additive)
└── transaction_count (mesure additive = 1)
3. Gestion SCD
Changement d’adresse client - SCD Type 2 :
DIM_CUSTOMER
├── customer_key (PK surrogate)
├── customer_business_key (NK)
├── customer_name
├── address
├── effective_date
├── expiry_date
├── current_flag
└── version_number
Justification : Analyses géographiques nécessitent historique complet
Modification produit - SCD Type 1 :
- Corrections d’erreurs : écrasement direct
- Mise à jour descriptions : pas d’impact analytique
Changement de taux d’intérêt - SCD Type 3 :
DIM_PRODUCT
├── current_interest_rate
├── previous_interest_rate
└── rate_change_date
C. Défis complexes
1. Relations many-to-many (Comptes multi-titulaires)
Solution : Bridge Table
BRIDGE_ACCOUNT_CUSTOMER
├── account_key (FK)
├── customer_key (FK)
├── ownership_percentage
├── role (titular, authorized, beneficiary)
├── effective_date
└── expiry_date
FACT_TRANSACTION (modifiée)
├── account_key → bridge_account_customer_key
├── allocation_factor (pour répartir les montants)
Allocation des transactions :
- Répartition proportionnelle selon pourcentages
- Mesures additives conservées
- Possibilité d’analyse par titulaire principal
2. Granularité multiple
Solution : Architecture Lambda
Batch Layer (analyses quotidiennes) :
FACT_TRANSACTION_DAILY
├── date_key
├── customer_key
├── account_key
├── daily_transaction_count
├── daily_total_amount
├── daily_avg_amount
└── end_of_day_balance
Speed Layer (détection fraude temps réel) :
FACT_TRANSACTION_STREAMING
├── transaction_timestamp
├── customer_key
├── amount
├── merchant_category
├── risk_score (calculé en temps réel)
└── fraud_flag
Serving Layer :
- Données batch pour reporting standard
- Données streaming pour alertes
- Réconciliation périodique
3. Conformité réglementaire
Audit Trail complet :
AUDIT_LOG
├── audit_id
├── table_name
├── record_key
├── operation_type (INSERT/UPDATE/DELETE)
├── old_values (JSON)
├── new_values (JSON)
├── user_id
├── timestamp
├── business_reason
└── regulation_reference
Data Lineage :
DATA_LINEAGE
├── target_table
├── target_column
├── source_system
├── source_table
├── source_column
├── transformation_rule
├── last_update
└── validation_status
RGPD - Droit à l’effacement :
- Pseudonymisation des identifiants
- Soft delete avec marqueurs
- Logs de suppression
- Impact analysis avant suppression
Exercice 2 : Optimisation OLAP - Correction
A. Séquences d’opérations OLAP
1. “Évolution smartphones France vs Allemagne”
Séquence détaillée :
- SLICE sur dimension Géographie = “France”
- SLICE sur dimension Produit = “Smartphones”
- ROLL-UP sur dimension Temps vers “Trimestre”
- PIVOT pour avoir Trimestres en colonnes, Mesures en lignes
- DICE pour ajouter Géographie = “Allemagne”
- PIVOT final : Pays en lignes, Trimestres en colonnes
Requête MDX équivalente :
SELECT
{[Time].[Trimester].Members} ON COLUMNS,
{[Geography].[France], [Geography].[Germany]} ON ROWS
FROM Sales
WHERE ([Product].[Smartphones])
2. “Ventes par catégorie 2023, puis détail mensuel top performers”
Séquence détaillée :
- SLICE sur dimension Temps = “2023”
- ROLL-UP sur dimension Produit vers “Catégorie”
- Analyse des résultats → identification top 3 catégories
- DRILL-DOWN sur dimension Temps vers “Mois”
- FILTER pour garder seulement les top 3 catégories
- PIVOT : Mois en colonnes, Catégories en lignes
3. “Paris vs Londres, puis B2B vs B2C”
Séquence détaillée :
- DICE sur dimension Géographie = {“Paris”, “Londres”}
- ROLL-UP sur dimension Géographie vers “Ville”
- Analyse comparative entre villes
- DRILL-ACROSS vers dimension Client
- SLICE successive sur Type_Client = “B2B” puis “B2C”
- PIVOT pour créer matrice Ville × Type_Client
B. Choix de schémas et performance
1. Performance vs Storage
Recommandation : Schéma en étoile
Justifications :
- Requêtes typiques : Analyses cross-dimensionnelles fréquentes
- Performance critique : Retail nécessite réponses sub-secondes
- Volumes : Dimensions relativement stables, explosion des faits
- Utilisateurs : Business analysts non-techniques
Cas d’exception Snowflake :
- Dimension Géographie si hiérarchies très profondes
- Dimension Produit si catalogues très riches (mode/high-tech)
2. Dénormalisation recommandée
Dimension Temps :
DIM_TIME (dénormalisée)
├── date_key
├── full_date
├── day_name, day_abbrev
├── day_of_week, day_of_month, day_of_year
├── week_of_year, week_of_month
├── month_name, month_abbrev, month_number
├── quarter_name, quarter_number
├── year_number
├── is_weekend, is_holiday
├── season_name
└── fiscal_year, fiscal_quarter
Justification : Dimension de référence, interrogée dans 90% des requêtes
Dimension Géographie :
DIM_GEOGRAPHY (partiellement dénormalisée)
├── geography_key
├── store_name, store_code
├── store_address (complet)
├── city_name, city_code
├── region_name, region_code
├── country_name, country_code
├── continent_name
├── timezone, currency
├── store_type, store_size_category
└── opening_date
3. Stratégie de partitionnement
Partitionnement horizontal par date :
FACT_SALES
├── PARTITION sales_2024_Q1 (Jan-Mar 2024)
├── PARTITION sales_2024_Q2 (Apr-Jun 2024)
├── PARTITION sales_2024_Q3 (Jul-Sep 2024)
├── PARTITION sales_2024_Q4 (Oct-Dec 2024)
└── PARTITION sales_2025_Q1 (Jan-Mar 2025)
Avantages :
- Partition pruning : Élimination automatique des partitions
- Maintenance : Chargement parallèle, archivage simple
- Performance : Scans limités aux périodes pertinentes
Sous-partitionnement par géographie :
SUBPARTITION BY HASH(geography_key) SUBPARTITIONS 8
C. Gestion des hiérarchies complexes
1. Hiérarchie géographique irrégulière
Problème : Zones économiques spéciales (Monaco, Hong Kong, etc.)
Solution : Hiérarchies multiples
DIM_GEOGRAPHY_BRIDGE
├── geography_key (FK)
├── hierarchy_type ('administrative', 'commercial', 'fiscal')
├── parent_geography_key
├── level_number
├── level_name
└── path_description
Exemple d’utilisation :
- Monaco → France (administratif)
- Monaco → Zone Euro (commercial)
- Monaco → Paradis fiscal (fiscal)
2. Hiérarchie produit multiple
Solution : Dimensions multiples
DIM_PRODUCT_USAGE
├── product_key (FK to main product dim)
├── usage_category
├── usage_subcategory
└── target_demographic
DIM_PRODUCT_PRICE
├── product_key (FK)
├── price_range
├── price_tier
└── competitive_position
DIM_PRODUCT_SEASON
├── product_key (FK)
├── season_primary
├── season_secondary
└── seasonality_index
Fact table adaptée :
FACT_SALES
├── product_key (FK to main dimension)
├── product_usage_key (FK to usage dimension)
├── product_price_key (FK to price dimension)
├── product_season_key (FK to season dimension)
Exercice 3 : Migration architecture - Correction
A. Architecture hybride
1. Architecture intégrée
Couche Legacy (maintenue) :
┌─────────────────┐
│ DW Existant │
│ (Inmon/3NF) │ ──────┐
└─────────────────┘ │
│
┌─────────────────┐ │ ┌──────────────────┐
│ Data Marts │ │ │ Data Lake │
│ Finance/Ops │ ──────┼───→│ Raw IoT Data │
│ R&D │ │ │ Archives │
└─────────────────┘ │ └──────────────────┘
│
↓
┌──────────────────┐
│ Integration │
│ Layer (New) │
└──────────────────┘
│
┌─────────────────────────────────────────┐
│ │
↓ ↓
┌─────────────────┐ ┌──────────────────┐
│ Streaming │ │ ML/AI Layer │
│ Platform │ │ Feature Store │
│ (Kafka/Flink) │ │ Model Registry │
└─────────────────┘ └──────────────────┘
Nouvelles couches :
Data Lake (HDFS/S3) :
- Raw zone : IoT brut, logs, fichiers
- Refined zone : Données nettoyées, partitionnées
- Curated zone : Données préparées pour ML
Streaming Platform :
- Kafka : Ingestion temps réel IoT
- Flink/Spark Streaming : Transformations en vol
- Redis : Cache des métriques temps réel
ML/AI Layer :
- Feature Store : Features calculées batch + streaming
- Model Registry : Versioning des modèles ML
- Prediction API : Scoring temps réel
2. Stratégie de migration
Phase 1 (3 mois) - Coexistence :
- Déploiement du Data Lake en parallèle
- Duplication des données critiques (DW → Lake)
- Tests de performance et qualité
- Formation des équipes
Phase 2 (6 mois) - Streaming IoT :
- Mise en place pipeline IoT → Lake
- Développement APIs temps réel
- Intégration progressive avec systèmes existants
- Monitoring et alertes
Phase 3 (9 mois) - ML Integration :
- Déploiement Feature Store
- Migration modèles ML existants
- Nouveaux modèles exploitant données temps réel
- Optimisation performances
Phase 4 (12 mois) - Optimisation :
- Rationalisation des flux de données
- Décommissionnement composants legacy
- Gouvernance unifée
- Formation utilisateurs finaux
3. Gouvernance des données
Data Catalog unifié :
UNIFIED_CATALOG
├── asset_id (global unique)
├── asset_type (table, stream, model, api)
├── source_system
├── business_owner
├── technical_owner
├── data_classification
├── retention_policy
├── lineage_upstream
├── lineage_downstream
├── quality_score
├── last_accessed
└── compliance_tags
Data Quality Framework :
- Règles communes : Validation cross-systèmes
- Monitoring unifié : Dashboard qualité global
- Alerting : Notifications proactives sur anomalies
- Remediation : Workflows automatisés de correction
B. Modélisation évolutive
1. SCD complexes - Firmware devices
Problème : Un capteur change de firmware, impactant la précision des mesures historiques.
Solution : SCD Type 2 avec métadonnées techniques
DIM_DEVICE
├── device_key (PK surrogate)
├── device_serial_number (NK)
├── device_model
├── firmware_version
├── calibration_date
├── accuracy_class
├── effective_date
├── expiry_date
├── is_current
├── change_reason
└── backward_compatibility_flag
Impact sur analyse historique :
- Recalibration : Facteurs de correction appliqués rétroactivement
- Traceability : Marquage des périodes affectées
- Reporting : Notes explicatives sur changements méthodologiques
2. Événements vs États IoT
Recommandation : Tables séparées avec pont
FACT_IOT_EVENTS (détail brut) :
├── event_timestamp
├── device_key
├── sensor_type
├── raw_value
├── processed_value
├── quality_flag
└── event_sequence_number
FACT_DEVICE_SNAPSHOTS (états agrégés) :
├── snapshot_timestamp (every 5min)
├── device_key
├── avg_temperature
├── max_pressure
├── battery_level
├── operational_status
└── alert_count
Justification :
- Performance : Snapshots pour analyses rapides
- Auditabilité : Events pour traçabilité complète
- Flexibilité : Recalcul possible des snapshots
3. Agrégations pré-calculées
Stratégie par fréquence d’usage :
Très haute fréquence (sub-seconde) :
REALTIME_METRICS (Redis)
├── device_id
├── current_value
├── trend_5min
├── anomaly_score
└── last_update
Haute fréquence (minutes) :
FACT_DEVICE_5MIN
├── time_bucket (5min intervals)
├── device_key
├── avg_value, min_value, max_value
├── std_deviation
├── sample_count
└── quality_percentage
Fréquence standard (heures/jours) :
- Agrégations batch dans DW traditionnel
- Cubes OLAP pour analyses multi-dimensionnelles
C. Conformité et sécurité
1. RGPD - Droit à l’oubli
Défis techniques dans DW :
- Données dénormalisées : Éparpillement dans multiple tables
- Agrégations : Impact sur métriques pré-calculées
- Sauvegardes : Purge des backups et archives
- Logs : Nettoyage des traces d’audit
Solution technique :
-- Table de mapping pour pseudonymisation
PATIENT_PSEUDONYM_MAP
├── patient_business_key (encrypted)
├── pseudonym_key (used in DW)
├── encryption_key_version
├── creation_date
├── deletion_request_date
└── deletion_completed_date
-- Processus de suppression
DELETION_IMPACT_ANALYSIS
├── patient_pseudonym_key
├── affected_table
├── affected_records_count
├── aggregation_impact
├── deletion_status
└── completion_timestamp
Processus automatisé :
- Requête de suppression → Identification pseudonyme
- Impact analysis → Recensement données affectées
- Validation métier → Confirmation faisabilité
- Suppression technique → Exécution par lot
- Vérification → Contrôle absence résiduelle
- Documentation → Preuve de conformité
2. Anonymisation recherche médicale
Techniques par type de données :
Données directement identifiantes :
- Suppression : Noms, adresses, numéros
- Hachage : Identifiants techniques avec sel
Données quasi-identifiantes :
- Généralisation : Âge exact → Tranche d’âge
- Perturbation : Ajout bruit statistique dates
- Suppression sélective : Codes postaux rares
Données sensibles :
- k-anonymat : Minimum k individus par combinaison
- l-diversité : Minimum l valeurs différentes par attribut sensible
- t-closeness : Distribution proche population générale
3. Audit trail
Architecture de traçabilité :
ACCESS_LOG
├── session_id
├── user_id
├── query_hash
├── tables_accessed[]
├── rows_returned
├── execution_time
├── access_timestamp
└── business_justification
DATA_MODIFICATION_LOG
├── transaction_id
├── table_name
├── operation_type
├── affected_keys[]
├── old_checksum
├── new_checksum
├── modification_timestamp
├── authorized_by
└── business_reason
Monitoring proactif :
- Accès anormaux : Volumes, heures, utilisateurs
- Modifications sensibles : Données critiques, privilégiées
- Performance : Requêtes coûteuses, ressources
- Conformité : Respect des politiques de rétention
Exercice 4 : Plateforme streaming - Correction
A. Architecture multi-vitesse
1. Lambda architecture détaillée
SPEED LAYER (Real-time)
┌─────────────────────────────────────────────────────────┐
│ Kafka Streams │ Flink CEP │ Real-time ML │
│ ↓ │ ↓ │ ↓ │
│ Redis/Hazelcast │ Alerts │ Recommendations │
└─────────────────────────────────────────────────────────┘
↓
SERVING LAYER
┌─────────────────────────────────────────────────────────┐
│ API Gateway (GraphQL/REST) │
│ ┌─────────────┬─────────────┬─────────────────────────┐│
│ │ User API │ Artist API │ Analytics API ││
│ │ │ │ ││
│ │ - Profile │ - Royalties │ - Dashboards ││
│ │ - Playlists │ - Stats │ - Reports ││
│ │ - Reco │ - Trends │ - A/B Tests ││
│ └─────────────┴─────────────┴─────────────────────────┘│
└─────────────────────────────────────────────────────────┘
↑
BATCH LAYER
┌─────────────────────────────────────────────────────────┐
│ Master Dataset (HDFS/S3) │
│ ┌─────────────────┬─────────────────┬─────────────────┐│
│ │ Raw Events │ Curated │ Aggregated ││
│ │ │ │ ││
│ │ - Plays │ - User Sessions │ - Daily Stats ││
│ │ - Clicks │ - Clean Tracks │ - Artist Metrics││
│ │ - Interactions │ - Enriched │ - Genre Trends ││
│ └─────────────────┴─────────────────┴─────────────────┘│
└─────────────────────────────────────────────────────────┘
Speed Layer (< 100ms) :
- Kafka : 10M events/sec, partitioning par user_id
- Flink CEP : Pattern detection (skip patterns, repeat listening)
- Feature Store : Pré-calcul features pour ML temps réel
Batch Layer (minutes à heures) :
- Spark : Processing des 100M streams/jour
- Hive/Presto : Requêtes analytiques complexes
- Airflow : Orchestration des pipelines batch
2. Hot/Warm/Cold storage
Hot Storage (0-7 jours) - SSD/Memory :
- User current sessions (Redis)
- Real-time recommendations (Hazelcast)
- Trending tracks cache (Memcached)
- Anti-fraud models (in-memory)
Warm Storage (7 jours - 2 ans) - SSD :
- Recent plays for personalization
- User behavior analytics
- A/B test data
- Operational metrics
Cold Storage (> 2 ans) - Archive :
- Historical plays for ML training
- Deleted user data (retention légale)
- System logs and audit trails
- Raw event backups
Politiques automatisées :
- Tiering : Migration automatique selon âge et accès
- Compression : Ratios différents par température
- Indexing : Index complets hot, partiels warm, minimaux cold
3. APIs et exposition
GraphQL API (Unified) :
type User {
id: ID!
profile: UserProfile
currentPlaying: Track
recommendations: [Track!]!
playlists: [Playlist!]!
listeningHistory(limit: Int): [PlayEvent!]!
}
type Query {
user(id: ID!): User
search(query: String!): SearchResults
trending(genre: Genre, geo: Country): [Track!]!
analytics(metric: Metric, period: Period): AnalyticsResult
}
type Subscription {
userActivity(userId: ID!): PlayEvent
trendingUpdates: TrendingUpdate
}
REST APIs spécialisées :
- Ingestion API : POST /events (haute throughput)
- Royalties API : GET /royalties/{artist_id} (calculs complexes)
- Admin API : Gestion contenu, utilisateurs (sécurisée)
B. Modélisation multi-granularité
1. Fact tables multiples
FACT_PLAY_EVENTS (détail maximum) :
├── play_id (PK)
├── user_id (FK)
├── track_id (FK)
├── session_id (FK)
├── device_id (FK)
├── timestamp_start
├── timestamp_end
├── duration_played_seconds
├── skip_reason_code
├── audio_quality
├── geographical_location
├── playlist_context_id
├── recommendation_algorithm_id
└── user_interaction_flags (JSON)
FACT_USER_DAILY_ACTIVITY (agrégation utilisateur) :
├── activity_date
├── user_id (FK)
├── total_listening_time_minutes
├── unique_tracks_played
├── unique_artists_played
├── skip_rate_percentage
├── discovery_rate_new_tracks
├── avg_session_duration_minutes
├── device_switches_count
├── playlist_interactions_count
└── social_shares_count
FACT_ARTIST_ROYALTIES (agrégation artiste) :
├── calculation_period_start
├── calculation_period_end
├── artist_id (FK)
├── country_id (FK)
├── total_streams
├── unique_listeners
├── revenue_generated_usd
├── revenue_per_stream
├── top_track_id
├── growth_rate_vs_previous_period
└── royalty_splits (JSON - multi-rights holders)
2. Dimensions évolutives
Catalog dynamique avec versioning :
DIM_TRACK
├── track_key (PK surrogate)
├── track_id (NK business)
├── title
├── artist_primary_id (FK)
├── album_id (FK)
├── duration_seconds
├── explicit_flag
├── availability_countries (JSON array)
├── genre_primary, genre_secondary
├── mood_tags (JSON array)
├── bpm (beats per minute)
├── key_signature
├── energy_level (1-10)
├── danceability_score (0-1)
├── acousticness_score (0-1)
├── popularity_score (calculated daily)
├── release_date
├── label_id (FK)
├── isrc_code
├── copyright_info
├── effective_date
├── expiry_date
├── is_current_version
├── version_reason
└── audio_fingerprint_hash
Gestion des suppressions du catalogue :
DIM_TRACK_TOMBSTONE
├── track_id
├── removal_date
├── removal_reason (rights, quality, legal)
├── replacement_track_id (si applicable)
├── impact_analysis_completed
└── user_notification_sent
Métadonnées enrichies dynamiquement :
FACT_TRACK_METADATA_EVOLUTION
├── track_id (FK)
├── metadata_date
├── mood_scores (JSON - ML generated)
├── similarity_clusters (JSON - algorithmic)
├── user_generated_tags (JSON - crowdsourced)
├── cultural_context (JSON - regional preferences)
├── seasonal_popularity_pattern (JSON)
└── recommendation_contexts (JSON)
3. Bridge tables - Playlists collaboratives
Gestion many-to-many complexe :
BRIDGE_PLAYLIST_CONTRIBUTORS
├── playlist_id (FK)
├── user_id (FK)
├── contributor_role (owner, editor, viewer, collaborator)
├── permission_level (add, remove, reorder, share)
├── contribution_weight (for analytics allocation)
├── joined_date
├── last_activity_date
├── invitation_status (pending, accepted, declined)
└── notification_preferences (JSON)
BRIDGE_PLAYLIST_TRACKS
├── playlist_id (FK)
├── track_id (FK)
├── added_by_user_id (FK)
├── position_order
├── added_timestamp
├── play_count_from_playlist
├── skip_count_from_playlist
├── likes_from_playlist
├── removal_timestamp (soft delete)
├── removal_reason
└── recommendation_source (manual, algorithmic, social)
Allocation des métriques pour analytics :
- Plays weighted by contribution : Répartition selon poids contributeurs
- Discovery attribution : Crédit à l’ajouteur du track
- Engagement metrics : Agrégation par rôle dans la playlist
C. Optimisations avancées
1. Columnar storage
Données à stocker en colonaire (Parquet/ORC) :
Event stream data :
OPTIMAL FOR COLUMNAR:
- user_id (high selectivity, frequent filtering)
- track_id (joins fréquents avec catalogue)
- timestamp (range queries constantes)
- geographical_data (analyses par région)
- device_type (segmentation comportementale)
SUBOPTIMAL FOR COLUMNAR:
- event_metadata_json (peu de patterns répétitifs)
- user_agent_string (très haute cardinalité)
- session_context (accès row-wise fréquent)
Partitioning strategy :
-- Partitioning hybride par date + géographie
PARTITION BY (
year = YEAR(event_timestamp),
month = MONTH(event_timestamp),
geo_region = CASE
WHEN country IN ('US','CA','MX') THEN 'AMERICAS'
WHEN country IN ('GB','FR','DE','ES','IT') THEN 'EUROPE'
ELSE 'OTHER'
END
)
CLUSTERED BY user_id INTO 256 BUCKETS
Compression par type de donnée :
- Timestamps : Delta encoding + ZSTD
- IDs : Dictionary encoding + Snappy
- Metrics : RLE (Run Length Encoding) pour valeurs répétées
- Text : LZ4 pour performance décompression
2. Stratégies de compression
Tiered compression selon usage :
HOT DATA (0-7 days):
- Compression: LZ4 (fast decompression)
- Ratio: ~3:1
- Priority: Query speed over storage
WARM DATA (7 days - 6 months):
- Compression: ZSTD level 3
- Ratio: ~7:1
- Balance: Speed/Storage
COLD DATA (> 6 months):
- Compression: ZSTD level 19 + columnar
- Ratio: ~15:1
- Priority: Storage cost over speed
Compression spécialisée par domaine :
- Audio metadata : Similarity-based clustering puis compression
- User behavior patterns : Sequence compression algorithms
- Geographical data : Spatial indexing + coordinate quantization
3. Caching multi-niveaux
L1 Cache - Application Level (Redis Cluster) :
# User current context (TTL: 30min)
user:123456:current_session -> {
"current_track": "track789",
"queue": ["track790", "track791"],
"position": 120,
"preferences": {...}
}
# Hot tracks cache (TTL: 5min)
trending:genre:rock:country:US -> [
{"track_id": "abc123", "score": 95.2},
{"track_id": "def456", "score": 92.1}
]
# Recommendation cache (TTL: 1hour)
reco:user:123456 -> [
{"track_id": "xyz789", "reason": "similar_users", "confidence": 0.87}
]
L2 Cache - Query Result Cache (Hazelcast) :
// Complex aggregations cache (TTL: 4hours)
analytics🧑🎨456:daily:2024-01-15 -> {
"streams": 1523420,
"unique_listeners": 45231,
"countries": {...},
"age_demographics": {...}
}
// Catalog metadata cache (TTL: 24hours)
track:789:metadata -> {
"title": "Song Title",
"duration": 240,
"features": {...}
}
L3 Cache - CDN Level (CloudFlare/CloudFront) :
- Static assets : Album covers, artist images
- API responses : Public charts, genre listings
- Computed playlists : Daily/weekly algorithmic playlists
Cache invalidation strategies :
EVENT-DRIVEN INVALIDATION:
- Track metadata change → Invalidate all related caches
- User behavior update → Invalidate personal recommendations
- Chart updates → Invalidate trending caches
TIME-BASED INVALIDATION:
- User session data: 30 minutes
- Recommendations: 1-4 hours selon user activity
- Analytics: 4-24 hours selon criticité métier
PROBABILISTIC INVALIDATION:
- 5% chance early refresh before TTL expire
- Prevents cache stampede on popular items
Conseils pédagogiques et points d’évaluation
Critères d’évaluation par exercice
Exercice 1 (Banque) - Critères
Excellent (90-100%) :
- Architecture complète avec justification de chaque composant
- Gestion SCD appropriée avec compréhension métier
- Solutions créatives pour many-to-many (bridge tables)
- Prise en compte réglementaire (RGPD, audit)
Bon (75-89%) :
- Architecture cohérente avec composants principaux
- Modélisation dimensionnelle correcte (4 étapes Kimball)
- Gestion basique des SCD
- Identification des défis ETL
Satisfaisant (60-74%) :
- Structure basique data warehouse
- Schéma en étoile simple
- Compréhension concepts fondamentaux
Exercice 2 (Retail) - Critères
Excellent :
- Séquences OLAP détaillées et logiques
- Justifications techniques pour choix architecture
- Solutions innovantes pour hiérarchies complexes
- Stratégies optimisation avancées
Bon :
- Opérations OLAP correctes
- Choix étoile vs flocon justifié
- Partitioning basique proposé
Satisfaisant :
- Identification opérations OLAP principales
- Compréhension schémas de base
Exercice 3 (HealthTech) - Critères
Excellent :
- Architecture lambda/kappa bien conçue
- Stratégies migration réalistes
- Gestion RGPD technique détaillée
- Vision intégrée temps réel/batch
Bon :
- Coexistence legacy/moderne planifiée
- Compréhension enjeux conformité
- Solutions data lake appropriées
Satisfaisant :
- Identification composants nouveaux
- Compréhension besoins évolution
Exercice 4 (Streaming) - Critères
Excellent :
- Architecture distribuée cohérente haute performance
- Modélisation multi-granularité sophistiquée
- Optimisations techniques avancées (compression, cache, columnar)
- Compréhension business model streaming
Bon :
- Architecture big data basique
- Tables de faits multiples appropriées
- Stratégies cache simples
Satisfaisant :
- Identification défis volumétrie
- Modèles dimensionnels de base
Pierre-Henri Paris
Associate Professor in Artificial Intelligence
My research interests include Knowlegde Graphs, Information Extraction, and NLP.