SQL
Warehouse filtering, joins, aggregation and reproducible governed metrics.
Analysis · Module 08
Python for Analysts.
Use Python where it gives you analytical leverage. Do not rewrite a perfectly good warehouse query in pandas just because Python looks more impressive.
Core idea
Python is not a badge of seniority. It is useful when it makes the analysis easier to reason about, reproduce or extend.
01
Context
The SQL result is correct. The investigation is getting messy.
“We have the clean order table. Now we need to inspect distributions, compare dozens of segments, test different thresholds and document the exploration.”
You could force all of this into one giant SQL script. But once the task becomes iterative exploration rather than stable warehouse aggregation, Python can be a better analytical workspace.
02
Tool choice
Use the simplest tool that fits the job.
Python
Flexible cleaning, exploratory analysis, statistics and reusable analytical workflows.
Spreadsheet
Fast checks, small reconciliations and stakeholder-friendly ad hoc work.
BI tool
Repeatable monitoring, interactive slicing and decision-facing communication.
Rule
A strong analyst is not the person who uses the most advanced tool. It is the person who chooses the lowest-complexity workflow that stays trustworthy.
03
The pandas workflow
Keep the notebook boring and inspectable.
Read a clean extract or query result into a DataFrame.
Check shape, dtypes, missing values, duplicates and basic ranges.
Create explicit derived columns without hiding business logic.
Group, compare, rank and inspect distributions or anomalies.
Reconcile important totals back to the source query.
Export only the tables or visuals needed for the decision.
04
Inspect first
Never start with the fancy analysis.
First 60 seconds with a DataFrame
import pandas as pd
orders = pd.read_csv("orders.csv", parse_dates=["created_at", "completed_at"])
print(orders.shape)
print(orders.dtypes)
print(orders.isna().mean().sort_values(ascending=False).head(10))
print(orders["order_id"].duplicated().sum())
print(orders["status"].value_counts(dropna=False))
print(orders["gross_value"].describe())This is not glamorous, but it gives you immediate information about scale, types, missingness, uniqueness, categories and suspicious numeric ranges.
05
Business logic
Make derived metrics explicit.
Create transparent analytical columns
orders["delivery_minutes"] = (
orders["completed_at"] - orders["created_at"]
).dt.total_seconds() / 60
orders["is_late"] = orders["delivery_minutes"] > 45
orders["order_value_band"] = pd.cut(
orders["gross_value"],
bins=[0, 15, 30, 60, float("inf")],
labels=["0–15", "15–30", "30–60", "60+"]
) Definition check
Before using is_late, confirm that 45 minutes is the agreed SLA and that completed_at is the correct endpoint for the business definition.
06
Exploration
Move quickly without losing the grain.
Compare late-delivery rate by segment
segment_summary = (
orders
.groupby(["city", "customer_type"], dropna=False)
.agg(
orders=("order_id", "nunique"),
median_delivery=("delivery_minutes", "median"),
p90_delivery=("delivery_minutes", lambda s: s.quantile(0.90)),
late_rate=("is_late", "mean")
)
.reset_index()
.sort_values(["late_rate", "orders"], ascending=[False, False])
)
print(segment_summary.head(20))Notice the analysis uses median and p90 alongside late rate. That connects directly to the previous statistics module: the tail of the distribution may matter more than the average.
07
Merges
pandas can duplicate your data too.
Use merge validation when you know the relationship
orders = orders.merge(
cities[["city_id", "city_name", "country"]],
on="city_id",
how="left",
validate="many_to_one"
)Distinct order rows.
Expected if city_id is unique.
Make pandas fail loudly when the relationship is not what you expected.
08
Challenge
Turn a messy notebook into an analyst workflow.
Exercise · 20 minutes
Make it reproducible.
- Which calculations should stay in SQL before the data reaches Python?
- What checks should run immediately after loading the extract?
- Which derived fields need business definitions documented?
- How would you check that a pandas merge did not duplicate orders?
- Which output should be exported for a dashboard instead of recreating the dashboard in Python?
- What notebook cells could become reusable functions?
Reveal one clean notebook structure
01Context
Business question, metric definitions and data source.
02Load & QA
Shape, types, nulls, keys, freshness and controls.
03Transform
Explicit, documented analytical columns.
04Explore
Segments, distributions, rankings and hypotheses.
05Validate
Reconcile important numbers to the source.
06Output
Decision-ready tables, visuals and conclusions.
09
AI assist
Use AI to accelerate code, not bypass review.
Python analysis prompt
I have an order-level pandas DataFrame called orders.
Known grain:
one row per order
Important columns:
order_id, customer_id, city_id, created_at, completed_at,
status, gross_value, customer_type
Task:
Investigate what segments are associated with delivery times above the 45-minute SLA.
Before writing code:
1. Restate the grain and SLA definition.
2. List the QA checks you would run first.
3. Identify useful distribution statistics beyond the mean.
4. Write readable pandas code in small steps.
5. Add validation checks after any merge.
6. Do not invent columns or conclusions that are not in the data. 10
Validation
The Python analyst checklist.
01
Can this task be done more simply and safely in SQL?
02
Did I inspect shape, data types, nulls and duplicates before analysis?
03
Are derived columns based on explicit business definitions?
04
Did a merge change the expected number of business entities?
05
Can I reproduce important totals with a control query?
06
Did I avoid silently dropping nulls or outliers?
07
Is the notebook readable from top to bottom without hidden state?
08
Could someone else rerun this analysis and get the same result?
11
Analysis stage complete
You now have more than a tool stack.
Prove the data deserves trust.
Make the important comparison easy to see.
Calibrate uncertainty and avoid weak inference.
Use Python when flexible analysis adds leverage.
Stage 02 takeaway
The analyst workflow is now complete from trustworthy data to decision-ready evidence. Next, the course changes gear: instead of treating AI as a helper inside individual lessons, we will learn how to make AI part of the analytical system itself.