A company is using Snowflake in Azure in the Netherlands. The company analyst team also has data in JSON format that is stored in an Amazon S3 bucket…

Option A is the best design to meet the requirements because it uses a materialized view on top of

an external table against the S3 bucket in AWS Singapore. A materialized view is a database object

that contains the results of a query and can be refreshed periodically to reflect changes in the

underlying data1. An external table is a table that

data files stored in a cloud storage

service, such as Amazon S32. By using a materialized view on top of an external table, the company

can provide access to frequently changing data, keep egress costs to a minimum, and maintain low

latency. This is because the materialized view will cache the query results in Snowflake, reducing the

need to access the external data files and incur network charges. The materialized view will also

improve the query performance by avoiding scanning the external data files every time. The

materialized view can be refreshed on a schedule or on demand to capture the changes in the

external data files1.

Option B is not the best design because it uses an external table against the S3 bucket in AWS

Singapore and copies the data into transient tables. A transient table is a table that is not subject to

the Time Travel and Fail-safe features of Snowflake, and is automatically purged after a period of

time3. By using an external table and copying the data into transient tables, the company will incur

more egress costs and operational overhead than using a materialized view. This is because the

external table will access the external data files every time a query is executed, and the copy

operation will also transfer data from S3 to Snowflake. The transient tables will also consume more

storage space in Snowflake and require manual maintenance to ensure they are up to date.

Option C is not the best design because it copies the data between providers from S3 to Azure Blob

storage to collocate, then uses Snowpipe for data ingestion. Snowpipe is a service that automates

the loading of data from external sources into Snowflake tables4. By copying the data between

providers, the company will incur high egress costs and latency, as well as operational complexity

and maintenance of the infrastructure. Snowpipe will also add another layer of processing and

storage in Snowflake, which may not be necessary if the external data files are already in a queryable

format.

Option D is not the best design because it uses AWS Transfer Family to replicate data between the S3

bucket in AWS Singapore and an Azure Netherlands Blob storage, then uses an external table against

the Blob storage. AWS Transfer Family is a service that enables secure and seamless transfer of files

over SFTP, FTPS, and FTP to and from Amazon S3 or Amazon EFS5. By using AWS Transfer Family, the

company will incur high egress costs and latency, as well as operational complexity and maintenance

of the infrastructure. The external table will also access the external data files every time a query is

executed, which may affect the query performance.

Reference: 1: Materialized Views 2: External Tables 3: Transient Tables 4: Snowpipe Overview 5: AWS

Transfer Family