Gate: Raw → Silver promotion
Checks: Schema drift · Null rates · Row volume anomaly · Critical column presence
Model: Gemini 2.5 Flash (non-PASS cases only)
Hard gate: FAIL HIGH/CRITICAL → quarantine raw load, stop downstream
Gate: Transformation deployment
Checks: SQL/dbt join correctness · Duplicate ratio · Source-target schema alignment · BigQuery anti-patterns
Model: Gemini 2.5 Pro (complex multi-hop SQL validation)
Hard gate: CRITICAL formula/join defects → block deploy
Gate: Gold/Reporting promotion
Checks: Row loss Raw→Silver→Gold · Aggregate drift · Dropped key counts · Dataplex lineage watermark alignment
Model: Gemini 2.5 Flash
Hard gate: HIGH/CRITICAL drift → hold Gold promotion
Gate: Dashboard / exec reporting refresh
Checks: Business rule enforcement · Gross margin sanity · Gold vs Reporting reconciliation · ML.DETECT_ANOMALIES (ARIMA_PLUS)
Model: Gemini 2.5 Pro (structured JSON anomaly summaries)
Hard gate: CRITICAL KPI trust failure → block dashboard refresh
batch_status Roll-Up
QUARANTINED — any agent release_blocked=TRUE
PENDING_REVIEW — any agent review_required=TRUE
RELEASED — all agents clear
Timeout / Partial Failure
Agent timeout → persist SYSTEM_FALLBACK_HANDLER with INSUFFICIENT_DATA/HIGH
UC1 fallback → UC2/UC3/UC4 continue in advisory with evidence_sufficiency=INSUFFICIENT
Advisory vs Gate Mode
In advisory_mode=TRUE: batch_status computed and logged but does NOT halt data movement
In advisory_mode=FALSE: hard gates enforce QUARANTINED blocks
Exception Suppression
Evaluated after deterministic severity, before routing. SUPPRESS clears block. DOWNGRADE reduces HIGH→MEDIUM only. CRITICAL is never reduced.
BigQuery
Source, target, and deterministic evidence engine for all four agents.
- dq_results dataset — agent_findings, audit_violations
- dq_admin dataset — thresholds, exceptions, golden_labels
- MERGE-based idempotent ingestion (no plain INSERT)
- fn_route() routing UDF in dq_results
- PARTITION BY DATE(run_ts) · CLUSTER BY agent_name/status
- ML.DETECT_ANOMALIES on ARIMA_PLUS (UC4)
Vertex AI — Gemini 2.5
Model invoked only for non-PASS deterministic cases. Model proposes; orchestrator decides.
- Gemini 2.5 Flash — UC1/UC2 high-frequency classification
- Gemini 2.5 Pro — UC3/UC4 complex SQL/KPI validation
- gemini-2.0-flash RETIRED — never use
- Structured output: response_mime_type="application/json"
- Canonical response schema enforced — status, severity, evidence_refs
- Business keys hashed (SHA-256) before invocation
Dataplex
Metadata curation and data lineage tracking across pipeline layers.
- Data Lineage API — tracks state boundaries Raw→Silver→Gold
- Cloud Data Catalog — structural asset metadata
- dq_asset_inventory table — scanned asset registry
- Watermark alignment check in UC2 cross-layer reconciliation
- Per-asset quality scan metadata
Cloud Workflows
Executes the 5-step DAG preserving agent dependency ordering and advisory/gate semantics.
- UC1 → UC3 → UC2 (uses UC1+UC3) → UC4 (uses UC2)
- Passes advisory_mode into every agent call
- SYSTEM_FALLBACK_HANDLER for timeout/partial failure
- Triggers merge_audit_violations post-run
- Computes batch_status roll-up
Pub/Sub
Publishes violation alerts for open HIGH/CRITICAL findings that have not yet been alerted.
- Alert eligibility: alerted=FALSE gate before publish
- UPDATE alerted=TRUE after publish (idempotent)
- Feeds Teams/Slack/PagerDuty notifications
- Triggers steward review queue notifications
Secret Manager
Stores all service credentials. No secrets in environment variables or code.
- Service account key rotation
- Vertex AI API credentials
- BigQuery connection secrets
- IAM: dataEditor scoped to dq_results only
- minimize_logs() strips keys/tokens/emails before Vertex AI
Cloud Logging & Monitoring
Full observability stack for agent performance and pipeline health.
- logger.warning() for all fallback / INSUFFICIENT_DATA events
- Token counts — input_token_count, output_token_count per run
- Latency tracking — latency_ms per agent execution
- Advisory metrics: FP/FN rate, review queue volume
- Promotion gate criteria monitoring (§13.3)
Looker Studio
Executive KPI dashboards powered by the Gold layer, gated by UC4 assurance before refresh.
- v_agent_summary — flat view for dashboard consumption
- v_agent_findings — UNNEST findings for per-finding drill-down
- UC4 CRITICAL failure blocks dashboard refresh
- Routing target visible in dashboard (RELEASED/PENDING/QUARANTINED)
- Run DDL §1–§3 (datasets, agent_findings, all supporting tables)
- Deploy fn_route() UDF (§5)
- Load MERGE SQL files into repo (§4 build gate)
- Create views v_agent_summary, v_agent_findings (§7)
- Load 160 golden labels into golden_labels (§15)
- Architecture approved by lead architect
- Run all 4 agents with advisory_mode=TRUE
- 14-day passive live pipeline observation window
- Track FP/FN rate (§13.1 query) daily
- Monitor review queue volume and latency
- Zero schema failures or unhandled JSON errors
- Steward team processes review queue within capacity
- Enable hard blocking gates per agent (advisory_mode=FALSE)
- Refactor lookup_exception() to batch-query per run (N→1)
- Expand golden labels to 100+ per agent (400+ total)
- Rollback runbook tested end-to-end
- UC4 Looker dashboard gated by UC4 assurance result
- Model-initiated actions within bounded scope
- Full BigQuery estate coverage
- Cloud Composer for enterprise-scale scheduling
- Looker Studio fully integrated and auto-refreshed
- Data Lineage API at full scale
Result Table
dq_results.agent_findings — agent_results is RETIRED
JSON Payload
payload_json JSON containing {"findings":[...]}
Status Enum
PASS | WARNING | FAIL | INSUFFICIENT_DATA
Severity Enum
LOW | MEDIUM | HIGH | CRITICAL — always UPPERCASE
Evidence Sufficiency
SUFFICIENT | PARTIAL | INSUFFICIENT
Routing Targets
RELEASED | PENDING_REVIEW | QUARANTINED
Model
gemini-2.5-flash default · gemini-2.5-pro escalation · gemini-2.0-flash RETIRED
Decision Ownership
Orchestrator deterministic gate decides. Model proposes only.
Ingestion
MERGE-based idempotent only — no plain INSERT ever
Rollout Order
Advisory mode → deterministic gates → bounded agentic (in that order)