Data types and comments for Views, Materialized Views, and Dynamic Tables

Data types for views and similar objects (e.g., Dynamic Tables and Materialized Views) that don't include data types in their DDL definitions are visible in SqlDBM projects. This gives you a consistent, fully documented schema across every object type in your project.

Views have additional visualization options. This feature may not be available by default and might need to be enabled upon request. Please contact your account manager for more information.
 

The flavor of data types visibility will depend on whether your SqlDBM project supports editable View columns or not. Right now that includes the following but more will be added to this list:

• Snowflake

• Redshift

• Google BigQuery

• Databricks

All other project types allow users to dynamically infer data types and columns from upstream sources through the "Analyze views" button, but do not allow importing or custom editing. 

Visualizing data types on the diagram

Data types for Views must be enabled on the diagram using the canvas properties. As these data types are metadata-only, they use a separate visibility option than tables do (i.e., "Data type"). 

To access this setting, click on an empty space in a diagram, and make sure from the right panel: Diagram Properties → View mode options → "Data type for views" is enabled. 

Editing data types

On Snowflake and Databricks, you can view and edit data types in two places. Both work the same as they do for Table columns.

• Canvas — enter object edit mode, click the data type field next to a column, and type a value or pick one from the dropdown. Parameters are supported, e.g. VARCHAR(255), NUMBER(38,0).
• Column Properties editor — the Data type field under Column Properties → Column works the same as it does for table columns.

Block column format (Snowflake and Databricks)

Because data types can't live inside the CREATE VIEW statement itself, SqlDBM uses a SQL comment block prepended to the DDL to carry them:

/* SqlDBM_col_dt
Object> rep."my_view"
Columns> id: number(10,0), name: varchar, curr_date: date
*/
CREATE OR REPLACE VIEW rep."my_view" (
  id,
  name,
  curr_date
) AS
SELECT id, name, curr_date FROM dummy;

• On Forward Engineering, SqlDBM emits this block before the DDL so the data types travel with the script.
• On Reverse Engineering (DDL upload or Direct Connect), SqlDBM parses the block and populates data types on the matching columns. Columns not listed in the block are left without a data type.

Reverse Engineering — by database

Virtual relationships

When an object participates in a virtual relationship:

• If a column in the pair has no data type set, the pair is not validated.
• If both columns have data types, SqlDBM checks that they match. A mismatch raises a "Data type mismatch paired columns" warning in the Column pairs section of the column Properties editor.

Excel import and export

• Export — data types are written to the DATA TYPE column on the Columns tab of the structure file. Empty cells indicate no data type is set.
• Import — any data types present in the DATA TYPE column are applied to the matching object columns. Blank cells leave the column without a data type.
• On Snowflake, new columns for views, materialized views, and dynamic tables can also be created via Excel import. On all other databases, Excel can only set data types on columns that already exist.

Copy, clone, and drag

Data types follow columns:

• Copying or cloning an object (within a project or to another project of the same database type) preserves column data types.
• Dragging a column from one object to another keeps its data type in the target.

Validation

If a data type is set on a column:

• It's validated against the list of supported data types for the project's database.
• Invalid values surface a diagram error so you can fix them.

A column with no data type set does not raise any error — data types on views, materialized views, and dynamic tables are always optional.