Extract, transform, load (ETL) definition
Extract, transform, load (ETL) is a data integration process in which data is pulled from one or more source systems, converted into a consistent structure and format, and then written into a target system such as a data warehouse. The transformation happens before loading, so the destination receives data that is already cleaned and conformed.
Extract, transform, load (ETL) is a three-step process for moving data between systems: read it from a source, reshape it into a common structure, then write it into a destination. Content is a frequent source. Migrating into Sanity typically means extracting documents from a legacy system, transforming them into the document types and Portable Text your schema defines, and importing the result, which is why modeling decisions get made during the transform step rather than after.
The three steps in extract, transform, load (ETL) are extract, transform, and load, performed in that order.
Extract reads data out of source systems: a relational database, an API, a spreadsheet export, log files, or a content repository. The goal is a faithful copy of the source, with as little interpretation as possible at this stage. Extraction can be a full pull of everything or an incremental pull of only what changed since the last run.
Transform is where the real work happens. Source records are cleaned (removing duplicates, fixing encodings, standardizing dates and currencies), validated against expected types, joined with data from other sources, and reshaped to match the target's schema. Business rules live here too: calculating derived fields, mapping legacy status codes to current ones, or splitting one messy field into several typed ones.
Load writes the transformed records into the destination, usually a data warehouse, a database, or an application's content store. Loads are often run as batches on a schedule, and they can either append new records or replace existing ones.
The difference between ETL and ELT is the order of the last two steps and where the transformation runs. In ETL, data is transformed in a separate processing layer before it reaches the destination, so only clean, conformed data lands. In ELT (extract, load, transform), raw data is loaded into the destination first and transformed there, using the destination's own compute.
ETL suits targets that enforce a strict schema and cannot accept malformed input, and it suits cases where sensitive fields must be masked or dropped before they are stored. Loading content into an application that validates documents against a schema is an ETL-shaped problem, because a half-formed document is not a valid document.
ELT became common as cloud data warehouses made storage cheap and compute elastic, letting teams keep the raw source data alongside the transformed version. That is useful for analytics, where you frequently want to re-derive a table after changing your mind about a transformation, without re-extracting from the source.
ETL is used for content migrations because the source system and the target system almost never model content the same way, and something has to reconcile them. A migration off a legacy platform is an extract (read every page, post, asset, and taxonomy term), a transform (map old templates to new document types, convert HTML bodies into the target's rich text representation, resolve internal links and asset references), and a load (write the results into the new system).
The transform step is where migrations get decided. Legacy content often carries formatting embedded in body HTML, inconsistent field usage across years of editors, and relationships expressed only as hardcoded URLs. Turning that into typed, structured documents is not a mechanical conversion, it is a modeling exercise, and it is normally iterative: run the transform, inspect the output, adjust the mapping, run it again.
For migrations into Sanity, the transform target is the schema you have defined, including document types, references between documents, and Portable Text for rich text. Because the schema is code, you can run the transform against it repeatedly in a development dataset before touching production content.
A reverse ETL pipeline moves data in the opposite direction from a conventional one: it extracts from the central warehouse or data store and loads into the operational tools where people work, such as a CRM, a support desk, an ad platform, or a marketing automation system.
The reasoning is that the warehouse usually holds the most complete picture of a customer or a product, but the warehouse is not where anyone takes action. Reverse ETL syncs the useful parts of that picture back out, so a salesperson sees a product usage score in their CRM record rather than in a dashboard they never open.
Content systems participate in both directions. Content can be a source for analytics and search indexes, and enriched data can flow back in to populate fields such as product specifications, inventory status, or localized pricing. The transform step in either direction does the same job: reconciling two systems that describe the same things with different structures.
Extract, transform, load (ETL) breaks down when the destination needs data that is current within seconds rather than hours, because classic ETL is batch-oriented and runs on a schedule. A pipeline that runs nightly produces a destination that is, on average, half a day out of date. For financial reporting that is usually fine. For a search index, a personalization engine, or a retrieval layer feeding an AI assistant, it is not.
The common replacements are streaming pipelines and event-driven syncs. Instead of scanning the source for changes on a timer, the source emits an event when something changes, and a downstream process reacts to that single change. The transformation logic is often identical to what an ETL job would do. What changes is the trigger and the unit of work: one record at the moment it changed, rather than every record at 2am.
The practical pattern in our experience is a hybrid. Teams keep a batch job for backfills, reconciliation, and the initial load, and add event-driven syncs for the ongoing delta. The batch job is the safety net that catches whatever the event stream missed.
Unlock New Possibilities with Sanity
With extract, transform, load under your belt, it's time to see what Sanity can do for you. Explore our features and tools to take your content to the next level.
Last updated: