You've spent hours debugging a 200-line SQL query that processes terabytes of data, only to find the bottleneck is a CROSS JOIN you didn't notice. Or maybe you've stared at a table with 50 columns, wondering which ones actually matter for your churn prediction model. BigQuery is powerful, but it's also complex—and that's where AI-assisted prompts come in. This article isn't about generic advice; it's a collection of 10 specific, battle-tested prompts I use daily to get the most out of BigQuery. Each one includes a real-world example and the exact syntax you need. No fluff, just results.
1. The Query Optimizer: From Slow to Fast in Seconds
The Problem: Your query runs in 30 seconds, but it should run in 3. You suspect a bad JOIN or a missing filter, but you can't spot it.
The Prompt:
You are a BigQuery performance expert. Analyze the following SQL query and identify performance bottlenecks. Consider table partitioning, clustering, JOIN order, and the use of approximate functions. Provide a rewritten query that is at least 10x faster, and explain each change.
[Paste your query here]
Why It Works: This prompt forces the AI to act as a specialist, not just a code generator. It asks for a specific performance gain (10x) and an explanation, so you learn why the changes matter.
Example:
I had a query that joined a 10TB events table with a 1GB user table on user_id. The AI noticed the events table was partitioned by date, but the query didn't filter on date. It added a WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) clause, reducing the scanned data from 10TB to 1TB. The query time dropped from 45 seconds to 4 seconds.
Result: Faster queries, lower costs (BigQuery charges per byte scanned), and a clear understanding of what went wrong.
2. The Schema Decoder: Turning Raw Tables into Business Insights
The Problem: You have a table with cryptic column names like col_1, col_2, and user_created_at. You need to understand what each column means and how to use it.
The Prompt:
You are a data analyst. Given the following BigQuery table schema and sample rows, interpret each column in business terms. Suggest which columns are most useful for [specific business goal, e.g., customer segmentation]. Provide a sample SQL query that extracts meaningful insights.
Schema:
[Paste schema here]
Sample rows:
[Paste sample data here]
Why It Works: The AI uses its training on common data patterns to infer column meanings. It also asks for a business context, so the output is actionable.
Example: I used this on a table with columns ts, ev, ua. The AI identified ts as a timestamp, ev as an event type (e.g., 'purchase'), and ua as a user agent string. It then suggested a query to count events per day and filter by device type.
Result: You save hours of manual data exploration and get a head start on your analysis.
3. The Cost Cutter: Reducing Your BigQuery Bill by 50%
The Problem: Your BigQuery costs are spiraling out of control. You need to find queries that scan too much data or use expensive operations.
The Prompt:
You are a BigQuery cost optimization expert. Review the following query and suggest changes to reduce the amount of data scanned. Consider using partitioning, clustering, or approximate functions. Also, check for any unnecessary columns in SELECT. Provide a cost-efficient version of the query.
[Paste query]
Why It Works: BigQuery charges per TB scanned, so reducing scanned data is the biggest cost lever. This prompt focuses on exactly that.
Example: A query I had used SELECT * from a table with 100 columns, but only 5 were needed. The AI suggested listing only those 5 columns, cutting the slot usage by 95%. The cost dropped from $0.50 per query to $0.03.
Result: Lower costs, better performance, and a more efficient team.
4. The Anomaly Detector: Uncovering Outliers in Your Data
The Problem: You need to find anomalies in a time-series dataset, such as sudden spikes in traffic or drops in sales.
The Prompt:
You are a data scientist. Write a BigQuery SQL query to detect anomalies in a time-series table. The table is [table name] with columns [column names]. Use the Z-score method with a rolling window of [window size] days. Return rows where the absolute Z-score exceeds [threshold].
Table: `project.dataset.sales`
Columns: `date`, `revenue`
Why It Works: This prompt is specific about the method (Z-score) and the parameters, so the AI can generate a precise query.
Example: The AI generated a query using AVG() and STDDEV() window functions to calculate the Z-score for each day's revenue, then flagged days where the absolute Z-score was > 2.5. I found a data ingestion error that had doubled a day's revenue.
Result: You catch data quality issues before they affect business decisions.
5. The Forecasting Wizard: Predicting Future Trends with ARIMA
The Problem: You need to forecast next month's sales, but you're not a statistician.
The Prompt:
You are a time-series forecasting expert. Using BigQuery ML, create an ARIMA model to forecast [metric] for the next [number] days. The data is in table `[table]` with a timestamp column `[timestamp]` and a value column `[value]`. Provide the SQL to create the model, evaluate it, and generate forecasts.
Table: `project.dataset.daily_sales`
Columns: `sales_date`, `total_sales`
Why It Works: BigQuery ML has built-in ARIMA support, but the syntax is specific. This prompt gives the AI all the context it needs.
Example: The AI generated three queries: one to create the model with CREATE MODEL, one to evaluate it with ML.EVALUATE, and one to forecast with ML.FORECAST. I ran the forecast and got daily sales predictions for the next 30 days with confidence intervals.
Result: You get accurate forecasts without needing a PhD in statistics.
6. The Data Quality Checker: Cleaning Up Your Tables
The Problem: Your data has missing values, duplicates, or inconsistent formats. You need to clean it up.
The Prompt:
You are a data quality engineer. Write a BigQuery SQL script to identify and fix data quality issues in the table `[table]`. Check for NULLs, duplicates, and non-standard values in columns [column list]. Provide a query to show the issues and a query to fix them.
Table: `project.dataset.customers`
Columns: `customer_id`, `email`, `signup_date`
Why It Works: This prompt is specific about the types of issues to check for, so the AI doesn't miss anything.
Example: The AI found duplicate customer IDs and NULL emails. It generated a query to list duplicates and a DELETE query to keep only the first occurrence (using ROW_NUMBER()).
Result: Cleaner data leads to more reliable analysis and fewer surprises.
7. The Pivot Table Pro: Reshaping Data for Reports
The Problem: You have data in a long format, but you need it in a wide format for a report or a chart.
The Prompt:
You are a SQL expert. Write a BigQuery query to pivot the table `[table]` from long to wide format. The rows are identified by [key column], the pivot column is [pivot column], and the value column is [value column]. Use conditional aggregation with SUM(CASE WHEN ...).
Example: Convert a table with `product_id`, `month`, `sales` into a table where each month is a column.
Why It Works: Pivoting is a common but tricky operation. This prompt gives the AI a clear template.
Example: I had a table with 12 months of sales data per product. The AI generated a query that created 12 columns, one for each month, using SUM(CASE WHEN month = 'Jan' THEN sales END) AS Jan. This made it easy to create a monthly comparison chart.
Result: You can quickly create presentation-ready data without manual spreadsheet work.
8. The Window Function Ninja: Advanced Analytics Made Easy
The Problem: You need to calculate running totals, moving averages, or rank items within groups.
The Prompt:
You are a SQL expert. Write a BigQuery query using window functions to calculate the following: [describe the metric]. The table is `[table]` with columns [columns]. Provide the query and explain how it works.
Example: Calculate a 7-day moving average of daily active users.
Why It Works: Window functions are powerful but have tricky syntax. This prompt ensures the AI gets it right.
Example: I needed a 7-day moving average of active users. The AI generated a query using AVG(dau) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), which worked perfectly.
Result: You can perform sophisticated analytics without learning the details of window function syntax.
9. The Multi-Dataset Join: Combining Data from Different Sources
The Problem: You have data in multiple BigQuery tables that need to be joined, but the relationships are complex.
The Prompt:
You are a data engineer. Write a BigQuery query to join the following tables: [table1] with columns [columns], [table2] with columns [columns]. The relationship is [describe relationship, e.g., one-to-many]. Include a WHERE clause to filter for [filter]. Optimize the query for performance.
Why It Works: This prompt gives the AI clear table definitions and a relationship, so it can write an efficient JOIN.
Example: I joined a users table with an orders table on user_id. The AI noticed that some users had no orders and used a LEFT JOIN to include them, and added a WHERE to filter for orders in the last 30 days.
Result: You get accurate combined data without missing records.
10. The Query Explainer: Learning from AI's Thought Process
The Problem: You have a complex query and you want to understand what it does, or you want to learn best practices.
The Prompt:
You are a BigQuery expert. Explain the following query line by line, including what each function does and why it might be used. Also, suggest any improvements or alternative approaches.
[Paste query]
Why It Works: This prompt turns the AI into a tutor, helping you learn and improve your own skills.
Example: I pasted a query that used a subquery in the FROM clause. The AI explained that it was a derived table and suggested using a CTE for better readability. It also pointed out that the EXTRACT function could be replaced with DATE_TRUNC for performance.
Result: You become a better SQL writer while getting your task done.
These ten prompts are my go-to toolkit for BigQuery. They've saved me countless hours and helped me avoid costly mistakes. Try them out, and you'll see why AI-assisted analytics is the future. Remember, the best way to learn is by doing—so copy a prompt, paste your query, and watch the magic happen. Your future self will thank you.
Comments