AI-Accelerated Data Engineering

Introduction

30 Second Summary

Every data engineer writes the same kinds of SQL, tests, and documentation over and over again. AI tools can generate solid first drafts of all of it in seconds, but only if you know how to prompt them and how to verify what they give back.

In this project, you will practice five hands-on AI-assisted workflows using your existing Snowflake tables, dbt models, and Python scripts as real context for AI prompts. You will also build a reusable prompt cookbook that you can take into interviews and onto the job.

What You'll Build

You will complete five AI-assisted exercises covering SQL generation, dbt test and documentation generation, debugging, code review, and query optimization, validating every output against your real Snowflake data.

By the end of this project, you'll have:

• Practiced 5 concrete AI-assisted workflows for data engineering, from SQL generation to code review.
• Built a prompt cookbook of reusable templates you can apply to future data engineering tasks.
• Validated every AI-generated output by running it against your actual Snowflake data, reinforcing the principle that AI output is a first draft requiring human verification.
• Secret Mission: Use AI to generate an orphaned-refunds dbt test and a Python data quality report script.

Are there any prerequisites?

This project uses the dbt project from Project 2 and the Python scripts from Project 3 as input context for your AI prompts. You also need a free account on any LLM chat interface such as Claude.ai or ChatGPT.

AI-Assisted SQL Generation

Your Snowflake trial holds five raw financial tables loaded in Project 1. Calculating real business metrics from those tables requires SQL that handles cent-to-dollar conversions, division by zero, and monthly date grouping.

In this step, you'll feed your table schemas to an LLM and prompt it to write that SQL for you. Then you'll run the result against real data to see exactly where the AI nails it and where it falls short.

In this step, get ready to:

• Generate a monthly revenue SQL query by prompting an AI with your table schemas.
• Validate the AI-generated SQL by running it in Snowsight.
• Save the prompt and corrected output to your prompt cookbook.

Paste your table schemas into the AI

An AI generates much better SQL when it knows the exact columns and data types it's working with. You'll pull the CREATE TABLE statements from your Snowflake tables and paste them into the AI as context before writing your prompt.

• Open your AI tool of choice (Claude.ai, ChatGPT, or any LLM chat interface) in your browser.
• Sign in or create a free account if you haven't already.

Which AI tool should I use?

Any free LLM chat interface works for this project. Claude.ai and ChatGPT both offer free tiers with no credit card required.

The prompts and techniques you practice here transfer directly to any AI tool, including paid options like dbt Copilot or GitHub Copilot.

• Open a new browser tab and sign into your Snowflake trial account at your Snowflake URL.
• In Snowsight, click Worksheets in the left sidebar.
• Click the + button in the top right to open a new SQL worksheet.
• Make sure your worksheet context is set to the database and schema where your raw tables live (the same ones you used in Project 1).

Now you need to retrieve the CREATE TABLE statements for your five raw tables. These statements show the AI every column name and data type.

• Retrieve the CREATE TABLE statement for your first table by running this query in your Snowsight worksheet:

SELECT GET_DDL('TABLE', 'RAW_CHARGES');

• Copy the output. Then run the same query for the remaining four tables: RAW_REFUNDS, RAW_DISPUTES, RAW_CUSTOMERS, and RAW_PAYMENT_METHODS.

What does GET_DDL do?

The GET_DDL function returns the full CREATE TABLE statement for any Snowflake table. This includes every column name, data type, and constraint. It gives the AI everything it needs to write accurate SQL against your tables.

• Switch to your AI tool's browser tab.
• Start a new chat.
• Paste all five CREATE TABLE statements into the chat input.
• Below the pasted statements, add the following prompt:

Given these Snowflake tables, write a SQL query that calculates monthly revenue,
refund rate, and chargeback rate, grouped by month. Include comments explaining
the business logic.

• Send the message and wait for the AI to generate its response.

What makes this prompt effective?

This prompt follows three key practices for AI-assisted data engineering. First, it provides full context (the CREATE TABLE statements). Second, it names specific metrics and grouping requirements. Third, it asks for inline comments so you can verify the AI's reasoning.

Vague prompts like "write me a revenue query" produce vague results. Specific context and requirements produce SQL you can actually validate.

Before running anything, review the AI's generated SQL carefully. This review is the skill the job description means by "without lowering the bar."

• Check whether the query converts cents to dollars. Your raw tables store amounts in cents, so any revenue or refund amount should be divided by 100.
• Check that the query references the correct column names from your actual tables.
• Check for division-by-zero handling. If a month has zero charges, calculating a refund rate would fail. The AI should use NULLIF, a CASE WHEN statement, or a similar guard.
• Check that the monthly grouping uses an appropriate date truncation function.

Missing a check? Ask the AI to fix it

If the AI missed cent-to-dollar conversion or division-by-zero handling, paste the generated SQL back into the chat and ask it to add the missing logic. Iterating on AI output is a normal part of the workflow.

Run and validate the AI-generated SQL

Reviewing code by reading is only half the job. Running the query against real data is the validation step that separates "looks right" from "is right."

• Copy the AI-generated SQL query (with any corrections you've made so far).
• Switch to your Snowsight browser tab.
• Paste the SQL into your worksheet.
• Click the Run button (or press Cmd+Enter on macOS, Ctrl+Enter on Windows) to execute the query.

You should see a results table with rows grouped by month, showing revenue, refund rate, and chargeback rate columns.

• Compare these results with the metrics you calculated manually in Project 1. Do the revenue totals match? Are the rates in the right ballpark?
• If any numbers look off, check the AI's SQL for incorrect column references, missing joins, or wrong aggregation logic.
• Fix any errors directly in the Snowsight worksheet and re-run until the results match your Project 1 calculations.

Query returned an error?

If you see a column-not-found error, the AI likely guessed a column name that doesn't exist in your tables. Check the exact column names in your CREATE TABLE statements and update the query to match.

If you see a permissions error, make sure your worksheet context is set to the correct database and schema from Project 1.

Help me debug this Snowflake SQL error from an AI-generated query

What did this exercise teach you?

AI is highly reliable for common SQL patterns like SELECT, JOIN, GROUP BY, and window functions. It tends to get the overall structure right.

Where it stumbles is domain-specific details: the cent-to-dollar conversion that only someone familiar with Stripe data would know, or the exact column names in your particular schema. This is why human review is non-negotiable.

Save your first prompt cookbook entry

A prompt cookbook is a personal reference of effective prompts you can reuse across future data engineering tasks. Each entry captures what you asked, what context you provided, and what the AI got right and wrong.

• Open prompt_cookbook.md in your code editor.
• Replace the empty file contents with the following template:

# Prompt Cookbook

## Entry 1: Monthly Revenue and Rates SQL Generation

**Task**: Generate SQL to calculate monthly revenue, refund rate, and chargeback rate from raw Snowflake financial tables.

**Prompt**: Given these Snowflake tables, write a SQL query that calculates monthly revenue, refund rate, and chargeback rate, grouped by month. Include comments explaining the business logic.

**Context**: CREATE TABLE statements for raw_charges, raw_refunds, raw_disputes, raw_customers, raw_payment_methods pasted as context.

**Output**: [Corrected SQL query with cent-to-dollar conversion, division-by-zero handling, and monthly grouping]

**Lessons**: [Notes on what the AI got right and wrong, e.g., whether it handled cent-to-dollar conversion, used correct columns, handled NULL division]

What does each field capture?

• Task is a one-line summary of the goal. This helps you find the right template later.
• Prompt is the exact text you sent to the AI. Copy-paste it so you can reuse it word for word.
• Context describes what supporting material you included. Next time you face a similar task, you'll know what context to gather.
• Output is the corrected version of the AI's response, after your review and fixes.
• Lessons captures where the AI excelled and where it fell short. Over time, these notes reveal patterns in AI reliability.

• Replace the Output placeholder with your actual corrected SQL query from the Snowsight worksheet.
• Replace the Lessons placeholder with your notes on what the AI got right and wrong.
• Save prompt_cookbook.md.

✔️ Awesome, I've got everything!

Great. Double check you've saved your file with the corrected SQL and your lessons filled in.

ⓧ I'd like to double check the full code

Here is the full structure your prompt_cookbook.md should follow. The Output and Lessons sections should contain your own content from this exercise.

# Prompt Cookbook

## Entry 1: Monthly Revenue and Rates SQL Generation

**Task**: Generate SQL to calculate monthly revenue, refund rate, and chargeback rate from raw Snowflake financial tables.

**Prompt**: Given these Snowflake tables, write a SQL query that calculates monthly revenue, refund rate, and chargeback rate, grouped by month. Include comments explaining the business logic.

**Context**: CREATE TABLE statements for raw_charges, raw_refunds, raw_disputes, raw_customers, raw_payment_methods pasted as context.

**Output**: [Corrected SQL query with cent-to-dollar conversion, division-by-zero handling, and monthly grouping]

**Lessons**: [Notes on what the AI got right and wrong, e.g., whether it handled cent-to-dollar conversion, used correct columns, handled NULL division]

Your first AI-assisted workflow is complete, and your prompt cookbook has its first entry. Next up, you'll use AI to generate dbt tests and documentation for your existing models.

AI-Generated dbt Tests and Documentation

In Step 1, you generated SQL with AI and validated it against your real Snowflake data. That exercise proved out the core AI workflow: provide context, prompt, review, and verify.

Now you'll apply that same workflow to dbt. In this step, you'll use AI to generate tests and column documentation for your fct_transaction_summary model, then confirm everything works by running dbt build.

In this step, get ready to:

• Generate dbt data_tests for your fct_transaction_summary model using AI.
• Generate financial-term column descriptions using AI and add them to your dbt project.
• Validate all AI-generated tests and documentation by running dbt build and dbt docs.

Prompt AI to generate dbt tests

Your dbt project from Project 2 already has a fct_transaction_summary model. Writing tests for it manually means looking up YAML syntax, remembering test types, and formatting everything correctly. AI can generate all of this from the model's SQL alone.

• Open your dbt project from Project 2 in VS Code or your text editor.
• Find the file models/marts/fct_transaction_summary.sql and copy its entire contents.
• Switch to your AI tool (Claude.ai, ChatGPT, or whichever you used in Step 1).
• Paste the SQL into the chat, then add this prompt below it:

Generate a dbt YAML file with data_tests for this model. Include unique and not_null tests on the primary key, accepted_values on transaction_status, and a relationships test linking charge_id back to stg_charges. Use the current dbt syntax with the data_tests key.

What should the AI output look like?

The AI should return a YAML file with a models: key and a data_tests section under each column. Check for these four things:

• The primary key column (like transaction_id) has both unique and not_null tests.
• The transaction_status column has an accepted_values test with values that match your actual data (like succeeded, failed, pending).
• The charge_id column has a relationships test with to: ref('stg_charges') and field: charge_id.
• The YAML uses data_tests as the key, not the older tests key that was renamed in recent dbt versions.

If any of these checks fail, ask the AI to correct its output. A common issue is using the outdated tests key instead of data_tests.

AI used the wrong test syntax?

If the AI generates tests: instead of data_tests:, it may be trained on older dbt documentation. Prompt it again with: "Use the data_tests key, not the older tests key. dbt renamed this in recent versions."

If the AI guesses incorrect values for accepted_values, check your actual data in Snowflake to find the correct status values.

My AI-generated dbt tests use the wrong syntax or incorrect values. How do I fix this?

Prompt AI to generate column descriptions

Tests check that your data is correct. Documentation explains what your data means. Good column descriptions help anyone reading your dbt project understand the business logic behind each field.

• In the same AI chat (or a new one), paste the fct_transaction_summary.sql contents again.
• Add this prompt below the pasted SQL:

Generate a dbt YAML file with detailed column descriptions for every column in this model. The descriptions should explain what each column means in financial terms, not just technical terms. For example, net_amount should explain that it represents the final settlement amount after all deductions.

What makes a good column description?

Financial-term descriptions connect technical columns to business meaning. For example, amount described as "an integer column" is technically correct but unhelpful.

Describing it as "the original charge amount in cents, before refunds or disputes" tells a data analyst exactly what the number represents. That extra context is what makes AI-generated documentation worth keeping.

• Review each description the AI generates.
• Edit any descriptions that are vague, technically incorrect, or missing financial context.
• Open your dbt project's _marts.yml file in the models/marts/ directory.
• Merge the AI-generated tests from the previous substep and the column descriptions from this substep into _marts.yml. Both the data_tests and columns with descriptions should be under the fct_transaction_summary model entry.
• Save _marts.yml.

Merging YAML carefully

If your _marts.yml already has an entry for fct_transaction_summary, add the data_tests and columns sections under the existing entry. Do not duplicate the model name.

Validate with dbt and update your cookbook

AI-generated YAML needs to pass the same quality bar as hand-written YAML. Running dbt build confirms that the tests are syntactically correct and that they pass against your actual Snowflake data.

• In your terminal, navigate to your dbt project directory.
• Compile and test your project by running this command:

dbt build

You should see output showing your models building and your new tests running. Every test should show PASS.

Tests failing after dbt build?

• If a unique or not_null test fails, the issue is in your data, not your YAML. Check the failing rows in Snowflake to understand why.
• If you see a YAML parsing error, double-check the indentation. YAML is sensitive to spaces, and a single misalignment will break the file.
• If the relationships test fails, make sure stg_charges exists as a model in your project and contains the charge_id column.

My AI-generated dbt tests are failing. How do I debug this?

Now generate the documentation site to see your column descriptions in action.

• Generate your dbt docs by running this command:

dbt docs generate

• Serve the docs site locally by running:

dbt docs serve

• Your browser should open automatically to the dbt docs site.
• Navigate to the fct_transaction_summary model in the docs site.
• Verify that every column shows the financial-term description you reviewed and edited.

Descriptions not showing up?

Make sure you saved _marts.yml before running dbt docs generate. The generate command reads from disk, so unsaved changes will not appear.

Also confirm that the columns: key is nested under the correct model name in your YAML. A misplaced indent can put the descriptions under the wrong model.

My dbt docs are not showing the column descriptions I added. How do I fix this?

Now update your prompt cookbook with the templates from this step.

• Open prompt_cookbook.md in your text editor.
• Add Entry 2 for dbt test generation by copying this template below your existing Entry 1:

## Entry 2: dbt Test Generation

**Task**: Generate dbt YAML data_tests for fct_transaction_summary model.

**Prompt**: Generate a dbt YAML file with data_tests for this model. Include unique and not_null tests on the primary key, accepted_values on transaction_status, and a relationships test linking charge_id back to stg_charges. Use the current dbt syntax with the data_tests key.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Paste your corrected YAML with data_tests here]

**Lessons**: [Notes on YAML syntax correctness, test configuration accuracy]

What does each field capture?

• Task names the goal in one sentence.
• Prompt is the exact text you sent to the AI.
• Context describes what you pasted alongside the prompt.
• Output is the corrected version you actually used, not the raw AI response.
• Lessons records what the AI got right and wrong so future prompts improve.

• Below Entry 2, add Entry 3 for documentation generation:

## Entry 3: dbt Documentation Generation

**Task**: Generate financial-term column descriptions for fct_transaction_summary.

**Prompt**: Generate a dbt YAML file with detailed column descriptions for every column in this model. The descriptions should explain what each column means in financial terms, not just technical terms.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Paste your reviewed and edited column descriptions here]

**Lessons**: [Notes on description quality and accuracy]

• Fill in the Output and Lessons sections for both entries with your actual results from this step.
• Save prompt_cookbook.md.

Why fill in the Lessons field?

The Lessons field is the most valuable part of your cookbook. Noting what the AI got right and wrong builds your intuition for future prompts. Over time, you will learn which instructions produce better output and which details you always need to correct.

✔️ Awesome, I've got everything!

Make sure you have saved both _marts.yml and prompt_cookbook.md.

ⓧ I'd like to double check the full code

Your prompt_cookbook.md should now have three entries. Here is the full structure:

# Prompt Cookbook

## Entry 1: Monthly Revenue and Rates SQL Generation

**Task**: Generate SQL to calculate monthly revenue, refund rate, and chargeback rate from raw Snowflake financial tables.

**Prompt**: Given these Snowflake tables, write a SQL query that calculates monthly revenue, refund rate, and chargeback rate, grouped by month. Include comments explaining the business logic.

**Context**: CREATE TABLE statements for raw_charges, raw_refunds, raw_disputes, raw_customers, raw_payment_methods pasted as context.

**Output**: [Corrected SQL query]

**Lessons**: [Notes on AI accuracy]

## Entry 2: dbt Test Generation

**Task**: Generate dbt YAML data_tests for fct_transaction_summary model.

**Prompt**: Generate a dbt YAML file with data_tests for this model. Include unique and not_null tests on the primary key, accepted_values on transaction_status, and a relationships test linking charge_id back to stg_charges. Use the current dbt syntax with the data_tests key.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Corrected YAML with data_tests]

**Lessons**: [Notes on YAML syntax correctness, test configuration accuracy]

## Entry 3: dbt Documentation Generation

**Task**: Generate financial-term column descriptions for fct_transaction_summary.

**Prompt**: Generate a dbt YAML file with detailed column descriptions for every column in this model. The descriptions should explain what each column means in financial terms, not just technical terms.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Reviewed and edited column descriptions added to _marts.yml]

**Lessons**: [Notes on description quality and accuracy]

Your dbt project now has AI-generated tests and documentation that passed validation against real data. Next up, you'll use AI to debug broken SQL and review Python code for potential issues.

AI-Powered Debugging and Code Review

Your dbt project now has AI-generated tests and documentation, all validated against real data.

Generating new code is one thing. Fixing broken code is another. In this step, you'll test how well AI handles debugging and code review, two tasks where it can save you serious time.

In this step, get ready to:

• Debug a broken SQL query using AI.
• Get an AI-powered code review of your Python extraction script.
• Update your prompt cookbook with debugging and code review templates.

Break a SQL query and debug it with AI

When a query fails, you need to read the error message, trace the cause, and write a fix. AI can shortcut this process by parsing the error message and the query together to pinpoint the root cause.

To test this, you'll create a query that's intentionally broken, grab the error from Snowflake, and hand both to the AI.

• Open Snowsight in your browser and navigate to Worksheets.
• Click the + Worksheet button to create a new worksheet.
• Write a SQL query that contains a deliberate error. Paste this example into the worksheet:

-- Intentionally broken query: status is in SELECT but not in GROUP BY
SELECT
    customer_id,
    status,
    SUM(amount) AS total_amount,
    COUNT(*) AS charge_count
FROM raw_charges
GROUP BY customer_id

What makes this query broken?

The status column appears in the SELECT clause but is missing from the GROUP BY clause. Snowflake doesn't know how to collapse multiple status values into a single row per customer.

If your table uses different column names, swap them in. The important thing is that the query produces a Snowflake error.

Other error types to try

You can also try referencing a column that doesn't exist (like total_revenue instead of amount) or using the wrong join type. Pick whichever error feels most realistic to you.

• Click the Run button (or press Cmd+Enter on macOS, Ctrl+Enter on Windows) to execute the broken query.
• You should see a red error message in the results panel. Select and copy the full error message text.
• Switch to your AI tool (Claude.ai, ChatGPT, or similar). Paste both the broken query and the Snowflake error message together, followed by this prompt:

This Snowflake SQL query produces the following error.
Explain what is wrong, why it happened, and provide the corrected SQL.

[Paste your broken SQL query here]

[Paste the Snowflake error message here]

Why include both the query and the error?

Providing both pieces of context helps the AI give a precise answer. The error message tells it what went wrong, and the query tells it where to look. Without the query, the AI can only guess at the structure.

Read through the AI's response carefully before applying any fix.

• Check whether the AI correctly identifies the root cause of the error.
• Verify that the corrected SQL actually addresses the issue (for example, does the fix add status to the GROUP BY clause, or does it wrap it in an aggregate function?).
• Copy the AI's corrected SQL and paste it into your Snowsight worksheet, replacing the broken query.
• Click Run to execute the corrected query.

The query should now return results without errors. If the AI's fix introduced a new problem, correct it yourself and note what it got wrong.

AI's fix still produces an error?

Paste the new error back into the AI along with the updated query. AI tools handle iterative debugging well because each round narrows the problem space.

If the query references table or column names that don't exist in your Snowflake database, the AI can't catch that without seeing your schema. Double-check that you're using columns from your actual raw_charges table.

Help me debug this Snowflake SQL error

Use AI to review your Python script

Debugging fixes a specific error. Code review looks at the bigger picture: error handling gaps, security concerns, and performance issues. AI is surprisingly good at spotting common patterns here.

The catch is that AI also generates false positives. Not every suggestion it makes will be relevant to your use case. Learning to separate the signal from the noise is a critical skill.

• Open your extract_stripe.py script from Project 3 in your editor.
• Select all the code and copy it to your clipboard.
• Switch to your AI tool and paste the full script, followed by this prompt:

Review this Python script for potential issues. Check for:
- error handling gaps
- missing edge cases
- security concerns (hardcoded credentials)
- performance issues

Suggest specific improvements with code examples.

Why ask for specific categories?

Listing the categories (error handling, edge cases, security, performance) in the prompt tells the AI exactly what lens to apply. Without these categories, you'll get a generic review. With them, you get structured feedback you can evaluate point by point.

The AI will return a list of suggestions. Now comes the most important part of this exercise: evaluating each suggestion critically.

• Read through each suggestion the AI provides.
• For each suggestion, decide whether it is valid (a real issue worth fixing), a false positive (technically correct but irrelevant to your context), or wrong (the AI misunderstood something).

What types of suggestions should you expect?

AI typically catches hardcoded credentials quickly. If your script has a Stripe API key stored as a plain string, expect the AI to flag it immediately. It will also spot missing try/except blocks and suggest retry logic for API calls.

Where AI tends to miss the mark is business logic. It might suggest pagination improvements without understanding Stripe's specific rate limits. It might recommend caching that doesn't make sense for a one-time extraction. These are the false positives you need to catch.

AI suggestions seem too generic?

If the suggestions read like boilerplate advice, try adding more context to your prompt. Mention that the script extracts data from the Stripe API and loads it into Snowflake. Domain-specific context produces domain-specific feedback.

Help me get better code review results from AI

Add debugging and code review templates to your prompt cookbook

You now have two more proven workflows to document. Adding these to your prompt_cookbook.md turns a one-time exercise into a reusable reference.

• Open prompt_cookbook.md in your editor.
• Add Entry 4 for SQL Debugging below your existing entries by copying this template:

## Entry 4: SQL Debugging

**Task**: Identify and fix errors in broken Snowflake SQL queries.

**Prompt**: This Snowflake SQL query produces the following error. Explain what is wrong, why it happened, and provide the corrected SQL.

**Context**: Broken SQL query and full Snowflake error message pasted as context.

**Output**: [Paste the AI's explanation and corrected SQL here]

**Lessons**: [Your notes on whether the AI correctly identified the root cause, and what types of errors it catches well vs. misses]

What should go in the Lessons section?

Write down whether the AI correctly identified the root cause on the first try. Note whether it explained why the error happened or just gave you fixed code. These notes help you calibrate your trust in AI debugging over time.

• Fill in the Output and Lessons sections with your actual results from the debugging exercise.
• Now add Entry 5 for Python Code Review by copying this template below Entry 4:

## Entry 5: Python Code Review

**Task**: Review Python data engineering script for issues.

**Prompt**: Review this Python script for potential issues. Check for: error handling gaps, missing edge cases, security concerns (hardcoded credentials), and performance issues. Suggest specific improvements with code examples.

**Context**: Full extract_stripe.py script pasted as context.

**Output**: [List each AI suggestion and your evaluation: valid, false positive, or irrelevant]

**Lessons**: [AI catches well: syntax errors, common patterns like missing try/except, hardcoded secrets. AI misses: business logic errors, domain-specific validation, Stripe API edge cases.]

• Fill in the Output section with each suggestion the AI gave you, along with your verdict (valid, false positive, or irrelevant) for each one.
• Update the Lessons section with your observations about what the AI caught well versus what it missed.
• Save prompt_cookbook.md.

✔️ Awesome, I've got everything!

Great. Double check that you've saved prompt_cookbook.md with all five entries.

ⓧ I'd like to double check the full code

Here's the full structure your prompt_cookbook.md should have at this point. Your actual content in the Output and Lessons fields will differ based on your AI's responses.

# Prompt Cookbook

## Entry 1: Monthly Revenue and Rates SQL Generation

**Task**: Generate SQL to calculate monthly revenue, refund rate, and chargeback rate from raw Snowflake financial tables.

**Prompt**: Given these Snowflake tables, write a SQL query that calculates monthly revenue, refund rate, and chargeback rate, grouped by month. Include comments explaining the business logic.

**Context**: CREATE TABLE statements for raw_charges, raw_refunds, raw_disputes, raw_customers, raw_payment_methods pasted as context.

**Output**: [Your corrected SQL query]

**Lessons**: [Your notes on AI accuracy]

## Entry 2: dbt Test Generation

**Task**: Generate dbt YAML data_tests for fct_transaction_summary model.

**Prompt**: Generate a dbt YAML file with data_tests for this model. Include unique and not_null tests on the primary key, accepted_values on transaction_status, and a relationships test linking charge_id back to stg_charges. Use the current dbt syntax with the data_tests key.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Your corrected YAML with data_tests]

**Lessons**: [Your notes on YAML syntax correctness, test configuration accuracy]

## Entry 3: dbt Documentation Generation

**Task**: Generate financial-term column descriptions for fct_transaction_summary.

**Prompt**: Generate a dbt YAML file with detailed column descriptions for every column in this model. The descriptions should explain what each column means in financial terms, not just technical terms.

**Context**: SQL for fct_transaction_summary.sql pasted as context.

**Output**: [Your reviewed and edited column descriptions added to _marts.yml]

**Lessons**: [Your notes on description quality and accuracy]

## Entry 4: SQL Debugging

**Task**: Identify and fix errors in broken Snowflake SQL queries.

**Prompt**: This Snowflake SQL query produces the following error. Explain what is wrong, why it happened, and provide the corrected SQL.

**Context**: Broken SQL query and full Snowflake error message pasted as context.

**Output**: [AI explanation of root cause and corrected SQL]

**Lessons**: [Notes on whether AI correctly identified root cause; types of errors AI catches well vs. misses]

## Entry 5: Python Code Review

**Task**: Review Python data engineering script for issues.

**Prompt**: Review this Python script for potential issues. Check for: error handling gaps, missing edge cases, security concerns (hardcoded credentials), and performance issues. Suggest specific improvements with code examples.

**Context**: Full extract_stripe.py script pasted as context.

**Output**: [List of AI suggestions with evaluation of each: valid, false positive, or irrelevant]

**Lessons**: [AI catches well: syntax errors, common patterns like missing try/except, hardcoded secrets. AI misses: business logic errors, domain-specific validation, Stripe API edge cases]

What did this step teach you about AI reliability?

AI handles syntax errors and common patterns with high reliability. GROUP BY mistakes, missing columns, and unhandled exceptions are well-trodden territory for LLMs.

Business logic and domain-specific validation are a different story. The AI doesn't know your Stripe data model or your company's financial rules. That's where your expertise stays essential.

You've seen how AI handles debugging and code review. Next up, you'll push it further with SQL optimization and script automation.

AI for SQL Optimization and Automation

In Step 3, you used AI to debug broken SQL and critically evaluate code review suggestions. You now know which types of errors AI catches well and where it falls short.

This step pushes AI into two more advanced workflows: optimizing a SQL query for performance and generating a complete Python automation script from scratch. Both exercises reveal an important pattern. AI gives confident-sounding advice that you need to evaluate against how your specific tools actually work.

In this step, get ready to:

• Use AI to generate SQL optimization suggestions and evaluate them against Snowflake's cost-based optimizer.
• Generate a complete Python data quality report script using AI, then run and fix it against your real data.
• Complete your prompt_cookbook.md with a Principles section summarizing when AI helps and when it needs editing.

Prompt AI to optimize your SQL query

SQL optimization is one of the most common tasks AI tools are used for. The catch is that many optimization tips are generic advice that doesn't account for how your specific database engine works.

Snowflake has a cost-based optimizer that automatically determines execution plans. This means many traditional SQL tuning tricks are irrelevant for Snowflake.

• Open the raw fct_transaction_summary SQL query from Project 1 (the raw SQL version, not the dbt model).
• Paste the full query into your AI chat interface along with the following prompt:

Optimize this Snowflake SQL query for performance. Consider:
- CTE vs. subquery trade-offs
- Join order
- Unnecessary computations
- Snowflake-specific optimizations

Explain each optimization and why it improves performance.

The AI will return an optimized version of your query along with explanations for each change. Before you accept anything, evaluate each suggestion against what you know about Snowflake.

• Read through each optimization the AI suggests.
• For each suggestion, decide whether it is valid for Snowflake or based on generic SQL advice that does not apply.
• Note your evaluation next to each suggestion. You will add this to your prompt cookbook shortly.

What should I look for in the AI's suggestions?

CTE vs. subquery: In Snowflake, CTEs and subqueries perform similarly because the cost-based optimizer handles both efficiently. If the AI claims one is significantly faster than the other, that is misleading for Snowflake.

Join order: Snowflake's optimizer automatically determines the best join order. Manual join reordering suggestions are usually irrelevant.

Valid suggestions to watch for: Reducing unnecessary columns (replacing SELECT * with specific columns), removing redundant computations, and using appropriate data types. These are legitimate improvements regardless of engine.

Why does Snowflake's cost-based optimizer matter here?

AI models are trained on general SQL optimization advice from across the internet. Much of that advice targets databases like MySQL or PostgreSQL where the query planner is less sophisticated. Snowflake handles many of those concerns automatically. Catching these mismatches is exactly what "without lowering the bar" means in practice.

Generate a Python data quality report with AI

Generating entire scripts from natural language descriptions is one of AI's strongest use cases. The quality of the output depends heavily on how specific your prompt is.

• Paste the following prompt into your AI chat interface:

Write a Python script that connects to Snowflake, queries the fct_transaction_summary table, and generates a markdown-formatted data quality report showing:
- Total row count
- Null counts per column
- Min/max/avg for numeric columns
- Distinct value counts for categorical columns

Use the snowflake-connector-python library. Use environment variables for Snowflake credentials, not hardcoded values. Close the cursor and connection when done.

Be specific about security

Notice the prompt explicitly says "use environment variables, not hardcoded values." Without this instruction, many AI models generate scripts with placeholder credentials hardcoded directly in the file. Always specify security requirements upfront in your prompts.

The AI will generate a complete Python script. Before running it, review the output carefully.

• Check that the script uses os.environ for Snowflake credentials instead of hardcoded strings.
• Check that it closes both the cursor and connection at the end.
• Check that it correctly separates numeric columns from categorical columns for the statistics.
• If the AI hardcoded credentials or missed any of these points, fix the script before proceeding.

Compare your AI-generated version against this reference to catch any differences.

✔️ I'll review mine first

Great. Use the review checklist above and fix any issues before saving.

ⓧ Let me check the reference

Here is what the corrected script should look like. Compare it against your AI-generated version:

import snowflake.connector
import pandas as pd

# Snowflake connection parameters (use environment variables, not hardcoded values)
import os

conn = snowflake.connector.connect(
    user=os.environ['SNOWFLAKE_USER'],
    password=os.environ['SNOWFLAKE_PASSWORD'],
    account=os.environ['SNOWFLAKE_ACCOUNT'],
    warehouse=os.environ.get('SNOWFLAKE_WAREHOUSE', 'COMPUTE_WH'),
    database=os.environ.get('SNOWFLAKE_DATABASE', 'STRIPE_DB'),
    schema=os.environ.get('SNOWFLAKE_SCHEMA', 'MARTS')
)

cursor = conn.cursor()

# Query the fct_transaction_summary table
cursor.execute('SELECT * FROM fct_transaction_summary')
df = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame(df, columns=columns)

# Generate markdown report
report_lines = ['# Data Quality Report: fct_transaction_summary', '']
report_lines.append(f'**Total Rows**: {len(df)}')
report_lines.append('')
report_lines.append('## Null Counts per Column')
for col in df.columns:
    null_count = df[col].isnull().sum()
    report_lines.append(f'- {col}: {null_count} nulls')
report_lines.append('')
report_lines.append('## Numeric Column Statistics')
for col in df.select_dtypes(include='number').columns:
    report_lines.append(f'- {col}: min={df[col].min()}, max={df[col].max()}, avg={df[col].mean():.2f}')
report_lines.append('')
report_lines.append('## Categorical Column Distinct Counts')
for col in df.select_dtypes(include='object').columns:
    report_lines.append(f'- {col}: {df[col].nunique()} distinct values')

with open('data_quality_report.md', 'w') as f:
    f.write('\n'.join(report_lines))

print('Report written to data_quality_report.md')
cursor.close()
conn.close()

• Save the corrected script as data_quality_report.py in your project folder.

Now run the script against your actual Snowflake data. You need to set your Snowflake credentials as environment variables first.

• Set your Snowflake environment variables in your terminal by running these commands (replace the placeholder values with your actual credentials):

macOS

export SNOWFLAKE_USER=your-snowflake-username
export SNOWFLAKE_PASSWORD=your-snowflake-password
export SNOWFLAKE_ACCOUNT=your-snowflake-account-identifier

Windows

$env:SNOWFLAKE_USER="your-snowflake-username"
$env:SNOWFLAKE_PASSWORD="your-snowflake-password"
$env:SNOWFLAKE_ACCOUNT="your-snowflake-account-identifier"

Where do I find my Snowflake account identifier?

Your Snowflake account identifier is the part of your Snowflake URL before .snowflakecomputing.com. For example, if your URL is abc12345.us-east-1.snowflakecomputing.com, your account identifier is abc12345.us-east-1. You can find this in your Snowsight URL bar.

• Run the data quality report script by running this command:

python data_quality_report.py

• You should see Report written to data_quality_report.md printed in your terminal.
• Open data_quality_report.md and verify it contains all four sections: total rows, null counts, numeric statistics, and categorical distinct counts.

Script failing to connect?

Make sure your environment variables are set in the same terminal session where you run the script. If you opened a new terminal window, you need to set them again.

Double check that your account identifier format is correct. It should include the region (e.g. abc12345.us-east-1), not just the account name.

If you see a ModuleNotFoundError for snowflake.connector or pandas, install them by running:

pip install snowflake-connector-python pandas

Still stuck? Help me debug my Snowflake connection in data_quality_report.py

Complete your prompt cookbook

You have now completed all the AI-assisted exercises. The final task is to document what you learned so you can reference it in the future.

• Open prompt_cookbook.md in your editor.
• Add Entry 6 for SQL Optimization and Entry 7 for Python Automation Script Generation using the same format as your previous entries (Task, Prompt, Context, Output, Lessons).
• Copy the following template for both new entries:

## Entry 6: SQL Optimization

**Task**: Optimize Snowflake SQL query for performance.

**Prompt**: Optimize this Snowflake SQL query for performance. Consider: CTE vs. subquery trade-offs, join order, unnecessary computations, and Snowflake-specific optimizations. Explain each optimization and why it improves performance.

**Context**: Raw fct_transaction_summary SQL query pasted as context.

**Output**: [Optimized SQL with before/after comparison and evaluation of each suggestion]

**Lessons**: [Notes on which optimizations are valid for Snowflake's cost-based optimizer vs. which are irrelevant]

## Entry 7: Python Automation Script Generation

**Task**: Generate a Python data quality report script for Snowflake.

**Prompt**: Write a Python script that connects to Snowflake, queries the fct_transaction_summary table, and generates a markdown-formatted data quality report showing: total row count, null counts per column, min/max/avg for numeric columns, and distinct value counts for categorical columns. Use the snowflake-connector-python library.

**Context**: fct_transaction_summary table schema and Snowflake connection details structure.

**Output**: [Corrected data_quality_report.py]

**Lessons**: [Notes on what needed fixing in the generated script]

What goes in the Lessons field?

Fill in the bracketed sections with your actual experience. For Entry 6, note which optimization suggestions were valid for Snowflake and which were generic advice. For Entry 7, note what the AI got wrong in the Python script and what you had to fix.

Now add the most important part of the cookbook. A Principles section captures everything you have learned across all seven exercises about working with AI effectively.

• Add the following Principles section at the bottom of prompt_cookbook.md:

## Principles

**When AI helps most**: Boilerplate code, syntax, documentation, common SQL patterns, test scaffolding.

**When AI needs heavy editing**: Business logic, domain-specific validation, tool-specific parsing, Snowflake optimizer nuances.

**Verification workflow**: Generate → Review → Test → Deploy. Never skip the Test step.

Why does this cookbook matter?

This prompt cookbook is a tangible deliverable you can reference in interviews. When someone asks how you use AI for data engineering, you can walk them through specific prompt templates and the lessons you learned from validating the output.

The Principles section is the most valuable part. It captures the professional judgment that separates someone who uses AI effectively from someone who copies and pastes without thinking.

• Customize the Principles section with your own observations from the exercises. Add or adjust bullet points based on what you personally experienced.
• Save prompt_cookbook.md.

✔️ Awesome, I've got everything!

Double check that your prompt_cookbook.md has all 7 entries plus the Principles section, and that data_quality_report.py runs successfully.

ⓧ I'd like to double check the full code

Here is the complete data_quality_report.py for reference:

import snowflake.connector
import pandas as pd

# Snowflake connection parameters (use environment variables, not hardcoded values)
import os

conn = snowflake.connector.connect(
    user=os.environ['SNOWFLAKE_USER'],
    password=os.environ['SNOWFLAKE_PASSWORD'],
    account=os.environ['SNOWFLAKE_ACCOUNT'],
    warehouse=os.environ.get('SNOWFLAKE_WAREHOUSE', 'COMPUTE_WH'),
    database=os.environ.get('SNOWFLAKE_DATABASE', 'STRIPE_DB'),
    schema=os.environ.get('SNOWFLAKE_SCHEMA', 'MARTS')
)

cursor = conn.cursor()

# Query the fct_transaction_summary table
cursor.execute('SELECT * FROM fct_transaction_summary')
df = cursor.fetchall()
columns = [desc[0] for desc in cursor.description]
df = pd.DataFrame(df, columns=columns)

# Generate markdown report
report_lines = ['# Data Quality Report: fct_transaction_summary', '']
report_lines.append(f'**Total Rows**: {len(df)}')
report_lines.append('')
report_lines.append('## Null Counts per Column')
for col in df.columns:
    null_count = df[col].isnull().sum()
    report_lines.append(f'- {col}: {null_count} nulls')
report_lines.append('')
report_lines.append('## Numeric Column Statistics')
for col in df.select_dtypes(include='number').columns:
    report_lines.append(f'- {col}: min={df[col].min()}, max={df[col].max()}, avg={df[col].mean():.2f}')
report_lines.append('')
report_lines.append('## Categorical Column Distinct Counts')
for col in df.select_dtypes(include='object').columns:
    report_lines.append(f'- {col}: {df[col].nunique()} distinct values')

with open('data_quality_report.md', 'w') as f:
    f.write('\n'.join(report_lines))

print('Report written to data_quality_report.md')
cursor.close()
conn.close()

And here is the complete Principles section for prompt_cookbook.md:

## Principles

**When AI helps most**: Boilerplate code, syntax, documentation, common SQL patterns, test scaffolding.

**When AI needs heavy editing**: Business logic, domain-specific validation, tool-specific parsing, Snowflake optimizer nuances.

**Verification workflow**: Generate → Review → Test → Deploy. Never skip the Test step.

Secret mission

AI-Generated Data Quality Test and Report

You have practiced five AI-assisted workflows. Now put it all together. Use AI to generate a real dbt singular test that catches orphaned refunds, plus a Python script that produces an automated data quality report. This is a preview of what AI-accelerated data quality looks like in production.

Before you go

Clean Up Your Resources

This project used free AI tools and your existing project files, so there are no ongoing costs. Decide whether to keep your new files and AI-generated outputs, or clean them up.

Resources you used:

• Free AI tool account (Claude.ai, ChatGPT, or similar).
• prompt_cookbook.md file with reusable prompt templates.
• AI-generated dbt tests and documentation added to your dbt project.
• AI-generated SQL queries saved in Snowflake worksheets.
• Snowflake trial account (created in Project 1).
• dbt project (from Project 2) with new tests and docs.
• Python scripts (from Project 3) reviewed by AI.

Keep everything running

No action needed. Choose this if you plan to keep practicing AI-assisted workflows or want to expand your prompt cookbook.

• Your prompt_cookbook.md stays in your project folder, ready for new entries.
• Your AI-generated dbt tests and documentation remain in your dbt project.
• Your Snowflake trial continues running until its credits expire.
• Your free AI tool accounts remain active with no charges.

Pause - I'll come back to this later

Keep your files but close any running processes to free up system resources.

• Close any open Snowflake worksheets in your browser.
• Stop the dbt docs serve process if it is still running in your terminal. Press Ctrl+C to stop it.
• Keep your prompt_cookbook.md file and dbt project folder intact for when you return.
• Log out of your AI tool (Claude.ai, ChatGPT, etc.) if you are on a shared computer.

Delete - I don't want to use this again

Remove all project outputs created during this project. This does not affect your original dbt project or Python scripts from previous projects.

• Delete the prompt_cookbook.md file from your project folder.
• Remove any AI-generated dbt test files you added to your tests/ directory.
• Revert any AI-generated documentation changes in your _marts.yml file, or restore it from version control.
• Delete any AI-generated Python scripts you saved locally.
• Delete any Snowflake worksheets you created during the exercises.
• Your free AI tool accounts can remain open at no cost, or you can delete them from each provider's account settings page.

Mission accomplished

That's a Wrap!

Nice work! You just learned how to use AI to accelerate real data engineering tasks while keeping quality high.

You've learned how to:

• Practiced 5 AI-assisted workflows for data engineering, including SQL generation, dbt test and documentation generation, debugging, code review, and query optimization.
• Validated every AI-generated output against real Snowflake data, reinforcing the principle that AI output is a first draft that always requires human verification before deployment.
• Built a reusable prompt cookbook with templates for common data engineering tasks that you can reference in interviews and on the job.
• Generated and validated an orphaned-refunds dbt test and a Python data quality report using AI prompts in the Secret Mission.

Ready to quiz yourself?