Data extraction is the first step of every extract, transform, and load (ETL) or extract, load, and transform (ELT) pipeline. The data extraction method a data engineer chooses determines data freshness in the data warehouse and the query load each job puts on the source database.

Your chosen extraction method also shapes how much maintenance the pipeline needs when a vendor changes its API or schema. The wrong method can break your scripts with every source update, so engineers spend their time fixing code instead of adding new sources. Here’s a breakdown of what data extraction means, common challenges, best practices, and how the main data extraction methods compare.

What is data extraction, and how does it work?

Data extraction is the process that copies raw data out of source systems such as SaaS applications, production databases, ERP systems, and file stores to a destination. Extraction forms the first stage of every ETL pipeline and of data ingestion, before the records are loaded into a data warehouse or data lake.

Every extraction job, whether it’s a Python script or a managed connector, follows a fixed order of steps. Here’s how the extraction job moves records from a source system to staging:

1. Identification: Data engineers list every system that holds the data the business needs, such as a CRM, an ERP system, or event logs. The list also records each source’s format, so each source gets the right parser during extraction.
2. Connection: An extraction script connects to each source through a database driver or an authenticated API call. The team stores API keys and database passwords outside the script, so a new password doesn’t require a code change.
3. Retrieval: Each run pulls either the full table or only the rows that changed since the previous run, based on the extraction strategy. A row count check against the source confirms that no records failed to carry over from the source.
4. Staging: A cloud storage bucket or a staging schema in the data warehouse holds the raw records. Engineers keep this raw copy unchanged so they can process it later without querying the source again.

The retrieval process varies across sources. For example, a database table and a scanned PDF store data in different structures, so each type of data needs its own extraction approach.

Types of data extraction

Data extraction falls into three types (structured, semi-structured, and unstructured), based on how the source system organizes its data. Structured sources need little parsing, while semi-structured and unstructured sources need more work before the records fit into tables.

Structured data

Structured data follows a fixed schema of rows and columns, as in relational databases and CSV exports. Every field has a known name and type, so a SQL query can pull new rows from an orders table directly.

Semi-structured data

Semi-structured data doesn’t follow a fixed table schema, though each record has organizational markers such as keys and tags. JSON API responses and XML files are common examples. A payment API response, for instance, can nest the billing address inside each transaction so the pipeline separates the address into columns.

Unstructured data

Unstructured data has no predefined model. It’s often used for scanned PDFs, images, emails, and support tickets. Extraction relies on optical character recognition (OCR) or natural language processing to find usable fields, such as the vendor name and total on a PDF invoice.

Common data extraction methods and techniques

Common data extraction methods span different extraction strategies and access techniques. Extraction strategies determine which records an extraction job copies from the source and when it runs. Access techniques, also called data extraction techniques, distinguish the access methods, such as API calls or SQL queries.

A typical pipeline combines one extraction strategy, such as change data capture (CDC), with one access technique, such as SQL queries. The source system determines which strategy and technique a pipeline uses. Production databases support SQL queries and log-based CDC, while SaaS applications typically pair API calls with incremental extraction.

Extraction strategies

An extraction strategy affects the query load on the source system and the freshness of data in the destination. Here are the strategies worth considering:

• Full extraction: A full extraction job copies the entire table on every run, keeping the logic easy to follow but adding query load as the table grows.

• Incremental extraction: An incremental job pulls only data that’s changed since the last run.
• Change data capture: A log reader copies every committed change (deletes included) from the database transaction log with minimal source load. Log-based CDC requires log access, so a database administrator has to grant access before setup.

Full extraction suits small lookup tables, while CDC suits large transactional databases. Incremental extraction works well for SaaS APIs that expose a last-modified field.

Ways to access data

An access technique is the connection method an extraction job uses to read records from a source system. Here’s how each technique works:

• API-based extraction: A job sends authenticated requests to a vendor’s REST API and reads JSON responses page by page. API data extraction fits SaaS applications without direct database access, though rate limits can slow large backfills.
• Database queries (SQL): SQL queries run through a database driver and pull only the tables and columns the pipeline needs. SQL extraction suits databases, but long-running queries can slow the application on that database. To mitigate this problem, SQL can query logs instead, identifying and replicating changes without affecting operational tables.
• File-based extraction: File parsers read CSV, Excel, JSON, or Parquet files from cloud storage or file servers. File-based pulls suit vendor exports and legacy systems without an API, although a changed column order can break the parser.
• Web scraping and document (OCR) extraction: Scrapers pull values from web page HTML, and OCR turns scanned documents into text. Scraping and OCR fit sources with no API, but a page redesign or a blurry scan can produce incorrect fields.

Each access technique needs its own authentication and parsing code, so a custom pipeline can fail when a source changes its login method or response format. Data engineers who run custom pipelines catch broken runs early by setting alerts on failed runs and retesting the parsing code whenever a vendor announces an API change.

Common data extraction challenges

Schema drift and API rate limits commonly cause data extraction failures. Inconsistent source data also causes errors when missing or duplicate records reach reports.

Here’s how common data extraction issues unfold:

• Schema drift: When a source team renames a column or changes a data type, pipelines have to detect schema drift and update the destination table before fixed-column jobs fail.
• Rate limits: SaaS APIs cap requests per minute or per day, so retry logic with backoff has to keep extraction within API rate limits without dropping records.
• Inconsistent data quality: Missing values and duplicate rows in source records can break loads or lead to inaccurate reports unless validation checks catch them during extraction.

Best practices for data extraction

Missing fields and duplicate rows are easier to fix at the source compared to after they show up in a dashboard. Here are some best practices that keep extraction jobs accurate:

• Check data at the source: Confirm required fields and value ranges before records load, and flag sudden drops in row volume.
• Pull only changed records: Switch to incremental extraction or CDC after the first full load, which limits a failed run’s reprocessing to its own batch.
• Log every failure: Wrap API calls in retry logic and log rate limit errors apart from permission errors so the on-call engineer knows whether to wait or fix credentials.
• Monitor pipeline health: Track run duration and row counts for each job, and alert data engineers when a value moves outside its normal range.
• Document each pipeline: Record the source, extraction method, run schedule, and validation rules for every pipeline in one shared place.

Automate data extraction with Fivetran

Custom extraction scripts can break when a vendor updates its API or lowers its rate limits. A renamed source column can also stop a scheduled sync to a data warehouse, pulling engineers away from new pipelines.

Fivetran connectors automate data extraction from each source and load the records into your destination on the schedule you choose. More than 750 prebuilt connectors cover SaaS applications, databases, ERP systems, and file sources, all maintained by Fivetran.

‍

After the initial full load, every sync pulls only changed records, and database sources use log-based CDC to capture deletes. When a source adds a column or changes a data type, Fivetran updates the destination table based on your schema change settings.

For sources without a prebuilt connector, the Connector SDK runs Python code your team writes on Fivetran’s infrastructure, and the AI Connector Agent generates connectors for REST API sources. After your team models the data, Fivetran Activations sends the modeled tables from your data warehouse into over 200 business tools, such as CRMs.

Start a free trial with Fivetran to replace custom extraction code with managed connectors that update when source APIs change.

FAQ

What does data extraction mean in ETL?

In ETL, data extraction is the first step, where a pipeline copies records from source systems into a staging area before transformation and loading. In ELT, the data is transformed in the data warehouse.

What are the different types of extraction methods?

Extraction methods fall into logical types, such as full and incremental extraction. They’re also categorized by physical types, which describe how a pipeline connects to the source: Online extraction reads directly from the live source system while offline extraction reads from an export staged outside the source.

‍

[CTA_MODULE]