Skip to main content

External Table

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with external table:

  • Manage the lifecycle of new and existing external tables
  • Manage grants of external table

Usage

note

We have introduced SOLE for Data Products as a new framework for SOLE to help you easily build an ecosystem of data products. The major difference is in how you define Snowflake objects in the configuration file. Rather than having a grouped collection of objects, SOLE for Data Products goes for modular, self-describing, and explicit object definition.
Learn more about SOLE for Data Products, currently available as a private preview.

We have also introduced Data products as an extra layer on top of the data product platform capabilities making managing data products easier than ever. Learn more about Data Products, currently available as a private preview.

databases:
<database-name>:
schemas:
<schema-name>:
external_tables:
<external-table-name>:
<configuration-key>: <value>
grants:
<privilege>:
- <role-name>
- <role-name>

Supported parameters

The engine supports the parameters listed below.

Configuration KeyRequired/OptionalData Types and ValuesDescription
columnsRequiredObject: See here for a definition of columnsDefines the column to create in the external table. Minimum one required
file_formatRequiredMap: See here for a definition of file_formatSpecifies the file format for the external table. For info about the options of file format, see Snowflake Docs.
locationRequiredString/Map: See here for a definition of locationSpecifies a location for the external table
auto_refreshOptionalBooleanSpecifies whether to automatically refresh the external table metadata once, immediately after the external table is created
aws_sns_topicOptionalStringSpecifies the AWS SNS topic for the external table
commentOptionalStringSpecifies a comment for the external table
copy_grantsOptionalBooleanSpecifies to retain the access permissions from the original table when an external table is recreated using the CREATE OR REPLACE TABLE variant
deletedOptionalBoolean: True enables deletion prevention, False does nothingSpecifies what objects are allowed to be deleted
grantsOptionalMap: See Supported External Table Grants to RolesLists Privileges and Roles to which privileges are granted on the current external table
manage_modeOptionalString: all (default), none, grantsConfigures what properties to manage for the external table.
See Changing Manage Mode before changing the value.
ownerOptionalStringName of the role that owns the external table
partition_byOptionalList of Column namesSpecifies any partition columns to evaluate for the external table
patternOptionalStringA regular expression pattern string
refresh_on_createOptionalBooleanSpecifies whether to refresh when an external table is created
warning

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phases for a few parameters like comment, columns, null_input_behavior, return_behavior, etc.

file_format parameter

You can configure the file_format options for the external table. You can find the configurations it accepts here. Regardless of the configurations, some parameters are specific to the external table:

  • The type configuration key is format_type and it accepts the following values:
    • CSV
    • JSON
    • AVRO
    • PARQUET
  • For format_type: PARQUET, below are possible values for the compression parameter:
    • AUTO
    • SNAPPY
    • NONE

Summary of supported parameters by file format

  • If format_type is CSV
    • compression
    • record_delimiter
    • field_delimiter
    • skip_header
    • skip_blank_lines
  • If format_type is JSON
    • compression
  • If format_type is AVRO
    • compression
  • If format_type is PARQUET
    • compression

Examples

Specifying/Referencing file format

file_format:
format_database: "SALES"
format_schema: "PUBLIC"
format_name: "CSV_FORMAT"

Specifying file format type

file_format:
format_type: "CSV"
compression: "AUTO"
field_delimiter: ","
skip_header: 1
skip_blank_lines: false

location parameter

You can specify the name of the stage (if it belongs to the same schema and database as the external table) or the name of the schema and database as well.

See Database-Level Object Reference section for more information

The Location parameter supports the following parameters if explicitly provided:

Configuration KeyRequired/OptionalData Types and ValuesDescription
databaseRequired - if Stage is present in another Database than the current oneStringName of the database, in which the stage exists
schemaRequired - if Stage is present in another Database or Schema than the current oneStringName of the schema, in which the stage exists
stageRequiredStringName of the Stage
pathOptionalStringPath in the external stage

Example

location:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE"
path: "lifecycle_runner"

columns parameter

You can specify the columns of the external table to be managed.

Multiples columns in an object format can be listed in the columns parameter, with each having the following supported parameters:

Configuration KeyRequired/OptionalData Types and ValuesDescription
ASRequiredStringSpecifies the expression for the column. When queried, the column returns results derived from this expression.
typeRequiredStringData type of the Column

Example

columns:
NAME:
type: "text"
as: "(value:c1::text)"
YEAR:
type: "text"
as: "(value:c2::text)"

Supported external table grants to roles

Following are the privileges you can grant to roles in the external table definition:

  • ALL PRIVILEGES
  • SELECT
  • OWNERSHIP
  • REFERENCES
ALL PRIVILEGES handling

When you define ALL PRIVILEGES in the SOLE configuration file, you grant all the privileges listed above to roles on this object except OWNERSHIP. However, the management of ALL PRIVILEGES in SOLE differs from its handling in Snowflake. See Handling ALL PRIVILEGES in SOLE for more information.

Examples

databases:
SALES_RECORD:
schemas:
SALES:
external_tables:
EXTERNAL_TABLE_1:
file_format:
format_type: "CSV"
compression: "AUTO"
location:
database: "DUMMY_DATABASE"
schema: "PUBLIC"
stage: "LIFECYCLE_STAGE"
path: "lifecycle_runner"
columns:
NAME:
type: "text"
as: "(value:c1::text)"
YEAR:
type: "text"
as: "(value:c2::text)"
comment: "Test external table"
pattern: ".*[.]csv"
auto_refresh: true
copy_grants: false
refresh_on_create: true