External Tables

Question 1

What are external tables? What is the differences against regular tables?

Answer 1

External tables - data actually lies outside of the SF in a cloud. Data is not COPY INTO.
Using External tables, SF provides a way that you can build DDL (table) on top of data present in cloud s3/azure/gcp

Question 2

Is external tables better performant than regular tables?

Answer 2

No - As data is outside of SF, it is not better performance compared to regular tables. However you can build materialized views on top of external tables for performance.

Question 3

Can you write to external tables? Can you use external tables in joins etc.

Answer 3

External tables are read only. But you can add or remove columns though.
Yes external tables can be used in joins/filters etc.

Question 4

What are the default columns in external tables?

Answer 4

VALUE : Variant data type column which represents 1 row in the external file
METADATA$FILENAME: it provides file name and path

Question 5

What is virtual columns in external tables?

Answer 5

Using the VALUE column, you can parse the row and create multiple virtual columns in external tbales.

select

value: c1::int as ID,
value: c2::varchar as name ,
value: c3::int as dept from sample_ext;

create or replace external table sample_ext 
(ID INT as  (value:c1::int), 
Name varchar(20) as ( value:c2::varchar), 
dept int as (value:c3::int))
with location = @mys3stage
file_format = mys3csv;

Question 6

What is partitioning external tables?

Answer 6

It is arranging and organizing your external files using logical paths in s3. Ex: date/time/country etc.

Question 7

How can you refresh metadata of external tables?

Answer 7

Using SQS service in s3. And these event notifications charge extra credits in s3.

Question 8

By default does it error out when there is an error in scanning one of the underlying files?

Answer 8

No, by default it will skip and go to next file. It will end up in partial scanning.

Question 9

How long does the results persist in external tables?

Answer 9

Similar to regular one, results persist for 24 hours unless some DML operations or auto ingest from SQS happens.

Question 10

What tables/views in information schema provide information on external tables?

Answer 10

External_tables view
External_tables_files (table)
and couple others.

Question 11

What SF object type is used to connect between SF and AWS?

Answer 11

Integration object which contains secret keys, credentials, URL so we need not mention them explicitly in our code.

Question 12

What is search optimization services? (SOS)

Answer 12

For point look up queries- for specific filters and those columns are also non clustered.

It is a service with which speed of the query can be increased 25 times.
Improves the performance of point look up queries.

This is like alternative to speed up queries like larger warehouses/materialized views/clustering.

For queries that use non clustered columns

Question 13

What are the benefits of search optimization services? (SOS)?

Answer 13

1. Cost effective ( no need to increase the WH size just for few queries)
2. Fast running queries for users

Question 14

How do you add search optimization services? (SOS) to existing table?

Answer 14

ALTER TABLE add search optimization

Question 15

What metadata columns you will see when SOS is enabled?

Answer 15

When doing SHOW TABLES - will see 3 additional columns

search optimization (flag)
search_optimization_progress (it takes some time to do SOS - so it says whether the set up is complete or not)
search_optimization_bytes (size)

Question 16

How does the query profile look when SOS is enabled?

Answer 16

you will see a box for SOS and can see pruning where partitions scanned is much lesser than partitions total

Question 17

Does SOS service impact storage and computing costs?

Answer 17

Yes.

storage - SF creates service access path (SAP) data structures which require space and these are used when queries run on SOS tables. Usually 25% of original table size.

compute - Adding SOS itself takes some computing needed and when table changes a lot, computing is needed for maintenance.

Question 18

Can you remove SOS once set up?

Answer 18

Yes using alter table command

ALTER TABLE DROP SEARCH OPTIMIZATION

Question 19

What kind of functions uses SOS and which one does not?

Answer 19

ex: all colum_name=values should use SOS
but if you are casting cast(column to number)=2 - this will not use SOS.

But if you put it along with any other valid SOS column then it is ok.

select id, c1, c2, c3
    from test_table
    where c1 = 1
       and
          c3 = to_date('2004-03-09')
    order by id;

Question 20

Does delete/update/merge use SOS?

Answer 20

yes they do

ex: delete from table where id=5