This section covers the following best practices:
.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.
You can use schemas to allow the components of a particular feature to be “dropped” cleanly using a DROP SCHEMA ____ CASCADE command.
Best practice is to place credentials (name/pass/URI) for external systems into a JNDI (Java Naming Directory Interface) properties file, which can be protected with Linux permissions.
This is much better than having these credentials visible in clear text in the SQL code (foreign server/stream declarations or UDX call).
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.
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).