Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #309 (2020-11-22 02:31:39)

[feature] external table resource
This introduce resource creation and deletion for external table.

## Test Plan
* [x] acceptance tests

## References
https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html

fevin86: @ryanking can you please have another look at this PR?
yonidavidson: Hi, does it expose notification_channel so I can use it to register to s3 events?
ryanking: @fevin86 can you merge the main branch and resolve conflicts?
fevin86: @ryanking, I have merged the main and fixed the build. Can you please have a look again?
ryanking: /ok-to-test sha=690609c
ryanking: /ok-to-test sha=db20625
alldoami: /ok-to-test sha=a9947c4
ryanking: /ok-to-test sha=dc4cbc11670ccbdc5fedd24039b95d70d050b3ba
github-actions[bot]: Integration tests failure for [dc4cbc11670ccbdc5fedd24039b95d70d050b3ba](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/488553218)
ryanking: @fevin86 see test failures
fevin86: @ryanking can you kick off the integration test again?

Patch:
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index 541b2ef9..957368ba 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -114,6 +114,7 @@ func getResources() map[string]*schema.Resource {
 		"snowflake_storage_integration":       resources.StorageIntegration(),
 		"snowflake_stream":                    resources.Stream(),
 		"snowflake_table":                     resources.Table(),
+		"snowflake_external_table":            resources.ExternalTable(),
 		"snowflake_task":                      resources.Task(),
 		"snowflake_user":                      resources.User(),
 		"snowflake_view":                      resources.View(),
diff --git a/pkg/resources/external_table.go b/pkg/resources/external_table.go
new file mode 100644
index 00000000..e95570e9
--- /dev/null
+++ b/pkg/resources/external_table.go
@@ -0,0 +1,331 @@
+package resources
+
+import (
+	"bytes"
+	"database/sql"
+	"encoding/csv"
+	"fmt"
+	"strings"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+	"github.com/pkg/errors"
+)
+
+const (
+	externalTableIDDelimiter = '|'
+)
+
+var externalTableSchema = map[string]*schema.Schema{
+	"name": {
+		Type:        schema.TypeString,
+		Required:    true,
+		ForceNew:    true,
+		Description: "Specifies the identifier for the external table; must be unique for the database and schema in which the externalTable is created.",
+	},
+	"schema": {
+		Type:        schema.TypeString,
+		Required:    true,
+		ForceNew:    true,
+		Description: "The schema in which to create the external table.",
+	},
+	"database": {
+		Type:        schema.TypeString,
+		Required:    true,
+		ForceNew:    true,
+		Description: "The database in which to create the external table.",
+	},
+	"column": {
+		Type:        schema.TypeList,
+		Required:    true,
+		MinItems:    1,
+		ForceNew:    true,
+		Description: "Definitions of a column to create in the external table. Minimum one required.",
+		Elem: &schema.Resource{
+			Schema: map[string]*schema.Schema{
+				"name": {
+					Type:        schema.TypeString,
+					Required:    true,
+					Description: "Column name",
+					ForceNew:    true,
+				},
+				"type": {
+					Type:        schema.TypeString,
+					Required:    true,
+					Description: "Column type, e.g. VARIANT",
+					ForceNew:    true,
+				},
+				"as": {
+					Type:        schema.TypeString,
+					Required:    true,
+					Description: "String that specifies the expression for the column. When queried, the column returns results derived from this expression.",
+					ForceNew:    true,
+				},
+			},
+		},
+	},
+	"location": {
+		Type:        schema.TypeString,
+		Required:    true,
+		ForceNew:    true,
+		Description: "Specifies a location for the external table.",
+	},
+	"file_format": {
+		Type:        schema.TypeString,
+		Required:    true,
+		ForceNew:    true,
+		Description: "Specifies the file format for the external table.",
+	},
+
+	"aws_sns_topic": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		ForceNew:    true,
+		Description: "Specifies the aws sns topic for the external table.",
+	},
+	"partition_by": {
+		Type:        schema.TypeList,
+		Optional:    true,
+		Elem:        &schema.Schema{Type: schema.TypeString},
+		ForceNew:    true,
+		Description: "Specifies any partition columns to evaluate for the external table.",
+	},
+	"refresh_on_create": {
+		Type:        schema.TypeBool,
+		Optional:    true,
+		Description: "Specifies weather to refresh when an external table is created.",
+		Default:     true,
+		ForceNew:    true,
+	},
+	"auto_refresh": {
+		Type:        schema.TypeBool,
+		Optional:    true,
+		Description: "Specifies whether to automatically refresh the external table metadata once, immediately after the external table is created.",
+		Default:     true,
+		ForceNew:    true,
+	},
+	"copy_grants": {
+		Type:        schema.TypeBool,
+		Optional:    true,
+		Description: "Specifies to retain the access permissions from the original table when an external table is recreated using the CREATE OR REPLACE TABLE variant",
+		Default:     false,
+		ForceNew:    true,
+	},
+	"comment": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		ForceNew:    true,
+		Description: "Specifies a comment for the external table.",
+	},
+	"owner": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		ForceNew:    true,
+		Description: "Name of the role that owns the external table.",
+	},
+}
+
+func ExternalTable() *schema.Resource {
+	return &schema.Resource{
+		Create: CreateExternalTable,
+		Read:   ReadExternalTable,
+		Delete: DeleteExternalTable,
+
+		Schema: externalTableSchema,
+		Importer: &schema.ResourceImporter{
+			State: schema.ImportStatePassthrough,
+		},
+	}
+}
+
+type externalTableID struct {
+	DatabaseName      string
+	SchemaName        string
+	ExternalTableName string
+}
+
+//String() takes in a externalTableID object and returns a pipe-delimited string:
+//DatabaseName|SchemaName|ExternalTableName
+func (si *externalTableID) String() (string, error) {
+	var buf bytes.Buffer
+	csvWriter := csv.NewWriter(&buf)
+	csvWriter.Comma = externalTableIDDelimiter
+	dataIdentifiers := [][]string{{si.DatabaseName, si.SchemaName, si.ExternalTableName}}
+	err := csvWriter.WriteAll(dataIdentifiers)
+	if err != nil {
+		return "", err
+	}
+	strExternalTableID := strings.TrimSpace(buf.String())
+	return strExternalTableID, nil
+}
+
+// externalTableIDFromString() takes in a pipe-delimited string: DatabaseName|SchemaName|ExternalTableName
+// and returns a externalTableID object
+func externalTableIDFromString(stringID string) (*externalTableID, error) {
+	reader := csv.NewReader(strings.NewReader(stringID))
+	reader.Comma = externalTableIDDelimiter
+	lines, err := reader.ReadAll()
+	if err != nil {
+		return nil, fmt.Errorf("Not CSV compatible")
+	}
+
+	if len(lines) != 1 {
+		return nil, fmt.Errorf("1 line at a time")
+	}
+	if len(lines[0]) != 3 {
+		return nil, fmt.Errorf("3 fields allowed")
+	}
+
+	externalTableResult := &externalTableID{
+		DatabaseName:      lines[0][0],
+		SchemaName:        lines[0][1],
+		ExternalTableName: lines[0][2],
+	}
+	return externalTableResult, nil
+}
+
+// CreateExternalTable implements schema.CreateFunc
+func CreateExternalTable(data *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	database := data.Get("database").(string)
+	dbSchema := data.Get("schema").(string)
+	name := data.Get("name").(string)
+
+	// This type conversion is due to the test framework in the terraform-plugin-sdk having limited support
+	// for data types in the HCL2ValueFromConfigValue method.
+	columns := []map[string]string{}
+	for _, column := range data.Get("column").([]interface{}) {
+		columnDef := map[string]string{}
+		for key, val := range column.(map[string]interface{}) {
+			columnDef[key] = val.(string)
+		}
+		columns = append(columns, columnDef)
+	}
+	builder := snowflake.ExternalTable(name, database, dbSchema)
+	builder.WithColumns(columns)
+	builder.WithFileFormat(data.Get("file_format").(string))
+	builder.WithLocation(data.Get("location").(string))
+
+	builder.WithAutoRefresh(data.Get("auto_refresh").(bool))
+	builder.WithRefreshOnCreate(data.Get("refresh_on_create").(bool))
+	builder.WithCopyGrants(data.Get("copy_grants").(bool))
+
+	// Set optionals
+	if v, ok := data.GetOk("partition_by"); ok {
+		partiionBys := expandStringList(v.(*schema.Set).List())
+		builder.WithPartitionBys(partiionBys)
+	}
+
+	if v, ok := data.GetOk("pattern"); ok {
+		builder.WithPattern(v.(string))
+	}
+
+	if v, ok := data.GetOk("aws_sns_topic"); ok {
+		builder.WithAwsSNSTopic(v.(string))
+	}
+
+	if v, ok := data.GetOk("comment"); ok {
+		builder.WithComment(v.(string))
+	}
+
+	stmt := builder.Create()
+	err := snowflake.Exec(db, stmt)
+	if err != nil {
+		return errors.Wrapf(err, "error creating externalTable %v", name)
+	}
+
+	externalTableID := &externalTableID{
+		DatabaseName:      database,
+		SchemaName:        dbSchema,
+		ExternalTableName: name,
+	}
+	dataIDInput, err := externalTableID.String()
+	if err != nil {
+		return err
+	}
+	data.SetId(dataIDInput)
+
+	return ReadExternalTable(data, meta)
+}
+
+// ReadExternalTable implements schema.ReadFunc
+func ReadExternalTable(data *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	externalTableID, err := externalTableIDFromString(data.Id())
+	if err != nil {
+		return err
+	}
+
+	dbName := externalTableID.DatabaseName
+	schema := externalTableID.SchemaName
+	name := externalTableID.ExternalTableName
+
+	stmt := snowflake.ExternalTable(name, dbName, schema).Show()
+	row := snowflake.QueryRow(db, stmt)
+	externalTable, err := snowflake.ScanExternalTable(row)
+	if err != nil {
+		return err
+	}
+
+	err = data.Set("name", externalTable.ExternalTableName.String)
+	if err != nil {
+		return err
+	}
+
+	err = data.Set("owner", externalTable.Owner.String)
+	if err != nil {
+		return err
+	}
+
+	return nil
+}
+
+// DeleteExternalTable implements schema.DeleteFunc
+func DeleteExternalTable(data *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	externalTableID, err := externalTableIDFromString(data.Id())
+	if err != nil {
+		return err
+	}
+
+	dbName := externalTableID.DatabaseName
+	schema := externalTableID.SchemaName
+	externalTableName := externalTableID.ExternalTableName
+
+	q := snowflake.ExternalTable(externalTableName, dbName, schema).Drop()
+
+	err = snowflake.Exec(db, q)
+	if err != nil {
+		return errors.Wrapf(err, "error deleting pipe %v", data.Id())
+	}
+
+	data.SetId("")
+
+	return nil
+}
+
+// ExternalTableExists implements schema.ExistsFunc
+func ExternalTableExists(data *schema.ResourceData, meta interface{}) (bool, error) {
+	db := meta.(*sql.DB)
+	externalTableID, err := externalTableIDFromString(data.Id())
+	if err != nil {
+		return false, err
+	}
+
+	dbName := externalTableID.DatabaseName
+	schema := externalTableID.SchemaName
+	externalTableName := externalTableID.ExternalTableName
+
+	q := snowflake.ExternalTable(externalTableName, dbName, schema).Show()
+	rows, err := db.Query(q)
+	if err != nil {
+		return false, err
+	}
+	defer rows.Close()
+
+	if rows.Next() {
+		return true, nil
+	}
+
+	return false, nil
+}
diff --git a/pkg/snowflake/external_table.go b/pkg/snowflake/external_table.go
new file mode 100644
index 00000000..b82d0b95
--- /dev/null
+++ b/pkg/snowflake/external_table.go
@@ -0,0 +1,174 @@
+package snowflake
+
+import (
+	"database/sql"
+	"fmt"
+	"strings"
+
+	"github.com/jmoiron/sqlx"
+)
+
+// externalTableBuilder abstracts the creation of SQL queries for a Snowflake schema
+type ExternalTableBuilder struct {
+	name            string
+	db              string
+	schema          string
+	columns         []map[string]string
+	partitionBys    []string
+	location        string
+	refreshOnCreate bool
+	autoRefresh     bool
+	pattern         string
+	fileFormat      string
+	copyGrants      bool
+	awsSNSTopic     string
+	comment         string
+}
+
+// QualifiedName prepends the db and schema if set and escapes everything nicely
+func (tb *ExternalTableBuilder) QualifiedName() string {
+	var n strings.Builder
+
+	if tb.db != "" && tb.schema != "" {
+		n.WriteString(fmt.Sprintf(`"%v"."%v".`, tb.db, tb.schema))
+	}
+
+	if tb.db != "" && tb.schema == "" {
+		n.WriteString(fmt.Sprintf(`"%v"..`, tb.db))
+	}
+
+	if tb.db == "" && tb.schema != "" {
+		n.WriteString(fmt.Sprintf(`"%v".`, tb.schema))
+	}
+
+	n.WriteString(fmt.Sprintf(`"%v"`, tb.name))
+
+	return n.String()
+}
+
+// WithComment adds a comment to the ExternalTableBuilder
+func (tb *ExternalTableBuilder) WithComment(c string) *ExternalTableBuilder {
+	tb.comment = c
+	return tb
+}
+
+// WithColumns sets the column definitions on the ExternalTableBuilder
+func (tb *ExternalTableBuilder) WithColumns(c []map[string]string) *ExternalTableBuilder {
+	tb.columns = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithPartitionBys(c []string) *ExternalTableBuilder {
+	tb.partitionBys = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithLocation(c string) *ExternalTableBuilder {
+	tb.location = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithRefreshOnCreate(c bool) *ExternalTableBuilder {
+	tb.refreshOnCreate = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithAutoRefresh(c bool) *ExternalTableBuilder {
+	tb.autoRefresh = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithPattern(c string) *ExternalTableBuilder {
+	tb.pattern = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithFileFormat(c string) *ExternalTableBuilder {
+	tb.fileFormat = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithCopyGrants(c bool) *ExternalTableBuilder {
+	tb.copyGrants = c
+	return tb
+}
+func (tb *ExternalTableBuilder) WithAwsSNSTopic(c string) *ExternalTableBuilder {
+	tb.awsSNSTopic = c
+	return tb
+}
+
+// ExternalexternalTable returns a pointer to a Builder that abstracts the DDL operations for a externalTable.
+//
+// Supported DDL operations are:
+//   - CREATE externalTable
+//
+// [Snowflake Reference](https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html)
+
+func ExternalTable(name, db, schema string) *ExternalTableBuilder {
+	return &ExternalTableBuilder{
+		name:   name,
+		db:     db,
+		schema: schema,
+	}
+}
+
+// Create returns the SQL statement required to create a externalTable
+func (tb *ExternalTableBuilder) Create() string {
+	q := strings.Builder{}
+	q.WriteString(fmt.Sprintf(`CREATE EXTERNAL TABLE %v`, tb.QualifiedName()))
+
+	q.WriteString(fmt.Sprintf(` (`))
+	columnDefinitions := []string{}
+	for _, columnDefinition := range tb.columns {
+		columnDefinitions = append(columnDefinitions, fmt.Sprintf(`"%v" %v AS %v`, EscapeString(columnDefinition["name"]), EscapeString(columnDefinition["type"]), EscapeString(columnDefinition["as"])))
+	}
+	q.WriteString(strings.Join(columnDefinitions, ", "))
+	q.WriteString(fmt.Sprintf(`)`))
+
+	if len(tb.partitionBys) > 1 {
+		q.WriteString(` PARTIION BY `)
+		q.WriteString(EscapeString(strings.Join(tb.partitionBys, ", ")))
+	}
+
+	q.WriteString(` WITH LOCATION = ` + EscapeString(tb.location))
+	q.WriteString(fmt.Sprintf(` REFRESH_ON_CREATE = %t`, tb.refreshOnCreate))
+	q.WriteString(fmt.Sprintf(` AUTO_REFRESH = %t`, tb.autoRefresh))
+
+	if tb.pattern != "" {
+		q.WriteString(fmt.Sprintf(` PATTERN = '%v'`, EscapeString(tb.pattern)))
+	}
+
+	q.WriteString(fmt.Sprintf(` FILE_FORMAT = ( %v )`, EscapeString(tb.fileFormat)))
+
+	if tb.awsSNSTopic != "" {
+		q.WriteString(fmt.Sprintf(` AWS_SNS_TOPIC = '%v'`, EscapeString(tb.awsSNSTopic)))
+	}
+
+	if tb.copyGrants {
+		q.WriteString(" COPY GRANTS")
+	}
+
+	if tb.comment != "" {
+		q.WriteString(fmt.Sprintf(` COMMENT = '%v'`, EscapeString(tb.comment)))
+	}
+
+	return q.String()
+}
+
+// Drop returns the SQL query that will drop a externalTable.
+func (tb *ExternalTableBuilder) Drop() string {
+	return fmt.Sprintf(`DROP EXTERNAL TABLE %v`, tb.QualifiedName())
+}
+
+// Show returns the SQL query that will show a externalTable.
+func (tb *ExternalTableBuilder) Show() string {
+	return fmt.Sprintf(`SHOW EXTERNAL TABLES LIKE '%v' IN SCHEMA "%v"."%v"`, tb.name, tb.db, tb.schema)
+}
+
+type externalTable struct {
+	CreatedOn         sql.NullString `db:"created_on"`
+	ExternalTableName sql.NullString `db:"name"`
+	DatabaseName      sql.NullString `db:"database_name"`
+	SchemaName        sql.NullString `db:"schema_name"`
+	Comment           sql.NullString `db:"comment"`
+	Owner             sql.NullString `db:"owner"`
+}
+
+func ScanExternalTable(row *sqlx.Row) (*externalTable, error) {
+	t := &externalTable{}
+	e := row.StructScan(t)
+	return t, e
+}


Test patch:
diff --git a/pkg/resources/external_table_acceptance_test.go b/pkg/resources/external_table_acceptance_test.go
new file mode 100644
index 00000000..058d8837
--- /dev/null
+++ b/pkg/resources/external_table_acceptance_test.go
@@ -0,0 +1,70 @@
+package resources_test
+
+import (
+	"fmt"
+	"testing"
+
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/acctest"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+)
+
+func TestAccExternalTable(t *testing.T) {
+	accName := acctest.RandStringFromCharSet(10, acctest.CharSetAlpha)
+
+	resource.Test(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: externalTableConfig(accName),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttr("snowflake_external_table.test_table", "name", accName),
+					resource.TestCheckResourceAttr("snowflake_external_table.test_table", "database", accName),
+					resource.TestCheckResourceAttr("snowflake_external_table.test_table", "schema", accName),
+					resource.TestCheckResourceAttr("snowflake_external_table.test_table", "comment", "Terraform acceptance test"),
+				),
+			},
+		},
+	})
+}
+
+func externalTableConfig(name string) string {
+	s := `
+resource "snowflake_database" "test" {
+	name = "%v"
+	comment = "Terraform acceptance test"
+}
+
+resource "snowflake_schema" "test" {
+	name = "%v"
+	database = snowflake_database.test.name
+	comment = "Terraform acceptance test"
+}
+
+resource "snowflake_stage" "test" {
+	name = "%v"
+	database = snowflake_database.test.name
+	schema = snowflake_schema.test.name
+	comment = "Terraform acceptance test"
+}
+
+resource "snowflake_external_table" "test_table" {
+	database = snowflake_database.test.name
+	schema   = snowflake_schema.test.name
+	name     = "%v"
+	comment  = "Terraform acceptance test"
+	column {
+		name = "column1"
+		type = "VARIANT"
+    as = "($1:\"CreatedDate\"::timestamp)"
+	}
+	column {
+		name = "column2"
+		type = "VARCHAR"
+    as = "($1:\"CreatedDate\"::timestamp)"
+	}
+  file_format = "TYPE = CSV"
+  location = "@${snowflake_database.test.name}.${snowflake_schema.test.name}.${snowflake_stage.test.name}"
+}
+`
+	return fmt.Sprintf(s, name, name, name, name)
+}
diff --git a/pkg/resources/external_table_internal_test.go b/pkg/resources/external_table_internal_test.go
new file mode 100644
index 00000000..5374abfe
--- /dev/null
+++ b/pkg/resources/external_table_internal_test.go
@@ -0,0 +1,72 @@
+package resources
+
+import (
+	"fmt"
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func ExternalTestTableIDFromString(t *testing.T) {
+	r := require.New(t)
+	// Vanilla
+	id := "database_name|schema_name|table"
+	table, err := externalTableIDFromString(id)
+	r.NoError(err)
+	r.Equal("database_name", table.DatabaseName)
+	r.Equal("schema_name", table.SchemaName)
+	r.Equal("table", table.ExternalTableName)
+
+	// Bad ID -- not enough fields
+	id = "database"
+	_, err = streamOnTableIDFromString(id)
+	r.Equal(fmt.Errorf("3 fields allowed"), err)
+
+	// Bad ID
+	id = "||"
+	_, err = streamOnTableIDFromString(id)
+	r.NoError(err)
+
+	// 0 lines
+	id = ""
+	_, err = streamOnTableIDFromString(id)
+	r.Equal(fmt.Errorf("1 line at a time"), err)
+
+	// 2 lines
+	id = `database_name|schema_name|table
+	database_name|schema_name|table`
+	_, err = streamOnTableIDFromString(id)
+	r.Equal(fmt.Errorf("1 line at a time"), err)
+}
+
+func ExternalTestTableStruct(t *testing.T) {
+	r := require.New(t)
+
+	// Vanilla
+	table := &externalTableID{
+		DatabaseName:      "database_name",
+		SchemaName:        "schema_name",
+		ExternalTableName: "table",
+	}
+	sID, err := table.String()
+	r.NoError(err)
+	r.Equal("database_name|schema_name|table", sID)
+
+	// Empty grant
+	table = &externalTableID{}
+	sID, err = table.String()
+	r.NoError(err)
+	r.Equal("||", sID)
+
+	// Grant with extra delimiters
+	table = &externalTableID{
+		DatabaseName:      "database|name",
+		ExternalTableName: "table|name",
+	}
+	sID, err = table.String()
+	r.NoError(err)
+	newTable, err := streamOnTableIDFromString(sID)
+	r.NoError(err)
+	r.Equal("database|name", newTable.DatabaseName)
+	r.Equal("table|name", newTable.OnTableName)
+}
diff --git a/pkg/resources/external_table_test.go b/pkg/resources/external_table_test.go
new file mode 100644
index 00000000..5836a7aa
--- /dev/null
+++ b/pkg/resources/external_table_test.go
@@ -0,0 +1,74 @@
+package resources_test
+
+import (
+	"database/sql"
+	"testing"
+
+	sqlmock "github.com/DATA-DOG/go-sqlmock"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources"
+	. "github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers"
+	"github.com/stretchr/testify/require"
+)
+
+func TestExternalTable(t *testing.T) {
+	r := require.New(t)
+	err := resources.ExternalTable().InternalValidate(provider.Provider().Schema, true)
+	r.NoError(err)
+}
+
+func TestExternalTableCreate(t *testing.T) {
+	r := require.New(t)
+
+	in := map[string]interface{}{
+		"name":        "good_name",
+		"database":    "database_name",
+		"schema":      "schema_name",
+		"comment":     "great comment",
+		"column":      []interface{}{map[string]interface{}{"name": "column1", "type": "OBJECT", "as": "a"}, map[string]interface{}{"name": "column2", "type": "VARCHAR", "as": "b"}},
+		"location":    "location",
+		"file_format": "format",
+	}
+	d := externalTable(t, "database_name|schema_name|good_name", in)
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`CREATE EXTERNAL TABLE "database_name"."schema_name"."good_name" \("column1" OBJECT AS a, "column2" VARCHAR AS b\) WITH LOCATION = location REFRESH_ON_CREATE = true AUTO_REFRESH = true FILE_FORMAT = \( format \) COMMENT = 'great comment'`).WillReturnResult(sqlmock.NewResult(1, 1))
+
+		expectExternalTableRead(mock)
+		err := resources.CreateExternalTable(d, db)
+		r.NoError(err)
+		r.Equal("good_name", d.Get("name").(string))
+	})
+}
+
+func expectExternalTableRead(mock sqlmock.Sqlmock) {
+	rows := sqlmock.NewRows([]string{"name", "type", "kind", "null?", "default", "primary key", "unique key", "check", "expression", "comment"}).AddRow("good_name", "VARCHAR()", "COLUMN", "Y", "NULL", "NULL", "N", "N", "NULL", "mock comment")
+	mock.ExpectQuery(`SHOW EXTERNAL TABLES LIKE 'good_name' IN SCHEMA "database_name"."schema_name"`).WillReturnRows(rows)
+}
+
+func TestExternalTableRead(t *testing.T) {
+	r := require.New(t)
+
+	d := externalTable(t, "database_name|schema_name|good_name", map[string]interface{}{"name": "good_name", "comment": "mock comment"})
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		expectExternalTableRead(mock)
+
+		err := resources.ReadExternalTable(d, db)
+		r.NoError(err)
+		r.Equal("good_name", d.Get("name").(string))
+		r.Equal("mock comment", d.Get("comment").(string))
+	})
+}
+
+func TestExternalTableDelete(t *testing.T) {
+	r := require.New(t)
+
+	d := externalTable(t, "database_name|schema_name|drop_it", map[string]interface{}{"name": "drop_it"})
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`DROP EXTERNAL TABLE "database_name"."schema_name"."drop_it"`).WillReturnResult(sqlmock.NewResult(1, 1))
+		err := resources.DeleteExternalTable(d, db)
+		r.NoError(err)
+	})
+}
diff --git a/pkg/resources/helpers_test.go b/pkg/resources/helpers_test.go
index b5668e5e..741e54be 100644
--- a/pkg/resources/helpers_test.go
+++ b/pkg/resources/helpers_test.go
@@ -192,6 +192,14 @@ func table(t *testing.T, id string, params map[string]interface{}) *schema.Resou
 	return d
 }
 
+func externalTable(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
+	r := require.New(t)
+	d := schema.TestResourceDataRaw(t, resources.ExternalTable().Schema, params)
+	r.NotNil(d)
+	d.SetId(id)
+	return d
+}
+
 func task(t *testing.T, id string, params map[string]interface{}) *schema.ResourceData {
 	r := require.New(t)
 	d := schema.TestResourceDataRaw(t, resources.Task().Schema, params)
diff --git a/pkg/snowflake/external_table_test.go b/pkg/snowflake/external_table_test.go
new file mode 100644
index 00000000..1821593f
--- /dev/null
+++ b/pkg/snowflake/external_table_test.go
@@ -0,0 +1,33 @@
+package snowflake
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestExternalTableCreate(t *testing.T) {
+	r := require.New(t)
+	s := ExternalTable("test_table", "test_db", "test_schema")
+	s.WithColumns([]map[string]string{{"name": "column1", "type": "OBJECT", "as": "expression1"}, {"name": "column2", "type": "VARCHAR", "as": "expression2"}})
+	s.WithLocation("location")
+	s.WithFileFormat("file format")
+	r.Equal(s.QualifiedName(), `"test_db"."test_schema"."test_table"`)
+
+	r.Equal(s.Create(), `CREATE EXTERNAL TABLE "test_db"."test_schema"."test_table" ("column1" OBJECT AS expression1, "column2" VARCHAR AS expression2) WITH LOCATION = location REFRESH_ON_CREATE = false AUTO_REFRESH = false FILE_FORMAT = ( file format )`)
+
+	s.WithComment("Test Comment")
+	r.Equal(s.Create(), `CREATE EXTERNAL TABLE "test_db"."test_schema"."test_table" ("column1" OBJECT AS expression1, "column2" VARCHAR AS expression2) WITH LOCATION = location REFRESH_ON_CREATE = false AUTO_REFRESH = false FILE_FORMAT = ( file format ) COMMENT = 'Test Comment'`)
+}
+
+func TestExternalTableDrop(t *testing.T) {
+	r := require.New(t)
+	s := ExternalTable("test_table", "test_db", "test_schema")
+	r.Equal(s.Drop(), `DROP EXTERNAL TABLE "test_db"."test_schema"."test_table"`)
+}
+
+func TestExternalTableShow(t *testing.T) {
+	r := require.New(t)
+	s := ExternalTable("test_table", "test_db", "test_schema")
+	r.Equal(s.Show(), `SHOW EXTERNAL TABLES LIKE 'test_table' IN SCHEMA "test_db"."test_schema"`)
+}