Data Craze Weekly #3

This message was sent first to subscribers of Data Craze Weekly newsletter.

_## Week in Data

Today I have prepared for you one interesting fact and a handful of information from the world of data.

Lateral JOIN

I’ll be honest with you, I don’t know how it happened that I didn’t know this connection. However, I know that I now have a new course video to record and PDF updates about joins.

You learn all your life, and that’s how I recently came across join lateral syntax.

It turns out that this is an element of the ISO syntax available, among others, in Oracle, PostgreSQL, MySQL or SQL Server (using APPLY).

What’s so great about it? Well, it allows us to actually make a loop within a loop. If we assume that they are making FROM (some table), we loop through the rows in a given table, then lateral allows us to make another loop by referring to elements from the parent object (it can be a table, function or view).

Taking a post from the documentation:

LATERAL is primarily useful when the cross-referenced column is necessary for computing the row(s) to be joined.

A detailed explanation with an example in the link.

Link: https://www.cybertec-postgresql.com/en/understanding-lateral-joins-in-postgresql/

Projects vs Data Products

Very interesting topics, how the approach to data-related topics is changing. From a design approach (ticket and off we go) to a product approach (what can we do with data to make life better for users).

TL;DR - Projects have a clear definition of what needs to be delivered while products do not. This fundamentally changes how teams are incentivized and what it means to “do work”.

Link: https://medium.com/@claykarges/data-projects-vs-data-products-why-mindset-matters-c2a02fb7c165

About contracts in the world of data

Times when one person did everything - from design, to integration with the data source, to data processing at the visualization layer. Fortunately or unfortunately, they become history.

And with new times, there are also new concepts - data contract.

This is nothing more than an agreement between at least two parties (usually the backend and the frontend) about what the data schema will look like - what attributes, what data types, if nesting (like JSON), what kind, etc.

I think it’s a very good thing, just used wisely (like everything else). Please remember that such things are supposed to help us and not introduce additional paperwork, so let’s use them where necessary, with mutual consent, and everyone will have a better life.

Link: https://towardsdatascience.com/data-contracts-from-zero-to-hero-343717ac4d5e

Tools

popsql – I quote from the website “Collaborative SQL editor for your team” hmm. Imagine this, you are writing a wonderful piece of code, and suddenly you get a message that your friend is changing commas from the end to the beginning and the whole mood goes to hell 😁

Seriously, such a tool has its advantages. You can easily use them in a conversation with a business analyst (or a business person familiar with SQL) to give your ideas and look at the corrections from the other side. Additionally, it will nicely show you the history of changes, there are undoubtedly advantages.

However, when someone has been building their working style in an IDE or terminal for years, it may be difficult to switch to such a tool.

Link: https://popsql.com/

Check Your Skills

#SQL I wrote about LATERAL JOIN above, but here it is without using this structure.

“For the data from the WISHLIST table, find 3 products (PRODUCTS table) that meet the price condition from the wishlist.”

Solution: https://sqlfiddle.com/#!17/6f615/2

More SQL related questions you can find at SQL - Q&A

Data Jobs

Pledge - Data Engineer – UK (London) – £55,000 – £80,000

Skills sought: Python, SQL, AWS Glue, Spark, Serverless._