I’ve been writing SQL for ~15 years. I’ve seen hundreds of thousands of lines of code.

Over time I developed a set of patterns and best practices I always come back to when writing queries.

This is my attempt to decode them 👇👇👇
Rule 1: Always use CTEs

When writing a complex query it’s a good idea to break it down into smaller components. As tempting as it might be to solve the query in one step don’t.

CTEs make your query easier to write and maintain in the future.
Rule 2: Keep CTEs small and single purpose

Your CTE needs to be an encapsulated logical component that helps you build your final query easier. It shouldn't try to do too much.
Rule 3: Don’t repeat yourself (DRY)

If you find yourself doing the same joins in a query, abstract it to a CTE.

If you find yourself doing the same joins in multiple queries, abstract them to a view.
Rule 4: Don’t tangle dependency chains

In modern data warehouses it's common to build tables on top of other tables in multiple layers of dependency.

Make sure your joins are at the same layer otherwise your dependency graph will look like spaghetti code.
Rule 5: Reduce your data before joining

Modern data warehouses are fast but that doesn’t mean you shouldn’t try to improve query performance.

If you only need six months of data, pre-filter that in a CTE first before your final select.
Rule 6: Only select the columns you need

It’s tempting to do SELECT * everywhere but modern cloud warehouses use columnar storage so the more columns you choose the slower, more expensive your query will be.
Rule 7: Expect the unexpected

From NULLs, to missing data, duplicate rows and random values, real world data is messy. A well-written query is robust enough to handle many of these cases without crashing or giving inaccurate results.
Rule 8: Start with a left join

Real world data is messy. You never know if the column you’re joining on is fully represented in both tables. An inner join will filter out the non-matching rows and they could be important.
If you’ve found this helpful follow me @ergestx

• • •

Missing some Tweet in this thread? You can try to force a refresh

Keep Current with Ergest Xheblati 🦊

Ergest Xheblati 🦊 Profile picture

Stay in touch and get notified when new unrolls are available from this author!

Read all threads

This Thread may be Removed Anytime!


Twitter may remove this content at anytime! Save it as PDF for later use!

Try unrolling a thread yourself!

how to unroll video
  1. Follow @ThreadReaderApp to mention us!

  2. From a Twitter thread mention us with a keyword "unroll"
@threadreaderapp unroll

Practice here first or read more on our help page!

Did Thread Reader help you today?

Support us! We are indie developers!

This site is made by just two indie developers on a laptop doing marketing, support and development! Read more about the story.

Become a Premium Member ($3/month or $30/year) and get exclusive features!

Become Premium

Too expensive? Make a small donation by buying us coffee ($5) or help with server cost ($10)

Donate via Paypal

Or Donate anonymously using crypto!


0xfe58350B80634f60Fa6Dc149a72b4DFbc17D341E copy


3ATGMxNzCUFzxpMCHL5sWSt4DVtS8UqXpi copy

Thank you for your support!

Follow Us on Twitter!