ETL/ELT: Fivetran + dbt + DWH Is the Modern Default

About this article

As the fifth installment of the “Data Architecture” category in the series “Architecture Crash Course for the Generative-AI Era,” this article explains ETL / ELT.

In the AI era, transformation logic not written in code can’t be read by AI and becomes liability. This article explains the difference between ETL and ELT, modern typical compositions (Fivetran + dbt + BigQuery etc.), data-quality testing and lineage, recommended compositions by scale, and the structure where GUI ETL tools become liability in the AI era.

What is ETL/ELT in the first place

ETL/ELT is “the mechanism for gathering scattered data into one place and shaping it into a form easy to analyze.”

Imagine prepping ingredients for a kitchen. You procure ingredients from the greengrocer, fishmonger, and butcher (business DBs, SaaS, logs) - that’s Extract. You wash, cut, and season them - that’s Transform. You organize and store them in the fridge - that’s Load. Without this prep, when an order comes in you’d have to run out to buy ingredients. ETL automates this prep process in the world of data.

Why ETL / ELT is needed

There are three reasons. Running aggregation queries on the operational database (OLTP) gets in the way of the operational workload, so the data has to be copied to a separate database for analysis. Company-wide analysis is impossible unless the operational database, the SaaS products (Salesforce, Stripe and so on) and the logs are gathered in one place. And raw data is full of inconsistent notation, duplicates and gaps, so nothing meaningful comes out of it without cleansing. A data pipeline is the circulatory system of a data platform: block it and analysis across the company stops.

The difference between ETL and ELT

Since cloud DWHs got fast enough, ELT - transforming inside the DWH - has become mainstream. The era of preparing a separate transformation server with ETL is ending.

The typical modern setup

Modern data pipelines flow as “extract → load → transform → utilize,” with role-specialized tools used per stage. As best practice, the following composition has settled in.

[Business DB / SaaS]
      │
      ▼ Fivetran / Airbyte (extract+load = EL)
[DWH: BigQuery / Snowflake]
      │
      ▼ dbt (transform = T)
[tidy data marts]
      │
      ▼ Looker / Tableau / Metabase
[Dashboards / BI]

Extract + load (EL) is handled by Fivetran, transform (T) by dbt - this layer split is the modern standard pattern.

The EL layer — hand it to Fivetran or Airbyte

Tools that automate ingestion from business DBs and SaaS to the DWH. Previously it was normal to write custom scripts per system, but today connector-equipped SaaS tools are common, ingesting from hundreds of sources with just connection settings.

Fivetran (under Starburst) is the industry-standard managed EL. It provides 300+ source connectors, syncing automatically from Salesforce, Stripe, PostgreSQL, etc. to the DWH with just connection settings. Differential sync, schema-change tracking, and retries are all automated, and the strength is barely needing engineer hands - but data-volume billing can run hundreds of thousands to millions of yen monthly.

Airbyte is the OSS version of Fivetran, available for free via self-hosting (also has a paid cloud version). Connector counts keep growing, and it’s the top choice for cost-conscious mid-size companies. Stability and support don’t yet match Fivetran, but the functional gap has narrowed year by year.

The T layer — dbt is the de facto standard

Tools that transform data inside the DWH with SQL to prepare for analysis. The modern de facto standard is dbt (data build tool, the tool defining data-transformation pipelines in SQL), providing the mechanism to version-control + test + document SQL transformation logic.

Before dbt, transformation logic was scattered across SQL files, stored procedures, and Excel macros - and the accident of numbers no one knew who built or how showing up in executive meetings happened often. dbt revolutionized data platforms by solving this problem with “SQL + Git + tests.”

It’s harder to find a reason not to use dbt - it’s the de facto standard for modern data transformation.

Orchestration — only once you need it

Pipelines need scheduling and dependency management like “run at 2am daily” or “run next when the prior job succeeds.” This is handled by an orchestrator - Airflow has been the longtime de facto, with Prefect and Dagster also rising today.

For scales completable with dbt Cloud / Fivetran’s scheduling features, a dedicated orchestrator isn’t needed.

How to choose — by scale and freshness

Freshness requirements decide the setup. If “a report every morning” is enough, a nightly batch does it; if a few minutes of delay is acceptable, a micro-batch on a fifteen-minute cycle; if you need seconds, streaming (the subject of the next article). Most business requirements are satisfied by daily, and reaching for real time without a reason raises cost and complexity sharply.

Numeric gates on pipeline quality

Industry baseline values as of April 2026.

The modern standard is to make dbt tests mandatory in CI and stop the pipeline on a quality violation. It is the single largest investment against the classic accident of “the monthly report looks wrong — and tracking down why takes weeks.”

Three scenarios

If you are building solo or at a startup

With zero data engineers, the setup to aim at is Fivetran plus dbt Cloud plus BigQuery — a minimum configuration that runs on SQL alone, from around 00 a month. Delegate all connector maintenance to the SaaS and write no scripts of your own. While the analytical requirements are light, choosing to have no ETL at all is also a perfectly good answer.

Personal / Startup: Ship in One Month Is Correcten.senkohome.com/arch-intro-case-startup/

If you are a small or mid-size SaaS

With SRE people available, open source works: Airbyte, dbt Core and Airflow keep costs down and stay flexible. The thing worth establishing at this stage is making dbt tests mandatory in CI and stopping the pipeline on a quality violation. Skip that and you will meet the classic accident of “the numbers in the monthly report look wrong.”

Small-Mid SaaS - Lean on Managed and Run with Few Peopleen.senkohome.com/arch-intro-case-saas/

If you are a large enterprise

Where control matters, Fivetran and dbt are joined by a catalogue such as DataHub, with commercial support secured. As the number of pipelines grows, nobody can tell the blast radius of a failure unless lineage and ownership are centralised in that catalogue.

Large-Enterprise Core: Design That Holds Up for Yearsen.senkohome.com/arch-intro-case-enterprise/

AI decision axes — Transformations not written as code are unreadable to AI

dbt’s SQL + YAML model is the optimal input/output format for AI

In dbt, data transformation logic is written in SQL and model metadata is defined in YAML. Both are formats AI can accurately generate and modify. For instructions like “write a dbt model creating monthly sales summary from the orders table,” AI can accurately output SQL files under models/ and schema.yml.

Data lineage visualization guarantees reliability of AI-generated code

When data transformation paths are visualized with OpenLineage or dbt docs, you can trace “which source tables does this AI-generated dbt model depend on” and “which downstream dashboards does it affect.” Without lineage, there’s risk of AI-generated model defects propagating downstream.

Pitfalls and forbidden moves

Here are the six most dangerous direct causes of numbers you cannot trust.

Author’s note - the 1 person-month sucked by “the army of self-built scripts”

There’s a story often heard about a mid-size company that hand-built “10+ Python scripts for SaaS integrations one by one” for Salesforce, Stripe, Zendesk, etc. - one would break with each vendor schema change, and before they noticed, one young engineer’s worth of effort went entirely to “pipeline maintenance.” The motivation to “not pay SaaS billing” started self-building, but the typical pattern was that in personnel costs, it cost several times more than the billing.

In another case, an organization had transformation logic scattered across SQL files, stored procedures, and Excel macros - critical transformations remained only on the PC of a person who left the company. The numbers in executive meetings stopped for months, and half a year was needed to rebuild. Effort that wouldn’t have been spent had it been Git-managed in dbt.

I myself once watched a Python-script integration scenario where, the moment the rookie took over, vendor API changes broke it weekly and patches were rushed at night. These cases teach the value of code visualization and standardization. The tens of thousands of yen monthly for Fivetran + dbt is overwhelmingly cheap as an investment compared to the risk of self-building and person-locking - that’s the modern conclusion.

Self-built scripts always lose by the hour. Fivetran/Airbyte billing is recouped in personnel costs.

What to decide - what is your project’s answer?

For each of the following, try to articulate your project’s answer in 1-2 sentences. Starting work with these vague always invites later questions like “why did we decide this again?”

• Extract tool (Fivetran / Airbyte / self-build)
• Transform tool (dbt as top candidate)
• Orchestrator (Airflow / Prefect / not needed if light)
• Data catalog (DataHub / dbt docs / not needed)
• Data quality tests (required dbt test items)
• Schedule frequency (daily / hourly / real-time)
• Failure notification destinations (Slack, PagerDuty)

Related Articles

Summary

This article covered ETL / ELT, including the difference between ETL and ELT, typical compositions, data-quality testing, lineage, phased recommendations by scale, and how GUI ETL becomes liability in the AI era.

ELT + dbt as the basic form, extraction with Fivetran/Airbyte, mandatory quality testing, and lean on a code-based + Git-managed approach. That is the practical answer for ETL/ELT design in 2026.

Next time we’ll cover streaming (handling real-time data).

Back to series TOC -> ‘Architecture Crash Course for the Generative-AI Era’: How to Read This Book

I hope you’ll read the next article as well.