Skip to content

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

- 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>)
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 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
depends_on Optional List of String: See Object Dependencies for a definition of depends_on List of SOLE-managed objects that this external table depends on. Objects defined in the list will be added to the generated HCL dependencies list to ensure proper creation order.
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
  • APPLYBUDGET
  • DELETE
  • EVOLVE SCHEMA
  • INSERT
  • REBUILD
  • TRUNCATE
  • UPDATE

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

- 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
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