Gains:
- Ability to take robust SQL and pandas code and read and verify it line by line by giving clear schema and purpose to artificial intelligence
- Ability to catch silent errors such as row count, fitting function and throughput after merge/JOIN
- Ability to solve the problem in debug without silencing it and avoid running the code without testing it in the production environment
Data science has two primary languages: SQL (Structured Query Language — the language for querying data from databases) and Python (specifically, the pandas library — the standard tool for manipulating tables programmatically). In this unit, we will learn to use AI as a code partner: getting solid SQL and pandas code from it with the right questions, reading and validating that code, debugging it, and never running it blindly. AI writes repetitive code in seconds instead of minutes; But it is your job to make sure that the code it produces processes the correct column with the correct logic. Working code does not mean correct code.
Why producing code with AI is powerful but risky
AI provides three big benefits in code generation: speed (writes a 30-line groupby-pivot operation in seconds), reminder (reminds you of a pandas function you forgot), and teaching (explains the code line by line). But it carries three risks: silent logic error (code that sums the wrong column runs without errors), fitted function (suggests a method that does not exist), and yield trap (code that works on small data but crashes at 10 million rows). So the golden rule: Read the AI's code as if you wrote it yourself. Don't run the line you don't understand.
SQL: process data at source
SQL allows you to retrieve data from the database and process it there; You can summarize millions of lines without pulling them into Python. Basic building blocks: SELECT (which columns), WHERE (which rows), GROUP BY (group and summarize), JOIN (join tables), HAVING (post-group filter). AI is very helpful in writing complex JOINs and window functions, but be sure to check two things: is the JOIN via the correct key (the wrong key duplicates rows) and is the filter logic correct (especially NULL behavior and date ranges).
Caution: Do not run an AI-generated SQL query directly against the production database. Test with a small copy or LIMIT first. Never run an UPDATE/DELETE query without validating the WHERE condition; A wrong WHERE can delete the entire table.
Python/pandas: flexible analysis
pandas is the standard way to manipulate tables (DataFrame) in Python. The most efficient use of AI is to give it a clear scheme and purpose. Most commonly used operations: filter, groupby, merge, pivot_table, apply. AI writes these quickly; What you want to check is the logic: is the grouping in the correct column, has the merge changed the number of rows unexpectedly (always check the number of rows after the merge), are the chain operations changing the original.
transaction
SQL
pandas
checkpoint
Filtering
WHERE
df[df.x > 5]
NULL/NaN behavior
grouping
GROUP BY
df.groupby()
Is it the right column?
merge
JOIN
df.merge()
Row count change
Summary
AVG(), SUM()
.mean(), .sum()
Which column was collected
Sort by
ORDER BY
.sort_values()
Direction (ascending/descending)
deduplication
DISTINCT
.drop_duplicates()
In which columns?
Debugging: with AI
When code fails, AI is an excellent debugging partner. Give it the full error message and the relevant code snippet. But beware of two traps. First, the AI may suggest a solution that “silences” the error (e.g., hiding alerts) — this does not fix the error, it hides it. Second, AI sometimes silently changes another behavior while “solving” a problem. Rule: understand the fix, resolve not mute, and verify that the output is still correct after the fix.
Want interpretable and maintainable code
When buying code from AI, ask for code that is readable and maintainable, not just code that "works". When you or a colleague opens that code months later, it should be able to understand what it does. To do this, make it a habit to have the AI include three things: meaningful variable names (orders_temiz, not df2), short comment lines at critical steps (explaining why, not what is being done), and a named constant instead of a magic number (ACCEPT_ESIGI = 0.85 instead of 0.85 buried in the code). Also avoid long single-line chains (connecting five actions in one line); these make debugging difficult. By default, AI often produces concise and “smart” code; If you clearly say "write readable, interpretable, maintainable", you will get a much more maintainable output. This is also the basis of reproducibility (Unit 10): code that is not understood is code that cannot be re-run safely.
three mini cases
Case 1 — JOIN replication. An analyst combined the orders with the product table and found the total turnover to be 3 times higher. Cause: each product had multiple rows (different colors) in the product table; JOIN duplicated each order. The AI's code was "working", but the number of lines had jumped from 240 thousand to 690 thousand. Lesson: always check the number of lines after merge/JOIN.
Case 2 — Fitting function. He suggested AI df.groupby('x').summarize() to an intern; There is no such method in pandas (there is .agg()). The code didn't work, the intern was lost for 20 minutes. Lesson: verify a function you don't recognize from the doc; AI can make up methods.
Case 3 — Yield collapse. One code was querying the database in apply for each row; It ran on 5,000 lines, took 9 hours on 4 million lines, and stopped. When AI suggested a vectorized (batch) solution, the time was reduced to 40 seconds. Lesson: code that works on small data may crash on big data; Consider efficiency.
Four copyable templates
1) Requesting SQL with schema:
Your role: SQL assistant (PostgreSQL). Tables:- orders(id, customer_id, date timestamp, amount numeric)- customers(id, city text)Task: Get the total turnover and number of orders per city in 2024, sorted by turnover in descending order. Explain how you handle NULL cities. I will test the query with LIMIT first; UPDATE/DELETE generation.
2) pandas process with checkpoint:
I have DataFrames df (orders) and df_customers (customers). Calculate average amount per city. IMPORTANT: print the number of rows before and after merge so I can see if there is duplication. Explain in which column you merged and why you chose inner/left.
3) Code explanation and verification:
Explain the following pandas code line by line: what does each line do, what assumptions does it make, in what cases could it give wrong results? Let me know if I used a fudge function. Code: [paste]
4) Debugging:
This code gives this error. Full error message: [paste]. Code: [paste]. Explain the ROOT cause of the error and fix it. Fix it by actually solving the problem, not by silencing the alert. Also indicate whether the fix changed the output.
Weak prompt / Strong prompt
Weak prompt:
Write a query that gives me sales per city.
Table names, columns, database type, NULL behavior are unclear. The AI is common, it will probably produce a query that doesn't fit your table.
Powerful prompt:
Your role: SQL assistant (MySQL 8). Table: sales(id, city varchar, amount decimal, date date). Task: Get the total and average amount, number of orders per city for the year 2024; Sort decreasing by total amount; Show only cities with more than 100 orders (HAVING).NULL exclude city. Explain the query; I will test with LIMIT.
Here database, schema, filter, sorting and NULL rule are obvious.
Common mistakes
- Running the code without reading it. Working code is not correct code; The code that manipulates the wrong column also runs without errors.
- Not checking the number of rows after merge/JOIN. The wrong key silently duplicates rows and inflates totals.
- Not verifying the fitting function. AI may suggest methods that do not exist; Confirm from the document that you do not recognize it.
- Not thinking about efficiency. apply/loop working on small data crashes on millions of rows; vectorize.
- Run directly on production database. Especially running UPDATE/DELETE without WHERE or testing is disastrous.
Tip: Get in the habit of adding a "validation line" to every piece of code you receive from the AI: a pre- and post-processing number of lines, a few sample lines, and a critical total by hand. These three checks catch most silent logic errors.
In summary
AI is a powerful partner that quickly produces SQL and pandas code, but it is not a blind authority. Give him the scheme and purpose clearly; Read the code it produces as if you wrote it yourself; Check number of rows, fitting functions and throughput after merge/JOIN; Do not run it without testing it on the production database. When debugging, aim to solve the problem, not silence it. The code that works is not the correct code; Only you can guarantee accuracy.
Application task
Choose an analysis question (e.g. “monthly turnover per channel”) and request code from the AI with both SQL and pandas. Read both code line by line, check the number of lines after merge/JOIN and manually verify at least one critical sum. Compare whether the two codes produce the same result; If different, find out why.
checklist
- [ ] Have I given the table/schema and purpose clearly to the AI?
- [ ] Did I read and understand the code it produced line by line?
- [ ] Did I check the number of lines after merge/JOIN?
- [ ] Have I verified the functions I don't recognize from the documentation?
- [ ] Have I tested the code on safe/small data first and not in the production environment?