2.4 AI-Assisted Data Preparation and LLM Query Generation
Key Takeaways
- The v4.2 exam guide explicitly lists prompting LLMs for query generation as part of preparing and cleaning data, alongside Dataform, Dataflow, and Cloud Data Fusion.
- Gemini in BigQuery documents SQL generation, completion, explanation, comment conversion, Python assistance, data canvas, data insights, conversational analytics, data preparation, a data engineering agent, and SQL translation rules.
- Generated SQL runs with the caller's identity, so policy tags, row access policies, and dataset IAM still apply and cannot be bypassed by an assistant.
- Generation quality is bounded by metadata quality: column descriptions and Dataplex Catalog registration improve suggestions more than longer prompts do.
- An LLM accelerates authoring but never establishes correctness; Dataform assertions, ASSERT statements, and Dataplex automated data quality remain the verification layer.
2.4 AI-Assisted Data Preparation and LLM Query Generation
The v4.2 exam guide expanded the Designing for reliability and fidelity sub-domain to read: "Preparing and cleaning data (e.g., Dataform, Dataflow, and Cloud Data Fusion, prompting LLMs for query generation)." That final clause is new, and it is the one candidates most often skip. It puts Gemini's assistive features inside BigQuery on the scored surface — not as a novelty, but as another tool in the data-preparation toolbox that has to be evaluated against the same reliability, cost, and governance criteria as Dataform or Data Fusion.
The framing that earns marks: an LLM accelerates the authoring of a transformation; it does not validate the result. Questions in this area almost always hinge on knowing which half of the problem the assistant actually solves.
The Gemini in BigQuery Feature Catalog
Google documents a specific set of assistive capabilities. Knowing them by name matters, because exam distractors are frequently real features applied to the wrong task.
| Feature | What It Does | Typical Data-Prep Use |
|---|---|---|
| SQL generation | Turns a natural-language prompt into a GoogleSQL query | Draft a cleansing or reshaping query against an unfamiliar schema |
| SQL completion | Autocompletes SQL as you type | Speed up authoring in the BigQuery editor |
| SQL explanation | Describes what an existing query does | Reverse-engineer an inherited, undocumented transformation |
| Comment conversion | Converts an inline comment into the SQL it describes | Sketch a pipeline step before writing it |
| Python code assistance | Generates and completes Python, including BigQuery DataFrames code | Author a pandas-style preparation step that pushes down to BigQuery |
| Data canvas | Natural-language surface to find, join, query, and visualize table assets | Exploratory profiling of a new source before designing the pipeline |
| Data insights | Generates queries from table metadata to surface patterns and statistics | Automated first-pass profiling of an unfamiliar table |
| Conversational analytics | Natural-language conversations with data through configured data agents | Give analysts a governed question-answering surface over curated tables |
| Data preparation | Context-aware, AI-generated transformation recommendations to cleanse data | Suggest standardization, type-fixing, and null-handling steps |
| Data engineering agent | Builds, modifies, and troubleshoots pipelines from natural-language prompts, and generates semantic metadata | Scaffold or debug a pipeline end to end |
| SQL translation rules (Preview) | Gemini-enhanced customizations for SQL dialect translation | Migrate legacy Teradata or Oracle SQL into GoogleSQL |
These features require Gemini to be set up for the project and to have access to BigQuery customer data and metadata. They are configuration, not magic: an organization that has not enabled them will not see them, which is itself a plausible scenario cue.
Prompt Quality Is a Metadata Problem
The single most useful insight for this blueprint bullet is that generation quality is bounded by metadata quality. Gemini grounds its suggestions in table schemas, column names, column descriptions, and the surrounding catalog metadata. A warehouse where every column is named col_17 and carries no description produces weak generated SQL; the same warehouse with descriptive names, populated column descriptions, and Dataplex Catalog entries produces sharply better suggestions.
This creates a genuinely testable chain of reasoning: if a scenario complains that generated queries keep referencing the wrong join key or inventing columns, the architectural remedy is to enrich the metadata — add column descriptions, register the tables in Dataplex Catalog, apply consistent naming — not to write longer prompts.
Effective prompting for data preparation follows a few disciplined habits:
- Name the tables explicitly rather than describing them, so the model grounds on real schema instead of guessing.
- State the partition filter you require. Ask for "filtered to the last 7 days on
event_date" and you get a pruned query; omit it and you can get a full-table scan against a 40 TB table. - State the dialect and target. "GoogleSQL for BigQuery" avoids suggestions borrowed from other SQL dialects.
- Ask for the transformation, then ask for the test. Generating the assertion alongside the query is what turns a draft into something reviewable.
Generated SQL Is a Draft, Not a Validated Artifact
Three governance facts decide most exam questions in this area:
- The generated query runs with the caller's identity. Gemini does not elevate privileges. Column-level security enforced by policy tags, row access policies, and dataset IAM all still apply, so a generated query that touches a masked column returns masked values — or fails — exactly as a hand-written one would. "Use Gemini to bypass the policy tag" is never a correct answer.
- A generated query costs what any query costs. There is no separate, cheaper execution path. A generated query that omits a partition filter scans the whole table and bills accordingly, which is why
maximum_bytes_billedand a dry-run estimate belong in any workflow where AI-drafted SQL reaches a schedule. - Correctness still has to be proven. The reliability answer in this sub-domain is unchanged: Dataform assertions,
ASSERTstatements, and pipeline-level data-quality checks are what establish that the output is right. An AI assistant writing the transformation does not reduce the need for a test; if anything, it raises it, because the author's mental model of the logic is thinner than if they had written it by hand.
The clean mental separation to carry into the exam:
| Question Asks About | Correct Tooling |
|---|---|
| Writing the transformation faster | Gemini SQL generation, data canvas, data preparation recommendations |
| Understanding an inherited transformation | Gemini SQL explanation |
| Proving the output is correct | Dataform assertions, ASSERT, Dataplex automated data quality |
| Proving the output is complete and fresh | Pipeline SLIs, freshness monitoring, row-count reconciliation |
| Preventing a runaway cost | maximum_bytes_billed, partition filters, custom quotas |
| Governing who can see what | Policy tags, row access policies, dataset IAM |
Where AI-Assisted Preparation Fits Beside Dataform, Dataflow, and Data Fusion
The blueprint lists LLM query generation in the same breath as three execution engines, which is a hint about how to reason: the assistant is an authoring layer, and the engine underneath is chosen on the usual criteria.
- Dataform remains the answer for declarative, version-controlled SQL transformations inside BigQuery with dependency resolution and assertions. Gemini helps you write the SQLX; Dataform is still what runs and tests it.
- Dataflow remains the answer when transformation must happen in flight, on unbounded data, or with logic that SQL expresses poorly. Generated SQL does not change that boundary.
- Cloud Data Fusion remains the answer when the requirement is a visual, low-code pipeline for users who do not write code, with Wrangler for interactive cleansing and built-in lineage.
- Gemini data preparation sits alongside Wrangler conceptually — both suggest cleansing steps interactively — but it produces recommendations grounded in the table's own content and metadata rather than a fixed directive library.
A scenario that says "the team has no engineers who write code and needs lineage out of the box" still resolves to Data Fusion, even if it also mentions AI assistance. A scenario that says "analysts should ask questions in natural language against governed, curated tables" resolves to conversational analytics with a configured data agent.
Exam Traps and Antipatterns Summary
| Scenario Cue | Wrong Answer | Correct Reasoning |
|---|---|---|
| "Generated queries keep hallucinating column names" | Write longer, more detailed prompts | Enrich schema metadata: column descriptions and Dataplex Catalog registration ground the model |
| "Analysts used AI to write a query and it returned masked values" | Grant the AI service a broader role | Expected behavior — the query runs as the caller, and policy tags still apply |
| "An AI-generated scheduled query cost $4,000 in one run" | Disable Gemini for the project | Require a partition filter and set maximum_bytes_billed; generated SQL bills like any SQL |
| "We must guarantee the cleansed output is correct" | The LLM validated the logic | Dataform assertions or ASSERT checks; generation is authoring, not verification |
| "Business users need to ask questions in plain language over curated data" | Give them the BigQuery console and SQL generation | Conversational analytics with a governed data agent |
| "Migrate 4,000 legacy Teradata queries to BigQuery" | Rewrite each one by hand | Batch SQL translation, with Gemini-enhanced translation rules for recurring dialect patterns |
A data engineering team has enabled Gemini in BigQuery and now drafts most of its cleansing transformations with SQL generation. Leadership asks the team lead to guarantee that the nightly curated tables are correct before the finance dashboard reads them. What should the team lead implement?
Analysts at a healthcare provider use Gemini SQL generation in BigQuery to explore a patient table whose diagnosis and member ID columns are protected with policy tags. An analyst without the Fine-Grained Reader role generates a query selecting those columns and is surprised that the results are masked. What is the correct interpretation?
A retail analytics team reports that Gemini's generated queries frequently reference columns that do not exist and join their orders and customers tables on the wrong key. The warehouse has grown organically, with abbreviated column names and no column descriptions. Which action will most improve the quality of generated SQL?