How to use Common Table Expressions vs Subqueries vs Views vs Temp Tables like a data engineer! #sql

Common Table Expression and subquery is a very common debate! The temporary table crowd is pretty loud too! Don't forget the materialized view crowd !

[anandahs]

fist time learning that materialized can be run concurrently with unique index. thanks for great content.

[stanislawcronberg]

When a DE at my work showed me how to rewrite some of my queries with indexed temp tables to join all the model features into a final table I was so happy

[Kyriaeus]

The CROSS APPLY or LATERAL JOIN clause is also nice if you’re doing multiple steps of transformations on the same relation and don’t want to break out a separate CTE for each layer.

[peekknuf]

Man you're on fire, producing content at the pace I can't consume it :D
Nice job!

[karlnorberg]

Huge thanks for this informative video! Picking up some handy stuff even after +10 years of data engineering or whatever we call it now 🙂

[apibarra]

Love the video! I am a huge proponent of CTEs. Nothing worst when you open a table’s source code and see on massive block of join to multiple tables and sub queries all with their own unique filtering logic at the bottom of the joins or nested in the sub query. I feel like I can understand the logic and read the code much quicker with the CTEs at the top.

[chandanjha]

Important thing to note here is that at 6:30, if you are creating a permanent view in your DB, you need to do it only once as its a DDL statement adding a new object to your schema. It does not have to be repeated everytime else your code will start throwing error.

Modern DBs may let you create temporary views whose scope is only during the execution and is not global. You may choose to use that.

[udzdnxc]

Zach…sir…this video is effin awesome!!! Like the speed of explanation and the examples!!! 👊

[SQLBucketHatGirl]

If only this was posted earlier id probably pass my technical interview on materialized transformation. 😢 thank you for the great explanation and use cases.

[ingenieroriquelmecagardomo]

Awesome video! Would love to see you cover Change Data Capture - with all your breadth and experience, i'm sure it would result in an amazing video.

[subhasishsarkar]

Great work, thank you so much Zach! Very informative and concise. Keep these coming!

[jhonnafg]

this is so helpful Zach, we get table apis and do a bunch of dataset out of it to build up our sass applications. This sheds light since my current role as a DE in this company is really heavy on SQL but not that part of the etl, they kind of separated that and is more on the backend de. Im trying to bridge that since this is also my first role as de

[AntMak-pc]

Great video! What could have been briefly included, in my opinion, is the comparison between materialized views vs (normal) tables, as they seem pretty similar. When to use which one.


Keep rocking the great work👍

[sagarjoshi]

Hey Zach, thank for such informative videos.
Next can you please explain something about dbt usecases. Points like how it tests sql, orchestration, pipline. Other alternativer if any.

[MikeHaynes-xdmw]

Please make more videos like this. Can you just do a series on query optimization

[DookyButter]

I’m a BI developer who occasionally has to dabble in some heavy SQL scripting and your content is hitting some nails on the head that I’ve always been curious about. Humorously, I arrived at the same idea that CTEs are (most often) the best use case scenario. We have another developer that uses subqueries heavily and the performance is really bad.

Great content. You def earned a sub!

[diaconescutiberiu]

Great content, as ever! I'd like to see a video on Data Vault (with hashes) or at least if you can recommend some resources for it... a practical example would be best. Keep up the good work

[dima]

Delivery tip: if something is awesome, you don't have to say so. It will be apparent. Having said that, seeing the 1 to 1 translation between different concepts WAS awesome.

[md.jannatultasnim]

i used CTEs, views, materialized views, temp tables but never found any detail in depth explanations how they works internally and performance comparison. Now everything is clear to me Thank you for the contents you shared. Another thing, as you described here 15:14 does this happens only in the relational DBMS ? or in both relational and data warehouses like snowflake BigQuery ?

[dima]

9:29 can you explain why this is slow in more detail? I assumed that everything would run once and get reused in-memory. What exactly re-ran and why?