bigquery-augmented-analytics
Execute in-database augmented analytics in Google BigQuery using Table-Valued Functions (TVFs) for automated metric anomaly detection, multi-dimensional root-cause attribution, and ARIMA_PLUS counterfactual causal inference. Do not use for generic SQL CRUD or external pandas exports.
- License 许可
- MIT
- 最近更新
- 2026-11-15
- Stars
- ★ 0
- Forks
- 0
01 前言 Front Matter
- name
- bigquery-augmented-analytics
- description
- Execute in-database augmented analytics in Google BigQuery using Table-Valued Functions (TVFs) for automated metric anomaly detection, multi-dimensional root-cause attribution, and ARIMA_PLUS counterfactual causal inference. Do not use for generic SQL CRUD or external pandas exports.
02 资源文件 3
- 参考文档CARD.md4.5 KB
- 参考文档config.default.yaml330 B
- 脚本scripts/bigquery_augmented_analytics.py51.7 KB
03 SKILL.md 正文
BigQuery Augmented Analytics: In-Database Anomaly, Attribution & Causal Engine
bigquery-augmented-analytics is the autonomous data investigation engine that executes in-database AI, ML, and statistical inference directly inside Google BigQuery via Table-Valued Functions (TVFs). Based on the Google Cloud framework authored by Jenny Ortiz and Haoming Chen, this skill empowers agents to diagnose why business metrics change, detect structural step-level shifts, isolate high-dimensional segment drivers via Apriori rules, and quantify true causal lift against synthetic counterfactual baselines—all without exporting a single row of raw data.
Rather than succumbing to the "Data Egress Tax" (pulling millions of rows into local memory for pandas or R), this skill chains compact, expressive BigQuery TVFs where computation is pushed down directly into the distributed columnar storage engine.
[1. Temporal Profiling] ➔ [2. Shift Isolation] ➔ [3. Combinatorial Attribution] ➔ [4. Causal Lift] ➔ [5. Executive Synthesis]
ML.TREND / SEASONALITY ML.DETECT_CHANGE_POINTS AI.KEY_DRIVERS / CORRELATION AI.CAUSAL_EFFECT Root-Cause Brief
See CARD.md for the companion summary card, TVF reference table, and verification checklist. Consult data-management-architect for data contracts and deterministic-validation-loop for structured observation validation.
1. Temporal Profiling & Noise Stripping
Before diagnosing metric changes, decompose raw time-series into underlying structural trajectories and predictable cyclical rhythms. This prevents treating regular seasonal variations as structural anomalies.
- Trend Decomposition via
ML.TREND:- Strips high-frequency noise and transient spikes from the underlying growth or decline trajectory.
- Example BigQuery SQL:
WITH daily_metrics AS ( SELECT DATE(timestamp) AS metric_date, COUNT(*) AS total_events FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` GROUP BY metric_date ) SELECT * FROM ML.TREND( TABLE daily_metrics, data_col => 'total_events', timestamp_col => 'metric_date' ) ORDER BY metric_date;
- Cyclical Periodicity Discovery via
ML.SEASONALITY:- Identifies recurring daily, weekly, monthly, and quarterly patterns across metric values.
- Example BigQuery SQL:
SELECT * FROM ML.SEASONALITY( TABLE daily_metrics, data_col => 'total_events', timestamp_col => 'metric_date', seasonalities => ['WEEKLY', 'MONTHLY'] ) ORDER BY metric_date;
Completion criterion: Underlying trend direction separated from short-term noise; dominant recurring cycles documented; raw variance normalized against expected seasonality.
2. Structural Shift & Change-Point Isolation
Isolate the exact historical dates or intervals where a metric experienced a statistically significant, persistent regime shift rather than a transitory fluctuation.
- Step-Change Detection via
ML.DETECT_CHANGE_POINTS:- Employs scalable Bayesian change-point algorithms capable of evaluating millions of distinct time series in seconds.
- Example BigQuery SQL:
WITH baseline_series AS ( SELECT DATE(start_time) AS trip_date, COUNT(*) AS daily_trips FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` GROUP BY trip_date ) SELECT start_time, end_time, mean, variance, change_point_prob FROM ML.DETECT_CHANGE_POINTS( TABLE baseline_series, data_col => 'daily_trips', timestamp_col => 'trip_date' ) WHERE change_point_prob >= 0.95 ORDER BY start_time DESC;
- Dynamic Boundary Parameterization:
- Extract the primary shift timestamp ($t_0$) with the highest change probability or longest duration.
- Designate the pre-shift interval ($t < t_0$) as the Reference Group and the post-shift interval ($t \ge t_0$) as the Interest Group for downstream TVF chaining.
Completion criterion: Statistically significant change-point timestamp $t_0$ isolated with probability $\ge 95%$; time horizons parameterized for attribution.
3. Combinatorial Attribution & Multi-Dimensional Slicing
Scan high-dimensional categorical features across millions of rows to explain the exact drivers of the metric variance between the reference and interest cohorts.
- Multi-Dimensional Driver Analysis via
AI.KEY_DRIVERS:- Uses Apriori association rule mining to identify combinations of dimensions (segments) that disproportionately explain the delta in metric volume.
- Example BigQuery SQL:
WITH cohort_partitioned AS ( SELECT trip_id, subscriber_type, bike_type, start_station_name, end_station_name, (DATE(start_time) >= '2018-02-01') AS is_interest_period FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` WHERE DATE(start_time) BETWEEN '2017-09-01' AND '2018-06-30' ) SELECT segment, metric_delta, percentage_change, segment_contribution FROM AI.KEY_DRIVERS( TABLE cohort_partitioned, metric_col => 'trip_id', dimension_cols => ['subscriber_type', 'bike_type', 'end_station_name'], interest_label_col => 'is_interest_period', min_apriori_support => 0.01, top_k => 20 ) ORDER BY segment_contribution DESC;
- Pairwise Metric Correlation via
ML.CORRELATION:- Evaluates directional strength between metric pairs across categorical dimensions to identify mediating mechanisms.
- Example BigQuery SQL:
SELECT segment, corr_col, correlation, segment_size FROM ML.CORRELATION( TABLE `bigquery-public-data.chicago_taxi_trips.taxi_trips`, target_col => 'trip_total', target_correlation_cols => ['trip_miles', 'trip_seconds'], dimension_cols => ['payment_type', 'company'] ) ORDER BY segment_size DESC;
Completion criterion: Top contributing dimension segments identified; percentage lift and absolute contribution quantified; confounding sub-dimensions ranked.
4. Counterfactual Impact & Causal Lift Estimation
Differentiate between organic trajectory growth and the true return on investment (ROI) or causal impact of an intervention, policy change, or campaign launch.
- Synthetic Counterfactual Modeling via
AI.CAUSAL_EFFECT:- Builds an internal
ARIMA_PLUSpredictive model to project what the metric baseline would have been had the intervention never occurred. - Compares the observed trajectory against this synthetic control to isolate the true causal lift.
- Example BigQuery SQL:
WITH time_series_input AS ( SELECT DATE(start_time) AS trip_date, COUNT(*) AS trip_count FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips` WHERE DATE(start_time) BETWEEN '2017-01-01' AND '2018-12-31' GROUP BY trip_date ) SELECT actual, counterfactual_baseline, absolute_effect, relative_effect, prob_causal_effect FROM AI.CAUSAL_EFFECT( TABLE time_series_input, data_col => 'trip_count', timestamp_col => 'trip_date', intervention_timestamp => '2018-02-01', output_time_series => FALSE );
- Builds an internal
- Time-Series Counterfactual Trajectory:
- When inspecting post-intervention divergence curves over time, set
output_time_series => TRUEto extract daily confidence intervals (lower_bound,upper_bound).
- When inspecting post-intervention divergence curves over time, set
Completion criterion: True incremental lift and causal effect probability calculated; organic trend isolated from program impact.
5. Executive Diagnostic Synthesis & Delivery
Transform technical TVF outputs into actionable business diagnoses and decisions:
- Synthesize the 4-Point Diagnostic Narrative:
- When: Shift timestamp and persistence duration (from Stage 2).
- What: Magnitude of change and decomposed trend slope (from Stage 1).
- Who/Where: Disproportionate contributing segments and dimension combinations (from Stage 3).
- Impact: True incremental causal lift over organic baseline (from Stage 4).
- Conversational Analytics Seam:
- Format results as deterministic Markdown tables and structured JSON observations for ReAct agent step consumption and conversational presentation.
Completion criterion: Executive root-cause brief produced with verified TVF backing; recommendations grounded in causal evidence.
Visual Brief Pillar
Before executing heavy or multi-stage TVF pipelines on large-scale datasets, agents must generate an interactive HTML Visual Brief to visually communicate query parameters, DAG execution sequences, and preliminary data topologies:
- Target File Location:
- Save to
%TEMP%\bigquery-augmented-analytics-<timestamp>.html(Windows) or/tmp/bigquery-augmented-analytics-<timestamp>.html(Unix).
- Save to
- Styling & Components:
- Dark theme (
#0d1117) loading Tailwind CSS and Mermaid.js via CDN. - Render a TVF Investigative Chaining DAG showing the data flow from
ML.DETECT_CHANGE_POINTS$\to$AI.KEY_DRIVERS$\to$AI.CAUSAL_EFFECT. - Render a Cohort Partitioning Table showing the reference vs interest definitions.
- Render the Diagnostic Evaluation Scorecard Table.
- Dark theme (
- Delivery:
- Present the absolute, clickable HTML file path to the user.
Mandatory Checkpoint Gate
To prevent excessive BigQuery slot consumption, unintended partition scans, or misconfigured multi-stage queries, agents must enforce a mandatory human-in-the-loop review gate:
- Prior to executing write-intensive TVF queries or multi-million-row scans, present an
implementation_plan.mdartifact detailing:- Target dataset and table identifiers.
- Incurred byte scan estimates and partition filters.
- Formulated TVF chain parameters (
data_col,dimension_cols,min_apriori_support). - Planned post-intervention horizon and confidence level.
- Set
RequestFeedback: truein artifact metadata. - STOP and wait for explicit user approval before triggering production TVF jobs.
Programmatic IoC Service Seam (Rule 49)
Autonomous ReAct agent step loops and swarm workers dynamically resolve the BigQuery Augmented Analytics engine directly in-memory via the typed micro-kernel key:
from harness.kernel.context import ServiceContext
from harness.services.bigquery_augmented_analytics import (
BIGQUERY_AUGMENTED_ANALYTICS_SERVICE_KEY,
BigQueryAugmentedAnalyticsService,
)
async def run_investigation(context: ServiceContext) -> None:
bq_service: BigQueryAugmentedAnalyticsService = context.require(
BIGQUERY_AUGMENTED_ANALYTICS_SERVICE_KEY
)
result = bq_service.run_investigation(
target_table="bigquery-public-data.austin_bikeshare.bikeshare_trips",
metric_col="trip_id",
timestamp_col="start_time",
dimension_cols=["subscriber_type", "bike_type"],
)
print("When:", result.narrative.when)
print("Impact:", result.narrative.impact)
Domain-Partitioned Plugin Tools (Rule 18 & Rule 45)
The co-located plugin in plugins/data_engineering/bigquery_augmented_analytics/ exposes modular tool actions for ReAct agent step dispatch:
bigquery_temporal_profile: Decomposes trend slope and weekly/monthly seasonality cycles viaML.TRENDandML.SEASONALITY.bigquery_detect_change_points: Isolates persistent structural regime shifts ($t_0$) with probability $\ge 0.95$ viaML.DETECT_CHANGE_POINTS.bigquery_attribute_drivers: Identifies high-dimensional Apriori segment rules explaining cohort deltas viaAI.KEY_DRIVERS.bigquery_causal_lift: Estimates true incremental impact against synthetic counterfactual control viaAI.CAUSAL_EFFECT.bigquery_augmented_pipeline: Orchestrates the complete 5-stage investigation end-to-end.bigquery_visual_brief: Renders a dark-theme interactive HTML visual brief with Tailwind CSS and Mermaid DAG topology.
Headless Click CLI Seam (Rule 10 & Rule 6)
Developers, CI/CD verification runners, and headless agents can inspect and dry-run TVF compilation without cloud credentials via harness bq or harness bigquery:
# Compile and dry-run Stage 1 Temporal Profiling TVF SQL
harness bq profile --table austin_bikeshare.trips --data-col trip_id --timestamp-col start_time --dry-run
# Isolate structural regime shifts (probability >= 95%)
harness bq change-points --table austin_bikeshare.trips --data-col trip_id --timestamp-col start_time --min-prob 0.95
# Scan combinatorial Apriori driver segments
harness bq drivers --table austin_bikeshare.trips --metric-col trip_id --dims subscriber_type,bike_type --interest-cond "DATE(start_time) >= '2026-02-15'" --min-support 0.01
# Model synthetic counterfactual causal lift
harness bq causal --table austin_bikeshare.trips --data-col trip_id --timestamp-col start_time --intervention 2026-02-15
# Execute end-to-end 5-stage pipeline and generate visual brief
harness bq pipeline --table austin_bikeshare.trips --metric-col trip_id --timestamp-col start_time --dims subscriber_type,bike_type --dry-run --brief report.html
Anti-Patterns
- The Data Egress Tax — Exporting millions of rows from BigQuery to local pandas or R memory instead of executing in-engine SQL TVFs.
- Fluctuation Hallucination — Mistaking recurring cyclical seasonality or random noise for structural change points without verifying via ML.TREND and ML.SEASONALITY.
- Naive Pre/Post Attribution — Attributing 100% of post-intervention delta to an event without estimating the ARIMA_PLUS counterfactual baseline.
- Unparameterized Date Hardcoding — Hardcoding arbitrary date cuts in AI.KEY_DRIVERS instead of chaining dynamic timestamps from ML.DETECT_CHANGE_POINTS.
- Micro-Segment Noise Flooding — Setting min_apriori_support to zero without pruning, leading to thousands of statistically insignificant micro-cohorts.
04 同仓库更多 Skills
ui-behavior-primitive-architect
Architect, audit, and verify zero-dependency headless UI behavior primitives, WebMCP agent bridges, WAI-ARIA/SSR/RTL tri-modal checks, and framework expert routing. Do not use for static CSS theme styling or backend database migrations.
trustgraph-ontology-governor
Govern knowledge graphs, SHACL-AF triple policies, OntologyRAG subclass validation, PROV-O dual named graphs, and Laya entropy decision scoring. Do not use for relational SQL queries or generic web scraping.
shim-harness-governor
Govern universal CLI agent loops, 4-tier tool permissions, ancestor-symlink workspace sandboxes, deterministic-first command routing, collision-safe MCP namespacing, and layered skill loading. Do not use for static UI styling or external SQL analytics.
graphiti-zep-memory-governor
Govern bi-temporal knowledge graphs with Graphiti future-edge clamping, MinHash/LSH entity dedup, RRF/MMR reranking, and Zep typed ontologies. Do not use for static SQL joins or generic web crawling.
agora-mcp-workbench-architect
Architect, audit, and orchestrate Agora Workbench MCP servers, loopback sidecars, unified state graphs, gateway/dispatcher connectors, and policy-enforced data lakes. Do not use for static frontend CSS styling or non-MCP desktop GUI automation.
skill-eval-assurance-runner
Evaluate and assure agent skills using small-N Wilson confidence intervals, unbiased pass^k reliability, deterministic trajectory graders, preflight boundary honesty, and sealed digest verification. Do not use for subjective prose review or unconstrained production deployment.