Skip to main content

DDL reference Private Preview

When you deploy a context repository, CES compiles your YAML semantic model into engine-specific DDL and executes it on your account. You never write this DDL directly, CES generates it, but understanding the structure helps you debug deploy failures, grant access to end users, and verify what was created. Select your engine below.

CES compiles your Snowflake semantic model YAML into a CREATE OR REPLACE SEMANTIC VIEW statement and executes it on your Snowflake account using the Atlan service role.

CREATE OR REPLACE SEMANTIC VIEW PROD_DB.ANALYTICS.SALES_PIPELINE

  tables (
    PROD_DB.SALES.OPPORTUNITIES as OPPORTUNITIES         -- physical table mapped to logical alias
      comment = 'Active and closed sales opportunities.',

    PROD_DB.SALES.ACCOUNTS as ACCOUNTS
      comment = 'Customer and prospect account records.'
  )

  facts (
    OPPORTUNITIES.ANNUAL_RECURRING_REVENUE as TOTAL_ARR  -- aggregate measure
      comment = 'Sum of annual recurring revenue.',

    OPPORTUNITIES.DEAL_SIZE as DEAL_COUNT                -- fact from a different column
      comment = 'Count of deals in the pipeline.'
  )

  dimensions (
    OPPORTUNITIES.STAGE_NAME as STAGE_NAME               -- categorical dimension
      comment = 'Current stage in the sales cycle.',

    OPPORTUNITIES.CLOSE_DATE as CLOSE_DATE               -- time dimension
      comment = 'Expected or actual close date.',

    ACCOUNTS.REGION as REGION
      comment = 'Geographic region of the account.'
  )

  relationships (
    OPPORTUNITIES(ACCOUNT_ID) references ACCOUNTS(ACCOUNT_ID)  -- join condition
      with relationship type left join
  );

Statement structure​

ClauseRequiredDescription
CREATE OR REPLACE SEMANTIC VIEWYesCreates or replaces the semantic view at the fully qualified path <database>.<schema>.<view_name>. CES derives the view name from your repository name.
tables (...)YesMaps one or more physical Snowflake tables to logical aliases. Each alias is referenced in facts, dimensions, and relationships.
facts (...)NoAggregate measures compiled from your YAML metrics[]. Each fact maps a column to a logical name with an optional comment.
dimensions (...)NoCategorical and time attributes compiled from your YAML dimensions[] and time_dimensions[].
relationships (...)NoJoin definitions compiled from your YAML relationships[]. Omitted if your repository has only one table.

tables clause​

tables (
  <database>.<schema>.<table> as <alias>
    comment = '<description>'
)
ElementDescription
<database>.<schema>.<table>Fully qualified physical table path. Must match the base_table in your YAML.
as <alias>Logical alias used everywhere else in the DDL. Compiled from the name field on your YAML table. UPPER_SNAKE_CASE.
commentCompiled from description in the YAML. Truncated at 200 characters.

facts clause​

facts (
  <alias>.<column> as <fact_name>
    comment = '<description>'
)
ElementDescription
<alias>.<column>Table alias and physical column reference. Compiled from the expr field on the YAML metric.
as <fact_name>Logical name for the measure. Compiled from the name field on the YAML metric. UPPER_SNAKE_CASE.
commentCompiled from description in the YAML. Truncated at 200 characters.
note

The DDL clause is named facts, while the YAML uses metrics. CES handles this translation automatically. Both terms refer to the same concept: aggregate measures your business cares about.

dimensions clause​

dimensions (
  <alias>.<column> as <dim_name>
    comment = '<description>'
)

Both dimensions[] and time_dimensions[] from your YAML compile into the same dimensions clause in the DDL. Cortex Analyst distinguishes time dimensions from categorical ones based on the column's Snowflake data type (DATE or TIMESTAMP_TZ), not from a separate DDL clause.

ElementDescription
<alias>.<column>Table alias and physical column reference. Compiled from expr in the YAML.
as <dim_name>Logical name. Compiled from name in the YAML. UPPER_SNAKE_CASE.
commentCompiled from description in the YAML. Truncated at 200 characters.

relationships clause​

relationships (
  <left_alias>(<left_col>) references <right_alias>(<right_col>)
    with relationship type <join_type>
)
ElementDescription
<left_alias>(<left_col>)Left-hand table alias and join column. Compiled from left_table and relationship_columns[].left_column in the YAML.
references <right_alias>(<right_col>)Right-hand table alias and join column. Compiled from right_table and relationship_columns[].right_column.
with relationship type <join_type>Compiled from join_type in the YAML. Values: LEFT JOIN, INNER JOIN, FULL JOIN. Defaults to LEFT JOIN.

Verify deployment​

After CES runs the DDL, confirm the semantic view was created:

SHOW SEMANTIC VIEWS IN SCHEMA <database>.<schema>;

To inspect the view definition:

SELECT GET_DDL('SEMANTIC_VIEW', '<database>.<schema>.<view_name>');

Grant access to end users​

Business users need explicit grants to query the semantic view through Cortex Analyst. Replace <consumer_role> with the Snowflake role assigned to your users.

-- Grant access to a specific semantic view
GRANT REFERENCES, SELECT ON SEMANTIC VIEW <database>.<schema>.<view_name>
  TO ROLE <consumer_role>;

-- Grant access to all future semantic views in the schema
GRANT SELECT ON FUTURE SEMANTIC VIEWS IN SCHEMA <database>.<schema>
  TO ROLE <consumer_role>;
GRANT REFERENCES ON FUTURE SEMANTIC VIEWS IN SCHEMA <database>.<schema>
  TO ROLE <consumer_role>;

Update or replace​

CES always uses CREATE OR REPLACE SEMANTIC VIEW. Re-deploying a certified repository replaces the existing view in Snowflake. End-user grants are preserved across replacements because grants apply to the view path, not the view definition.

See also​