Data Engineering Fundamentals, Part 3: ETL/ELT Concepts Every Data Engineer Should Understand

 

This is Part 3 of a 5-part series on data engineering fundamentals. Part 2 covered Python basics, link at the top once published. Part 4 moves into data warehousing with a focus on Snowflake.

ETL vs ELT, and why the difference matters

ETL, Extract, Transform, Load, means data is transformed before it reaches the warehouse, usually on a separate processing server. ELT, Extract, Load, Transform, loads raw data into the warehouse first and transforms it there using the warehouse's own compute. Most modern stacks have shifted to ELT because cloud warehouses like Snowflake are powerful and cheap enough to handle transformation directly, and it keeps a copy of raw, untransformed data available if your transformation logic needs to change later.

I'll use Matillion as the example tool throughout this post, since it's a widely used ELT platform, but these concepts apply across tools like Fivetran, Airbyte, or Azure Data Factory too.

Core concepts that matter regardless of tool

Extraction and connectors. Every pipeline starts with pulling data from a source, a database, a SaaS platform's API, a file drop. Understanding how a tool authenticates with and paginates through a source is often more important than the transformation logic itself.

Orchestration vs transformation. Keep these conceptually separate even if your tool blurs the line visually. Orchestration jobs control sequencing, error handling, and notifications. Transformation logic actually reshapes the data. Mixing the two makes pipelines hard to debug and hand off to a teammate.

Incremental loads vs full loads. Reloading an entire source table every run is simple but wasteful and slow as data grows. Incremental loading, pulling only new or changed records since the last run, usually based on a timestamp or an ID watermark, is what most production pipelines actually need.

Change Data Capture (CDC). A more advanced form of incremental loading that captures inserts, updates, and deletes directly from a source database's transaction log, rather than querying for changes. Useful when you need near real-time freshness or when a source doesn't have a reliable "last updated" column.

Idempotency. A well-designed pipeline should be safe to run twice without creating duplicate data. This usually means using upserts (merge statements) rather than blind inserts, so a rerun after a failure doesn't corrupt your target table.

Error handling and monitoring, not an afterthought. Production pipelines need proper logging, retry logic, and alerting built in from day one. A pipeline that works perfectly in testing but has no visibility when it fails in production isn't actually production-ready.

How this looks in Matillion specifically

Matillion structures work into orchestration jobs (controlling flow and sequencing) and transformation jobs (doing the actual SQL-pushdown transformation work inside your warehouse). It uses Shared Jobs for reusable logic and grid variables to loop over multiple tables or sources without duplicating job logic. Under the hood, it pushes transformation SQL down to run inside Snowflake or your chosen warehouse, which is the ELT pattern in action, Matillion orchestrates, the warehouse does the heavy lifting.

Why these concepts outlast any specific tool

Tools come and go, this industry has moved through Informatica, SSIS, Talend, and now Matillion, Fivetran, and dbt within a couple of decades. But incremental loading, idempotency, orchestration versus transformation, and proper error handling are concepts you'll carry with you regardless of which tool your next employer happens to use. Learn the concepts through one tool, and you'll pick up the next tool far faster.

Where to go from here

Once you understand how data actually moves and transforms, the next question is where it lands. Part 4 covers data warehousing concepts with a deep focus on Snowflake, one of the most widely adopted cloud warehouses today.

[Continue to Part 4: Data Warehousing and Why Snowflake Stands Out]