Skip to main content

Learning path

ETL/ELT Architecture learning path: everything to study, topic by topic

Comprehensive ETL/ELT assessment covering data extraction patterns, transformation logic, loading strategies, pipeline orchestration, change data capture, data quality, warehouse and lake architectures, and enterprise data governance.

Short answer

To learn ETL/ELT Architecture, work through 6 modules and 22 topics in the order they build on each other. It starts with ETL Fundamentals, then moves on to Data Extraction and Transformation Patterns. Use the map as a checklist, or measure your level and study only what is missing.

6
modules
22
topics
45
min to assess

What you will study

Topics are grouped by module. Open one to see its objectives and key concepts.

1 · ETL Fundamentals4 topics
  • ETL vs ELT Paradigms

    Traditional ETL architecture, modern ELT approach, when to choose each, tool ecosystems (Informatica, Talend, dbt, Fivetran), cloud-native ELT advantages

    Objectives

    • Explain the difference between ETL and ELT architectures
    • Identify when each paradigm is appropriate

    Key concepts

    • ETL definition
    • ELT definition
    • transform-in-place
    • staging area
    • tool landscape
    • cloud-native ELT
  • Batch vs Stream Processing

    Batch processing fundamentals, stream processing with Kafka/Kinesis/Flink, micro-batch patterns, lambda and kappa architectures, latency trade-offs

    Objectives

    • Distinguish batch from stream processing and their use cases
    • Describe lambda and kappa architecture trade-offs

    Key concepts

    • batch processing
    • stream processing
    • micro-batch
    • lambda architecture
    • kappa architecture
    • event-driven pipelines
    • latency vs throughput
    • windowing
  • Data Quality Basics

    Data quality dimensions (accuracy, completeness, consistency, timeliness), validation rules, profiling, null handling, deduplication fundamentals

    Objectives

    • List and explain data quality dimensions
    • Apply basic data validation and profiling techniques

    Key concepts

    • accuracy
    • completeness
    • consistency
    • timeliness
    • data profiling
    • null handling
    • deduplication
    • validation rules
  • Data Formats & Serialization

    CSV, JSON, Avro, Parquet, ORC format characteristics, schema evolution, columnar vs row-based storage, compression codecs, serialization frameworks

    Objectives

    • Compare common data formats and their trade-offs
    • Choose the right format for a given pipeline scenario

    Key concepts

    • CSV
    • JSON
    • Avro
    • Parquet
    • ORC
    • columnar vs row-based
    • compression codecs
    • schema evolution
2 · Data Extraction4 topics
  • Source Connectors

    JDBC/ODBC connectors, file-based extraction, cloud storage sources (S3, GCS, ADLS), SaaS connectors, connection pooling, credential management

    Objectives

    • Configure connectors for relational and cloud sources
    • Manage credentials and connection pools securely

    Key concepts

    • JDBC/ODBC
    • file-based extraction
    • cloud storage sources
    • SaaS connectors
    • connection pooling
    • credential management
    • connector configuration
    • source catalog
  • Change Data Capture

    Log-based CDC (Debezium, Oracle GoldenGate), trigger-based CDC, timestamp-based CDC, CDC event schemas, handling deletes and schema changes, exactly-once semantics

    Objectives

    • Compare CDC strategies and their trade-offs
    • Design a CDC pipeline with Debezium or equivalent

    Key concepts

    • log-based CDC
    • Debezium
    • trigger-based CDC
    • timestamp-based CDC
    • CDC event schema
    • handling deletes
    • schema evolution in CDC
    • exactly-once delivery
  • API Data Extraction

    REST API pagination, rate limiting, OAuth2 token management, webhook ingestion, GraphQL extraction, retry strategies, idempotent extraction

    Objectives

    • Implement robust API extraction with pagination and rate limiting
    • Handle authentication and retries for API data sources

    Key concepts

    • REST pagination
    • rate limiting
    • OAuth2 tokens
    • webhook ingestion
    • GraphQL extraction
    • retry with backoff
    • idempotent extraction
    • cursor-based pagination
  • Incremental Extraction

    Watermark columns, high-water mark pattern, partition-based extraction, full vs incremental loads, state management, idempotent re-runs

    Objectives

    • Implement incremental extraction using watermark patterns
    • Design idempotent extraction pipelines

    Key concepts

    • watermark columns
    • high-water mark
    • partition-based extraction
    • full vs incremental loads
    • state management
    • idempotent re-runs
    • extraction metadata
    • gap detection
3 · Transformation Patterns4 topics
  • Data Cleansing & Validation

    Null handling strategies, type casting and coercion, string normalization, outlier detection, constraint validation, data masking, PII handling

    Objectives

    • Apply data cleansing techniques for common data issues
    • Implement PII masking and constraint validation

    Key concepts

    • null handling
    • type casting
    • string normalization
    • outlier detection
    • constraint validation
    • data masking
    • PII handling
    • referential integrity
  • Schema Mapping & Evolution

    Source-to-target mapping, schema drift detection, schema registry (Confluent), backward/forward compatibility, auto-mapping tools, data contracts

    Objectives

    • Design source-to-target schema mappings
    • Handle schema evolution and drift in production pipelines

    Key concepts

    • source-to-target mapping
    • schema drift
    • schema registry
    • backward compatibility
    • forward compatibility
    • auto-mapping
    • data contracts
    • schema versioning
  • Aggregation & Enrichment

    Window functions, grouping and rollups, lookup tables, dimension enrichment, derived metrics, temporal aggregation, deduplication with ranking

    Objectives

    • Apply window functions and aggregations for data transformation
    • Enrich records with lookup and dimension data

    Key concepts

    • window functions
    • GROUP BY / rollup
    • lookup enrichment
    • dimension enrichment
    • derived metrics
    • temporal aggregation
    • deduplication
    • ranking functions
  • Slowly Changing Dimensions

    SCD Type 1 (overwrite), Type 2 (history), Type 3 (previous column), Type 4 (mini-dimension), Type 6 (hybrid), effective dating, surrogate keys

    Objectives

    • Implement SCD Type 2 with effective dating and surrogate keys
    • Choose the appropriate SCD type for a given business requirement

    Key concepts

    • SCD Type 1
    • SCD Type 2
    • SCD Type 3
    • SCD Type 6
    • effective dating
    • surrogate keys
    • natural keys
    • history tracking
4 · Loading Strategies4 topics
  • Bulk Loading Techniques

    COPY commands (Snowflake, Redshift, BigQuery), staged loading, parallel loading, transaction management, atomic swaps, truncate-and-reload

    Objectives

    • Execute efficient bulk loads into modern data warehouses
    • Implement atomic load patterns for data consistency

    Key concepts

    • COPY command
    • staged loading
    • parallel loading
    • transaction management
    • atomic swap
    • truncate-and-reload
    • load partitioning
    • error file handling
  • Upsert & Merge Operations

    MERGE statement, INSERT ON CONFLICT, deduplication on load, idempotent loads, conflict resolution strategies, soft deletes

    Objectives

    • Write MERGE statements for upsert patterns
    • Design idempotent load processes

    Key concepts

    • MERGE statement
    • INSERT ON CONFLICT
    • deduplication on load
    • idempotent loads
    • conflict resolution
    • soft deletes
    • matched/not matched
    • deterministic merges
  • Data Warehouse Loading Patterns

    Star schema loading order, fact and dimension table loading, surrogate key generation, late-arriving dimensions, conformed dimensions

    Objectives

    • Design loading sequences for star schema warehouses
    • Handle late-arriving dimensions and conformed dimension patterns

    Key concepts

    • star schema loading
    • fact table loading
    • dimension table loading
    • surrogate key generation
    • late-arriving dimensions
    • conformed dimensions
    • load dependency ordering
    • bridge tables
  • Data Lake & Lakehouse Loading

    Delta Lake, Apache Iceberg, Apache Hudi, partitioning strategies, compaction, time travel, ACID transactions on object storage, medallion architecture

    Objectives

    • Compare lakehouse table formats (Delta, Iceberg, Hudi)
    • Design medallion architecture with proper partitioning

    Key concepts

    • Delta Lake
    • Apache Iceberg
    • Apache Hudi
    • partitioning strategies
    • compaction
    • time travel
    • ACID on object storage
    • medallion architecture
5 · Pipeline Orchestration4 topics
  • Workflow Engines

    Apache Airflow DAGs, Prefect flows, Dagster assets, AWS Step Functions, task dependencies, sensors, operators, executor types

    Objectives

    • Design DAGs for complex ETL workflows in Airflow or equivalent
    • Choose the right orchestration tool for a given scenario

    Key concepts

    • Airflow DAG
    • Prefect flow
    • Dagster asset
    • task dependencies
    • sensors
    • operators
    • executor types
    • dynamic task generation
  • Error Handling & Recovery

    Retry strategies, dead-letter queues, circuit breakers, partial failure handling, checkpointing, idempotent pipelines, alerting on failure

    Objectives

    • Design fault-tolerant ETL pipelines with retry and recovery
    • Implement dead-letter queues and checkpointing for resilience

    Key concepts

    • retry strategies
    • dead-letter queues
    • circuit breakers
    • partial failure
    • checkpointing
    • idempotent pipelines
    • failure alerting
    • graceful degradation
  • Monitoring & Observability

    Pipeline metrics (latency, throughput, error rate), data quality dashboards, SLA tracking, log aggregation, distributed tracing, anomaly detection

    Objectives

    • Set up comprehensive monitoring for data pipelines
    • Define SLAs and anomaly detection for pipeline health

    Key concepts

    • pipeline metrics
    • data quality dashboards
    • SLA tracking
    • log aggregation
    • distributed tracing
    • anomaly detection
    • alerting thresholds
    • observability pillars
  • Scheduling & Dependency Management

    Cron-based scheduling, event-driven triggers, cross-DAG dependencies, data-aware scheduling, backfill strategies, SLA deadlines

    Objectives

    • Design scheduling strategies for complex pipeline dependencies
    • Implement backfill and catch-up mechanisms

    Key concepts

    • cron scheduling
    • event-driven triggers
    • cross-DAG dependencies
    • data-aware scheduling
    • backfill strategies
    • SLA deadlines
    • sensor-based triggers
    • dependency resolution
6 · Enterprise Data Integration2 topics
  • Data Governance & Lineage

    Data catalog (DataHub, Amundsen, OpenMetadata), column-level lineage, data classification, access control, compliance (GDPR, CCPA), metadata management

    Objectives

    • Design a data governance framework with lineage tracking
    • Implement compliance controls for sensitive data in pipelines

    Key concepts

    • data catalog
    • column-level lineage
    • data classification
    • access control
    • GDPR compliance
    • metadata management
    • data stewardship
    • impact analysis
  • Scalability & Performance

    Horizontal scaling of pipelines, partitioning strategies, parallel execution, resource optimization, cost management, multi-region replication, capacity planning

    Objectives

    • Design scalable ETL architectures for enterprise workloads
    • Optimize pipeline cost and performance at scale

    Key concepts

    • horizontal scaling
    • partitioning strategies
    • parallel execution
    • resource optimization
    • cost management
    • multi-region replication
    • capacity planning
    • auto-scaling

Levels: Junior, Mid, Senior, and Expert

Explain it in your own words

Graded by AI · no account · 1 question

No options to guess from: you write, the AI grades against the topic's rubric and tells you what level your answer reads at.

0 / 600

Already studying ETL/ELT Architecture?

The assessment measures topic by topic, and your plan skips what you already know.

Measure my level

Open to read. An account is only needed to be measured.

Frequently asked questions

Where should I start studying ETL/ELT Architecture?

Start with ETL Fundamentals: ETL vs ELT Paradigms, Batch vs Stream Processing, and Data Quality Basics. The map orders topics by what they depend on, so each one prepares the next.

How long does a full ETL/ELT Architecture assessment take?

A full assessment of this path takes about 45 minutes. Study time depends on where you start; a diagnostic shows which topics you can skip.

Do I need an account to use this page?

No. The map, objectives and key concepts are open. You only need an account to be measured, get your gap by topic and receive a plan.