SQL + Python Data Engineering Lab
An interactive SQL, Python, Pandas, ETL and data quality lab demonstrating how disorganized datasets become reliable analytical assets.
The simulated dataset carries duplicates, missing values, inconsistent formats and invalid dates. The lab uses SQL, Python and Pandas to investigate, clean and validate them. It then exports an analysis-ready dataset.
All data is fictional and deterministic. No sensitive fiscal, personal or institutional information is used.
The queries and pipelines shown are safe technical demonstrations. Results are computed from a simulated dataset using deterministic functions equivalent to the SQL and Pandas operations displayed.
This is how data arrives: fields as text, mixed formats and real extraction defects. The first rows below already contain some of the problems the audit detects.
| id | competence_date | revenue_name | category | estimated_value | collected_value |
|---|---|---|---|---|---|
| R-2025-1101-01 | 2025-01-01 | ISS | tributaria | 3780 | 3667 |
| R-2025-1101-02 | 2025-02-01 | ISS | tributaria | 3636 | 3636 |
| R-2025-1101-03 | 2025-03-01 | ISS | tributaria | 3492 | 3597 |
| R-2025-1101-03 | 2025-04-01 | ISS | tributaria | 3348 | 3549 |
| R-2025-1101-05 | 2025-05-01 | ISS | tributaria | 3816 | 3587 |
| R-2025-1101-06 | 2025-13-45 | ISS | tributaria | 3672 | 3562 |
Ten preset queries cover the investigation cycle: trend, gap, duplicate/missing/validity audits, ranking, running total, outliers and the final BI-ready dataset.
Purpose: Group the simulated revenue by competence month and spot the trend.
SELECT
strftime('%Y-%m', competence_date) AS competence_month,
SUM(collected_value) AS collected_revenue
FROM revenue_clean
GROUP BY competence_month
ORDER BY competence_month;Pandas pipeline equivalent
df.groupby(df["competence_date"].dt.to_period("M"))["collected_value"].sum()| competence_month | collected_revenue |
|---|---|
| 2025-01 | 7,211 |
| 2025-02 | 12,211 |
| 2025-03 | 11,157 |
| 2025-04 | 7,754.56 |
| 2025-05 | 11,495 |
| 2025-06 | 7,189 |
| 2025-07 | 10,569 |
| 2025-08 | 50,529 |
Executive reading: The monthly view is the base of executive tracking: consecutive drops raise alerts.
The queries and pipelines shown are safe technical demonstrations. Results are computed from a simulated dataset using deterministic functions equivalent to the SQL and Pandas operations displayed.
Each step below shows the problem, the conceptual Pandas code and — on execution — how many records are actually affected in the simulated dataset.
Problem: Columns with spaces, mixed casing and divergent names break downstream scripts.
import pandas as pd
df = pd.read_csv("revenue_raw.csv")
df.columns = (
df.columns
.str.strip()
.str.lower()
.str.replace(" ", "_")
)Problem: Mixed date formats and text-encoded numbers (1.234,56) prevent aggregation.
df["competence_date"] = pd.to_datetime(
df["competence_date"],
errors="coerce",
dayfirst=True,
)
df["collected_value"] = pd.to_numeric(
df["collected_value"]
.str.replace(".", "", regex=False)
.str.replace(",", ".", regex=False),
errors="coerce",
)Problem: " iptu ", "Iss" and "TRIBUTARIA" are the same thing written three ways.
df["revenue_name"] = df["revenue_name"].str.strip().str.upper()
df["category"] = (
df["category"]
.str.strip()
.str.lower()
.str.normalize("NFKD")
)Problem: Missing values and repeated rows inflate sums and distort metrics.
df = df.drop_duplicates()
df = df.drop_duplicates(subset="id", keep="first")
df["category"] = df["category"].fillna(
df["revenue_code"].map(code_to_category)
)
df = df.dropna(subset=["competence_date", "collected_value"])Problem: Negative values, orphan codes and outliers need explicit decisions, not silence.
valid = df["collected_value"] >= 0
known = df["revenue_code"].isin(dim_revenue["code"])
rejected = df[~(valid & known)]
df = df[valid & known]
df["is_outlier"] = (
df["collected_value"] > df["estimated_value"] * 5
)Problem: The end goal is a stable dataset that Power BI consumes without surprises.
monthly = (
df.groupby([df["competence_date"].dt.to_period("M"), "revenue_name"])
.agg(collected=("collected_value", "sum"))
.reset_index()
)
df.to_csv("revenue_clean.csv", index=False)Run the pipeline to audit the dataset, apply the deterministic transformations, compare before/after and unlock the cleaned dataset export.
Run the pipeline to see the log, the metrics and the before/after comparison.
From extraction to decision: the same data goes through investigation, cleansing, auditing and publication — ending in the Power BI case's executive dashboard.
The dataset cleaned in this lab shares the same domain as the Power BI case — fiscal KPIs, targets and municipal revenue. See the data applied in the executive dashboard.
SQL
Python
Pandas
ETL
Power BI
Next.js
TypeScript
Vercel