External Table

You can provide configuration to Snowflake Object Lifecycle Engine for the following operations with an 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 public 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.

Default Configuration

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

Data Products Configuration

- external_table:
    name: <external-table-name>
    <configuration-key>: <value>
    database: rel(database.<database-name>)
    schema: rel(schema.<schema-name>)
    grants:
      <privilege>:
        - rel(role.<role-name>)
        - rel(role.<role-name>)

Supported parameters

The engine supports the parameters listed below.

Configuration Key	Required/Optional	Data Types and Values	Description
columns	Required	Object: See here for a definition of columns	Defines the column to create in the external table. At least one is required
file_format	Required	Map: See here for a definition of file_format	Specifies the file format for the external table. For info about the options of file format, see Snowflake Docs.
location	Required	String/Map: See here for a definition of location	Specifies a location for the external table
auto_refresh	Optional	Boolean	Specifies whether to automatically refresh the external table metadata once, immediately after the external table is created
aws_sns_topic	Optional	String	Specifies the AWS SNS topic for the external table
comment	Optional	String	Specifies a comment for the external table
copy_grants	Optional	Boolean	Specifies to retain the access permissions from the original table when an external table is recreated using the CREATE OR REPLACE TABLE variant
deleted	Optional	Boolean: True enables deletion prevention, False does nothing	Specifies what objects are allowed to be deleted
grants	Optional	Map: See Supported External Table Grants to Roles	Lists Privileges and Roles to which privileges are granted on the current external table
manage_mode	Optional	String: all (default), none, grants	Configures what properties to manage for the external table. See Changing Manage Mode before changing the value.
owner	Optional	String	Name of the role that owns the external table
partition_by	Optional	List of Column names	Specifies any partition columns to evaluate for the external table
pattern	Optional	String	A regular expression pattern string
refresh_on_create	Optional	Boolean	Specifies 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 Key	Required/Optional	Data Types and Values	Description
database	Required - if Stage is present in another Database than the current one	String	Name of the database, in which the stage exists
schema	Required - if Stage is present in another Database or Schema than the current one	String	Name of the schema, in which the stage exists
stage	Required	String	Name of the Stage
path	Optional	String	Path 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 manage.

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

Configuration Key	Required/Optional	Data Types and Values	Description
AS	Required	String	Specifies the expression for the column. When queried, the column returns results derived from this expression.
type	Required	String	Data 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

Default Configuration

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

Data Products Configuration

- external_table:
    name: EXTERNAL_TABLE_1
    database: rel(database.SALES_RECORD)
    schema: rel(schema.SALES)
    file_format:
      format_type: "CSV"
      compression: "AUTO"
    location:
      database: rel(database.DUMMY_DATABASE)
      schema: rel(schema.PUBLIC)
      stage: rel(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