Files
skills/public/data-etl-patterns.html
zeekay 747dafba10 Register WebMCP tools on every page, and name each page's twin from site.py
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.
2026-10-26 20:57:05 -07:00

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 &lt;!-- ETL operations on target tables --&gt;
Incremental Load
→ Only new/changed records
→ Use when: Large datasets, history preservation
→ Pattern: INSERT WHERE timestamp &gt; 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=&quot;etl_state&quot;):
self.engine = create_engine(source_conn_str)
self.state_table = state_table
def get_last_watermark(self, table_name: str) -&gt; datetime:
&quot;&quot;&quot;Retrieve last processed timestamp&quot;&quot;&quot;
query = text(f&quot;&quot;&quot;
SELECT max_timestamp
FROM {self.state_table}
WHERE table_name = :table_name
&quot;&quot;&quot;)
with self.engine.connect() as conn:
result = conn.execute(query, {&quot;table_name&quot;: table_name}).fetchone()
return result[0] if result else datetime(1970, 1, 1)
def extract_incremental(
self,
table_name: str,
timestamp_col: str = &quot;updated_at&quot;
) -&gt; pd.DataFrame:
&quot;&quot;&quot;Extract records since last watermark&quot;&quot;&quot;
last_watermark = self.get_last_watermark(table_name)
query = text(f&quot;&quot;&quot;
SELECT *
FROM {table_name}
WHERE {timestamp_col} &gt; :watermark
ORDER BY {timestamp_col}
&quot;&quot;&quot;)
df = pd.read_sql(query, self.engine, params={&quot;watermark&quot;: last_watermark})
return df
def update_watermark(self, table_name: str, new_watermark: datetime):
&quot;&quot;&quot;Update state after successful load&quot;&quot;&quot;
query = text(f&quot;&quot;&quot;
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
&quot;&quot;&quot;)
with self.engine.connect() as conn:
conn.execute(query, {
&quot;table_name&quot;: table_name,
&quot;watermark&quot;: new_watermark,
&quot;now&quot;: datetime.now()
})
conn.commit()
# Usage
extractor = IncrementalExtractor(&quot;postgresql://user:pass@localhost/source&quot;)
df = extractor.extract_incremental(&quot;orders&quot;, &quot;updated_at&quot;)
if not df.empty:
max_timestamp = df[&quot;updated_at&quot;].max()
# ... load to destination ...
extractor.update_watermark(&quot;orders&quot;, 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 = {&quot;Authorization&quot;: f&quot;Bearer {api_key}&quot;}
def extract_paginated(
self,
endpoint: str,
page_size: int = 100,
rate_limit_delay: float = 0.5
) -&gt; Iterator[Dict[str, Any]]:
&quot;&quot;&quot;Extract all records using pagination&quot;&quot;&quot;
page = 1
while True:
params = {&quot;page&quot;: page, &quot;per_page&quot;: page_size}
response = requests.get(
f&quot;{self.base_url}/{endpoint}&quot;,
headers=self.headers,
params=params,
timeout=30
)
response.raise_for_status()
data = response.json()
records = data.get(&quot;results&quot;, [])
if not records:
break
yield from records
# Check if more pages exist
if not data.get(&quot;has_more&quot;, 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(&quot;https://api.example.com&quot;, &quot;key123&quot;)
@retry(
stop=stop_after_attempt(3),
wait=wait_exponential(multiplier=1, min=4, max=10)
)
def extract_with_retry():
records = list(extractor.extract_paginated(&quot;customers&quot;))
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):
&quot;&quot;&quot;Add validation rule&quot;&quot;&quot;
self.validators.append((name, func))
return self
def transform(self, df: pd.DataFrame) -&gt; TransformResult:
&quot;&quot;&quot;Apply transformations and validations&quot;&quot;&quot;
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({
&quot;rule&quot;: name,
&quot;count&quot;: len(invalid_rows),
&quot;sample&quot;: invalid_rows.head(5).to_dict(&#x27;records&#x27;)
})
invalid_mask |= ~valid_mask
except Exception as e:
errors.append({
&quot;rule&quot;: name,
&quot;error&quot;: str(e)
})
return TransformResult(
valid_records=df[~invalid_mask],
invalid_records=df[invalid_mask],
validation_errors=errors
)
# Usage
transformer = Transformer()
transformer.add_validator(
&quot;email_format&quot;,
lambda row: &quot;@&quot; in str(row.get(&quot;email&quot;, &quot;&quot;))
)
transformer.add_validator(
&quot;positive_amount&quot;,
lambda row: float(row.get(&quot;amount&quot;, 0)) &gt; 0
)
transformer.add_validator(
&quot;required_fields&quot;,
lambda row: all(pd.notna(row.get(f)) for f in [&quot;id&quot;, &quot;created_at&quot;])
)
result = transformer.transform(df)
print(f&quot;Valid: {len(result.valid_records)}&quot;)
print(f&quot;Invalid: {len(result.invalid_records)}&quot;)
print(f&quot;Errors: {result.validation_errors}&quot;)</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):
&quot;&quot;&quot;Upsert using INSERT ON CONFLICT&quot;&quot;&quot;
from sqlalchemy import create_engine
from io import StringIO
engine = create_engine(&quot;postgresql://...&quot;)
# Create temp table
temp_table = f&quot;{table_name}_temp&quot;
df.to_sql(temp_table, engine, if_exists=&quot;replace&quot;, 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 = &quot;, &quot;.join(key_columns)
update_clause = &quot;, &quot;.join([f&quot;{c} = EXCLUDED.{c}&quot; for c in update_columns])
upsert_query = f&quot;&quot;&quot;
INSERT INTO {table_name} ({&#x27;, &#x27;.join(all_columns)})
SELECT {&#x27;, &#x27;.join(all_columns)} FROM {temp_table}
ON CONFLICT ({key_clause})
DO UPDATE SET {update_clause}
&quot;&quot;&quot;
with engine.connect() as conn:
conn.execute(upsert_query)
# Clean up temporary staging table after merge
conn.execute(f&quot;DROP TABLE {temp_table}&quot;)
conn.commit()
# Snowflake merge
def merge_snowflake(df: pd.DataFrame, table_name: str, key_columns: list):
&quot;&quot;&quot;Merge using Snowflake MERGE statement&quot;&quot;&quot;
from snowflake.connector.pandas_tools import write_pandas
import snowflake.connector
conn = snowflake.connector.connect(...)
# Write to temp staging table
stage_table = f&quot;{table_name}_stage&quot;
write_pandas(conn, df, stage_table, auto_create_table=True)
# Build merge statement
key_clause = &quot; AND &quot;.join([f&quot;target.{k} = source.{k}&quot; for k in key_columns])
update_clause = &quot;, &quot;.join([f&quot;{c} = source.{c}&quot; for c in df.columns])
insert_columns = &quot;, &quot;.join(df.columns)
insert_values = &quot;, &quot;.join([f&quot;source.{c}&quot; for c in df.columns])
merge_query = f&quot;&quot;&quot;
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})
&quot;&quot;&quot;
cursor = conn.cursor()
cursor.execute(merge_query)
# Clean up staging table after successful merge
cursor.execute(f&quot;DROP TABLE {stage_table}&quot;)
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) -&gt; dict:
if self.state_file.exists():
return json.loads(self.state_file.read_text())
return {&quot;runs&quot;: []}
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):
&quot;&quot;&quot;Execute ETL with error handling and state tracking&quot;&quot;&quot;
run_id = datetime.now().isoformat()
run_state = {
&quot;run_id&quot;: run_id,
&quot;started_at&quot;: run_id,
&quot;status&quot;: &quot;running&quot;
}
try:
# Extract
print(f&quot;[{self.name}] Extracting...&quot;)
raw_data = extract_fn()
run_state[&quot;extracted_count&quot;] = len(raw_data)
# Transform
print(f&quot;[{self.name}] Transforming...&quot;)
result = transform_fn(raw_data)
run_state[&quot;valid_count&quot;] = len(result.valid_records)
run_state[&quot;invalid_count&quot;] = len(result.invalid_records)
# Load
print(f&quot;[{self.name}] Loading...&quot;)
load_fn(result.valid_records)
run_state[&quot;status&quot;] = &quot;success&quot;
run_state[&quot;completed_at&quot;] = datetime.now().isoformat()
except Exception as e:
run_state[&quot;status&quot;] = &quot;failed&quot;
run_state[&quot;error&quot;] = str(e)
raise
finally:
self.state[&quot;runs&quot;].append(run_state)
self.state[&quot;last_run&quot;] = run_state
self._save_state()
return run_state
# Usage
pipeline = ETLPipeline(&quot;orders_etl&quot;, Path(&quot;/tmp/orders_state.json&quot;))
result = pipeline.run(
extract_fn=lambda: extractor.extract_incremental(&quot;orders&quot;),
transform_fn=lambda df: transformer.transform(df),
load_fn=lambda df: upsert_postgres(df, &quot;orders&quot;, [&quot;order_id&quot;])
)</code></pre>
<h2>Quick Reference</h2>
<h3>Common Extraction Sources</h3>
<pre><code class="lang-python"># PostgreSQL/MySQL
pd.read_sql(&quot;SELECT * FROM table&quot;, engine)
# REST API
requests.get(url, headers=headers).json()
# CSV/Parquet
pd.read_csv(&quot;file.csv&quot;)
pd.read_parquet(&quot;file.parquet&quot;)
# S3
s3 = boto3.client(&#x27;s3&#x27;)
obj = s3.get_object(Bucket=&#x27;bucket&#x27;, Key=&#x27;key&#x27;)
pd.read_csv(obj[&#x27;Body&#x27;])</code></pre>
<h3>Transformation Functions</h3>
<pre><code class="lang-python"># Type casting
df[&#x27;date&#x27;] = pd.to_datetime(df[&#x27;date&#x27;])
df[&#x27;amount&#x27;] = df[&#x27;amount&#x27;].astype(float)
# Null handling
df.fillna({&#x27;column&#x27;: default_value})
df.dropna(subset=[&#x27;required_col&#x27;])
# String cleaning
df[&#x27;email&#x27;] = df[&#x27;email&#x27;].str.lower().str.strip()
# Deduplication
df.drop_duplicates(subset=[&#x27;key&#x27;], keep=&#x27;last&#x27;)</code></pre>
<h3>Load Patterns</h3>
<pre><code class="lang-python"># Append
df.to_sql(&#x27;table&#x27;, engine, if_exists=&#x27;append&#x27;, index=False)
# Replace
df.to_sql(&#x27;table&#x27;, engine, if_exists=&#x27;replace&#x27;, index=False)
# Bulk insert (faster)
from sqlalchemy import insert
records = df.to_dict(&#x27;records&#x27;)
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>