Databricks

Databricks Recursive CTE Example: Querying Hierarchical Data

If you have ever tried to query hierarchical data in Databricks like an employee organizational chart, a bill of materials, or category trees you know it used to be a headache. Historically, data engineers had to write complex Python loops in PySpark to walk through parent-child relationships step-by-step. Thankfully, that is no longer necessary. Databricks now natively supports recursive Common Table Expressions (CTEs). Using the WITH RECURSIVE clause, you can easily query hierarchies and graph data directly in Databricks SQL. Let’s break down exactly how recursive CTEs work and look…

Continue ReadingDatabricks Recursive CTE Example: Querying Hierarchical Data
0 Comments
July 6, 2026
Databricks

Databricks Common Table Expressions (CTEs): A Simple Guide

If you read our recent guide on Databricks SQL subqueries, you know that nesting queries inside one another can get messy very quickly. If you nest them three or four levels deep, your code becomes incredibly hard to read and debug. This is where Databricks Common Table Expressions (CTEs) save the day. CTEs are the secret to writing clean, professional, and easily maintainable SQL. In this guide, we will look at what CTEs are, why you should use them over subqueries, and how to write them in Databricks. Page Content…

Continue ReadingDatabricks Common Table Expressions (CTEs): A Simple Guide
0 Comments
July 6, 2026
Databricks

Databricks SQL Subqueries: Examples and Best Practices

If you write SQL in Databricks, you are eventually going to need a subquery. Whether you are trying to filter data based on another table, calculate a moving average, or just organize a massive query into readable chunks, subqueries are an essential tool for any data engineer. In this guide, we will break down exactly how subqueries work in Databricks SQL, look at the specific types supported, and walk through practical examples you can use right away. Page Content Introduction What is a Subquery in Databricks? Types of subqueries in…

Continue ReadingDatabricks SQL Subqueries: Examples and Best Practices
0 Comments
July 6, 2026
Databricks

Databricks Temporary Tables Support

If you have been waiting for Databricks to add temporary tables, good news—they are finally here in Databricks Runtime 16.4. If you are migrating from databases like Snowflake, Oracle, or Netezza, you already know how incredibly useful these are. Let's break down how to create Databricks temporary tables, why you should use them, and how they actually differ from temporary views. Page Contents Introduction What Are Temporary Tables? Why Use Temporary Tables in Databricks? Temporary Tables vs Temporary Views Databricks Temporary Tables Examples Conclusion Introduction With the release of Databricks Runtime…

Continue ReadingDatabricks Temporary Tables Support
Comments Off on Databricks Temporary Tables Support
June 23, 2025
Databricks

Lateral Column Alias in Databricks – Example

If you have ever written a complex SQL query, you have probably run into this frustrating problem: you calculate a new column using an alias, but when you try to use that alias again in the exact same SELECT statement, the database throws an error. Historically, the only way around this was to write a subquery or a Common Table Expression (CTE). That is no longer the case. The lateral column alias in Databricks allows you to reuse an expression specified earlier in the same SELECT list. This is a…

Continue ReadingLateral Column Alias in Databricks – Example
Comments Off on Lateral Column Alias in Databricks – Example
September 24, 2024
Snowflake

Database Migration to Snowflake: Best Practices and Tips

Moving your legacy data warehouse to the Snowflake Data Cloud is one of the best decisions you can make for performance, scalability, and cost-efficiency. However, migrating from traditional systems like Oracle, Netezza, Teradata, or SQL Server is rarely a simple "lift and shift" operation. Without a solid strategy, you risk data loss, security gaps, and unexpected cloud computing costs. In this guide, we will break down the essential steps for a successful Snowflake migration and the best practices you need to follow to optimize your new environment. Page Content Introduction…

Continue ReadingDatabase Migration to Snowflake: Best Practices and Tips
Comments Off on Database Migration to Snowflake: Best Practices and Tips
April 20, 2023
GCP BigQuery

Reuse Column Aliases in BigQuery – Lateral Column alias

BigQuery lateral Column alias are columns that are derived from the previously computed columns in same SELECT statement. Derived columns or lateral Column alias are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. Many PostgreSQL relational databases such as Netezza supports reuse of the column aliases within the same SELECT statement but GCP BigQuery does not support reuse of calculated derived columns. In this article, we will identify the alternate methods reuse column aliases in Google…

Continue ReadingReuse Column Aliases in BigQuery – Lateral Column alias
Comments Off on Reuse Column Aliases in BigQuery – Lateral Column alias
April 18, 2023
Redshift

How to use Amazon Redshift Replace Function?

The Amazon Redshift REPLACE function is one of the important string functions. The replace function allows you to manipulate the string in Amazon Redshift. This string function is similar to translate and regexp_replace functions. Amazon Redshift Replace Function Page Contents Introduction to Amazon Redshift Introduction to Amazon Redshift Syntax of the Redshift Replace Function Redshift Replace Function Example Usage of the Redshift Replace Function Best practices for using the Redshift Replace Function Conclusion Introduction to Amazon Redshift Amazon Redshift is a fully managed, cloud-based data warehouse service offered by Amazon…

Continue ReadingHow to use Amazon Redshift Replace Function?
Comments Off on How to use Amazon Redshift Replace Function?
March 13, 2023
Redshift

How to Optimize Query Performance on Redshift?

In most of the cases, we pay lots of attention to improve the performance of the web application, but ignore the back-end SQL performance tuning. Amazon Redshift is a fully managed, petabyte-scale, massively parallel data warehouse that offers simple operations and high performance. Amazon Redshift can run a data model such as production transaction system third-normal-form model, star and snowflake schemas, data vault, or simple flat tables. This article takes you through the most common performance-related opportunities when writing query in Amazon Redshift and gives you concrete guidance on how to optimize…

Continue ReadingHow to Optimize Query Performance on Redshift?
Comments Off on How to Optimize Query Performance on Redshift?
January 31, 2023
Redshift

Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL

Amazon Redshift is a data warehousing service provided by Amazon Web Services (AWS). It allows users to store and analyze large amounts of data in a scalable and cost-effective manner. Amazon AWS Redshift RSQL is a command-line client for interacting with Amazon Redshift clusters and databases. Redshift RSQL is similar to Teradata BTEQ and is used to interact with the data stored in a Redshift cluster. In this article, we will check Amazon Redshift RSQL control statements such as IF, ELSE, GOT, LABEL,etc. Redshift RSQL Control Statements Amazon Redshift RSQL…

Continue ReadingRedshift RSQL Control Statements – IF-ELSE-GOTO-LABEL
Comments Off on Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL
January 27, 2023