AlpinaShop has spent three modules generating data without ever looking at it. Orders pile up in alpinashop-pedidos, visits sit in the load balancer logs, images are served from the CDN and shopping carts are abandoned in Firestore. Lucía, the analyst, has a notebook full of questions nobody has answered: which backpack sells best in Catalonia, whether the autumn campaign moved the needle, why 30 % of shopping carts stall at the shipping step, what an order is worth on average and how that number has evolved over two years.

Most people's instinctive reaction to that problem is to open a connection to the production database and start writing SELECT. In 02-03 we already avoided the worst of it by creating the read replica alpinashop-pedidos-replica-informes, so that reports would not compete with purchases. But that replica is a sticking plaster. A query that walks two years of order lines to group by category and month will still take minutes and will still be awkward to write, because PostgreSQL is not built for that. PostgreSQL is built to answer "give me order 48213" in a millisecond, not to answer "give me the average of every order over the last two years grouped by six dimensions".

In this lesson you will understand why those two questions need different machines, you will create the alpinashop_analitica dataset in alpinashop-datos, you will load the order history into it, you will write the SQL that genuinely answers Lucía's questions and — this is what separates someone who knows how to use BigQuery from someone who gets a surprise invoice — you will learn exactly how you pay and how you avoid overpaying.

1. Why an analytical warehouse is not "a bigger database"
2. How BigQuery works inside: columns, separation and Dremel
3. Cloud SQL versus BigQuery: the table you need to internalise
4. Structure: project, dataset, table and view
5. Creating the alpinashop_analitica dataset
6. Data types, STRUCT and ARRAY: denormalisation as a virtue
7. Creating AlpinaShop's tables
8. Loading data: from Cloud Storage, in streaming and without loading at all
9. Bringing the history over from Cloud SQL with federated queries
10. Business SQL: Lucía's questions, answered
11. The cost model and how not to bankrupt yourself
12. Partitioning and clustering: the before and after, measured
13. Materialized views, result cache and quotas
14. Logical versus physical storage
15. Access control: dataset, table, column and row
16. INFORMATION_SCHEMA: auditing who spends and on what
17. BigQuery ML, mentioned and deferred

There are two families of workload over data, and confusing them is the most expensive conceptual mistake in this profession.

OLTP (online transaction processing) is what alpinashop-pedidos does. Many very small, concurrent operations that read and write a handful of rows identified by primary key, with strict transactional guarantees. "Insert this order", "update the stock of SKU MOCH-40L-AZ", "give me user 8821's shopping cart".

OLAP (online analytical processing) is what Lucía wants. Few, very large queries that read millions of rows, touch few columns of each one, aggregate, group and sort, and write nothing.

The difference is not one of size, it is one of shape. And that shape determines how the bytes are best stored on disk.

Picture the lineas_pedido table with 8 million rows and these columns: pedido_id, sku, nombre_producto, categoria, cantidad, precio_unitario, descuento, iva, fecha, pais_envio, metodo_pago, email_cliente, notas.

• PostgreSQL stores the data by rows: every field of line 1, then every field of line 2, and so on. It is perfect for "give me the whole of line 1", because it is contiguous on disk.
• Lucía's question, by contrast, is SELECT categoria, SUM(cantidad * precio_unitario) FROM lineas_pedido GROUP BY categoria. It needs only three of the thirteen columns. But to read those three, PostgreSQL has to drag all thirteen off the disk, because they are interleaved. It reads ten times more bytes than it uses.

BigQuery stores the data by columns: all the categoria values together, all the cantidad values together. The query above reads exactly three blocks of disk and never touches the other ten. And since each block holds values of the same type that are very repetitive — categoria will have a dozen distinct values across eight million rows — it compresses brutally well.

From that follow the two rules that govern everything else in BigQuery:

• Reading few columns is cheap; reading all of them is expensive. That is why SELECT * is, literally, the fastest way to spend money.
• Filtering on a column does not avoid reading it. If you filter with WHERE pais_envio = 'ES', BigQuery has to read all of the pais_envio column to know which rows match. The only way not to read data is for it to be partitioned or clustered, which we will see in section 12.

Three architectural decisions explain why BigQuery behaves the way it does.

Its own columnar storage (Capacitor). The data is kept in a compressed columnar format on Google's distributed file system (Colossus), replicated automatically. You never see files, you do not manage indexes, you do not run VACUUM and you do not define tablespaces. There is nothing to administer.

Complete separation of storage and compute. This is the most important one and the one that changes your mindset. In Cloud SQL, disk and CPU live on the same machine: if you need more CPU you also pay for more machine, and if the database is idle the machine is still switched on and billing. In BigQuery there is no machine.

• Storage is billed per GB per month held. It always exists.
• Compute is billed per query executed (or per reserved capacity). If nobody queries, compute costs zero.

The immediate consequence for AlpinaShop: you can have 2 TB of history stored and, if Lucía goes on holiday, pay only for the storage — a few euros — without shutting anything down. And the other way round: a single query can mobilise hundreds of machines for 8 seconds and then hand them back. That elasticity is impossible in a server-based model.

Distributed execution (Dremel engine). When you launch a query, the planner breaks it down into an execution tree. The leaves of the tree — the workers — read fragments of columns in parallel, each does its share of the aggregation, and the intermediate results travel up the tree, mixing in an in-memory shuffle layer until the final result comes out.

The unit of that compute is called a slot: a slot is, roughly, a slice of CPU with its associated memory. A query uses as many slots as the planner decides and as are available. We will come back to slots in section 11, because they are the key to the capacity pricing model.

The right way to read this table is not "BigQuery is better". It is: they are complementary tools and AlpinaShop needs both. The shop will carry on buying against Cloud SQL. Lucía will analyse against BigQuery. The job of this lesson and the ones that follow is to build the pipe that carries the data from the first to the second.

BigQuery's hierarchy rests on the resource hierarchy you already know from 01-04:

Four points of precision that avoid problems later on:

• The project is the billing unit. The queries Lucía launches are charged to the project where they run, not to the project where the table lives. That allows marketing, for instance, to query data in alpinashop-datos while paying from its own project. For AlpinaShop, everything runs and is billed in alpinashop-datos, which already carries its centro-coste label.
• The dataset is the unit of organisation and of permissions. It is where roles are granted, and it is what gets shared.
• The dataset's location is immutable. It is decided at creation time and cannot be changed: to move it you have to create another one and copy the data. We will choose europe-west1, consistent with the rest of AlpinaShop's infrastructure and with the GDPR requirement to keep European customers' personal data in the EU.
• You cannot JOIN across datasets in different locations. If someone creates alpinashop_marketing in us-central1 tomorrow, they will not be able to cross it with alpinashop_analitica. It is the most common irreversible design mistake in BigQuery. Fix the location as a team standard from day one.

A table's full name is project.dataset.table, and in standard SQL it is written between backticks when the project contains hyphens:

A note on naming: dataset and table identifiers accept letters, numbers and underscores, but not hyphens. That is why the project is alpinashop-datos (with a hyphen) and the dataset is alpinashop_analitica (with an underscore).

We will work with the bq tool, which comes bundled with the gcloud CLI and with Cloud Shell since 01-06.

Option by option:

• --location=europe-west1: the immutable location. It goes before the mk subcommand, not after; that is a common mistake.
• --dataset: says that the resource to create is a dataset and not a table.
• --default_table_expiration=0: no automatic expiry. If you put 2592000 (30 days in seconds), every table created here would self-destruct after 30 days. It is extremely useful in a test dataset and catastrophic in production, so it is worth being explicit.
• The three labels replicate the entorno/equipo/centro-coste scheme AlpinaShop settled on in 01-04, so that the analytics cost can be isolated in the billing report.

It is also worth creating a working dataset with an expiry, so that the temporary tables from analyses do not pile up forever:

Verification:

In the output of bq show, look at "location": "europe-west1". If it says US, you have created the dataset in the wrong place: delete it now, before putting data in it, because afterwards there is no cheap way back.

BigQuery's scalar types are few and deliberately strict:

A rule that saves grief: amounts go in NUMERIC, never in FLOAT64. With FLOAT64, adding €1.10 and €2.20 can give 3.3000000000000003, and that phantom cent will show up in a board report at the worst possible moment.

And now the part that sets modelling in BigQuery apart from classic relational modelling: nested types.

• ARRAY<T>: an ordered list of values of type T inside a single cell.
• STRUCT<...>: a record with named fields inside a single cell.
• They combine: ARRAY<STRUCT<sku STRING, cantidad INT64, precio NUMERIC>> is "a list of order lines inside the order's row".

In PostgreSQL, an order with three lines is four rows spread across two tables joined by a foreign key. In BigQuery it can be a single row that contains its three lines inside it.

Why is that better here? Because in a distributed engine, a JOIN between two large tables forces data to move between machines through the shuffle layer, and that is what is expensive. If the lines already travel glued to their order, the JOIN disappears: it becomes an UNNEST, which is a local operation inside each worker, with no network in between.

That row contains a complete order. envio is a STRUCT (a record), and lineas is an ARRAY<STRUCT<...>> (a list of records). Everything needed to analyse the order is in one single place on disk.

The practical modelling rule: normalise in OLTP to avoid duplication when writing; denormalise in OLAP to avoid JOIN when reading. Storage is cheap (a few cents per GB per month); shuffle is not.

That said, you should not take it to extremes. For AlpinaShop we will keep pedidos and lineas_pedido as separate tables — because that is how they arrive from PostgreSQL and it makes incremental loading easier — and we will nest where it genuinely pays off: the shipping block inside the order, and the events inside the browsing session. It is an honest middle ground and a very common one in practice.

We are going to create the four tables with DDL in SQL, which is more readable and more versionable than a schema JSON. These statements are run in BigQuery Studio, the console's query interface (BigQuery menu), or from bq query --use_legacy_sql=false.

The three details that matter:

• PARTITION BY fecha_pedido physically splits the table into one partition per day. A query that filters by date will only read the partitions it needs.
• CLUSTER BY estado, canal sorts the data inside each partition by those columns, in that order. Filters on estado will be able to skip whole blocks.
• require_partition_filter=TRUE is the best defensive decision in the whole lesson: it rejects any query that does not filter on fecha_pedido. A SELECT * FROM pedidos will fail with an explicit error instead of scanning two years of data. It is a cheap seat belt that prevents absurd invoices.

A design note about lineas_pedido: we have duplicated fecha_pedido from the header. In a relational model that would be reprehensible redundancy. Here it is essential, because a table can only be partitioned by a column of its own. Without that duplication, any JOIN between orders and lines would force a scan of the entire lines table. It is the perfect example of why OLTP modelling rules do not transfer literally.

And the browsing table, which does make full use of nesting:

A 40-click session is one row, not 40. Counting how many sessions reached iniciar_pago but not compra — the abandoned-cart question Lucía brought along — will be a local operation, with no JOIN and no shuffle.

There are four ways of getting data into BigQuery, and picking the wrong one is a classic source of cost and pain.

It is the workhorse and it is free, which surprises a lot of people. Google does not charge for batch ingestion: it charges for the resulting storage and for the subsequent queries.

Suppose the nightly dump of the order history leaves files in the bucket. Supported formats: CSV, line-delimited JSON (NDJSON), Avro, Parquet and ORC.

• The pedidos-*.csv wildcard lets you load dozens of files in a single operation, and BigQuery processes them in parallel. It is far better than one giant file.
• --null_marker='\N' translates the null marker pg_dump uses in CSV format; without it you would end up with the literal string \N in the cells.
• Passing the schema in a JSON file is preferable to --autodetect. Autodetection guesses, and guessing with money is a bad idea: it is quite capable of inferring FLOAT64 for a column of amounts or STRING for a date in an odd format.

An honest comparison of formats:

Recommendation for AlpinaShop: Avro or Parquet for the automated processes, because they carry the schema inside and there is no type ambiguity and no surprises with commas in product names. CSV only for what arrives from third parties, such as the carrier file we will see in 04-05.

When the data has to be available within seconds — the real-time autumn campaign dashboard — you use the Storage Write API, which replaces the old tabledata.insertAll and is cheaper and offers better guarantees.

Two important warnings about streaming:

1. It costs money per GB inserted, whereas batch loading is free. If the data can wait an hour, do not stream it: accumulate it in the bucket and load it in batch. It is one of the most profitable and most ignored cost optimisations there is.
2. For AlpinaShop, the real route will not be this code inside the Flask application, but Pub/Sub → BigQuery (04-04) or Pub/Sub → Dataflow → BigQuery (04-02). Writing straight to BigQuery from the website couples the shop to the analytical warehouse: if BigQuery has an incident, we do not want the shop to stop selling.

Sometimes the data is already in alpinashop-catalogo and duplicating it is not worth it. An external table leaves the files where they are and BigQuery reads them at query time.

Advantages: zero duplication, zero ingestion cost, the data always reflects the bucket. Drawbacks: slower queries, no partitioning or clustering, and no result cache. Use them for data queried now and again, or as a landing zone before a real load.

BigLake is the evolution of external tables: it adds fine-grained access control — column-level and row-level permissions over data that lives in the bucket — without the analyst needing permission on the bucket. It relies on a BigQuery connection with its own service account, which is what accesses Cloud Storage. For AlpinaShop, that means Lucía will be able to query the export CSVs without having storage.objectViewer on alpinashop-catalogo, which fits the least privilege of 03-04.

The order history lives in alpinashop-pedidos. There is an elegant way of bringing it over: EXTERNAL_QUERY, the federated query, which runs SQL directly on PostgreSQL and returns the result to BigQuery as if it were a table.

First, the connection. Marta creates it once:

Note two deliberate decisions:

• We point at the read replica alpinashop-pedidos-replica-informes, not at the primary instance. That is exactly what it was created for in 02-03: so that reports never touch the database that serves purchases.
• We use the informes_lectura user, which only has SELECT. The real password must come from Secret Manager (db-password-catalogo is the catalogue's one; this will get its own secret), never from a file in the repository, as we established in 03-06.

Now the history load, in a single statement:

How to read it:

• The string inside EXTERNAL_QUERY is PostgreSQL SQL, not BigQuery SQL. It runs over there, with that syntax. The triple quotes save you from escaping the internal single quotes.
• Filtering inside that string (WHERE creado_en >= ...) is critical: the fewer rows that cross the network, the better. Filtering outside, in BigQuery, would bring everything over anyway.
• The STRUCT(...) builds the nested envio block out of the six flat columns PostgreSQL returns. This is where you can see the translation between the two models.

When NOT to use federated queries: for large, repeated loads. EXTERNAL_QUERY puts load on the PostgreSQL instance and does not parallelise; it is for the initial dump and for bringing over small, fresh tables (the product master, for example). The daily synchronisation of the history will be done with the export → load pattern orchestrated in 04-06, or with CDC, which we will see in 04-05.

BigQuery uses GoogleSQL, an ANSI-standard dialect with extensions. If you know SQL, you know 90 % of it. Let us get to the real questions.

The WHERE on fecha_pedido is not cosmetic: it is what activates partition pruning and what stops require_partition_filter=TRUE from rejecting the query. Without it, BigQuery would read the whole table.

APPROX_QUANTILES computes percentiles approximately but far more cheaply than an exact calculation over millions of rows. Putting the median next to the mean is not a statistical whim: if a €4,000 corporate order lands in March, the mean shoots up and the median does not flinch. Showing only the mean is how you mislead a board of directors without meaning to.

Be careful with NOT IN and nulls: if the subquery returned any null sku, NOT IN returns zero rows without warning. In production, LEFT JOIN ... WHERE l.sku IS NULL or NOT EXISTS is safer.

Two gems here:

• OVER (PARTITION BY categoria) computes over each group without collapsing the rows, which is precisely what a GROUP BY cannot do.
• QUALIFY filters on the result of a window function directly, with no need to wrap everything in another subquery. It is a GoogleSQL extension and it saves an enormous amount of noise.

UNNEST(eventos) turns each session's array of events into rows, and the comma before it is an implicit CROSS JOIN with the parent row: every event keeps access to sesion_id, pais and the rest. It is local to each worker, with no shuffle. The last field answers directly the abandoned-cart question Lucía had been carrying since module 3, and NULLIF(..., 0) avoids the division by zero on a day with no checkouts started.

This section is worth the price of the lesson on its own. BigQuery is wonderful and it is also the service that gives most people a fright when the invoice arrives.

There are two compute models, and they can be mixed per project:

The current editions are Standard, Enterprise and Enterprise Plus, with slot reservations that can autoscale (you pay for the slots used, with a minimum) or be committed for one or three years at a discount. Each adds features: Standard covers the basics, Enterprise adds governance and CMEK, Enterprise Plus adds advanced recovery and strict residency.

For AlpinaShop the decision is on demand, and it should be said with the numbers: a team of three people querying a few hundred gigabytes a month spends a few euros. Reserving slots would carry a much higher fixed monthly cost. The industry rule of thumb is to migrate to capacity when on-demand spend stably exceeds the cost of the equivalent reservation, something that usually happens from tens of terabytes queried per month onwards.

Now, what you really have to do every day.

It returns something like:

428 MB. At ~$5/TB, about $0.002. No problem. But that same calculation on a badly written query can come back with 900 GB and cost $4.50 every time somebody hits run. Ten analysts refreshing a dashboard every five minutes turn that into a four-figure monthly invoice.

In BigQuery Studio you do not need the command: the interface shows, top right, in real time as you type, "This query will process X". Teach everyone who gets access to look there. It is the most profitable habit you can install in a data team.

You pay for the bytes of the columns read. SELECT * on pedidos reads all fourteen columns; SELECT pedido_id, total_pedido reads two. Typical difference: between five and twenty times.

LIMIT does not reduce the cost. It is misunderstanding number one. To inspect data use bq head or the console's Preview tab: they read the storage directly and cost nothing.

And a handy trick when you want many columns except one:

We already declared partitioning and clustering when creating the tables. Now let us measure exactly what they buy.

Partitioning: splits the table into chunks by the value of a column (date, integer range, or ingestion time). BigQuery discards whole partitions without reading them if the WHERE allows it.

Clustering: physically sorts the data inside each partition by up to four columns. It allows blocks within the partition to be skipped.

The experiment, with an orders table covering two years (let us say 40 GB):

From 3 GB to 4 MB: 750 times less. And not a single letter of the SQL has changed, only the physical definition of the table. Over a year of daily queries, that difference is what separates a three-euro invoice from a two-thousand-euro one.

Clustering does not show up in the --dry-run because its saving is only known at execution time (the planner does not know in advance how many blocks it will be able to skip). It is measured on the real result:

A quick decision guide:

A nuance about the order in CLUSTER BY estado, canal: the order matters. Clustering sorts first by estado and within that by canal. A filter on canal alone makes far less use of the clustering than a filter on estado. Put the column you filter on most first.

Three more mechanisms for paying less, in increasing order of effort.

If you run exactly the same query over data that has not changed, BigQuery returns the cached result: zero cost and an immediate answer, for about 24 hours. It is invalidated when any table involved is modified.

It gets broken accidentally more easily than it seems: it is enough for the query to use CURRENT_TIMESTAMP() or other non-deterministic functions, or for the text to change by one character. If a dashboard uses WHERE fecha >= CURRENT_DATE() - 7, it will never cache properly. It is worth fixing explicit dates whenever the report allows it.

A view is saved SQL: it takes up no space and it is run — and paid for — in full every time.

A materialized view stores the precomputed result and BigQuery refreshes it incrementally when the base table changes. On top of that, and this is the best part, the optimiser uses it automatically even when you query the original table.

The Looker Studio dashboard Lucía will build in 04-07 will read a few megabytes from this materialized view instead of scanning the lines table every time someone opens the report. Limitations to be aware of: it only supports a subset of SQL (aggregations yes; JOIN with restrictions and window functions no), and the incremental refresh consumes compute, so do not create twenty of them.

Hard limits on bytes processed can be imposed, per project and per user:

And at session or individual-query level:

For AlpinaShop the policy is: a project quota of 2 TB/day, require_partition_filter on the large tables, and a budget alert in Billing (01-04) at €50 a month on the centro-coste:analitica label. Three independent safety nets, none of them expensive.

Storage has its own model, and for some years now you can choose how it is billed per dataset.

The price per GB in physical mode is roughly double, but the GB are usually between 4 and 10 times fewer because BigQuery compresses repetitive columns very well. For data like AlpinaShop's — categories, statuses, countries repeated millions of times — physical mode usually comes out clearly cheaper. You have to measure it before switching:

There is also long-term pricing: a partition that is not modified for 90 consecutive days automatically drops in price, by around half. It is automatic, there is nothing to do, and it affects neither performance nor availability. Since most of AlpinaShop's history is never touched, a good part of the storage will end up at that reduced rate on its own.

Watch out for one detail: any UPDATE on a partition resets its 90-day counter. A badly designed process that rewrites the entire history every night keeps the whole table at the high rate forever. It is another argument in favour of incremental loads by partition.

Everything from IAM (03-04) applies here, plus two layers that only exist in BigQuery.

The relevant predefined roles:

The nuance that confuses everybody: dataViewer is not enough to query. Seeing the data and running a job are different permissions. You also need jobUser on the project where the query runs. If Lucía says "I can see the table but I cannot query it", this is it 90 % of the time.

As always since 03-04: permissions to groups, never to people. When another analyst joins, it is enough to put them in gcp-datos@.

Let us also remember the custom role analistaCatalogo created in 03-04 in alpinashop-datos. Now it makes sense to complete it with the minimum analytical permissions:

GDPR warning. email_cliente is personal data. Processing it for analytical purposes requires a legal basis, minimisation and restricted access. What follows is a technical control, not a legal opinion: any processing of real personal data must be reviewed by a compliance professional or the DPO before going into production. All the data in this course is fictitious.

The control is done with Data Catalog policy tags. The idea: you tag the column, and only someone holding the Fine-Grained Reader role on that tag can read it.

And it is applied to the column:

From that moment on, if Lucía runs SELECT * FROM pedidos, the whole query fails with an access error on email_cliente. She has to write SELECT * EXCEPT(email_cliente) or list the columns. That is a desirable side effect: it reinforces the habit of not using SELECT *, which we already know is expensive as well.

For AlpinaShop, the full recommendation is not to bring the raw email into the analytical warehouse at all. Lucía needs to know how many distinct customers bought, not who they were. A pseudonymised identifier does the job just as well:

The salt has to come from Secret Manager (03-06) and not be written into the SQL as it is here; without a salt, a hash of an email is reversible by brute force and is still personal data. In 04-07 we will see the specific tool for this, Sensitive Data Protection.

A row access policy filters which rows each identity sees. Useful if AlpinaShop opens a French subsidiary tomorrow and each sales team should only see its own market:

The filter is applied transparently and inescapably: the user writes their normal SELECT and only gets Spanish rows, without knowing the policy exists.

If you want to give access to the result but not to the base table, you use an authorized view: permission is granted on the view, the view has permission on the table, and the user does not. It is the clean mechanism for exposing aggregated data to marketing without opening up the order detail to them.

BigQuery observes itself. INFORMATION_SCHEMA is a set of read-only views with metadata and with the job history. This query ought to be run once a week in any serious team:

The price of 6.25 is hard-coded as an order of magnitude: replace it with the one in force in your region and verify it in the official documentation, because it changes.

Other useful queries from the same place:

That last one is the analytical equivalent of the orphan-disk clean-up in 02-01: in every data warehouse a year old there are _backup_final_v2 tables nobody remembers and that are paid for every month.

BigQuery includes BigQuery ML, which lets you train models with SQL syntax: CREATE MODEL ... OPTIONS(model_type='linear_reg'), and then ML.PREDICT. You can do regression, classification, time series with ARIMA_PLUS, k-means, matrix factorisation for recommendations and even invoke Vertex AI models from SQL.

For AlpinaShop it is a very attractive route: demand forecasting per SKU ahead of the autumn campaign, or customer segmentation, without taking the data out of BigQuery or setting up any infrastructure.

But that is module 5. There we will look at Vertex AI, AutoML and recommendation models with the rigour they deserve: how training and validation data are separated, how a model is evaluated and why a good metric in testing can be useless in production. Here we simply note that the door exists and that it is one CREATE MODEL away.

Creating the dataset in US by accident. It is the console's default option and it is irreversible. As well as preventing JOINs with europe-west1, it takes European customers' personal data outside the EU. Always check the location before loading the first byte, and fix --location=europe-west1 in the team's configuration.

Believing that LIMIT makes the query cheaper. It does not: the same bytes are read. To look at data, bq head or the preview, which are free.

Using SELECT * out of habit. In a columnar engine it is between five and twenty times more expensive. Write out the columns, or use SELECT * EXCEPT(...).

Forgetting the partition filter. Without WHERE fecha_pedido ..., a partitioned table is read in full and the partitioning is useless. Turn on require_partition_filter=TRUE and let the engine protect you.

Using FLOAT64 for amounts. The rounding errors show up in exactly the report management sees. NUMERIC always for money.

Doing UPDATE and DELETE as in PostgreSQL. BigQuery supports them, but they rewrite entire partitions, they are expensive and they reset the long-term counter. For bulk updates, MERGE or rewriting the whole partition with WRITE_TRUNCATE. And to correct one field of one order, do it in Cloud SQL and resynchronise.

Writing to BigQuery from the web application. It couples the shop to the analytical warehouse. Publish to Pub/Sub (04-04) and let another process do the writing. The shop must be able to sell even when analytics is down.

Granting dataViewer and forgetting jobUser. It is the most repeated support ticket in the BigQuery world.

Putting LIMIT on a query that sorts. ORDER BY over millions of rows forces a global sort that can exhaust resources with the Resources exceeded error. Aggregate first, sort afterwards; or use window functions with QUALIFY.

Tip: use labels on your jobs. bq query --label=proceso:panel-direccion lets you separate out in INFORMATION_SCHEMA how much each consumer of data costs. When someone asks "how much does the management dashboard cost us?", you will have the number.

Tip: keep the DDL in Git. The CREATE TABLE statements in this lesson are code. They belong under version control alongside the rest of the project, not only inside BigQuery. In 06-07 we will turn them into Terraform.

In alpinashop-datos, create a test dataset called alpinashop_ejercicios in europe-west1 whose tables expire after 3 days. Inside it, create a table opiniones with: opinion_id (STRING, required), sku (STRING, required), fecha (DATE, required), puntuacion (INT64), texto (STRING), pais (STRING). It must be partitioned by fecha, clustered by sku and reject queries with no partition filter. Then insert three sample customer reviews and check with --dry-run how many bytes it costs to query a single day's worth.

Using the alpinashop_analitica tables, write a single query that returns, for each month of 2026 and only for orders that are neither cancelled nor returned: the month, the number of orders, total revenue, the average order value, the best-selling category of that month and the percentage that category represents of the month's total. Order by month. Before running it, estimate its cost with --dry-run.

Marta gets the budget alert: alpinashop-datos has spent €180 this month when the historical figure was €12. Nobody has loaded any new data. Write the INFORMATION_SCHEMA queries that let you (a) identify the user or service account responsible, (b) isolate the specific query and how many times it has run, and (c) determine whether it makes use of the cache. Then propose three concrete corrective measures, ordered from the most immediate to the most structural.

Solution 1

The result will be a few hundred bytes: with three rows, it is almost all metadata. What matters is the habit, not the figure. And if you remove the WHERE fecha, the query will not be expensive: it will fail, because require_partition_filter=TRUE rejects it. That error is exactly the behaviour we want.

Solution 2

The keys to the solution:

• The CTEs (WITH) break the problem into readable steps. BigQuery evaluates them as part of the plan; they do not create tables and they do not cost extra.
• QUALIFY ROW_NUMBER() OVER (...) = 1 keeps the leading category of each month in a single pass. ROW_NUMBER and not RANK because we want exactly one row even in a tie.
• The SUM(...) OVER (PARTITION BY month) computes the month's total without collapsing the rows, which is what allows the division that gives the share.
• The date filters are on both partitioned tables. Leaving them out of lineas_pedido would make the JOIN scan the whole table: the query would give the same result and cost twenty times more.
• NULLIF(..., 0) protects against division by zero in a month with no sales.

The --dry-run over two years of history should come back with a few hundred megabytes: cents. Without the partition filters, tens of gigabytes.

Solution 3

(a) Identify who is responsible:

(b) Isolate the query and count its executions:

(c) Check how well the cache is being used:

The typical and predictable diagnosis: the culprit is not a person but a dashboard that auto-refreshes every 5 minutes running SELECT * FROM lineas_pedido with no date filter. A cache_pct close to zero confirms it: if the query carries CURRENT_TIMESTAMP() or the table is receiving continuous writes, the cache is never any use. 288 executions a day × 2 GB × 30 days ≈ 17 TB ≈ €100.

Three corrective measures, from the immediate to the structural:

1. Today (containment): apply --maximum_bytes_billed to the dashboard's user or service account and set the project's daily quota at 2 TB. The bleeding stops within minutes even if nobody touches the dashboard.
2. This week (correction): rewrite the dashboard's query with explicit columns and a partition filter, and drop the refresh from 5 minutes to 1 hour. That is usually enough to divide the cost by a hundred.
3. This month (structural): create a materialized view with the aggregation the dashboard needs and point the dashboard at it; turn on require_partition_filter=TRUE on the large tables so the problem cannot recur; and schedule the weekly audit query with an alert if any user goes over a TB threshold.

Measure 3 is the only one that prevents a relapse. The first two buy time.

AlpinaShop now has a data warehouse. In this lesson you have understood the real difference — not the slogan version — between OLTP and OLAP: it is not a matter of size but of access pattern, and that pattern is what justifies columnar storage, the separation of storage and compute, and the distributed execution across slots that makes a query over eight hundred million rows finish in seconds.

You have created the alpinashop_analitica dataset in alpinashop-datos, in europe-west1, knowing that this location is an irreversible decision with compliance implications and consequences for your ability to cross data. You have defined the tables pedidos, lineas_pedido, productos and visitas with the right types — NUMERIC for money, always — with a STRUCT for the shipping block and with an ARRAY<STRUCT<...>> of events inside each session, understanding why denormalisation, which in PostgreSQL would be a mistake, is here the way to avoid the shuffle that is what is genuinely expensive in a distributed engine.

You have loaded data by all four routes and you know which to use: batch from Cloud Storage — free — for volume, streaming only when seconds matter, external tables and BigLake for what is not worth duplicating, and EXTERNAL_QUERY against the alpinashop-pedidos-replica-informes replica for the initial dump of the history, deliberately pointing at the replica and with the read-only informes_lectura user.

You have written the SQL that answers the questions Lucía had been accumulating for three modules: sales by category and month, average order value with the median beside it so as not to mislead anyone, products that do not sell, per-category rankings with window functions and QUALIFY, and the complete conversion funnel with UNNEST over the nested events, which finally puts a number on that 30 % of abandoned shopping carts.

And, above all, you have learned how not to bankrupt yourself: --dry-run before running, SELECT * banished, LIMIT unmasked as a false economy, partitioning by date that turned 3 GB into 4 MB, clustering ordered by the column you filter on most, require_partition_filter as a seat belt, materialized views for the dashboards, a result cache that is free as long as you do not break it, quotas per project and per query, physical versus logical storage measured with your own data, and INFORMATION_SCHEMA as the tool that turns "the invoice has gone up" into "this query, this user, 288 times a day". You have closed off access with roles granted to groups, you have remembered that dataViewer without jobUser queries nothing, and you have protected email_cliente with a policy tag — with the express GDPR warning and the recommendation not to bring the raw email into the warehouse at all, but a pseudonymised identifier salted from Secret Manager.

There is one obvious gap left. Everything we have loaded is history: a snapshot of what has already happened, brought over from PostgreSQL in one go. But AlpinaShop never stops selling. Right now, as you read this, there are customers browsing the catalogue, adding backpacks to their shopping carts and confirming orders, and none of those facts reaches alpinashop_analitica. Synchronising by hand every night is a sticking plaster that breaks the moment somebody asks "I want to see the campaign's sales today, not tomorrow".

In 04-02, Cloud Dataflow, we will build the missing pipe. You will see what a data pipeline is and why a script on a VM is not enough; you will learn the Apache Beam model, which describes both a batch process and a real-time one with the same code; you will write in Python a pipeline that reads the history from the alpinashop-catalogo bucket, cleans it, aggregates it and writes it into the tables you have just created; and you will step into the genuinely interesting territory of streaming — event time versus processing time, watermarks, windows and late data — to leave ready the pipeline that will consume the pedidos-nuevos topic as soon as we create it in 04-04. When you finish that lesson, data will stop arriving when somebody remembers and will start arriving on its own.