1. Best practices for building resilient data integrations
  2. Develop Efficiently and Securely

Develop Efficiently and Securely

Use these best practices to ensure your development process is efficient and safe.

Use Strict Environment and Source Code Control

Careful and strict use of separate environments and source code control is essential to a clean and secure development process:

Many customers use at least two, and often three or more, separate environments. Development, test, stage, and production environments are typical. Use strict control over promotion from environment to environment.

Your source control system must guarantee the consistency of the artifacts:

  • Metadata for creation of tables
  • Sample and source data
  • ETL code
  • PaaS versions and patch levels

Consider a "metadata steward" who controls the evolution of metadata and ensures all developers use the same definitions. Also consider designating a "release manager" who controls when artifacts are imported into any environment other than the dev environment, and who knows at all times who is using which environments for what purpose.

Dedicated environments have several benefits. In addition to a production environment, consider dev, test, and performance environments. These environments can be scaled as needed.

In a dev environment:

  • All changes to the code, even minor ones, are developed.
  • Unit tests (using sample data – often a subset of the real data for faster runs) are performed.
  • Code that is ready for promotion must be identified as such. You can implement processes to review and automatically promote code to a test or production environment.

In a QA environment:

  • Consolidation of the changes made in dev (only changes validated for promotion).
  • Validation of change consistency (using the same metadata, consistent orchestration, etc.) and that code runs properly.
  • No code changes are allowed.

An additional environment used for performance testing can be advantageous. In a performance environment:

  • Establish a baseline for the performance of the code.
  • Limited code changes allowed by a performance team only, to validate performance enhancements.
  • Identify bottlenecks, investigate, and report required changes so they can be implemented in Development.
  • Continue to monitor performance with all code that is certified by the QA team to identify performance drifts and address issues.

Note that it's important to continue to monitor performance in the production environment. Performance degradation is sometimes a warning of a degradation of the environment. In particular, Oracle recommends controlling execution plans using SQL Plan Management (SPM). Execution plan changes in production can cause failures, and table loads cause execution plan change risks.

Automate Administration Tasks

Manual admin tasks take time and add risk:

  • Manually resetting the environment to retry a load (due to code error, product bug, code change, etc) relies on developers running SQL statements manually.
  • Manual processes are error prone and errors can further delay the next try.
  • Manual processes are slow: developers need to be careful with what they are doing and lose a lot of time double-checking that they have done the right thing.
  • Errors are costly: the need to fix the errors (potentially re-loading erased data) can delay development work.
  • Onboarding of new developers is slower and induces more risk because they must be trained to follow manual processes.

Admin tasks should be fully automated so that developers can just set a few parameters and run a job that is already validated to perform the correct tasks each time.

Consider automating the following tasks:

  • Reset environment data, variables, and parameters to allow for the retry of a load.
  • Rebuild environments: PaaS tools and patches when needed, metadata, data, PaaS code. This should include the ability to run an initial load of the target environment.
  • Purge Oracle Data Integrator logs and scenario reports (which improves performance): the OdiPurgeLog tool available in packages can be used for creating dedicated scenario and schedule purges.
  • Purge staging tables that were used for loads that were successful.
  • Purge or re-locate files that were successfully loaded.
  • When patching, use silent installs (record patching process in a development environment, and then replay in other environments).

Manage Patching

Patching is important to keep your systems updated with the latest security and bug fixes, but a poorly-managed patching regimen can create vulnerabilities and cause unexpected downtime events.

Oracle recommends:

  • Validate who in your organization will receive and respond to alerts from Oracle about patching.
  • Document the patching level of all of your environments. Note that some services are patched and upgraded automatically by Oracle, but you may need to manually patch non-native services and especially any on-premises instances.
  • Define patching windows. For manual patching, identify time windows that are least disruptive for your users.

Follow User Account Best Practices

Follow best practices with user accounts to ensure security of your data, environments, and integrations.

Each developer must have their own Oracle Data Integrator user account, even if they are granted supervisor privileges. Do not share accounts. Separated user accounts improves communication and ensures accountability:

  • Locked objects will show who is editing the object.
  • Last updates will show who last modified an object, and when.

When Oracle Data Integrator needs access to a database:

  • Create a dedicated database user specifically for Oracle Data Integrator. Do not re-use an individual user's account, or an account used by another service.
  • The Oracle Data Integrator user should be the owner of the staging schema (used to perform create, drop, insert, update, and delete operations).
  • The Oracle Data Integrator user should have privileges limited to actual use for other schemas (select, insert, update, and delete).

Use Proven Utilities

Some tools work better that others: stick with the ones that are proven to work in your environment, and document the standard so that new team members know what to use.

For example:

  • Key generation: There are several formats for keys. Make sure to document the correct key format your developers should use (for example, you may require the use of the RSA format, and prohibit the OpenSSH format).
  • ZIP utilities: Not all content is compressed the same way by different utilities. Make sure you have control over the compression algorithm that is used to compress files. Oracle recommends the use of 7Zip.