Best Practices

This section covers the following best practices:

Organizing .sql files for an implementation

.sql files can be organized to match the acquire/condition, analyze, and deliver sub-pipelines in an application. Such files can also have built-in interchangeability so that different data sources can be swapped in and out without requiring alteration of the remainder of the pipeline.

One schema or multiple schemas

You can use schemas to allow the components of a particular feature to be “dropped” cleanly using a DROP SCHEMA ____ CASCADE command.

You can also use schemas to simplify the starting/stopping of pumps for individual application modules using ALTER PUMP myschema.*;


A foreign stream can inherit options from its foreign server. In this way, you can create a foreign server with common options and use it for multiple foreign streams, each of which would have just the specific options needed for its variant.

For example, you could configure a foreign server for FileWriter that serves as an XML writer with default naming convention and rotation settings, then have multiple foreign streams which vary by target filenames, rotation frequency, and so on.

JNDI files vs inline OPTIONS

JNDI properties files can be used to customize options on deployment - so the same SQL can be used for development, test and production, changing only the properties files.


In commenting SQL, it is best to put short summaries for each object in DESCRIPTION (these are visible in s-Studio).

Longer explanations should be placed in object headers (these are visible in SQLdocs).