There will be no singularity

Friends from Luna Park are looking for a Data Engineer — could be you or someone you know (could be DS, Python dev or ML engineer if you ask me)

Palabra.ai — the first ever real-time voice interpreter: starting as a tiny team of five engineers, they built a speech-to-text solution that works 50 times faster than OpenAI's Whisper!
The interpreter prototype is releasing in a month and will work in Zoom with a two-second delay — right now it’s one of a kind!

They are now looking for a senior engineer, who will write and optimize complex multi-node data pipelines, work on large-scale data scraping and manage datasets that include hundreds of thousands of hours of audio data.

Key requirements:
🟣5+ years of industry experience as a software developer/data engineer;
🟡Python skills (including modern backend frameworks, low-level asyncio, multithreading/multiprocessing);
🔵A good grasp on neural networks and related tools (NumPy, PyTorch, audio-related libraries such as torchaudio, librosa, etc);
🟢Experience in deploying, orchestrating, and scaling multi-node pipelines in the cloud.

Nice-to-haves:
🔘Compilated languages such as Go, C/C++, and Rust;
🔘CUDA or other GPU-related frameworks experience;
🔘Audio processing-related experience.

Salary is $70k-100k, plus equity up to 1%. It’s a fully remote position.

To apply or learn more, reach out to my Luna Park buddy Fedya @owlkov

Snowflake's hidden gem - CTE macros

We all like to use CTEs (Common Table Expression).
It makes our code cleaner and sometimes help to speed up queries.

But somehow, Snowflake documentation hides from us one beautiful behavior of CTE that will make your life even more convenient.

What does the documentation say?

A CTE (common table expression) is a named subquery defined in a WITH clause. You can think of the CTE as a temporary view for use in the statement that defines the CTE

In other words:
- we can only use SELECT in the CTE
- the result of the CTE is a view-like object

Surely many of you have used the CTE to define some constants that are used further in the query. For example:

WITH
  var_cte AS (
    SELECT 'Snowflake' AS vendor
  )
SELECT *
FROM t
WHERE
  vendor = (SELECT vendor FROM var_cte)

Tolerable, but not perfect. Especially scalar subqueries. Scalar subqueries are bad practice. Avoid using them!

Can this be done in a more elegant way? Yes! Look at this:

WITH
  var_cte AS ('Snowflake')
SELECT *
FROM t
WHERE
  vendor = var_cte;

Wow! The code became easier to read, and we got rid of scalar subqueries at the same time!

What if we want to use several values at once? We can make an object:

WITH
  var_cte AS ({'vendor': 'Snowflake'})
SELECT * FROM t WHERE
  vendor = var_cte:vendor;

Or here's the IN analogue:

WITH
  var_cte AS (['Snowflake', 'Bigquery'])
SELECT * FROM t WHERE
  ARRAY_CONTAINS(vendor::VARIANT, var_cte)

Although it's not so beautiful anymore…
It turns out that CTE can be not only a view-like object, but also a scalar value!

Very cool, but even this is not a final:

CREATE TABLE t AS SELECT 1 AS a, 2 AS b;

WITH
  var_cte AS (a+b)
SELECT
  var_cte
FROM t;

As a result, we will get a table with a var_cte column and a value of 3.

I.e. CTE is not only a view-like object, and not only a scalar value, but also an alias to any expression!

Here's another example:

WITH
  var_cte AS (SUM(a))
SELECT
  var_cte
FROM t;

Yes, you can use any function calls there, including aggregate function calls.

And even that works too:

WITH
  var_cte1 AS (a),
  var_cte2 AS (var_cte1+b)
SELECT
  var_cte2
FROM t;

And like a function's argument:

WITH
  var_cte AS (a + b)
SELECT
  ROUND(var_cte/2, 0)
FROM t;

Are there any downsides? Unfortunately, yes…
First of all, CTE macros refuse to work when you use them in UNION and inline FROM queries:

-- doesn't work!

WITH
  var_cte AS (a+b)
SELECT var_cte FROM t1
UNION
SELECT var_cte FROM t2
;

-- and here

WITH
  var_cte AS (a)
SELECT * FROM (
  SELECT var_cte FROM t
);

Maybe Snowflake engineers will finish this functionality and CTE macros will become possible to use everywhere.

And second, none of the data lineage tools will tell you that.

But the good news is that in dwh.dev we take CTE macros into account at compile time and display all relevant connections in lineage!

PS: I found out about it quite by accident from the last example in the documentation of the ENCRYPT_RAW function

PPS: thumbs up on linkedin

You are here:
TypeScript + SQL-based OPERATING SYSTEM by Mike Stonebraker

https://www.dbos.dev/blog/announcing-dbos

Here we go again...

https://github.com/electric-sql/pglite

We at dwh.dev decided to make some vertical videos (how do you do, fellow kids?).
What do you think?
Like it on YouTube, please.

SQL-WTF TIL (Snowflake edition)

WHAT FIELDS WILL I SEE AS A RESULT OF A SELECT QUERY THAT RETRIEVES MULTIPLE FIELDS WITH IDENTICAL NAMES?

Hold on before answering; it's not all that obvious 🙂

Let me explain what the answer depends on:

The type of client from which the query is made.
- Whether we are using USING or not.
- Whether there is data in the sources or not.
- Whether we are looking directly at the result of the executed query or at the result from the history using RESULT_SCAN.

Cool, right? Now, I'll briefly explain each point 🙂

Client Type
Database client creators love adding spices to the work process of data engineers 🙂 All examples discussed were executed in Snowsight (in SnowSQL, the result will be different). To be fair, it's not always clear which part of the described chaos is attributed to the client and which to the database itself. But we won't delve into that now…

The creators decided that when more than one column with the same name is encountered in the result, it would be a good idea to rename them! A, A1, A2… The idea is great, but how do I now understand if these are actually existing columns or not?

In reality, such columns do not exist. You cannot refer to them in SQL.

USING
USING is syntactic sugar to shorten the ON clause. But it affects not only ON, which might seem logical, but also the list of columns.

CREATE TABLE T1 (A INT, B INT);
SELECT
  * 
FROM T1 
  LEFT JOIN T1 as T1_1 USING(A);

It is implied that the result should be identical to:

SELECT
  * 
FROM T1
  LEFT JOIN T1 as T1_1 ON T1.A = T1_1.A

and we should get A, B, A, B (or the renamed version described above).

But no! USING removes one column A. Really, why do you need 2 identical columns that will break everything for you later? So, it should be A, B, B.

Everything is fine here. But what if we continue to use JOIN with USING?

SELECT
  * 
FROM T1
  LEFT JOIN T1 as T1_1 USING(A)
  LEFT JOIN T1 as T1_2 USING(A)
  ...

Do you think that each time another column B will be added? Nope! 🙂 All subsequent JOIN USING with identically named columns will simply be ignored! Moreover, this will happen until a JOIN with ON is encountered!

SELECT
  * 
FROM T1
  LEFT JOIN T1 as T1_1 USING(A)
  LEFT JOIN T1 as T1_2 USING(A)
  LEFT JOIN T1 as T1_3 ON T1.A = T1_3.A

will return A B B2 A2 B_3

Empty and Non-Empty Tables
Yes, Snowflake (Snowsight) renames columns differently depending on whether there is data in the tables or not!

-- EMPTY TABLES
SELECT * FROM t1 CROSS JOIN t1 a;
-- A B A_2 B_2

-- TABLE T1 IS NOT EMPTY
SELECT * FROM t1 CROSS JOIN t1 a;
-- A B A B

RESULT_SCAN
This function returns the result of a query by its UUID in QUERY_HISTORY:

SELECT * FROM t1 CROSS JOIN t1 a;
-- A B A_2 B_2

SELECT * FROM  TABLE(RESULT_SCAN(LAST_QUERY_ID()));
-- A B A_1 B_1

But now, A1 and B1 are actual columns!

SELECT A_1 FROM 
(
  SELECT * FROM  TABLE(RESULT_SCAN(LAST_QUERY_ID()))
);
-- OK

More examples are here: Github
Thumbs up here: Linkedin

Forgot to post the year in review :)

SNOWFLAKE: SELECT * ILIKE EXCLUDE REPLACE RENAME

Database vendors are competing to see who can come up with the most features for SELECT *.
Snowflake is not lagging behind and supports as many as 4 modifiers for SELECT *:
ILIKE EXCLUDE REPLACE RENAME

We all know that SELECT * is bad. Now it's 4 times worse :)
Ok, 3 times. You can't use them all together (either ILIKE or EXCLUDE).

But the most disgusting modifier is REPLACE. It allows you to replace one column with any expression. Good luck debugging, dudes :)

What happens to lineage when using these modifiers?
A few examples are collected here

Take a look at one of them:

CREATE TABLE t19(
 id INT,
 c1 BOOLEAN, 
 c2 BOOLEAN,
 c3 BOOLEAN,
 c4 BOOLEAN,
 c12c BOOLEAN
);
CREATE TABLE t20(
 c5 BOOLEAN, 
 c6 BOOLEAN
);

CREATE VIEW v20 AS
 SELECT
 * 
 ILIKE 'c%'
 REPLACE (a.c1 OR b.$2 AS c1)
 RENAME c1 AS c0
 FROM t19 a, t20 b
;

The result of dwh.dev is on the screenshot.

Thumbs up here!
PS: Just a reminder about our data lineage challenge

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
PPS: https://github.com/dwh-dev/data-lineage-challenge