Best practices

Object Metadata

It is to be expected that Data Catalog object definitions change over time. Use these best practice guidelines to ensure that object definitions are in sync with Compass data storage and query functionality.

• Add new properties to the metadata as required.
• Add a new property to replace an existing property, leave the deprecated property in the metadata definition.
• Loosen constraints of existing properties, such as extending string lengths or increasing the scale and precision of decimal values. Do not tighten constraints.
• Do not remove or rename properties.
• Do not change the data type of an existing property. This makes old and new data incompatible. Instead, add a new property.
• Use the identifier paths, variation path and delete indicator properties for variation handling.

In the Data Catalog, object names are not case-sensitive. In the Data Catalog metadata, properties are case-sensitive. For Compass queries, all object names and property names are case-insensitive. Therefore, do not use duplicate property names in a data object with different capitalization. For example, the Compass query functionality cannot detect a difference between properties named ITEM, Item, and item. Good practice is to use distinct property names as a rule.

These characters are supported for object names:

• A through Z in uppercase or lowercase
• 0 through 9
• Underscore

These characters are supported for property names:

• A through Z in uppercase or lowercase
• 0 through 9
• Underscore, period, and space

Use double quotes around object names and property names that include an underscore or spaces or begin with a number. Use double quotes around names that are SQL reserved words.

Query syntax is case-insensitive. The documentation uses uppercase to highlight the query functionality and keywords. Uppercase is not required unless specifically noted for a function or keyword.

Data values in Data Lake data objects are case-sensitive. Values such as “ABC” and “abc” are distinct.

Batch Ingestion API

As a best practice, applications can include condition-based logic that micro-batches data for a pre-determined amount of time, or if a certain amount of data is buffered. There is no file size limitation imposed within the service, but it is best practice that files are no smaller than 5 MB and no larger than 50 MB. This reduces risks associated with network interruptions and improves overall processing performance of downstream consumers that directly interface with raw data objects.

Query Hints

Hints provide extra instructions to execute a query. Hints must be placed on the first lines of the query in the first position, before the SELECT or WITH clause. Query hints are optional, and one or more hints may be used. The hint syntax starts with two hyphens followed by an asterisk: –*. Hints are case insensitive, but they are documented with mixed case to distinguish the hint syntax.

We recommend that you use infor.() table functions when available instead of query hints. This best practice should improve Compass performance.

Compass SQL API

You should select an optimal size for downloading query results based on hardware constraints and network timeouts. This will also allow you to re-retrieve a portion of the result in the event of a connection failure. Keep in mind that the result of a query is static, and does not change when data is added or updated in the Data Lake. The page size is based on rows, with a maximum limit of 100,000 rows with a 10 MB size limit. The version 2 Compass APIs functionality uses OFFSET and LIMIT parameters for the result set retrieved. Query results are usually available for roughly 20 hours after receiving the FINISHED status.