EXTERNAL TABLE

Config path: /<database>/<schema>/external_table/<name>.yaml

Example:

columns:
  dt:
    type: DATE
    expr: "to_date(split_part(metadata$filename, '/', 2))::date"
    comment: "Date of ingestion"

  id:
    type: NUMBER(38,0) NOT NULL
    expr: "$1:id::number(38,0)"

  name:
    type: VARCHAR(255) NOT NULL
    expr: "$1:name::varchar(255)"

location:
  stage: test_external_stage
  file_format: test_parquet_format

partition_by: [dt]

Schema

• columns (dict)

  • {key} (ident) - column name

  • {value} (dict)

    • type (str) - full data type with optional "NOT NULL" constraint (not enforced)
    • expr (str) - SQL expression describing column

    • comment (str)

• location (dict)

  • stage (ident) - stage name

  • path (str) - path prefix for files stage

  • pattern (str) - regular expression to filter files in stage

  • file_format (ident) - file format for files in stage
• partition_by (list)

  • {items} (ident) - column names for PARTITION BY

• partition_type (str) - example: USER_DEFINED

• auto_refresh (bool) - enable AUTO_REFRESH (default: False)

• refresh_on_create (bool) - refresh once immediately after creation (default: False)

• aws_sns_topic (str) - SNS topic for S3 bucket

• table_format (str) - example: DELTA

• integration (ident) - notification integration name for Azure
• comment (str)

• primary_key (list)

  • {items} (ident) - column names for PRIMARY KEY constraint

• unique_keys (list)

  • {items} (list)

    • {items} (ident) - column names for UNIQUE KEY constraint

• foreign_keys (list)

  • {items} (dict) - FOREIGN KEY definitions

    • columns (list)

      • {items} (ident) - column names from current table

    • ref_table (ident) - reference table

    • ref_columns (list)

      • {items} (ident) - column names from reference table

Policy reference parameters

• row_access_policy (dict)

  • policy_name (ident) - name of ROW ACCESS POLICY
  • columns (list)

    • {items} (ident) - reference column names

Usage notes

1. If automatic metadata refresh it not available for your cloud provider, it should be implemented separately. SnowDDL cannot refresh external table metadata for you.

2. file_format can only be specified by name referencing to FILE_FORMAT object.

Links

• CREATE EXTERNAL TABLE
• SHOW EXTERNAL TABLES
• DESC EXTERNAL TABLE
• Working with External Tables
• Parser & JSON Schema (GitHub)