01 · Business Problem
Pipelines fail at the edges. The edges you forgot to test.
Most ETL tutorials show the happy path. Real pipelines fail when HTML structure changes mid-scrape, a product has a null price, or the database connection drops after 40 successful writes.
Website
↓
Extract
↓
Broken HTML
↓
Pipeline Crash
↓
No Data
Website
↓
Extract
↓
Broken Card
↓
Return None · Skip
↓
Continue
↓
47 Good Pages
PythonBeautifulSouppandas
PostgreSQLSQLAlchemyGoogle Sheets API
pytestMagicMock
02 · Architecture
Three independently testable layers. One clean pipeline.
Each layer has one responsibility and one owner. Extract never calls transform. Load never calls extract. Each can be swapped, tested, and debugged in isolation.
Fashion Website
│
requests + BeautifulSoup
│
┌───────────────▼────────────────┐
│ Extract Layer · utils/extract.py │
└───────────────┬────────────────┘
│
pandas DataFrame
│
┌───────────────▼────────────────┐
│ Transform Layer · utils/transform.py │
│ Validation · Cleaning │
│ Currency Conversion · Dedup │
└───────────────┬────────────────┘
│
Clean DataFrame
│
┌─────────────────┼─────────────────┐
▼ ▼ ▼
CSV File PostgreSQL Google Sheets
Extract — fail-safe per card
Each product card is parsed by extract_product(). The function wraps all field access in try/except and returns None on any error. A single broken card never crashes the scrape loop — extract_data() skips None results with continue.
Load — three destinations, one contract
save_csv(), save_postgres(), and save_gsheet() each follow the same contract: check empty → write → catch exceptions. Each is independently callable. The pipeline doesn't fail completely if one destination is unavailable.
04 · Testing Architecture
No live database. No real HTTP. Millisecond-fast deterministic tests.
The test suite uses MagicMock throughout — every external dependency is replaced with a controlled fake. Tests run identically in CI, on a plane, and at 2am when the database is unreachable.
test_extract.py
↓
MagicMock
Fake HTML
No Internet
test_transform.py
↓
Real DataFrame
Mixed/empty/invalid
input data
test_load.py
↓
MagicMock
Fake DB + Fake Google API
No PostgreSQL
Fast · Deterministic · No Live Dependencies
"A test that requires a live database connection is a test that fails at 2am when the database is unreachable."
How MagicMock replaces real systems
Test
↓
Real Website
↓
Real Database
↓
Real Google API
↓
Slow · Unstable
Test
↓
Fake HTML
↓
Fake DB
↓
Fake Google API
↓
Milliseconds · Deterministic
06 · Data Validation Flow
Explicit validation chain. No silent drops.
Raw Data
↓
↓ No
↓ No
↓ No
↓ Yes
Convert USD → IDR
↓
Clean Dataset
Why this order?
Each filter is ordered from cheapest to most expensive to compute. Null checks run before string operations. String operations run before numeric casting. This prevents type errors from propagating downstream.
Key snippet
# Reject "Price Unavailable" etc.
df = df[df["price"].str.contains(
r"\d", regex=True
)]
# Type-safe numeric conversion
df["rating"] = (
df["rating"]
.str.extract(r"([\d.]+)")[0]
.astype(float)
)
df["colors"] = (
df["colors"]
.str.extract(r"(\d+)")[0]
.astype(int)
)
07 · Design Decisions
Every choice was deliberate.
| Problem |
Solution |
Benefit |
| Broken HTML card crashes pipeline |
try/except → return None |
Pipeline keeps running |
| Hardcoded exchange rate |
EXCHANGE_RATE_USD_TO_IDR env var |
Configurable without code change |
| Google API hard to test |
Dependency injection on save_gsheet(service=...) |
Directly mockable without credentials |
Monolithic save_all() function |
Split into save_csv, save_postgres, save_gsheet |
Each independently testable and deployable |
| Silent data drops in transform |
Explicit ordered filter chain with logging |
Every drop is traceable |
Dependency Injection in practice
The injectable service parameter in save_gsheet() wasn't planned — it emerged because the function was hard to test without credentials. The need to test revealed a design problem, and the fix made the code better.
Without DI — untestable
def save_gsheet(df):
creds = load_credentials() # ← always runs
service = build('sheets',
'v4',
credentials=creds)
# needs real creds to test
With DI — testable
def save_gsheet(df,
service=None):
if service is None:
creds = load_credentials()
service = build(...)
# test passes service=MagicMock()
10 · Lessons Learned
QA isn't a safety net. It's a design discipline.
Writing tests for this pipeline changed how I wrote the pipeline itself. Functions that are hard to test are usually hard to maintain.
Testability shapes design
When you write the test first, you catch structural problems early. Functions with too many dependencies are hard to mock — and that difficulty is the design feedback.
MagicMock for external APIs
The Google Sheets API chain (spreadsheets().values().clear().execute()) looks daunting to mock. Chained MagicMock calls just work — each returns another Mock by default.
Coverage is a floor, not a goal
100% branch coverage doesn't mean the pipeline is correct. It means every code path has been exercised at least once. The actual data quality contracts live in the assertions.
Modular load > monolithic save
Splitting into save_csv, save_postgres, save_gsheet made each independently testable. A monolithic save_all() would have been a test nightmare.