Explore Python ETL for SAP and non-SAP systems, with a practical example, pipeline best practices, and custom Python capabilities in Migravion.

Python ETL: Building Custom Data Pipelines for SAP and Non-SAP Systems

Enterprise data rarely arrives in exactly the structure a target system expects. A source application may expose nested API responses, use its own product classifications, or store several business attributes in a single field. Even when standard connectors are available, moving that data successfully can require logic specific to the source, target, and business process.

Python ETL helps teams address these requirements. Python can retrieve data, reshape records, apply business rules, and prepare information for loading into another system. Its flexibility is especially useful in environments that combine SAP applications with databases, legacy systems, and cloud services.

The challenge is making that custom logic reliable enough for ongoing use. A script that successfully processes a sample still needs configuration, validation, error handling, and execution controls, before it can become part of an enterprise data pipeline. This article explains how Python ETL works, where custom code adds value, and how it can be combined with visual workflow design in Migravion.

What Is Python ETL?

Python ETL is the use of Python to perform or support extract, transform, and load operations. These operations retrieve data from a source, prepare it according to defined requirements, and deliver it to a target system.

Python can participate in all three stages or handle only the parts that require custom logic:

• Extract: Retrieve data from various sources, such as APIs, databases, and files. Custom extraction logic may handle pagination, selection criteria, or source-specific response structures.
• Transform: Change data values, formats, structures, and relationships. Examples include converting units, expanding nested records, applying reference mappings, and calculating derived attributes.
• Load: Submit prepared data through an appropriate target interface. Custom loading logic may construct request payloads, process responses, and record the outcome of each operation.

A Python ETL pipeline connects these stages into a controlled process. It also defines what happens when data is invalid, an interface becomes unavailable, or not all records are accepted.

This distinction matters because a script and a pipeline solve different parts of the problem. A script contains processing logic; a pipeline coordinates that logic with connections, execution settings, data checks, and operational results.

When Is Python Useful for ETL?

Standard connectors and visual mapping tools can cover many common requirements. Python becomes useful when a workflow needs behavior that is difficult to express through the available configuration options.

Typical situations include:

• Custom source interfaces: A business application may provide an API with unusual pagination, filtering, or response handling. Python can implement the source-specific behavior needed to retrieve a complete and correctly scoped dataset. For example, an API might initially return only product identifiers, requiring a separate request for each product’s details. Custom extraction logic can coordinate these requests, respect request limits, and distinguish a temporary connection failure from a record that is no longer available.
• Complex business transformations: A target value may depend on several source attributes and reference rules. For example, assigning a product category might require a combination of product family, intended use, and market, with more specific rules taking precedence over general ones. Python can evaluate these conditions in a defined order and identify records that match conflicting rules or that don’t match at all. This makes ambiguous classifications visible, instead of silently assigning a default that could affect downstream business processes.
• Nested or irregular data: An API may return products containing lists of descriptions, packaging units, and classifications. Python can separate these structures into related datasets, while preserving the identifiers that connect them. The logic also needs to distinguish independent lists: a product with three descriptions and two packaging options should not automatically become six combined records. Handling each relationship explicitly helps prevent unintended duplication and preserves the meaning of the source data.
• Data enrichment: Records may need additional information from reference files or another application. For example, a legacy product record may contain a local category code that must be supplemented with an approved group-level classification. Python can look up the additional attributes and check whether each source value has exactly one applicable match. Missing, duplicate, or outdated reference entries can then be reported separately, preventing the introduction of uncertain values or multiplying records.
• Specialized validation: Some checks involve more than a required field or an allowed value. Python can evaluate relationships between attributes or records — for example, whether every alternative packaging unit has a positive conversion factor and a corresponding base unit. It can also check conditional requirements — for example, whether a particular product category requires additional attributes. Useful validation logic returns the violated rule, relevant record identifier, and problematic values, so that data owners can correct the issue, without investigating the script.
• Target-specific preparation: A destination may require a particular payload structure, field combination, or sequence of operations. For example, an interface may expect product header data and related descriptions in separate requests, with the identifier returned by the first operation included in the next. Python can construct these requests and interpret their responses. It can also distinguish an omitted field from an explicitly empty value, which matters when an interface treats one as “leave unchanged” and the other as “clear the existing value.”

The strongest reason to introduce Python is a clearly defined requirement. Keeping straightforward mappings configurable makes the workflow easier to maintain, while custom code handles the logic that needs additional flexibility.

How a Python ETL Pipeline Works

A reliable pipeline establishes a clear contract between its stages: what data enters each stage, what processing occurs, and what output the next stage receives. The following sequence describes a typical workflow, although some checks may also occur during extraction and transformation.

Step #1: Extract the source data

Extraction begins by defining the records and attributes required for the use case. A migration may need an approved snapshot, while a recurring integration may need to select records that have changed since the previous successful run.

Python extraction logic can retrieve the selected data and convert source responses into a structure that downstream components can process. Python’s standard json module, for example, supports decoding JSON responses into Python objects.

Completeness needs explicit attention. Receiving a successful response from an API does not establish that every page was retrieved or that the correct selection criteria were applied.

Practical tip: Preserve source identifiers and relevant extraction metadata. These details help teams trace target records back to their origin and investigate missing or unexpected data.

Step #2: Transform the records

Transformation aligns source data with the agreed target requirements. This can involve standardizing values, applying mapping tables, converting formats, restructuring records, or deriving new attributes.

For example, a source application may use kilogram, kg, and KILO for the same unit. Transformation logic can map those values to an approved target code. However, an unfamiliar value should be handled as an exception, unless an authorized rule defines its meaning.

Structural transformation also needs to preserve business relationships. If a product contains multiple packaging units, separating those units into another dataset must retain the product identifier and the relevant conversion information.

Practical tip: Make transformation rules explainable. A reviewer should be able to identify the source value, the rule applied, and the resulting target value.

Step #3: Validate the prepared data

Validation determines whether the transformed records satisfy the rules required for loading. Typical checks include mandatory attributes, permitted codes, identifier uniqueness, reference validity, and consistency between related records.

Validation should also distinguish between different outcomes. A record may be ready to load, require correction, or be intentionally excluded under an approved scope rule. Treating all three outcomes as a generic processing failure makes remediation more difficult.

Pre-load checks reduce avoidable errors, but target systems may enforce additional rules based on their configuration and current business state. Both sets of checks are necessary.

Practical tip: Return actionable exception details. “Unit mapping missing for source value KILO” gives a data owner a clearer correction task than “Validation failed.”

Step #4: Load the data and verify the result

The loading stage submits approved records through an interface suitable for the destination and business object. In SAP environments, teams need to select the integration option appropriate to their system and scope; SAP documents the integration scenarios and APIs available for them.

The pipeline must then interpret the response. Depending on the interface, a successful submission may indicate acceptance for processing, rather than completion of the business operation.

Verification should connect submitted records to target outcomes. This may include checking acceptance and rejection counts, returned identifiers, selected field values, or relevant totals.

Practical tip: Define the evidence of completion before implementing the loading logic. This prevents teams from treating “request sent” as equivalent to “record successfully processed.”

Python ETL Example: Preparing Product Data for SAP

Consider a manufacturer that maintains product information in a non-SAP application and needs to deliver an approved subset to SAP. The source exposes an API that contains product descriptions, category labels, and packaging information.

Some fields can be mapped directly. Others require custom logic, because the source uses nested structures and business values that differ from the target requirements.

The table below illustrates how the workflow could handle these requirements. It is an example architecture, rather than a description of one universal SAP loading method.

Suppose a product has a base unit of kilograms and an alternative unit of bags. Standardizing the labels does not establish how many kilograms one bag contains. The workflow must either obtain that conversion from an authoritative source or flag the record for correction.

This is where Python ETL provides practical value: custom logic can handle the relationship between attributes, while the surrounding workflow controls selection, mapping, execution, and reporting.

The same pattern can support migration preparation or recurring integration. Each scenario still needs its own selection rules, timing, and recovery design.

Common Python ETL Challenges

Python provides flexibility, but that flexibility also creates responsibilities. Teams need to manage how custom code behaves when source data, execution conditions, or target requirements change.

Common challenges include:

• Changing source structures: An API field may disappear, change type, or become optional. Code that assumes a fixed response can fail or produce incomplete data. For example, a source application might replace a single product category field with a list of classifications. A script expecting one text value could stop processing or overlook additional classifications. Explicit input checks help detect these changes, before unsuitable data reaches downstream components.
• Dependency and environment differences: A script may rely on packages or versions that are available in development, but absent from the execution environment. For example, a developer might use an additional Python package to read a supplier’s Excel workbook, only to discover that the package is unavailable on the server running the scheduled workflow. The pipeline then fails before transformation begins. Documented dependencies and verification in the actual runtime help prevent these deployment problems.
• Growing data volumes: An approach that loads every record into memory may work for a small sample, but struggle with production volumes. For example, a customer data workflow tested with 5,000 records might retrieve several million records during its first full run, then create additional copies, while joining reference data. Memory consumption can exceed the available resources, even though the transformation rules are correct. Batch processing and representative volume tests help reveal these constraints.
• Partial failures: A run may load some records before a connection fails. For example, SAP might successfully process a batch of customer updates, but a network interruption could prevent the pipeline from receiving the confirmation. The workflow cannot safely assume that the batch failed. Resubmitting it, without checking the target outcome, may repeat updates or, for creation operations, produce duplicates, where the interface permits them. Recovery logic needs to account for both confirmed failures and uncertain outcomes.
• Limited troubleshooting information: A technical exception may identify a failed function, without revealing which business record caused it. For example, a conversion error might occur, because one product contains the text “not specified” in a weight field. If the log records only “numeric conversion failed,” support teams may need to search thousands of records to locate the problem. Including the source identifier, field name, problematic value, and processing stage makes the correction task much clearer.
• Unclear ownership of rules: Developers may implement assumptions that have not been approved by business data owners. For example, a developer might replace every missing product weight with zero, so that records pass a numeric-field check. The script runs successfully, but zero may be interpreted as a confirmed weight and affect logistics calculations. Business owners need to define whether missing values should remain empty, use an approved default, or prevent loading.

These challenges show why technical completion and a correct business outcome need separate checks. A reliable pipeline must make unexpected conditions visible and provide enough information for teams to investigate, correct, and resume processing.

Best Practices for Reliable Python ETL Pipelines

Reliable Python ETL combines maintainable code with explicit data and execution controls. These controls help teams understand how records were processed, identify exceptions, and recover from interruptions.

The following practices provide a foundation for workflows that can be operated and maintained beyond their initial implementation:

• Separate configuration from processing logic: Parameters like selection criteria, file locations, and batch settings should be adjustable, without rewriting the core script. This makes the same logic easier to use across projects and environments. Configuration values also need validation. A missing selection date, for example, should not silently turn an incremental extraction into a full load. Recording the effective settings for each run helps teams explain differences in scope and results.
• Keep custom logic modular: Separate retrieval, transformation, validation, and response handling, where practical. Smaller components are easier to review and troubleshoot, and changes to one requirement are less likely to affect unrelated behavior. For example, a revised product-category mapping should not require changes to the code that retrieves API pages. Components should have clear inputs and outputs, with transformation logic kept independent of external connections, wherever possible, so that business rules can be tested in isolation.
• Define data structures explicitly: Document field names, types, optional values, and relationships between datasets. Identifiers should retain their intended representation; converting a code to a number can remove meaningful leading zeros. Define how the workflow interprets missing fields, empty strings, null values, and zero, since these may carry different business meanings. For related datasets, specify the expected relationships — for example, each product can have multiple descriptions, but only one description per language — and check those expectations during processing.
• Manage connections and credentials securely: Use the connection management facilities available in the execution environment, with access limited to the operations the workflow requires. Credentials should be kept out of script source, exported configuration, and diagnostic output. Connection settings should also allow credentials to be renewed without modifying the processing logic. Review error messages, as well as ordinary logs, because failed requests can inadvertently expose authentication details or sensitive record contents.
• Choose batch sizes through measurement: Consider payload size, memory use, source limits, and target response times. Larger batches may reduce request overhead, but they can also increase timeout risk and make failed records harder to isolate. Test with representative records: a batch of products containing many descriptions and packaging entries can behave very differently from the same number of simple product records. Extraction and loading may also need different batch sizes, depending on the capabilities of each interface.
• Test business rules and failure scenarios: Include missing values, unfamiliar codes, malformed records, duplicate identifiers, and interrupted loads. Verify expected field values, record relationships, and exception outcomes alongside successful execution. For example, a test for an unmapped category should confirm that the record is held for correction and receives a useful explanation. Test how the pipeline behaves when the target accepts only part of a request or when a response is lost after processing.
• Design safe recovery behavior: Establish how the pipeline identifies previously processed records and determines whether an operation can be retried. Distinguish temporary technical failures from data errors that require correction; repeatedly submitting an invalid record will not resolve the underlying issue. Where a response is lost, check the target outcome before submitting the operation again. Progress markers should advance only when the required processing outcome is confirmed, so that restarting the workflow does not silently skip unresolved records.
• Record meaningful execution evidence: Logs should identify the run, processing stage, relevant record, and outcome. Include useful context, such as source identifiers, returned target identifiers, processing counts, and actionable error details, while limiting sensitive data. When records are split or combined during transformation, preserve enough traceability to connect the resulting records to their sources. A run summary should also make unresolved exceptions visible, even when the overall execution finishes without a technical error.
• Reconcile outcomes against the selected scope: Account for records that were loaded, rejected, intentionally excluded, or remain unresolved. Where transformations split or combine records, reconciliation must reflect those rules, rather than assume identical source and target counts. For example, 100 products with several language descriptions may legitimately produce more than 100 description records. Counts should be supplemented with relevant checks on identifiers, relationships, field values, or business totals, since matching counts alone cannot establish that the correct data reached the target.

Together, these practices make a pipeline easier to review, support, and adapt. They also provide the evidence needed to determine whether each run achieved its intended data outcome and which records still require attention.

Combining Python with Visual ETL Workflow Design

Python and visual workflow design can complement each other. Visual components provide a way to configure connections, map fields, and coordinate processing. Python extends the workflow where a requirement needs specialized behavior.

For example, a standard connector may retrieve a source table, while custom Python logic restructures selected records. In another scenario, a Python-based source component may retrieve data from an unusual API and pass it to configurable mapping and target components.

The useful dividing line is maintainability. Standard operations can remain visible and configurable, while custom code is concentrated in components with a defined purpose and documented inputs and outputs.

This approach also supports reuse. A developer can create a component whose behavior is controlled by parameters, allowing workflow users to adjust supported options, without changing the implementation.

Reuse still requires clear boundaries. Users need to know which inputs the component accepts, what structures it returns, and how it reports exceptions. A configurable interface makes code easier to use, but the underlying logic still needs review and maintenance.

How Migravion Supports Custom Python ETL

Migravion supports custom Python logic through Plugin Builder Source and Plugin Builder Target. These components allow teams to incorporate specialized behavior into data workflows alongside mapping and other configured processing.

Their relevant capabilities include:

• Custom Python source logic: Plugin Builder Source can initiate a workflow without receiving data from another plugin. Its runtime script can implement source-specific retrieval, generate datasets, or apply custom processing, before making data available to downstream components.
• Configurable input parameters: Configuration fields act as variables for the scripts. Teams can expose relevant settings through fields, such as text inputs, connection selectors, directories, and toggles, allowing supported behavior to be adjusted, without changing the core logic.
• Dynamic field generation: A Python Field Rendering Script can generate the component’s field structure, based on its configuration or external source information. This supports components whose available fields depend on the selected data source or settings.
• Runtime transformations and business rules: Python runtime scripts can process data, apply transformations and conditions, and interact with external systems. Business Rules provide an additional way to define behavior used by the component’s scripts.
• Custom target processing: Plugin Builder Target supports script-driven runtime operations and configurable data fields. Depending on the implementation, this can include preparing target structures, applying specialized logic, and interacting with an external destination.
• Batch and processing settings: Plugin Builder Target provides batch-size and data-processing-mode settings. These controls allow teams to configure execution behavior according to their scenario, with performance verified against actual workloads.
• Reusable source extensions: A configured Plugin Builder Source can be saved as an Extension. This turns custom source logic into a reusable component that can simplify setup across multiple projects.
• Workflow execution and result analysis: After configuring and mapping the components, teams can run the project manually, schedule it, or trigger it through an event. The Target documentation also describes technical reports and CSV logs for analyzing processing results.

In the product-data example, a custom Source component could retrieve and restructure the non-SAP API response. Downstream workflow components could then map and validate the prepared data, before an appropriate SAP target component handles loading. A custom Target component could be introduced where the destination requires additional script-driven behavior.

These capabilities give teams a foundation for custom Python ETL workflows. Each implementation brings together the business rules, validation checks, error handling, and loading requirements needed for its source and target systems.

Conclusion

Python ETL gives teams the flexibility to handle source interfaces, data structures, and business rules that require custom logic. Its value grows when that logic is supported by clear configuration, validation, recovery behavior, and evidence of target outcomes.

For SAP and non-SAP workflows, combining Python with visual design allows teams to keep routine operations configurable, while addressing specialized requirements through code. Migravion’s Plugin Builder components support this approach by bringing custom Python logic into reusable, controlled data workflows.

Request a demo to see how Migravion can support your custom Python ETL requirements in data integration, migration, or data preparation projects.