There will be no singularity

SNOWFLAKE SPECIAL BEHAVIOR: REUSING COLUMN ALIASES In any database, you can use aliases for both columns and expressions within queries:

SELECT
 id AS user_id,
 name AS user_name,
 age AS user_age,
 age * 2 AS user_age_doubled
FROM users;

But what can we do with these aliases? Database vendors allow different things. For example, in MySQL and PostgreSQL, aliases can only be used in GROUP BY and ORDER BY. In ClickHouse and Snowflake, however, aliases can be used everywhere. But, as usual, there are nuances 🙂 Let's create syntactically identical VIEWs:

CREATE TABLE abc AS
 SELECT 
 1 AS a,
 100 AS b,
 1000 AS c
;

CREATE VIEW v13 AS 
SELECT 
 a+1 AS b,
 b+1 AS c
FROM abc;

CREATE VIEW v14 AS 
SELECT 
 a+1 AS d,
 d+1 AS e
FROM abc;

At first glance, the lineage for these views should be the same But in v14 both columns will depend on ABC.A, and in v13 only column B. Why is this so? Because in Snowflake, when using an alias equivalent to the original column name from the source, the original column takes precedence! By the way, in ClickHouse, it works differently… What was meant by saying that aliases work everywhere? Aliases can be reused in JOIN and WHERE clauses:

CREATE TABLE t12 AS SELECT 1 AS a, 100 AS b;
CREATE TABLE t13 AS SELECT 1 AS c, 100 AS d;

CREATE VIEW v15 AS
SELECT 
 a + b AS e,
 c + d AS f
FROM 
 t12 
 JOIN t13 ON e = f;

or even like this:

CREATE VIEW v16 AS
SELECT 
 a1 + b1 AS e,
 c1 + d1 AS f,
 ROUND(e, f) AS g,
 ROUND(f, e) AS h 
FROM 
 t12 AS t121(a1, b1) 
 JOIN t13 AS t131(c1, d1) ON e = f
WHERE 
 g = h;

Since in dwh.dev we display not only the data flows but also the columns used in JOIN and WHERE, you will also see the original column sources in the corresponding data lineage section. PS: Thumbs Up Here

There is a new item (from the subscriber) in my collection of non-DB SQL tools at github: https://explainextended.com/2023/12/31/happy-new-year-15/ GPT in 500 lines of SQL

SQL-WTF SQL-TIL It would seem that what can happen with escaping characters in strings? Everything has been known for a long time and works the same everywhere... But what happens if you escape characters that don't need to be escaped?

CREATE TABLE vals(s VARCHAR(5));
INSERT INTO vals(s) VALUES('%');
INSERT INTO vals(s) VALUES('\%');
INSERT INTO vals(s) VALUES('\\%');

SELECT
    s,
    s LIKE '%' as "%",
    s LIKE '\%' as "\%",
    s LIKE '\\%' as "\\%"
FROM vals;

Now let's run in different databases. Note the values of S. Snowflake:

| S  | %    | \%   | \\%   |
|----|------|------|-------|
| %  | TRUE | TRUE | FALSE |
| %  | TRUE | TRUE | FALSE |
| \% | TRUE | TRUE | TRUE  |

Sqlite:

| s   | % | \% | \\% |
|-----|---|----|-----|
| %   | 1 | 0  | 0   |
| \%  | 1 | 1  | 0   |
| \\% | 1 | 1  | 1   |

Mysql/MariaDB:

| s  | % | \% | \% |
|----|---|----|----|
| %  | 1 | 1  | 1  |
| \% | 1 | 0  | 0  |
| \% | 1 | 0  | 0  |

PostgreSQL:

| s   | %    | \%    | \\%   |
|-----|------|-------|-------|
| %   | true | true  | false |
| \%  | true | false | true  |
| \\% | true | false | true  |

Oracle and SQL Server doesn't support this syntax. So, well... Good thing at least '%' works the same everywhere.....

inding out how comprehensive and accurate your data observability is in reality, upon which you rely? We're launching a new initiative - the Data Lineage Challenge. This is a set of examples specific to various databases that will help you assess the quality of your data lineage tool: https://github.com/dwh-dev/data-lineage-challenge As dwh.dev specializes in Snowflake, the first set of examples will be dedicated to it. We'll be updating this repository with examples for Snowflake as well as other databases. Subscribe not to miss out! Feel free to send examples of the "special behavior" of the databases you work with, and we'll add them to this repository. Additionally, we'd love to see the results of the data lineage tools you're currently using. Hopefully, this will assist their developers in making these tools even better 🙂 PS: Also, dwh.dev provides an API for SQL parsing and compilation. Contact us to get demo access. We collaborate with different data companies (even our competitors). PPS: support us on linkedin

k8s in SQL by Snowflake: https://docs.snowflake.com/en/developer-guide/snowpark-container-services/overview

https://jepsen.io/analyses/mysql-8.0.34 Reason for not using MySQL #1234: MySQL’s “Repeatable Read” does not satisfy PL-2.99 Repeatable Read

New week, new "special behavior" in Snowflake 🙂 In Snowflake, there is a special function IDENTIFIER() that allows referencing objects using strings and session variables:

SELECT
  * 
FROM
  IDENTIFIER($my_table)

Documentation can be found here: https://docs.snowflake.com/en/sql-reference/identifier-literal Additionally, you can reference an object in the column list:

SELECT 
  IDENTIFIER($my_table).* 
FROM 
  IDENTIFIER($my_table)

Wait, that's not the "special behavior" yet... 1)

IDENTIFIER($my_table).*

works, but

IDENTIFIER($my_table).column_name 

does not This looks very strange... But until you try this: 2)

SELECT 
  IDENTIFIER($my_table1).*, 
  IDENTIFIER($my_table2).* 
FROM 
  IDENTIFIER($my_table1),
  IDENTIFIER($my_table2)

... and you'll get twice of all the columns 🙂 Because IDENTIFIER(whats_ever).* gets replaced by just *! As a result, the above query is converted to

SELECT 
  *,
  * 
FROM 
  IDENTIFIER($my_table1),
  IDENTIFIER($my_table2);

PS: send your reactions here 🙂

Don't be alarmed. It's time to rewrite the name of the channel in English 🙂

A bit of explanation in my own words: 1) Venn diagrams describe interactions between sets. However, when we perform a JOIN, we don't get a single set but a set of sets (a table). Therefore, Venn diagrams aren't suitable for describing what actually happens after a JOIN. 2) In such illustrations (even if supplemented with tables), usually only one, almost the rarest case is depicted: unique values in both columns and an equality condition for the join. There are no repetitions, no NULLs, and no complex join conditions. Previously, I created a static analyzer for PostgreSQL - holistic.dev. Besides type inference, I wanted to infer two more important parameters: constraints and the row count class. If we have information about unique indexes and foreign keys, we can infer the uniqueness of values, NULLability, and the row count class. What are these classes? NONE, ONE, ONE_OR_NONE, MANY, MANY_OR_NONE. Why are they needed? For instance, automatic asserts in code can be created. These exist in the Node.js driver. Or if you store your SQL in separate files, automatic checks can be made to ensure nothing broke during query or schema updates and to detect Row Explosion. So, outputting these classes and constraints were the most complex parts of the entire compiler :) All because of JOIN. Maybe that's why my eye still twitches when I see such explainers... People might look at this and then receive bills for 50 million... Conclusion: Venn diagrams are suitable for explaining UNION/INTERSECT/EXCEPT. For JOIN, this picture by Andreas Martinson is more suitable.

Days without explanation JOINs via Venn diagrams by data influencers: 0 https://blog.jooq.org/say-no-to-venn-diagrams-when-explaining-joins/

Lads, can you please like the post of who is using Snowflake? Or other analytical databases. Maybe you remember, I used to make a service for static analysis of PostgreSQL (holistic.dev) and posted stuff about PG. For quite some time now, I've been doing an observability platform for Snowflake (dwh.dev) with my team, and I'm fully immersed in this area. And would love to understand how interested you guys are in this at all. Is it worth sharing details about creating such software? Or is it necessary to bring back SQL-WTF about PostgreSQL? In short, tell us what you would like to see. PS: Yes, by the way, we have Snowflake chat rooms (RU/EN) and a channel with Snowflake news.