Beginner

Source Systems

Memahami berbagai sumber data dan cara mengekstraknya

⏱️ 30 min read 📅 Updated Jan 2025 👤 By DataLearn Team

Mode Baca Pemula

Anggap source system sebagai “asal data”. Fokus baca:

Jenis source yang paling umum
Karakteristik data dari tiap source
Metode ekstraksi yang cocok per source

Kamus istilah: DE-GLOSSARY.md

Prasyarat Ringan

Paham konsep dasar database dan API
Tahu bahwa data bisa datang dalam format berbeda
Paham perbedaan data real-time vs batch secara umum

Istilah Penting (3 Lapis)

Istilah: Source System

Definisi awam: Tempat asal data sebelum masuk pipeline data.

Definisi teknis: Sistem operasional atau eksternal yang menghasilkan data untuk ingestion.

Contoh praktis: PostgreSQL aplikasi, API payment gateway, log clickstream.

Istilah: CDC

Definisi awam: Ambil hanya data yang berubah.

Definisi teknis: Teknik capture perubahan insert/update/delete dari source database.

Contoh praktis: Order baru/diupdate otomatis tersinkron ke warehouse tanpa full dump.

Apa itu Source Systems?

Source Systems adalah sistem atau aplikasi yang menciptakan data. Ini adalah titik awal dari data engineering lifecycle. Tanpa memahami source systems, kita tidak bisa membangun pipeline yang efektif.

💡 Key Concept

Data engineers don't control source systems, but must understand them deeply to build reliable pipelines.

Jenis-jenis Source Systems

🗄️

OLTP Databases

MySQL, PostgreSQL, Oracle - Transaksi bisnis harian

📡

APIs

REST, GraphQL - Data dari third-party services

📁

Files

CSV, JSON, Parquet, Log files

🔄

Message Queues

Kafka, RabbitMQ - Event streaming real-time

📱

IoT Devices

Sensors, wearables, smart devices

☁️

SaaS Apps

Salesforce, HubSpot, Google Analytics

1. OLTP Databases (Online Transaction Processing)

OLTP databases adalah sistem transaksi bisnis utama. Mereka designed untuk ACID compliance dan low-latency writes, bukan untuk analytics.

Common OLTP Databases

Database	Type	Best For	CDC Support
PostgreSQL	Open Source RDBMS	Web apps, complex queries	✅ Logical Replication
MySQL	Open Source RDBMS	Web apps, high read traffic	✅ Binary Log
SQL Server	Enterprise RDBMS	Enterprise applications	✅ Change Tracking
MongoDB	Document Store	Flexible schemas, JSON data	✅ Change Streams

Extraction Methods

📋 Full Extract

When: Small tables (< 1M rows), initial load

SELECT * FROM users;

Pros: Simple, complete snapshot

Cons: Slow untuk large tables, heavy load on source

📅 Incremental (Timestamp)

When: Tables dengan updated_at column

SELECT * FROM orders
WHERE updated_at > :last_extract_time;
                

Pros: Efficient, lower load

Cons: Misses hard deletes, requires timestamp column

🔢 Incremental (Auto-increment)

When: Append-only tables dengan ID

SELECT * FROM events
WHERE id > :last_max_id;
                

Pros: Reliable, no timestamp needed

Cons: Doesn't capture updates/deletes

🔄 CDC (Change Data Capture)

When: Real-time requirements, audit needs

Tools: Debezium, AWS DMS, Fivetran

Pros: Captures all changes, low latency

Cons: More complex setup

CDC (Change Data Capture) Deep Dive

CDC adalah metode terbaik untuk merekam setiap perubahan data di source system.

🔄 How CDC Works

Database (Write Ops) → Transaction Log (WAL/BinLog) → CDC Connector → Message Queue → Data Pipeline

            🛠️ Popular CDC Tools
            Debezium - Open source, Kafka-based, supports many databases
AWS DMS - Managed service, easy setup
Fivetran/Airbyte - SaaS solutions, no-code/low-code
Maxwell/Canal - MySQL binlog readers

        

2. APIs (Application Programming Interfaces)

APIs adalah cara umum untuk mengakses data dari third-party services atau internal microservices.

Types of APIs

Type	Format	Best For
REST	JSON, HTTP verbs	General purpose, simple CRUD
GraphQL	Query language	Flexible data fetching, mobile apps
gRPC	Protocol Buffers	High performance, internal services
Webhook	HTTP POST callbacks	Real-time event notifications

API Pagination Patterns

# Python example: Handling pagination dengan cursor-based API
import requests

def fetch_all_data(api_url, api_key):
    all_data = []
    cursor = None

    while True:
        params = {"limit": 100}
        if cursor:
            params["cursor"] = cursor

        response = requests.get(
            api_url,
            headers={"Authorization": f"Bearer {api_key}"},
            params=params
        )
        data = response.json()

        all_data.extend(data["results"])

        # Check for next page
        cursor = data.get("next_cursor")
        if not cursor:
            break

    return all_data
        

            ⚠️ API Best Practices
            Respect rate limits - Implement exponential backoff
Handle failures gracefully - Retry with circuit breaker pattern
Idempotency - Ensure duplicate requests don't corrupt data
Track state - Store last fetch timestamp/cursor

        

3. Message Queues & Event Streaming

Message queues dan event streaming systems adalah source untuk real-time data.

Apache Kafka Concepts

📊 Kafka Architecture

Producers → Topics (Partitioned) → Consumers

Topics → Partitions → Segments (on disk)
Consumer Groups → Parallel processing

Concept	Description
Topic	Category/feed name (e.g., "user-events", "orders")
Partition	Ordered, immutable sequence of messages within a topic
Offset	Unique ID of a message within a partition
Consumer Group	Group of consumers that share work (one consumer per partition)
Retention	How long to keep messages (time-based or size-based)

4. Files & Object Storage

Files masih menjadi source data yang umum, terutama untuk:

Data exports dari legacy systems
Log files
Third-party data dumps
Media files (images, documents)

File Formats Comparison

Format	Structure	Best For	Compression
CSV	Text, row-based	Simple data, human-readable	❌ Poor
JSON	Text, nested objects	Semi-structured data, APIs	⚠️ Moderate
Parquet	Binary, columnar	Analytics, large datasets	✅ Excellent
Avro	Binary, row-based	Streaming, schema evolution	✅ Good
ORC	Binary, columnar	Hadoop ecosystem	✅ Excellent

5. IoT & Streaming Data

IoT devices menghasilkan data dengan karakteristik khusus:

High volume: Millions of events per second
Time-series: Data points with timestamps
Out-of-order: Events may arrive out of sequence
Late arriving: Network delays cause late data

            📡 IoT Protocols
            MQTT - Lightweight, publish-subscribe, ideal untuk IoT
CoAP - REST-like protocol untuk constrained devices
HTTP/2 - Untuk devices dengan resource lebih
WebSocket - Persistent connection, bidirectional

        

Source System Discovery Checklist

Sebelum membangun pipeline, selalu tanyakan:

✅ Discovery Questions

Volume:

Berapa banyak data per hari/jam/detik?
Expected growth rate?

Velocity:

Batch atau streaming?
Latency requirements?

Variety:

Structured, semi-structured, atau unstructured?
Schema stability?

Veracity:

Data quality issues?
Missing data patterns?

Case Study: E-commerce Data Sources

🛒 Tokopedia Source Systems Architecture

Transactional Data:

PostgreSQL (Product Catalog, Orders) → CDC via Debezium
MySQL (User Profiles) → Binary log replication

Behavioral Data:

Mobile App Events → Kafka (via SDK)
Website Clickstream → Kafka (via JavaScript tracker)

Third-party:

Payment Gateway API → Scheduled API pulls
Logistics Partner → Webhook callbacks

Decision Framework: Source System Strategy

Decision Point	Pilih Opsi A Jika...	Pilih Opsi B Jika...
CDC vs Batch Extract	Butuh sinkronisasi near real-time dan source mendukung logs	Latency longgar atau source legacy tanpa CDC support
Pull API vs Webhook/Event push	Provider tidak menyediakan event endpoint	Perlu update cepat dan efisiensi network
Single source of truth vs Multiple source fusion	Ada sistem otoritatif yang jelas	Nilai bisnis butuh penggabungan dari banyak sistem operasional

Failure Modes & Anti-Patterns

            Anti-Patterns di Source Integration
            No contract with source owners: perubahan source terjadi tanpa notifikasi.
Polling too aggressive: rate-limit dan throttling dari provider.
Ignoring source semantics: delete/update handling salah dan data drift.
One-size-fits-all ingestion: semua source diperlakukan sama.

        

Production Readiness Checklist

            Checklist Source System
            Owner source system dan contact escalation jelas.
Schema contract/versioning disepakati.
Latency expectation per source terdokumentasi.
Rate limit, retry, timeout policy diterapkan.
Delete/update semantics diuji end-to-end.
Fallback plan saat source unavailable tersedia.

        

✏️ Exercise: Source System Analysis

Kamu diminta untuk mendesain pipeline untuk sebuah fintech lending app. Identifikasi source systems berikut dan extraction method yang tepat:

User registration data (PostgreSQL, 100 inserts/day)
Transaction history (MySQL, 10K inserts/day)
Credit score dari third-party API (daily update)
Mobile app events (real-time fraud detection)
Bank statement uploads (PDF files)

Hint: Pertimbangkan volume, latency requirement, dan format data.

🎯 Quick Quiz

1. Metode terbaik untuk capture semua perubahan data termasuk delete adalah?

A. Full Extract

B. Timestamp-based Incremental

C. CDC (Change Data Capture)

D. API Polling

2. Format file terbaik untuk analytics workload adalah?

A. CSV

B. JSON

C. Parquet

D. XML

3. Kafka partition key digunakan untuk?

A. Data encryption

B. Determining which partition a message goes to

C. Message compression

D. Authentication

Kesimpulan

Memahami source systems adalah fundamental untuk data engineering. Setiap source memiliki karakteristik berbeda: OLTP databases untuk transaksi, APIs untuk integrasi, Kafka untuk streaming, dan files untuk batch.

Pilih extraction method yang sesuai: Full extract untuk small tables, incremental untuk large tables, dan CDC untuk real-time requirements.

            🎯 Key Takeaways
            CDC adalah gold standard untuk database replication
Parquet > CSV untuk analytics workloads
Respect API rate limits dengan exponential backoff
Selalu pertimbangkan volume, velocity, variety, veracity

        

📚 References & Resources

Primary Sources

Fundamentals of Data Engineering - Joe Reis & Matt Housley (O'Reilly, 2022)
Chapter 7: Understanding Source Systems
Designing Data-Intensive Applications - Martin Kleppmann (O'Reilly, 2017)
Chapter 5: Replication & Chapter 11: Stream Processing