Data Extraction and Handling

In the Connector SDK, data extraction is about fetching data from the source system. Data handling is the process of shaping this data as needed, such as cleaning, reformatting, or otherwise preparing it before sending it to Fivetran. If your data is already well-structured, it may require minimal changes and simpler data handling.

Effective data handling is one of the most important parts of connector development because it keeps destination data consistent, easy to query, and ready for downstream use. Fivetran manages much of the work for you, including data type inference, automatic schema migration, and de-duplication based on primary keys. However, your connector must still shape records consistently to match your business needs.

Planning for data extraction and handling

Both data extraction and data handling logic can be placed in the update() function in your connector.py file or split across helper files in your project. The update() function is always the entry point for connector execution and is responsible for orchestrating the process of pulling, preparing, and loading data.

For efficient data movement from source to destination, plan in advance which data you want to extract and how you will structure that data when loading into the destination. Important factors to consider include:

• How does the source system expose data: API, bulk dump, or direct SQL?
• What schema or data structure will make it easiest for end users to work with the data once it is in the destination?
• What’s the expected data volume per sync?
• How will you transform source responses into destination-ready tables?

Data extraction

In your Connector SDK code, you can extract data by one or a combination of the following methods:

• Calling APIs (REST or GraphQL)
• Querying databases
• Reading files (CSV or JSON) from SFTP or object storage
• Fetching records from logs or events

At this stage, the extracted data is still in its incoming format and may be nested, paginated, or inconsistent.

Plan your extraction logic

When writing your extraction logic, keep the following key aspects in mind:

Schema decisions (data shape and keys)

Identify which fields to pull, whether you’ll define a schema, and normalize the data based on your business use case. Fivetran automatically infers columns and data types, and creates a primary key by hashing the full record if you do not define one. While this works for cases like event tables where each event is unique, we recommend defining a primary key, such as an employee ID. When a primary key is defined, it ensures that updates modify existing records rather than creating duplicates.

Early schema planning helps guide your connector development and makes it easier to turn data into clean, analytics-ready rows later.

Sync strategy (how data changes over time)

Consider how your connector syncs data — Decide whether it fetches only new or changed data since the last sync, reload all data each time, or use a mix of both. Your sync strategy affects how you extract and handle data, including how you detect updates and deletes. For example, you may need to send soft deletes if your source does not provide delete events. Your sync strategy also determines how you track progress between syncs. For more on tracking progress and resume points, see state management.

Pagination (working with large datasets)

If you are working with large datasets, we recommend following best practices for handling large datasets, especially using pagination if the data source supports it.

Data handling

Data handling takes place after extraction and before loading data to the destination. This is where you process your source data, such as handling nested objects, fixing data types, or mapping fields to new column names, and then send the resulting rows to Fivetran.

Working with inconsistent or varying data types

When you pull data from a source, the values don’t always map cleanly into the structure and types you want in your destination. Consider how to handle two key aspects: data type conversions and nested or repeated structures.

Review source fields for inconsistencies in format or type, such as numbers sometimes appearing as text or timestamps in different formats. Fivetran automatically infers data types and handles mismatches by up-typing as needed. For more information, see data type hierarchies and type inference. If you need a specific data type or format for a column, you can declare it in your schema, or handle type introspection and conversions as you process each record in your Connector SDK code. For example, if your business case requires all timestamps to be in UTC, convert these values as part of your record processing before sending them to Fivetran.

Tips for handling data types and formats consistently:

• Send timestamps in ISO-8601 UTC (for example, 2025-01-10T12:34:56Z). This quickstart example shows how to convert different timestamp formats to UTC during data handling.
• Send booleans as real booleans (True and False)
• Send numerics consistently (avoid "42" sometimes and 42 other times)
• Keep DECIMAL-like values stable in representation (for example, don’t mix cents and dollars)

Working with nested or repeated structures

Some sources return deeply nested JSON or arrays inside records. You have several options for handling this type of data, each with its own advantages. Choose the option that best matches your analytics needs and the complexity of your data.

• Flatten into additional columns:

  If your source provides a single nested object, such as an address:

    {
     "id": "1",
     "name": "Alice",
     "address": {
       "city": "New York",
       "zip": "10001"
      }
    }
  

  You could flatten it into:

  id	name	address_city	address_zip
  1	Alice	New York	10001

• Break out into child tables:

  If your source provides a list of nested objects, such as orders per user:

    {
     "user_id": "1",
     "name": "Alice",
     "orders": [
       {"order_id": "A1", "amount": 20},
       {"order_id": "B2", "amount": 35}
     ]
    }
  

  You could split into:

  Parent table:

  user_id	name
  1	Alice

  Child table:

  order_id	user_id	amount
  A1	1	20
  B2	1	35

• Write as a JSON blob:

  For very complex or highly variable data, you might store a nested object as a single JSON column:

    {
      "id": "1",
      "name": "Alice",
      "metadata": {
        "preferences": {
          "theme": "dark",
          "notifications": true
        },
      "history": ["<history_event_1>", "<history_event_2>"]
     }
    }
  

  Resulting table:

  id	name	metadata
  1	Alice	{"preferences": {...}, ... }

The Data handling patterns example shows how to implement each of these approaches in connector.py.

Best practices for working with large datasets

Follow these best practices to avoid common pitfalls that can slow down performance:

Avoid buffering large data in memory

Try creating buffers with a manageable amount of data to prevent excessive memory usage and performance slowdowns.

Use pagination

Improve efficiency by fetching data in smaller chunks instead of processing large datasets at once. If the source system supports pagination, take advantage of it to retrieve data in manageable segments.

Use parallel fetching from source

For certain use cases, such as small files, parallel fetching can improve performance over sequential fetching. When dealing with multiple small files:

• Fetch and stream each file in parallel to improve performance.
• Ensure that the contents of each individual file are upserted sequentially to maintain data consistency.

Use compression

When using file-based connectors, uploading compressed files (for example, .bz2, .gz, .zip) can enhance data transfer efficiency by reducing file sizes and minimizing network bandwidth usage, provided the connector supports decompression. However, it's essential to balance the computational efforts required for compression and decompression against the benefits of reduced transfer times.

In scenarios involving substantial data volumes or limited bandwidth, the advantages of compression typically outweigh the processing overhead, leading to overall improved efficiency. For instance, this approach is particularly efficient for large text files, as compression decreases transfer times and accelerates network performance. Once transferred, the files can be decompressed effectively by the connector, assuming the compression and decompression processes are optimized.

Split connections into manageable units

Performing sequential upserts and syncing a large number of tables with a single connection can slow down performance. Consider breaking it into smaller, more manageable connections, and sync a manageable set of tables to each connection.

If the system performs sequential upserts, splitting the connector can help reduce bottlenecks and enhance throughput.

Checkpoint regularly when syncing

With high-volume data sets that take a long time to complete a sync, you must use the following checkpointing best practices:

• Checkpoint regularly, (for example, every 10 minutes). Checkpointing saves the state, which saves the sync progress. If sync fails with an error, then, without checkpointing, the next sync will start from the previous sync's starting point. With regular checkpointing, the next sync will start from the last checkpoint of the previous failed sync, thus saving time.
• Avoid calling checkpoints too frequently during long syncs, although frequent checkpoints may be acceptable for shorter syncs. Excessive checkpointing can cause performance issues, so it's best to checkpoint approximately once every 10 minutes or every 1,000 records, depending on your data volume and sync characteristics.

Practical examples from the Connector SDK repository

To help you get started, the Fivetran Connector SDK repository offers quickstart and common pattern examples, and the Community Connectors repository provides custom connector examples. Together they showcase how to extract, clean, and normalize data from different source types, demonstrating best practices for pulling data, handling various formats, and preparing data for loading into your destination.

API data extraction examples

• Weather API (requests and config-driven extraction): Demonstrates basic data extraction from a public API using configuration files.
• Parsing JSON into a typed object: Shows how to fetch JSON from an API and map it into a Python object for cleaner transformation.
• Syncing a large data set: Shows how to handle large paginated and unpaginated data sets from an API.

Database data extraction (querying a source)

• Apache HBase connector: Extracts rows from HBase tables using database clients, ideal for non-HTTP data sources.

File/document extraction

• Extracting data from a PDF: Parses and extracts data from PDFs, useful when your source is a file.

Data normalization and transformation

• Normalize inconsistent timestamps: Centralizes timestamp parsing and serialization to handle multiple formats.
• Base64 encoding/decoding: Shows how to decode or encode base64 content in your connector logic.
• Type shaping with specified types: Offers a reference for controlling data representation and avoiding ambiguous types.
• Pandas-based cleanup and normalization: Uses Pandas for dataframe-style cleaning and transformations before sending records.

These examples provide practical starting points for implementing robust data extraction and handling logic in your own connector projects.

Related concepts

• Pagination: Fetches data in smaller batches or pages so your connector can handle large sources.
• Schema management: Defines how your destination tables are structured and updated as your data changes.
• State management: Keeps track of what data has already been synced, so only new or updated records are processed each time.

Together, these concepts help you build connectors that are reliable, incremental, and predictable.