Daten in BigQuery validieren

Ein praxisnaher Leitfaden zur Datenqualität in Google BigQuery — die sechs Prüfungen, die jede Tabelle braucht, wie Sie sie schreiben, ohne Terabytes zu scannen, und wie Sie daraus einen versionierten Data Contract machen, der nach Zeitplan läuft.

2026-08-29 · 7 min read

Um Daten in BigQuery zu validieren, schreiben Sie jede Erwartung als Aggregatabfrage, die eine Anzahl von Verstößen zurückgibt, und führen den Stapel nach jedem Ladelauf aus. Die sechs Prüfungen, die den größten Teil der Schäden abfangen, sind: Nullwerte in Pflichtspalten, doppelte Schlüssel, Werte außerhalb einer erlaubten Menge, Zahlen außerhalb eines plausiblen Bereichs, fehlerhaft formatierte Zeichenketten und veraltete Partitionen. Das Besondere an BigQuery ist, dass jede Prüfung Geld kostet: Abgerechnet wird pro gescanntem Byte, eine naive Validierungssuite, die bei jedem Lauf eine ganze Faktentabelle liest, ist also ein Posten auf der Rechnung und nicht nur ein Latenzproblem. Nahezu die gesamte Arbeit besteht darin, die Prüfungen billig zu machen.

Warum Validierung in BigQuery zuerst ein Kostenproblem ist

BigQuery hat keine Indizes und keine Zugriffspfade auf Zeilenebene. Eine WHERE-Klausel auf einer nicht partitionierten Spalte reduziert die gescannten Bytes nicht — sie filtert nach dem Lesen. Zwei Mechanismen senken die Kosten tatsächlich:

Partition Pruning. Ein Filter auf der Partitionierungsspalte (oder auf _PARTITIONTIME / _PARTITIONDATE bei Partitionierung nach Ingestionszeit) schränkt ein, welche Partitionen gelesen werden. Das ist der große Hebel.
Column Pruning. BigQuery ist spaltenorientiert, SELECT COUNT(*) FROM t WHERE col IS NULL liest also nur col und nicht die Zeile. Schreiben Sie in einer Prüfung nie SELECT *.

Ein dritter Hebel ist kostenlos: INFORMATION_SCHEMA.PARTITIONS und die Tabellenmetadaten tragen Zeilenzahlen und Änderungszeitpunkte bei, ohne überhaupt Daten zu scannen.

Setzen Sie trotzdem eine harte Obergrenze. Jede Validierungsabfrage sollte mit maximum_bytes_billed laufen, damit ein vertippter Filter den Job scheitern lässt, statt 40 TB zu scannen. Der Rest des Vorgehens — die sechs Prüfungen, der Zeitplan, der Contract, der sie zusammenhält — ist derselbe wie überall und im vollständigen Leitfaden zur Datenvalidierung beschrieben; BigQuery-spezifisch sind die Kosten.

Die sechs Prüfungen, die jede Tabelle braucht

Nehmen wir an, orders ist auf DATE(created_at) partitioniert und Sie validieren den Ladelauf des letzten Tages.

1. Vollständigkeit — Nullwerte in Pflichtspalten

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND order_id IS NULL;

Die eigentliche Arbeit leistet hier der Partitionsfilter — ohne ihn liest diese Abfrage jedes Byte der Tabelle.

2. Eindeutigkeit — doppelte Geschäftsschlüssel

SELECT COUNT(*) AS violations
FROM (
    SELECT order_id
    FROM `proj.sales.orders`
    WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
      AND order_id IS NOT NULL
    GROUP BY order_id
    HAVING COUNT(*) > 1
);

BigQuery kennt keine Primärschlüssel, die es durchsetzt, und Streaming-Inserts sind At-least-once. Doppelte Schlüssel sind hier kein Randfall — sie sind das erwartbare Fehlerbild eines wiederholten Ladelaufs, und diese Prüfung ist oft die wertvollste Regel im ganzen Contract.

Ein Vorbehalt, den man kennen sollte: Eine Duplikatprüfung, die auf eine Partition begrenzt ist, sieht keine Zeile, die über zwei Tage hinweg dupliziert wurde. Müssen Ihre Schlüssel global eindeutig sein, muss diese Prüfung die gesamte Tabelle scannen — lassen Sie sie dann täglich statt stündlich laufen und planen Sie das Budget dafür ein.

3. Konformität — Werte außerhalb einer erlaubten Menge

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

NULL NOT IN (...) ergibt in GoogleSQL wie überall sonst NULL — behalten Sie also die IS NOT NULL-Absicherung, sonst verschwinden Nullwerte aus der Zählung.

4. Genauigkeit — Zahlen außerhalb eines plausiblen Bereichs

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Nutzen Sie NUMERIC (oder BIGNUMERIC) für Geldbeträge. FLOAT64 folgt IEEE 754 und wird an der Grenze mit einer Schranke uneins sein.

5. Konformität — fehlerhaft formatierte Identifier

SELECT COUNT(*) AS violations
FROM `proj.sales.orders`
WHERE created_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND reference IS NOT NULL
  AND NOT REGEXP_CONTAINS(reference, r'^ORD-[0-9]{6}
#39;);

REGEXP_CONTAINS nutzt RE2, es gibt also keine Rückwärtsreferenzen und keine Lookarounds — aber alles andere, was Sie voraussichtlich schreiben, funktioniert, und die Linearzeit-Garantie von RE2 sorgt dafür, dass ein pathologisches Muster die Abfrage nicht aufhängen kann.

6. Aktualität — Freshness

Die billige Variante liest überhaupt keine Tabellendaten:

SELECT COUNT(*) AS violations
FROM `proj.sales.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'orders'
  AND last_modified_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND partition_id = FORMAT_DATE('%Y%m%d', CURRENT_DATE());

Die Variante auf Zeilenebene — Zählen der Zeilen, deren created_at älter als die Schwelle ist — misst etwas subtil anderes: nicht „wurde die Tabelle geladen", sondern „sind die Daten darin aktuell". Beides lohnt sich. Die Metadatenprüfung erwischt eine Pipeline, die nicht gelaufen ist; die Zeilenprüfung erwischt eine Pipeline, die gelaufen ist und veraltete Zeilen produziert hat.

Eine BigQuery-spezifische Stolperfalle: Zeilen im Streaming-Puffer sind abfragbar, aktualisieren aber last_modified_time nicht sofort — eine rein metadatenbasierte Aktualitätsprüfung kann auf einer Streaming-Tabelle also hinterherhinken.

Ein Service-Account mit Lesezugriff

Die Validierung braucht zwei Rollen auf dem Projekt, das die Daten hält:

roles/bigquery.dataViewer — Tabellendaten und Metadaten lesen
roles/bigquery.jobUser — Query-Jobs ausführen

Vergeben Sie dataViewer auf Dataset- statt auf Projektebene, wenn Sie den Zugriff auf bestimmte Tabellen begrenzen wollen. Setzen Sie dann die Kostenobergrenze auf der Verbindung, damit keine einzelne Prüfung entgleiten kann:

-- Enforced per job by the client, not in SQL:
--   maximum_bytes_billed = 10_000_000_000   (10 GB)

Catalyst verbindet sich mit einem Service-Account-Key, wendet pro Prüfung eine Obergrenze für abgerechnete Bytes an und meldet eine Prüfung, die sie überschreitet, als error statt als fail — eine Infrastrukturgrenze sollte nie als Datenproblem gemeldet werden.

Von Ad-hoc-SQL zum Data Contract

Die Abfragen oben sind korrekt und nicht wartbar: Der Partitionsfilter ist sechsmal kopiert, die Schwellenwerte sind unsichtbar, und nichts sagt Ihnen, welche Prüfungen überhaupt noch laufen. Die Erwartungen zu deklarieren behebt das. Catalyst nutzt den Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 2.0.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: NUMERIC
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: reference
        logicalType: string
        quality:
          - rule: regex
            dimension: conformity
            severity: warning
            mustBe: "'^ORD-[0-9]{6}#39;"
      - name: created_at
        logicalType: timestamp
        physicalType: TIMESTAMP
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importiert das Schema aus INFORMATION_SCHEMA.COLUMNS, kompiliert jede Regel zu GoogleSQL und hält das Ergebnis jeder Prüfung samt einer Stichprobe der fehlerhaften Zeilen fest. Weil das YAML die maßgebliche Quelle ist und unverändert durch den visuellen Builder läuft, ist der im Pull Request geprüfte Contract exakt derjenige, der ausgeführt wird.

BigQuery-Stolperfallen, die man kennen sollte

Falle	Was passiert	Was zu tun ist
Kein Partitionsfilter	Jede Prüfung scannt die ganze Tabelle	Auf der Partitionsspalte filtern
`SELECT *` in einer Prüfung	Liest jede Spalte	Nur die geprüfte Spalte referenzieren
At-least-once-Streaming	Stille doppelte Schlüssel	Immer eine `duplicateCount`-Regel aufnehmen
Streaming-Puffer	Metadaten-Aktualität hinkt hinterher	Metadaten- und Zeilenprüfung kombinieren
Geldbeträge als `FLOAT64`	Schranken stimmen am Rand nicht überein	`NUMERIC` verwenden
RE2-Regex	Keine Lookarounds oder Rückwärtsreferenzen	Muster umschreiben oder `customSql` nutzen
Keine durchgesetzten Schlüssel	Constraints sind rein deklarativ	Eindeutigkeit explizit validieren

Zeitplanung und Alerting

Stoßen Sie die Validierung aus dem Ende des Ladelaufs an, nicht von einer Uhr. In BigQuery zählt das doppelt: Eine Prüfung, die läuft, bevor der Ladelauf fertig ist, meldet einen Fehlalarm — und stellt Ihnen den Scan trotzdem in Rechnung.

Teilen Sie die Suite nach Kosten auf. Billige, partitionsbezogene Prüfungen können bei jedem Ladelauf laufen; teure Prüfungen über die ganze Tabelle — globale Eindeutigkeit, tabellenübergreifende referenzielle Integrität — gehören in einen täglichen Zeitplan. Alarmieren Sie beim Übergang von pass zu fail statt den Zustand zu wiederholen, und halten Sie Musterprüfungen auf Schweregrad warning, damit sie informieren statt zu wecken.

Häufige Fragen

Was kostet Datenvalidierung in BigQuery?

Es sind die Bytes, die jede Prüfung scannt, abgerechnet zum On-Demand-Tarif, oder die Slot-Zeit auf einer Reservierung. Eine partitionsbezogene Prüfung auf einer einzelnen Spalte liegt typischerweise im Megabyte-Bereich; dieselbe Prüfung ohne Partitionsfilter kann Terabytes umfassen. Schreiben Sie Partitionsfilter in jede Regel, deckeln Sie jede Abfrage mit maximum_bytes_billed und nutzen Sie INFORMATION_SCHEMA für alles, was nur Metadaten braucht.

Kann ich BigQuery-Daten validieren, ohne SQL zu schreiben?

Ja. Deklarieren Sie die Erwartung und lassen Sie das Tool GoogleSQL erzeugen. Catalyst importiert Spalten und Typen aus INFORMATION_SCHEMA, schlägt einen Satz Basisregeln vor und kompiliert sie je Dialekt — SQL schreiben Sie nur für wirklich maßgeschneiderte Logik über eine customSql-Regel.

Welche Berechtigungen braucht ein Validierungstool?

roles/bigquery.dataViewer auf den Datasets, die geprüft werden sollen, plus roles/bigquery.jobUser auf dem Projekt, damit es Abfragen ausführen kann. Schreibzugriff ist nicht erforderlich — jede Prüfung ist ein aggregierendes SELECT.

Warum bekomme ich in BigQuery doppelte Zeilen, obwohl ein Primärschlüssel deklariert ist?

Die Primär- und Fremdschlüssel-Constraints von BigQuery sind nicht durchgesetzte Metadaten, die der Query-Optimizer nutzt; sie weisen doppelte Inserts nicht ab. Zusammen mit der At-least-once-Zustellung der Streaming-API macht das Duplikate zum normalen Ergebnis eines wiederholten Ladelaufs statt zu einem seltenen Fehler. Eine duplicateCount-Regel ist hier nicht optional.

Funktioniert das mit BigQuery-Views und externen Tabellen?

Views lassen sich problemlos validieren — die Prüfung läuft gegen das Ergebnis der View. Externe Tabellen (Cloud Storage, Sheets, BigLake) funktionieren ebenfalls, aber bei den meisten gibt es kein Partition Pruning, jede Prüfung liest also die gesamte Quelle. Kalkulieren Sie das ein oder materialisieren Sie die externe Tabelle zuerst.