Przejdź do treści
Biblioteka skilli
Dane i analityka · Przegląd i audyt

bigquery-patterns

/bigquery-patterns

Pilnuje dyscypliny SQL w BigQuery: suche przebiegi, limity bajtów, przycinanie, bezpieczne złączenia i kontrole spójności.

Repozytorium
cardume ↗
Licencja
MIT
Ostatnia zmiana repozytorium
04.11.2026

Ocena i bezpieczeństwo

100/ 100Klasa: dobry
Format25 / 25
Frontmatter z nazwą i opisem, poprawna nazwa, opis do 1024 znaków, nazwa zgodna z folderem.
Odkrywalność25 / 25
Opis mówi, kiedy użyć skilla, ma sensowną długość i jest w 3. osobie.
Treść35 / 35
Co najmniej 150 słów i do 500 linii, nagłówki, listy kroków, przykłady albo kod.
Higiena15 / 15
Bez zaślepek typu TODO, bez ścieżek Windows, z licencją.
Ryzykobrak sygnałów w skanie
Pliki w paczce
2
Skrypty
0
Komentarze HTML
0
Adresy URL w SKILL.md
0
Przestarzałość

Brak sygnałów. Repozytorium zmienione 04.11.2026, bez odwołań do wycofanych modeli i API.

Licencja

MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.

Co robi i kiedy

Pilnuje dyscypliny SQL w BigQuery: suche przebiegi, limity bajtów, przycinanie, bezpieczne złączenia i kontrole spójności.

Oryginalny opis (EN)

BigQuery SQL discipline: dry runs, byte caps, pruning, safe joins, integrity checks. Use when writing, reviewing or optimizing BigQuery SQL, or when a query is slow or expensive.

Jak działa

  1. 1. Cost control
  2. 2. Correctness
  3. 3. Performance
  4. 4. Running statements safely
  5. 5. Proving a change kept the data intact
  6. Worked example: a churn list that comes back empty
Sekcje z nagłówków SKILL.md, w oryginale · 141 linii, 1 340 słów
Podgląd SKILL.md
---
name: bigquery-patterns
description: "BigQuery SQL discipline: dry runs, byte caps, pruning, safe joins, integrity checks. Use when writing, reviewing or optimizing BigQuery SQL, or when a query is slow or expensive."
allowed-tools: Read Write Edit
---

# BigQuery patterns

In BigQuery the expensive and the wrong decisions are both made in the SQL text: what it reads
decides the bill, how it joins decides whether the numbers are right, and a write cannot be taken
back by cancelling the job. This skill is organised by the question you are answering: what will
it cost, is it correct, is it fast enough, is it safe to run, and did the change keep the data
intact. Copy-ready SQL is in `reference/sql-patterns.md` (cited as reference §N).

The project's rules file (`CLAUDE.md`, `docs/standards.md`) overrides the defaults here: the
confirmation threshold, the projects per environment, required labels and naming.

## 1. Cost control

**The byte-estimate rule.** Before running a query, get its estimate with
`bq query --use_legacy_sql=false --dry_run` (or `QueryJobConfig(dry_run=True)`; reference §6). The
estimate is free and is the only input to this rule:

- **Estimate at or above the confirmation threshold** (default **100 GiB**; a rules-file value such
  as `bigquery_confirm_bytes: 50GiB` wins): show the query, the estimate and the project it will
  bill, and wait for the user's go-ahead.
- **Below the threshold**: run it, with `--maximum_bytes_billed` set a little above the estimate
  (about 1.5×) so a change between dry run and run fails instead of billing more.
- **No estimate possible** (permissions, a tool without a dry-run flag): treat it as above the
  threshold and say why.

On-demand pricing bills the estimate; capacity (editions) pricing bills slot time instead, but
bytes read still drive slot use, so the same rule applies.

**Read less.** In order of usual payoff:

1. **Columns.** BigQuery is columnar: `SELECT *` reads every column, and `LIMIT` does not reduce
   bytes. Name the columns the result needs; `SELECT * EXCEPT (...)` only for exploration.
2. **Partitions.** Filter the partition column against constants or parameters so the planner can
   prune (reference §7). On a `TIMESTAMP`-partitioned table write a half-open range
   (`ts >= TIMESTAMP(d) AND ts < TIMESTAMP(DATE_ADD(d, INTERVAL 1 DAY))`); functions on the column
   or bounds from a subquery may prevent pruning. Confirm with the dry-run estimate, which drops to
   roughly one partition's size when pruning works.
3. **Clustering.** Columns that are filtered or joined on often (`user_id`, `customer_id`,
   `country`) as cluster keys cut bytes further within each partition; the dry run cannot show
   that saving, the billed bytes afterwards can.
4. **Do the work once.** A scheduled query that re-aggregates raw events every day should usually
   become an incremental table that processes only the new partition (reference §10), or a
   materialized view when the shape fits.

Guard tables against accidental full scans with `require_partition_filter = TRUE` on large
partitioned tables, and give scratch datasets a default table expiration.

## 2. Correctness

Most wrong numbers in BigQuery come from five places. Check each one a query touches:

| Risk | What goes wrong | Defence |
| :--- | :--- | :--- |
| Join fan-out | A key assumed unique on one side is not, rows multiply, sums inflate | Prove uniqueness of the join key before joining (reference §2–3); compare row counts before and after the join |
| Lost rows | `INNER JOIN` or a `WHERE` on the right side of a `LEFT JOIN` silently drops unmatched rows | Count orphans; keep unmatched rows with an explicit unknown member |
| Casts | `CAST` aborts on bad input; `SAFE_CAST` turns it into `NULL` without telling anyone | `SAFE_CAST` plus a count of values that were non-null before and null after (reference §4) |
| `NULL` logic | `col != 'x'` excludes `NULL`; `NOT IN` with a `NULL` in the list returns nothing; `COUNT(col)` skips `NULL` | `IS DISTINCT FROM`, `NOT EXISTS`, and say which count you mean |
| Time | `CURRENT_DATE()` is UTC; `DATE(ts)` uses UTC unless given a zone; reruns on another day give another answer | Pass the run's logical date as a parameter; name the time zone (`DATE(ts, 'America/Sao_Paulo')`) |

Also: deduplicate on a business key with a deterministic tie-break (reference §5), use `NUMERIC`
or `BIGNUMERIC` for money rather than `FLOAT64`, and compare floating values with a tolerance.

## 3. Performance

Slow queries are usually doing more work than the answer needs, not short of slots.

- **Reduce early.** Filter and aggregate before joining; join the smaller, pre-aggregated side.
- **Avoid repeated work.** A CTE referenced several times may be evaluated several times; persist
  an expensive intermediate result in a temporary table inside a script when reused.
- **Window functions** with `PARTITION BY` on a skewed key put one huge group on one worker; check
  the key distribution first.
- **Approximate when allowed**: `APPROX_COUNT_DISTINCT` for exploration, exact counts for reported
  figures.
- **Design the table for its queries**: partition by the date most queries filter on, cluster by
  up to four columns most queries filter or join on (reference §9).
- **Read the plan**: the job's execution details (stage timings, records read and written, skew
  between average and maximum worker time) show which stage to fix. Find the costliest jobs with
  `INFORMATION_SCHEMA.JOBS` (reference §12).

## 4. Running statements safely

- **Environment first.** Every query names its project and dataset explicitly; never rely on the
  default project. Do not run exploratory or test statements against production; prepare them for
  the owner or the deployment pipeline instead.
- **Writes need approval each time.** `INSERT`, `UPDATE`, `DELETE`, `MERGE`, `TRUNCATE`, `DROP`,
  `CREATE OR REPLACE` and `ALTER`: state the target table, environment and expected rows affected,
  then wait for the user. `DELETE` and `UPDATE` always carry a `WHERE` that you have counted first
  with a `SELECT COUNT(*)` using the same predicate.
- **Keep a way back.** Before changing a table that matters, take a snapshot or note the time for
  `FOR SYSTEM_TIME AS OF` within the dataset's time-travel window (reference §14).
- **All or nothing.** Several dependent statements go in one script with
  `BEGIN TRANSACTION ... COMMIT TRANSACTION`, and values go in as query parameters, not string
  concatenation (reference §15).
- **Bound the runtime.** Set a job timeout (`jobTimeoutMs` / `job_timeout_ms`, or the operator's
  timeout) so a runaway job stops on its own; watch running jobs and cancel only after telling the
  user (reference §13).
- **Label jobs** with the labels the rules file requires (team, pipeline, environment) so cost can
  be attributed later.

## 5. Proving a change kept the data intact

A query that runs is not a query that is right. For every table a change writes, profile it before
and after on the same partitions and filter (reference §1):

- row count and distinct count of the grain key (duplicates must stay at zero);
- null count of every added or changed column;
- sum of each key measure, compared exactly when no change was intended;
- orphan count for each fact-to-dimension join.

Report the numbers, the deltas, and the explanation for each delta. "Looks the same" is not
evidence.

## Worked example: a churn list that comes back empty

A weekly job lists customers with no order this quarter from `crm.customers` and
`sales.orders` (partitioned by `order_date`):

```sql
SELECT customer_id, email
FROM crm.customers
WHERE customer_id NOT IN (
  SELECT customer_id FROM sales.orders WHERE order_date >= DATE_TRUNC(CURRENT_DATE(), QUARTER))
```

It returned rows for months, then zero after guest checkout started writing orders with a `NULL`
`customer_id`.

1. Diagnose before fixing: `SELECT COUNTIF(customer_id IS NULL) FROM sales.orders` over the same
   partitions. One `NULL` in the subquery makes every `NOT IN` comparison unknown (§2).
2. Rewrite as `NOT EXISTS` (or a `LEFT JOIN ... WHERE o.customer_id IS NULL` on a deduplicated
   order side), which ignores `NULL` keys and keeps the intent.
3. Replace `CURRENT_DATE()` with the run's logical date passed as a parameter, so a rerun on
   another day gives the same list, and check that the subquery still prunes partitions in the
   dry run (§1).
4. Prove it (§5): the row count of the new result against the old query run on a week before the
   first `NULL` appeared, and a count of guest orders now excluded on purpose.

Related: `cardume-data:lakehouse-architecture` (layer contracts and modelling),
`cardume-data:gcp-observability-patterns` (finding costly or failing jobs from telemetry).
Pliki w paczce (2)
  • SKILL.md 9 KB
  • reference/sql-patterns.md 12 KB

Wersje

W bazie nie ma innych skilli o nazwie bigquery-patterns.

Migawka bazy z 13.11.2026 · wersja paczki 6ef9f5c8933a (skrót SHA-256 całej paczki)

Pobrania w ostatnich 30 dniach: 0 · ranking

Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.

Podobne skille

  • 100bigquery-query-optimization
    Diagnozuje i przepisuje wolne zapytania SQL w BigQuery na podstawie planu zapytania: przycinanie partycji i klastrów, kolejność JOIN, skośne złączenia, selektywność WHERE i wybór funkcji.
  • 100bigquery-query-optimization
    Diagnozuje i przepisuje wolne zapytania BigQuery na podstawie planu: partycje, kolejność złączeń i błędy zasobów.
  • 90bigquery-sql
    Optymalizuje zapytania SQL w BigQuery pod kątem kosztu i wydajności, stosując sprawdzone zasady przetwarzania danych.
  • 100bigquery
    Odpytuje hurtownię BigQuery: SQL na tabelach, zdarzenia Amplitude w BigQuery oraz metryki, które słownik danych wskazuje jako przechowywane w BigQuery.
  • 100bigquery-optimization
    Optymalizuje środowisko BigQuery: pojemność, partycje, klastry, koszty przechowywania i zapytania SQL.
  • 92write-script-bigquery
    Prowadzi przez pisanie zapytań BigQuery zgodnie z konwencjami tej platformy.

Uwagi i zgłoszenia

Wiadomość trafi do Bartosza Sokolińskiego. Adres e-mail jest potrzebny tylko do odpowiedzi. Szczegóły w polityce prywatności.