Data Flows

A schema defines the shape of data in a source or target system. When working on a data flow in Data Integration, schema drift occurs when the data definitions change.

For example, an attribute might be added or removed in the source, or an attribute in the target might be renamed. If you don't handle schema drift, the ETL processes might fail or you might lose data quality.

By default, Data Integration handles schema drift for you. When you configure a source operator in the data flow designer, after selecting a data entity, click the Advanced Options tab on the Properties panel. The selected Allow Schema Drift checkbox indicates that schema drift is enabled.

When schema drift is enabled, Data Integration can detect schema definition changes in the specified data entities during data flow design time and runtime. Any changes are automatically picked up and the schema adapted to accommodate new attributes, removed attributes, different attribute names, changed data types, and so on.

If you clear the Allow Schema Drift checkbox, you disable schema drift to lock the schema definitions when the data flow is defined. When schema drift is disabled, Data Integration uses a fixed shape of the specified data entity even when the underlying shape has changed.

Without handling schema drift, data flows can become vulnerable to upstream data source changes. With the help of schema drift, data flows become more resilient and adapt automatically to any changes. You don't need to redesign thedata flows when schema definition changes occur.

For a JSON file, schema drift is disabled by default and can't be enabled if a custom schema is used to infer the entity shape. If you want schema drift to be available and enabled, edit the JSON source in the data flow or data loader task and clear the Use custom schema checkbox.

In Data Integration, a data operation in a data flow can be pushed to a source or target data system for processing.

For example, a sort or filter operation can be performed in the source system while the data is being read. In the case where one of the sources for a join operation is in the same system as the target, the data operation can be pushed to the target system.

Data Integration can use pushdown in a data flow when you use relational data systems that support pushdown. The current list includes Oracle databases, Oracle Autonomous Data Warehouse, Oracle Autonomous Transaction Processing, and MySQL.

By default, Data Integration uses pushdown when applicable. When you configure a source operator in the data flow designer, after selecting a data entity, click the Advanced Options tab on the Properties panel. The selected Allow Pushdown checkbox indicates that pushdown is enabled.

When pushdown is enabled, Data Integration converts applicable data operation logic into SQL statements that are then directly run on the relational database. By pushing data processing to the database, less data is pulled and loaded.

If you clear the Allow Pushdown checkbox, you disable pushdown. When pushdown is disabled, Data Integration pulls all the data from the source system and processes the data on the Apache Spark clusters allocated to theworkspace.

By letting Data Integration use pushdown, performance is improved because:

• The processing power of the database is used
• Less data is ingested for processing.

Based on optimization, Data Integration can use partial or full pushdown in a data flow. Partial pushdown is performed when a supported relational data system is used at the source or target. Full pushdown is performed when the following conditions are present:

• Only one target exists in the data flow.
• In a data flow with a single source, both the source and target use the same connection to a supported relational data system.
• In a data flow with multiple sources, all the sources must also use the same database and connection.
• All the transformation operators and functions in the data flow can generate valid pushdown SQL code.

Data preparation ensures that Data Integration processes ingest accurate and meaningful data with fewer errors to produce quality data for more reliable insights.

Preparing data includes cleansing and validating data to reduce errors, and transforming and enriching data before loading the data into target systems. For example, the data might come from different sources with various formats and even duplicate information. Data preparation might involve removing duplicate attributes and rows, standardizing on a format for all date attributes, and masking sensitive attribute data such as credit cards and passwords.

Data Integration provides out-of-the-box shaping operators and functions, and transformations that you can use in interactive tools to prepare data as you design the logic for the ETL processes. For example, the Attributes tab lets you search incoming attributes by a pattern and apply an exclude rule.

On the Data tab, you can apply transformations on a single attribute, or you can filter the attributes by a name pattern or data type, and then apply transformations on a group of attributes. You can also preview the results of data transformations in the Data tab, without having to run the entire data flow.

In Data Integration, you use the Map tab to describe the flow of data from source attributes to target attributes.

A target can be an existing data entity or a new data entity. In a data flow, the Map tab is only applicable to a target operator for an existing data entity. For existing target data entities, the source attributes and any customized attributes coming from upstream operations are mapped to attributes on the target.

You can choose to use automatic mapping or manual map. For automatic mapping, Data Integration can map incoming attributes to target attributes with the same name, or according to their position in the attribute lists. For manual map, you can drag an incoming attribute from the source list to an attribute in the target list to create a mapping. Alternatively, you can use the Map Attribute dialog to create a mapping by selecting a source attribute and a target attribute. You can also use a source attribute pattern and target attribute pattern to create the mapping.

When you select the Create new data entity checkbox on a target operator, the Map tab is not available. Data Integration uses the incoming source attributes to create the table or file structure with a one to one mapping.

Tools to help you navigate a data flow or pipeline on the canvas include:

• View: Click this menu to choose to open or close the Properties, Operators, Validation, and Parameters panels.
• Zoom in: Lets you zoom in on the design.
• Zoom out: Lets you zoom out to view more of the design.
• Reset Zoom: Returns to the default view of the design.
• Grid Guide: Toggles the grid guides on and off.
• Auto Layout: Arranges the operators on the canvas.
• Delete: Removes the selected operator from the canvas.
• Undo: Removes the last action performed.
• Redo: Performs the last action if you previously clicked Undo.

The canvas is the main work area, where you design the data flow or pipeline .

Drag objects from the Operators panel onto the canvas to begin.

You start with a blank canvas for a data flow. For a data flow to be valid, you must have at least one source and one target defined.

For a pipeline, you start with a canvas that has a start operator and an end operator. The pipeline design must include at least one task operator to be valid.

To connect two operators, hover over an operator until you see the connector (small circle) on the right side of the operator. Then drag the connector to the operator you want to connect to. A connection is valid when a line connects the operators after you drop the connector.

To insert an operator between two connected operators, right-click the connection line and use the Insert menu.

To delete a connection, you can right-click a line and select Delete.

To duplicate a source, target, or expression operator, right-click the operator icon and select Duplicate.

The Operators panel displays the operators you can add to a data flow or pipeline.

Drag operators from the Operators panel onto the canvas to design the data flow or pipeline. Each operator has a different set of properties that you configure using the Properties panel.

For a data flow, you can add input, output, and shaping operators.

For a pipeline, you add input, output, and task operators to construct a sequence.

The Properties panel lets you configure the data flow or pipeline and its operators.

Use the Validation tab to validate the entire data flow or pipeline.

In the Parameters tab, you can view all the parameters defined at the data flow or pipeline level, including system-generated parameters. For user-defined parameters, you can delete parameters, and if applicable, edit the default values of parameters.

For a data flow, after you add an operator on the canvas, select the operator and use the Properties panel to configure the operator. You can:

• Specify the operator details such as the identifier, and configure operator-specific settings in the Details tab.
• View the operator inbound and outbound attributes in the Attributes tab.
• Map incoming attributes to attributes in the target data entity for a target operator in the Map tab.
• Preview a sampling of data in the Data tab.
• Validate the configuration of the operator in the Validation tab.

Similarly for a pipeline, after you add an operator on the canvas, select the operator and use the Properties panel to configure the operator. You can:

• Specify a name for the operator in the Details tab. For a merge operator, you specify a merge condition. For an expression operator, you add one or more expressions. For task operators, you select a task to bind to the operator, and specify when the task runs based on the run status of the upstream operator.

  For all task operators, you can select design-time tasks from projects in the current workspace, and published tasks from any Application in the current workspace. With published REST tasks and OCI Data Flow tasks, you can also select a task from any Application in another workspace in the same compartment or another compartment.

• If applicable, specify task run options in the Configuration tab.
• If applicable, configure incoming parameters in the Configuration tab.
• View the outputs in the Output tab, which can be used as inputs for the next operator in the pipeline.
• If applicable, validate the configuration of the operator in the Validation tab.

The Parameters panel displays the parameters used in a data flow or pipeline.

You can also delete a parameter from the Parameters panel.

You can view all the error and warning messages for a data flow or pipeline in the Global validation panel.

On the canvas toolbar, click Validate to check and debug the data flow before using the data flow in an integration task. Similarly, check and debug the pipeline before using it in a pipeline task. The Global validation panel opens and displays error and warning messages for you to address. Selecting a message brings you to the operator that produced the error or warning message.