📥 Data Extraction Methods & Techniques
📥 Data Extraction
Extraction is like mining raw ore from different locations — underground mines (databases), trade ships (APIs), and open fields (web). You don't refine it here. You just bring it in.
Data Extraction is the first step of ETL/ELT — pulling raw data from source systems into a staging area or data lake for further processing.
Extraction should be non-intrusive — it must not slow down or disrupt the source system (e.g., the live OLTP database that customers depend on).
🔍 Extraction Methods
Full Extraction vs Incremental Extraction
| Type | What It Does | When to Use |
|---|---|---|
| Full Extraction | Pulls all data from the source every time | Small tables, first-time load, reference data |
| Incremental Extraction | Pulls only new or changed data since last run | Large tables, daily pipelines, transactional data |
Full: SELECT * FROM Orders
-- Every run: 10 million rows. Slow. Expensive.
Incremental: SELECT * FROM Orders WHERE updated_at > '2024-06-20'
-- Every run: only yesterday's changes. Fast. Efficient.
Always prefer incremental extraction for large tables — full extraction on a 100M-row table daily is a waste of compute and time.
🛠️ Extraction Techniques
1️⃣ Database Extraction
Pulling data directly from relational or NoSQL databases using queries or connectors.
Tools: JDBC/ODBC drivers, Sqoop, Spark JDBC, Debezium
Example — SQL Server to Snowflake:
Source: SELECT * FROM dbo.Sales WHERE sale_date = CAST(GETDATE()-1 AS DATE)
Tool: Azure Data Factory reads this query and loads results into Snowflake staging
Types:
- Query-based — run a SQL query to extract rows
- Timestamp-based — filter using
updated_atorcreated_atcolumns - Log-based (CDC) — read the database transaction log to capture inserts/updates/deletes
CDC (Change Data Capture) is the most efficient database extraction method — it captures every change at the database log level without querying the table at all. Used by tools like Debezium, AWS DMS.
2️⃣ API Extraction
Pulling data from external systems or SaaS platforms via APIs (Application Programming Interfaces).
Example — Extracting from Salesforce CRM:
GET https://mycompany.salesforce.com/services/data/v57.0/query
?q=SELECT Id, Name, Amount FROM Opportunity WHERE CloseDate = TODAY
Response (JSON):
{
"records": [
{ "Id": "006xx", "Name": "Infosys Deal", "Amount": 500000 },
{ "Id": "007xx", "Name": "TCS Deal", "Amount": 750000 }
]
}
Key considerations:
- Rate limits — APIs cap how many requests per minute/hour; extraction must respect this
- Authentication — OAuth2, API keys, JWT tokens
- Pagination — large datasets come in pages; extraction must loop through all pages
- Versioning — API versions change; pipelines must handle breaking changes
Tools: Fivetran, Airbyte, Stitch, custom Python scripts (requests library)
For popular SaaS tools (Salesforce, HubSpot, Stripe, Google Analytics), use pre-built connectors (Fivetran/Airbyte) instead of building custom API extractors.
3️⃣ Web Scraping
Extracting data from websites by parsing their HTML when no API is available.
Example — Scraping product prices from an e-commerce site:
import requests
from bs4 import BeautifulSoup
url = "https://example.com/products"
page = requests.get(url)
soup = BeautifulSoup(page.content, "html.parser")
products = soup.find_all("div", class_="product-card")
for p in products:
name = p.find("h2").text
price = p.find("span", class_="price").text
print(name, price)
Use cases:
- Competitor price monitoring
- News and sentiment data collection
- Real estate listings, job postings
- Market research when no API exists
Web scraping has legal and ethical risks:
- Many sites explicitly prohibit scraping in their Terms of Service
- Aggressive scraping can overload servers (treat it like a DDoS attack)
- Always check
robots.txtbefore scraping - Prefer official APIs when they exist
📂 Data Types in Extraction
Data arrives in 3 fundamental forms — and your extraction approach must handle all of them.
1️⃣ Structured Data
Data that fits neatly into rows and columns — has a predefined schema.
| Property | Detail |
|---|---|
| Format | Tables (SQL databases), CSV, Excel |
| Schema | Fixed — columns and types defined upfront |
| Query | SQL |
| Examples | Employee table, sales orders, inventory records |
| order_id | customer_id | amount | order_date |
|----------|-------------|--------|------------|
| 1001 | C001 | 4500 | 2024-06-21 |
| 1002 | C002 | 1200 | 2024-06-21 |
2️⃣ Semi-Structured Data
Data that has some structure (tags, keys) but doesn't fit rigid rows/columns — the schema is flexible.
| Property | Detail |
|---|---|
| Format | JSON, XML, YAML, Avro, Parquet |
| Schema | Flexible — fields can vary per record |
| Query | JSONPath, XPath, SQL on JSON (BigQuery, Snowflake) |
| Examples | API responses, log files, config files, social media posts |
// JSON — semi-structured (nested, flexible fields)
{
"order_id": "1001",
"customer": { "name": "Raj", "city": "Pune" },
"items": [
{ "product": "Laptop", "qty": 1, "price": 75000 },
{ "product": "Mouse", "qty": 2, "price": 800 }
],
"tags": ["electronics", "express-delivery"]
}
Most modern APIs return JSON — it's the most common semi-structured format you'll work with.
3️⃣ Unstructured Data
Data with no predefined structure — cannot be stored in traditional rows/columns without transformation.
| Property | Detail |
|---|---|
| Format | Text, images, audio, video, PDFs, emails |
| Schema | None |
| Processing | NLP, Computer Vision, OCR, embeddings |
| Examples | Customer reviews, support chat logs, invoices (PDF), CCTV footage |
| Storage | Data Lake (S3, ADLS), not a relational DW |
Examples by type:
Text → "The product quality was excellent but delivery was slow"
Image → Product photos, ID documents
Audio → Call center recordings
Video → CCTV footage, training videos
PDF → Invoices, contracts, reports
Unstructured data cannot be directly loaded into a relational DW. It must first be processed (OCR, NLP, embeddings) to extract structured features before it can be analysed.
🗺️ Data Types Summary
| Type | Structure | Format | Storage | Processing |
|---|---|---|---|---|
| Structured | Rigid rows/columns | SQL, CSV, Excel | RDBMS, DW | SQL queries |
| Semi-structured | Flexible keys/tags | JSON, XML, Parquet | Lake, DW | JSONPath, SQL |
| Unstructured | None | Text, Image, Video | Data Lake | NLP, CV, OCR |
🧪 Practice Drill
// Try answering these:
// Q1. What is the difference between Full and Incremental extraction? When would you use each?
// Q2. A company wants to extract data from Salesforce daily into their warehouse. Which extraction technique should they use and what tool would you recommend?
// Q3. A database has 500 million rows. The team is doing a nightly full extraction. What problem does this cause and what's the fix?
// Q4. Classify each data source as Structured, Semi-structured, or Unstructured:
// - Customer support emails
// - Oracle HR database
// - Salesforce API response (JSON)
// - CCTV footage
// - CSV sales export
// - Server log files
// Q5. Why can't unstructured data be loaded directly into a relational Data Warehouse?
💡 Click for Solutions
A1.
- Full extraction — pulls all data every time; used for small tables or initial loads
- Incremental extraction — pulls only new/changed data since last run; used for large tables and ongoing pipelines
- Use full for: reference/lookup tables, first-time loads
- Use incremental for: transactional tables, daily pipelines, anything over a few million rows
A2.
- Technique: API Extraction (Salesforce exposes a REST API)
- Tool: Fivetran or Airbyte — pre-built Salesforce connector handles auth, pagination, rate limits automatically
A3.
- Problem: 500M rows extracted nightly = massive compute cost, slow pipeline, high load on source DB
- Fix: Switch to incremental extraction using a timestamp column (
updated_at > last_run_time) or CDC to capture only changed rows
A4.
| Source | Type |
|---|---|
| Customer support emails | Unstructured |
| Oracle HR database | Structured |
| Salesforce API response (JSON) | Semi-structured |
| CCTV footage | Unstructured |
| CSV sales export | Structured |
| Server log files | Semi-structured |
A5. Relational DWs require a fixed schema (defined columns and data types). Unstructured data (text, images, video) has no inherent schema — it must first be processed (NLP for text, OCR for PDFs, Computer Vision for images) to extract structured features before it can be stored in a DW.
← Previous Topic | Next Topic → Next Topic