ELT Modernisation: The Architectural Shift Your AI Stack Depends On

TL;DR:

ELT modernisation replaces brittle, monolithic ETL pipelines with cloud-native architecture where raw data lands directly in the warehouse and transforms run in-place as version-controlled SQL. The result is faster ingestion, better reproducibility, lower operational overhead, and an AI-ready data foundation. Success depends on a risk-scored inventory before any code is written, parallel-run validation before any cutover, and treating transformation code with the same engineering discipline as production software.

Why ETL Is No Longer the Right Default

Legacy ETL pipelines were built for a different era. Compute was expensive, storage was precious, and transforming data before loading it into the warehouse made economic sense. Those conditions no longer apply.

Cloud object storage is now cheap enough to retain raw data indefinitely. Elastic warehouse compute in Snowflake, BigQuery, and Databricks scales on demand rather than running on a fixed server. The economic case for transforming before loading has largely disappeared for analytical workloads.

What remains is the technical debt of ETL architectures that were never designed for modern scale: business logic buried in undocumented stored procedures, monolithic SSIS packages maintained by engineers who left years ago, and transformation engines that fail silently when upstream schemas change. Consequently, the question for most enterprise data teams is not whether to modernise but how to do it without breaking the pipelines that production analytics depends on.

ETL vs ELT: What Actually Changes

ELT and ETL describe the same three operations in a different order, and that order changes almost everything about cost, ownership, and failure modes.

ETL extracts from source systems, transforms in a dedicated external runtime such as Informatica PowerCenter or SSIS, then loads the cleaned result into the warehouse. The transformation engine owns state, manages retries at the row level, and applies business logic before data ever reaches its destination.

ELT extracts from source systems, loads raw data directly into the warehouse, then transforms in-place using the warehouse’s own compute engine.

The operational shift matters as much as the technical one. Transformation ownership moves from a proprietary runtime to SQL-based code running inside the warehouse. Raw data is retained, which means analysts can re-transform historical records when business logic changes without re-extracting from source systems. That reprocessability alone eliminates entire categories of incident response work.

When to Keep ETL and When to Move to ELT

ELT is the right default for analytical workloads. However, several conditions still favour ETL or a hybrid approach.

Keep ETL when real-time, low-latency requirements exist for operational decisions like fraud scoring at transaction time. Also Keep ETL when pre-load data masking is mandatory, meaning regulated data flows where PII must be masked before it enters any storage layer. Keep ETL when complex stateful row-level logic is required that is harder to express cleanly as SQL.

Move to ELT when analysts need to answer novel questions on raw data. And move to ELT when your SLA is measured in hours, not milliseconds. Move to ELT when reproducibility, lineage, and AI/ML readiness are architectural requirements.

The strangler fig pattern works well for hybrid migrations. Route new data sources and new analytical workloads to the ELT stack while legacy ETL pipelines continue serving existing consumers. Dual-run critical pipelines in both stacks until parity is confirmed, then migrate consumers one at a time.

The Five-Phase ELT Modernisation Roadmap

Phase 1: Assessment — The Step Most Teams Skip

Skipping the assessment phase is the single most common cause of failed migrations. The hard part of ELT modernisation is not the technical rewrite. It is surfacing business logic that lives only in the memory of the engineer who built the original SSIS package in 2014.

Catalogue every pipeline: source, destination, schedule, SLA, and consumer list. Map embedded business logic, especially undocumented transformations in stored procedures. Score migration risk across data volume, consumer count, SLA sensitivity, and logic complexity. Identify the ten highest-risk pipelines for parallel-run validation. Treat these as your proof-of-concept batch before touching anything in production.

Edgematics’ Data and AI Maturity Assessment provides the structured discovery framework that gives leadership a risk-scored inventory before any migration commitment is made.

Phase 2: Design — Bronze, Silver, Gold

Decompose monolithic jobs into composable layers. The bronze layer lands raw source copies exactly as they arrive, preserving the complete audit trail. The silver layer applies cleaning, typing, and deduplication. The gold layer holds business-ready marts that BI tools and AI models consume.

Define data contracts at each boundary: agreed schema, SLA, and quality expectations between producers and consumers. Organisations that skip contracts consistently encounter shareability and reusability problems that undermine the entire ELT investment.

Phase 3: Build and Validate

Implement dbt models as version-controlled SQL, one model per logical transformation. Add schema tests, referential integrity tests, and business-rule assertions per model. Gate promotion to production on test passage. Never decommission the legacy pipeline until parity is confirmed.

Pro Tip: Define your parity threshold before the parallel run starts. Row count match alone is insufficient. Agree on acceptable variance for numeric aggregates and document the reconciliation methodology so stakeholders sign off on the cutover criteria before the engineering team does.

Phase 4: Orchestration and Operations

Wire DAGs in Apache Airflow, Dagster, or Prefect to manage dependencies, retries, and backfill windows. Implement idempotent incremental logic using MERGE patterns so reruns produce identical results. Before cutover, run a deliberate chaos exercise: rerun the last seven days of incremental models and confirm the output is identical. If it is not, you have a non-idempotent model that will cause production incidents. Fix it before the legacy pipeline is decommissioned, not after.

Phase 5: Cutover and Optimise

Three cutover patterns each carry a distinct risk profile. The strangler fig migrates workloads incrementally with the lowest risk and longest timeline. Parallel run operates both stacks simultaneously for a defined window with the highest confidence but doubled costs during migration. Selective big bang decommissions low-risk, low-consumer pipelines in a single cutover and is acceptable only for non-critical flows.

For enterprises carrying significant legacy ELT pipeline debt, Edgematics’ AI-powered Migration Toolkit cuts migration time and cost by 50 to 70% across source and target platforms, compressing the build phase without sacrificing the validation rigour that regulated workloads require.

The Modern ELT Stack: Tools by Layer

The modern ELT stack is a set of specialised, composable tools, each owning a distinct layer. This is the key architectural difference from legacy monolithic platforms that bundled ingestion, transformation, scheduling, and monitoring into a single proprietary runtime.

Ingestion: Fivetran for managed, low-maintenance connectors to SaaS sources. Airbyte for open-source, extensible connections and CDC patterns. Both handle schema evolution and deliver raw data to the warehouse without transformation.

Warehouse/lakehouse: Snowflake for separation of storage and compute with strong governance. BigQuery for serverless architecture and built-in ML integration. Databricks for lakehouse architecture and SQL analytics directly on data lakes.

Transformation: dbt is the de facto standard for the T in ELT, turning SQL SELECT statements into versioned, tested, and documented models.

Orchestration: Apache Airflow for mature Python-native DAGs. Dagster for asset-centric design with strong observability. Prefect for developer-friendly dynamic workflows.

Quality and observability: dbt’s built-in test framework for structural integrity. Great Expectations for expectation suites. Monte Carlo for ML-driven anomaly detection and data reliability monitoring.

How dbt Enforces Engineering Discipline

dbt is what turns ELT from a pattern into a discipline. Models live in a Git repository. Every change goes through a pull request and code review before merging. CI pipelines run dbt test and dbt build on a staging environment, blocking merges when tests fail. This prevents silent regressions — the most common failure mode when analysts modify shared models without understanding downstream impact.

Teams that treat ELT modernisation as a mechanical one-to-one rewrite consistently reproduce the same fragility in the new stack. They port a 3,000-line stored procedure into a single dbt model with no tests, no documentation, and no modularisation. The model runs. The data looks right. Six months later, a schema change in a source system breaks it silently, and nobody knows which downstream reports are affected because there is no lineage.

The cultural shift that separates successful modernisation programmes is treating transformation code with the same rigour as application code: pull requests, code review, tests that block deployment, and generated documentation that a new team member can read on day one.

This is precisely the argument at the heart of Episode 6 of the Data Enablers Podcast, The Convergence of AI and Data Management. The episode makes the case that AI and data management have converged and that organisations treating modernisation as a purely technical IT project consistently underperform those treating it as a business-led capability build. Governance is a growth issue, not a control function. For any data engineering leader navigating a modernisation programme and needing to make that case internally, it is a direct and commercially grounded conversation.

Pro Tip: Keep dbt models narrow and single-purpose. Name models by their grain and purpose. Write at least one test per model and treat a model with no tests as a deployment blocker in your CI configuration.

Governance, Lineage, and Compliance in ELT Pipelines

ELT’s flexibility is also its governance risk. Raw data lands in the warehouse before quality controls are applied, which means governance must be built into the transformation layer explicitly.

Data Contracts and Publish Gating

Data contracts formalise the schema agreements, SLA commitments, and quality expectations between producers and consumers. CI pipelines must block model promotion when tests fail. No untested model should reach a gold layer that BI tools or AI models consume.

Lineage and Compliance Controls

dbt’s dependency graph produces column-level lineage artifacts that auditors can inspect to trace any figure back to its source. For US-specific compliance, data residency controls and HIPAA-applicable de-identification must be configured at the warehouse and ingestion layer before raw data lands, not retrofitted after the first audit.

Edgematics’ Data Engineering and Governance practice embeds these controls into pipeline architecture from the first sprint, flagging 95% of data issues before they reach production and ensuring lineage is captured automatically rather than reconstructed under pressure.

Pro Tip: Gate gold-layer publishes with a mandatory test suite, but keep silver-layer tests lightweight and focused on structural integrity. Over-testing intermediate models slows CI without improving auditability.

Cost Management in ELT Architecture

Cost in ELT is variable and query-driven, which is both the advantage and the risk. Unlike a fixed ETL server with predictable monthly spend, warehouse compute scales with query complexity, frequency, and data volume.

Poorly written dbt models that scan full tables on every run generate significant unexpected compute spend. Incremental models and clustering reduce this dramatically. Materialization frequency is a key lever: views are free to define but expensive to query repeatedly; tables pay compute at build time and are cheap to query.

Dual-run overhead doubles production environment costs for the duration of the parallel run. Budget for this explicitly and define a clear timeline for cutover rather than allowing parallel operation to extend indefinitely.

The break-even point between ETL server costs and ELT warehouse compute depends heavily on query volume and incremental model coverage. Model this before committing to a target platform rather than discovering the economics mid-migration.

Key Takeaways

What We Have Learned From Modernisation Programmes

The most consistent finding across modernisation engagements is that the technical migration is rarely the hard part. The hard part is surfacing the business logic that lives nowhere except in the memory of the engineer who built the original package years ago.

In one engagement, a financial services client migrated 200+ Informatica PowerCenter workflows to a dbt-on-Snowflake stack. Using agent-assisted migration techniques, the team reduced the build phase by approximately 50%. The parallel-run validation phase consumed the majority of the remaining timeline. Post-migration, the client saw fewer on-call incidents, no proprietary licence renewals, and analyst self-service on the gold layer within 60 days of cutover.

Critically, the parallel run surfaced three pipelines where legacy execution semantics produced results that differed from the ELT equivalent due to implicit ordering assumptions in the original packages. None of these would have been caught by row-count reconciliation alone. Business-level KPI comparison is non-negotiable for financial and regulated workloads.

Edgematics Group

How Edgematics Supports ELT Modernisation

Edgematics delivers end-to-end ELT modernisation programmes covering assessment, decomposition, CI/CD implementation, and dual-run validation. Our Data Engineering and Governance solutions cover architecture design, dbt model development, data quality management, lineage, and compliance frameworks across complex regulated environments. The Data Strategy practice scopes the right migration path before any infrastructure investment is committed. Our AI and Machine Learning practice connects the modernised ELT foundation to production-grade models and analytics workflows. For enterprises evaluating where to start, the Data and AI Maturity Assessment produces a prioritised migration roadmap and risk register you can take to your leadership team.

Book a Discovery Call to start the conversation.

FAQ

Is ELT better than ETL?

For cloud-native analytical workloads, ELT is the better default. It delivers faster data availability, elastic scalability, and reprocessability without separate transformation infrastructure. ETL remains the right choice when pre-load data masking, real-time operational processing, or complex stateful logic is required.

Is ETL outdated?

ETL as a pattern is not obsolete, but on-premises monolithic platforms like Informatica PowerCenter and Microsoft SSIS are increasingly difficult to justify. Modern ETL replatformed onto managed cloud services remains valid for regulated and latency-sensitive workloads.

Will AI replace ETL?

AI-assisted tooling is already compressing migration timelines, with agent-assisted approaches reporting 40 to 60% reductions in build time for typical estates. AI accelerates the migration and helps generate dbt models from legacy exports, but it does not eliminate the need for engineering judgement, governance design, or parallel-run validation.

What is modern ELT?

Modern ELT is a cloud-native architecture where raw data lands in a warehouse or lakehouse such as Snowflake, BigQuery, or Databricks, and transformations run in-place using SQL-based tools like dbt, orchestrated by Airflow, Dagster, or Prefect, with version control, automated tests, and generated lineage as standard operating practice.

How long does ELT modernisation take?

Timeline depends on pipeline complexity, refactoring debt, and the migration path chosen. A structured assessment before scoping reduces estimation errors significantly. Agent-assisted migration techniques can compress the build phase by 40 to 60% for typical estates, though the parallel-run validation phase typically remains the longest stage for regulated workloads.