SnowDDL
Search
K
Comment on page

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

Usage notes

  1. 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. 2.
    file_format can only be specified by name referencing to FILE_FORMAT object.