Oil and Gas ETL Pipelines: How to Build Them to Last

This guide is for data leads, CTOs, and engineers at oil and gas data businesses, operators, and mineral and land companies. Typically, a pipeline built for one state breaks when the next state's files arrive. Or production numbers stop matching what the regulator reports. Or the one engineer who understood the parsers has left.

The examples below come from public US data sources and from our own work building ETL across all 50 US states for an oil and gas data platform.

What is an oil and gas ETL pipeline?

An oil and gas ETL (extract, transform, load) pipeline collects data about wells and their activity from many sources, standardizes it, and loads it into one place where it can be queried, mapped, and analyzed. Unlike most business ETL, its sources are mostly public regulators that publish on their own schedules, in their own formats, with their own definitions.

Most oil and gas data platforms combine some version of these data types:

Reporting rules keep adding to this list. EPA's 2024 revisions to its oil and gas greenhouse gas rule (Subpart W) moved onshore production reporting from the basin level to the wellpad level, starting with 2025 data reported in 2026, according to Trinity Consultants. A pipeline that only modeled basins now needs a wellpad grain.

Where does oil and gas data come from, and in what formats?

Oil and gas data comes from dozens of independent regulators, and no two publish the same way. The National Petroleum Council's 2011 regulatory data management paper counted 38 oil- and gas-producing states or Native American nations. It concluded that a national data portal would need further investment from both industry and regulators.

Here is what a handful of real sources look like today:

Three practical consequences follow from this table.

1. You need one parser per source, not one per format. Two CSV files from different states rarely share column names, units, or meanings.
2. Some formats need specialist handling. The Railroad Commission still publishes production ledgers in EBCDIC, a mainframe encoding that most modern tools don't read by default.
3. Sources change without warning you. The Railroad Commission retired its R-3 EBCDIC file in August 2025 and replaced it with JSON a few weeks later.

Well logs add another format. LAS (Log ASCII Standard), maintained by the Canadian Well Logging Society, is the most widely used format for digital log data. Version 3.0 adds tops, core data, and deviation surveys to version 2.0, so a parser has to handle both.

Why is the well identifier the hardest join in oil and gas data?

The well identifier is the key that every dataset joins on, and it is less consistent than it looks. The API well number has up to 14 digits:

• Digits 1–2: state code
• Digits 3–5: county code
• Digits 6–10: unique well identifier within the county
• Digits 11–12: sidetrack code
• Digits 13–14: event sequence code

Sources publish this number with different lengths, with or without dashes, and with or without the sidetrack and event digits. A 10-digit API number from one dataset and a 14-digit number from another can describe the same well, or two different wellbores of the same well.

The standard itself has moved. The American Petroleum Institute transferred custody of the well numbering standard to the PPDM Association in 2010. PPDM's US Well Number Standard is now the official successor. It assigns permanent, unique identifiers to both well origins and wellbores, partly because identifiers have been assigned inconsistently.

To make well identity reliable in a pipeline:

1. Normalize every identifier to a single 14-digit form, padding missing sidetrack and event digits explicitly rather than guessing.
2. Decide the grain of each table: well origin, wellbore, or completion. Production often reports at the lease or unit level, not the wellbore.
3. Keep a crosswalk table that maps each source's raw identifier to your normalized one, so you can trace any record back to where it came from.
4. Use shared definitions. PPDM publishes "What is a Well" and "What is a Completion" vocabularies, because the same word means different things across regulatory systems.

Why do well coordinates need special handling?

Well coordinates need special handling because sources use different geodetic datums, and mixing them silently moves wells on your map. The Texas Railroad Commission's digital map layers use NAD 27. Many other systems use NAD 83 or WGS 84.

The difference matters. According to Penn State's geospatial course materials, shifts between NAD 27 and NAD 83 range from 10 to 100 meters in the lower 48 states. For a map-first product, 100 meters can place a well on the wrong lease or the wrong side of a section line.

Record the datum of every source at ingestion, and transform coordinates to one standard on load. Never infer a datum from the data itself. Spatial databases such as PostGIS can store geometry with its spatial reference and transform it in a query, but only if the original datum was captured correctly.

How should an oil and gas pipeline handle revised and late data?

An oil and gas pipeline must treat history as mutable, because the sources revise their own numbers. A load that only appends new months will drift away from the official figures over time.

The revisions are documented and regular:

• EIA re-estimates the prior two months every time it releases monthly production. When annual figures are finalized, it revises up to 10 prior years for oil, according to its EIA-914 revision policy.
• ONRR warns that late operator reports can raise volumes for past months, and that retroactive changes to agreements and units can shift volumes between states, the federal government, and tribes.

The engineering answer is well established. Apache Airflow's best practices recommend making every task idempotent so a re-run produces the same result, using UPSERT instead of INSERT to avoid duplicate rows, and reading from a fixed data partition instead of "whatever is latest."

For oil and gas data, that translates into three rules:

1. Keep every raw file you download, labeled with the date you fetched it, so you can reprocess any period.
2. Reload a rolling window of recent months on every run, rather than only the newest month.
3. Upsert on the well identifier, period, and product, so a revised volume replaces the old one instead of duplicating it.

What does a reliable oil and gas ETL architecture look like?

A reliable oil and gas ETL architecture separates source-specific work from shared logic, so adding a source never means touching the core. This is the stage design we recommend:

The canonical model in stage 3 is where most long-term value sits. Industry standards help here. Energistics publishes WITSML, PRODML, and RESQML for drilling, production, and reservoir data. The OSDU Forum publishes an open data model and platform for subsurface data, with 190 member organizations. You don't have to adopt them wholesale, but borrowing their definitions avoids inventing your own.

How do you add a new state without rebuilding the pipeline?

You add a new state by writing a new adapter, not by changing the pipeline. If stages 3 to 7 only ever see standardized rows, a new source only needs code in stages 1 and 2.

A practical checklist for onboarding a new state:

1. Collect sample files from every dataset you need, including older years, because formats often changed over time.
2. Document the source: encoding, datum, identifier format, units, update schedule, and revision behavior.
3. Write the adapter that maps the source to your canonical model. Keep all state-specific logic inside it.
4. Add tests using the real sample files, so a future format change fails a test instead of a customer report.
5. Reconcile totals against the regulator's own published summaries before switching the state on.

The fear most data leads describe is that this knowledge lives in one engineer's head. Writing the source documentation in step 2 is what keeps a pipeline maintainable after that person moves on.

How do you know the pipeline is still working?

You know the pipeline is working when it tells you about problems before your users do. That sounds obvious, yet it is rare. In Monte Carlo's 2023 survey of 200 data decision-makers, 74% said business stakeholders found data problems first all or most of the time.

Monitor each source, not just the whole pipeline:

• Freshness: did each source deliver on its expected schedule?
• Volume: is the row count for this period within a normal range of the last one?
• Schema: did a column appear, disappear, or change type?
• Reconciliation: do your state totals match the regulator's published totals?
• Identity: how many records failed to match a known well?

Maintenance is the long-term cost to plan for. In a Fivetran and Wakefield Research survey of 300 data leaders, data engineers spent 44% of their time maintaining pipelines. With dozens of public sources that change on their own schedules, oil and gas pipelines are no exception.

Proof: how Peak ETL built one platform across 50 states

Peak ETL, an oil and gas data business in Colorado Springs, needed exactly this kind of pipeline. Its customers, landmen and speculators, had been choosing between six-figure enterprise data tools and manual research across courthouses and state websites.

6sense HQ built Spud Wire, a map-first platform for Peak ETL. Its ETL pipelines ingest data from all 50 US states, across SFTP, scraping, Excel, and CSV sources. They bring together four data types: well, production, land, and regulatory data. The architecture absorbs each new state's format without rebuilding the system. Rendering thousands of map points gets expensive fast, so the team used an open-source approach to keep operating costs low.

Jason Holden, Owner of Peak ETL, said: "6sense HQ delivered the working platform and stayed responsive as requirements changed."

When shouldn't you build your own oil and gas ETL pipeline?

You shouldn't build your own pipeline when a commercial data provider already delivers what you need and the data isn't your product. Enterprise oil and gas data services exist precisely so operators don't have to parse 50 states themselves.

Build only if one of these applies:

• The data platform is what you sell, as it was for Peak ETL.
• You need sources, fields, or freshness that commercial providers don't offer.
• You need to own the data model to combine public data with your own operational data.
• Licensing costs grow faster than the value you get from the data.

For a structured way to decide, see our guide to build vs. buy for energy data software.

When you shouldn't work with 6sense HQ: if a commercial provider covers your needs, buy from them and skip us. We're also not the right fit if you want a turnkey data subscription. We build and maintain pipelines, integrations, and data layers that you own, and we start by scoping one clear piece, such as one state or one data type, rather than a rebuild.

The bottom line

Oil and gas ETL pipelines succeed or fail on four problems: format sprawl across regulators, inconsistent well identifiers, mixed coordinate datums, and sources that revise their own history. Solve these with per-source adapters, a canonical model keyed on a normalized 14-digit well identifier, explicit datum conversion, idempotent reloads of recent periods, and monitoring for each source. With that design, adding a new state becomes a contained task instead of a rebuild.

About the author: AKM Ahsan is CTO of 6sense HQ, a software engineering partner for energy, mobility, and operations-heavy companies. 6sense HQ built the 50-state ETL behind Peak ETL's Spud Wire platform. LinkedIn

Pipeline breaking every time a state changes its files? Book a 30-minute technical review with a senior engineer, and get a straight read on what to fix first, whether or not you work with us. See how we approach energy data engineering.