# EXTERNAL TABLE

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

Example:

```yaml
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)
    * <mark style="background-color:red;">**type**</mark> (str) - full [data type](/guides/data-types.md) with optional "NOT NULL" constraint (not enforced)
    * <mark style="background-color:red;">**expr**</mark> (str) - SQL expression describing column
    * **comment** (str)
* <mark style="background-color:red;">**location**</mark> (dict)
  * <mark style="background-color:red;">**stage**</mark> (ident) - stage name
  * **path** (str) - path prefix for files stage
  * **pattern** (str) - regular expression to filter files in stage
  * <mark style="background-color:red;">**file\_format**</mark> (ident) - [file format](/basic/yaml-configs/file-format.md) 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](/guides/other-guides/integrations.md) 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](/basic/yaml-configs/row-access-policy.md)
  * **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](https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html)
* [SHOW EXTERNAL TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-external-tables.html)
* [DESC EXTERNAL TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-external-table.html)
* [Working with External Tables](https://docs.snowflake.com/en/user-guide/tables-external.html)
* [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/external_table.py)


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.snowddl.com/basic/yaml-configs/external-table.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.