AlpinaShop porta tres mòduls generant dades sense mirar-se-les. Les comandes s'acumulen a alpinashop-pedidos, les visites queden als registres del balancejador, les imatges se serveixen des del CDN i les cistelles s'abandonen a Firestore. La Lucía, l'analista, té una llibreta amb preguntes que ningú no ha respost: quina motxilla es ven més a Catalunya, si la campanya de tardor va moure l'agulla, per què el 30 % de les cistelles es queden al pas d'enviament, quant val de mitjana una comanda i com ha evolucionat aquest número en dos anys.

La reacció instintiva de molta gent davant d'aquest problema és obrir una connexió a la base de dades de producció i començar a escriure SELECT. A 02-03 ja vam evitar el pitjor creant la rèplica de lectura alpinashop-pedidos-replica-informes, perquè els informes no competissin amb les compres. Però aquesta rèplica és un pedaç. Una consulta que recorri dos anys de línies de comanda per agrupar per categoria i mes continuarà trigant minuts i continuarà sent incòmoda d'escriure, perquè PostgreSQL no està construït per a això. PostgreSQL està construït per respondre "dona'm la comanda 48213" en un mil·lisegon, no per respondre "dona'm la mitjana de totes les comandes dels últims dos anys agrupades per sis dimensions".

En aquesta lliçó entendràs per què aquestes dues preguntes requereixen màquines diferents, crearàs el conjunt de dades alpinashop_analitica a alpinashop-datos, hi carregaràs l'històric de comandes, escriuràs el SQL que respon de debò a les preguntes de la Lucía i —això és el que separa qui sap fer servir BigQuery de qui rep una factura sorpresa— aprendràs exactament com es paga i com s'evita pagar de més.

1. Per què un magatzem analític no és "una base de dades més gran"
2. Com funciona BigQuery per dins: columnes, separació i Dremel
3. Cloud SQL davant de BigQuery: la taula que cal interioritzar
4. Estructura: projecte, conjunt de dades, taula i vista
5. Creació del conjunt de dades alpinashop_analitica
6. Tipus de dades, STRUCT i ARRAY: la desnormalització com a virtut
7. Creació de les taules d'AlpinaShop
8. Carregar dades: des de Cloud Storage, en streaming i sense carregar-les
9. Portar l'històric des de Cloud SQL amb consultes federades
10. SQL de negoci: les preguntes de la Lucía, respostes
11. El model de cost i com no arruïnar-se
12. Particionat i agrupació en clústers: l'abans i el després mesurats
13. Vistes materialitzades, memòria cau de resultats i quotes
14. Emmagatzematge lògic davant de físic
15. Control d'accés: conjunt de dades, taula, columna i fila
16. INFORMATION_SCHEMA: auditar qui gasta i en què
17. BigQuery ML, esmentat i ajornat

Hi ha dues famílies de càrrega de treball sobre dades, i confondre-les és l'error conceptual més car d'aquesta professió.

OLTP (processament de transaccions en línia) és el que fa alpinashop-pedidos. Moltes operacions molt petites, concurrents, que llegeixen i escriuen unes poques files identificades per clau primària, amb garanties transaccionals estrictes. "Insereix aquesta comanda", "actualitza l'estoc del SKU MOCH-40L-AZ", "dona'm la cistella de l'usuari 8821".

OLAP (processament analític en línia) és el que vol la Lucía. Poques consultes, molt grans, que llegeixen milions de files, toquen poques columnes de cadascuna, agreguen, agrupen i ordenen, i no escriuen res.

La diferència no és de mida, és de forma. I aquesta forma determina com convé desar els bytes al disc.

Imagina't la taula lineas_pedido amb 8 milions de files i aquestes columnes: pedido_id, sku, nombre_producto, categoria, cantidad, precio_unitario, descuento, iva, fecha, pais_envio, metodo_pago, email_cliente, notas.

• PostgreSQL desa les dades per files: tots els camps de la línia 1, després tots els de la línia 2, etc. És perfecte per a "dona'm la línia 1 sencera", perquè és contigua al disc.
• La pregunta de la Lucía, en canvi, és SELECT categoria, SUM(cantidad * precio_unitario) FROM lineas_pedido GROUP BY categoria. Només necessita tres de les tretze columnes. Però per llegir aquestes tres, PostgreSQL ha d'arrossegar del disc les tretze, perquè estan entrellaçades. Llegeix deu vegades més bytes dels que fa servir.

BigQuery desa les dades per columnes: tots els valors de categoria junts, tots els de cantidad junts. La consulta anterior llegeix exclusivament tres blocs de disc i no toca els altres deu. I com que cada bloc conté valors del mateix tipus i molt repetitius —categoria tindrà una dotzena de valors diferents en vuit milions de files—, es comprimeix brutalment bé.

D'aquí es deriven les dues regles que governen tota la resta a BigQuery:

• Llegir poques columnes és barat; llegir-les totes és car. Per això SELECT * és, literalment, la manera més ràpida de gastar diners.
• Filtrar per una columna no evita llegir-la. Si filtres per WHERE pais_envio = 'ES', BigQuery ha de llegir tota la columna pais_envio per saber quines files compleixen la condició. L'única manera de no llegir dades és que estiguin particionades o agrupades en clústers, cosa que veurem a l'apartat 12.

Tres decisions d'arquitectura expliquen per què BigQuery es comporta com es comporta.

Emmagatzematge columnar propi (Capacitor). Les dades es desen en un format columnar comprimit al sistema de fitxers distribuït de Google (Colossus), replicat automàticament. Tu no veus fitxers, no gestiones índexs, no fas VACUUM i no defineixes tablespaces. No hi ha res per administrar.

Separació total d'emmagatzematge i càlcul. Aquesta és la més important i la que canvia la mentalitat. A Cloud SQL, el disc i la CPU són a la mateixa màquina: si necessites més CPU, pagues també més màquina, i si la base de dades està aturada, la màquina continua encesa i facturant. A BigQuery no hi ha màquina.

• L'emmagatzematge es factura per GB al mes que hi ha desats. Existeix sempre.
• El càlcul es factura per consulta executada (o per capacitat reservada). Si ningú no consulta, el càlcul costa zero.

Conseqüència immediata per a AlpinaShop: pots tenir 2 TB d'històric desats i, si la Lucía se'n va de vacances, pagar únicament l'emmagatzematge —uns pocs euros— sense apagar res. I a l'inrevés: una consulta pot mobilitzar centenars de màquines durant 8 segons i després tornar-les. Aquesta elasticitat és impossible en un model amb servidor.

Execució distribuïda (motor Dremel). Quan llances una consulta, el planificador la descompon en un arbre d'execució. Les fulles de l'arbre —els workers— llegeixen fragments de columnes en paral·lel, cadascun fa la seva part de l'agregació, i els resultats intermedis pugen per l'arbre barrejant-se en una capa de shuffle en memòria fins a produir el resultat final.

La unitat d'aquest càlcul s'anomena slot: un slot és, aproximadament, una porció de CPU amb la seva memòria associada. Una consulta fa servir tants slots com el planificador consideri i n'hi hagi de disponibles. Tornarem als slots a l'apartat 11, perquè són la clau del model de preus per capacitat.

La lectura correcta d'aquesta taula no és "BigQuery és millor". És: són eines complementàries i AlpinaShop necessita totes dues. La botiga continuarà comprant contra Cloud SQL. La Lucía analitzarà contra BigQuery. La feina d'aquesta lliçó i de les següents és construir la canonada que porta les dades de la primera a la segona.

La jerarquia de BigQuery es recolza en la jerarquia de recursos que ja coneixes de 01-04:

Quatre precisions que eviten problemes més endavant:

• El projecte és la unitat de facturació. Les consultes que llanci la Lucía es carreguen al projecte on s'executen, no al projecte on viu la taula. Això permet, per exemple, que màrqueting consulti dades d'alpinashop-datos pagant des del seu propi projecte. Per a AlpinaShop, tot s'executa i es factura a alpinashop-datos, que ja té la seva etiqueta centro-coste.
• El conjunt de dades és la unitat d'organització i de permisos. És on es concedeixen rols, i és el que es comparteix.
• La ubicació del conjunt de dades és immutable. Es decideix en crear-lo i no es pot canviar: per moure'l cal crear-ne un altre i copiar-hi les dades. Triarem europe-west1, coherent amb la resta de la infraestructura d'AlpinaShop i amb l'exigència del RGPD de mantenir les dades personals de clients europeus a la UE.
• No es pot fer un JOIN entre conjunts de dades d'ubicacions diferents. Si demà algú crea alpinashop_marketing a us-central1, no el podrà creuar amb alpinashop_analitica. És l'error de disseny irreversible més comú a BigQuery. Fixa la ubicació com a estàndard de l'equip des del primer dia.

El nom complet d'una taula és projecte.dataset.taula, i en SQL estàndard s'escriu entre accents greus quan el projecte porta guions:

Una nota de nomenclatura: els identificadors de conjunt de dades i de taula admeten lletres, números i guions baixos, però no guions. Per això el projecte és alpinashop-datos (amb guió) i el conjunt de dades és alpinashop_analitica (amb guió baix).

Treballarem amb l'eina bq, que ve inclosa a la CLI de gcloud i a Cloud Shell des de 01-06.

Opció per opció:

• --location=europe-west1: la ubicació immutable. Va abans de la subcomanda mk, no després; és un error habitual.
• --dataset: indica que el recurs que s'ha de crear és un conjunt de dades i no una taula.
• --default_table_expiration=0: sense caducitat automàtica. Si hi posessis 2592000 (30 dies en segons), tota taula creada aquí s'autodestruiria als 30 dies. És utilíssim en un conjunt de dades de proves i catastròfic en producció, així que convé ser explícit.
• Les tres etiquetes repliquen l'esquema entorno/equipo/centro-coste que AlpinaShop va fixar a 01-04, perquè el cost d'analítica es pugui aïllar a l'informe de facturació.

Convé crear també un conjunt de dades de treball amb caducitat, perquè les taules temporals de les anàlisis no s'acumulin per sempre:

Verificació:

A la sortida de bq show fixa't en "location": "europe-west1". Si diu US, has creat el conjunt de dades al lloc equivocat: esborra'l ara, abans de posar-hi dades, perquè després ja no hi ha marxa enrere barata.

Els tipus escalars de BigQuery són pocs i deliberadament estrictes:

Regla que estalvia disgustos: els imports van en NUMERIC, mai en FLOAT64. Amb FLOAT64, sumar 1,10 € i 2,20 € pot donar 3.3000000000000003, i aquest cèntim fantasma apareixerà en un informe de direcció en el pitjor moment possible.

I ara la part que distingeix el modelatge a BigQuery del modelatge relacional clàssic: els tipus imbricats.

• ARRAY<T>: una llista ordenada de valors del tipus T dins d'una única cel·la.
• STRUCT<...>: un registre amb camps anomenats dins d'una única cel·la.
• Es combinen: ARRAY<STRUCT<sku STRING, cantidad INT64, precio NUMERIC>> és "una llista de línies de comanda dins de la fila de la comanda".

A PostgreSQL, una comanda amb tres línies són quatre files repartides en dues taules unides per clau forana. A BigQuery pot ser una sola fila que conté, a dins, les seves tres línies.

Per què això és millor aquí? Perquè en un motor distribuït, un JOIN entre dues taules grans obliga a moure dades entre màquines per la capa de shuffle, i això és el que és car. Si les línies ja viatgen enganxades a la seva comanda, el JOIN desapareix: es converteix en un UNNEST, que és una operació local dins de cada worker, sense xarxa pel mig.

Aquesta fila conté una comanda completa. envio és un STRUCT (un registre), i lineas és un ARRAY<STRUCT<...>> (una llista de registres). Tot el que cal per analitzar la comanda és en un sol lloc del disc.

La regla pràctica de modelatge: normalitza en OLTP per evitar duplicitat en escriure; desnormalitza en OLAP per evitar JOIN en llegir. L'emmagatzematge és barat (uns cèntims per GB al mes); el shuffle no ho és.

Dit això, no cal portar-ho a l'extrem. Per a AlpinaShop mantindrem pedidos i lineas_pedido com a taules separades —perquè és com arriben de PostgreSQL i facilita la càrrega incremental— i imbricarem allà on aporti de debò: el bloc d'enviament dins de la comanda, i els esdeveniments dins de la sessió de navegació. És un punt intermedi honest i molt comú a la pràctica.

Crearem les quatre taules amb DDL en SQL, que és més llegible i versionable que un JSON d'esquema. Aquestes sentències s'executen a BigQuery Studio, la interfície de consulta de la consola (menú BigQuery), o des de bq query --use_legacy_sql=false.

Els tres detalls que importen:

• PARTITION BY fecha_pedido divideix físicament la taula en una partició per dia. Una consulta que filtri per data només llegirà les particions necessàries.
• CLUSTER BY estado, canal ordena les dades dins de cada partició per aquestes columnes, en aquest ordre. Els filtres per estado podran saltar-se blocs sencers.
• require_partition_filter=TRUE és la millor decisió defensiva de tota la lliçó: rebutja qualsevol consulta que no filtri per fecha_pedido. Un SELECT * FROM pedidos fallarà amb un error explícit en comptes d'escanejar dos anys de dades. És un cinturó de seguretat barat que evita factures absurdes.

Nota de disseny sobre lineas_pedido: hem duplicat fecha_pedido des de la capçalera. En un model relacional això seria una redundància censurable. Aquí és imprescindible, perquè una taula només es pot particionar per una columna pròpia. Sense aquesta duplicació, qualsevol JOIN entre comandes i línies obligaria a escanejar la taula de línies sencera. És l'exemple perfecte de per què les regles del modelatge OLTP no es traslladen literalment.

I la taula de navegació, que sí que aprofita la imbricació a fons:

Una sessió de 40 clics és una fila, no 40. Comptar quantes sessions van arribar a iniciar_pago però no a compra —la pregunta de la cistella abandonada que portava la Lucía— serà una operació local, sense JOIN i sense shuffle.

Hi ha quatre maneres de posar dades a BigQuery, i triar l'equivocada és una font clàssica de cost i de dolor.

És el cavall de batalla i és gratis, cosa que sorprèn molta gent. Google no cobra la ingesta per lots: cobra l'emmagatzematge resultant i les consultes posteriors.

Suposem que el bolcat nocturn de l'històric de comandes deixa fitxers al bucket. Formats admesos: CSV, JSON delimitat per línies (NDJSON), Avro, Parquet i ORC.

• El comodí pedidos-*.csv permet carregar desenes de fitxers en una sola operació, i BigQuery els processa en paral·lel. És molt millor que un fitxer gegant.
• --null_marker='\N' tradueix la marca de nul que fa servir pg_dump en format CSV; sense això acabaries amb la cadena literal \N a les cel·les.
• Passar l'esquema en un fitxer JSON és preferible a --autodetect. L'autodetecció endevina, i endevinar amb diners és mala idea: és capaç d'inferir FLOAT64 per a una columna d'imports o STRING per a una data amb un format estrany.

Comparació honesta de formats:

Recomanació per a AlpinaShop: Avro o Parquet als processos automàtics, perquè porten l'esquema a dins i no hi ha ambigüitat de tipus ni sorpreses amb les comes dels noms de producte. CSV només per al que arriba de tercers, com el fitxer del transportista que veurem a 04-05.

Quan la dada ha d'estar disponible en segons —el tauler de la campanya de tardor en temps real— es fa servir la Storage Write API, que substitueix l'antiga tabledata.insertAll i és més barata i amb millors garanties.

Dos advertiments importants sobre el streaming:

1. Costa diners per GB inserit, mentre que la càrrega per lots és gratis. Si la dada pot esperar una hora, no la transmetis en streaming: acumula-la al bucket i carrega-la per lots. És una de les optimitzacions de cost més rendibles i més ignorades.
2. Per a AlpinaShop, la via real no serà aquest codi a l'aplicació Flask, sinó Pub/Sub → BigQuery (04-04) o Pub/Sub → Dataflow → BigQuery (04-02). Escriure directament a BigQuery des del web acobla la botiga al magatzem analític: si BigQuery té una incidència, no volem que la botiga deixi de vendre.

De vegades la dada ja és a alpinashop-catalogo i no compensa duplicar-la. Una taula externa deixa els fitxers on són i BigQuery els llegeix en el moment de la consulta.

Avantatges: zero duplicació, zero cost d'ingesta, la dada sempre reflecteix el bucket. Inconvenients: consultes més lentes, sense partició ni agrupació en clústers, i sense memòria cau de resultats. Fes-les servir per a dades consultades de tant en tant o com a zona d'aterratge abans d'una càrrega real.

BigLake és l'evolució de les taules externes: afegeix control d'accés fi —permisos a nivell de columna i de fila sobre dades que viuen al bucket— sense que l'analista necessiti permís sobre el bucket. Es recolza en una connexió de BigQuery amb el seu propi compte de servei, que és qui accedeix a Cloud Storage. Per a AlpinaShop, això significa que la Lucía podrà consultar els CSV d'exportació sense tenir storage.objectViewer sobre alpinashop-catalogo, cosa que encaixa amb el mínim privilegi de 03-04.

L'històric de comandes viu a alpinashop-pedidos. Per portar-lo hi ha una via elegant: EXTERNAL_QUERY, la consulta federada, que executa SQL directament a PostgreSQL i retorna el resultat a BigQuery com si fos una taula.

Primer, la connexió. La crea la Marta una sola vegada:

Fixa't en dues decisions deliberades:

• Apuntem a la rèplica de lectura alpinashop-pedidos-replica-informes, no a la instància principal. És exactament per al que es va crear a 02-03: que els informes no toquin la base de dades que atén les compres.
• Fem servir l'usuari informes_lectura, que només té SELECT. La contrasenya real ha de sortir de Secret Manager (db-password-catalogo és la del catàleg; per a això es crearà el seu propi secret), mai d'un fitxer al repositori, tal com vam fixar a 03-06.

Ara la càrrega de l'històric, en una sola sentència:

Com llegir-ho:

• La cadena que va dins d'EXTERNAL_QUERY és SQL de PostgreSQL, no de BigQuery. S'executa allà, amb la sintaxi d'allà. Les cometes triples eviten haver d'escapar les cometes simples internes.
• Filtrar dins d'aquesta cadena (WHERE creado_en >= ...) és crític: com menys files creuin la xarxa, millor. Filtrar fora, a BigQuery, ho portaria tot igualment.
• El STRUCT(...) construeix el bloc imbricat envio a partir de les sis columnes planes que retorna PostgreSQL. Aquí es veu la traducció entre els dos models.

Quan NO fer servir consultes federades: per a càrregues grans i repetides. EXTERNAL_QUERY posa càrrega a la instància de PostgreSQL i no paral·lelitza; serveix per al bolcat inicial i per portar taules petites i fresques (el mestre de productes, per exemple). La sincronització diària de l'històric es farà amb el patró exportar → carregar orquestrat a 04-06, o amb CDC, que veurem a 04-05.

BigQuery fa servir GoogleSQL, un dialecte estàndard ANSI amb extensions. Si saps SQL, en saps el 90 %. Anem amb les preguntes reals.

El WHERE sobre fecha_pedido no és cosmètic: és el que activa la poda de particions i el que fa que require_partition_filter=TRUE no rebutgi la consulta. Sense ell, BigQuery llegiria la taula sencera.

APPROX_QUANTILES calcula percentils de manera aproximada però moltíssim més barata que el càlcul exacte sobre milions de files. Incloure la mediana al costat de la mitjana no és un caprici estadístic: si una comanda corporativa de 4.000 € entra al març, la mitjana es dispara i la mediana no es immuta. Ensenyar només la mitjana és com s'enganya sense voler un comitè de direcció.

Compte amb NOT IN i els nuls: si la subconsulta retornés algun sku nul, NOT IN retorna zero files sense avisar. En producció és més segur LEFT JOIN ... WHERE l.sku IS NULL o NOT EXISTS.

Dues joies aquí:

• OVER (PARTITION BY categoria) calcula sobre cada grup sense col·lapsar les files, que és justament el que un GROUP BY no pot fer.
• QUALIFY filtra pel resultat d'una funció de finestra directament, sense necessitat d'embolcallar-ho tot en una altra subconsulta. És una extensió de GoogleSQL i estalvia moltíssim soroll.

UNNEST(eventos) converteix l'array d'esdeveniments de cada sessió en files, i la coma que hi ha al davant és un CROSS JOIN implícit amb la fila pare: cada esdeveniment conserva accés a sesion_id, pais i la resta. És local a cada worker, sense shuffle. L'últim camp respon directament a la pregunta de la cistella abandonada que portava la Lucía des del mòdul 3, i NULLIF(..., 0) evita la divisió per zero quan un dia no hi ha pagaments iniciats.

Aquest apartat val per si sol el preu de la lliçó. BigQuery és meravellós i també és el servei amb què més gent s'emporta un ensurt a la factura.

Hi ha dos models de càlcul, i es poden barrejar per projecte:

Les edicions actuals són Standard, Enterprise i Enterprise Plus, amb reserves de slots que poden ser d'escalat automàtic (pagues els slots que es fan servir, amb un mínim) o compromeses a un o tres anys amb descompte. Cadascuna afegeix funcions: Standard cobreix el bàsic, Enterprise afegeix govern i CMEK, Enterprise Plus afegeix recuperació avançada i residència estricta.

Per a AlpinaShop la decisió és sota demanda, i cal dir-ho amb els seus números: un equip de tres persones consultant uns pocs centenars de gigabytes al mes gasta uns pocs euros. Reservar slots tindria un cost fix mensual molt superior. La regla pràctica del sector és migrar a capacitat quan la despesa sota demanda supera de manera estable el cost de la reserva equivalent, cosa que sol passar a partir de desenes de terabytes consultats al mes.

Ara, el que de debò cal fer cada dia.

Retorna una cosa com:

428 MB. A ~5 $/TB, uns 0,002 $. Cap problema. Però el mateix càlcul sobre una consulta mal escrita pot retornar 900 GB i costar 4,50 $ cada vegada que algú premi executar. Deu analistes refrescant un tauler cada cinc minuts converteixen això en una factura de quatre xifres al mes.

A BigQuery Studio no cal la comanda: la interfície mostra a dalt a la dreta, en temps real mentre escrius, "Aquesta consulta processarà X". Ensenya a mirar aquí a tothom que rebi accés. És el costum més rendible que pots instal·lar en un equip de dades.

Es paga pels bytes de les columnes llegides. SELECT * sobre pedidos llegeix les catorze columnes; SELECT pedido_id, total_pedido en llegeix dues. Diferència habitual: entre cinc i vint vegades.

LIMIT no redueix el cost. És el malentès número u. Per inspeccionar dades fes servir bq head o la pestanya Vista prèvia de la consola: llegeixen directament l'emmagatzematge i no costen res.

I una utilitat que sí que ajuda quan vols moltes columnes menys una:

Ja vam declarar partició i agrupació en clústers en crear les taules. Ara mesurem què compren exactament.

Partició: divideix la taula en trossos pel valor d'una columna (data, rang enter, o el temps d'ingesta). BigQuery descarta particions senceres sense llegir-les si el WHERE ho permet.

Agrupació en clústers: ordena físicament les dades dins de cada partició per fins a quatre columnes. Permet saltar-se blocs dins de la partició.

L'experiment, amb una taula de comandes de dos anys (suposem 40 GB):

De 3 GB a 4 MB: 750 vegades menys. I no ha canviat ni una lletra del SQL, només la definició física de la taula. A escala d'un any de consultes diàries, aquesta diferència és la que separa una factura de tres euros d'una de dos mil.

L'agrupació en clústers no apareix al --dry-run perquè el seu estalvi només es coneix en executar (el planificador no sap per endavant quants blocs podrà saltar). Es mesura al resultat real:

Guia de decisió ràpida:

Un matís sobre l'ordre a CLUSTER BY estado, canal: l'ordre importa. L'agrupació en clústers ordena primer per estado i dins per canal. Un filtre només per canal aprofita molt menys l'agrupació que un filtre per estado. Posa primer la columna per la qual més filtres.

Tres mecanismes més per pagar menys, en ordre d'esforç creixent.

Si executes exactament la mateixa consulta sobre dades que no han canviat, BigQuery retorna el resultat de la memòria cau: cost zero i resposta immediata, durant unes 24 hores. S'invalida en modificar qualsevol taula implicada.

Es trenca sense voler amb més facilitat de la que sembla: n'hi ha prou que la consulta faci servir CURRENT_TIMESTAMP() o funcions no deterministes, o que el text canviï en un caràcter. Si un tauler fa servir WHERE fecha >= CURRENT_DATE() - 7, mai no farà bé la memòria cau. Convé fixar dates explícites quan l'informe ho permeti.

Una vista és SQL desat: no ocupa espai i s'executa —i es paga— sencera cada vegada.

Una vista materialitzada desa el resultat precalculat i BigQuery el refresca de manera incremental quan canvia la taula base. A més, i això és el millor, l'optimitzador la fa servir automàticament encara que tu consultis la taula original.

El tauler de control de Looker Studio que construirà la Lucía a 04-07 llegirà uns pocs megabytes d'aquesta vista materialitzada en comptes d'escanejar la taula de línies cada vegada que algú obri l'informe. Limitacions que cal conèixer: només admet un subconjunt de SQL (agregacions sí; JOIN amb restriccions i finestres no), i el refresc incremental consumeix càlcul, així que no en creïs vint.

Es poden imposar límits durs de bytes processats, per projecte i per usuari:

I a nivell de sessió o de consulta individual:

Per a AlpinaShop la política és: quota de projecte a 2 TB/dia, require_partition_filter a les taules grans, i una alerta de pressupost a Facturació (01-04) a 50 € mensuals sobre l'etiqueta centro-coste:analitica. Tres xarxes de seguretat independents, cap de cara.

L'emmagatzematge té el seu propi model, i des de fa uns anys es pot triar com es factura per conjunt de dades.

El preu per GB en mode físic és aproximadament el doble, però els GB solen ser entre 4 i 10 vegades menys perquè BigQuery comprimeix molt bé les columnes repetitives. Per a dades com les d'AlpinaShop —categories, estats, països que es repeteixen milions de vegades— el mode físic sol sortir clarament més barat. Cal mesurar-ho abans de canviar:

A més existeix el preu de llarga durada: una partició que no es modifica durant 90 dies consecutius baixa automàticament de preu, al voltant de la meitat. És automàtic, no cal fer res, i no afecta el rendiment ni la disponibilitat. Com que la major part de l'històric d'AlpinaShop no es toca mai, bona part de l'emmagatzematge acabarà a aquesta tarifa reduïda tot sol.

Compte amb un detall: qualsevol UPDATE sobre una partició reinicia el seu comptador de 90 dies. Un procés mal dissenyat que reescrigui l'històric sencer cada nit manté tota la taula a tarifa alta per sempre. És un altre argument a favor de les càrregues incrementals per partició.

Aquí s'aplica tot el d'IAM (03-04), més dues capes que només existeixen a BigQuery.

Els rols predefinits rellevants:

El matís que confon tothom: dataViewer no n'hi ha prou per consultar. Veure les dades i executar un treball són permisos diferents. Cal també jobUser al projecte on s'executa la consulta. Si la Lucía diu "veig la taula però no la puc consultar", és això el 90 % de les vegades.

Com sempre des de 03-04: permisos a grups, mai a persones. Quan entri un altre analista, n'hi ha prou de posar-lo a gcp-datos@.

Recordem també el rol personalitzat analistaCatalogo que es va crear a 03-04 a alpinashop-datos. Ara té sentit completar-lo amb els permisos analítics mínims:

Avís de RGPD. email_cliente és una dada personal. El seu tractament amb finalitats analítiques exigeix base legal, minimització i limitació de l'accés. El que segueix és un control tècnic, no un dictamen jurídic: qualsevol tractament de dades personals reals l'ha de revisar un professional de compliment normatiu o el DPD abans de posar-se en producció. Totes les dades d'aquest curs són fictícies.

El control es fa amb etiquetes de política de Data Catalog. La idea: s'etiqueta la columna, i només qui tingui el rol Lector detallat sobre aquesta etiqueta la pot llegir.

I s'aplica a la columna:

A partir d'aquell moment, si la Lucía executa SELECT * FROM pedidos, la consulta falla sencera amb un error d'accés a email_cliente. Ha d'escriure SELECT * EXCEPT(email_cliente) o llistar columnes. És un efecte secundari desitjable: reforça el costum de no fer servir SELECT *, que ja sabem que a més és car.

Per a AlpinaShop, la recomanació completa és no portar l'email en cru al magatzem analític. La Lucía necessita saber quants clients diferents van comprar, no qui. Un identificador pseudonimitzat serveix igual:

La sal ha de venir de Secret Manager (03-06) i no estar escrita al SQL com aquí; sense sal, un hash d'email és reversible per força bruta i continua sent dada personal. A 04-07 veurem l'eina específica per a això, Sensitive Data Protection.

Una política d'accés a files filtra quines files veu cada identitat. És útil si demà AlpinaShop obre filial a França i cada equip comercial només ha de veure el seu mercat:

El filtre s'aplica de manera transparent i ineludible: l'usuari escriu el seu SELECT normal i només obté les files d'Espanya, sense saber que existeix la política.

Si vols donar accés al resultat però no a la taula base, es fa servir una vista autoritzada: es concedeix permís sobre la vista, la vista té permís sobre la taula, i l'usuari no. És el mecanisme net per exposar dades agregades a màrqueting sense obrir-los el detall de comandes.

BigQuery s'observa a si mateix. INFORMATION_SCHEMA són vistes de només lectura amb metadades i amb l'historial de treballs. Aquesta consulta s'hauria d'executar un cop per setmana en qualsevol equip seriós:

El preu de 6,25 està posat a mà com a ordre de magnitud: substitueix-lo pel vigent a la teva regió i verifica'l a la documentació oficial, perquè canvia.

Altres consultes útils del mateix lloc:

Aquest últim és l'equivalent analític de la neteja de discos orfes de 02-01: en tot magatzem de dades amb un any de vida hi ha taules _backup_final_v2 que ningú no recorda i que es paguen cada mes.

BigQuery inclou BigQuery ML, que permet entrenar models amb sintaxi SQL: CREATE MODEL ... OPTIONS(model_type='linear_reg'), i després ML.PREDICT. Es pot fer regressió, classificació, sèries temporals amb ARIMA_PLUS, k-means, factorització matricial per a recomanacions i fins i tot invocar models de Vertex AI des de SQL.

Per a AlpinaShop és una via molt atractiva: previsió de demanda per SKU abans de la campanya de tardor, o segmentació de clients, sense treure la dada de BigQuery ni muntar infraestructura.

Però això és mòdul 5. Allà veurem Vertex AI, AutoML i els models de recomanació amb el rigor que mereixen: com se separen les dades d'entrenament i validació, com s'avalua un model i per què una mètrica bona en proves pot ser inútil en producció. Aquí només queda anotat que la porta existeix i que és a un CREATE MODEL de distància.

Crear el conjunt de dades a US per descuit. És l'opció per defecte de la consola i és irreversible. A més d'impedir els JOIN amb europe-west1, treu dades personals de clients europeus fora de la UE. Comprova sempre la ubicació abans de carregar el primer byte, i fixa --location=europe-west1 a la configuració de l'equip.

Creure que LIMIT abarateix la consulta. No ho fa: es llegeixen els mateixos bytes. Per mirar dades, bq head o la vista prèvia, que són gratis.

Fer servir SELECT * per costum. En un motor columnar és entre cinc i vint vegades més car. Escriu les columnes, o fes servir SELECT * EXCEPT(...).

Oblidar el filtre de partició. Sense WHERE fecha_pedido ..., una taula particionada es llegeix sencera i la partició no serveix de res. Activa require_partition_filter=TRUE i deixa que el motor et protegeixi.

Fer servir FLOAT64 per a imports. Els errors d'arrodoniment apareixen justament a l'informe que veu direcció. NUMERIC sempre per als diners.

Fer UPDATE i DELETE com a PostgreSQL. BigQuery els admet, però reescriuen particions senceres, són cars i reinicien el comptador de llarga durada. Per a actualitzacions massives, MERGE o reescriptura de la partició completa amb WRITE_TRUNCATE. I per corregir una dada d'una comanda, fes-ho a Cloud SQL i torna a sincronitzar.

Escriure a BigQuery des de l'aplicació web. Acobla la botiga al magatzem analític. Publica a Pub/Sub (04-04) i que sigui un altre procés qui escrigui. La botiga ha de poder vendre encara que l'analítica estigui caiguda.

Donar dataViewer i oblidar jobUser. És el tiquet de suport més repetit del món BigQuery.

Posar LIMIT a una consulta que ordena. ORDER BY sobre milions de files obliga a una ordenació global que pot esgotar els recursos amb l'error Resources exceeded. Agrega primer, ordena després; o fes servir funcions de finestra amb QUALIFY.

Consell: fes servir etiquetes als treballs. bq query --label=proceso:panel-direccion permet després separar a INFORMATION_SCHEMA quant costa cada consumidor de dades. Quan algú pregunti "quant ens costa el tauler de direcció?", tindràs el número.

Consell: desa el DDL a Git. Les sentències CREATE TABLE d'aquesta lliçó són codi. Han d'estar versionades juntament amb la resta del projecte, no només dins de BigQuery. A 06-07 les convertirem en Terraform.

Crea a alpinashop-datos un conjunt de dades de proves anomenat alpinashop_ejercicios a europe-west1 les taules del qual caduquin als 3 dies. A dins, crea una taula opiniones amb: opinion_id (STRING, obligatori), sku (STRING, obligatori), fecha (DATE, obligatori), puntuacion (INT64), texto (STRING), pais (STRING). Ha d'estar particionada per fecha, agrupada en clústers per sku i rebutjar consultes sense filtre de partició. Després insereix tres ressenyes d'exemple i comprova amb --dry-run quants bytes costa consultar les d'un únic dia.

Amb les taules d'alpinashop_analitica, escriu una única consulta que retorni, per a cada mes de 2026 i només per a comandes ni cancel·lades ni retornades: el mes, el nombre de comandes, la facturació total, la cistella mitjana, la categoria més venuda d'aquell mes i el percentatge que aquella categoria representa sobre el total del mes. Ordena per mes. Abans d'executar-la, estima'n el cost amb --dry-run.

La Marta rep l'alerta de pressupost: alpinashop-datos porta gastats 180 € aquest mes quan l'històric eren 12 €. Ningú no ha carregat dades noves. Escriu les consultes d'INFORMATION_SCHEMA que et permetin (a) identificar l'usuari o compte de servei responsable, (b) aïllar la consulta concreta i quantes vegades s'ha executat, i (c) determinar si aprofita la memòria cau. Després proposa tres mesures correctores concretes, ordenades de la més immediata a la més estructural.

Solució 1

El resultat serà d'uns pocs centenars de bytes: amb tres files, és gairebé tot metadades. L'interessant és l'hàbit, no la xifra. I si treus el WHERE fecha, la consulta no serà cara: fallarà, perquè require_partition_filter=TRUE la rebutja. Aquest error és exactament el comportament desitjat.

Solució 2

Les claus de la solució:

• Les CTE (WITH) parteixen el problema en passos llegibles. BigQuery les avalua com a part del pla; no creen taules ni costen a part.
• QUALIFY ROW_NUMBER() OVER (...) = 1 es queda amb la categoria líder de cada mes en una sola passada. ROW_NUMBER i no RANK perquè volem exactament una fila encara que hi hagi empat.
• El SUM(...) OVER (PARTITION BY mes) calcula el total del mes sense col·lapsar les files, cosa que permet dividir per obtenir la quota.
• Els filtres de data són a les dues taules particionades. Ometre'ls a lineas_pedido faria que el JOIN escanegés la taula completa: la consulta donaria el mateix resultat i costaria vint vegades més.
• NULLIF(..., 0) protegeix de la divisió per zero en un mes sense vendes.

El --dry-run sobre un històric de dos anys hauria de donar uns centenars de megabytes: cèntims. Sense els filtres de partició, desenes de gigabytes.

Solució 3

(a) Identificar el responsable:

(b) Aïllar la consulta i comptar execucions:

(c) Comprovar l'aprofitament de la memòria cau:

Diagnòstic típic i esperable: el culpable no és una persona, sinó un tauler de control que s'autorefresca cada 5 minuts executant SELECT * FROM lineas_pedido sense filtre de data. Un pct_cache proper a zero ho confirma: si la consulta porta CURRENT_TIMESTAMP() o la taula rep escriptures contínues, la memòria cau no serveix mai. 288 execucions al dia × 2 GB × 30 dies ≈ 17 TB ≈ 100 €.

Tres mesures correctores, del més immediat al més estructural:

1. Avui mateix (contenció): aplicar --maximum_bytes_billed a l'usuari o al compte de servei del tauler i fixar la quota diària del projecte a 2 TB. L'hemorràgia s'atura en minuts encara que ningú no toqui el tauler.
2. Aquesta setmana (correcció): reescriure la consulta del tauler amb les columnes explícites i filtre de partició, i baixar el refresc de 5 minuts a 1 hora. Sol ser prou per dividir el cost per cent.
3. Aquest mes (estructural): crear una vista materialitzada amb l'agregació que el tauler necessita i apuntar-hi el tauler de control; activar require_partition_filter=TRUE a les taules grans perquè el problema no es pugui repetir; i programar la consulta d'auditoria setmanal amb una alerta si algun usuari supera un llindar de TB.

La mesura 3 és l'única que impedeix la reincidència. Les dues primeres compren temps.

AlpinaShop ja té magatzem de dades. En aquesta lliçó has entès la diferència real —no la d'eslògan— entre OLTP i OLAP: no és qüestió de mida, sinó de forma d'accés, i aquesta forma és la que justifica l'emmagatzematge columnar, la separació d'emmagatzematge i càlcul, i l'execució distribuïda en slots que fa que una consulta sobre vuit-cents milions de files acabi en segons.

Has creat el conjunt de dades alpinashop_analitica a alpinashop-datos, a europe-west1, sabent que aquesta ubicació és una decisió irreversible amb implicacions de compliment normatiu i de capacitat de creuar dades. Has definit les taules pedidos, lineas_pedido, productos i visitas amb tipus correctes —NUMERIC per als diners, sempre—, amb STRUCT per al bloc d'enviament i amb un ARRAY<STRUCT<...>> d'esdeveniments dins de cada sessió, entenent per què la desnormalització, que a PostgreSQL seria un error, aquí és la manera d'evitar el shuffle, que és el que és veritablement car en un motor distribuït.

Has carregat dades per les quatre vies i saps quina fer servir: lots des de Cloud Storage —gratis— per al volum, streaming només quan els segons importen, taules externes i BigLake per al que no compensa duplicar, i EXTERNAL_QUERY contra la rèplica alpinashop-pedidos-replica-informes per al bolcat inicial de l'històric, apuntant deliberadament a la rèplica i amb l'usuari informes_lectura de només lectura.

Has escrit el SQL que respon a les preguntes que la Lucía portava tres mòduls acumulant: vendes per categoria i mes, cistella mitjana amb la seva mediana al costat per no enganyar ningú, productes que no venen, rànquing per categoria amb funcions de finestra i QUALIFY, i l'embut de conversió complet amb UNNEST sobre els esdeveniments imbricats, que per fi posa número al 30 % de cistelles abandonades.

I, sobretot, has après a no arruïnar-te: --dry-run abans d'executar, SELECT * desterrat, LIMIT desemmascarat com a fals estalvi, particionat per data que va convertir 3 GB en 4 MB, agrupació en clústers ordenada per la columna per la qual més filtres, require_partition_filter com a cinturó de seguretat, vistes materialitzades per als taulers, memòria cau de resultats que és gratis si no la trenques, quotes per projecte i per consulta, emmagatzematge físic davant de lògic mesurat amb dades pròpies, i INFORMATION_SCHEMA com l'eina que converteix "la factura ha pujat" en "aquesta consulta, aquest usuari, 288 vegades al dia". Has tancat l'accés amb rols a grups, has recordat que dataViewer sense jobUser no consulta res, i has protegit email_cliente amb una etiqueta de política —amb l'advertiment exprés de RGPD i la recomanació de no portar l'email en cru al magatzem, sinó un identificador pseudonimitzat amb sal de Secret Manager.

Queda un buit evident. Tot el que hem carregat és històric: una foto del que ja va passar, portada de PostgreSQL d'una sola vegada. Però AlpinaShop no para de vendre. Ara mateix, mentre llegeixes això, hi ha clients navegant pel catàleg, afegint motxilles a la cistella i confirmant comandes, i cap d'aquests fets no arriba a alpinashop_analitica. Sincronitzar a mà cada nit és un pedaç que ja es trenca quan algú demana "vull veure les vendes de la campanya avui, no demà".

A 04-02, Cloud Dataflow, construirem la canonada que falta. Veuràs què és un pipeline de dades i per què un script en una VM no n'hi ha prou; aprendràs el model d'Apache Beam, que descriu amb el mateix codi un procés per lots i un en temps real; escriuràs en Python un pipeline que llegeix l'històric del bucket alpinashop-catalogo, el neteja, l'agrega i l'escriu a les taules que acabes de crear; i entraràs al territori realment interessant del streaming —temps de l'esdeveniment davant de temps de procés, marques d'aigua, finestres i dades tardanes— per deixar preparat el pipeline que consumirà el topic pedidos-nuevos així que el creem a 04-04. Quan acabis aquella lliçó, les dades deixaran d'arribar quan algú se'n recorda i començaran a arribar soles.