Google PDE: Analisi con BigQuery e Ingegneria del Data Warehouse — Guida allo studio

By Laurynas Brazys · 21 August 2026 · #google #PDE #certification #exam-prep #study-guide

Fa parte della Google Professional Data Engineer — Guida allo studio. Esercitati con risposte verificate nel centro esami Google, oppure fai test cronometrati su ExamRoll.io.

Panoramica

BigQuery è un warehouse di analisi MPP (Massively Parallel Processing) serverless e colonnare che separa lo storage dal calcolo, fornendo scalabilità quasi infinita, SQL ANSI e governance integrata. L’ingegneria del warehouse su BigQuery bilancia la progettazione dello schema (partizionamento, clustering, denormalizzazione vs normalizzazione, record nidificati), i modelli di ingestione (caricamenti batch, streaming, Storage Write API) e la gestione del carico di lavoro (edizioni on-demand o basate sulla capacità e prenotazioni). Una sicurezza robusta (viste autorizzate, policy a livello di riga/colonna, policy tag) coesiste con controlli dei costi e strumenti per le prestazioni per minimizzare i byte scansionati e ridurre la latenza. Questa sezione tratta la progettazione di base, le operazioni e le modalità di fallimento che è necessario prevedere in produzione.

Storage e Semantica: Dataset, Tabelle, Viste e Accesso al Lake

• Dataset, tabelle, viste:

  • I dataset definiscono l’ambito per IAM e la governance. Mantenere dataset per-tenant per l’isolamento e la chiarezza nella fatturazione.
  • Le tabelle standard archiviano i dati in modo nativo; il partizionamento e il clustering governano il layout e il pruning.
  • Le viste incapsulano la logica SQL senza archiviare dati. Le viste autorizzate consentono al proprietario di una vista di esporre sottoinsiemi ristretti ad altri progetti o tenant, nascondendo le tabelle sottostanti.
  • Le viste materializzate (MV) persistono risultati precalcolati e si aggiornano automaticamente. La riscrittura della query (query rewrite) utilizza le MV in modo trasparente quando compatibili; predicati o funzioni incompatibili le bypassano.
  • Le tabelle esterne fanno riferimento a dati in Cloud Storage, Google Drive o Google Sheets. Evitano l’ingestione ma sacrificano il throughput e il supporto delle funzioni in cambio della convenienza. Per analisi ripetute, eseguire l’ingestione in tabelle native.

• Partizionamento, clustering e record nidificati:

  • Partizionare per tempo di ingestione, DATE/TIMESTAMP/DATETIME o intervallo di interi per effettuare il pruning delle scansioni. Utilizzare filtri WHERE sulla colonna di partizione o decoratori come _PARTITIONDATE per abilitare il pruning.
  • Eseguire il clustering su colonne ad alta cardinalità, filtrate o unite in join di frequente (fino a 8). BigQuery riesegue il clustering automaticamente; DML ripetuti di piccole dimensioni possono degradare temporaneamente la qualità del clustering.
  • I record nidificati e ripetuti (STRUCT, ARRAY) modellano relazioni uno-a-molti senza l’overhead dei join. Usare UNNEST con giudizio; UNNEST ripetuti su array di grandi dimensioni possono causare un’espansione notevole (fan out).

• Denormalizzazione vs normalizzazione:

  • Denormalizzare gli attributi delle dimensioni nelle tabelle dei fatti per minimizzare i join e sfruttare le scansioni colonnari; questo è ideale per analisi con carichi di lettura pesanti (read-heavy).
  • Normalizzare quando l’amplificazione della scrittura, gli hotspot di aggiornamento o i self-join causano conflitti o complessità (ad esempio, separando le tabelle anagrafiche dei pazienti da quelle delle visite per evitare esplosioni dei self-join). Considerare un approccio ibrido: entità centrali normalizzate con tabelle dei fatti ampie e denormalizzate o figli nidificati.

• Viste materializzate: considerazioni sulla progettazione

  • Ideali per aggregati stabili e incrementali su tabelle di base partizionate. L’aggiornamento delle MV è asincrono; gli utenti a valle dovrebbero tollerare finestre di obsolescenza (staleness) o interrogare le tabelle di base come fallback.
  • Filtrare e raggruppare per la colonna di partizione per l’aggiornamento incrementale. Funzioni non deterministiche, join non supportati o UDF possono squalificare le MV dalla riscrittura della query.

• Query federate, BigLake e pushdown:

  • Le query federate leggono sistemi esterni (ad esempio, Cloud SQL) direttamente con SQL. Sono convenienti per join leggeri o esplorazioni una tantum, ma hanno una latenza più alta e quote più restrittive; estrarre i dati in BigQuery per analisi pesanti.
  • Le tabelle BigLake unificano la governance del lake e del warehouse con controlli a livello di colonna e riga sui dati in Cloud Storage o in formati di tabella aperti (come Parquet). Il pushdown dei predicati e delle proiezioni riduce i byte scaricati; le scansioni di grandi dimensioni favoriscono comunque l’ingestione in tabelle native per ottenere le massime prestazioni.

• Tabelle con caratteri jolly (wildcard) e shard legacy:

  • Le query con caratteri jolly sono un modello legacy per le tabelle partizionate per data (sharded). Preferire il partizionamento nativo, ma quando necessario: SELECT … FROM bigquery-public-data.noaa_gsod.gsod* WHERE _TABLE_SUFFIX >= ‘2010’.

Ottimizzazione delle query e gestione del carico di lavoro

• Partition pruning e clustering:

  • Filtrare sempre sulla colonna di partizione per evitare la scansione di partizioni a freddo. Usare BETWEEN con intervalli ristretti.
  • Ordinare le chiavi di clustering per selettività; le chiavi iniziali dovrebbero corrispondere a filtri e join frequenti. Evitare il clustering su colonne con cardinalità molto bassa.

• Ottimizzazione del piano di query:

  • Utilizzare EXPLAIN e i dettagli di esecuzione per individuare join sbilanciati (skewed joins), shuffle di grandi dimensioni o scansioni non soggette a pruning.
  • Ridurre le colonne il prima possibile con elenchi SELECT e sottoquery; BigQuery è un database colonnare ed elimina in modo efficiente le colonne non utilizzate.
  • Preferire le aggregazioni approssimate (ad esempio, APPROX_QUANTILES) per compromessi tra velocità e costo su grandi quantità di dati.

  • Applicare la deduplicazione con le funzioni di finestra (window functions) quando le fonti di ingestione possono ripetere gli eventi:

undefined

• Join e denormalizzazione:

  • Collocare le chiavi di join come chiavi di cluster per ridurre lo shuffle. I filtri di Bloom o la pre-aggregazione possono aiutare in caso di sbilanciamento (skew) estremo.
  • Denormalizzare le dimensioni piccole e a lenta variazione (slowly changing dimensions) all’interno delle tabelle dei fatti per evitare join intensivi (hot joins). Per dimensioni molto ampie con aggiornamenti frequenti, normalizzare e affidarsi a chiavi di cluster e join materializzati.

• Gestione del carico di lavoro, slot, edizioni e autoscaling:

  • On-demand: BigQuery scala elasticamente la capacità di calcolo per ogni query; si paga per TB scansionato. Controllare i costi con il limite massimo di byte fatturati (maximum bytes billed) e il partition pruning.
  • Basato sulla capacità con le edizioni di BigQuery (Standard, Enterprise, Enterprise Plus) utilizza le prenotazioni di slot. Acquistare commitment di base, creare prenotazioni e assegnare progetti o cartelle. L’autoscaling può aggiungere slot durante i picchi e rilasciarli quando la domanda diminuisce; utilizzare prenotazioni separate per ETL e BI per prevenire interferenze.
  • Priorità dei job: interattiva (predefinita) per bassa latenza; batch per backfill e query pianificate. I job batch vengono messi in coda finché non è disponibile capacità inutilizzata nella prenotazione o nel servizio, quindi vengono eseguiti al costo normale.

• Concorrenza e quote:

  • Utilizzare prenotazioni e assegnazioni per isolare i carichi di lavoro critici. Per tenant misti, collocarli in prenotazioni o progetti distinti con limiti di concorrenza personalizzati.
  • Etichettare i job per l’attribuzione; monitorare INFORMATION_SCHEMA.JOBS e le metriche di Cloud Monitoring per l’utilizzo degli slot e i ritardi in coda.

• Caching di BI e aggiornamento dei dati (freshness):

  • La cache dei risultati delle query migliora la latenza/costo per query identiche; disabilitarla nei client che necessitano di un aggiornamento dei dati inferiore all’ora. Alcuni strumenti di BI mettono in cache i dati in modo indipendente: disabilitare la cache dei report per visualizzare i risultati più recenti.

Ingestione, federazione e ripristino

• Job di caricamento:

  • I caricamenti batch da Cloud Storage (preferibilmente Avro/Parquet) sono affidabili ed economici. Impostare esplicitamente schema e codifica; codifiche CSV non corrispondenti sono una causa comune di discrepanze byte per byte.
  • Utilizzare i decoratori di partizione o caricare su tabelle partizionate per evitare operazioni di merge. Per caricamenti di grandi dimensioni, parallelizzare per partizione.

• Ingestione in streaming e Storage Write API:

  • Gli inserimenti in streaming legacy sono semplici ma hanno quote più restrittive e possono manifestare eventual consistency per alcuni secondi; il time travel su dati molto recenti potrebbe subire ritardi.
  • La Storage Write API è il percorso consigliato per scritture ad alta velocità e bassa latenza con migliori controlli di deduplicazione. Utilizzare l’idempotenza (offset dello stream) per prevenire duplicati.
  • La progettazione dell’applicazione dovrebbe tollerare eventi in transito (in-flight): ritardare le query interattive in base alla disponibilità prevista (ad esempio, 2 volte la latenza osservata) o utilizzare watermark sulle partizioni basate sul tempo di ingestione.

• Progettazione con Dataflow e code dead-letter:

  • Per file CSV forniti da partner con righe malformate, utilizzare Dataflow per il parsing e la validazione, scrivere i record validi su BigQuery tramite la Storage Write API e instradare gli errori a una tabella dead-letter per l’ispezione.
  • Quando si legge da BigQuery su larga scala, preferire letture basate su query (fromQuery) per selezionare solo i campi necessari e ridurre lo shuffle.

• Query pianificate e trasformazioni:

  • Utilizzare le query pianificate per trasformazioni ELT, rollup incrementali e manutenzione delle tabelle. Preferire la scrittura su destinazioni partizionate e clusterizzate. Le query pianificate hanno come priorità predefinita quella batch e si integrano con le prenotazioni.

• Notifiche e osservabilità:

  • Esportare gli audit log di BigQuery con un sink di log verso Pub/Sub per attivare alert su specifici job di inserimento in tabella:

undefined

• Utilizzare gli audit log di Cloud Logging e le viste di INFORMATION_SCHEMA per scoprire i pattern di utilizzo e applicare la governance.

• Time travel, snapshot e cloni:

  • Il time travel consente di interrogare una tabella a un timestamp precedente (predefinito 7 giorni). Usare FOR SYSTEM_TIME AS OF per leggere stati passati.
  • Gli snapshot di tabella catturano una vista point-in-time con copy-on-write; utilizzarli per backfill consistenti o per il ripristino. I cloni di tabella forniscono copie quasi istantanee dei metadati per lo sviluppo o analisi what-if, con un consumo minimo di storage fino a quando non divergono.
  • Scelte di ripristino:
    • Errori di piccola entità: interrogare usando il time travel e ripristinare con INSERT...SELECT.
    • Ripristino di grandi dimensioni: creare da uno snapshot o da un clone, quindi scambiare (swap).
  • Impostare la scadenza di tabelle e partizioni per applicare la retention; verificare che la retention sia allineata con le esigenze del time travel.

• Origini federate:

  • Utilizzare la federazione con Cloud SQL per join leggeri; per analisi prolungate o scansioni di grandi dimensioni, pianificare un processo di estrazione e caricamento (extract-load) in tabelle native.
  • Le tabelle BigLake su file Parquet/ORC in Cloud Storage possono applicare policy tag ed eseguire il push-down di filtri e proiezioni di colonna; aspettarsi comunque una latenza superiore rispetto allo storage nativo.

Sicurezza, governance e controllo dei costi

• IAM e principio del privilegio minimo:

  • Concedere ruoli a livello di set di dati in modo minimale (BigQuery Data Viewer, Data Editor) solo agli utenti approvati; limitare l’accesso API agli account di servizio e a gruppi selezionati.
  • Separare client e ambienti per set di dati e progetto per l’isolamento. Assegnare le prenotazioni (reservations) per progetto o cartella per prevenire l’effetto “noisy neighbors” (vicini rumorosi).

• Viste autorizzate e sicurezza a livello di riga:

  • Le viste autorizzate espongono solo colonne/righe selezionate a progetti esterni, mentre il progetto della vista mantiene l’accesso alla tabella. Mantenere la vista e la sorgente nello stesso set di dati o utilizzare l’autorizzazione a livello di set di dati per il progetto di destinazione.
  • La sicurezza a livello di riga con policy di accesso alle righe (row access policies) filtra le righe per utente o gruppo al momento della query; combinare con le viste autorizzate per un controllo a più livelli.

• Sicurezza a livello di colonna e policy tag:

  • Utilizzare i policy tag di Data Catalog per proteggere le colonne sensibili e abilitare il mascheramento dei dati (data masking). Assegnare l’accesso ai tag (non alle tabelle) per allinearsi alla classificazione dei dati. Per l’accesso dei partner, mascherare o negare le colonne PII tramite i tag.

• BigQuery ML e analisi in-warehouse:

  • Addestrare e servire modelli direttamente in BigQuery (ad esempio, regressione lineare/logistica, XGBoost, K-means, serie temporali) con CREATE MODEL e ML.PREDICT. Archiviare le feature in tabelle partizionate e utilizzare il riaddestramento pianificato.
  • I modelli remoti (remote models) consentono di invocare Vertex AI o endpoint esterni da SQL per lo scoring all’interno di una governance federata. Mettere in cache gli output nelle tabelle per ammortizzare la latenza per query ripetute.

• Controlli dei costi e risoluzione dei problemi di performance:

  • Ridurre i byte scansionati:
    • Partizionare e clusterizzare; filtrare sempre su queste chiavi.
    • Selezionare solo le colonne necessarie; evitare SELECT *.
    • Utilizzare viste materializzate e la cache dei risultati (result caching) dove applicabile.
    • Impostare maximum_bytes_billed per limitare il costo.
  • Risolvere i problemi di performance:
    • Ispezionare i dettagli di esecuzione dei job per individuare skew, partizioni non eliminate (non-pruned) o hotspot di shuffle. Rivedere le chiavi di join o pre-aggregare per ridurre lo shuffle.
    • Convalidare le riscritture delle viste materializzate (MV rewrites); assicurarsi che i predicati e le funzioni deterministiche siano compatibili.
    • Per le dashboard a cui mancano dati di streaming recenti, tenere conto del ritardo di consistenza (consistency lag) o disabilitare la cache lato client.
  • Visibilità della governance: eseguire audit con Cloud Logging, INFORMATION_SCHEMA e etichette di job dettagliate (fine-grained job labels) per l’addebito (charge back) e l’identificazione di anomalie.

Scenario pratico

NovaCare Health gestisce una piattaforma di telemedicina regionale. Un design basato su una singola tabella patient_and_visit ha supportato un progetto pilota, ma con una scalabilità di 100 volte superiore i report vanno in timeout, compaiono duplicati a causa degli upsert in streaming e i partner richiedono un rigoroso isolamento dei dati.

Approccio:

1. Riprogettare lo schema e il layout

   • Creare tabelle principali normalizzate: patients(patient_id, demographics, updated_at) e visits(visit_id, patient_id, visit_ts, metrics, updated_at).
   • Rendere visits una tabella partizionata su DATE(visit_ts), clusterizzata per patient_id e visit_id. Mantenere le piccole dimensioni di riferimento denormalizzate all’interno di visits per la velocità delle dashboard.
   • Motivazione: la normalizzazione evita costosi self-join e pesanti aggiornamenti di righe su una singola tabella “calda” (hot table). Il partizionamento elimina le scansioni storiche (pruning); la clusterizzazione co-localizza join e filtri su patient_id, riducendo lo shuffle.

2. Eseguire l’ingestione con la Storage Write API e garantire l’idempotenza

   • Utilizzare una pipeline Dataflow per analizzare gli eventi in entrata, convalidarli e scriverli su stream nominati nella Storage Write API con offset idempotenti.
   • Instradare gli eventi malformati a una tabella BigQuery dead-letter per il triage.
   • Motivazione: la Storage Write API offre throughput più elevato, latenza inferiore e migliori garanzie di deduplicazione rispetto allo streaming legacy. Il dead-lettering preserva la visibilità sui problemi di qualità dei dati dei partner.

3. Progettare per la freschezza e la deduplicazione nelle query

   • Per l’analisi interattiva, aggiungere un breve watermark (ad esempio, 2 volte la disponibilità osservata) prima di interrogare la partizione più recente; oppure filtrare per _PARTITIONDATE dove partition_date <= CURRENT_DATE() per escludere le righe in transito (in-flight).
   • Utilizzare ROW_NUMBER() OVER (PARTITION BY visit_id ORDER BY event_ts DESC) = 1 nelle viste che devono tollerare i tentativi di ripetizione (retry) a monte.
   • Motivazione: lo streaming è “eventually consistent” per un breve intervallo. Il watermarking e la deduplicazione basata su finestre (window-based) proteggono le dashboard da lacune e duplicati transitori.

4. Accelerare gli aggregati comuni con le viste materializzate

   • Creare viste materializzate (MV) allineate alle partizioni su visits per i KPI giornalieri raggruppati per DATE(visit_ts) e coorti di pazienti. Assicurarsi che i predicati siano compatibili con la riscrittura (rewrite).
   • Motivazione: le viste materializzate riducono la latenza e i byte scansionati per i report ricorrenti; BigQuery riscrive le query per utilizzare la MV in modo trasparente.

5. Applicare l’isolamento dei tenant e la sicurezza granulare

   • Posizionare ogni partner in un set di dati dedicato. Concedere ruoli di set di dati con privilegio minimo ai gruppi di partner.
   • Pubblicare viste autorizzate per benchmark condivisi e trasversali ai partner senza rivelare le tabelle grezze.
   • Applicare policy tag alle colonne PII e aggiungere policy di accesso alle righe (row access policies) a visits per limitare l’accesso per partner_id per l’analisi interna multi-tenant.
   • Motivazione: la segmentazione per set di dati per tenant, unita a viste autorizzate e policy tag, applica il principio del privilegio minimo consentendo al contempo una condivisione controllata.

6. Gestire i carichi di lavoro con edizioni, prenotazioni e autoscaling

   • Acquistare capacità nelle edizioni di BigQuery e creare due prenotazioni (reservations): etl (per i sink di Dataflow, trasformazioni pianificate) e bi (per query ad hoc/reporting). Assegnare i progetti di conseguenza e abilitare l’autoscaling per assorbire i picchi.
   • Pianificare le query ELT come batch con SLA chiari; impostare maximum_bytes_billed per i progetti interattivi.
   • Motivazione: prenotazioni separate impediscono che l’ETL sottragga risorse alla BI. L’autoscaling gestisce i carichi di lavoro con picchi improvvisi (bursty loads) senza sovradimensionamento (overprovisioning).

7. Governare i costi e osservare l’utilizzo

   • Richiedere filtri su visit_ts; rifiutare SELECT * nelle viste condivise. Utilizzare INFORMATION_SCHEMA.JOBS per rilevare scansioni non eliminate (non-pruned scans) e join sbilanciati (skewed joins).
   • Esportare gli audit log di BigQuery su Pub/Sub con un sink di log filtrato per i job di inserimento su visits per attivare alert di monitoraggio in caso di picchi imprevisti.
   • Motivazione: il “byte-pruning” e la proiezione delle colonne controllano i costi; gli audit log evidenziano i pattern di accesso e le anomalie quasi in tempo reale.

8. Pianificare il ripristino e i backfill

   • Abilitare policy di scadenza predefinite per le tabelle che siano in linea con le esigenze di conformità e di time travel. Per modifiche di grandi dimensioni, creare uno snapshot, eseguire le modifiche e ripristinare rapidamente se necessario. Utilizzare cloni di tabella per analisi di tipo “what-if” in dev/test senza duplicare lo storage.
   • Motivazione: snapshot e cloni forniscono reti di sicurezza veloci ed efficienti in termini di spazio; il time travel copre piccoli ripristini correttivi.

9. Integrare il ML in-warehouse

   • Archiviare le feature ingegnerizzate (engineered features) in tabelle partizionate e addestrare modelli di classificazione BigQuery ML per il rischio di riammissione. Per i modelli esterni ospitati su Vertex AI, creare modelli remoti (remote models) e mettere in cache le previsioni in una tabella clusterizzata per join a bassa latenza.
   • Motivazione: mantenere il ML vicino ai dati riduce lo spostamento e la complessità della governance; la cache dell’inferenza remota ammortizza la latenza e i costi.

Con questo design, NovaCare ottiene performance prevedibili con un carico 100 volte superiore, un forte isolamento dei tenant e costi governati, preservando al contempo analisi a bassa latenza e un ripristino riproducibile.

← Archiviazione dei Dati · Tutti i domini · Elaborazione di Flussi con Dataflow e Apache Beam →

Esercitati su queste domande → · Pratica cronometrata su ExamRoll.io →

Sfoglia Google →