From Metadata to DDL

Guide objective

The purpose of this guide is to provide a high-level understanding of how the DDL statements are generated based on metadata.

Agile Data Engine creates DDL statements automatically based on the metadata provided by developers. The DDL statements are created based on the metadata of entity properties, Attributes, Physical types, Physical options, Keys & References, Permissions, and chosen target database product. In addition, changes made to metadata are reflected in DDL statements automatically and in the target database when the changes are deployed.

Metadata of example table

Let’s look at the example below. We list the metadata at first and after that explain how the DDL statements are generated based on those.

In the Entity properties metadata picture we see the

• entity name ¹

• entity type ²

• physical type ³

• distribution style (which is left empty in this example) ⁴

• (database) schema ⁵

• and description ⁶

Because the physical type is table, Agile Data Engine creates a CREATE TABLE statement. The name of the table is STG_TAXI_ZONES_CSV, it is created on the database schema staging with a comment / description NY taxi zones in CSV format.

In the Attributes list picture we see all the attributes created for this entity and some of the attributes metadata. The CREATE TABLE statement will contain all the attributes given here.

In the Attribute metadata picture we see a complete list of available metadata for an attribute such as the comment / description of the attribute⁷. This is where you can edit the metadata of the attribute. The changes are reflected in the DDL statements automatically and in the target database when the changes are deployed.

In the Physical options picture we see the physical options given to the entity. Some of the options are database product specific and are added only to DDL statements of database products that support them. For example, the STORAGE.COLUMN_STORE⁸ physical option is supported by Azure SQL Database and on Azure Synapse SQL. And the STORAGE.TRANSIENT_TABLE⁹ physical option is supported by Snowflake.

In the Key list picture we see the keys defined for this entity. In this case the locationid is defined to be a primary key¹⁰.

DDL of example table

Agile Data Engine generates DDL statements based on metadata. This allows the DDL statements to be automatically generated for each supported database product. Below are examples of two different database products.

Snowflake

Agile Data Engine generates the following Snowflake specific DDL statement based on the previously provided metadata. The statement is a CREATE TABLE³ statement with the TRANSIENT⁹ keyword. The table’s name is STG_TAXI_ZONES_CSV¹ created on the staging⁵ schema with a comment⁶. The attribute list is based on the defined attributes with the given data types, constraints, comments⁷ and so on. Finally, a PRIMARY KEY constraint¹⁰ is given.

Note, the superscripts refer to the previous Metadata of example table chapter.

CREATE TRANSIENT TABLE staging.STG_TAXI_ZONES_CSV (
    locationid TINYINT  NOT NULL COMMENT 'Business key'
    , borough VARCHAR (20) NULL
    , zone VARCHAR (50) NULL
    , service_zone VARCHAR (20) NULL
    , CONSTRAINT PK_STG_TAXI_ZONES_CSV PRIMARY KEY ( locationid )
)
COMMENT='NY taxi zones in CSV format';

Azure SQL Database

Agile Data Engine generates the following Azure SQL Database specific DDL statement based on the previously provided metadata. The statement is a CREATE TABLE³ statement. The table’s name is STG_TAXI_ZONES_CSV¹ created on the staging⁵ schema. The comments^6,7 are added as extended properties. The attribute list is based on the defined attributes with the given data types, constraints, and so on. A clustered COLUMNSTORE index⁸ is given based on the physical options. Finally, a PRIMARY KEY constraint¹⁰ is given.

Note, the superscripts refer to the previous Metadata of example table chapter.

CREATE TABLE [staging].[STG_TAXI_ZONES_CSV] (
    [locationid] TINYINT  NOT NULL
    , [borough] VARCHAR (20) NULL
    , [zone] VARCHAR (50) NULL
    , [service_zone] VARCHAR (20) NULL
    , INDEX [STG_TAXI_ZONES_CSV_CSI] CLUSTERED COLUMNSTORE
);

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'NY taxi zones in CSV format', @level0type = N'Schema', @level0name = 'staging',
@level1type = N'TABLE', @level1name = 'STG_TAXI_ZONES_CSV';

EXEC sp_addextendedproperty @name = N'MS_Description', @value = 'Business key',
@level0type = N'Schema', @level0name = 'staging',
@level1type = N'TABLE', @level1name = 'STG_TAXI_ZONES_CSV', @level2type = N'Column', @level2name = 'locationid';

ALTER TABLE [staging].[STG_TAXI_ZONES_CSV] ADD CONSTRAINT [PK_STG_TAXI_ZONES_CSV] PRIMARY KEY ([locationid] );