Snowflake Cross Database Access, Syntax and Examples

The cross database access does allows you to access the objects such as tables, view, synonyms, functions that are available on the same cloud warehouse server. In this article, we will check Snowflake cross database access and it’s restrictions.

Snowflake Cross Database Access

Using cross database access method, you can refer the object present in another database. For example, you can refer function present in another database and use result in the current database.

select demo_db..isnumeric(to_variant('hello')) is_numeric;
+------------+
| IS_NUMERIC |
|------------|
| False      |
+------------+

You can use cross database object in;

INSERT Statements
UPDATE Statements
DELETE statements
CREATE TABLE Statements
TRUNCATE TABLE Statements

Referencing Database Object from other Database

To access objects in other databases, you must use the three-level naming, which consists of the following components:

Database: Name of database to which you are connecting
Schema: The schema name where the object is present
Object: The name of the object, table, view, synonym or function.

You specify objects in the FROM clause of SQL statements in the three-level form. You can use the standard form or the shorthand notation.

Fully Qualified Naming Convention for Cross Database Access

Following syntax is the fully qualified naming conversion to access object present in another database.

database.schema.object-name

For example,

Consider following fully qualified naming convention to access function.

select demo_db.public.isnumeric(to_variant(1.5)) is_numeric;
+------------+
| IS_NUMERIC |
|------------|
| True       |
+------------+

Another method to write fully qualified path is to write only database and object name.

Following is the syntax.

database..object-name

For example,

select demo_db..isnumeric(to_variant('hello')) is_numeric;
+------------+
| IS_NUMERIC |
|------------|
| False      |
+------------+

Snowflake Cross Database Access Examples

Following are some common cross database access examples.

Access Table Present in another Database

Display the content of the table present in another database.

For example,

>select * from demo_db.public.S_STUDENTS;
+----+------+-----------+
| ID | NAME | CITY      |
|----+------+-----------|
|  1 | AAA  | London    |
|  3 | CCC  | Bangalore |
|  4 | DDD  | Mumbai    |
|  5 | EEE  | Bangalore |
+----+------+-----------+

Snowflake CTAS using Table Present in another Database

Create table using CTAS using table present in another database.

For example,

create table s_student as select * from demo_db.public.S_STUDENTS;
+---------------------------------------+
| status                                |
|---------------------------------------|
| Table S_STUDENT successfully created. |
+---------------------------------------+

Join Table Present in Another Database using Cross Database Access

You can join the table present in another database.

For example,

select S1.* from S_STUDENT s1 join demo_db.public.S_STUDENTS s2 on s1.ID = s2.id ;
+----+------+-----------+
| ID | NAME | CITY      |
|----+------+-----------|
|  1 | AAA  | London    |
|  3 | CCC  | Bangalore |
|  4 | DDD  | Mumbai    |
|  5 | EEE  | Bangalore |
+----+------+-----------+

Snowflake Cross Database Access Features

Following are some of the features:

You can CREATE, DROP, ALTER objects in another database
DELETE and TRUNCATE command is supported on objects present in another database.