TLDR

PRQL is a SQL complier that lets you write SQL with a different mindset. PRQL outputs a SQL query at base. ClickHouse and DuckDB support PRQL via community extentions and the PRQL dialect can be executed directly.

Isn’t SQL good enough?

Lets start by acknowledging that there is nothing wrong with the state of SQL language other than the fact that it can only be written is a certain way. Which for some might not be the most intuitive way of thinking about how the query engine works. SQL Query Execution Order Source - bytebytego

Let’s break down the order of execution as depicted in the diagram above:

  1. All the tables are call called which are part of the origin from statement and mentioned in the join statement.
  2. Tables are join on the criteria mentioned in the ON statement.
  3. WHERE filter contrainted is applied.
  4. Columns are then categorised in to groups as declared in the GROUP BY statement.
  5. HAVING
  6. Columns are not selected based on the SELECT statement.
  7. Columns then are orderd based on the order of columns mentioned in the ORDER BY statement.
  8. LIMIT of rows are then applied to the output of the query.

So as you can see the order which the engine execute the query is not the same way the query is written. This mental model is fine if you are used to how sql statement works. But mistakes can happen has the execution is not the same as the statements are declared and this can cause performance degradation for someone who doesn’t know the execution order or isn’t experience with sql engine.

This is where the PRQL comes in to provide an alternative, itss neither good or bad alternative. Really depends on different users and provides them with an option.

Mental model of PRQL might not have sense to seasoned SQL users who really understand how it works under the hood.

What is even a PRQL?

Pipeline Relational Query Language - Prequel

PRQL is a complier written in rust, its objective is to make sql queries more intuitive.

Lets experiment

  • CSV
  • DuckDB
  • Snowflake

dbt extention

Not so stable and is a bit of dark magic. RECOMMENDATION: Not to use it in production, it might break at any point and dbt is not too keen on supporting this feature anytime soon.

Limitations

Other than Clickhouse, DuckDB and PostGreSQL there is not much native support for prql. IDE and LSP are also underdeveloped to support PRQL.

Conclusion

Use prql as a complier, write the queries in it and then use the output of the complier as the query to be used in production.

I think the use is also quite limited and unless the team that you are part of all agree on onboarding prql, adoption is going to be a challenge.