How to Streamline ETL Processes in Travel and Hospitality with Luigi?

ETL in Data Warehousing: A 2026 Guide

The ETL process in data warehousing extracts raw data from source systems, transforms it to match a target schema and business rules, then loads it into a central warehouse for analytics and reporting. It is the pipeline layer that turns incompatible, fragmented data from databases, SaaS tools, APIs, and IoT devices into something analysts and AI models can reliably use.

In most companies, sales data lives in Salesforce, finance uses a different system, and the logistics team has its own database with a schema nobody outside the department fully understands. Someone, usually a data engineer, has to make all of it usable in one place.

That is not a new problem. What has changed is the scale: more sources, more formats and faster accumulation. The teams responsible for that data need infrastructure that does not buckle under it.

ETL (extract, transform, load) is still the foundation of most of that infrastructure, though not always in its traditional form and often alongside newer approaches such as ELT.

The ETL Pipeline: Five Stages, Not Three

The textbook version of ETL has three steps: extract, transform, load. Production pipelines have five, and the two missing from the textbook diagram, validation and monitoring, are often where things go wrong.

Extract

Extraction pulls raw data from source systems. In practice that means relational databases, cloud applications, flat files, REST APIs, streaming platforms, IoT sensors, and whatever combination of systems a given organisation has built up, often over many years, often without a unified data strategy behind any of it.

Two approaches exist. Full extraction pulls everything, every run. It is straightforward to implement and tends to become unmanageable as datasets grow. Incremental extraction pulls only what has changed since the last run, and what that looks like in practice depends on the source. For relational databases, it is usually log-based CDC, which reads transaction logs (WAL in Postgres, binlog in MySQL) to capture inserts, updates, and deletes as they happen. For SaaS sources like Salesforce or HubSpot, incremental sync typically relies on API queries filtered by a high-watermark column such as updated_at, which is closer to timestamp polling than CDC in the strict sense. Trigger-based CDC is a third variant, where database triggers write changes to shadow tables for downstream consumption. For any pipeline running against large or frequently updated sources, incremental in one form or another is the practical default.

Validate and Stage

Extracted data does not go straight into transformation. It lands in a staging area first, a temporary workspace where validation rules run before anything downstream is affected.

Validation catches nulls in required fields, type mismatches, duplicate records, values outside expected ranges, and referential integrity problems where records reference IDs that do not exist in the parent table. These are ordinary data quality issues that appear constantly in real-world source systems.

Errors caught in staging are contained. The same errors found after three months of reporting has been built on them are much harder to fix.

A few production realities sit on top of the basic checks. Schema drift, where a source system quietly adds or renames a column without warning anyone downstream, will break any pipeline that assumes a static structure, and handling it explicitly beats finding out at 3am. Loads need to be idempotent, meaning that a rerun produces the same result instead of duplicating rows, and that property does not come for free. Backfill, filling in historical data after a new source comes online or transformation logic changes, is a different workload from regular incremental sync and worth planning for separately. Data contracts, explicit agreements between producers and consumers about what a field means and how it may change, help prevent this kind of breakage.

Transform

This is the stage most people mean when they talk about ETL work. Raw data gets restructured, cleaned, standardised, and reshaped to match the warehouse schema and whatever business rules apply.

The scope of transformation varies enormously by context. A basic pipeline might just remap field names and convert date formats. A complex one handles currency normalisation across multiple sources, deduplication logic that requires fuzzy matching, aggregation across incompatible time granularities, and compliance requirements such as masking PII before it reaches storage, generating the audit trail that a regulator will eventually request, and enforcing data minimisation rules under GDPR.

That last category tends to be underestimated at the design stage. Compliance logic built into transformation is far easier to audit and maintain than compliance logic scattered across downstream systems.

Load

Transformed data moves into the target system. Cloud data warehouses such as Snowflake, BigQuery, and Amazon Redshift are the most common destination now, though data lakes and lakehouse architectures are also widely used depending on the use case.

Four loading strategies are common in practice. Full load replaces warehouse contents entirely each run, which is fine for small reference tables and wasteful for anything larger. Incremental append adds only new records and works well for immutable event data where history never changes. Incremental merge, usually called upsert, inserts new records and updates existing ones matched by primary key, typically via a SQL MERGE statement. Partition overwrite replaces specific partitions wholesale, which is efficient when data is naturally partitioned by date or region. The right choice depends on data volume, how often the source changes, query patterns and storage cost tolerance.

The Load stage also has to handle something textbook descriptions tend to gloss over: dimensions that change over time. Slowly Changing Dimensions, or SCDs, are the standard vocabulary for it. Type 1 simply overwrites the old value, which is easy and loses history entirely. Type 2 keeps the full history by adding a new row whenever a value changes, with effective-from and effective-to columns marking the validity window. Anyone doing analytics where historical accuracy matters, say attributing a sale to the account owner at the time of the deal rather than whoever owns the account today, needs SCD Type 2 for at least some tables. dbt has supported it natively through snapshots since version 0.14.0, released in July 2019, when snapshots replaced the earlier archives feature.

Monitor and Audit

Without observability, a pipeline fails without warning.

Monitoring covers row count reconciliation between source and target, latency tracking, alerts when jobs fail or run longer than expected, data lineage documentation that traces any record back to its origin, and scheduled quality checks that verify the data matches expectations, not just that the pipeline completed.

Row count reconciliation is the simplest check, and it only catches gross failures. More reliable approaches include hash or checksum comparison across partitions, and sample-based row diff. Freshness SLAs deserve the same weight as failure alerts. Stale data often looks fine on downstream dashboards, so nobody notices until a decision has been made on the wrong numbers.

Apache Airflow and Dagster handle scheduling and orchestration; frameworks such as Great Expectations apply continuous quality rules. Without this layer, teams stop trusting the warehouse and start working around it.

ETL vs ELT: Where They Actually Differ

The distinction is about sequencing, not technology.

ETL transforms data before it enters the warehouse. A processing layer between source and destination handles business logic first, then sends clean data downstream. This model made sense when warehouse compute was expensive and storage was constrained. Loading raw, unprocessed data was not a viable option.

ELT reverses it. Data gets extracted and loaded into the warehouse in raw form, then transformed in place using the warehouse's own compute. The approach became practical when cloud warehouses such as Snowflake, BigQuery, and Redshift arrived with elastic scaling. Loading raw data became cheap and fast. SQL transformations running inside the warehouse became more maintainable than transformation logic sitting in a separate layer.

dbt (data build tool) formalised this shift. By treating SQL models as version-controlled, testable code running inside the warehouse, dbt gave data teams a software engineering discipline for what had previously been a collection of scripts and stored procedures. By 2026, ELT with dbt as the transformation layer is the default for most cloud-native data teams.

ETL still makes sense in specific situations: when data must be masked before it reaches any storage system at all, when the target is an on-premises warehouse that cannot accept raw input, when transformation logic requires specialised compute that SQL cannot handle, or when data residency rules prevent raw data from entering a cloud environment. Most mature organisations run both. ELT for the bulk of pipelines, ETL for the flows where pre-load processing is a hard requirement.

A newer pattern is zero-ETL: cloud-native integrations where data flows from a source system directly into the warehouse without a separate pipeline layer in between. AWS Aurora replicating to Redshift is one example, Snowflake's direct connection to Salesforce Data Cloud is another. It does not replace ETL or ELT in most cases, but for specific source-to-warehouse flows inside a single vendor ecosystem, it removes a lot of pipeline code.

Real-Time vs Batch: Picking the Right Model

Not every pipeline needs to move data in real time. Applying streaming infrastructure to problems that batch processing handles well is one of the more expensive habits in data engineering.

Batch pipelines move data at scheduled intervals: hourly, nightly, weekly, depending on the use case. End-of-month financial reporting. Weekly marketing attribution. Catalogue sync that runs at 2am when source system load is low. Batch is simpler to build, easier to debug when something breaks, and considerably cheaper to operate than streaming alternatives.

Streaming ETL processes data continuously. Apache Kafka or AWS Kinesis carry the events; Flink or Spark Streaming handle transformation on the fly. Results appear in the warehouse within seconds of the originating event. For fraud detection, where the decision window might be a single transaction, that latency matters. The same goes for live inventory during a high-traffic trading period, or patient monitoring systems where delays have clinical implications.

The distinction between true streaming and microbatch is worth understanding before anyone picks a tool. Flink handles events one at a time with very low latency and is the closest thing to continuous processing. Spark Structured Streaming, despite the name, actually processes data in small batches (typically every few seconds), which is usually enough for analytics use cases and tends to give better throughput for the compute cost. Kafka itself has two layers that get confused a lot: Kafka Connect moves data in and out of Kafka topics through source and sink connectors and is effectively an ingestion layer, while Kafka Streams is a client library for building stream-processing applications directly on top of Kafka without needing a separate cluster.

Two concepts cause more pain in streaming pipelines than anything else. The first is delivery semantics. At-least-once is the default in most systems and it means a given event may end up being processed more than once during a failover, which forces every downstream consumer to be idempotent. Exactly-once is achievable but comes with real cost in throughput and complexity, so it is worth being deliberate about where you actually need it. The second is time. Event time and processing time are not the same thing, events arrive late, out of order, or both, and streaming systems handle this through watermarks (how long the system is willing to wait for stragglers) and windowing (how events are grouped into finite chunks for aggregation). Misconfigured watermarks tend to show up as numbers that quietly drift from reality, and by the time anyone notices, the dashboards have been wrong for a while.

The common mistake is choosing streaming because it sounds more sophisticated, then spending engineering time maintaining infrastructure that a nightly batch job would have handled.

Most organisations at scale run both: streaming for the few data flows where latency affects outcomes, batch for everything else.

ETL in Cloud Environments

Cloud warehousing changed what ETL looks like in practice and who is responsible for building it.

On-premises ETL meant dedicated servers, licensed software, infrastructure teams and significant upfront capital expenditure. Cloud-native ETL, usually ELT in modern stacks, replaced most of that with managed services billed on consumption.

The current standard stack: a managed ingestion tool (Fivetran or Airbyte) extracts from sources and loads into the warehouse; dbt runs transformations as SQL models inside the warehouse; Airflow or Dagster orchestrates scheduling and monitoring; Snowflake, BigQuery or Redshift provides compute and storage. Each layer can be swapped independently, which avoids the vendor lock-in of earlier, monolithic data infrastructure.

One thing cloud ETL design consistently underweights: data residency. Organisations operating under GDPR, or handling cross-border flows between EU and non-EU jurisdictions, need explicit planning around which data moves where and in what state. Cloud convenience and regulatory compliance do not always align. Discovering the conflict after the pipeline is in production is substantially more expensive than designing for it upfront.

Open table formats such as Apache Iceberg and Delta Lake are increasingly part of the modern warehouse stack. They bring database-style transactional guarantees (ACID, schema evolution, time travel) to data stored as files in cloud object storage. Iceberg in particular is now supported across major platforms including Snowflake, Databricks, AWS via S3 Tables, and Google Cloud via BigLake, which makes lakehouse architectures far more practical than they used to be.

ETL Tools in 2026: What Teams Are Actually Choosing

Different tools lead in different parts of the pipeline, and most teams assemble a stack rather than look for one platform that does everything.

Fivetran is known for reliable, low-maintenance ingestion: over 700 pre-built connectors, automated schema management, and change data capture that handles database-level incremental sync without engineering involvement. Teams choose it when they need predictable, production-grade ingestion and do not want to staff a team to maintain it. Pricing is based on Monthly Active Rows (MAR), which is manageable at moderate volumes and worth modelling carefully before committing at scale. Since 1 March 2025, Fivetran has billed MAR per connection, which removed the bulk discount that used to apply across connectors; deletes count toward paid MAR, and there is a $5 base charge per standard connection. For multi-connector setups this changes the cost model, so projections made under the old pricing need revisiting. Fivetran and dbt Labs announced their merger in October 2025 and completed it on 1 June 2026, so the ingestion and transformation layers of this stack now come from one company.

Airbyte started as open source in 2020 and has grown to over 600 connectors, with community contributions covering sources that Fivetran does not offer. Self-hosted deployment is free; Airbyte Cloud provides a managed alternative. Self-hosting gives flexibility but needs engineering capacity to run reliably, a cost that rarely appears in the initial calculation, so teams choosing Airbyte to save money should model the full cost, not just the licence.

dbt handles transformation only: it does not extract or load anything. It runs SQL models inside the warehouse and treats them as version-controlled, testable code. dbt Core is the open-source CLI; dbt Cloud adds a managed environment with scheduling and collaboration features. In 2025, dbt Labs launched dbt Fusion, a Rust-based rewrite of the engine focused on faster performance and native SQL understanding. It is no longer in beta: dbt's documentation now describes it as dbt v2, the current generation of dbt and the default when you install it. In ELT workflows, dbt is effectively the standard transformation layer, and experience with it is a baseline expectation in most data engineering job descriptions.

Custom pipelines built on Kafka, Spark, Airflow or purpose-built frameworks remain the right answer when off-the-shelf tools cannot meet the requirement: proprietary source systems with no available connector, streaming use cases with transformation logic that SQL cannot express, or data sovereignty requirements that rule out SaaS platforms entirely. Custom development costs more and needs sustained engineering ownership, but when a requirement falls outside what managed tooling covers, it is the only workable option.

Where ETL Matters Most in Practice

The business case for ETL shows up where fragmented or delayed data already costs something: a decision made on stale information, a forecast built on incomplete inputs, a compliance audit that surfaces data quality problems nobody knew existed.

Ecommerce companies consolidate data from storefronts, logistics platforms and customer service tools into one warehouse for demand forecasting and inventory management. Financial services teams reconcile transactions across core banking systems and third-party data providers, with transformation logic that handles regulatory requirements, multiple currencies and audit trails. Healthcare organisations integrate patient records across clinical systems, lab feeds and insurance claims, with pre-load masking and HL7/FHIR compliance built into the transformation layer.

Machine learning teams depend on ETL more than it seems, because model quality depends on pipeline quality. Inconsistent, poorly transformed source data produces models that behave inconsistently, and debugging a model trained on a poorly managed dataset is much harder than fixing the pipeline before training begins.

When Managed Tools Are Not Enough

Fivetran and Airbyte were designed for the standard case and handle it well.

In the standard case, sources have connectors, transformation logic fits within dbt's SQL framework, compliance requirements do not restrict cloud data movement, and pipeline volumes are within the pricing tier that makes commercial sense. Many organisations fit that profile.

Others do not. A logistics operation tracking tens of thousands of vehicles in real time cannot run on nightly batch ingestion. A financial institution under strict data residency requirements cannot route unmasked records through a US-hosted SaaS platform. A healthcare provider integrating a proprietary clinical system has no connector to wait for.

In those cases the question shifts from which tool to use to what custom development involves: pipeline architecture designed for the specific sources and data volumes, transformation logic that handles the compliance and governance requirements precisely, and engineering ownership of infrastructure that off-the-shelf tools would have maintained automatically.

Conclusion

The tooling for ETL in data warehousing has changed, with cloud warehouses, managed connectors and ELT-native transformation frameworks, but the underlying problem is the same: disparate source systems, incompatible formats, and a business that needs clean, reliable data to make decisions.

Getting the architecture right means picking the correct processing model for each data flow, choosing tools that match the actual compliance and operational requirements, and building monitoring infrastructure that surfaces problems before they reach the analysts depending on the warehouse.

For most organisations, managed tooling on standard cloud infrastructure handles this well. Proprietary sources, strict data residency, high-throughput streaming or custom transformation logic call for purpose-built pipelines.