Data Warehouse Schema Types

The tables in a Vertica database are assumed to obey a star schema or snowflake schema design, which are typical schema designs used in data warehouses. These designs produce excellent performance on any Vertica system platform. Each design is discussed in a separate section below.

If your full schema cannot be described as a star or snowflake, consider reorganizing the schema into one or more stars and/or snowflakes; a single Vertica database can hold multiple star and/or snowflake schemas that are queried separately and the result tables joined in the application program. (Multiple schemas may be included in a future release.)

If you have a denormalized single-table schema, you can consider it to be the fact table in a star schema. If you do this, however:

• Avoid using SELECT * queries, which in this case defeat the advantages of using a column store.
• Use the LIMIT Clause to avoid very large query result sets.

Porting an Existing Schema

You may need to reconstruct the CREATE TABLE and CREATE VIEW statements, etc., that define an existing schema. An ETL tool can do this for you or your source database system may have a way to generate DDL. For example:

• DB2 has the db2look tool.
• Oracle has the DBMS_METADATA package.

Keep in mind that these tools (especially from Oracle) tend to use proprietary data types and additional storage clauses, so some edits will be needed.