Change-aware loads, not repeated rewrites.
Set-based SQL compares business attributes before updating. Normalizing pandas/NumPy values into suitable Python/SQL parameters avoids mismatched nulls and types at the loading boundary.
01 / Reliable dataCompleted capstone
Olist.
A reliable analytical foundation for a messy public e-commerce dataset.
MY ROLE
Individual internship capstone · Atos
PROJECT CONTEXT
Atos internship · Jul–Sep 2026
CORE STACK
Python / SQL Server / ETL / Power BI
PRODUCT HISTORY / SCD TYPE 201
Raw input→Validate→Trusted
PRODUCT / P-001VERSIONSTATE
Home · 500g01Historical
Office · 650g02Current ↗
Attributes change. The history stays.
Illustrative recordsA dashboard is only as useful as its inputs.
The public Brazilian Olist dataset spans orders, products, payments, reviews, and locations. Those files need consistent types, trustworthy relationships, and traceable transformations before they can support useful reporting.
I built this individual data engineering capstone during my Atos internship. It processes CSV files and locally exposed paginated API feeds into SQL Server analytical tables, product history, and reporting views.
The geolocation source alone contains 1,000,163 raw rows, including 261,831 duplicates in the inspected report. That scale describes input records, not unique customers or trusted output rows.
From ingestion to reporting.
I implemented the capstone’s entity pipelines, SQL loading logic, product history, API consumer, scheduler, audit/data-quality persistence, tests, and a populated four-page Power BI report.
- Separate staging, trusted, data-quality, and audit concerns, with run identifiers and source-row lineage.
- Load related entities in dependency order and normalize Python values before binding SQL parameters.
- Track product changes with SCD Type 2, plus snapshot soft deletion and reactivation under a complete-snapshot assumption.
- Expose analytical views and consider fact grain before building report joins.
Keep the path to a number visible.
- 01CSV files + local paginated APIs
- 02Raw staging + source lineage
- 03Validation + transformation
- 04SQL Server tables + product history
- 05Analytical views → Power BI
The API feeds serve supplied payments/reviews data locally. They demonstrate pagination and ingestion behavior; they are not a live commerce-provider integration.
Reliability lives in the details.
History is part of the model.
When product attributes change, the old version expires and a new current version is created. Missing products may be soft-deleted only because master-entity inputs are treated as complete snapshots; partial feeds would need a different policy.
A failed load should leave an explanation.
Each pipeline has a business transaction, while audit and data-quality logging use separate persistence. A rollback can therefore preserve the diagnostic record explaining the failure.
Bound the integrations.
API ingestion uses pagination, timeouts, and bounded retries. Scheduling controls are process-local; they are not a distributed scheduling guarantee.
Respect the analytical grain.
Order items and payments can each contain multiple rows per order. Joining both directly can multiply facts. Reporting requires an explicit grain and suitable aggregation before combining those relationships.
What the project demonstrates.
The source and saved artifacts support implemented pipelines, history tracking, transaction handling, and a four-page Power BI report. Existing tests cover change detection, parameter normalization, API behavior, scheduling, and transaction failures.
The portfolio review did not rerun SQL Server pipelines, refresh Power BI, or execute the original test suite. Historical reports and the CV contain different test counts, so no exact passing count is published here. Numerical speedup claims are also omitted because their benchmark context was not reproduced.
This is internship/capstone work using a public dataset. It is not a production customer deployment, and dataset sales totals are not commercial revenue I generated.
A product changes. Its past doesn’t.
Select a snapshot to see why unchanged inputs preserve the same version and changed business attributes create history. The table remains available as a text equivalent.
Explore / product historySynthetic records
Illustrative example — no live database connection. Product P-001, with three prepared snapshots.
Incoming attributesHome · 500g
| Version | Category | Weight | Valid from | Valid until | State |
|---|---|---|---|---|---|
| 01 | Home | 500g | 2026-01-01 | Open | Current |
The first snapshot creates one current product version.
Project links
The source repository is private. This public case study explains the implementation without exposing the project’s source code.
Let’s start with
a good problem.
Questions about the work, an engineering opportunity, or an idea to explore? I’d like to hear it.
Get in touch