mirror of
https://github.com/hanzoai/skills.git
synced 2026-11-19 00:47:36 +00:00
Every page now loads /webmcp.js, scripts/webmcp.ts with its types stripped (npm run webmcp). Where the browser has document.modelContext it registers two read-only tools: search_skills over the published skills index, and get_skill, which returns a skill's markdown checked against the index digest. The markdown alternate link moves from build.py to site.py, so CI holds every page to it. The API catalog gains the site itself (llms.txt and the skills index) and hanzo.ai/agents; the live discovery check now fetches every catalog link. tests/site.mjs drives the tools through a recording modelContext, and checks a browser without one loads clean.
673 lines
29 KiB
HTML
673 lines
29 KiB
HTML
<!doctype html>
|
|
<html lang="en"><head>
|
|
<meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1">
|
|
<title>data-etl-patterns</title>
|
|
<meta name="color-scheme" content="dark">
|
|
<style>
|
|
/* Committed to one visual world: a dark instrument panel. No light variant —
|
|
the dot field and the hairline rules only read on black, and a naive
|
|
inversion would wash both out. Every colour is painted explicitly so the page
|
|
never borrows a host background. */
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen';src:url('/fonts/Zen-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Variable.woff2') format('woff2');font-weight:100 900;font-style:normal;font-display:swap}
|
|
@font-face{font-family:'Zen Mono';src:url('/fonts/ZenMono-Italic-Variable.woff2') format('woff2');font-weight:100 900;font-style:italic;font-display:swap}
|
|
@font-face{font-family:'Zen Pixel Square';src:url('/fonts/ZenPixel-Square.woff2') format('woff2');font-style:normal;font-display:swap}
|
|
|
|
:root{
|
|
--ink:#f4f4f2; --soft:#9c9c98; --faint:#6a6a66; --ground:#000; --panel:#0a0a0a;
|
|
--line:#232323; --line-hi:#3a3a3a; --accent:#f4f4f2;
|
|
--font-zen-sans:'Zen',-apple-system,BlinkMacSystemFont,"Segoe UI",Helvetica,Arial,sans-serif;
|
|
--font-zen-mono:'Zen Mono',ui-monospace,"SF Mono",SFMono-Regular,Menlo,Consolas,monospace;
|
|
--font-zen-pixel-square:'Zen Pixel Square',monospace;
|
|
--mono:var(--font-zen-mono);
|
|
--sans:var(--font-zen-sans);
|
|
/* Controls are pills and surfaces are soft, as on hanzo.ai. */
|
|
--r:999px; --r-soft:14px;
|
|
}
|
|
*{box-sizing:border-box}
|
|
html{-webkit-text-size-adjust:100%}
|
|
body{margin:0;background:var(--ground);color:var(--ink);font-family:var(--sans);
|
|
font-size:16px;line-height:1.65;-webkit-font-smoothing:antialiased}
|
|
.wrap{max-width:60rem;margin:0 auto;padding:0 1.75rem}
|
|
|
|
/* The dot field. A halftone made of one repeating radial-gradient, faded out
|
|
along the diagonal by a mask so it reads as a wave rather than wallpaper. */
|
|
/* No overflow clip here: the dot field is inset to the box already, and a clip
|
|
would cut off the copy-prompt menu. */
|
|
.hero{position:relative;border-bottom:1px solid var(--line)}
|
|
.hero::before{content:"";position:absolute;inset:0;
|
|
background-image:radial-gradient(var(--line-hi) 1px, transparent 1px);
|
|
background-size:7px 7px;
|
|
-webkit-mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
mask-image:linear-gradient(115deg,#000 0%,rgba(0,0,0,.55) 38%,transparent 72%);
|
|
pointer-events:none}
|
|
.hero .wrap{position:relative;padding-top:5.5rem;padding-bottom:4rem}
|
|
|
|
nav{border-bottom:1px solid var(--line)}
|
|
nav .in{max-width:60rem;margin:0 auto;padding:1.1rem 1.75rem;display:flex;
|
|
justify-content:space-between;align-items:center;gap:1rem}
|
|
.brand{font-family:var(--mono);font-size:.85rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--ink);text-decoration:none;
|
|
display:inline-flex;align-items:center;gap:.6rem}
|
|
.brand i{font-style:normal;color:var(--faint)}
|
|
.mark{width:1.15em;height:1.15em;display:block;flex:0 0 auto}
|
|
nav a.up{font-family:var(--mono);font-size:.72rem;letter-spacing:.14em;
|
|
text-transform:uppercase;color:var(--soft);text-decoration:none}
|
|
nav a.up:hover{color:var(--ink)}
|
|
|
|
h1{font-family:var(--mono);font-size:clamp(2rem,5.4vw,3.4rem);line-height:1.12;
|
|
letter-spacing:-.02em;margin:0 0 1.1rem;font-weight:500;text-wrap:balance}
|
|
h1 em{font-style:normal;color:var(--faint)}
|
|
.lede{color:var(--soft);font-size:1.05rem;max-width:52ch;margin:0 0 2.25rem}
|
|
|
|
.btns{display:flex;flex-wrap:wrap;gap:.7rem;margin:0 0 .5rem;align-items:center}
|
|
.btn{display:inline-flex;align-items:center;gap:.5rem;font-family:var(--mono);
|
|
font-size:.72rem;letter-spacing:.14em;text-transform:uppercase;
|
|
padding:.8rem 1.4rem;border:1px solid var(--line-hi);border-radius:var(--r);
|
|
color:var(--ink);text-decoration:none;background:transparent;cursor:pointer;
|
|
line-height:1.2;font-weight:400}
|
|
.btn:hover{background:#141414;text-decoration:none;border-color:var(--soft)}
|
|
.btn.solid{background:var(--ink);color:#000;border-color:var(--ink)}
|
|
.btn.solid:hover{background:#d8d8d4}
|
|
.btn .chev{width:.7em;height:.7em;flex:0 0 auto;transition:transform .15s ease}
|
|
.btn[aria-expanded="true"] .chev{transform:rotate(180deg)}
|
|
|
|
/* Copy-prompt selector. Each row copies the prompt for its own target. */
|
|
.copy{position:relative;display:inline-flex}
|
|
.menu{position:absolute;top:calc(100% + .5rem);left:0;z-index:20;min-width:21rem;
|
|
background:var(--panel);border:1px solid var(--line-hi);border-radius:var(--r-soft);
|
|
padding:.4rem;display:flex;flex-direction:column;
|
|
box-shadow:0 12px 32px rgba(0,0,0,.75)}
|
|
.menu[hidden]{display:none}
|
|
.mi{display:flex;align-items:center;gap:.7rem;width:100%;text-align:left;
|
|
padding:.62rem .8rem;border:0;border-radius:calc(var(--r-soft) - .35rem);
|
|
background:transparent;color:var(--ink);font-family:var(--mono);font-size:.76rem;
|
|
letter-spacing:.04em;text-decoration:none;cursor:pointer;line-height:1.3;
|
|
white-space:nowrap}
|
|
.mi:hover,.mi:focus-visible{background:#181818;outline:none}
|
|
.mi .mark{width:1.05rem;height:1.05rem;color:var(--soft)}
|
|
.mi:hover .mark{color:var(--ink)}
|
|
.mi u{margin-left:auto;color:var(--faint);text-decoration:none;font-size:.68rem;
|
|
letter-spacing:.1em;text-transform:uppercase}
|
|
|
|
/* Works-with bar: one scrolling row, name and mark per agent. */
|
|
.strip{border-bottom:1px solid var(--line);background:var(--panel)}
|
|
.strip .in{max-width:60rem;margin:0 auto;padding:.9rem 1.75rem;display:flex;
|
|
align-items:center;gap:1.25rem;font-family:var(--mono);font-size:.72rem;
|
|
letter-spacing:.1em;text-transform:uppercase;color:var(--faint)}
|
|
.strip b{color:var(--soft);font-weight:400;flex:0 0 auto;
|
|
border-right:1px solid var(--line);padding-right:1.25rem;align-self:stretch;
|
|
display:flex;align-items:center}
|
|
.bar{display:flex;align-items:center;gap:.5rem;flex:1 1 auto;min-width:0;
|
|
overflow-x:auto;scrollbar-width:none;scroll-snap-type:x proximity;
|
|
-webkit-overflow-scrolling:touch;
|
|
-webkit-mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent);
|
|
mask-image:linear-gradient(90deg,#000 calc(100% - 3rem),transparent)}
|
|
.bar::-webkit-scrollbar{display:none}
|
|
.agent{display:inline-flex;align-items:center;gap:.45rem;flex:0 0 auto;
|
|
padding:.4rem .85rem;border:1px solid var(--line);border-radius:var(--r);
|
|
color:var(--soft);white-space:nowrap;scroll-snap-align:start;
|
|
text-decoration:none;background:var(--ground)}
|
|
.agent:hover{color:var(--ink);border-color:var(--line-hi);text-decoration:none}
|
|
.agent .mark{width:.95rem;height:.95rem}
|
|
|
|
h2{font-family:var(--mono);font-size:1.1rem;letter-spacing:-.01em;margin:2.75rem 0 .75rem;font-weight:500}
|
|
h3{font-size:1rem;margin:2rem 0 .5rem;font-weight:600}
|
|
h4,h5,h6{font-size:.94rem;margin:1.5rem 0 .5rem;font-weight:600}
|
|
p{margin:0 0 1rem;max-width:70ch}
|
|
ul,ol{margin:0 0 1rem;padding-left:1.35rem;max-width:70ch}
|
|
li{margin:.22rem 0}
|
|
a{color:var(--ink);text-decoration:underline;text-underline-offset:3px;
|
|
text-decoration-color:var(--line-hi)}
|
|
a:hover{text-decoration-color:var(--ink)}
|
|
a:focus-visible{outline:1px solid var(--ink);outline-offset:3px}
|
|
code{font-family:var(--mono);font-size:.85em;background:var(--panel);
|
|
border:1px solid var(--line);border-radius:5px;padding:.1em .36em}
|
|
pre{background:var(--panel);border:1px solid var(--line);padding:1rem 1.1rem;
|
|
border-radius:var(--r-soft);overflow-x:auto;margin:0 0 1.25rem}
|
|
pre code{background:none;border:0;padding:0;font-size:.82rem;line-height:1.7}
|
|
blockquote{margin:0 0 1rem;padding-left:1rem;border-left:1px solid var(--line-hi);color:var(--soft)}
|
|
hr{border:0;border-top:1px solid var(--line);margin:2.5rem 0}
|
|
table{border-collapse:collapse;width:100%;margin:0 0 1.25rem;font-size:.92rem;
|
|
display:block;overflow-x:auto}
|
|
th,td{border:1px solid var(--line);padding:.5rem .7rem;text-align:left}
|
|
|
|
.curl{display:block;background:var(--panel);border:1px solid var(--line);
|
|
border-radius:var(--r-soft);padding:.95rem 1.1rem;font-family:var(--mono);
|
|
font-size:.8rem;color:var(--soft);overflow-x:auto;margin:0 0 2.5rem;
|
|
white-space:pre;line-height:1.9}
|
|
.curl b{color:var(--ink);font-weight:400}
|
|
.curl i{font-style:normal;color:var(--faint)}
|
|
|
|
/* index */
|
|
#q{width:100%;padding:.85rem 1.2rem;font-family:var(--mono);font-size:.85rem;color:var(--ink);
|
|
background:var(--panel);border:1px solid var(--line);border-radius:var(--r);margin:0 0 .6rem}
|
|
#q::placeholder{color:var(--faint);text-transform:uppercase;letter-spacing:.1em;font-size:.72rem}
|
|
#q:focus{outline:none;border-color:var(--soft)}
|
|
#count{color:var(--faint);font-family:var(--mono);font-size:.72rem;letter-spacing:.1em;
|
|
text-transform:uppercase;margin:0 0 2rem}
|
|
.cat{margin:2.5rem 0 .5rem;font-family:var(--mono);font-size:.7rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);border-bottom:1px solid var(--line);
|
|
padding-bottom:.45rem}
|
|
.row{display:block;padding:.6rem .9rem;margin:0 -.9rem;border-bottom:1px solid var(--line);
|
|
border-radius:var(--r-soft);text-decoration:none}
|
|
.row:hover{background:#0a0a0a}
|
|
.nm{font-family:var(--mono);font-size:.84rem;color:var(--ink);display:block}
|
|
.ds{color:var(--soft);font-size:.9rem;display:block;margin-top:.12rem}
|
|
footer{margin-top:4rem;padding:2rem 0 4rem;border-top:1px solid var(--line);
|
|
color:var(--faint);font-size:.85rem}
|
|
footer a{color:var(--soft)}
|
|
.fmap{display:grid;gap:1.75rem;grid-template-columns:repeat(auto-fit,minmax(11rem,1fr));
|
|
margin:0 0 2rem;font-size:.85rem}
|
|
.fmap b{display:block;font-family:var(--mono);font-size:.68rem;letter-spacing:.16em;
|
|
text-transform:uppercase;color:var(--faint);font-weight:400;margin:0 0 .6rem;
|
|
border-bottom:1px solid var(--line);padding-bottom:.4rem}
|
|
.fmap a{display:inline-block;color:var(--ink);text-decoration:none;margin:.3rem .35rem 0 0}
|
|
.fmap a:hover{text-decoration:underline}
|
|
.fmap div{color:var(--faint);line-height:1.5}
|
|
.fend{border-top:1px solid var(--line);padding-top:1.25rem;font-size:.82rem}
|
|
|
|
@media (max-width:640px){
|
|
.hero .wrap{padding-top:3.25rem;padding-bottom:2.75rem}
|
|
.btns{gap:.5rem}
|
|
.btn{padding:.75rem 1.15rem;font-size:.68rem}
|
|
.copy,.copy .btn{width:100%;justify-content:center}
|
|
.menu{left:0;right:0;min-width:0}
|
|
.strip .in{flex-direction:column;align-items:flex-start;gap:.6rem;padding:.9rem 1.75rem}
|
|
.strip b{border-right:0;padding-right:0}
|
|
.bar{width:100%}
|
|
}
|
|
</style>
|
|
<link rel="icon" href="/favicon.svg">
|
|
<link rel="canonical" href="https://hanzoskills.com/data-etl-patterns">
|
|
<link rel="alternate" type="text/markdown" href="/data-etl-patterns.md">
|
|
<script type="module" src="/webmcp.js"></script>
|
|
</head>
|
|
<!-- Reading this with a machine? Every page here is also plain markdown.
|
|
This one: https://hanzoskills.com/data-etl-patterns.md
|
|
Index: https://hanzoskills.com/llms.txt
|
|
Corpus: https://hanzoskills.com/all.md
|
|
Append .md to any path for the markdown twin. -->
|
|
<body>
|
|
<nav><div class="in">
|
|
<a class="brand" href="/"><svg class="mark" viewBox="0 0 67 67" fill="currentColor" aria-hidden="true"><path d="M22.21 67V44.6369H0V67H22.21Z"/><path d="M66.7038 22.3184H22.2534L0.0878906 44.6367H44.4634L66.7038 22.3184Z"/><path d="M22.21 0H0V22.3184H22.21V0Z"/><path d="M66.7198 0H44.5098V22.3184H66.7198V0Z"/><path d="M66.7198 67V44.6369H44.5098V67H66.7198Z"/></svg> HANZO<i>SKILLS</i></a>
|
|
<a class="up" href="/">All skills</a>
|
|
</div></nav>
|
|
|
|
<div class="wrap">
|
|
<h1>data-etl-patterns</h1><p class="lede">Designing data extraction from multiple sources (databases, APIs, files)</p><h1>ETL Patterns</h1>
|
|
<p><strong>Scope</strong>: Extract-Transform-Load patterns, data sources, transformations, incremental processing <strong>Lines</strong>: 312 <strong>Last Updated</strong>: 2025-10-18 <strong>Format Version</strong>: 1.0 (Atomic)</p>
|
|
<h2>When to Use This Skill</h2>
|
|
<p>Use this skill when:</p>
|
|
<ul><li>Designing data extraction from multiple sources (databases, APIs, files)</li><li>Implementing transformation logic for data cleansing and enrichment</li><li>Loading data into warehouses or data lakes</li><li>Setting up incremental vs full load strategies</li><li>Handling CDC (Change Data Capture) patterns</li><li>Building batch data pipelines with Python, SQL, or dbt</li></ul>
|
|
<h2>Core Concepts</h2>
|
|
<h3>ETL vs ELT</h3>
|
|
<pre><code>ETL (Extract-Transform-Load)
|
|
→ Transform before loading (traditional warehouses)
|
|
→ Use when: Limited warehouse resources, complex transformations
|
|
|
|
ELT (Extract-Load-Transform)
|
|
→ Transform after loading (modern cloud warehouses)
|
|
→ Use when: Cloud warehouse available (Snowflake, BigQuery, Redshift)
|
|
→ Benefits: Leverage warehouse compute, raw data preservation</code></pre>
|
|
<h3>Load Strategies</h3>
|
|
<pre><code>Full Load
|
|
→ Complete dataset replacement
|
|
→ Use when: Small datasets, no history tracking needed
|
|
→ Pattern: TRUNCATE + INSERT or DROP + CREATE <!-- ETL operations on target tables -->
|
|
|
|
Incremental Load
|
|
→ Only new/changed records
|
|
→ Use when: Large datasets, history preservation
|
|
→ Pattern: INSERT WHERE timestamp > last_run
|
|
|
|
Upsert (Merge)
|
|
→ Insert new, update existing
|
|
→ Use when: Slowly changing dimensions
|
|
→ Pattern: MERGE or INSERT ON CONFLICT UPDATE
|
|
|
|
Append-Only
|
|
→ Always insert, never update
|
|
→ Use when: Immutable event streams
|
|
→ Pattern: INSERT with deduplication downstream</code></pre>
|
|
<h3>Change Data Capture (CDC)</h3>
|
|
<pre><code>Trigger-based CDC
|
|
→ Database triggers capture changes
|
|
→ Overhead on source system
|
|
|
|
Log-based CDC (Recommended)
|
|
→ Read database transaction logs
|
|
→ Tools: Debezium, AWS DMS, Fivetran
|
|
→ No overhead on source
|
|
|
|
Timestamp-based CDC
|
|
→ Query by updated_at column
|
|
→ Simple but requires timestamp column</code></pre>
|
|
<h2>Patterns</h2>
|
|
<h3>Pattern 1: Incremental Extraction (Timestamp-based)</h3>
|
|
<pre><code class="lang-python"># Python with SQLAlchemy
|
|
from sqlalchemy import create_engine, text
|
|
from datetime import datetime, timedelta
|
|
import pandas as pd
|
|
|
|
class IncrementalExtractor:
|
|
def __init__(self, source_conn_str, state_table="etl_state"):
|
|
self.engine = create_engine(source_conn_str)
|
|
self.state_table = state_table
|
|
|
|
def get_last_watermark(self, table_name: str) -> datetime:
|
|
"""Retrieve last processed timestamp"""
|
|
query = text(f"""
|
|
SELECT max_timestamp
|
|
FROM {self.state_table}
|
|
WHERE table_name = :table_name
|
|
""")
|
|
|
|
with self.engine.connect() as conn:
|
|
result = conn.execute(query, {"table_name": table_name}).fetchone()
|
|
return result[0] if result else datetime(1970, 1, 1)
|
|
|
|
def extract_incremental(
|
|
self,
|
|
table_name: str,
|
|
timestamp_col: str = "updated_at"
|
|
) -> pd.DataFrame:
|
|
"""Extract records since last watermark"""
|
|
last_watermark = self.get_last_watermark(table_name)
|
|
|
|
query = text(f"""
|
|
SELECT *
|
|
FROM {table_name}
|
|
WHERE {timestamp_col} > :watermark
|
|
ORDER BY {timestamp_col}
|
|
""")
|
|
|
|
df = pd.read_sql(query, self.engine, params={"watermark": last_watermark})
|
|
return df
|
|
|
|
def update_watermark(self, table_name: str, new_watermark: datetime):
|
|
"""Update state after successful load"""
|
|
query = text(f"""
|
|
INSERT INTO {self.state_table} (table_name, max_timestamp, updated_at)
|
|
VALUES (:table_name, :watermark, :now)
|
|
ON CONFLICT (table_name)
|
|
DO UPDATE SET max_timestamp = :watermark, updated_at = :now
|
|
""")
|
|
|
|
with self.engine.connect() as conn:
|
|
conn.execute(query, {
|
|
"table_name": table_name,
|
|
"watermark": new_watermark,
|
|
"now": datetime.now()
|
|
})
|
|
conn.commit()
|
|
|
|
# Usage
|
|
extractor = IncrementalExtractor("postgresql://user:pass@localhost/source")
|
|
df = extractor.extract_incremental("orders", "updated_at")
|
|
|
|
if not df.empty:
|
|
max_timestamp = df["updated_at"].max()
|
|
# ... load to destination ...
|
|
extractor.update_watermark("orders", max_timestamp)</code></pre>
|
|
<h3>Pattern 2: API Extraction with Pagination</h3>
|
|
<pre><code class="lang-python">import requests
|
|
from typing import Iterator, Dict, Any
|
|
import time
|
|
|
|
class PaginatedAPIExtractor:
|
|
def __init__(self, base_url: str, api_key: str):
|
|
self.base_url = base_url
|
|
self.headers = {"Authorization": f"Bearer {api_key}"}
|
|
|
|
def extract_paginated(
|
|
self,
|
|
endpoint: str,
|
|
page_size: int = 100,
|
|
rate_limit_delay: float = 0.5
|
|
) -> Iterator[Dict[str, Any]]:
|
|
"""Extract all records using pagination"""
|
|
page = 1
|
|
|
|
while True:
|
|
params = {"page": page, "per_page": page_size}
|
|
response = requests.get(
|
|
f"{self.base_url}/{endpoint}",
|
|
headers=self.headers,
|
|
params=params,
|
|
timeout=30
|
|
)
|
|
response.raise_for_status()
|
|
|
|
data = response.json()
|
|
records = data.get("results", [])
|
|
|
|
if not records:
|
|
break
|
|
|
|
yield from records
|
|
|
|
# Check if more pages exist
|
|
if not data.get("has_more", False):
|
|
break
|
|
|
|
page += 1
|
|
time.sleep(rate_limit_delay) # Respect rate limits
|
|
|
|
# Usage with backoff
|
|
from tenacity import retry, stop_after_attempt, wait_exponential
|
|
|
|
extractor = PaginatedAPIExtractor("https://api.example.com", "key123")
|
|
|
|
@retry(
|
|
stop=stop_after_attempt(3),
|
|
wait=wait_exponential(multiplier=1, min=4, max=10)
|
|
)
|
|
def extract_with_retry():
|
|
records = list(extractor.extract_paginated("customers"))
|
|
return pd.DataFrame(records)
|
|
|
|
df = extract_with_retry()</code></pre>
|
|
<h3>Pattern 3: Transformation with Data Quality Checks</h3>
|
|
<pre><code class="lang-python">from typing import Callable
|
|
import pandas as pd
|
|
from dataclasses import dataclass
|
|
|
|
@dataclass
|
|
class TransformResult:
|
|
valid_records: pd.DataFrame
|
|
invalid_records: pd.DataFrame
|
|
validation_errors: list
|
|
|
|
class Transformer:
|
|
def __init__(self):
|
|
self.validators = []
|
|
|
|
def add_validator(self, name: str, func: Callable):
|
|
"""Add validation rule"""
|
|
self.validators.append((name, func))
|
|
return self
|
|
|
|
def transform(self, df: pd.DataFrame) -> TransformResult:
|
|
"""Apply transformations and validations"""
|
|
errors = []
|
|
invalid_mask = pd.Series([False] * len(df), index=df.index)
|
|
|
|
# Data type conversions
|
|
df = df.copy()
|
|
|
|
# Apply validations
|
|
for name, validator in self.validators:
|
|
try:
|
|
valid_mask = df.apply(validator, axis=1)
|
|
invalid_rows = df[~valid_mask]
|
|
|
|
if not invalid_rows.empty:
|
|
errors.append({
|
|
"rule": name,
|
|
"count": len(invalid_rows),
|
|
"sample": invalid_rows.head(5).to_dict('records')
|
|
})
|
|
invalid_mask |= ~valid_mask
|
|
|
|
except Exception as e:
|
|
errors.append({
|
|
"rule": name,
|
|
"error": str(e)
|
|
})
|
|
|
|
return TransformResult(
|
|
valid_records=df[~invalid_mask],
|
|
invalid_records=df[invalid_mask],
|
|
validation_errors=errors
|
|
)
|
|
|
|
# Usage
|
|
transformer = Transformer()
|
|
transformer.add_validator(
|
|
"email_format",
|
|
lambda row: "@" in str(row.get("email", ""))
|
|
)
|
|
transformer.add_validator(
|
|
"positive_amount",
|
|
lambda row: float(row.get("amount", 0)) > 0
|
|
)
|
|
transformer.add_validator(
|
|
"required_fields",
|
|
lambda row: all(pd.notna(row.get(f)) for f in ["id", "created_at"])
|
|
)
|
|
|
|
result = transformer.transform(df)
|
|
|
|
print(f"Valid: {len(result.valid_records)}")
|
|
print(f"Invalid: {len(result.invalid_records)}")
|
|
print(f"Errors: {result.validation_errors}")</code></pre>
|
|
<h3>Pattern 4: Upsert (Merge) Load</h3>
|
|
<pre><code class="lang-python"># PostgreSQL upsert
|
|
def upsert_postgres(df: pd.DataFrame, table_name: str, key_columns: list):
|
|
"""Upsert using INSERT ON CONFLICT"""
|
|
from sqlalchemy import create_engine
|
|
from io import StringIO
|
|
|
|
engine = create_engine("postgresql://...")
|
|
|
|
# Create temp table
|
|
temp_table = f"{table_name}_temp"
|
|
df.to_sql(temp_table, engine, if_exists="replace", index=False)
|
|
|
|
# Build upsert query
|
|
all_columns = df.columns.tolist()
|
|
update_columns = [c for c in all_columns if c not in key_columns]
|
|
|
|
key_clause = ", ".join(key_columns)
|
|
update_clause = ", ".join([f"{c} = EXCLUDED.{c}" for c in update_columns])
|
|
|
|
upsert_query = f"""
|
|
INSERT INTO {table_name} ({', '.join(all_columns)})
|
|
SELECT {', '.join(all_columns)} FROM {temp_table}
|
|
ON CONFLICT ({key_clause})
|
|
DO UPDATE SET {update_clause}
|
|
"""
|
|
|
|
with engine.connect() as conn:
|
|
conn.execute(upsert_query)
|
|
# Clean up temporary staging table after merge
|
|
conn.execute(f"DROP TABLE {temp_table}")
|
|
conn.commit()
|
|
|
|
# Snowflake merge
|
|
def merge_snowflake(df: pd.DataFrame, table_name: str, key_columns: list):
|
|
"""Merge using Snowflake MERGE statement"""
|
|
from snowflake.connector.pandas_tools import write_pandas
|
|
import snowflake.connector
|
|
|
|
conn = snowflake.connector.connect(...)
|
|
|
|
# Write to temp staging table
|
|
stage_table = f"{table_name}_stage"
|
|
write_pandas(conn, df, stage_table, auto_create_table=True)
|
|
|
|
# Build merge statement
|
|
key_clause = " AND ".join([f"target.{k} = source.{k}" for k in key_columns])
|
|
update_clause = ", ".join([f"{c} = source.{c}" for c in df.columns])
|
|
insert_columns = ", ".join(df.columns)
|
|
insert_values = ", ".join([f"source.{c}" for c in df.columns])
|
|
|
|
merge_query = f"""
|
|
MERGE INTO {table_name} AS target
|
|
USING {stage_table} AS source
|
|
ON {key_clause}
|
|
WHEN MATCHED THEN
|
|
UPDATE SET {update_clause}
|
|
WHEN NOT MATCHED THEN
|
|
INSERT ({insert_columns})
|
|
VALUES ({insert_values})
|
|
"""
|
|
|
|
cursor = conn.cursor()
|
|
cursor.execute(merge_query)
|
|
# Clean up staging table after successful merge
|
|
cursor.execute(f"DROP TABLE {stage_table}")
|
|
conn.commit()
|
|
cursor.close()</code></pre>
|
|
<h3>Pattern 5: Full ETL Pipeline with State Management</h3>
|
|
<pre><code class="lang-python">from pathlib import Path
|
|
import json
|
|
from datetime import datetime
|
|
|
|
class ETLPipeline:
|
|
def __init__(self, name: str, state_file: Path):
|
|
self.name = name
|
|
self.state_file = state_file
|
|
self.state = self._load_state()
|
|
|
|
def _load_state(self) -> dict:
|
|
if self.state_file.exists():
|
|
return json.loads(self.state_file.read_text())
|
|
return {"runs": []}
|
|
|
|
def _save_state(self):
|
|
self.state_file.write_text(json.dumps(self.state, indent=2, default=str))
|
|
|
|
def run(self, extract_fn, transform_fn, load_fn):
|
|
"""Execute ETL with error handling and state tracking"""
|
|
run_id = datetime.now().isoformat()
|
|
run_state = {
|
|
"run_id": run_id,
|
|
"started_at": run_id,
|
|
"status": "running"
|
|
}
|
|
|
|
try:
|
|
# Extract
|
|
print(f"[{self.name}] Extracting...")
|
|
raw_data = extract_fn()
|
|
run_state["extracted_count"] = len(raw_data)
|
|
|
|
# Transform
|
|
print(f"[{self.name}] Transforming...")
|
|
result = transform_fn(raw_data)
|
|
run_state["valid_count"] = len(result.valid_records)
|
|
run_state["invalid_count"] = len(result.invalid_records)
|
|
|
|
# Load
|
|
print(f"[{self.name}] Loading...")
|
|
load_fn(result.valid_records)
|
|
|
|
run_state["status"] = "success"
|
|
run_state["completed_at"] = datetime.now().isoformat()
|
|
|
|
except Exception as e:
|
|
run_state["status"] = "failed"
|
|
run_state["error"] = str(e)
|
|
raise
|
|
|
|
finally:
|
|
self.state["runs"].append(run_state)
|
|
self.state["last_run"] = run_state
|
|
self._save_state()
|
|
|
|
return run_state
|
|
|
|
# Usage
|
|
pipeline = ETLPipeline("orders_etl", Path("/tmp/orders_state.json"))
|
|
|
|
result = pipeline.run(
|
|
extract_fn=lambda: extractor.extract_incremental("orders"),
|
|
transform_fn=lambda df: transformer.transform(df),
|
|
load_fn=lambda df: upsert_postgres(df, "orders", ["order_id"])
|
|
)</code></pre>
|
|
<h2>Quick Reference</h2>
|
|
<h3>Common Extraction Sources</h3>
|
|
<pre><code class="lang-python"># PostgreSQL/MySQL
|
|
pd.read_sql("SELECT * FROM table", engine)
|
|
|
|
# REST API
|
|
requests.get(url, headers=headers).json()
|
|
|
|
# CSV/Parquet
|
|
pd.read_csv("file.csv")
|
|
pd.read_parquet("file.parquet")
|
|
|
|
# S3
|
|
s3 = boto3.client('s3')
|
|
obj = s3.get_object(Bucket='bucket', Key='key')
|
|
pd.read_csv(obj['Body'])</code></pre>
|
|
<h3>Transformation Functions</h3>
|
|
<pre><code class="lang-python"># Type casting
|
|
df['date'] = pd.to_datetime(df['date'])
|
|
df['amount'] = df['amount'].astype(float)
|
|
|
|
# Null handling
|
|
df.fillna({'column': default_value})
|
|
df.dropna(subset=['required_col'])
|
|
|
|
# String cleaning
|
|
df['email'] = df['email'].str.lower().str.strip()
|
|
|
|
# Deduplication
|
|
df.drop_duplicates(subset=['key'], keep='last')</code></pre>
|
|
<h3>Load Patterns</h3>
|
|
<pre><code class="lang-python"># Append
|
|
df.to_sql('table', engine, if_exists='append', index=False)
|
|
|
|
# Replace
|
|
df.to_sql('table', engine, if_exists='replace', index=False)
|
|
|
|
# Bulk insert (faster)
|
|
from sqlalchemy import insert
|
|
records = df.to_dict('records')
|
|
with engine.connect() as conn:
|
|
conn.execute(insert(table), records)
|
|
conn.commit()</code></pre>
|
|
<h2>Anti-Patterns</h2>
|
|
<pre><code>❌ NEVER: Extract entire table when incremental is possible
|
|
→ Use timestamp or ID-based incremental extraction
|
|
|
|
❌ NEVER: Load without validation
|
|
→ Always validate data quality before loading
|
|
|
|
❌ NEVER: Hardcode connection strings
|
|
→ Use environment variables or secrets manager
|
|
|
|
❌ NEVER: Skip error handling
|
|
→ Wrap extract/load in try-except, log failures
|
|
|
|
❌ NEVER: Load invalid records silently
|
|
→ Route to quarantine table for inspection
|
|
|
|
❌ NEVER: Run full loads on large tables
|
|
→ Use incremental or partitioned loads
|
|
|
|
❌ NEVER: Skip state management
|
|
→ Track watermarks, run history for recovery
|
|
|
|
❌ NEVER: Transform before validating schema
|
|
→ Validate source schema before transformations
|
|
|
|
❌ NEVER: Use SELECT * in production
|
|
→ Specify columns explicitly for stability</code></pre>
|
|
<h2>Related Skills</h2>
|
|
<ul><li><code>stream-processing.md</code> - Real-time alternatives to batch ETL</li><li><code>batch-processing.md</code> - Orchestrating ETL with Airflow</li><li><code>data-validation.md</code> - Advanced schema and quality validation</li><li><code>pipeline-orchestration.md</code> - Workflow management for ETL</li></ul>
|
|
<p><strong>Last Updated</strong>: 2025-10-18 <strong>Format Version</strong>: 1.0 (Atomic)</p>
|
|
<footer>
|
|
<div class="fmap">
|
|
<div><b>Build</b>
|
|
<a href="https://hanzo.app">hanzo.app</a> app builder
|
|
<a href="https://hanzo.codes">hanzo.codes</a> code with an agent
|
|
<a href="https://hanzoskills.com">hanzoskills.com</a> what an agent reads</div>
|
|
<div><b>Run</b>
|
|
<a href="https://console.hanzo.ai">console.hanzo.ai</a> cloud console
|
|
<a href="https://api.hanzo.ai">api.hanzo.ai</a> the API</div>
|
|
<div><b>Account</b>
|
|
<a href="https://hanzo.id">hanzo.id</a> sign in
|
|
<a href="https://pay.hanzo.ai">pay.hanzo.ai</a> top up
|
|
<a href="https://billing.hanzo.ai">billing.hanzo.ai</a> invoices and usage</div>
|
|
<div><b>Company</b>
|
|
<a href="https://hanzo.ai">hanzo.ai</a> canonical
|
|
<a href="https://hanzo.ventures">hanzo.ventures</a> ventures</div>
|
|
</div>
|
|
<div class="fend"><a href="/data-etl-patterns.md">This page as plain text</a> · <a href="/llms.txt">Index for agents</a></div>
|
|
</footer>
|
|
</div></body></html> |