## The devil is in the comparison

Consider this: You're leading a SaaS enterprise that provides project management tools. In January this year, there's an unexpected uptick in user registrations. Naturally, you consider attributing this success to the recent interface redesign introduced earlier that month.

However, before finalizing this conclusion, a team member suggests a YoY comparison. Intriguingly, when you analyze January of the previous year, there's a similar surge in user registrations—well before any design changes were made. This pattern hints at a possible seasonal trend, perhaps with businesses seeking project management tools in January to kick off the new year effectively.

How can one differentiate genuine growth from mere seasonal patterns or fleeting spikes? The answer lies in the strategic use of YoY growth. Beyond merely assessing present achievements or setbacks, YoY growth brings to light consistent trends, allowing companies to differentiate authentic growth triggers from regular fluctuations. 

In this article, you will explore in detail how to calculate YoY growth, emphasizing the nuances of YoY calculation using SQL, its significance across diverse business contexts, and its potential limitations. Additionally, you will learn how it can be effectively integrated with other metrics. This article is crafted for you if you are a data leader, data engineer, data analyst, or data scientist who are involved in building, testing, and maintaining data pipelines. The insights provided will empower you to enhance your data strategies, ensuring more accurate and efficient outcomes.

## What is YoY growth

**Year-over-Year (YoY)** growth is a tool that businesses and analysts use to measure and compare performance changes from one year to the next. For example, one might look at the number of new users acquired in August 2023 and compare it to the new user count from August 2022. By using this method, organizations can accurately identify genuine growth trends, even when there are temporary factors that could distort the overall picture.

In practical terms, you can apply YoY growth to various important metrics within a business, such as sales, user engagement, or revenue growth. It offers a clear view of a company's performance trends over time and is vital for making informed investment decisions. Since some metrics, such as sales or revenue, can be affected by seasonal changes, YoY growth helps identify and manage these fluctuations. In essence, year-over-year growth calculation is done by comparing data from the same months across different years.

### Interpretation of YoY growth in practice

When analyzing YoY growth, a consistent increase indicates that a company is doing better compared to the previous year—meaning higher sales, better profitability, or more customers. A decrease in YoY growth, on the other hand, indicates a drop in performance. It's important to note that new companies or startups might show high YoY growth percentages simply because they're starting from a low base. For established businesses, even a small positive YoY growth can signal steady and sustainable improvement.

But numbers don't operate in a vacuum. External factors, such as the overall economy and market competition can influence YoY growth. For instance, while high YoY numbers in a growing market are expected, strong growth during economic downturns indicates effective business strategies. Furthermore, comparing a company's YoY growth to its competitors or industry averages can provide additional insights, pointing out areas of opportunity or potential risks. This approach emphasizes the need for growth that is not just positive but also competitive.

Different industries have varied benchmarks for what constitutes a "good" YoY growth rate, largely influenced by market dynamics, competition, and external factors. For instance, in the rapidly evolving tech sector, especially for startups, a YoY growth rate of 100% or even higher might not be uncommon in the early stages. Conversely, for mature industries like traditional manufacturing or utilities, a YoY growth rate of 5-10% might be considered commendable. Retail businesses, depending on market saturation and competition, might aim for a YoY growth of 10-20% to stay competitive. E-commerce platforms, given their scalability and global reach, often target YoY growth rates above 20%. It's essential for businesses to not only aim for positive YoY growth but to also contextualize their growth against industry benchmarks and direct competitors to gauge their true market performance.

## How to calculate year over year growth

### Year over year growth formula

The principle behind the YoY change formula is straightforward. It's determined by taking the difference between a value from the current year and the same value from the previous year. This difference is then divided by the value from the previous year, and finally multiplying the result by 100 to get a percentage.

![Year over year growth formula](//images.ctfassets.net/iv1sg9nibjwl/pbaNHhu5iRKXtgA74KcRY/416f4c8868f8bc2e76862dcecec82c5c/YOY_Calculation_01.jpg)
Let’s consider a company’s revenue: 

* Revenue in 2022: $1,200,000 
* Revenue in 2021: $1,000,000 

![How to calculate year over year growth](//images.ctfassets.net/iv1sg9nibjwl/1RXXpl4r5jz7XiF7QUcbRF/ab4910ca05ee70b13dc016b8a5cb4c27/YOY_Calculation_02.jpg)

This means there’s a 20% YoY growth in revenue from 2021 to 2022.

### Calculating year over year growth with SQL

Consider you have the monthly recurring revenue and customers' data stored in a table. To calculate the YoY growth using SQL, you can use [`the LAG()`](https://docs.snowflake.com/en/sql-reference/functions/lag) window functions (make sure to handle division by zero cases if there are):

```sql
select
    month, 
    mrr, 
    lag(mrr, 12) over (order by month) as mrr_12_months_ago, 
    100 * (mrr / nullif(mrr_12_months_ago, 0) - 1) as yoy_mrr_growth, 
    customers, 
    lag(customers, 12) over (order by month) as customers_12_months_ago, 
    100 * (customers / nullif(customers_12_months_ago, 0) - 1) as yoy_customer_growth 
from 
    monthly_revenue 
;
```

In situations where you're unable to utilize the window function, you can compute the YoY growth by applying self join:
```sql
select
    mr1.month, 
    mr1.mrr, 
    mr2.mrr as mrr_12_months_ago, 
    100 * (mr1.mrr / nullif(mr2.mrr, 0) - 1) as yoy_mrr_growth, 
    mr1.customers, 
    mr2.customers as customers_12_months_ago, 
    100 * (mr1.customers / nullif(mr2.customers, 0) - 1) as yoy_customer_growth
from 
    monthly_revenue as mr1
    left join monthly_revenue as mr2 on mr2.month = mr1.month - interval '12 months'
;
```

In other scenarios, when we are focusing on quarterly qualified users, the approach changes a bit. If you don't already have a table with the computed metric, start by aggregating daily signups to get quarterly qualified new users. Then, use the lag function similarly to the previous example. However, adjust the lag offset to 4 to account for the quarterly metric, looking back 4 quarters for a year-over-year comparison, as opposed to the 12-month lag for monthly metrics.

```sql
with quarterly_data as (
    select 
        date_trunc(quarter, created_date) as created_quarter, 
        count(distinct user_id) as new_users
    from 
        users
    where 
        is_qualified = True
    group by 
        1
)
```
```sql
select 
    created_quarter, 
    new_users, 
    lag(new_users, 4) over (order by created_quarter) as new_users_last_year_same_quarter, 
    100 * (new_users / nullif(new_users_last_year_same_quarter, 0) - 1) as yoy_new_users_growth
from 
    quarterly_data
;
```

If you want to drill down further and understand the growth rate by specific categories, you’ll need to modify the initial SQL query above. You will introduce the **_partition by_** clause within the window function to segment the data by the chosen category and include the category in your `_select_` clause to have it displayed in the final output.  For instance, let’s calculate the revenue growth rate by subscription plans. 

```sql
select 
    subscription_plan, 
    month, 
    mrr, 
    lag(mrr, 12) over (partition by subscription_plan order by month) as mrr_12_months_ago, 
    100 * (mrr / nullif(mrr_12_months_ago, 0) - 1) as yoy_mrr_growth 
from 
    monthly_revenue
;
```

By looking at the growth rate for each category, we can easily understand which categories are driving growth and which ones are underperforming, and provide appropriate insights to stakeholders.

### Visualizing YoY growth

Now that we’ve delved into the mechanics of YoY calculation and its SQL implementation, it's equally essential to visualize this data effectively. Let's transition to tracking YoY growth visually.

To understand the evolving trend, it's insightful to first compare data across different years. A line chart serves this purpose. To create a line chart, ensure you possess a table featuring a column for the month and individual columns for each year being compared. Position the month on the horizontal axis and add data for every year as a distinct series. For enhanced clarity in observing trends, adjust the vertical axis so it doesn’t start at 0. 

Consider the following graph, which presents the monthly new user metric spanning three successive years:
![Visualizing YoY growth](//images.ctfassets.net/iv1sg9nibjwl/59gBIzGxqcTD8RQ9jk68DP/13ee53abf0f3ec1155cda9e876912b11/YOY_Calculation_03.jpg)

From this visualization, it's evident that challenges began emerging from the second half of the previous year, with the YoY growth for the initial months of the present year turning negative. For a more granular insight into the growth trajectory, let's combine monthly metrics with the YoY growth. First, ensure you have a table that includes columns for the month, metric and you growth. To construct a combined chart, place the month column on the horizontal axis and represent the monthly new users on the left vertical axis using bars. Meanwhile, illustrate the YoY growth metric with a line on the right-hand axis. Consider the final chart below:

![YoY growth metric with a line on the right-hand axis](//images.ctfassets.net/iv1sg9nibjwl/lUVObejCMzIquoZueMlms/691778e3f4c717733864e19039b53835/YOY_Calculation_04.jpg)
This way, we can evaluate the metric in relation to the previous year (identifying whether this year's figures are better than the last) while concurrently observing the growth trajectory. For example, while YoY growth might be positive, if the rate of growth is slowing down, it signals a need for proactive measures.

Alternatively, when the goal is to spotlight just the current month's metric alongside the YoY growth, using a big number visualization becomes advantageous. You should have columns for the year and the metric. Select the metric column as the value and use the year column to depict the trend as seen in the Monthly recurring revenue chart below.

![Monthly recurring revenue chart](//images.ctfassets.net/iv1sg9nibjwl/3uD5Z6YqablBPZkEhcmv4B/ca7ddaa3f08c0931a47522b35fdd4369/YOY_Calculation_05.jpg)

## YoY Growth in action: Use-cases and real-world examples

Beyond the basic understanding of YoY growth, let's delve into its practical application across different sectors:

1. **Retail industry**: Imagine a retail store that introduced a new product line last year. The sales of this product could have a significant YoY increase, especially if the product gained traction and became popular. By observing this surge, the store can anticipate inventory needs for the upcoming year, plan marketing campaigns, or even expand the product line.

2. **SaaS businesses**: For Software-as-a-Service (SaaS) companies, customer retention is crucial. By examining the YoY growth in subscriber numbers and comparing it to the rate of new sign-ups, these businesses can determine if their user base is growing organically or if there are potential issues with customer churn.

3. **Tourism and hospitality**: In regions where tourism is seasonal, like beach resorts or winter skiing locations, businesses might experience drastic YoY changes based on factors like weather conditions, global travel trends, or even geopolitical stability. A hotel in a popular summer destination could use YoY growth to anticipate bookings for the upcoming season, adjusting rates and promotions accordingly.

By observing these use-cases, it's clear that the YoY growth calculation isn't just a metric but a lens through which businesses can make informed strategic decisions. Whether it's product expansion, pricing strategies, or anticipating market trends, YoY growth serves as a valuable compass.

## YoY calculation common mistakes/best practices

### Best practices

To harness the full potential of YoY growth analysis and ensure reliable insights, businesses should adhere to specific best practices: 

1. **Consistent data collection**: Ensuring that data is collected using the same methods and standards year after year guarantees reliable comparisons. Changes in data collection techniques can lead to disparities that aren't reflective of actual performance differences.

2. **Supplementary metrics**: While YoY provides a yearly perspective, metrics like MoM or QoQ offer more immediate insights. Employing these metrics in tandem can paint a comprehensive picture, helping businesses spot both long-term trends and short-term shifts.

3. **Historical context:** A one-year comparison might not be enough. It's crucial to look back multiple years to spot recurring patterns, anomalies, or unique events that could influence the YoY growth rate.

4. **Recognize start-up growth patterns**: New ventures often show staggering YoY growth rates in their initial stages. Understanding that this can result from a low starting point—and not just business success—ensures realistic expectations and interpretations.

### Common mistakes

While YoY growth is a powerful analytical tool, certain pitfalls can skew interpretations and lead to misguided decisions. Being aware of these common mistakes is paramount: 

1. **Being misled by outliers**: A sudden surge or drop in a metric can distort YoY growth. For instance, a one-time marketing campaign or an unexpected event in the previous year can make current YoY growth seem misleadingly high or low. It's crucial to identify and understand such anomalies.

2.**Overlooking short-term fluctuations**: While YoY focuses on annual performance, there could be significant monthly or quarterly changes that it misses. If a business faces a new competitor or a product recall, YoY might not immediately reflect the resulting challenges.

3. **Over-relying on percentages**: For startups or businesses launching a new product, even a small increase in numbers can represent a massive percentage growth. While it's technically accurate, this can provide an inflated sense of success. Absolute figures, alongside percentages, offer a clearer perspective.

4. **Neglecting external influences**: External events, from economic downturns to global crises, can significantly influence a company’s YoY performance. Not recognizing the growth changes  due to broader industry trends, regulatory changes, or global economic conditions and assuming such changes are solely due to internal strategies can lead to misguided business decisions. 

Grasping the nuances of YoY growth and avoiding common pitfalls helps businesses make informed decisions, ensuring they respond effectively to genuine growth opportunities and challenges.

## MoM growth calculation in SQL: MoM vs YoY

In addition to Year-over-Year (YoY) growth, understanding growth metrics over shorter time intervals is crucial. One such metric is the Month-over-Month (MoM) growth, which enables businesses to promptly identify and react to recent trends and adjust strategies. The foundational formula for MoM remains consistent, mirroring the YoY approach. 

![MoM growth calculation in SQL: MoM vs YoY](//images.ctfassets.net/iv1sg9nibjwl/36SexftVZULERDLiNRTFF4/297842c03d23713b87d6c3df5301e2fc/YOY_Calculation_06.jpg)
The corresponding SQL query for MoM also closely resembles the YoY query. The main difference lies in the window function’s offset, shifting from 12 in YoY to just 1 for MoM calculation of monthly metrics. 

```sql
select  
    month, 
    mrr, 
    lag(mrr, 1) over (order by month) as mrr_previous_month, 
    100 * (mrr / nullif(mrr_previous_month, 0) - 1) as mom_mrr_growth 
from 
    monthly_revenue
```

## Conclusion

Year-over-Year (YoY) growth is a key metric for understanding how a business is doing over time. It's straightforward and helps quickly see longer-term trends. But, like any tool, it has its limits. To really grasp a company's performance, it's important to use YoY growth wisely and pair it with other metrics. When used together, these insights can offer a clearer view of where a business stands and where it's headed, helping leaders make smart decisions for the short and long term.

Throughout this article, we identified the role of YoY growth in tracking the longer-term performance of businesses. We've delved into the details of its calculations and demonstrated how businesses can compute this metric through practical SQL implementations. Alongside, we gained insight into visualizing YoY trends and understanding the potential pitfalls and best practices associated with its calculation. 

[PopSQL](https://popsql.com/), the collaborative SQL editor, further streamlined our journey by facilitating seamless query execution and intuitive data visualization. 

## FAQ on YoY growth calculation

### 1. Why is YoY growth preferred over other metrics like MoM?

YoY growth provides a long-term view, making it easier to spot genuine trends by minimizing the noise from short-term fluctuations.

### 2. How is YoY growth different from MoM growth?

While YoY compares metrics over a 12-month period, MoM examines changes from one month to the next, offering a shorter-term view.

### 3. Can YoY growth be negative?

Yes, if the current year's value is less than the previous year's, YoY growth will be negative, indicating a decline.

### 4. How frequently should I calculate YoY growth?

Although it's a yearly metric, calculating YoY growth monthly or quarterly can help businesses stay updated with current trends.


Uncover the significance of Year-over-Year (YoY) growth in business analysis. Our guide offers a quick understanding of its calculation, SQL implementation, and impactful visualization. Ideal for data professionals eager to leverage YoY growth for strategic decision-making.

YOY Calculation: How to Calculate Year-Over-Year Growth

Explore the Snowflake vs Redshift comparison to discover your ideal data warehousing match. Learn more about enhancing your data warehousing experience with PopSQL, a modern SQL editor that complements both Snowflake and Redshift.

Snowflake vs. Redshift: Key differences and comparison

Snowflake vs Redshift: Key differences and comparison

[dbt](https://docs.getdbt.com) is a tool that allows us to design, implement, and test workflows for the creation and population of data models. It solves common pain points in data model implementation, such as lack of standardization, complicated dependency management, and repetitive code. It integrates smoothly with popular cloud data warehouses, such as [Google Cloud BigQuery](https://cloud.google.com/bigquery), [Snowflake](https://signup.snowflake.com/?utm_source=google&utm_medium=paidsearch&utm_campaign=ap-ph-en-brand-core-exact&utm_content=go-rsa-evg-ss-free-trial&utm_term=c-g-snowflake-e&_bt=591349678933&_bk=snowflake&_bm=e&_bn=g&_bg=125204677822&gclsrc=aw.ds&gad=1&gclid=Cj0KCQjw7PCjBhDwARIsANo7CgnTl9497Z8rsOWjFKyczYPq9HGW0bj5gfCJJaw8JvofgD0oxHDHrbIaAl5eEALw_wcB), and [AWS Redshift](https://aws.amazon.com/redshift/). Its best features include auto-generated documentation, generic and custom tests, and Jinja templating for macros and models.

dbt is a powerful tool, however, visualizing the effects of the transformations and standardizations you write is not straightforward. You need to run dbt multiple times and manually check your database and logs to examine its behavior. This process can be cumbersome and hamper the velocity of development and testing. PopSQL takes care of all this through its seamless nature–allowing you to run, share, save, and visualize queries in an intuitive UI.

[PopSQL](https://popsql.com/) offers cool features that help you get the most out of dbt, making data analysis seamless across your teams. PopSQL has a dbt Core integration in [beta](https://popsql.com/dbt), with a planned public release later this year.

## dbt Setup

Before starting, you must install dbt using `pip` or `homebrew`. Once done, you should also install the necessary [dbt package for your data platform](https://docs.getdbt.com/docs/core/connect-data-platform/about-core-connections) such as Redshift, BigQuery, or Snowflake. Next, you must [set up your profiles.yml](https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml) by providing pertinent connection details.

If your data warehouse is BigQuery, here is the series of commands you will need:
1. `pip install dbt-core`
2. `pip install dbt-bigquery`
You will know that `dbt` is installed by running `dbt` and seeing the following output:

![Installing pip and homebrew](https://popsql.com/static/blog/images/dbt%20Superpower_01.jpg)

After which, you can get started on your project by running `dbt init`. It will generate a base template for your project. The base template can be considered the foundation of the _structure_ we are trying to build. We can later add in more features and flesh out the structure to come up with the final product.
You will then be asked the following details:
* Project name
* Database adapter (BigQuery, Snowflake, Redshift, etc.)
* Authentication method (OAuth or Service account)
* Project ID (name of the project within the cloud platform)
* Dataset ID
* Threads (number of concurrent threads)
* Timeout in seconds (default is 300)
* Region (US, EU, etc.)

## Connecting your database with PopSQL

If you do not have PopSQL yet, please download it from [here](https://popsql.com) to get the application running locally. In the upper right corner of the PopSQL UI, you can manage your connections by clicking the drop-down menu under the **Run** button.

![Managing connections in PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_02.jpg)

To add a new connection, click the **+** button and choose **Add new connection**:
![Adding a new connection to PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_03.jpg)
You will then be presented with a list of databases. Simply choose the database that you are using and provide the pertinent connection details. Once done, you should be able to see all your tables in the PopSQL main dashboard.

## Connecting dbt and PopSQL
To connect dbt and PopSQL, first make sure that you [have your dbt project files on a git repo](https://docs.github.com/en/get-started/quickstart/create-a-repo). Note that you don't need to use GitHub; any git repo should work with PopSQL (GitLab, GitHub, or Bitbucket). Then, on PopSQL, hit **Install dbt Core integration**.
![Installing dbt Core integration on PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_04.jpg)

Next step is to add a deploy key in your Github repo, which can be found under Settings.
![Add the deploy key](https://popsql.com/static/blog/images/dbt%20Superpower_05.jpg)

Apart from that, make sure to fill out the other details on PopSQL, such as the git repo URL, main git branch, and project subdirectory. Configure dbt by setting the dbt Core version and defining a default connection setup, if any.
![Configuring dbt core in PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_06.jpg)

Once done, you should be able to view your dbt files in PopSQL.
!View your dbt files in PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_07.jpg)

## Enhance dbt development with PopSQL
PopSQL is the first SQL editor with built-in dbt Core™ support. This integration makes your life simple by allowing you to focus on dbt development without the fuss of several dbt runs. In the following sections, you will see how you can truly superpower your dbt development with PopSQL.

### Reduced context switching
Context switching is real pain in dbt development. In practice, developers are constantly switching between their IDE, terminal, and browser. As mentioned above, it can be a jarring experience, especially for novices.

Consider a scenario wherein you are developing a full application that uses not just `dbt`, but also version control like Gitlab, a data warehouse solution like Snowflake/AWS Redshift/GCP BigQuery, and various processing steps written in either bash or Python and executed via an orchestration tool. The development process becomes overwhelming especially for newly onboarded resources. Using PopSQL to reduce context switching should augment productivity.

### Simplified data exploration and visualization
Prior to writing models, tests, and macros, the first step is to understand your data. To do this, you have to dig through your data and understand how each part relates to another. This is also one of the initial steps to creating a data model, which is a blueprint for the database.
PopSQL simplifies data exploration using queries.

Let’s take a look at the different features in the data on PopSQL by looking at a sample table named `events`. In this scenario, `events` refer to user actions on a website such as `Start Subscription`, `Delete Image`, and others.
```sql
SELECT DISTINCT name FROM events
```
You will see the output as follows:
![Exploring data with PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_08.jpg)

From here, we can see that we have 10 unique features that we can possibly analyze. Let’s try to answer a simple question: “Which feature is used the most?”
```sql
SELECT
name,
COUNT(
*
) AS invocations
FROM events
GROUP BY name
ORDER BY invocations DESC
```
![Most used feature in a table](https://popsql.com/static/blog/images/dbt%20Superpower_09.jpg)

Okay, now we have a general feeling of what features are most used. We can create a simple visualization to make our findings more intuitive. All we need to do is highlight the query in question and hit **Add to dashboard**.

![Creating simple visualization, Part 1](https://popsql.com/static/blog/images/dbt%20Superpower_10.jpg)

![Creating simple visualization, Part 2](https://popsql.com/static/blog/images/dbt%20Superpower_11.jpg)

Regardless of the task at hand, developers should always start by understanding their data, and running simple queries such as the ones above are very useful for that purpose.

### Preview your dbt model results instantly
Testing model queries can be cumbersome when you need to execute `dbt run` every time because if your assets are not properly tagged, you run the risk of redundantly running everything again. Executing a full `dbt run` could take up to 30 minutes for large data with many tables because there is latency between the local machine and the cloud. Hence, using PopSQL to test your queries quickly will shave off a large chunk of development time.
Now, let us define a model that tells us which features were most used during a certain time period. Let’s assume that this is relevant to our stakeholders because we ran a 5-day promotion where the full version of the application was available to everybody. We want to know what features were most interesting to our customers.
```sql
{{ config(
materialized="table",
schema="promotions"
) }}

WITH base_table AS (
SELECT
*
FROM users u
INNER JOIN events e
ON u.id = e.user_id
), most_used_features AS (
SELECT
COUNT(*) AS usage,
name
FROM base_table
WHERE time >= '2020-03-24'
AND time <= '2020-03-29'
GROUP BY name
ORDER BY usage DESC
) SELECT * FROM most_used_features
```
Before we execute the dbt build command, we can use the Preview button compile and run the query. That way you can check for problems without waiting for a full dbt run.

![Preview your dbt run](https://popsql.com/static/blog/images/dbt%20Superpower_12.jpg)

In the screenshot above, the run failed because the model file had a syntax error. This is one example of how you do not need to switch contexts. To fix the error, simply fix the syntax error within PopSQL. Then, once verified working, we can go on and hit **Run** to materialize the tables in our cloud data warehouse.

![dbt run on PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_13.jpg)

Unlike traditional dbt development, where users have to comb through logs to identify the errors, you can see that any problems are logged conveniently within PopSQL as shown in the screenshot above. Once the run is completed, we can view the results in our platform of choice. In this instance, it’s BigQuery as shown below:

![Showing dbt run results in BigQuery](https://popsql.com/static/blog/images/dbt%20Superpower_14.jpg)

### Speed up macros development
Using Jinja to write macros can sometimes get tricky especially when it comes to testing. This is because in order to test the macro, you will have to execute `dbt run` to see its effects. It can get very cumbersome.

Let’s say that we’ve written a macro that converts timestamps to dates by converting the date fields to Pacific time and removing the time component. The format is as follows: **MM-DD-YYYY**. The macro would look like this:
```sql
{% macro timestamp_to_date(time_col) %}
FORMAT_TIMESTAMP("%b-%d-%Y", {{ time_col }})
{% endmacro %}
```
We can test this out first on PopSQL like so:

```sql
SELECT FORMAT_TIMESTAMP("%b-%d-%Y", time) FROM supershy.events
```
Which will output this:
![Writing dbt macro on PopSQL](https://popsql.com/static/blog/images/dbt%20Superpower_15.jpg)

Then once we are confident, we can also apply the macros to other queries. We can transform the query from the previous step and aggregate data on a daily basis as shown:
```sql
{{ config(
materialized="table",
schema="promotions",
tags = ["staging", "promotions"]
) }}

WITH base_table AS (
SELECT
*,
{{ timestamp_to_date('time') }} as day
FROM supershy.users u
INNER JOIN supershy.events e
ON u.id = e.user_id
), most_used_features AS (
SELECT
name,
day,
COUNT(*) AS usage
FROM base_table
WHERE day >= 'Jan-1-2020'
AND day <= 'May-28-2021'
GROUP BY name, day
ORDER BY day DESC
) SELECT * FROM most_used_features
```
The chart below can be found in the **Dashboards** tab. Note that the visualization can be produced by highlighting the SQL statement and hitting **Add to dashboard** as demonstrated above.

![working with dbt macros, 1](https://popsql.com/static/blog/images/dbt%20Superpower_16.jpg)

![working with dbt macros, 2](https://popsql.com/static/blog/images/dbt%20Superpower_17.jpg)

### Evaluate your custom tests
The reason why we use custom tests is to catch test cases that aren’t covered by the generic tests. [dbt tests](https://docs.getdbt.com/docs/build/tests) are macros that test a certain condition and if the result is not null, then it fails.

A test that we often need but is cumbersome to run manually is the duplicate test. This test checks if there are duplicate rows in the table.
```sql
{% test check_duplicates(model, column_name, num_cols) %}

select distinct *
from {{ model }}
{{ dbt_utils.group_by(num_cols)}}
having count(*) > 1

{% endtest %}
```

Note that `[dbt_utils](https://github.com/dbt-labs/dbt-utils#group_by-source)` is a package with a suite of useful macros and it is available for public use. The macros within are comprehensive and can save you even more time as you do not have to reinvent the wheel.

In PopSQL, we test by simply hitting **Test**:

![Evaluating custom tests](https://popsql.com/static/blog/images/dbt%20Superpower_18.jpg)

PopSQL will log any failures either in the compilation or at runtime, as shown below:

![Errors highlighted in compilation](https://popsql.com/static/blog/images/dbt%20Superpower_19.jpg)

In less than 5 minutes, we have verified that our macro is working as expected. We can now use it as part of our pipelines.

### Manage schema changes in source data efficiently
Changes in source data are inevitable as businesses are constantly evolving. However, this translates to additional overhead for developers. You need to update your models accordingly to prevent any future accidents, such as schema mismatches and inconsistent data. Normally, business-side users are tasked with the responsibility of communicating any source changes. However, in PopSQL, you can actually set up notifications for schema changes.

![Setting hp notifications for schema changes](https://popsql.com/static/blog/images/dbt-Superpower_20.png)
[Source](https://popsql.com/product/data-catalog)

Then, to modify your model, simply do so in the PopSQL app and commit your changes to your repository.
![Modifying your model](https://popsql.com/static/blog/images/dbt%20Superpower_21.jpg)

### Implement version control
PopSQL has a native integration with Git for dbt. Meaning, that version control is streamlined similar to VSCode. Version control is important for keeping code up-to-date and implementing new features and fixes. If you are not already using a version control system, you can start with [Github](https://github.com).

![Implementing version control](https://popsql.com/static/blog/images/dbt%20Superpower_22.jpg)

## Conclusion
dbt on its own is a powerful tool. But it is not always easy to work with dbt due to constant context switching between terminal, code editor, and SQL editor. PopSQL brings it all for you through its powerful SQL editor that lets you manage your entire dbt development in one place. Its streamlined nature, using PopSQL for dbt development, shaves off hours (and sometimes days) of development time (and frustration).

With PopSQL, you can improve your dbt development experience radically as you:

* Conduct data exploration and create dashboards
* Test your model queries on PopSQL
* Test your macros on PopSQL
* Test your custom tests on PopSQL
* Update source models every time there is a change in source data
* Implement version control

Try the dbt core beta today by signing up [here](https://popsql.com/dbt).

Blog - dbt Superpower cover image

dbt Best Practices: Superpower Your dbt Project with PopSQL

Blog - chat gpt for sql cover

ChatGPT for SQL: How to Write SQL Queries with OpenAI

**Introduction**

In the fast-paced world of data analytics, continuous learning and adapting to new technologies makes a huge difference in efficiency. For those who already recognize the value of dbt, diving deeper into its mechanics, such as dbt macros, can offer an edge. If you're a leader in data or a hands-on expert like a data engineer or analyst, learning about dbt macros and their interplay with Jinja can be extremely beneficial. With the added benefit of tools like PopSQL on top of dbt Core, this process has become even more streamlined. In this post, we'll explore the intricacies of dbt macros, understanding their core components, the magic of Jinja, and how PopSQL's dbt integration can transform your macro creation and testing.

## What is a dbt macro?

A dbt macro, within the dbt framework, works like a function in traditional programming. It allows users to encapsulate specific, often repetitive, SQL logic into a named, callable unit. Just as you'd use a function in programming to avoid writing the same code repeatedly, a dbt macro lets you define a particular SQL operation once and then call upon that operation wherever you need it in your dbt project.

By leveraging macros, analysts and data engineers can maintain cleaner, more modular, and consistent codebases in their data transformation workflows. Think of it as a way to give SQL supercharged, function-like capabilities within the realm of dbt!

## How do macros work?

dbt macros use Jinja, a templating engine that is widely adopted in Python web frameworks. With Jinja, you can embed placeholders and logic directly into SQL code, making it dynamic and modular.

In a dbt macro, you can define a block of SQL with parameters. This block can then be reused throughout your dbt project with different arguments. 

### Example: a simple dbt macro

Let’s go through a super simple example. In this example we need to adjust all timestamps that are currently stored without a timezone, over to US Pacific time, since that’s the timezone the team has requested for all data analysis. We know those timestamps are currently in UTC time, so we can create a reusable macro to convert timestamps for us. First, create a new macro file under the macros folder in your dbt project with this code: 

```plaintext
{% macro to_reporting_timezone(column_name) %}

  convert_timezone('UTC', 'America/Los_Angeles',
{{column_name}}::timestamp_ntz)

{% endmacro %}
```

When you use this macro in one of your dbt models and pass in a UTC timestamp column as an argument, Jinja replaces the placeholder `{{ column_name }}` with the argument provided. So, invoking `{{ to_reporting_timezone('created_at') }}` would produce:

```plaintext
convert_timezone('UTC', 'America/Los_Angeles',
created_at::timestamp_ntz)
```

This new macro can be used across all your models. It’s super convenient when creating new models that also need to convert timestamps to the correct timezone. Plus, if you ever need to adjust the timezone used for reporting, you only need to change it in that one macro, rather than a bunch of individual model files. 

By using dbt macros with the Jinja templating engine, SQL code becomes more reusable, maintainable, and modular. It allows data teams to avoid redundancy, enforces consistency, and streamlines the development process.

## Jinja variables and placeholders in dbt macros 

### Variables 

**Syntax:** `% set... %`

When using Jinja in dbt macros, you can use set to assign values to variables that can be referenced or reused in your macro. In the example below, a variable called partners is defined that contains a list of three partners that resell our products. We’ll come back to this variable [when we review for loops later in this article](https://popsql.com/blog/dbt-macros#for-loops).

```plaintext
{% set partners =  ['costco', 'sams_club', 'target'] %}
```

### Placeholders

**Syntax:** `{{...}}`

Anything enclosed between double curly braces is evaluated and replaced with its value. In dbt, the most common use of this syntax is for the functions `ref()` and `source()`. In the example below, the `users` model is referenced.

```plaintext
select 
	organization_id, 
	min(created_at) as first_user_created_at

from {{ ref('users') }}
group by organization_id;
```

The `ref('users')` is a dbt function that gets replaced with the actual reference to the view or table that is defined in the users model of your dbt project. When the above code gets compiled, it looks like this:

```plaintext
select 
	organization_id, 
	min(created_at) as first_user_created_at

from dbt.users
group by organization_id;
```

## dbt Jinja syntax in macros

Next, let’s review Jinja syntax in the context of creating dbt macros. Jinja provides the building blocks for macros, so a strong understanding of Jinja syntax is key to writing the best possible macros.

### Comments

**Syntax:** `#...#`

Anything between `{#` and `#}` is a comment and will not be included in the final SQL. This is usually only necessary when you need to document something specific about your Jinja logic. You should prioritize documenting macro and input descriptions in dbt schema files rather than code comments. 

```plaintext
{# this text will appear in your file but not in the compiled output #}
```

### If statements 

**Syntax:** `{% if... %} … {% endif %}`

This syntax is used for simple conditionals (if statements) to add/remove lines of SQL. For example, in the dbt macro example below, the where statement only gets added to the query if the condition is met. In this case, the condition is that the model is being run incrementally. If the model is **not** being run incrementally, the where statement is left out of the query. 

```plaintext
{% if is_incremental() %}

  where last_update_at > (select max(last_update_at) from {{ this }})

{% endif %}
```

It’s possible to build very complex logic using Jinja, but we recommend trying to keep things simple and readable. Keep in mind that most people who use dbt are data analysts who need to understand how the SQL queries work, and may not have time to learn all the complex logical operators in Jinja.

### For loops

**Syntax:** `{% for... %}`

You can loop over lists or sequences using Jinja for loops. That can be super useful in dbt macros as a way to handle logic that can be difficult in SQL alone. For example, if you need to UNION multiple variations of the same table that is stored across a changing set of database schemas. 

In the dbt macro example below, we are using a variable called `partners` to create a list of partners we work with. For each partner, we have a separate database schema, but the **sales** table always uses the same columns. We need to `union` the tables together to get a complete list of partner sales. 

```plaintext
{% set partners =  ['costco', 'sams_club', 'target'] %}

{% for partner in partners %}

  select *,
    '{{ partner }}' as partner

  from {{ source(partner, 'sales') }}

  {# this adds union all to all selects except for the last one #}
  {% if not loop.last %} union all {% endif %}

{% endfor %}
```

The resulting query will look like this:

```plaintext
select *, 'costco' as partner
from costco.sales 

union all 

select *, 'sams_club' as partner
from sams_club.sales 

union all 

select *, 'target' as partner
from target.sales 
```

This for loop can be placed directly into a model file, or we can create a macro that can be referenced in multiple models. It’s also possible to adjust the code above slightly to be reusable if you have multiple tables that need to be union’d together (sales, customers, inventory, etc.)

## Best practices for creating dbt macros 

Building a dbt macro involves a systematic approach to make your SQL code more reusable and modular. Here's a step-by-step process to build a dbt macro:

*  **Identify Repetitive Patterns**:  
    Before diving into macro creation, review your SQL code to identify repetitive patterns or logic. Macros are ideal for situations where you find yourself writing the same or similar SQL across multiple models.
*  **Choose a Meaningful Name and create the macro file**:  
    Name your macro in a way that reflects its purpose. Your goal should be clarity and maintainability. Also try to avoid too many macros that do very similar things. If possible, create a more generic macro with multiple inputs that can be reused in more places.   
    In your dbt project, within the `macros/` directory, create a new `.sql` file for your macro. The name of the file should match the macro's name.
*  **Define the Macro**:  
    Within the file you created, use the `{% macro %}` and `{% endmacro %}` tags to define the macro. Within these tags, write your SQL logic, replacing variable parts with Jinja placeholders.   
    In the example below, the macro has two inputs: `json_column_name` to reference the column name that contains a JSON object, and `nested_column_name` to reference the nested column within that JSON object that needs to be extracted. 

```plaintext
{% macro from_json(json_column_name, nested_column_name) %}

{# trim double quotes due to the way Snowflake returns the parsed JSON #}
trim(

	parse_json({{json_column_name}}):"{{nested_column_name}}", 

  '"')

{% endmacro %}
```

*  **Leave comments to the macro**:  
    It's a good practice to add comments (using `{# ... #}`) within the macro file to describe the technical details of how it works, or any programming decisions you’ve made while creating the macro. This ensures that other team members (or even your future self) can understand its purpose and usage. 
*  **Document the macro in a schema file**:  
    In addition to comments in the macro code, you should add descriptions for the macro and inputs in your schema file. Just like models and columns, macros use the same syntax for documentation. You can create a `macros.yml` file in the `macros` folder of your dbt project so you have a convenient place to document your macros. Here’s an example schema file that defines two of the example macros we explored earlier in the article:

```plaintext
macros:

  - name: to_reporting_timezone
    description: A macro to convert UTC timestamps without a saved timezone into US Pacific time
    arguments:
      - name: column_name
        type: timestamp
        description: The name of the column you want to convert

  - name: from_json
    description: A macro to extract a nested column value out of a JSON column.
    arguments:
      - name: json_column_name
        type: variant
        description: The name of the JSON column that contains the nested column.

      - name: nested_column_name
        type: string
        description: The name of the nested column to be extracted from the JSON column. 
```

*  **Test the Macro**:  
    After writing your macro, use it within a model to ensure it works as expected. Remember, since macros are essentially templates, you'll need to call them from a model or another macro to see the final SQL output.

Remember, the primary objective of dbt macros is to make SQL code more maintainable and reduce redundancy. As you become more experienced with dbt and Jinja, the process of creating macros will become more intuitive, allowing you to focus on complex data transformations and modeling.

## Creating dbt macros in PopSQL

Software engineers typically navigate the development process with dexterity, having honed their skills in environments that demand intricate understanding of code, version control, and collaboration tools. However, as the data landscape evolves, many data analysts find themselves adopting tools like dbt, diving into a world traditionally reserved for developers. For these analysts, the steep learning curve can be daunting. 

This is where PopSQL can help. It offers a more intuitive, user-friendly interface to create dbt macros, bridging the gap between the realms of software development and data analysis. Even for those well-versed in technical intricacies, PopSQL provides a faster, more agile environment. Quick changes, which might entail a series of steps in local development setups, can be executed with remarkable speed in PopSQL, streamlining the process and boosting productivity.

PopSQL’s dbt Core integration runs on PopSQL’s servers so you can even make changes through a browser. All you need to do is grant git access to your dbt project and choose a database connection to use for dbt commands and previewing model results. 

You can see the integration in action by watching this 3-minute video: 

<div class="wistia_responsive_padding" style="padding:62.5% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><iframe src="https://fast.wistia.net/embed/iframe/76ew36hjwq?seo=true&videoFoam=true" title="Creating and applying dbt Macros in PopSQL Video" allow="autoplay; fullscreen" allowtransparency="true" frameborder="0" scrolling="no" class="wistia_embed" name="wistia_embed" msallowfullscreen width="100%" height="100%"></iframe></div></div>

<script src="https://fast.wistia.net/assets/external/E-v1.js" async></script>

## Conclusion

dbt macros, fueled by the power of Jinja, have undoubtedly paved the way for more dynamic and efficient data operations. By understanding the nuances of Jinja's syntax, the dbt macro-building process, and the role of dbt variables, teams can harness the true potential of their data pipelines. However, as with all techniques, having the right tools can make the journey smoother. PopSQL's integration with dbt exemplifies this, making the creation and testing of dbt macros not just feasible, but simple.

Blog - modern data stack cover

How to Write dbt Macros using Jinja

Hello, PopSQL community! We're back with another exciting announcement: our platform now supports connections to [Materialize](https://materialize.com). If you've ever wished you could just write materialized views and let the database handle keeping results up to date, your dreams just came true.

## Why Materialize?

For those of you unfamiliar with Materialize, here's a snapshot of why it's revolutionizing the way businesses build internal and customer-facing data products: 

*   **Familiar data warehouse architecture:** If you've used a cloud data warehouse, you know how to use Materialize: It has the same storage/compute separation, same SQL workflows.
*   **Streaming engine:** Computation happens on write, as data arrives, instead of query time, so that maintained results are available almost instantly.
*   **Purpose-built for operational data work:** Materialize shines in operational work where an analytic warehouse is too slow and a stream processor is too complicated: Personalization, Fraud Detection, Business Automation, Customer Notifications, even ML and AI feature-serving.

## PopSQL and Materialize: Real-time insights

Bringing Materialize into the PopSQL platform combines streaming data with real-time data collaboration. Here's how this integration will transform your data journey:

*   **Seamless querying**: [With the familiar PopSQL interface](https://popsql.com/product/sql-editor), you can now query your streaming data in Materialize using standard SQL queries. Experience the power of real-time data without the need for a learning curve. Build a query library, comment on team queries, and share links to queries for easy collaboration.
*   **Visualize in real time**: Leverage [PopSQL's charts](https://popsql.com/product/charts) to paint a dynamic picture of your streaming data. Add [query variables](https://popsql.com/product/query-variables) to empower non-technical team members to refresh queries from Materialize.
*   **Schedule updates to email or Slack**: PopSQL [queries can be scheduled](https://popsql.com/product/scheduled-queries) to refresh and post to Slack hourly, or even up-to-the-minute, powered by Materialize and PopSQL.

### Flexible connection and permissions options

PopSQL’s new Materialize connection has been set up to fully support all connection types:

*   **Private connection**: Your connection is only available to you. For additional security you can set up a private direct connection, so the credentials are stored locally.
*   **Fully shared connection**: Foster collaboration by allowing every team member to access your Materialize connection without any barriers. On PopSQL's Enterprise plan you can limit access to specific people and/or teams so you can set up multiple shared connections for different teams that require different levels of access.
*   **Per-user credentials**: Your Materialize connection is set up and owned by an admin, but for added security, individual users login with their own credentials before running queries. If you have complex permissions that vary by user, this option can save a ton of time while still enabling the collaboration features in PopSQL.

## Wrapping up

Our mission at PopSQL has always been to be at the forefront of database technology, providing our users with the tools they need to stay ahead of the curve. Materialize is redefining the landscape of real-time data products, and we're thrilled to integrate its capabilities into your PopSQL experience.

Dive in, stream on, and let real-time insights guide your decisions. Welcome to the future of database querying with PopSQL and Materialize.

Blog - materialize support cover

Introducing Materialize Support in PopSQL!

We're excited to unveil the latest addition to our PopSQL database connections: Trino SQL engine! For those who may not be familiar, Trino is an exceptional open-source, distributed SQL query engine. Trino is renowned for its performance and ability to handle federated queries across various data sources, from Hive and Cassandra to RDBMS. And now, you can unlock its potential right from PopSQL with the new Trino connector.

## Why add a Trino connector?

Here are some of the top reasons companies adopt the Trino SQL query engine. Check out Trino's [website](https://trino.io/) and [documentation](https://trino.io/docs/current/) for more information.

- **Federated queries**: Ever wanted to run SQL on a variety of data sources? With Trino, query Hive, Cassandra, relational databases, and more – all without moving your data.
- **High performance**: Built from the ground up for speed, Trino offers a state-of-the-art query compiler and execution engine, making it a go-to choice for organizations that prioritize performance.
- **Massively parallel processing**: Distribute queries across numerous workers and leverage parallel processing. The result? Significant performance gains for massive datasets accessing PetaBytes of raw data.
- **Full SQL support**: Enjoy ANSI SQL compatibility, complete with intricate queries, aggregations, joins, and window functions. This means Trino queries are right at home in PopSQL, and can be used in BI tools like Tableau and Looker.

## PopSQL + Trino: Dive deeper into the connection

In our commitment to provide enhanced flexibility and cater to diverse organizational needs, we've optimized the Trino connector in PopSQL to work as either a [cloud or direct connection](https://docs.popsql.com/docs/cloud-direct-connections).

- **Cloud connection**: Ideal for those who love the convenience of online platforms. By choosing the cloud connection, you can seamlessly use Trino via PopSQL's web app, schedule queries and alerts in Slack/email, build dashboards and more. You can even join PopSQL's dbt Core™ beta integration.
- **Direct connection**: We understand the sensitive nature of certain analytics tasks. Sometimes, you just need to operate locally or access a private network over a VPN. With the direct connection option and PopSQL's desktop app, your workflow remains uninterrupted, giving you the flexibility to connect in the manner that best suits your tasks.

Additionally, you'll be able to take full advantage of PopSQL's core features that are not available in the Trino UI:

- [Feature-rich SQL editor](https://popsql.com/product/sql-editor) with syntax highlighting and autocompletion, version history and support for real-time collaboration.
- [Saved query library](https://popsql.com/product/shared-queries) with shareable links to queries, private and shared folders, advanced search, and run history.
- Quick [charts](https://popsql.com/product/charts) and [dashboards](https://popsql.com/product/dashboards) with [query variables](https://popsql.com/product/query-variables) that can be [scheduled](https://popsql.com/product/scheduled-queries) or shared directly to Slack.

### Sharing your Trino connection in PopSQL: Tailored to your needs

Every organization operates differently, and PopSQL recognizes the need to support various workflows and security setups. PopSQL supports authentication to Trino and authorization details are respected based on the Trino user who logs into the PopSQL connection. PopSQL offers multiple ways to manage things from there:

- **Private connection**: Your connection is only available to you. For additional security you can set up a private direct connection, so the credentials are stored locally.
- **Fully shared connection**: Foster collaboration by allowing every team member to access the Trino connection without any barriers. On PopSQL's Enterprise plan you can still limit access to specific people and/or teams so you can set up multiple shared connections for different teams that require different levels of access.
- **Partially shared connection**: A blend of security and accessibility. Here, the connection is governed by an admin, but for added security, individual users need to input their credentials before running queries. If you have complex permissions that vary by user, this option can save a ton of time while still enabling the collaboration features in PopSQL.

## In summary

Our mission at PopSQL has always been to provide a seamless SQL experience, regardless of where your data lives. By introducing Trino to our array of database connections, we're taking another step in that direction. Get started with Trino on PopSQL today and unlock a world of querying possibilities!

We expect to work closely with the Trino team, our shared customers, and the open-source community in the coming months. Please reach out to PopSQL support or engage with the [Trino community](https://trino.io/community) for more info. Here's a quote from Martin Traverso, co-founder of [Trino](https://trino.io/) and CTO at [Starburst](https://www.starburst.io/):
> We are excited to see PopSQL adding support for Trino, joining our community, and enabling our shared users.

Blog - trino support cover

Announcing Trino Support in PopSQL!

Complex queries in SQL don't need to be hard to write. Learn what they are, how to create them, and some examples in this article.

## What is a complex SQL query?

When you start working with SQL, you'll learn about the `SELECT` query to get data from tables. You'll then learn a range of techniques such as using the `WHERE` keyword, joining to tables, and ordering your results.

Once you've got the basics, you'll start moving into more advanced queries.

A complex query in SQL is one that has more complexity and that you need to give some more thought to when you design and write it.

A complex query could include features such as:

- subqueries
- joining to many tables, and using different join types
- functions within functions
- group by and having

These kinds of queries take a bit more thought to understand how to design them and write them so they show the results you need, perform well, and are easy to understand.

## How to build a complex SQL query

The main piece of advice I can offer for writing a complex query is that it's like writing many small queries put together.

You might be confused or overwhelmed by the need to write something pretty complex.

However, I suggest writing a small query first, and then adding to it piece-by-piece, until you get the final result you need.

So, start small. Write a simple query that just selects one or two columns from a table.

Run the query and see the result. Is it what you are expecting?

If so, update the query to add a little more to it. Perhaps add some more columns, or a `WHERE` clause to filter data.

Run the query again and check the results. If they look good, keep going. If they don't, then review your query and see what may be causing the issue.

When you start to join to tables in your query, you may want to run a Select query on those tables first. This is so you can see the data in the table and to help you understand what your results might look like.

Once you can see the results from a simple Select in another table, then you may want to join it to your main query. Run the query, then check the results.

Keep going until you have the query and results that you need.

So, to write a complex query in SQL, it's easier to write it in smaller pieces and test it as you go by running the query and checking the results.

## Advanced SQL Techniques for Complex Queries

In the previous section, we mentioned that a complex SQL query may include features such as joining multiple tables and subqueries.

There are a range of other features in SQL to make it easier to write SQL complex queries.

Let's look at a few of them now.

### Common Table Expressions

A Common Table Expression (CTE) is a name you can give to a subquery within your main query.

The main reason you would do this is to simplify your query, making it easier to read and debug. It can sometimes improve performance, which is another benefit, but it's mostly about readability and simplification.

Here's a query with a subquery that does not have a Common Table Expression:

```sql
SELECT

columns

FROM table1

INNER JOIN (

SELECT columns

FROM table2

WHERE conditions

) t2

WHERE conditions;
```

This same query could be updated to use a Common Table Expression. This would involve giving a name to the subquery. This named subquery could then be used as though it was a table in the main query:

```sql
WITH t2 AS (

SELECT columns

FROM table2

WHERE conditions

)

SELECT columns

FROM table1

INNER JOIN t2

WHERE conditions;
```

This might not seem like much of an improvement.

However, there are a few ways it can help.

Moving the subquery to the top of the query and giving it a name will mean it's easier to understand what the query does. If your subquery selects all employees that have a higher-than-average salary, you could name your subquery something like `emp_above_avg_sal`. When you refer to this in the main query, you'll see this name and will know what it refers to.

You can also read and update the logic in one place. The logic to calculate the results for this subquery is at the top of the query. You can often copy and paste this query to another tab and run it separately, making it easier to understand and even debug the query.

It's also helpful if you have a long query and need this logic in several places. You can define it once, at the top of the query, and refer to it many times throughout your main query.

So, consider using Common Table Expressions whenever you have a subquery as a way to improve the readability of your query.

### Window Functions

Another helpful SQL feature is called "window functions".

In SQL, you have functions that calculate a value based on many other records, such as `SUM` and `COUNT`, and return a single row. These are called aggregate functions.

You can use the `Group By` clause with these functions to calculate numbers for each group that is shown.

Let's say you had a table of orders that looked like this:

| id | order_date | order_amount |
| --- | --- | --- |
| 1 | 2023-05-09 | 1250 |
| 2 | 2023-05-09 | 900 |
| 3 | 2023-05-10 | 1810 |
| 4 | 2023-05-11 | 650 |
| 5 | 2023-05-11 | 100 |

Let's say you wanted to see the total amount of orders per day. You can use a `SUM` and a `GROUP BY` for this:

```sql
SELECT order_date,

SUM(order_amount)

FROM orders

GROUP BY order_date;
```

Your results would be:

| order_date | SUM(order_amount) |
| --- | --- |
| 2023-05-09 | 2150 |
| 2023-05-10 | 1810 |
| 2023-05-11 | 750 |

This works, but using the `SUM` and `GROUP BY` in this way means that the records you display must match the way that the data is calculated.

You show one row for each date, and you calculate the sum for that date.

What if you wanted to see all orders and the sum for each date? Or, you wanted to see a running total: the sum of orders so far for that date?

You can do this with window functions.

A window function is a way to write an SQL function that lets you perform a calculation on a range of rows that's different to how you display the rows.

So, you might want a running total like this:

| id | order_date | order_amount | running_total |
| --- | --- | --- | --- |
| 1 | 2023-05-09 | 1250 | 1250 |
| 2 | 2023-05-09 | 900 | 2150 |
| 3 | 2023-05-10 | 1810 | 1810 |
| 4 | 2023-05-11 | 650 | 650 |
| 5 | 2023-05-11 | 100 | 750 |

Your query could look like this:

```sql
SELECT id,

order_date,

order_amount,

SUM(order_amount) OVER (

PARTITION BY order_date

ORDER BY order_id

)

FROM orders;
```

The `OVER` clause means that the function is calculated over a range of rows, and not the entire set of results.

The `PARTITION BY` clause refers to the column that's used to define the range of rows. In this example, all records with the same order_date are used for calculating the sum.

The `ORDER BY` determines which records come first in the running total calculation. In this example, it's by `order_id`.

This query will give you the results including the running total.

So, if you need to calculate data using a different range to what is being displayed, you can use a window function. If it's something you were going to do in application code, consider using a window function to take advantage of the database.

### Hierarchical Queries

The final technique that's common with complex SQL queries is a hierarchical query.

A hierarchical query, or recursive query, is where you write a query that links one record with other records in the same table.

This allows you to work with a table that stores different records where a record is a parent of another record.

Some common examples are:

- employees and managers, where the manager is an employee
- product categories and subcategories, which can have many levels

Rather than having many tables for each level, you can store all of your data in a single table and have a column that refers to the primary key of another record in the same table.

You can then write a query to show the data you need.

For example, you may have an employee table that has a column called `manager_id` that refers to that employee's manager:

| employee_id | first_name | manager_id |
| --- | --- | --- |
| 1 | Sarah | NULL |
| 2 | John | 1 |
| 3 | Michelle | 1 |
| 4 | Frank | 2 |
| 5 | Jerry | 4 |
| 6 | Paula | 4 |

This allows many levels of employees and managers. Sarah has a `manager_id` of `NULL` indicating she is at the head of the organization.

You can use SQL to write a recursive query or hierarchical query to show the entire organization chart.

Here's a query to do that:

```sql
WITH empdata AS (

(SELECT employee_id, first_name, manager_id, 1 AS level

FROM employee

WHERE employee_id = 1)

UNION ALL

(SELECT this.employee_id, this.first_name, this.manager_id, prior.level + 1

FROM empdata prior

INNER JOIN employee this ON this.manager_id = prior.employee_id)

)

SELECT e.employee_id, e.first_name, e.manager_id, e.level

FROM empdata e

ORDER BY e.level;
```

This query uses a Common Table Expression to get a list of employees and their managers.

Within the CTE, called `empdata`, we select the employee with an id of 1, which is the top of the organization.

We then combine this with all of the other employees, by selecting from the same query defined in the `WITH` clause, joining on the manager id and employee id.

This ability to refer to a CTE from within the same CTE is what makes the query a recursive query.

If you're using Oracle or SQL Server, this will work. If you're using MySQL or Postgres, you'll need to add the word `RECURSIVE` after the `WITH` keyword to get it to work:

`WITH RECURSIVE empdata AS ( …`

You will then display a list of employees and their level in the organization.

You can also do some interesting things with this query, such as:

- adding the name of a person's manager
- indenting their name a number of spaces depending on a person's level, to make it look like a tree

So, a recursive or hierarchical query uses a range of advanced techniques such as Common Table Expressions, Unions, and self-joins to get the result you want in a specific type of database design.

## More Examples

Let's take a look at some more examples of complex queries in SQL, using some of the techniques above, as well as other techniques.

### Ranking Results

You can calculate the rank of a record based on one of its values by using the RANK function as a window function.

For example, this query calculates the rank of an order based on its order amount.

```sql
SELECT

order_id,

order_date,

customer_id

order_amount,

RANK() OVER (ORDER BY order_amount DESC)

FROM orders;
```

This will show all orders, and the `RANK` column will show 1 for the highest order amount, 2 for the second highest, and so on. It shows the same values regardless of how you want to order the overall result.

### Difference in Rows

Another example is using a window function called `LAG` to find the difference of a value between two rows.

Without window functions, this would be difficult, but fortunately, we can use `LAG` as a window function to find this out.

Let's say we want to display the total of orders for each month, and then show the difference of this total compared to the previous month.

Our query may look like this:

```sql
SELECT

order_month,

order_total,

order_total - LAG(order_total) OVER (ORDER BY order_month) AS diff_last_month

FROM orders;
```

Using the `LAG` function lets you refer to the previous row, which is done using the `ORDER BY` clause within the `OVER` clause.

### Conditional Sum

You can use the `SUM` function to add up number values. It's often used to add everything in a column.

However, it can also be used to add numbers based on certain criteria.

Let's say we wanted to show the total amount of orders in a month but separated based on paid status.

Here's a query that could be used:

```sql
SELECT

o.order_month,

SUM(

CASE WHEN p.status = 'Paid' THEN o.order_amount ELSE 0 END

) AS total_amount_paid,

SUM(

CASE WHEN p.status = 'Pending' THEN o.order_amount ELSE 0 END

) AS total_amount_pending,

SUM(

CASE WHEN p.status = 'Default' THEN o.order_amount ELSE 0 END

) AS total_amount_default

FROM orders o

INNER JOIN payment_status p ON o.payment_status_id = p.id;
```

The result could be something like this:

| order_month | total_amount_paid | total_amount_pending | total_amount_default |
| --- | --- | --- | --- |
| 2023-01 | 1500 | 800 | 100 |
| 2023-02 | 1800 | 1200 | 50 |
| 2023-03 | 500 | 1550 | 0 |

You can use `SUM` along with `CASE` to create this kind of result where you only `SUM` values that meet certain criteria.

## Complex queries in PopSQL

There are several features within PopSQL that help you work with complex SQL queries.

### SQL Variables

A complex query in SQL can often include specifying parameter values, such as a value to check in the `WHERE` clause or conditions for a `CASE` statement.

If you have this written in an SQL script file, and need to change the values, you'll need to update the query manually for all occurrences of this value, then run the query - and hopefully you don't break it along the way.

In PopSQL, there's a handy feature called SQL Variables. You can add a placeholder for a variable within your query and specify a value. If you need to change the value to be used in the query, you can simply change the value of the variable and run the query.

Here's an example of a query with a variable.
![variable query example in SQL complex queries](/static/blog/covers/07-sql-complex-queries-variable-query-example.png)

We can see the query has the variable within the SQL (called `country_code`) and the variable at the top of the query outside the query itself. It has a value of "USA" but it can be changed.

You can change that value, and run the query again. This is much easier than manually editing the SQL script.

These variables can also be set to use a dropdown of specific values, so you can choose one from the list, reducing the chance of errors.

### Share SQL Queries

Sometimes when you're working on a complex query, you'll need to involve another person. This could be a person within your team to get a second opinion, or a person in another team who's an expert in a certain area of the business, for example.

In PopSQL, you can easily share your query with other people. You don't need to email them a link or a link to a GitHub file.

When you have a query opened, you can click on the “Share” button to specify how you want to share the query.
![how to share SQL complex queries](/static/blog/covers/08-sql-complexqueries-share-query.png)

You can select “Can View” if you want the other person to only be able to view the query. This can be helpful if you're presenting the query but don't need it to be edited, or if you only want someone to be able to run the query and see the results. If your query includes variables, the viewer can change those too.

You can select “Can Edit” if you want the other person to be able to view and edit the query. The other person can edit the query, run it, and essentially work on it with you to complete the query.

### SQL Editor

The SQL Editor within PopSQL has some handy features for working with complex queries.

**Run Multiple Statements**: You can write multiple statements in one query. This is common when working with complex queries, as you may be writing smaller queries as you build up to the final query, or take parts of a large query and run them independently.

If you have multiple statements, you can run them independently. You could also run them all at once by clicking the arrow next to the “Run” button and selecting “Run all statements”.
![how to run statements with PopSQL](/static/blog/covers/09-sql-complex-queries-run-statements.png)

Now, whenever you click “Run”, all of the statements will be executed. If this is something you do often, this can save you time.

**Query Descriptions**: You can add descriptions to your queries to make it easier to identify them in your editor. This is helpful whether you only have a few queries or a lot of queries. I think this is better than adding an SQL comment to the start of the query because you can see the description as you scroll down the query.

**Version History**: PopSQL includes a version history of your queries. This is helpful as you write complex SQL queries and if you ever need to go back in time to a previous version.

PopSQL's editor includes many features you'll find in other SQL editors such as tabbed windows and syntax highlighting, but these features mentioned above are additional features that are helpful for working with complex queries.

## Conclusion

Once you get comfortable with the basics of SQL, you'll start working with more complex queries that are longer and include more features such as subqueries and grouping. Getting better at writing and reading these queries comes with practice and understanding how they work. PopSQL can make it easier for you, whether you're writing them by yourself or working with a team of people.

Blog - sql complex queries cover

Complex SQL Queries: Examples & Advanced Techniques

When it comes to maintaining data integrity and accuracy in cloud-based data warehouses like Amazon Redshift, Google BigQuery, or Snowflake, the Data Build Tool (dbt) has emerged as a powerful solution adopted by data teams. In this guide, we aim to provide an in-depth understanding of the importance of dbt testing in data engineering, explore various types of dbt tests, and offer effective strategies to incorporate these tests into your data workflow.

## Why is dbt Testing Essential in Cloud Data Warehousing?
![dbt tests in cloud data warehousing](/static/blog/covers/dbt-testing-1-dbt-testing-in-cloud-data-warehousing.jpg)
dbt revolutionizes data transformations by [allowing data professionals to perform them directly within the data warehouse](https://www.getdbt.com/), leveraging the efficiency and SQL capabilities of modern cloud-based data warehouses like [Snowflake, BigQuery, or Redshift](https://www.casewhen.co/blog/unlocking-the-power-of-data-with-dbt-cloud). Testing in dbt becomes paramount to ensure data quality and consistency within these cloud data warehouses. Without proper testing, issues such as invalid data types, missing values, or broken relationships between tables can compromise the reliability of data analysis, potentially leading to erroneous business decisions.

## The Business Value of dbt Testing
![business value of dbt testing](/static/blog/covers/dbt-testing-2-business-value-of-dbt-testing.jpg)
Beyond the technical aspects, dbt testing brings significant business value, directly impacting the bottom line. By leveraging dbt testing to ensure accurate and reliable data, organizations gain the confidence to make data-driven decisions that drive business growth and profitability. Dbt testing also helps in reducing costs by identifying and rectifying data issues early on, minimizing the risk of financial losses and operational inefficiencies.

Additionally, [dbt testing contributes to regulatory compliance](https://www.getdbt.com/security/), enabling organizations to meet data accuracy and integrity requirements. It also promotes data democratization by empowering non-technical stakeholders to confidently access and utilize data.
![dbt testing map](/static/blog/covers/dbt-testing-3-dbt-testing-map.jpg)

One supporting business case study that highlights the business value of dbt testing is the success story of [Condé Nast](https://www.getdbt.com/success-stories/cond%C3%A9-nast/). By leveraging dbt Condé Nast was able to create an evergreen platform that allowed their teams around the world to work from the same data sets. This resulted in higher productivity and greater trust in their data, enabling them to become a global enterprise. The use of dbt testing ensured that 100% of their models were shipped with tests, ensuring data accuracy and reliability.

Another case study that showcases the benefits of dbt testing is the Nutrafol case study. Nutrafol's data team was able to [save over 100 hours per month by using automated dbt testing](https://www.datafold.com/case-studies). This allowed them to efficiently review dbt code changes and ensure the quality and integrity of their data.

These case studies demonstrate how dbt testing brings significant business value by enabling organizations to make data-driven decisions, reducing costs by identifying and rectifying data issues early on, ensuring regulatory compliance, and promoting data democratization. By leveraging dbt testing, organizations can gain the confidence to drive business growth and profitability.

PopSQL, with its dbt Core integration, is poised to make a significant difference to businesses implementing dbt testing. Here's how:

1. Enhanced SQL Editor: PopSQL provides a SQL editor with built-in dbt Core support, offering a seamless and intuitive environment for writing and executing SQL queries. This feature streamlines the process of working with dbt and allows users to leverage the power of dbt directly within the SQL editor.
2. Macros and Models Creation: With PopSQL's dbt Core integration, users can create macros and models within the SQL editor. Macros are reusable pieces of SQL code that can be used to eliminate repetitive SQL, making query development more efficient. Models, on the other hand, simplify BI queries by abstracting complex SQL logic into easily understandable and reusable components.
3. Data Catalog: PopSQL enables the creation of a [data catalog backed by dbt Core](https://popsql.com/dbt). This feature helps businesses organize and document their data assets, making it easier for users to discover and understand available data resources. A well-structured data catalog enhances data accessibility and promotes data democratization within the organization.
4. Collaboration and Sharing: PopSQL offers collaboration features that facilitate teamwork and knowledge sharing. Business users can easily analyze data without needing technical developers, thanks to PopSQL's crisp and clean UI editor. This promotes cross-functional collaboration and empowers non-technical stakeholders to access and utilize data effectively.

Overall, PopSQL's dbt Core integration provides businesses with a comprehensive solution for implementing dbt testing. It enhances the SQL editing experience, enables the creation of macros and models, simplifies BI queries, facilitates the creation of a data catalog, and promotes collaboration and knowledge sharing. By leveraging PopSQL, businesses can maximize the benefits of dbt testing and drive data-driven decision-making processes.

## Getting Started with dbt Tests: Schema Tests and Bespoke Data Tests
dbt provides a set of built-in schema tests, including Unique, Not Null, Accepted Values, and Relationships. These tests are crucial for checking the integrity of your data and ensuring data models are accurate and reliable.

The Unique test verifies the uniqueness of entries in a column, maintaining data integrity.
![unique dbt test](/static/blog/covers/dbt-testing-4-unique-dbt-test.jpg)

The Not Null test ensures that a column in a model doesn't contain any null values, preventing gaps in your data.
![not null dbt test](/static/blog/covers/dbt-testing-5-not-null-dbt-test.jpg)

The Accepted Values test ensures that all values in a column fall within a specified set of acceptable values, maintaining consistency across data entries.
![accepted values test](/static/blog/covers/dbt-testing-6-accepted-values-test.jpg)

The Relationships test verifies the integrity of relationships between tables by checking the correctness of foreign keys.
![relationships test](/static/blog/covers/dbt-testing-7-relationships-test.jpg)

In addition to the built-in schema tests, dbt allows you to craft bespoke data tests using custom SQL queries to test specific conditions. These tests provide flexibility and precision, enabling enterprises to address unique, business-specific data scenarios that may go beyond standard schema tests. With tools like PopSQL, writing and managing bespoke data tests becomes even more seamless, allowing data teams to efficiently craft and fine-tune these tests.
![custom schema dbt tests](/static/blog/covers/dbt-testing-8-custom-schema-tests.jpg)

## Unit Testing in dbt: Ensuring Every Piece Functions Perfectly
Unit testing in dbt is a crucial step in ensuring the quality and reliability of your data models. It involves writing SQL scripts to test individual components of your data models, verifying that they function as expected. These unit tests act as guards, preventing downstream data errors by validating each piece of the data pipeline.

Here are some key points about unit testing in dbt:

1. **Purpose:** The purpose of unit testing in dbt is to validate the logic and functionality of individual data models. It helps catch errors and inconsistencies early in the development process, reducing the risk of data issues downstream.
2. **Mocking Dependencies:** In dbt, you can test models independently by mocking their dependencies. This means creating mock inputs for the models and checking the results against the expected output. By isolating each model, you can ensure that it performs correctly regardless of the data it depends on.
3. **dbt Unit Testing Package:** There is a dbt package called "[dbt-unit-testing](https://github.com/EqualExperts/dbt-unit-testing)" that provides support for unit testing in dbt. This package contains macros that can be reused across dbt projects to facilitate unit testing.
4. **Tools and Frameworks:** There are various tools and frameworks available to [implement unit testing in dbt](https://towardsdatascience.com/how-to-do-unit-testing-in-dbt-cb5fb660fbd8). For example, you can use PopSQL, a user-friendly SQL editor, to conveniently write and execute unit tests. It provides a collaborative environment for teams to work together on testing.
5. **Benefits:** Unit testing in dbt offers several benefits. It helps ensure the accuracy and reliability of your data models, reduces the risk of data errors, and improves the overall quality of your data pipeline. It also promotes collaboration and knowledge sharing among team members.

To implement unit testing in dbt, you can follow these steps:

1. Identify the components of your data models that need to be tested.
2. Write SQL scripts to create mock inputs and define the expected output.
3. Use the dbt unit testing package or other tools/frameworks to execute the tests.
4. Analyze the test results and address any issues or discrepancies found.
5. Repeat the process as you make changes or additions to your data models.

By incorporating unit testing into your dbt workflow, you can ensure that every piece of your data pipeline functions perfectly, leading to more reliable and accurate data analysis.

With PopSQL, teams can conveniently write and execute unit tests using its user-friendly SQL editor and collaborate effectively. Here's how PopSQL supports unit testing and collaboration:

1. Collaborative SQL Editor: PopSQL provides a collaborative SQL editor that allows multiple team members to work together on writing and executing unit tests. This editor enables real-time collaboration, making it easy for team members to contribute and review each other's code.
2. Query Sharing: [PopSQL allows you to share query links](https://popsql.com/), which means you can share a URL for your unit test query instead of copying and pasting SQL code over chat or email. This makes it convenient for team members to access and review the unit tests.
3. Data Visualization: PopSQL offers data visualization capabilities, allowing you to visualize the results of your unit tests. This can be helpful in quickly identifying any issues or discrepancies in the data models being tested.
4. Integration with Slack: PopSQL integrates with Slack, a popular team communication platform. This integration enables you to push key metrics and updates from your unit tests to Slack, keeping your team members up to date on the testing progress.

Overall, PopSQL provides a user-friendly and collaborative environment for teams to write and execute unit tests. Its features like real-time collaboration, query sharing, data visualization, and Slack integration enhance the efficiency and effectiveness of unit testing in dbt.

## Test-Driven Development (TDD) in dbt: Quality Assurance from the Get-Go
![test driven development in dbt](/static/blog/covers/dbt-testing-9-test-driven-development-in-dbt.jpg)

Test-Driven Development (TDD) in dbt is a development approach that emphasizes quality assurance from the beginning of the development process. Here is a breakdown of this approach:

1. Writing tests before implementing the code: With TDD, developers write tests for the desired behavior of the code before actually writing the code itself. These tests serve as a specification for how the code should function and what the expected outcomes should be.
2. Ensuring transformations meet defined requirements: By writing tests upfront, TDD ensures that the data transformations implemented in dbt meet the defined requirements. This helps catch potential issues early on and ensures that the code produces the desired results.
3. Promoting maintainability: TDD promotes maintainability by encouraging developers to write modular and testable code. By breaking down the code into smaller units and writing tests for each unit, it becomes easier to understand, modify, and extend the codebase in the future.
4. Facilitating future code refactoring: With TDD, developers can refactor the code with confidence. Since there are tests in place that verify the expected behavior, any changes made during refactoring can be validated against these tests to ensure that the code still functions correctly.

Overall, TDD in dbt brings quality assurance to the forefront of the development process. By writing tests before implementing the code, developers can ensure that transformations meet defined requirements, promote maintainability, and facilitate future code refactoring. This approach helps improve the overall quality of the codebase and reduces the likelihood of introducing errors or regressions.

## dbt Test Configurations and Management: Keeping Your Testing on Point
Managing dbt tests is a critical responsibility in an enterprise setting. It involves maintaining the test directory, tracking test results, and understanding the causes of test failures. Errors in data transformation can have significant consequences, making dbt test management an indispensable part of any data engineering strategy. Tools like PopSQL can serve as reliable allies, allowing data teams to write, run, and share SQL scripts for effective test management.

## dbt Test Severity Levels and Prioritization: Making Every Test Count
![severity levels in dbt](/static/blog/covers/dbt-testing-10-severity-levels-in-dbt.jpg)

In the world of data testing, not all tests carry the same weight. Assigning severity levels to dbt tests helps identify which tests have a more significant impact on business operations. By prioritizing critical issues that require immediate attention and resolution, organizations can effectively manage their data quality.

dbt (data build tool) allows for the configuration of test severity levels. These severity levels determine the status of a test based on the number of failures it returns. Typically, a test returns the count of rows that fail the test query, such as duplicate records or null values. If the number of failures is non-zero, the test returns an error by default. However, it is possible to configure tests to return warnings instead of errors or to make the test status conditional on the number of failures returned.

The severity levels in dbt can be customized to align with typical Python log levels, such as INFO, WARN, and ERROR. This allows for semantic clarity and easier understanding of the severity of each test.

Automating severity levels is crucial to efficiently handle data issues. Manually going through all the steps to identify and address data issues can be time-consuming and prone to errors. By automating severity levels, organizations can streamline the process and ensure that critical issues are promptly addressed.

Implementing dbt tests is an effective way to proactively improve data quality.

However, scalability can be a challenge when dealing with a large number of tests. It is important to start with generic tests, leverage helpers and extensions, and model with testing in mind. Testing data sources, integrating alerting systems, establishing clear ownership of tests, and creating testing documentation are also important best practices.

In summary, assigning severity levels to dbt tests allows organizations to prioritize critical issues and effectively manage data quality. By automating severity levels and following best practices, organizations can ensure reliable and accurate data for their operations.

## Monitoring Your dbt Tests: From Reactive to Proactive Data Quality Management
![monitoring your dbt tests](/static/blog/covers/dbt-testing-11-monitoring-your-dbt-tests.jpg)

Data quality management should be a proactive and ongoing effort in an enterprise setting. Dbt enables storing test results directly in the data warehouse, allowing for analysis and monitoring of test performance over time. This approach facilitates a shift from reactive to proactive data quality management, where patterns and trends are understood, and data quality is proactively enhanced.

With this comprehensive guide to dbt testing in SQL for your cloud database, you'll gain a deep understanding of best practices, real-world examples, and the business value that dbt testing brings to data engineering. By mastering dbt testing, you can ensure the integrity, accuracy, and reliability of your data, empowering your organization to make data-driven decisions and drive business growth.

Blog - dbt testing cover

Mastering dbt Testing for Your Cloud Database (Best Practices)

## Introduction
If you've started learning about databases, you may have come across the term "normalization" or "database normalization".

In this article, you'll learn what normalization is, what the benefits are, and how to perform normalization in SQL using some database normalization examples.

## What is Normalization in SQL?
Normalization, also known as database normalization or normalization in SQL, is a process to improve a design of a database to meet specific rules.

When you create database tables and add columns, you're able to create them in almost any way and configuration that you like.

However, to use the benefits of a database and to have an effective design, it helps to follow a certain set of rules. The process of applying these rules is called normalization, and it has several benefits.

The main benefit of normalization is that a piece of information is stored in a single place.

Let's look at an example. Let's say we're designing a database for an eCommerce store that sells shoes.

One of the main items you'll store information about is the name of a product, such as a shoe called "Nike Air Max". On the website for this eCommerce store, you may need to show this in several places:
*   A list of products to browse
*   A person's shopping cart, which is a list of products they have added to a cart for purchasing
*   A customer's order and purchase receipt

There's a potential for this product name to be stored in many places in the database.

However, with a normalized database, the product name of Nike Air Max is only stored in one place. Any other area of the database that needs to know about the product name can refer to this specific product record.

Why is this helpful? There are a few reasons:
*   Making updates to a record is easier because it only needs to be done in one place
*   References to the record can be changed without removing the record
*   It's easier to keep a history of records over time
*   It reduces the risk of data entry errors

## How Does Normalization Impact SQL?
You might be wondering how the outcome of the normalization process impacts your SQL queries.

Normalization will likely result in more tables being created and more relationships between the tables.

A common reaction to this is that it would make writing SQL queries harder, because there are more tables to think about when writing, and slower, because there are more joins used.

However, that's not necessarily the case. Here are some benefits to writing SQL on a normalized database:
*   Less data is considered: with multiple smaller tables, there is less data for the database to look through and consider when returning results of a query, compared to tables with more rows and more columns.
*   Performance can be optimised: there are many ways to increase the performance of queries if there are many tables used
*   One place to make changes: writing Insert, Update, or Delete statements to make changes to data is easier because there's only one place to do it.

## How to Normalize
How do you follow the process of SQL normalization?

There are several stages of the normalization process. The three main stages are:
*   First normal form
*   Second normal form
*   Third normal form

A database is said to be normalized if it meets the rules specified in "third normal form", which is the third stage of the DBMS normalization process.

There are several further stages of SQL normalization, or database normal forms, such as Boyce Codd Normal Form and Fourth Normal Form.

We'll explain each of these stages in this article.

## Our Example
As we work through the normalization in DBMS process in this article, we'll continue our example of an online shoe store.

Let's say we have a single product table with the following columns, and an example record:
![Example record - Normalization in SQL](/static/blog/covers/example-record-of-single-product-table.png)
Here's the visual representation of this design in an Entity Relationship Diagram:
![Visual representation of this design in an Entity Relationship Diagram](/static/blog/covers/visual-representation-in-entity-relationship-diagram.png)
Let's see how we can apply the DBMS normalization process to this design.

## First Normal Form
The first normal form is the first step in the process.

For a database design to be in first normal form, or 1NF, each row needs to have a primary key, and each field needs to contain a single value.

In our example, the Category column can contain several values (Men or Women or Kids), and the Color column contains several values (Black, White, Green, and many others).

We also do not have a primary key in the table.

So, this design does not meet first normal form.

To adhere to the first normal form, we can add a new product ID column to the table, and move the Category and Color values to separate tables.

This means our database looks like this:
![database example 1 - SQL normalization](/static/blog/covers/database-1-sql-normalization.png)

This design now meets first normal form.

## Second Normal Form
For a database to meet second normal form, or 2NF, it must meet the rules of first normal form, and also "each non-key attribute must be functionally dependent on the primary key".

This means that any column that is not the primary key needs to depend on the primary key. If it doesn't, we need to solve that.

Let's check our design.
![database example 1 - SQL normalization](/static/blog/covers/database-1-sql-normalization.png)

Does each column in the product table depend on the product ID?

*   product name: Yes, this is directly related to the product ID.
*   usage: This contains values such as Casual or Running, and it's not dependent on the product ID. It can exist separately to the product.
*   manufacturer: The manufacturer does not depend on the product ID. A manufacturer, such as Nike, can exist independently of this Nike Air Max product.
*   price: Yes, this is specific to this product.
*   size: No, the size can exist separately from the product.
*   description: Yes, this is specific to this product.
*   number in stock: Yes, this is specific to this product.

We have several attributes that are not dependent on the product ID. They should be created as separate tables:
*   usage
*   manufacturer
*   size

We can create the tables in a similar way to the color and category tables.

Our diagram could look like this:
![diagram example 1 - SQL normalization](/static/blog/covers/diagram-1-sql-normalization.png)

Another step we need to take as part of second normal form is relating our tables together. This is done by adding foreign keys to tables, so that they can relate to the correct record.

Our database design currently has data in separate tables, but we still need to know which of the values in each table apply to a product. For example, we need to know the color of our product, even if it's in a separate table.

We do this by adding a foreign key to one of the tables.

The foreign key goes in the table that needs to refer to one record in another table. In our example, a product needs to refer to one color, so we add a foreign key into the product table, and this column will refer to the primary key in the color table. We can call this new column color ID.
![diagram example 2 - database normalization](/static/blog/covers/diagram-2-sql-normalization.png)

We can take the same approach for the other tables. The product needs to relate to the correct usage, size, category, and manufacturer records.

Our design will look like this:
![diagram example 3 - database normalization](/static/blog/covers/diagram-3-sql-normalization.png)

Our design is now in second normal form.

## Third Normal Form
The next step is called third normal form. This is a common place to end the SQL normalization process, because the design allows for efficient SQL queries and addresses the issues of databases that aren't normalised.

A design will meet third normal form if it has no "transitive functional dependency".

A "transitive functional dependency" means that every attribute that is not the primary key is dependent on only the primary key.

For example, in one table:
*   Column A determines Column B
*   Column B determines Column C

This is a "transitive functional dependency" because Column C depends on Column B instead of Column A.

How do we apply this to our design?

For all of our newer smaller tables (usage, size, category, color, and manufacturer), they all meet these criteria. They only have two columns, and the second column (the name) is dependent on the first column (the ID).

Let's look at our product table. For each of our columns, are they dependent on something other than the primary key of product ID?

*   category ID: No, this is only dependent on the product ID.
*   color ID: No, this is only dependent on the product ID
*   usage ID: No
*   size ID: Maybe - we'll look at this shortly.
*   manufacturer ID: No
*   product name: No
*   price: No
*   description: No
*   number in stock: No

What does the size ID column represent? It refers to a size of the product, such as "US Mens 9" from our example.

However, other size values could be "EU Womens 37" or "UK Mens 8". These sizes are actually dependent on whether the product is for Men or Women, so they are dependent on the category.

In our design, one way we can solve this is by removing the category ID from the product table and adding it to the size table.

Here's our updated design:
![diagram example 4 - database normalization](/static/blog/covers/diagram-4-sql-normalization.png)

So, our example Nike Air Max shoe would have a size ID that relates to the size of "US Mens 9", and this size record would have a category ID that relates to the value of "Mens".

Our design now meets third normal form. Because of this, our design is said to be normalized.

If we want to enhance our design, there are some more steps or "database normal forms" that can be applied.

## BCNF
BCNF stands for Boyce-Codd Normal Form, and it's a slightly stricter version of third normal form. It's often referred to as 3.5NF because it's close to third normal form.

A database design will meet BCNF if all redundancy based on functional dependency has been removed. This is best explained with an example.

It's uncommon that you'll find a table that is in third normal form but not in BCNF. We don't see any in our design for products.

Let's say we had a database design for a school, which had some tables for teachers and students, like this:
![database design - SQL normalization](/static/blog/covers/database-design-sql-normalization.png)

All of the columns depend on the ID of the table.

However, we have an address in both tables. The address could be separated into its own table, and related to the teacher and student table.

Our design could look like this:
![database design 2 - SQL normalization](/static/blog/covers/database-design-2-sql-normalization.png)

BCNF is one of the normal forms in SQL that is not used very often, but it's helpful if you want to make improvements to your design to reduce the risk of redundancy.

## Fourth Normal Form
Fourth normal form is the next level of normalization in SQL after BCNF.

Just like with BCNF, this is one of the uncommon normal forms in SQL, but it's still possible that your design can meet third normal form or BCNF but not meet fourth normal form.

The definition of fourth normal form is that "are no non-trivial multivalued dependencies other than a candidate key."

This means that any dependency between columns in the table involves a column that could be the primary key.

Let's see an example of fourth normal form.

We don't have any in our product database, so we'll use a different database normalization example for this.

Let's say we had a customer table.

Here's the design:
![database design 3 - SQL normalization](/static/blog/covers/database-design-3-sql-normalization.png)

And here's some sample data:
```
| id | customer_name | operating_system | location |
|----|---------------|------------------|----------|
| 2  | John          | Windows, macOS   | Chicago  |
| 6  | Michelle      | Windows          | Boston   |
| 8  | Mark          | Linux            | Chicago  |
| 15 | Sarah         | macOS, Windows   | Atlanta  |
```

This table contains two pieces of information about a customer:
*   the operating system that the customer uses, which can be more than one
*   the location the customer is based in, which is one value

This table also shows that these two pieces of information are independent: the operating system is not dependent on or related to the location. Both of the columns are dependent on the individual customer (the ID).

It also shows that there can be multiple values for an operating system, which is an example of a multi-valued dependency.

To move this into fourth normal form, we need to separate this multi-valued dependency into a separate table.

Our design would look like this:
![database design 4 - SQL normalization](/static/blog/covers/database-design-4-sql-normalization.png)

Our sample data would look like this:

**customer**
```
| id | customer_name | location |
|----|---------------|----------|
| 2  | John          | Chicago  |
| 6  | Michelle      | Boston   |
| 8  | Mark          | Chicago  |
| 15 | Sarah         | Atlanta  |
```

**customer\_operating\_system**
```
| id | operating_system |
|----|------------------|
| 2  | Windows          |
| 2  | macOS            |
| 6  | Windows          |
| 8  | Linux            |
| 15 | macOS            |
| 15 | Windows          |
```

The multi-valued dependency has been removed, and this design is now in fourth normal form.

## Conclusion
The SQL normalization process is a great way to improve the design of a database, by removing the risk of poor data quality, redundant data, and missing data. It also remains easy to write SQL queries against the tables to get the data you need.

The database normal forms can be summarised as:
*   First normal form: each table needs a primary key
*   Second normal form: any column that is not the primary key needs to depend on the primary key
*   Third normal form: any column that is not the primary key is only dependent on the primary key (and no other columns)
*   Boyce-Codd normal form: remove functional dependencies
*   Fourth normal form: remove multi-valued dependencies

When you create or modify your database by writing SQL queries, you'll need a tool to do this. PopSQL is a great choice for this, as it helps you and your team work together on SQL and store your scripts in a central location (as well as many other helpful features).

Blog - sql normalization cover

Normalization in SQL DBMS: 1NF, 2NF, 3NF, and BCNF Examples

[dbt](https://docs.getdbt.com) is a tool that allows us to design, implement, and test workflows for the creation and population of data models. It solves common pain points in data model implementation such as lack of standardization, complicated dependency management, and repetitive code. It integrates smoothly with popular cloud data warehouses such as [Google Cloud BigQuery](https://cloud.google.com/bigquery), [Snowflake](https://signup.snowflake.com/), and [AWS Redshift](https://aws.amazon.com/redshift/). Some of its best features include auto-generated documentation, generic and custom tests, and Jinja templating for macros and models. 

The auto-generated documentation is comprehensive and includes a visual representation of the data lineage. Custom and generic tests can also be leveraged to make the QA process much more efficient than traditional tests that are manually written and triggered. Finally, Jinja is a templating script that can be combined with SQL to create reusable functions. It is quite flexible because it is parameter based. It is also able to take environment variables–a useful feature for when engineers need to deploy dbt in containers.

dbt includes several elements such as [sources](https://docs.getdbt.com/docs/build/sources), [seeds](https://docs.getdbt.com/docs/build/seeds), [snapshots](https://docs.getdbt.com/docs/build/snapshots), and [metrics](https://docs.getdbt.com/docs/build/metrics). The most important of them all is the dbt model.

## dbt Models
A [data model](https://www.ibm.com/topics/data-modeling) is an organized design of how the data of entities in a database are related to each other. Some examples of common data model architectures are [Data Vault](https://www.data-vault.co.uk/what-is-data-vault/) and [Data Mesh](https://www.datamesh-architecture.com). A [dbt model](https://docs.getdbt.com/docs/build/models) can be thought of as a blueprint of a table or view that represents entities in a database. It is written using SQL and Jinja. Dependencies and transformations are typically written here. 

Traditional implementation involves a lot of work because stored procedures and DML/DDL statements have to be created for each table, view, and process. That means a lot of repetition. Over time, changes will inevitably need to be applied such as schema or transformation logic changes. These are also difficult to do for large models and systems because engineers have to go over everything again.

## How to build a dbt model
Imagine that a retail company would like to migrate its Point-of-Sales data from its legacy system to the cloud. Your team is tasked with creating a Proof-of-Concept to demonstrate to the client that a data warehouse is possible. You floated the idea of using dbt to implement the data model. The client is reluctant to use a non-traditional tool so they ask for a quick demonstration. You agree, and show them step-by-step how it is done.

First, your data modeler came up with a simple data model:  
![dbt data model](/static/blog/covers/dbtmodel-diagram-1.png)

As the data engineer, you are tasked with using dbt to generate the models based on the above schematic. Here is a summary of the steps to achieve this goal:

1.  Initialize the dbt project
2.  Write model files
3.  Generate documentation
4.  Orchestrate the workflows

A fully completed data model can be thought of as a structure. Therefore, we can think of the elements of the data model as parts of a structure.

### Initializing a dbt project
Before starting, you need to install dbt using `pip` or `homebrew`. Once done, you should also install the necessary [dbt package for your data platform](https://docs.getdbt.com/docs/core/connect-data-platform/about-core-connections) such as Redshift, BigQuery, or Snowflake. Next, you must [set up your profiles.yml](https://docs.getdbt.com/docs/core/connect-data-platform/profiles.yml) by providing pertinent connection details.

For example, if your data warehouse is on BigQuery, here is the series of commands you will need:

1.  `pip install dbt-core`
2.  `pip install dbt-bigquery`

You will know that dbt is installed by running `dbt` and seeing the following output:
![dbt is installed, terminal](/static/blog/covers/dbtmodel-terminal.png)

After which, you can get started on your project by running `dbt init`. It will generate a base template for your project. The base template can be considered the foundation of the “structure” we are trying to build. We can later add in more features and flesh out the structure to come up with the final product.

You will then be asked the following details:
*   Project name
*   Database adapter (BigQuery, Snowflake, Redshift, etc.)
*   Authentication method (OAuth or Service account)
*   Project id (name of the project within the cloud platform)
*   Dataset id
*   Threads (number of concurrent threads)
*   Timeout in seconds (default is 300)
*   Region (US, EU, etc.)

Then, a new folder with the Project name you supplied earlier will be created. It will contain the following directory structure:
![directory structure](/static/blog/covers/dbt-project-name.png)

You will notice that you already have sample files ready to use. For instance, if you peek into the “model” folder, you will see the following:
![dbt model folder](/static/blog/covers/dbt-model-folder.png)

You can immediately use the `dbt run` command on your terminal and it will generate the correct tables, provided your [profiles.yml is configured](https://docs.getdbt.com/docs/core/connect-data-platform/connection-profiles) correctly and the source data is ready. However, these models are not what we want for this use case. Meaning, we will have to write our own model files.

### Writing model files
Models can be thought of as the building blocks needed to build a structure. Without them, the house will not stand. From a technical standpoint, a model file is a SQL `SELECT` statement. However, note that we can use [CTEs](https://dev.mysql.com/doc/refman/8.0/en/with.html#:~:text=A%20common%20table%20expression%20(CTE,that%20statement%2C%20possibly%20multiple%20times)ss) to perform transformations on data. The first thing to do is to define our base tables. Our base tables should source from the raw source data that should also be present in the data warehouse. 

Remember that we are creating a POC for a retail company–so that means that in general, the schema will typically look like this the following. In practice, the schemas will have slight variations depending on the company.

`Customer` source table:
```
| Column name   | Data type |
|---------------|-----------|
| customer_id   | INT64     |
| first_name    | STRING    |
| last_name     | STRING    |
| house_number  | INT64     |
| street        | STRING    |
| city          | STRING    |
| email_address | STRING    |
```

`Item order` source table:
```
| Column name | Data type |
|-------------|-----------|
| item_id     | INT64     |
| quantity    | INT64     |
| price       | FLOAT     |
| description | STRING    |
```

Each source table corresponds to a base table. Meaning, we will have 2 base tables. To write the model files for each, we do the following:

#### Set configuration options
The small details in a structure such as the wallpaper and flooring need to be considered as well. We can think of those small details as the configuration options. Configuration options can be set either within the model file itself or in the [dbt\_project.yml file](https://docs.getdbt.com/reference/dbt_project.yml). This file contains various options to do the following: set paths, define start and end behavior, define project variables, and more. To see a list of all available options, please see this [link](https://docs.getdbt.com/reference/dbt_project.yml). 

In the context of a model, we can configure the following: 
*   Materialization – whether the model is a [materialized view](https://materialize.com/guides/materialized-views/), [incremental model,](https://www.javatpoint.com/software-engineering-incremental-model) or a [table](https://www.ibm.com/docs/en/db2-for-zos/11?topic=relationships-physical-database-design). 
*   Schema - A schema in dbt refers to the fully qualified path of the model in the data warehouse. The path usually consists of a dataset name and table name.
*   Tags - These can be used to organize dbt workflows. For example, models can be tagged with “staging”, “dev”, or “prod” and you can trigger dbt to run only for certain tags.

Your schema configuration should be placed at the top of the model file like so:
```plaintext
{{ config(
   materialized="view",
   schema="staging"
) }}
```

#### Define the SQL statement
Let’s start with customers.sql by defining the source data as follows. It’s as simple as selecting all the columns.
```plaintext
WITH source AS (
   SELECT
       customer_id,
       first_name,
       last_name,
       house_number,
       street,
       city,
       email_address
   FROM source_table
)
```

Notice that the columns related to name and address are separated in the source table. We will apply basic transforms to consolidate this data. We simply CONCAT the first\_name and last\_name columns to form the name column. We do the same with house\_number, street, and city to produce the address column.
```plaintext
, transformed AS (
   SELECT
       customer_id,
       CONCAT(first_name, ' ', last_name) as name,
       CONCAT(CAST(house_number AS STRING), ' ', street, city) AS address,
       email_address
   FROM source
)
SELECT
   *
FROM transformed
```

And that’s it! We have completed `customers.sql`.

We follow the same process for `item_orders.sql`:
```plaintext
{{ config(
   materialized="table",
   schema="staging"
) }}
WITH source AS (
   SELECT
       item_id,
       quantity,
       price,
       description
   FROM source_table
)
SELECT
   *
FROM source
```
Note that this is a lot simpler because we do not need to apply any transformations.

### Referencing models
Not all models are base models the same way not all building blocks stand alone. In practice, we want to create models that are derivations of other models the same way we expect to connect blocks together to form a structure. 

We can do that by using the following syntax:
```plaintext
{{ ref("name of model") }}
```

Another way to define models and variables, in general, is to use environment variables. dbt is able to parse these with the following syntax:
```plaintext
"{{ env_var('DBT_MY_VAR') }}"
```

Our goal is to create the `order` model – and it is derived from both `customers` and `item_orders`. To do that, we first create the CTEs to pull data from both base tables. Then, we do an `INNER JOIN` on `customer_id` and produce `total_price` by multiplying `quantity` and `price`.

Then, we want to aggregate on total\_price to come up with the total for each order. Finally, we apply the MD5 hash function on `customer_id`, `total_price`, and `order_date` to produce a unique `order_id`.
```
{{ config(
   materialized="view",
   schema="staging"
) }}
WITH customers AS (
   SELECT
       *
   FROM {{ ref("customers") }}
), item_orders AS (
   SELECT
       *
   FROM {{ ref("item_orders") }}
), individual_orders AS (
   SELECT
       c.customer_id as customer_id,
       i.item_id as item_id,
       CURRENT_TIMESTAMP() as order_date,
       c.address as shipping_address,
       SAFE_MULTIPLY(i.quantity, i.price) as total_price,
   FROM customers c
   INNER JOIN item_orders i
   ON c.customer_id = i.customer_id
), aggregated_orders AS (
   SELECT
       customer_id,
       order_date,
       shipping_address
       SUM(total_price) AS total_price,
   FROM individual_orders
   GROUP BY customer_id, order_date, shipping_address
), final_orders AS (
   SELECT
       MD5(customer_id, order_date, total_price) as order_id,
       customer_id,
       order_date,
       shipping_address,
       total_price
   FROM aggregated_orders
)
SELECT
   *
FROM final_orders
```
By now you might have noticed the unique syntax for references and configurations. The double brackets `{{ … }}` are actually a form of Jinja scripting. Using Jinja, we have the freedom to define macros. Macros can be thought of as reusable functions.

## dbt model documentation
All structures have blueprints that contractors can refer to when they want to make additions, repairs, or alterations. This is similar to how all codebases have documentation to help present and future developers understand the ecosystem. Without documentation, developers will have to figure out things that have already been figured out before–making everything much more inefficient.

The command to generate documentation is `dbt docs generate`. It will create html files and place them in the `target` folder. Then, use `dbt serve` to start serving your documentation website locally on port 8080.

The documentation dashboard looks like this:
![dbt documentation dashboard](/static/blog/covers/dbtmodel-dbt-docs.png)

It contains a visual representation of the data lineage and pertinent details for all models. It is useful for keeping on top of all changes being made by various subteams on the codebase.

## Orchestration
Running `dbt` locally is easy enough but eventually, we will want to take our work to production. In production, we can’t necessarily run everything manually so we will have to find ways to automate our workflows. To do that, we have several options:

1.  Using [Apache Airflow](https://docs.getdbt.com/guides/orchestration/airflow-and-dbt-cloud/1-airflow-and-dbt-cloud)
2.  [CI/CD](https://docs.getdbt.com/guides/orchestration/custom-cicd-pipelines/1-cicd-background)
3.  [Webhooks](https://docs.getdbt.com/guides/orchestration/webhooks) on dbt Cloud
4.  [Databricks](https://docs.getdbt.com/guides/orchestration/how-to-use-databricks-workflows-to-run-dbt-cloud-jobs)

Out of all these options, the most commonly used option is CI/CD. This is because CI/CD pipelines are highly customizable [DAGs](https://hazelcast.com/glossary/directed-acyclic-graph/). Imagine that you have a 20-man team and there are PR’s being opened left and right. It is very likely that the tech leads are spending all their time reviewing PRs instead of doing other meaningful work.

CI/CD pipelines can help speed up the process by running lint and unit tests automatically to tick off all the essential checks. By the time the PR falls onto the plate of a tech lead, they can skip the basic checks and go straight to optimization pointers.

A pipeline diagram looks like this:
![a dbt pipeline diagram](/static/blog/covers/dbtmodel-diagram-2.png)

So, a sample pipeline run with `dbt` could look like this:
![a sample pipeline run with dbt](/static/blog/covers/dbtmodel-flow.png)

## Conclusion
dbt is a tool that allows us to design, implement, and test data model workflows. One of its building blocks is the model. A dbt model is a representation of a table or view in the data model. To write a model, we use a SQL SELECT statement. Here, we can apply use CTEs (Common Table Expressions) and apply transforms using SQL. Additionally, we can document and test our models using the `dbt docs generate`, `dbt docs serve`, and `dbt test` commands.

Blog - dbt model cover

How to Write a dbt Model in SQL: Examples & Best Practices

The modern data stack is a collection of tools and technologies that help companies collect, store, process, and analyze data in a scalable, efficient, cost-effective, and composable way. This article will define what a modern data stack is, explore the benefits of migrating to this data tech stack, highlight how it differs from the legacy data stack, discuss implementation concerns/recommendations, and describe how PopSQL fits into this stack. Whether you're a solo data scientist, a small startup, or a large enterprise, the modern data stack will make all parts of your work with data much easier.



## What is the modern data stack?

Defining the Modern Data Stack can be challenging due to the wide range of tools and technologies it encompasses. Each company and developer would have their interpretation of what should or shouldn't be included in this data tech stack. Nevertheless, there are several common technology areas or buckets that every stack will typically have, and the Modern Data Stack can be roughly divided into four big parts.

1. Ingestion and Collection: As mentioned earlier, data exists in various forms, such as local files (e.g., CSVs), online sources, data warehouses, cloud tools, and relational database tables. Ingestion and collection involve the process of gathering, connecting to, and importing this data. Popular tools for ingestion include Fivetran, Stitch, and Matillion.
2. Data Warehousing: Data warehouses serve as a centralized repository for all the data that have been ingested in the previous step. Cloud data warehouses offer advantages (over legacy databases) such as scalability, performance, and security. Popular data warehousing platforms include Snowflake, BigQuery, Redshift, and Azure Data Warehouse. Many of these platforms have a variety of Machine Learning and Analysis functions built into the database directly.
3. Data Transformation: This part of the stack involves cleaning, transforming, and enriching the data to make it suitable for analysis. Data transformation tools like DBT and Matillion are commonly used for this purpose.
4. Analytics + Visualization + Data Collaboration: This step focuses on analyzing the data and presenting it in a user-friendly way. For many people, this is often the most visually appealing and exciting part of working with data. Popular Analytics, Visualization, and Data Collaboration tools include PopSQL, Tableau, Plotly, Looker, and Power BI.
5. Data Activation: Once data has been collected, stored, transformed, and analyzed, the final step in the modern data stack is data activation. Data activation is the process of using data insights to drive action and make informed decisions. 

Now that we understand the components of the modern data stack, let’s explore how it compares with a more legacy or traditional data stack.

## How is the Modern Data Stack Different from the Legacy Data Stack?

To better understand the Modern Data Stack, it's useful to understand the traditional or legacy data stacks that existed before. The legacy data stack involved relational databases, on-site data warehouses, ETL tools, BI tools, data integration middleware, data governance tools, statistical analysis tools, and custom-made solutions.

The pros of the legacy data stack were its maturity and range of capabilities for data management and analysis. However, it had cons too, like being expensive, inflexible, and difficult to scale up. Non-technical users often had to seek IT assistance to work with the data tools. The Modern Data Stack was created to overcome these limitations. It relies on cloud-based solutions, open-source technologies, and more flexible and scalable tools.

Here are some key differences between the legacy data stack and the Modern Data Engineering Stack:

1. Cloud-based vs. On-premises: The Modern Data Stack uses cloud-based tools, making it ideal for businesses with remote employees that need access from multiple locations. The legacy data stack is typically hosted on-premises, making data access more challenging. Many companies want to focus on their core competency and not have to worry about managing on-prem servers and databases.
2. Open Source vs. Proprietary: The Modern Data Stack leverages open-source technologies which are often freely available for use and modification (with an MIT license). The legacy data stack relies on proprietary technologies, limiting customization and increasing the risk of vendor lock-in.
3. Scalable vs. Inflexible: The Modern Data Stack is designed for scalability, easily expanding to accommodate growing business needs. A company can easily host everything from a few GBs of data up to many Petabytes. The Cloud software can easily be scaled up or down according to storage and computing needs. The legacy data stack is often inflexible and difficult to scale up, hindering businesses from keeping up with data volumes.
4. Cost-effective vs. Expensive: The Modern Data Stack is cost-effective, saving on data storage and processing costs. The legacy data stack can be expensive to set up and maintain.
5. Democratized Access vs. IT-Based Access: The Modern Data Stack allows broader stakeholders (e.g. non-technical users like marketers) to transform, analyze, and visualize data. The legacy data stack often required technical expertise or IT assistance to be able to work with the data.

Many of the differences noted above are also the core benefits of working with the modern data stack. The next section will expand on these benefits more specifically.

## Benefits of the modern data stack 

Let's delve into the key benefits that businesses can enjoy when embracing the modern data stack.

1. Enhanced Data Accessibility: User-friendly tools like PopSQL, Tableau, and Power BI democratize access to data. Marketers, product managers, and executives can analyze data without relying on technical experts or IT support, enabling faster decision-making.
2. Scalability and Flexibility: Cloud data warehouses like Snowflake, BigQuery, Redshift, and Azure Data Warehouse provide elastic storage and computing resources that can scale based on data volume. This ensures businesses can handle more data without performance issues or high infrastructure costs.
3. Cost-Effectiveness: The modern data stack eliminates upfront investments in hardware and infrastructure. Cloud providers offer pay-as-you-go pricing, allowing businesses to optimize costs. Tools like dbt and Matillion improve efficiency and reduce development time.
4. Improved Data Quality: Tools like dbt maintain data quality and consistency through best practices like testing and version control. This ensures reliable insights for informed decision-making.
5. Advanced Analytics: The modern data stack integrates machine learning and analysis functions, enabling advanced analytics without additional tools. Businesses can perform complex calculations and generate real-time insights for rapid response to market trends.

With all of these benefits, there surely must be some challenges or potential pitfalls of migrating to the MDS, right? The next section will explore these in depth.

## What are the challenges in transitioning to the Modern Data Stack?

Transitioning to the Modern Data Stack poses challenges that companies need to address to ensure a smooth switch. Here are some common issues to consider:

1. Data Complexity and Quality: Migrating complex and diverse data from legacy systems requires careful planning and effective data cleaning strategies. This involves dealing with data quality issues like missing values, duplicates, and inaccuracies.
2. Integration and Interoperability: Achieving seamless interoperability between existing infrastructure and new components may require custom tools or third-party software. 
3. Skill and Knowledge Gap: Shifting to a modern data stack may require additional training and support to bridge skill gaps within the organization.
4. Change Management: Overcoming resistance to adopting new tools and processes is crucial. Effective change management strategies involve clear communication, stakeholder involvement, and ensuring employees can adapt to the new stack.
5. Data Security and Compliance: Implementing strong access controls, encryption mechanisms, and data governance policies is vital for data security and compliance. It safeguards sensitive data from unauthorized access and ensures adherence to regulations.
6. Cost and Resource Allocation: Careful budgeting and resource allocation are necessary for upfront investments and ongoing costs associated with the modern data stack. This includes hardware, software, training, and support expenses.
7. Legacy System Decommissioning: Proper planning is essential to retire legacy systems without disrupting data access and business operations. This involves migrating data to the new stack and decommissioning legacy systems securely and in compliance with regulations.

By addressing these challenges through a structured approach and collaboration with external experts, organizations can successfully transition to a modern data stack and reap its benefits.

## What Makes a Tool "Modern" in the Data Stack?

Modern data stack technologies have distinct characteristics that set them apart. Here's what makes a tool modern:

1. Easy to Try and Deploy: Modern tools prioritize usability and accessibility. They offer free trials or open-source options, allowing users to evaluate them before committing.
2. Massive Scalability: Modern tools handle large data volumes, users, and complex use cases. They scale dynamically in the cloud, effortlessly managing high concurrency and big data without compromising performance or reliability.
3. Composable: Modern tools fit into a modular data stack. They integrate easily with other tools, enabling organizations to build custom data ecosystems that suit their needs.
4. Cloud-Native Capabilities: Modern tools leverage cloud technology for enhanced performance and flexibility. They use containerization, microservices, and serverless computing to achieve efficient resource utilization and easy management.
5. Automation and Intelligence: Modern tools automate tasks and provide intelligent insights. They utilize machine learning and AI to streamline workflows, optimize data pipelines, and free users from manual work.
6. Integration within the Data Ecosystem: Modern tools seamlessly integrate with other systems and tools. They offer APIs, connectors, and pre-built integrations to facilitate data exchange and collaboration.

In summary, modern tools in the data stack are characterized by their ease of use, scalability, modularity, cloud-native capabilities, automation, and integration. These features enable organizations to leverage data effectively, drive innovation, and achieve their data-driven goals.

## Data Activation: Turning Data into Actionable Insights

Once data is collected, stored, transformed, and analyzed using the modern data stack, the next important step is data activation. Data activation involves using data insights to drive action and make informed decisions. It bridges the gap between analyzing data and actually using it to improve business operations and achieve goals. Let's explore the concept of data activation and its significance in the modern data stack framework.

Data activation goes beyond simply looking at data or creating reports. It focuses on using data insights to guide decision-making and drive positive outcomes. It involves the following steps:

1. Identify Key Insights: By analyzing data, organizations uncover patterns, trends, and connections within the data. These insights provide valuable knowledge about customer behavior, market dynamics, operational inefficiencies, and other important aspects of the business.
2. Define Objectives: Based on the insights gained, organizations set clear goals and objectives that align with their strategic priorities. These objectives could be related to improving marketing campaigns, enhancing customer experience, optimizing operations, or launching new products.
3. Develop Actionable Strategies: With the objectives in mind, organizations formulate strategies and action plans to address the identified insights and achieve desired outcomes. These strategies might involve changes in marketing tactics, operational processes, product offerings, or customer engagement approaches.
4. Implement and Monitor: The formulated strategies are put into action, and their implementation is closely monitored. Organizations track key performance indicators (KPIs) to measure the effectiveness of the strategies and make adjustments as needed.
5. Iterate and Improve: Data activation is an iterative process. Organizations continuously analyze the impact of their strategies, gather new data, and refine their approaches based on evolving insights. This iterative approach allows organizations to adapt and improve in a dynamic business environment.

### Data Activation is crucial in the modern data stack, offering these benefits:

1. Informed Decision Making: It helps organizations make informed decisions by extracting valuable insights from large data sets.
2. Personalized Customer Experiences: Organizations gain a deep understanding of customer preferences, enabling them to deliver personalized experiences and targeted campaigns.
3. Improved Operational Efficiency: Data activation identifies inefficiencies, streamlining processes and reducing costs.
4. Competitive Advantage: Effective data activation drives innovation, strategy optimization, and adaptation to stay ahead of competitors.
5. Real-Time Responsiveness: The modern data stack enables timely decision-making and agile responses to market trends and customer demands.
6. Continuous Improvement: Data activation fosters ongoing optimization through data analysis and strategy iteration.

In summary, Data Activation unlocks data value, informs decisions, and drives positive outcomes. It improves customer experiences, operational efficiency, and helps a company gain a competitive advantage.

## Build a modern data stack to enhance your business 

Many tools within this data tech stack work with tabular data and SQL. SQL offers consistent syntax and commands that work across different database systems, even if there are slight variations in the specific SQL dialects.

However, data collaboration can be a bit messy. People often end up rewriting each other's queries, switching between multiple apps to access their data, and copying and pasting queries into messaging platforms like Slack or Github Gists.

That's where PopSQL comes in. It provides a user-friendly and visually appealing way to write SQL queries, visualize data, and share the results. With PopSQL, you can collaborate with colleagues on queries, saving time and working together seamlessly. You can easily share a query’s results or entire queries with others, making it simple to discuss findings and gather input from your teammates.

PopSQL is compatible with major cloud data warehouses and many common databases, allowing you to continue using your favorite tools and work with your preferred data sources without any hassle.

## Final Thoughts

The Modern Data Stack offers a powerful solution for storing, transforming, managing, and analyzing data effectively. By embracing the modern data stack architecture, organizations (and solo devs) can enjoy the advantages of cloud-based, scalable, cost-effective, and democratized access to data. 

Implementing a Modern Data Engineering Stack may present challenges related to data complexity and quality, integration and interoperability, skill and knowledge gaps, and change management. 

By carefully preparing, providing training, and offering support, companies can overcome these challenges and fully utilize the power of the Modern Data Stack. This allows them to make informed decisions based on data and stay ahead of the competition in today's data-centric business environment.

An Exploration of the Modern Data Stack

We want to make PopSQL the best way for data analysts to work with BigQuery data. Many of our users have already connected their BigQuery instances because PopSQL makes it easier to collaborate in real-time and stay aligned. Add to that built-in features such as folders, autosave, dark mode and tab support, and it’s easy to see why they get more done.

That’s why we’re happy to share a few enhancements to the BigQuery + PopSQL experience, including a new built-in cost estimator tool, support for nested columns across schema and result views, and new project filtering capabilities to speed up data discovery.

## See how much a query costs before running it

With this new enhancement, as soon as you start writing a query you’ll see a cost estimate next to the Run button based on how many bytes of data your query will process. Estimates are based on a [standard on-demand pricing](https://cloud.google.com/bigquery/pricing#on_demand_pricing) of $5.00 per TB. 

![Cost](/static/images/blog/bigquery-cost.png)

This new feature has been enabled for all customers on our Business and Enterprise plans.

## Easily dive into nested and repeated column data in one click

We now support RECORD columns in [BigQuery IDE](/connections/bigquery-client) to make it easy to examine nested and repeated column data.

In the schema view, you'll see both the main RECORD column like "addresses" in the pictured example and the nested attributes (e.g. "status", “current”, "address", etc).

When you query the data, in the results view, you'll see a summary of the data in the column and from there you can expand to pull up a table of the records.

![Arrays](/static/images/blog/bigquery-arrays.png)

## Focus on what matters, faster, with project filtering

![Filters](/static/images/blog/bigquery-filters.png)

We know that some customers deal with dozens if not hundreds of projects at a time. The result? Connecting to BigQuery and refreshing your schemas can sometimes take a while. What if you could zero in on just the projects you need? Our new project filtering does just that, letting you focus on the data you care about as well as giving you faster load times.

To configure this, go to Preferences > Connections > pick your connection > Advanced > Filter projects.

We hope you enjoy these new enhancements to the overall PopSQL + BigQuery experience. Happy (big) querying!

Blog - big query cover

New ways to focus, work faster and more efficiently with BigQuery using PopSQL

Connecting you to the data insights that power your business is nothing if we can’t help you keep that data secure. At PopSQL, we believe that our commitment to security is a commitment to you. That’s why we’re proud to announce that, as of this month, PopSQL has __achieved its SOC 2 Type II certification__.

## What is SOC 2 Type II compliance?

A SOC 2 Type II report is an audit of how a company’s internal controls safeguard customer data, and how well those controls are operating. With the proliferation of cloud services, businesses around the world rely on SOC 2 reports to assess and address the risks associated with 3rd party technology services and their providers. Completed by independent auditors, these reports cover several trust service criteria categories, [as denoted by the AICPA](https://us.aicpa.org/content/dam/aicpa/interestareas/frc/assuranceadvisoryservices/downloadabledocuments/trust-services-criteria.pdf), relevant to Security, Availability, Processing Integrity, Confidentiality and Privacy.

## Why SOC 2 certification matters

Our SOC 2 Type II audit provides not only an independent assessment of PopSQL’s security control environment, but also full transparency around describing our controls, the tests performed to assess them, the results of those tests, and an overall opinion on our controls’ design and operational effectiveness.

We’re proud to achieve this milestone, as well as of the dedication of our team around the processes, systems and operational rigor to maintain these controls over time. Because it’s not enough to make analyzing your data in PopSQL awesome, we also have to help you keep that data safe.

To learn more about PopSQL’s security posture, see our [Security page](https://popsql.com/security). And to request a copy of our  SOC 2 Type II report, please email us at <a href="mailto:security@popsql.com">security@popsql.com</a>.


Blog - soc2 cover

PopSQL is now SOC 2 Type II Certified

We're excited to welcome [Databricks](http://databricks.com) to our collection of cloud database connection partners.

Databricks is an industry-leading, cloud-based data lakehouse platform used for processing and transforming massive quantities of data which organizations can then explore through notebooks, analyze with SQL, or develop into machine learning models.

Created by the original creators of Apache Spark™ and Delta Lake, which made having a unified framework for building data pipelines for businesses easy. Databricks pushes that innovation further by providing a cloud platform to help create and easily manage your clusters and explore your business data. As part of their platform, they offer interactive workspaces that expose Spark's native R, Scala, Python, and SQL interfaces.

“The new integration between Databricks and PopSQL makes it that much easier and collaborative for our joint customers across financial services, healthcare and retail to dynamically query, analyze and understand their data sources in Databricks.” says Bilal Aslam, Director of Product, Databricks.

With the addition of [Databricks client](/connections/databricks-client) to our growing list of [cloud connections](https://popsql.com/connections), we’re continuing our push to make PopSQL your single platform to take stock of the data that power your business, as well as make collaboration between your technical and business teams a cinch.

So go ahead, connect PopSQL to your Databricks instance today, and let us know what you think.

![Quick look at Databricks](/static/blog/gifs/databricks.gif)

For more information, check out our docs on how to connect to Databricks in PopSQL [here](https://popsql.com/docs/connection-guides/connecting-to-databricks).

Blog - databricks cover

Announcing our Integration with Databricks

I'm excited to announce PopSQL's latest round of funding, a $14M Series A round led by [Tiger Global](https://www.tigerglobal.com/), with participation from [Gradient Ventures](https://www.gradient.com/) (Google's AI-focused venture fund), [Y Combinator](https://www.ycombinator.com/), and [FundersClub](https://fundersclub.com/).

This funding will allow us to accelerate our mission to help modern data teams turn business insights into action more efficiently, quickly, and accurately.

As part of this milestone, we are also excited to reflect on our momentum as a company. Since we announced our seed funding in September 2020, we have more than doubled our team and 5x'd our revenue. Now, over 2,000 companies use PopSQL globally – including household names such as Vroom, Redfin, Shipt, Udacity, Ramp, and doodle.

## Why SQL collaboration is broken

Back in 2017, while working at Instacart, I was heavily reliant on querying data to answer questions from stakeholders and to support any business case I had. At the time, legacy SQL editors had the basic functionality of running the query, but didn't help with the next tasks in my workflow: sharing, organizing, collaborating, and aligning on findings as a cross functional team.

Because everyone used their own SQL editor, we had to drop static SQL files over Slack to share our work, export results into Sheets to get charts, jump into data meetings only to find that another peer had already spent hours writing a similar query with different results, and end up storing hundreds of foundational .sql files on our desktops, which were unsearchable by anyone else. And when anyone on the team left, the company lost years worth of queries.

So, what's the impact? Gartner has [reported](https://www.gartner.com/smarterwithgartner/avoid-5-pitfalls-when-building-data-and-analytics-teams) that "through 2023, data scientists and analysts will lose 60% to 70% of their productive time to activities like finding, preparing, integrating and sharing datasets." If you're a VP of Analytics leading a 50-person team, that means you're operating more like a team of 15-20. Not great. What makes this even more costly is that this challenge is ubiquitous across all industries and departments.

Additionally, today's explosion of solutions in the data space – from ETL, warehousing, CDPs, data modeling, and BI tools – has led to an exponential increase in data volume, velocity, and variety. Coupled with remote and hybrid work environments, teams are even more isolated from each other, making it increasingly difficult to find data, understand it, and get answers.

## Bringing teams together with SQL

Many businesses have recognized that realtime collaboration has become critically important to help teams move faster and more cohesively. Designers have Figma, project managers have Notion, and engineers have GitHub. Data focused teams now have PopSQL.

![Screenshot of PopSQL product](/static/blog/series-a/product.png 'Collaborative SQL editor for teams')

With PopSQL, users can easily understand their data model, write version controlled SQL, collaborate with live presence, visualize data, share results, and organize foundational queries for search and discovery. Even if your team is already leveraging a BI tool or a variety of SQL editors, PopSQL enables seamless collaboration between your SQL power users, junior analysts, and even your less technical stakeholders who are hungry for data insights.

Given the gaps in the market, we have also recently added solutions to deliver additional business value for customers, including:

- [Data catalog](http://www.popsql.com/data-catalog) – onboard new hires faster, while keeping definitions at your fingertips
- [SQL notebooks (beta)](http://popsql.com/notebooks) – tell better data stories with the flexibility of cross database joins by referencing query results in subsequent queries
- [dbt integration (beta)](http://www.popsql.com/dbt) – reduce the amount of time required to model data and make dbt more accessible to stakeholders less experienced with git
- [Enterprise-grade security](https://popsql.com/security) – deploy PopSQL within your own VPC in less than 5 minutes with granular access controls, permissioning, SSO, audit logs, and approval workflows

## What's Next at PopSQL

At PopSQL, we believe your SQL editor should do more than just query and deliver results from your database. During this next chapter of growth, we are excited to help our customers continue to harness the power of their data.

Given our aspirations and the work ahead, we are investing heavily in our team. We're currently a tight-knit group of 12 and [hiring a ton](http://popsql.com/jobs)! This is just the beginning and an exciting time for us, so we are looking for a diverse group of people to join us on this journey to provide delightful experiences for our customers.

![Team photo during our retreat](/static/about/group/1.jpg 'Team photo during our retreat')

It's time to expect more from your SQL editor. We are just at the start of what's possible.

<em>If you'd like to learn more about how PopSQL can work with your organization, please email us at [hi@popsql.com](mailto:hi@popsql.com).</em>

Blog - announcement cover

PopSQL Raises $14M Series A from Tiger Global to Help Data Teams Collaborate

Importing your query results data into spreadsheets can be a tedious process that involves a lot of manual upkeep and file management. PopSQL's new CSV links allow enterprise customers to pull data directly from PopSQL into their spreadsheet programs like Google Sheets.

Using the Google Sheets `IMPORTDATA` function and a query's CSV link, Sheets will fetch your query results, import them into the spreadsheet, and refresh the results up to every hour. If you reference the imported results in formulas and charts, they will also update automatically as the results are refreshed. For more instructions on using this feature with Google Sheets, check out our [docs on CSV links](/docs/getting-more-out-of-popsql/csv-links).

![Google Sheets IMPORTDATA](/static/blog/gifs/20211208_google_sheets_import_data.gif)

Blog - csv links cover

CSV Links and Google Sheets Export

We're happy to announce that we've updated our connection management views. To access
it, you can hit `Manage Connections` in any connection chooser, or open
`Preferences > Connections`. As part of this new view, we have simplified some of the options and
improved wording to help you understand when you have a
[direct connection vs a cloud connection](/docs/getting-more-out-of-popsql/cloud-direct-connections),
finer grained control of [sharing a connection](/docs/popsql-for-teams/shared-connections), helpful
documentation links, as well as exposing enterprise features we offer for our various connectors.
As an added bonus, we now support private cloud connections.

Additionally, as we aim to make PopSQL the best [SQL editor](/product/sql-editor) out there, we're proud to offer support
for [ClickHouse](/connections/clickhouse-client) for direct connections. Check our docs for more on how to connect to
[ClickHouse](/docs/getting-started/connecting-to-clickhouse).

Blog - connections v3 and clickhouse cover

New Connections Management Views and Integration with ClickHouse

We've launched a number of updates of our dbt™ Core integration, lifting a number of our [limitations](/docs/getting-more-out-of-popsql/dbt#known-limitations) when we started the beta.

## Branching

One of the most requested features was the ability to handle branching. Now, each user can work in whatever branch they want within your repository, with
any change they make automatically synced back up to your repository. As part of this, we now also fully support all file operations, including creating, renaming, moving, and deleting files and folders within your file structure. Additionally, you can now set custom commit messages when you save a file.

## Syncing dbt Documentation with Data Catalog

We now support a sync of your dbt documentation with your organization's [Data Catalog](/data-catalog). See the documentation you've written for your models show up in PopSQL and be available within autocomplete and the schema browser. Please contact us if you would like this feature enabled.

## Specify dbt run args

When running a [dbt model](/blog/dbt-models), you can now specify arguments to be passed to the dbt instance when we got to execute the model. These arguments are the same as you would expect to pass if you were using the [dbt run cli command](https://docs.getdbt.com/reference/commands/run).

Blog - dbt updates cover

dbt Updates: Branching, Data Catalog, Dbt Run Args

We've added the ability to use conditional formatting and persistent filters to our data tables. Both of these features
apply anywhere a table is shown, with persistence aimed at tables shown within dashboards.

## Conditional Formatting

Conditional formatting helps you draw attention to certain values in your table. For example, if you have a chart of
net promotor scores, you can use this to color promoters green, detractors red, and passives as orange.

<script src="https://fast.wistia.com/embed/medias/bbzrhpocvc.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:80.83% 0 0 0;position:relative;margin-bottom: 40px;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_bbzrhpocvc videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/bbzrhpocvc/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

## Persistent Column Filters

Now when you use [filters](/docs/getting-started/query-results#results-table) on dashboard tables,
those filters are saved for subsequent users. For example, if your query returns 1000 rows, but you only want to show
rows where `lead_score > 0`, you can set that filter and it'll persist when the next user visits the dashboard.

Blog - conditional formatting cover

Conditional Formatting and Persistent Table Filters

Currently when you share a connection, you only have the option to make it private to you, or shared with your whole organization.

Now our enterprise customers can specify exactly which people or teams can access a connection, rather than the whole organization. For example, `Marketing` can access `Data Warehouse`, and `Engineering` can access `Data Warehouse` **and** `Production Replica`.

![](/static/images/shared_connections/granular_permissions.gif)

Other recent improvements related to connections:
* We overhauled the UI for connections
* You can create cloud connections that are private to you

Blog - Granular Permissions for Connections cover

Granular Permissions for Connections

Since PopSQL introduced charting, we have provided a preset palette to use for
the colors of your series. However, many people have asked to be able to provide
their own colors so they can make the chart best represent their data.

We have now introduced the ability to set your own colors for charts, enabled across
all types. For bar, combo, line, and scatter charts, when selecting columns for the
Y-Axis, you will now see a color picker next to the column select. If you use the
"Group By" functionality or pie charts, there will be a new section on the settings
sidebar that gives a list of available series and the ability to select specific colors.

For big number charts, you may set specific colors to use for the chart by setting up some
conditions under the chart formatting menu. You may utilize a range of different numerical
operators over several different conditions to show the big number in the color that fits
your needs.

Please see our [docs](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data)
for more information on using charting in PopSQL.

Blog - Specifying your own colors for charts cover

Specifying your own colors for charts

The [dbt](https://getdbt.com)™ platform has become an important part of the data team's workflow. It was recently voted as the [most popular open source project in data](https://twitter.com/sspaeti/status/1379671780525273090).

For data teams that are using the dbt platform, there is an inefficient workflow between writing [dbt models](/blog/dbt-models) in your code editor (eg VS Code), using your terminal to compile the model, and then copying and pasting the compiled code into your SQL editor to test things out. With PopSQL's new dbt Core™ integration, **now you can write dbt code right in your [SQL editor](/product/sql-editor)**.

<img class="rounded-lg" alt="workflow" src="/static/blog/images/dbt_workflow.png" />

For data teams not using the dbt platform yet, our integration is an easy way to get started. If you have SQL that you're often copying/pasting between queries, save that as a reusable snippet/macro and **eliminate repetitive SQL**.

<img class="rounded-lg" alt="macro" src="/static/blog/images/dbt_macro.png" />

Or if you have long SQL queries with multiple CTEs, you can break that down into a dbt model and save it in your database as a view or table to **simplify your BI queries**.

<img class="rounded-lg" alt="model" src="/static/blog/images/dbt_model.png" />

Next, we plan on showing your dbt documentation inside of PopSQL. So if you're writing table/column descriptions in your dbt source files, **we'll show those descriptions in autocomplete as you're writing SQL**, and in our schema browser.

<img class="rounded-lg" alt="dbt docs" src="/static/blog/images/dbt_docs.png" />

Our [dbt Core integration](/dbt) is currently in beta and we're gradually rolling it out. If you're interested in trying it, please [sign up for the beta](https://n811kmuga5v.typeform.com/to/lCRSZYeC).

<script src="https://fast.wistia.com/embed/medias/p98ekaexiu.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_p98ekaexiu videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/p98ekaexiu/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

More information:

- [popsql.com/docs/dbt](/docs/dbt) for documentation
- [popsql.com/dbt](/dbt) to learn more about the benefits
- [sign up for the beta](https://n811kmuga5v.typeform.com/to/lCRSZYeC?utm_source=updates)

Announcing PopSQL's dbt Core Integration

We recently added more control over how you share PopSQL dashboards. Bigger teams now have more granularity over who can edit or see their dashboards, making it easier to work together.

You can now search for one of your teams and give it edit or view access, or just choose a specific person within your company to share your dashboard with. See our [docs](/docs/getting-more-out-of-popsql/dashboards#sharing-a-dashboard) for the full information.

This feature is now available for our Enterprise customers, feel free to contact us if you want to enable this for your team.

![](/static/images/dashboards/granular-permissions.gif)

Blog - Granular Permissions for Dashboards cover

Granular Permissions for Dashboards

It is now possible to import spreadsheets from Google Sheets directly into your database using PopSQL!

This gives you the ability to query data from your spreadsheets using SQL, while also leveraging the full power of your database to enable joins across your other tables.

This feature is available now for Enterprise users on Athena database connections. Talk to us if you'd like to enable this on your plan or if you require support for other databases!

Please see our [docs](/docs/getting-more-out-of-popsql/sheets-import/) for full information - enjoy!

Blog - Google Sheets Import cover

Google Sheets Import

We've just added support for markdown formatted text widgets to dashboards in PopSQL!

This gives you an easy way to add some additional context to your dashboards. Use them to add documentation, link to external resources, render images from a URL, organize your dashboard into sections, or anything else you can think of!

Check out the [dashboard docs](/docs/getting-more-out-of-popsql/dashboards) for more information!

Blog - Dashboard Text Widgets cover

Dashboard Text Widgets

Now when you share a [presentation link](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results#presentation-link-for-a-viewer), your stakeholders can refresh the data right from their browser, without needing to hop into the PopSQL editor. Your stakeholders can also edit [query variables](/docs/getting-more-out-of-popsql/how-to-use-variables-in-a-query) such as date ranges or customer IDs.

This works for queries **and** dashboards.

Here's a video demo for queries:

<script src="https://fast.wistia.com/embed/medias/8e28m4cd2i.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_8e28m4cd2i videoFoam=true" style="height:100%;position:relative;width:100%">&nbsp;</div></div></div>

And a video demo for dashboards:

<script src="https://fast.wistia.com/embed/medias/v55ikisql4.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 2em 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_v55ikisql4 videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/v55ikisql4/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

For full info, check out our docs on [sharing a query/results](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results), and [dashboards](/docs/dashboards#sharing-a-dashboard).

Blog - Presentation Link Improvements cover

Presentation Link Improvements

## Multiselect Query Variables

We added a new query variable type called `multiselect`. Use it when you want people to choose one or many values from a dropdown, like choosing regions, or choosing event names. See our [docs on query variables](/docs/getting-more-out-of-popsql/how-to-use-variables-in-a-query#multiselect--find-multiple-event-names) for more info.

<img src="/static/images/query_variables/multiselect.gif" />

## Sorting

As your list of PopSQL queries grows, it can become challenging to find what you're looking for. To help with that, we added sorting to the left sidebar so you can sort your queries alphabetically, created date, updated date, or last opened.

<img src="/static/images/blog/20210118_querylist_sorting.png" class="w-3/5" />

## Zoom

Now you can zoom in PopSQL by pressing `Cmd+`, zoom out with `Cmd-`, or reset your zoom with `Cmd+0`. You can find more keyboard shortcuts [here](/docs/getting-more-out-of-popsql/keyboard-shortcuts).

Blog - Multiselect Query Variables, Sorting, and Zoom cover

Multiselect Query Variables, Sorting, and Zoom

## Big number charts
If you just want to show a number in your chart, like the number of users that signed up yesterday, you can use the big number chart:

![](/static/blog/covers/big-number-chart.png)

## Point labels
Sometimes you want to know the exact value of a point in your chart. Now you can do that in PopSQL by checking the 'Show Bar Labels' option.

![](/static/images/blog/show-bar-labels.png)

## Combo charts
You can now create charts that have two y-axes. For example, this chart has a bars representing the number of orders on the left y-axis, and a line representing the number of ratings on the right y-axis.

![](/static/images/blog/combo-chart.png)


Blog - Chart improvements cover

Chart improvements: big number charts, combo charts, and point labels

Now you can create comments on a SQL query to discuss changes with your teammates. To add a comment, highlight some text, right-click the text, and click `Comment SQL`. When you write a comment, the query author will get an email notification. Enjoy!

<script src="https://fast.wistia.com/embed/medias/z9w8s8etyn.jsonp" async></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:56.25% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_z9w8s8etyn videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/z9w8s8etyn/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onload="this.parentNode.style.opacity=1;" /></div></div></div></div>

Blog - Comments cover

Comments

I'm excited to announce that PopSQL has raised a $3.4M seed round led by Gradient Ventures (Google's AI venture fund), with participation from FundersClub, Y Combinator, and many great angel investors: Max Mullen (Co-Founder Instacart), Calvin French-Owen (CTO Segment), Guillermo Rauch (CEO Vercel), Rafael Corrales (Background.vc), Chris Nguyen (Co-Founder LogDNA), Immad Akhund (CEO Mercury), Oliver Jay (Asana), Jeff Chang (Pinterest), Brianne Kimmel (worklife.vc), and others. These people have helped us every step of the way and I'm immensely grateful to them. This round of financing will help us grow the team so we can pursue our vision of creating the best tool for teams to collaborate using data.

## History

I started PopSQL because I was frustrated with the existing tools on the market. My favorite SQL editor at the time was compatible with MySQL only, so I couldn't use it with our data warehouse. I was saving `.sql` files on my computer and copying/pasting SQL to people over Slack. When I had an insight to share, I had to export the query results to CSV, import to Google Sheets, and share the link with a coworker, but it was only a static snapshot of the data, and it was missing the actual SQL behind it. If the query or results changed, I had to repeat the entire tedious process. There had to be a better way.

So I started coding on the side while working as an engineer at Instacart and launched the initial product on Product Hunt. The results were exciting, and major companies like DoorDash, Redfin, and Shipt started to adopt the product. Next, PopSQL was accepted into Y Combinator, raised a seed round, and now, here we are.

Over the last few months, we've added a ton of value to the product:
* **We improved charting**: now you can create and customize charts right in PopSQL. No need to switch to Sheets to visualize your data.
* **Version history**: Restore your query to previous points in time if you make a mistake. Make bulk changes in your git repo and have them sync to PopSQL using our GitHub integration.
* **Scheduling and alerts**: Schedule your queries and dashboards to run every morning and send the results to Slack so you always have fresh data.
* **Dashboards**: Add multiple queries and charts to see your company metrics at a glance.
* **Search**: Instead of re-writing a query, search if someone else has already written it
* **Query variables**: Make it easy for business users to re-run your query with different variables, like user IDs or dates. Analysts can also use conditional logic using "if" statements.
* **Shared connections**: Make it safe and easy for teammates to access the database.
* **Web application**: Access PopSQL without having to download the desktop app at app.popsql.com.

## What's next?

We have big plans to take PopSQL further. We want to make the SQL editor more powerful: better autocomplete, snippets, and more to give data analysts superpowers. We want to take our collaborative features further with better storytelling, comments, and data dictionaries.

## We're hiring!

If you're passionate about data and want to shape the next generation of data tools, we want to hear from you! We're hiring for engineering: frontend, backend, and full stack. Check out <a href="/about">popsql.com/about</a> for more details.

Onwards! 🚀

Rahil<br />
CEO, PopSQL

Blog - Announcing PopSQL's seed round cover

Announcing PopSQL's $3.4M Seed Round

## GitHub Integration

We now have a GitHub integration! Once you connect a repo to PopSQL, if you make a change in PopSQL, it gets pushed to GitHub, and if you make a change on GitHub, it gets pushed to PopSQL. This gives you more control of your data. For example, if you need to make a bulk change to your SQL queries (ie a table name change), you can easily do a find and replace, push to GitHub, and the changes will be reflected in PopSQL. This feature is available in our Enterprise plan. Please contact us if you'd like to add it to your plan.

## More Metadata to Help You Understand What's Relevant

As you create more queries in PopSQL, it becomes hard to know which queries are relevant/important, and which queries are deprecated/safe to ignore. To solve that, we've added additional metadata to queries that tell you when the query was written, who it was written by, when it was last run, and more.

<img src="/static/images/blog/20200823_social_context.gif" />

## Improved Search

We improved the search experience so that it's faster to find what you're looking for, and we added additional metadata to show you what's relevant and what's not.

When you first open the search modal with `Cmd+K`, you'll see a list of queries you recently opened. As you start typing, we'll do an instant search for **query names** that match your search term. This is useful when you know the exact name of the query you want.

<img src="/static/images/search/fuzzyfinder.gif" />

For Premium Team users, if you want PopSQL to perform a deeper search to search the **body** of your queries, press `Enter`. This is useful when you want to search if a specific table is being used, or search by a comment you know you wrote. In this example, I'm searching for a [CTE](/learn-sql/postgresql/how-to-write-a-common-table-expression-in-postgresql) I know I've used.

<img src="/static/images/search/advanced_search.png" />

## Other Changes in This Release

* You now have the ability to rename dashboard widgets :tada:
* We simplified the `Share` button

Blog - august 2020 update cover

GitHub Integration, More Metadata to Help with Relevance, and Improved Search

[Query variables](/docs/query-variables) let data analysts DRY up their SQL without typing the same values over and over again. More importantly, they allow business users to easily re-run queries with different parameters, like when sales wants to pull a report on a specific account, or marketing wants to pull a report on a specific campaign.

We've improved query variables so that you can:
* Specify a type
  * String
  * Number
  * Date
  * Boolean
  * Dropdown
* Set a default value
* Mark the variable required

![](/static/images/query_variables/settings.gif)

Types are important so your teammates know what kind of value to fill in. For example, you can make a dropdown list:

<img src="/static/images/query_variables/dropdown.gif" />

If someone tries to run your query without filling out a required variable:

<img src="/static/images/blog/20200730_query_variable_required.png" class="w-1/2" />

For full details and lots more examples, please see our docs on [query variables](/docs/query-variables). Enjoy! :tada:

Blog - query variables cover

Query Variable Improvements

In this update, we've made it easier to analyze your results, switch between queries, and invite teammates.

## Improved query results table

We improved the [query results table](/docs/getting-started/query-results) to feel more like a spreadsheet. You can now:

* Filter columns
* Sort columns
* Hide columns
* Expand a row to read it in a vertical format
* Highlight cells to copy/paste into a spreadsheet

![](/static/images/query_results/table.mp4)

## Automatically switch connections when you switch queries

You might have some queries that are meant to be run against one connection (eg [Postgres IDE](/connections/postgresql-client)), and other queries that are meant to be run against another connection (eg [Snowflake IDE](/connections/snowflake-client )). PopSQL will now remember which connection you used with each query and automatically switch connections for you :tada:

## Invitation links

We've added a new way for people to join your PopSQL organization: [invitation links](/docs/popsql-for-teams/inviting-teammates#invitation-links).

![](/static/images/inviting_teammates/copy_invitation_link.mp4)

Blog - July 2020 Update cover

July 2020 Update

Lots of new features and improvements to announce in PopSQL this month! 🎉

## Public dashboards

Now you can make your dashboards public so people don't have to sign in to view your dashboard.

![](/static/images/blog/20200619_public_dashboards.gif)

## Scheduled dashboards

Now you can schedule dashboards to refresh at a set interval. Perfect for having fresh metrics every morning when your day starts.

![](/static/images/dashboards/schedule-refresh.png)

## Slack notifications for scheduled queries and dashboards

When your scheduled query or scheduled dashboard completes, PopSQL can now send a Slack message with a beautiful preview. Visit our [Slack docs](/docs/slack) to connect Slack with PopSQL.

![](/static/images/blog/20200619_scheduled_dashboard_slack.png)

## Share a link to a specific run

Previously, [presentation links](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results) would show the _latest_ run of your query.
Now, if you click the "link" icon on a run, the URL will be specific to that run. So when you share the link with someone, they'll see the exact same results you see.

![](/static/images/blog/20200619_share_link_to_run.gif)

## Expand rows for better readability

When you hover a row, click the arrow icon to expand the row so you can easily read the row.

![](/static/images/blog/20200619_expand_row.gif)

Blog - June 2020 Update cover

June 2020 Update

Lots of exciting things to announce today!

## Charts

Since the very beginning of PopSQL, customers have wanted to customize their charts in PopSQL. They liked that we automatically created a chart for them based on their SQL query results, but they didn't like that they couldn't configure that chart, like changing from bar chart to line chart, or choosing which column should be your X-axis.

Today, we're excited to announce that **you can now fully customize your charts in PopSQL!** After running your query, hit the `Chart` button, and you'll be able to create a beautiful chart to your liking. You can choose the chart type (bar, line, pie, etc), choose your X-axis, and choose one or many columns from your SQL query to plot. This saves precious time by eliminating the need to export to Excel just to make a chart.

Learn more about our new charts on our [docs](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/).

![charts](/static/blog/videos/charts.mp4)

## Draft Queries

When you create a new query in PopSQL, it starts off with the name "Untitled Query" until you give it a name, just like a Google Doc. The problem is, sometimes you're writing an ephemeral query that you have no intention of reusing or sharing, and therefore don't want to give it a name. So you end up with dozens of untitled queries, cluttering your private folder or team folder.

To solve this, **new queries now start off as drafts** and live in a new drafts folder. If you don't care about the query, there's no need to name it. If you do care about the query, give it a name, move it into your folder "My queries," or move it into "Team queries" if you want it to be discoverable by your team. We hope this helps you create a more organized workspace.

## Database Switcher and Draggable Tabs

Two other small but much needed improvements:

If you're connected to a server that has multiple databases, **you can now quickly switch between the databases**:
![database switcher](/static/blog/gifs/20200504_database_switcher.gif)

**You can now drag and rearrange tabs** in PopSQL, just like you can in Chrome:
![draggable tabs](/static/blog/gifs/20200504_draggable_tabs.gif)

Blog - New charts and draft queries cover

New charts, draft queries, and more

New features in 0.9.10 make PopSQL a more robust editor and allow teams to share links to queries with more control.

## Enhanced editor

We've greatly enhanced the query authoring experience. From minimap navigation to clearer error messages, these updates to the editor make query writing smoother and faster. You can customize the experience in your [app preferences](/docs/getting-started/customizing-your-preferences/).

<script src="https://fast.wistia.com/embed/medias/uoibau3ps5.jsonp" async></script></script><script src="https://fast.wistia.com/assets/external/E-v1.js" async></script><div class="wistia_responsive_padding" style="padding:55.94% 0 0 0;position:relative;"><div class="wistia_responsive_wrapper" style="height:100%;left:0;position:absolute;top:0;width:100%;"><div class="wistia_embed wistia_async_uoibau3ps5 videoFoam=true" style="height:100%;position:relative;width:100%"><div class="wistia_swatch" style="height:100%;left:0;opacity:0;overflow:hidden;position:absolute;top:0;transition:opacity 200ms;width:100%;"><img src="https://fast.wistia.com/embed/medias/uoibau3ps5/swatch" style="filter:blur(5px);height:100%;object-fit:contain;width:100%;" alt="" aria-hidden="true" onLoad="this.parentNode.style.opacity=1;" /></div></div></div></div>

## Run all statements

You can now run multiple statements at once. Perfect for faster analysis or chaining together actions (like creating, populating, and analyzing a [temporary table](/docs/getting-more-out-of-popsql/temporary-tables/) in one click).

![run all statements](/static/blog/videos/run-all-statements.mp4)

## Public share links

We have a new `Share` button that lets you get a PopSQL desktop link, web app link, or a presentation link. We also support public links now, so can share your query with people without them having to sign in. Check out our [sharing docs](/docs/popsql-for-teams/sharing-a-link-to-your-query-and-results/) for all the details.

![public share links](/static/blog/videos/public-share-links.mp4)

Happy querying! 🍭

Blog - enhanced editor cover

Enhanced editor, run all statements, and public share links

We've added many features in 0.9.5 that make PopSQL a better tool for teams and power-users alike.

## Team-Focused Features

### Slack Integrations

Does your team share a lot of links to PopSQL queries? Get the gist of a query without ever leaving Slack with our [new integration](/docs/getting-more-out-of-popsql/slack-integration/).
![alt text](/static/images/slack/unfurl.gif "A PopSQL link shared in Slack now displays key metadata about the query.")

### Query Descriptions

Introducing query descriptions in PopSQL. Bring all the needed context up top where your teammates can see it.
![alt text](/static/images/blog/20200331_query_descriptions.gif "User adding a description to a query in PopSQL")

### Invite Teammates

The more teammates on PopSQL, the more time you save. Now invite all your favorite colleagues in just a click.
![alt text](/static/images/blog/20200331_invite_teammates.gif "User opens the invite teammates feature in PopSQL and quickly sends multiple teammates an invitation.")

## Editor Improvements

### Improved Temporary Table Experience

Use a lot of temp tables? We’ve added [new preferences](/docs/getting-more-out-of-popsql/temporary-tables/) to give you an even better experience in PostgreSQL and Redshift.
![alt text](/static/images/blog/20200331_temp_table_improvements.png "Screenshot of preferences menu that allows user to specify how temporary tables are handled in PopSQL.")

### Attach a CSV to a Scheduled Query

When you [schedule a query](/docs/getting-more-out-of-popsql/scheduled-queries/) in PopSQL, you can now get a CSV of the query results in the notification email. Save a few steps or power a workflow.
![alt text](/static/images/blog/20200331_scheduled_query_with_attached_csv.gif "User schedules a query in PopSQL and specifies results include a CSV attached to the notification email.")

### Connect to Snowflake with Okta / SSO

For teams who use MFA and/or Okta to sign into [Snowflake IDE](/connections/snowflake-client), we have you covered. [Learn more](/docs/getting-started/connecting-to-snowflake/) about connecting with Okta.
![alt text](/static/images/blog/20200331_snowflake_okta.png "Snowflake and Okta logos, announcing that users can connect to Snowflake with Okta in PopSQL")

### Easy Access Preferences

PopSQL now puts your preferences and other key actions right where you need them.
![alt text](/static/images/blog/20200331_easy_access_preferences.gif "A screenshot showing a new access point to preferences menu in PopSQL")


Blog - slack integration cover

Slack integration, query descriptions, and more

With dashboards, you can see your key metrics at a glance. Add multiple queries and visualizations to a dashboard to stay informed on key metrics.

Choose whether to view the results as data or an [auto-generated chart](/docs/getting-more-out-of-popsql/how-to-make-charts-to-visualize-your-data/). Then rearrange and resize the results however you’d like. And with [query variables](/docs/query-variables/), you can refresh the entire dashboard with a click (view data by day or by week).

Perfect for weekly reports, product launches, marketing campaigns, sales growth, database health... any metric that matters to your team!

![alt text](/static/images/dashboards/dashboards.png)

This feature is available now for Premium Team users. Please see our [docs on dashboards](/docs/getting-more-out-of-popsql/dashboards/) for full information - enjoy! 📊

Blog - dashboards cover

Dashboards

PopSQL now saves you time, even when you don't have PopSQL open.

With scheduled queries, PopSQL can alert you on important events (think new orders over a certain size, or no new data in the past day). You can also schedule your queries to run at a specific point in time, or on a recurring basis like every hour, day, or week. No more updating queries fifteen minutes before the next meeting; let PopSQL do it for you.

![alt text](/static/images/blog/20200127_scheduled_queries.png "Image of PopSQL modal for scheduling a query")

This feature is available now for Premium Team users. Please see our docs on [how to schedule queries](/docs/getting-more-out-of-popsql/scheduled-queries/) for full information - enjoy!

Blog - Scheduled Queries cover

Scheduled Queries

PopSQL now supports connecting to BigQuery via OAuth. You can still connect via a Service Account if you'd like, but an OAuth connection is faster and simpler.

Connecting to OAuth is supported on the PopSQL desktop and [web app](https://app.popsql.com).

Read the [full guide on how to connect to BigQuery](/docs/getting-started/connecting-to-bigquery/).

Blog - bigquery oauth cover

BigQuery OAuth

PopSQL makes it extremely easy for your team to switch between databases. Those databases, though, have differences in syntax that are tricky to remember.

Date and time functions have tripped us up in particular. That's why we created this handy cheat sheet for SQL date and time functions across some of our most popular databases: PostgreSQL, MySQL, Redshift, Snowflake, and BigQuery.

<a href="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" target="_blank">Download the PDF</a>

<embed src="https://popsql-misc.s3.amazonaws.com/popsql-datetime-cheatsheet.pdf" type="application/pdf" width="100%" height="700px">

Blog - sql date time cover

SQL Date and Time Functions Cheat Sheet

Free samples are easily my favorite thing about grocery stores.

Free sample datasets, though? Those can be awfully dry.

You're guaranteed to need sample data at some point. Perhaps you need to interview a potential analyst but can't use your company's data. Maybe you're tired of your company's data. It's not the same as when you first met it. And you're dying to rekindle that spark that made you love SQL in the first place.

We've scoured the web for some non-boring datasets so you don't have to:

- <a href="https://github.com/timescale/examples/tree/master/crypto_tutorial/Cryptocurrency%20dataset%20Sept%2016%202019" target="_blank">Historical Bitcoin Prices</a>
- <a href="https://tech.instacart.com/3-million-instacart-orders-open-sourced-d40d29ead6f2" target="_blank">3 Million Instacart Orders (Open sourced)</a>
- <a href="https://movement.uber.com/cities?lang=en-US" target="_blank">2 Billion Uber rides (anonymized)</a>
- <a href="https://pleiades.stoa.org/" target="_blank">Database of the Ancient World</a> _(for history buffs)_
- <a href="https://www.kaggle.com/tmdb/tmdb-movie-metadata" target="_blank">5000 Movies Dataset</a>
- <a href="https://docs.atlas.mongodb.com/sample-data/sample-airbnb/" target="_blank">Airbnb listings Data</a> _(for [MongoDB](/connections/mongodb-client))_
- <a href="https://webrobots.io/kickstarter-datasets/" target="_blank">Data on all Kickstarter projects since 2016</a>
- <a href="https://www.kaggle.com/NUFORC/ufo-sightings" target="_blank">UFO Sightings</a> _(can you believe there have been 80,000 sightings and we still haven't taken a good picture?)_

Most of these datasets will be downloaded as a CSV. If you don't know how to import a CSV into a database, we have guides for that too: [PostgreSQL](/learn-sql/postgresql/how-to-import-a-csv-in-postgresql/), [MySQL](/learn-sql/mysql/how-to-import-a-csv-in-mysql), [Redshift](/learn-sql/redshift/how-to-import-a-csv-in-redshift/).

Need more data? Sign up for Jeremy Singer-Vine's newsletter, <a href="http://tinyletter.com/data-is-plural" target="_blank">Data is Plural</a>. He sends 5 interesting datasets every Wednesday.

Stay querying, my friends.

Blog - Cool Sample Data cover

Cool Sample Data

We've been working hard to build the best SQL editor for teams. With release 0.7.8, the two most highly requested databases are now live on PopSQL: [Amazon Athena](/connections/athena-sql-client) and [MongoDB IDE](/connections/mongodb-client). Learn more on how to connect to [Amazon Athena](/docs/getting-started/connecting-to-athena/) and [MongoDB](/docs/getting-started/connecting-to-mongodb/).

Blog - Announcing Support for MongoDB and Athena cover

Announcing Support for MongoDB and Athena

Better autocomplete, auto limiting results, and some performance improvements are all new in version 0.6.6 of PopSQL.

## Better autocomplete

Now suggestions will be more contextual. We'll suggest columns for tables that are in your query. Works with aliases too!

![alt text](/static/images/blog/20190402_better_autocomplete.gif "GIF of autocompleting an inner join")

## Auto limit

Sometimes you might forget to include a `limit` clause in your query and end up executing an expensive query. Now PopSQL will automatically include a `limit 100` clause for you. Of course you can disable this whenever you want by unchecking the box, or providing your own `limit` clause.

![alt text](/static/docs/query/auto_limit.gif)

## Performance improvements and bug fixes

Integer sorting is actually works now, and opening or closing `Explore` doesn't take ages anymore. Enjoy!


Blog - better autocomplete cover

Better Autocomplete and Auto Limit

Now PopSQL queries are automatically version controlled. You can view unlimited version history of your query and restore to any point in time. This is useful if you or a coworker has made a mistake on your query and you'd like to go back in time.

![alt text](/static/images/blog/20190203_version_history.png "Screenshot of version history")

Version history is available now for Premium Team members. Please see our [docs on version history](/docs/popsql-for-teams/version-history/) for full information 🎉

Blog - Version Control for SQL Queries cover

Version Control for SQL Queries

SQL queries commonly have a `where` condition for a specific ID or date range. Wouldn't it be nice to extract that into a variable? For example, show me users that belong to `$organization_id`, or users created after `$start_date`. If you're sharing SQL queries with non technical folks, what if they could edit the variable without changing the any code?

Now in PopSQL, you can have <strong>variables</strong> in your SQL queries. To create a variable, simply wrap you variable in double curly braces, like `{{id}}`:

![alt text](/static/images/blog/20190110_query_variables.png "Screenshot of a query variable")

This feature is available now for Premium members. Please see our [docs on query variables](/docs/query-variables/) for full information - enjoy!


Query Variables

A common workflow for is to [create a temporary table](/learn-sql/postgresql/how-to-create-a-table-in-postgresql/) to break down your analysis into smaller pieces.

Before, PopSQL would only maintain PostgreSQL and Redshift sessions for 30 seconds. So if you ran one query to create a temporary table and didn't run another query for 30 seconds, the session would be terminated and your temporary table would be dropped. Bummer!

Now, PopSQL will keep your PostgreSQL and Redshift sessions open for 8 hours. So if you run a query to create a temporary table, you can come back up to 8 hours later and it will still be there for you to continue your analysis.

Enjoy!

Blog - Temporary Tables cover

Temporary Tables

Has anyone written a query on retention? Does anyone use this table or column? How do you use that function again? These are all questions you can answer using PopSQL's improved search capability, now available for Premium Team subscribers.

You'll find a new search box in the top right of the app:

![alt text](/static/images/blog/20180716_search_box.png "Screenshot of the search box in PopSQL")

Clicking the search box will open the search popup. You can also press Cmd+T for faster access. When the search popup opens, we'll show you <strong>recently opened queries</strong> so you can jump back somewhere if you need to. When you start typing, we'll instantly <strong>search queries by name</strong>. You can use your mouse or arrows keys to quickly navigate to a result.

![alt text](/static/images/blog/20180716_recent_history.png "Screenshot of recently opened queries in PopSQL")

For Premium Team subscribers, we'll also perform a deeper search to find <strong>queries that have matching content</strong>. This is where you can see if anyone is using that table or column, or how to use that confusing function, or search by a comment you know you wrote last week.

![alt text](/static/images/blog/20180716_advanced_search.gif "Screenshot of the searching query contents in PopSQL")

We hope these new search capabilities help you get your data faster!

Blog - search cover

Search

If you're writing code for hours, you want something easy on your eyes. That's why we created dark mode for PopSQL, by popular demand.

![alt text](/static/images/blog/20180520_dark_mode.png "Screenshot of PopSQL dark mode")

Dark mode is available today for [Premium](https://popsql.com/pricing) users. To turn it on, go to `Preferences > Editor > Theme`, or in your menubar, `View > Dark Mode`.

Enjoy!

Blog - Dark Mode cover

Dark Mode

Mixpanel is great, but it's very helpful to have your raw event data in your database so you can use SQL to extract insights. In this post, I'll show you how you can accomplish that. We'll be using Ruby on Rails and PostgreSQL, but the general approach of pulling data from Mixpanel, inserting it into the database, and scheduling a background job on an interval should work with any language and database.



## Overview

Here's what we'll cover:

- A service class that is responsible for consuming the Mixpanel API, parsing the response body into JSON, building a database row for each event, and inserting those events efficiently
- A background job that calls the service class
- A scheduler that runs the background job on an interval (eg every minute)

There are some considerations that make this not so simple:

- How to avoid downloading duplicate events from Mixpanel?
- How to convert Mixpanel event timestamps to UTC?
- What to do if the request to Mixpanel fails?
- What to do if your database is slow and the background job takes longer than the interval?
- How can we do this in a memory efficient way?

## Dependencies

Here are some of the technologies we're using:

- <a href="https://github.com/mperham/sidekiq" target="_blank">Sidekiq</a> for background jobs
- <a href="https://github.com/mhenrixon/sidekiq-unique-jobs" target="_blank">sidekiq-unique-jobs</a> to create a lock while the background job is running
- <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> for scheduling jobs
- <a href="https://github.com/ohler55/oj" target="_blank">Oj</a> for fast JSON parsing
- <a href="https://github.com/zdennis/activerecord-import" target="_blank">activerecord-import</a> for bulk database inserts
- <a href="https://www.mixpanel.com" target="_blank">Mixpanel</a>...duh :wink:

## The background job

```ruby
class EventWorker
  include Sidekiq::Worker
  sidekiq_options retry: false, unique: :until_executed, log_duplicate_payload: true
  PT = Time.find_zone!('America/Los_Angeles')

  def perform
    from_date = PT.now.hour < 5 ? PT.yesterday : PT.today
    EventService.ingest_mixpanel_events(from_date: from_date, to_date: PT.today)
  end
end
```

We specify `retry: false` because if the job fails, it's okay, let it try again on the next interval.

The `unique` and `log_duplicate_payload` options are provided by `sidekiq-unique-jobs`. This will create a lock and release it only when the job is finished executing. That way, only one of these jobs can run at a time. If someone tries to enqueue this job and one is still running, it will be logged, and it the duplicate won't be processed.

The math with `from_date` is so that when 12:01am strikes, we don't just ignore yesterday's events, because our systems could still push an event that happened minutes ago, or Mixpanel could do the same if they have a delay in their processing. We're effectively creating a 5 hour buffer here. You can adjust this to whatever you like (eg 1 hour, 8 hours, etc).

## The service class

```ruby
# frozen_string_literal: true

class EventService
  def self.ingest_mixpanel_events(from_date:, to_date:)
    ApplicationRecord.transaction do
      # Blow away all the events in this range
      Time.use_zone(PT) do
        query = <<-SQL.squish
          delete from events
          where time between '%s' and '%s'
        SQL
        sanitized_query = ActiveRecord::Base.send(:sanitize_sql_array, [query, from_date.beginning_of_day.to_s(:db), to_date.end_of_day.to_s(:db)])
        ActiveRecord::Base.connection.exec_query(sanitized_query, "EventService#ingest_mixpanel_events")
        log "Deleted events between #{from_date.beginning_of_day} and #{to_date.end_of_day}"
      end

      # Ingest the events in this range
      (from_date..to_date).each do |day|
        events = []
        jsonl_string = fetch_mixpanel_raw_data(from_date: day, to_date: day)
        log "Fetched JSONL events from mixpanel for #{day}"
        Tempfile.open(['mixpanel', '.json']) do |file|
          file.write(jsonl_string.force_encoding('UTF-8'))
          log "Wrote JSONL to #{file.path}"
          file.rewind
          file.each_line do |line|
            events << convert_line_to_event(line)
          end
        end

        # Perform the inserts
        ActiveRecord::Base.logger.silence do
          Event.import(events, batch_size: 500)
        end
        log "Imported events for #{day}"
      end
    end
  end

  def self.convert_line_to_event(line)
    raw_event = Oj.load(line)
    raw_time = Time.zone.at(raw_event.dig('properties', 'time')) # This is a Pacific unix timestamp, but Rails doesn't know that
    pacific_time = Time.find_zone!('America/Los_Angeles').parse(raw_time.iso8601.gsub(/Z$/, '')) # Tell Rails it's a Pacific timestamp
    raw_event['properties']['time'] = pacific_time.utc # Convert the timestamp to UTC
    build_event_from_raw_event(raw_event)
  end

  def self.fetch_mixpanel_raw_data(from_date:, to_date:)
    conn = Faraday.new do |conn|
      conn.basic_auth(Rails.application.secrets.mixpanel_api_secret, '')
      conn.request :url_encoded
      conn.adapter Faraday.default_adapter
      conn.options.timeout = 60 # 60 seconds
    end
    resp = conn.get('https://data.mixpanel.com/api/2.0/export/', {from_date: from_date, to_date: to_date})
    resp.body
  end

  def self.build_event_from_raw_event(raw_event)
    # Choose whatever Mixpanel event properties you want in your table
    Event.new({
      name: raw_event['event'],
      time: raw_event.dig('properties', 'time'),
      distinct_id: raw_event.dig('properties', 'distinct_id')
    })
  end

  private

  def self.log(msg)
    Rails.logger.tagged(self.name) do
      Rails.logger.debug(msg)
    end
  end
end
```

There's a lot going on here, so let's try and break it down.

`ingest_mixpanel_events` the main method of the class. It's responsible for deleting events in our database in the `from_date..to_date` range, asking Mixpanel for the events in this range, and performing the database inserts. Why do we need to delete events in this range first? The reason is because <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> doesn't give you unique identifiers for each event, so you can't do something like "give me all of today's events, but don't insert ones that I already have." You _could_ do this with timestamps, like "give me all of today's events, but ignore ones up to $last_import_time," but then there's a chance you'll miss some events if you or Mixpanel ever insert events _before_ $last_import_time.

We use a `Tempfile` to store the response body from Mixpanel on disk rather than in an Array, which keeps the memory usage of this method low. Then we iterate over each line and call `convert_line_to_event`.

Everything is wrapped in a transaction because without one, if someone ran a query for today's events and the background job just deleted today's events, then the query would return nothing. But if they ran that query again in a few seconds when the background job has a chance to complete the inserts, then it would have results. Using a transaction prevents this confusing experience.

`convert_line_to_event` is responsible for taking `line` (String), parsing it as JSON, converting the timestamp to UTC, and calling `build_event_from_raw_event`. The timestamp work is necessary because <a href="https://mixpanel.com/help/reference/exporting-raw-data#api-details" target="_blank">Mixpanel stores events in your project's timezone</a>, and it's best practise to store timestamps in your database as UTC.

`fetch_mixpanel_raw_data` is responsible for hitting <a href="https://mixpanel.com/help/reference/exporting-raw-data#export-api-reference" target="_blank">Mixpanel's raw data export API</a> and returning the response body.

`build_event_from_raw_event` is responsible for taking `raw_event` (JSON) and creating an `Event` instance (assuming you have an `Event` model and `events` table).

## The scheduler

```ruby
every 1.minute, 'Run EventWorker' do
  EventWorker.perform_async
end
```

At PopSQL, we use <a href="https://github.com/nickelser/zhong" target="_blank">Zhong</a> to schedule jobs. Simply add a few lines to your `clock.rb` file to run the background job every minute:

## Ways to make this better

How can we reduce the memory footprint of the background job? When I used <a href="https://github.com/SamSaffron/memory_profiler" target="_blank">memory_profiler</a> to measure the memory usage, a lot of it was coming from initializing instances of `Event` for each row, and doing the time parsing/math.

<a href="https://twitter.com/andrewkane" target="_blank">@andrewkane</a> pointed out I could pass <a href="https://github.com/zdennis/activerecord-import/wiki/Examples#import-using-columns-and-arrays" target="_blank">activerecord-import arrays of columns and values</a>, which is its fastest import mechanism, rather than creating `Event` instances.

We could also hit the Mixpanel API _before_ opening the database transaction.

## Conclusion

Now that your database has your Mixpanel events up to the minute, you can use PopSQL to extract all kinds of insights. Who are the newest users? Which users activated or need help activating? Who's taking high value actions on the site right now that sales should follow up with? Enjoy, and feel free to chime in in the comments below.

<em>Thanks to <a href="https://twitter.com/octernion" target="_blank">Nick Elser</a> and <a href="https://twitter.com/andrewkane" target="_blank">Andrew Kane</a> for guidance and suggestions.</em>

Blog