---
title: "What Is an ETL Pipeline? Steps, Examples and Spatial ETL"
url: "https://wherobots.com/discover/etl-pipeline/"
description: "An ETL pipeline extracts, transforms and loads data. Learn the steps, ETL vs. ELT (extract, load, transform), and what changes for geospatial data."
published: "2026-11-09"
modified: "2026-11-09"
---

# What Is an ETL Pipeline? Steps, Examples and Spatial ETL

An extract, transform and load (ETL) pipeline is a set of steps that moves data from its sources into storage. It extracts the data, transforms it into a clean, consistent shape and loads it. For location data, the transform step needs three checks that a sales table never needs. Every layer must use the same coordinate system, the rule that maps a shape's numbers to places on Earth. Shapes must be valid and not empty. Map layers must join correctly to images. Without those checks, a spatial pipeline can pass every usual test on row counts and missing values and still produce wrong numbers.

We built a small spatial ETL pipeline in Wherobots to test that. It measured the mean normalized difference vegetation index (NDVI), a satellite measure of green leaves, within 500 m of each of 287 Denver schools. The inputs were Sentinel-2 satellite images and Overture Maps, open map data from the Overture Maps Foundation. Then we added three common mistakes, one at a time. Rounding longitude and latitude to 4 decimal places of a degree, a common way to shrink files, left 71,409 of Denver's 266,037 building outlines with no coordinates in our November 9, 2026 run. Most were small, with a median of 34.6 m², so total building area fell only 2%. Every emptied outline still passed `ST_IsValid`, the standard test of whether a shape is well formed, and the row count did not change.

![Map of 287 Denver schools, each colored by the mean normalized difference vegetation index (NDVI) within 500 m on June 21, 2025. Downtown schools are the least green: Academy of Denver 0.10 and Emily Griffith High School 0.14. The most green are in east and south Denver: Hill Middle School 0.58, University Park Elementary School 0.55 and Blessed Sacrament Catholic School 0.55.](https://wherobots.com/wp-content/themes/wherobots/assets/img/discover/etl-pipeline-schools-v2.webp)

**The pipeline’s output: the mean normalized difference vegetation index (NDVI) within 500 m of each of 287 Overture school records in Denver. NDVI is a score from -1 to 1 computed from red and near-infrared light, higher over green plants. The images come from June 21, 2025. For each day from June to August 2025, we took the cloudiest image tile over Denver, and June 21 had the lowest figure: 0.08% cloud cover. The median zone scored 0.35. Data: Overture Maps Foundation; Copernicus Sentinel-2 (European Space Agency). Analysis: Wherobots, November 9, 2026. Basemap: OpenFreeMap, OpenMapTiles, OpenStreetMap contributors.**

## Key takeaways

- An ETL pipeline extracts data from sources, transforms it and loads the result into a store such as a warehouse table. ELT (extract, load and transform) loads the raw data first and transforms it inside the store.
- Rounding Denver's 266,037 Overture building outlines to 4 decimal places of a degree emptied 71,409 of them, 27%. The rows stayed, but their shapes had no coordinates left. At Denver's latitude, that rounding snaps corners to a grid about 8.6 m east to west by 11.1 m north to south. The validity check `ST_IsValid` passed all of them, because an empty shape counts as valid. Total building area fell only 2%, because the emptied outlines were small: a median of 34.6 m², against 120 m² for all buildings.
- Two known coordinate mistakes also passed quietly. A buffer is the area within a set distance of a shape. Writing the 500 m buffer as `ST_Buffer(geom, 500)` on longitude and latitude drew each zone with a radius of 500 degrees, so each covered a whole Sentinel-2 image tile. The 287 schools got only 4 different NDVI values, one per tile, with no error. A radius of 0.0045 degrees, which reaches 500 m north and south of each school, drew ovals 771 m wide and 999 m tall on average, because at Denver's latitude a degree of longitude is shorter than a degree of latitude. The ovals had 23% less area than the zones drawn in meters, and none raised an error.
- Each shape carries a spatial reference identifier (SRID), a number that names its coordinate system. When we set the SRID of the 500 m zones to 0, Wherobots read their meter values as longitude and latitude. The step that averages image pixels inside each zone then returned no value for any of the 287 schools. The row count stayed at 287, so only a check for missing values caught it.
- How Wherobots did it: one Wherobots Job Run on November 9, 2026 read Denver's boundary, 289 school records and 266,037 building outlines from Overture Maps data in Wherobots Open Data. Dropping 2 duplicate schools left 287. It read only the image blocks inside the school zones from 4 Sentinel-2 tiles taken on June 21, 2025, without downloading the files. WherobotsDB, the Wherobots database engine, transformed and joined the data with Structured Query Language (SQL) queries that include spatial functions. The run loaded 287 rows to GeoParquet and an Apache Iceberg table and finished in 386 seconds on Wherobots' small runtime, the third-smallest size, including a check against a second image. Greenness comes from one summer day, and the school list from Overture's categories.

## What is an ETL pipeline?

[Databricks' ETL guide](https://www.databricks.com/discover/etl) defines ETL as the process data engineers use. In the guide's words, its steps are "to extract data from different sources, transform the data into a usable and trusted resource, and load that data into the systems end-users can access." The word pipeline means the steps run in order, as code, each time new data arrives or on a schedule.

ETL grew up with the data warehouse, a database built for reporting and analysis. The Kimball Group, founded by Ralph Kimball, lists [34 subsystems of the ETL architecture](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/etl-architecture-34-subsystems/) "that are required in almost every dimensional data warehouse back room." The back room is the processing behind a reporting database. Panos Vassiliadis reviewed the research in [A Survey of Extract-Transform-Load Technology](https://doi.org/10.4018/jdwm.2009070101), in 2009.

The three steps:

- **Extract:** read data from its sources. Sources include databases, files, application programming interfaces (APIs) and, for spatial work, map data and satellite images.
- **Transform:** turn the raw data into the shape its users need. That means fixing types, removing duplicates, joining sources, computing new columns and testing the result against rules.
- **Load:** write the result to the target, such as a warehouse table or files in cloud storage. A full load replaces the target. An incremental load adds or updates only the rows that changed.

Erhard Rahm and Hong Hai Do described data cleaning as a major part of ETL in [Data Cleaning: Problems and Current Approaches](https://dbs.uni-leipzig.de/research/publications/data-cleaning-problems-and-current-approaches), in 2000. They found it most needed where a pipeline combines sources that describe the same things in different ways.

## How an ETL pipeline works

A pipeline is code that a scheduler runs. A scheduler such as Apache Airflow starts each run at a set time or when new files arrive, and records whether the run succeeded. In Wherobots, a pipeline runs as a [Job Run](https://docs.wherobots.com/reference/python-sdk/jobs): a script that Wherobots runs on cloud machines of a chosen size. Wherobots also provides an [Airflow add-on](https://docs.wherobots.com/develop/run-operator) that starts a Job Run and waits for it to finish.

Each run follows the same pattern:

1. **Read only what changed.** The pipeline filters each source by date, area or update time, so it does not read everything again.
2. **Transform in an engine.** An engine is the software that runs the filters, joins and totals. For data too large for one computer, engines such as Apache Spark split the work across many machines, as the [PySpark](https://wherobots.com/discover/pyspark/) article explains.
3. **Test the output.** Tests check the row count, the columns and their types, missing values and business rules.
4. **Load, then reconcile.** After the write, the pipeline reads the target back and compares it with what it meant to write.

## ETL vs. ELT, and ETL pipeline vs. data pipeline

**ETL vs. ELT.** In ELT, the pipeline loads raw data into the target first and transforms it there with SQL. A data lake is cloud storage that holds raw files, and a lakehouse adds warehouse-style tables on top of those files. Cloud warehouses and lakehouses made ELT common, because they can run large transforms inside the store. ELT keeps the raw data, so a team can rerun a transform after it fixes a bug.

Michael Armbrust and coauthors described the cost of using a data lake and then a warehouse in [Lakehouse](https://www.cidrdb.org/cidr2021/papers/cidr2021_paper17.pdf), at the Conference on Innovative Data Systems Research in 2021. In many companies, they wrote, data is first transformed and loaded into a data lake, then loaded and transformed again into a warehouse. In their words, that creates "complexity, delays, and new failure modes."

**Data pipeline vs. ETL pipeline.** A data pipeline is any automated path that moves data between systems. An ETL pipeline is one kind. Other kinds copy tables unchanged between databases, or send warehouse data back to business applications.

**Streaming data pipelines.** A streaming pipeline processes each record as it arrives, such as a vehicle position or a sensor reading. Tyler Akidau and coauthors at Google described how to group endless streams, with records that arrive out of order, into time windows in [The Dataflow Model](https://www.vldb.org/pvldb/vol8/p1792-Akidau.pdf), in 2015. Armbrust and coauthors added [Structured Streaming](https://doi.org/10.1145/3183713.3190664) to Apache Spark in 2018. Streaming jobs there use the same code as scheduled batch jobs: SQL, or DataFrames, Spark's tables with named columns.

The post [Streaming spatial data into Wherobots](https://wherobots.com/blog/streaming-spatial-data-into-wherobots-with-spark-structured-streaming/) shows a spatial example.

**Data pipeline architecture.** A common layered design is the medallion architecture. In [Databricks' description](https://docs.databricks.com/aws/en/lakehouse/medallion), a bronze layer holds raw data, a silver layer holds cleaned and validated data, and a gold layer holds tables ready for reports and models. The post [Geospatial medallion architecture](https://wherobots.com/blog/geospatial-medallion-architecture/) applies it to spatial data. The post [Spatial data pipeline architecture with PostGIS and Wherobots](https://wherobots.com/blog/spatial-data-pipeline-architecture-postgis-wherobots/) shows where a database and Wherobots each fit.

## ETL pipeline examples

- **Retail reporting:** each night, a pipeline extracts the day's orders from the store database, converts currencies, joins product details and loads daily totals into a warehouse.
- **Web analytics:** a streaming pipeline reads page clicks, removes traffic from bots and loads counts per page every minute.
- **Crop monitoring:** after each satellite pass, a pipeline reads the new images and removes cloudy pixels. It computes a vegetation index for each field and loads one row per field and date. The [NDVI](https://wherobots.com/discover/ndvi/) article explains that index.
- **Map data refresh:** each month, a pipeline reads a new release of open map data and keeps one region. It checks the shapes and loads them into a table that other jobs query.
- **Green space near schools:** the Denver pipeline below. It joins school locations, building outlines and satellite images, and loads one row per school.

## How to build an ETL pipeline

1. **Define the output first.** Write down the table its users need: what each row stands for, which columns, how fresh and, for spatial data, which coordinate system.
2. **Extract with filters.** Read only the columns, dates and area the output needs.
3. **Transform in steps you can test.** Keep each step small, and check its row count and rules before the next one.
4. **Load to an open format.** Formats such as Apache Parquet and Apache Iceberg keep the column types, so other tools can read the output.
5. **Reconcile and monitor.** Read the output back, compare counts, and send an alert when a run fails or a count changes sharply.

SQL does most of the transform work in most pipelines. Python often runs the steps in order and sends SQL or DataFrame code to an engine.

## What changes in spatial ETL

Spatial ETL handles location data. Vector data is points, lines and polygons. Raster data is a grid of cells, such as a satellite image. The [raster vs. vector data](https://wherobots.com/discover/raster-vs-vector-data/) article explains both. Four things change for this data.

**Coordinate systems must match before a join.** A coordinate reference system (CRS) tells software how a shape's numbers map to places on Earth. Longitude and latitude are in degrees. The European Petroleum Survey Group (EPSG) registry, a public list of coordinate systems, numbers that system 4326. Sentinel-2 images are in meters, in Universal Transverse Mercator (UTM): the Earth split into 60 north-south strips, each with a flat grid in meters.

Denver's strip is EPSG:32613. Each shape carries its SRID, the number of its CRS. A buffer or distance written in one unit and applied to data in the other is wrong, and most engines raise no error. The [coordinate reference system](https://wherobots.com/discover/coordinate-reference-system/) article explains how systems differ.

**Shapes must be valid and not empty.** The Open Geospatial Consortium (OGC) publishes the [Simple Feature Access standard](https://www.ogc.org/standards/sfa/), which defines a valid polygon, for example one whose outline does not cross itself. Most engines test it with a function such as `ST_IsValid`. An empty shape, with no coordinates at all, counts as valid. So a pipeline also needs to count empty shapes, and compare areas before and after each step that changes coordinates.

**Rasters must be joined to vectors.** A raster-to-vector join, also called zonal statistics, computes a figure such as the mean or the count from the image cells inside each polygon. An example is the mean NDVI within 500 m of a school. Both layers must use the same CRS, and the join should read only the parts of the image it needs. The [spatial join](https://wherobots.com/discover/spatial-join/) article covers joins between vector layers.

**Formats must record the CRS and help readers skip data.** Three open formats do this:

- **GeoParquet** is Parquet with a geometry column and a CRS record. The [GeoParquet 1.1 specification](https://geoparquet.org/releases/v1.1.0/) treats data with no CRS record as longitude and latitude. Its "covering" option names a bounding-box column, "to help accelerate spatial data retrieval." A bounding box is the smallest rectangle around a shape. Software reading the data can skip any file whose rectangles fall outside the area it asks for.
- **Cloud Optimized GeoTIFF (COG)** is a raster file arranged so software can fetch only the blocks of the image it needs over the internet. The [OGC COG standard](https://docs.ogc.org/is/21-026/21-026.html) defines it, and the [GeoTIFF](https://wherobots.com/discover/geotiff/) article covers the format.
- **Apache Iceberg** is a table format: a set of Parquet files plus metadata that tracks versions and file statistics. Version 3 of the [Iceberg specification](https://iceberg.apache.org/spec/) adds geometry and geography types, each with a CRS, and stores a bounding box for each file. The [Havasu catalog](https://wherobots.com/discover/havasu-catalog/) article covers Wherobots' Iceberg tables.

## Spatial ETL in Wherobots: green space near Denver's schools

**The claim.** A spatial ETL pipeline needs checks on coordinates and shapes, because common spatial mistakes pass the usual checks on row counts, columns and missing values.

**What would break it.** For any one mistake below, the claim fails if that mistake raises an error or changes the row count, the count of missing values or the result of `ST_IsValid`.

**The pipeline.** It answers one question: what is the mean NDVI within 500 m of each school in Denver? NDVI, the normalized difference vegetation index, is near-infrared light minus red light, divided by their sum, for each satellite pixel. It runs from -1 to 1, and higher values mean more green leaves.

[Dadvand and colleagues](https://doi.org/10.1073/pnas.1503402112) used NDVI around Barcelona schools in 2015 as a measure of children's exposure to green space.

![Diagram of the Denver pipeline in three columns. Extract: Denver boundary, 289 school records, 266,037 building outlines and 4 Sentinel-2 tiles from June 21, 2025. Transform: check geometries, drop 2 duplicates, convert to UTM zone 13N, join NDVI to 500 m zones, join building cover. Load: GeoParquet and an Iceberg table, with 287 rows in and 287 read back.](https://wherobots.com/wp-content/themes/wherobots/assets/img/discover/etl-pipeline-diagram-v3.webp)

**The three stages of the Denver pipeline and the counts at each one. One Wherobots Job Run ran them, plus a check on a second image, in 386 seconds. Data: Overture Maps Foundation; Copernicus Sentinel-2 (European Space Agency). Analysis: Wherobots, November 9, 2026.**

### Extract

The pipeline read four layers from Wherobots Open Data, without downloading files:

- **Denver's boundary:** one polygon of 401.5 km² from Overture Maps divisions. Denver is a consolidated city and county, so Overture files it as a county.
- **Schools:** 289 Overture places in Denver whose category falls under school. The pipeline kept records that were open or had no status, with at least 0.7 on Overture's confidence score, a 0-to-1 rating that the place exists.
- **Buildings:** 266,037 Overture building outlines that touch Denver.
- **Sentinel-2 images** from June 21, 2025. For each day from June to August 2025, the pipeline took the cloudiest tile over Denver, and June 21 had the lowest figure: 0.08% cloud cover. The pipeline read three layers of each image, called bands, straight from Cloud Optimized GeoTIFFs in cloud storage. The bands are red light, near-infrared light and the scene classification layer, which labels each pixel as cloud, water, plants and so on.

The school filter shows why a transform step needs rules for categories as well as shapes:

```
CREATE OR REPLACE TEMP VIEW schools_raw AS
SELECT id, name, category, confidence, geom
FROM denver_places
WHERE array_contains(hierarchy, 'school')
  AND category NOT IN ('school', 'preschool')
  AND (status IS NULL OR status = 'open')
  AND confidence >= 0.7
  AND NOT regexp_like(lower(name), 'public schools|services|office|department|association|district|agency|club|lacrosse|track & field|cotillion|auditorium|events center|training center|seminary')
```

The rule leaves out Overture's general "school" category, which held 66 records that passed the other filters. Some are schools, such as Academia Ana Marie Sandoval. Others are not, such as a casino dealers school and a real estate institute. Leaving the category out drops both kinds.

The name rule removed 14 more records. Some are offices or foundations, such as Denver Public Schools Food and Nutrition Services. Others are clubs, sports programs or venues, such as Denver Chess Club and a lacrosse training center. Some unclear records remain, and the pipeline did not check the list against an official one. So the output is 287 Overture school records that may still include a few places that are not schools.

### Transform

**Check every shape.** All inputs passed: no missing, empty or invalid shapes, and every SRID was 4326.

**Drop duplicates.** The rule drops a school when another record with the same name sits within 100 m, and keeps the higher confidence score. It removed 2 records, leaving 287 schools.

A name rule cannot catch everything. Another 28 pairs of the same category sit within 100 m of each other under different names. Some are one school listed twice, such as North High School and Denver North High School, 42 m apart. Others may be separate schools that share a campus. The data alone cannot tell which.

**Match the coordinate system, then buffer in meters.** The schools are in longitude and latitude, and the images are in UTM zone 13N. `ST_Transform` converts each school's coordinates from degrees to meters in UTM zone 13N.

`ST_Buffer` then draws a 500 m zone around it:

```
CREATE OR REPLACE TEMP VIEW school_zones AS
SELECT id, name, category, geom,
       ST_Buffer(ST_Transform(geom, 'EPSG:4326', 'EPSG:32613'), 500) AS zone
FROM schools
```

The zones average 780,361 m², 0.6% less than the 785,398 m² of a true 500 m circle, because `ST_Buffer` draws the circle with straight segments.

**Compute NDVI once per image tile.** The next step clips the three bands once, to the area that the zones cover in each tile. It keeps only pixels that the [Scene Classification Layer](https://sentiwiki.copernicus.eu/web/s2-processing), which comes with Sentinel-2's Level-2A product, marks as vegetation (class 4) or bare ground (class 5). That removes clouds, shadows and water. NDVI is near-infrared minus red, divided by their sum:

```
CREATE OR REPLACE TEMP VIEW ndvi AS
WITH area AS (
  SELECT zs.scene_id, ST_Envelope(ST_Union_Aggr(z.zone)) AS box
  FROM zone_scene zs JOIN school_zones z ON z.id = zs.id GROUP BY zs.scene_id),
bands AS (
  SELECT /*+ REPARTITION(4) */ s.id AS scene_id,
         RS_Clip(RS_FromPath(s.red, 'raster.reader.auto-rescale=false'), 1, a.box, false, 0, true) AS red,
         RS_Clip(RS_FromPath(s.nir, 'raster.reader.auto-rescale=false'), 1, a.box, false, 0, true) AS nir,
         RS_Clip(RS_FromPath(s.scl, 'raster.reader.auto-rescale=false'), 1, ST_Buffer(a.box, 40), false, 0, true) AS scl
  FROM scenes s JOIN area a ON a.scene_id = s.id)
SELECT scene_id,
       RS_MapAlgebra(RS_AddBand(RS_AddBand(red, nir, 1, 2), RS_ReprojectMatch(scl, red, 'NearestNeighbor'), 1, 3), 'D',
         'ok = rast[0] > 0 && rast[1] > 0 && (rast[2] == 4 || rast[2] == 5);
          out = ok ? (rast[1] - rast[0]) / (rast[1] + rast[0]) : -9999.0;', -9999.0) AS ndvi
FROM bands
```

`RS_FromPath` reads each band where it is stored, and `RS_Clip` fetches only the parts of the file inside the rectangle around the zones. The classification layer has 20 m pixels, so `RS_ReprojectMatch` puts it on the 10 m grid of the red band. Of the 4.64 million pixels in those rectangles, 98.3% were classed as plants or bare ground and kept. This step took 115 seconds, the longest in the run. The [out-of-database raster post](https://wherobots.com/blog/raster-processing-at-scale-out-of-database-architecture-wherobotsdb/) explains how Wherobots reads only the parts of an image that a query needs.

**Join the raster to the zones.** `RS_ZonalStats` averages the NDVI pixels inside each zone and counts them:

```
CREATE OR REPLACE TEMP VIEW school_ndvi AS
SELECT /*+ BROADCAST(n) */ z.id, z.name, z.category, z.geom, z.zone,
       RS_ZonalStats(n.ndvi, z.zone, 1, 'mean') AS ndvi_mean,
       RS_ZonalStats(n.ndvi, z.zone, 1, 'count') AS clear_px
FROM school_zones z JOIN zone_scene zs ON zs.id = z.id JOIN ndvi n ON n.scene_id = zs.scene_id
```

A second join measured building cover: the share of each zone's area inside Overture building outlines, computed in square meters.

### Load

The pipeline wrote the 287 rows twice. The first copy is GeoParquet 1.1 with a bounding-box column. Here `path` is a new storage folder for each run, named with the time and a random suffix. The write stops if the folder already exists, so one run never replaces another run's copy:

```
(sedona.sql("SELECT *, struct(ST_XMin(geom) AS xmin, ST_YMin(geom) AS ymin, ST_XMax(geom) AS xmax, ST_YMax(geom) AS ymax) AS bbox FROM result")
 .write.format("geoparquet").option("geoparquet.version", "1.1.0").option("geoparquet.covering", "bbox")
 .mode("errorifexists").save(path))
```

The second copy is an Apache Iceberg table whose geometry column declares its CRS:

```
CREATE TABLE IF NOT EXISTS org_catalog.discover_etl_pipeline.denver_school_greenness (
  id STRING, name STRING, category STRING,
  ndvi_mean DOUBLE, clear_px INT, building_share DOUBLE,
  geom GEOMETRY(4326)
) USING iceberg
```

The load uses `INSERT OVERWRITE`, which replaces the table's rows in one Iceberg commit. If the insert fails, the table keeps its previous rows. The job first checks that an existing table has these columns in this order. The insert also names every column, so no value can land in the wrong column.

```
INSERT OVERWRITE org_catalog.discover_etl_pipeline.denver_school_greenness
SELECT id, name, category, ndvi_mean, clear_px, building_share, geom FROM result
```

Both copies read back with 287 rows, no missing shapes and SRID 4326. The job compares each copy with the transform output and stops with an error if a count, a missing shape or an SRID differs. It also compares the sums of the greenness values, the building shares and the pixel counts, which change if a value lands in the wrong column.

The pipeline stores the school points in longitude and latitude. The [Wherobots table documentation](https://docs.wherobots.com/data-engineering/iceberg-tables/geometry-tables) says a geometry column can declare SRID 0, 3857 or 4326. Our attempt to create a `GEOMETRY(32613)` column for UTM failed with `ST_INVALID_SRID_VALUE`, as documented.

### The result

Every one of the 287 zones got an NDVI value from at least 6,924 pixels classed as plants or bare ground. The median zone scored 0.35, and the scores ranged from 0.10 to 0.58. Only 8 zones scored below 0.2, and 85 scored 0.4 or above.

On the map, the 8 low zones sit downtown and in the northeast, and most of the 85 high zones sit in east and south Denver. Academy of Denver, downtown, scored lowest at 0.10. Hill Middle School scored highest at 0.58.

Greener zones tend to have less of their area under buildings. The median zone had 19.0% of its area inside building outlines. Across the 287 zones, NDVI and building cover had a correlation of -0.28, on a scale from -1 to 1 where 0 means no link: a weak negative link.

These are Overture records, and their categories need checks. "DSST Elevate Northeast Middle School," for example, is listed as an elementary school. The scores also come from one summer day, so they describe June 2025 and not a whole year.

### Three mistakes that pass the usual checks

We reran parts of the same pipeline with three common mistakes. Each test changed one step and kept the rest.

**1. Rounding coordinates to shrink files.** The GeoJSON specification, [Request for Comments (RFC) 7946](https://www.rfc-editor.org/rfc/rfc7946.html), notes that 6 decimal places of a degree is about 10 cm. Pipelines often round further to make files smaller.

Coordinates mark the corners of each outline. Rounding to 4 decimal places moves each corner to the nearest point on a grid. When the corners of a small outline land on the same point or along one line, it has no area left and becomes empty.

We rounded the 266,037 building outlines with `ST_ReducePrecision` and counted the empty ones:

- **6 decimal places:** none emptied.
- **5 decimal places:** 13 emptied.
- **4 decimal places:** 71,409 emptied, 27% of the total.

`ST_IsValid` returned true for all of them at every precision, and the row count stayed at 266,037. The emptied outlines were small: a median of 34.6 m², against 120 m² for all buildings. So total building area fell only 2%, from 53.5 to 52.4 km². The median building cover of the school zones moved from 19.0% to 18.6%.

![Map of the 742 building outlines within 300 m of Arrupe Jesuit High School in northwest Denver. After rounding to 4 decimal places, 291 small outlines became empty and 451 were kept.](https://wherobots.com/wp-content/themes/wherobots/assets/img/discover/etl-pipeline-rounding-v1.webp)

**Rounding to 4 decimal places emptied 291 of the 742 outlines within 300 m of Arrupe Jesuit High School. Its 500 m zone lost more outlines than any other school’s zone: 836 of 2,227. Every emptied outline still passed ST_IsValid. Data: Overture Maps Foundation. Analysis: Wherobots, November 9, 2026. Basemap: OpenFreeMap, OpenMapTiles, OpenStreetMap contributors.**

**2. A buffer written in degrees.** `ST_Buffer(geom, 500)` on longitude and latitude draws a circle with a radius of 500 degrees, larger than the Earth. Each zone then covered a whole image tile. The 287 schools got only 4 different NDVI values, one per tile, and no step raised an error.

A smaller mistake is harder to spot. A radius of 0.0045 degrees reaches 500 m north and south of a school. A degree of longitude is shorter than a degree of latitude away from the equator, so these zones were ovals, 771 m wide and 999 m tall on average. Each covered 23% less area than the zones drawn in meters. All 287 schools still got an NDVI value, so nothing in the output showed that the zones were the wrong shape.

**3. A lost coordinate system label.** We set the SRID of the 500 m zones to 0, as happens when a file format or tool drops the CRS. The [`RS_Intersects` documentation](https://docs.wherobots.com/reference/wherobots-db/raster-data/predicates/RS_Intersects) says Wherobots treats a shape with no SRID as longitude and latitude.

The zones' coordinates are in meters, around 500,000 m east. Read as degrees, 500,000 is far beyond the largest real longitude, 180. So no zone overlapped the image.

`RS_ZonalStats` returned no value for all 287 schools, while the row count stayed at 287.

**The verdict.** The claim holds for two of the three mistakes. Rounding and the buffers in degrees raised no error, kept the row count and passed `ST_IsValid`. The lost label also kept the row count, but it left every NDVI value missing, so a check for missing values catches it. One CRS problem did raise an error: at load time, Wherobots refused to create an Iceberg geometry column in UTM (EPSG:32613), a CRS that the column type cannot record.

**How we checked the result.** We used two outside checks:

- **The boundary:** the Census Bureau's TIGER/Line 2024 boundary for Denver covers 400.7 km² of land and water. It is stored in yet another CRS, the North American Datum of 1983 (NAD83, EPSG:4269). Overture's polygon covers 401.5 km². The land inside one outline but not the other totals 2.3 km², about 0.6% of Denver.
- **The image:** Sentinel-2B, the second Sentinel-2 satellite, passed over Denver about 10 minutes earlier the same day. The same steps on its images gave a median NDVI of 0.353, against 0.350, and a correlation of 0.997 across the 287 zones. No zone differed by more than 0.027.

## ETL research and what changes at scale

Research on ETL began with the data warehouse. Alkis Simitsis, Panos Vassiliadis and Timos Sellis modeled an ETL workflow as a graph of steps. They searched for faster orders of those steps in [Optimizing ETL Processes in Data Warehouses](https://doi.org/10.1109/ICDE.2005.103), at the International Conference on Data Engineering in 2005. Vassiliadis' [2009 survey](https://doi.org/10.4018/jdwm.2009070101) covers how researchers model ETL, each step of the process and the open problems of the time.

Two later lines of work changed where ETL runs. The Dataflow Model and Structured Streaming let pipelines process records as they arrive, with the same code as scheduled jobs. The lakehouse design runs transforms on the same open-format files that store the data.

Spatial processing on clusters began with GeoSpark. Jia Yu, Jinxuan Wu and Mohamed Sarwat added spatial types, indexes and partitioning to Apache Spark, at the [Association for Computing Machinery's SIGSPATIAL conference in 2015](https://doi.org/10.1145/2820783.2820860). Yu, Zhang and Sarwat extended it in [Spatial data management in Apache Spark: the GeoSpark perspective and beyond](https://doi.org/10.1007/s10707-018-0330-9), in GeoInformatica in 2019. GeoSpark became Apache Sedona. WherobotsDB, the engine that ran the Denver pipeline, comes from Sedona's creators.

Three problems remain open for spatial ETL at scale:

- **CRS records across formats:** GeoParquet, Iceberg and each engine record the CRS in their own way. A pipeline that crosses formats can lose the record, as the third test simulated.
- **Tests for shapes:** most data testing tools check counts, types and missing values. Few check empty shapes, area changes or whether a buffer is in meters.
- **Incremental updates:** a monthly map release changes some shapes and not others. Updating only the changed rows needs a stable ID for each feature, and joins that can run again on only those rows.

The [partitioning tax](https://wherobots.com/discover/partitioning-tax/) article covers the cost of splitting spatial data into tiles by hand. The [Iceberg geospatial types post](https://wherobots.com/blog/iceberg-geospatial-types-v3/) covers the new table types.

## Conclusions

1. An ETL pipeline is extract, transform and load, run as code. ELT loads raw data first and transforms it in the store. A data pipeline is the wider term for any automated data movement.
2. Count empty shapes after every step that changes coordinates. In Denver, rounding to 4 decimal places emptied 27% of 266,037 building outlines, and `ST_IsValid` passed all of them. Keep at least 6 decimal places of a degree, about 10 cm, unless a test shows that coarser rounding keeps every shape.
3. Convert data to a CRS in meters before any buffer or distance in meters. A 500 m buffer written on degrees gave 4 NDVI values for 287 schools. Before a step that takes a distance, check the unit of its input's SRID.
4. Treat a missing SRID as an error. With the label lost, the raster join returned no value for any of the 287 zones, while the row count stayed the same. Check every joined value for missing results, and check that every SRID is the one you expect.
5. Load to formats that record the CRS. GeoParquet and Iceberg both do. In Wherobots, an Iceberg geometry column declares SRID 0, 3857 or 4326, so a pipeline that works in UTM converts its output back to 4326 before it loads.
6. Check categories, not only shapes. Overture's general school category mixed schools with other places, and 28 pairs of nearby records under different names remained after a name rule. If the output counts or ranks places, compare the list with a second source before you publish it.

## Related terms

- [PySpark](https://wherobots.com/discover/pyspark/): the Python interface to Apache Spark, which runs many ETL pipelines
- [Spatial join](https://wherobots.com/discover/spatial-join/): the join types behind the building cover step
- [Coordinate reference system](https://wherobots.com/discover/coordinate-reference-system/): what EPSG:4326 and EPSG:32613 mean
- [GeoTIFF](https://wherobots.com/discover/geotiff/): the raster format behind Cloud Optimized GeoTIFFs
- [Havasu catalog](https://wherobots.com/discover/havasu-catalog/): Wherobots' Apache Iceberg tables with spatial types
- [NDVI](https://wherobots.com/discover/ndvi/): the vegetation index the pipeline computes
- [Raster vs. vector data](https://wherobots.com/discover/raster-vs-vector-data/): the two data models the pipeline joins
- [Partitioning tax](https://wherobots.com/discover/partitioning-tax/): the cost of splitting spatial data into tiles by hand

## Read more from Wherobots

- [Spatial data pipeline architecture with PostGIS and Wherobots](https://wherobots.com/blog/spatial-data-pipeline-architecture-postgis-wherobots/)
- [Streaming spatial data into Wherobots with Spark Structured Streaming](https://wherobots.com/blog/streaming-spatial-data-into-wherobots-with-spark-structured-streaming/)
- [Geospatial medallion architecture](https://wherobots.com/blog/geospatial-medallion-architecture/)
- [Spatial data in Apache Iceberg](https://wherobots.com/blog/apache-iceberg-spatial-data/)
- [Raster processing at scale: the out-of-database architecture](https://wherobots.com/blog/raster-processing-at-scale-out-of-database-architecture-wherobotsdb/)

Run the pipeline above with a Wherobots free trial at [cloud.wherobots.com](https://cloud.wherobots.com).

## Frequently asked questions

### What is an ETL pipeline?

An ETL pipeline is a set of steps, written as code, that runs on a schedule or when new data arrives. ETL stands for extract, transform and load. The pipeline extracts data from sources such as databases, files and application programming interfaces (APIs). It transforms the data by cleaning, joining and reshaping it. Then it loads the result into a store such as a data warehouse.

### What are examples of ETL pipelines?

A nightly job that copies the day’s sales from a store database, converts currencies and loads daily totals into a warehouse is an ETL pipeline. So is a job that reads new satellite images, computes a vegetation index for each farm field and loads one row per field. Our Denver example read schools, building outlines and Sentinel-2 images. It measured the mean normalized difference vegetation index (NDVI), a satellite measure of green leaves, within 500 m of each school and loaded one row per school.

### Is SQL an ETL tool?

Structured Query Language (SQL) is a query language, and most ETL pipelines use it for the transform step: filters, joins and totals. A pipeline also needs a way to read sources, write the output and run on a schedule. Engines such as Apache Spark and WherobotsDB run SQL for all three steps. In our Denver pipeline, every extract and transform step, and the Iceberg load, was SQL with spatial functions.

### Is Python used for ETL?

Yes. Python can call databases, APIs and file formats, and it can drive engines such as Apache Spark through PySpark. For large data, Python usually sends SQL or DataFrame steps to an engine that runs them on many machines. Libraries that hold all the data in one computer’s memory, such as pandas, fit small inputs.

### Is Databricks ETL or ELT?

Databricks is a data platform, and it runs both patterns. Its ETL guide compares ETL with ELT (extract, load, transform) and covers each on Databricks. Its medallion architecture first loads raw data into a bronze layer, then cleans and validates it into silver and gold layers. That order is closer to ELT, because the raw data lands before most transforms run.

### What is the difference between ETL and ELT?

ETL transforms data before it loads into the target. ELT (extract, load and transform) loads the raw data first and transforms it inside the target. The target is usually a cloud warehouse or lakehouse that can run large SQL jobs. ELT keeps the raw data for later reprocessing. ETL keeps only the cleaned result in the target.

### What is the difference between a data pipeline and an ETL pipeline?

A data pipeline is any automated path that moves data from one system to another. An ETL pipeline is one type of data pipeline that has a transform step between reading and writing. Other data pipelines copy data without changing it, stream events as they arrive or send warehouse data back to business applications.

### What is a streaming data pipeline?

A streaming data pipeline processes records as they arrive, instead of in scheduled batches. Examples are vehicle positions, sensor readings and website clicks. Streaming engines group records into time windows and handle records that arrive late. Apache Spark Structured Streaming runs streaming jobs with the same DataFrame and SQL code as batch jobs.

### What is data pipeline architecture?

Data pipeline architecture is the design of how data moves from sources to its users. It sets which systems extract the data, where it lands, how it is transformed and how often each step runs. Common patterns are batch ETL, ELT into a warehouse or lakehouse, and streaming. Layered designs such as the medallion architecture keep raw, cleaned and business-ready copies.

### What is spatial ETL?

Spatial ETL is an ETL pipeline for location data: points, lines, polygons and raster images. Its transform step adds checks that other tables do not need. It puts every layer in one coordinate reference system, checks that shapes are valid and not empty, and joins image pixels (raster data) to points, lines and polygons (vector data). Its load step writes formats that record the coordinate system, such as GeoParquet and Apache Iceberg.