PRQL-based models

[benfdking]

Background

PRQL is a modern language for transforming data. It overcomes lot's of the shortcomings of SQL for pipeline design.

The pipeline nature of the language would make it a lovely fit for refactoring models because you essentially can take subsections.

Proposal

Questions still needing to be answered:

Major:

• Why this rather than Malloy or another language?
• How would the asset referencing work?

From q.<model> could be replaced just like we do it for SQL. We could either do it upfront or keep it more uniform after the SQL conversion.

• How would this interact with Jinja-like templating?

Might have to just not have Jinja-like templating.

• What would the file structure be for the file and project_file?

Intial proposal could look like .prql file extensions. Would read them just like a model and you could have the definition in the yaml file.

• What can we do for autocomplete and all of those niceties?
• How would this work in charts?
• How would it work with the SQL inference engine?

Convert then run through the inference engine.

• How do we work where PRQL works natively with the database, like Clickhouse?

Just don't use that.

Minor:

• What is the impact on performance, binary size, etc? Do we want to think of this as an extension or as built-in?
• Can we mark this as experimental and not make promises? (make it feel more like a two-way door)

Nit:

• Complexity of versioning systems?

Abandoned Ideas

Sections

Implementation

UX

[kflk]

PRQL is rust so you could maybe pull it in as a library? See the work done for PRQL DBT integration here: https://github.com/prql/dbt-prql

[max-sixty]

Hi from the PRQL team!

PRQL is rust so you could maybe pull it in as a library?

Yes this indeed would be quite a simple addition given quary is in rust.

(dbt-prql isn't the best example, since that's doing a lot of hackery in python since dbt doesn't have a proper plugin system. The integration in quary would be much easier — just call the compile method from prqlc...)

[benfdking]

Hey @max-sixty,

Yep, we see no reason why it would be difficult to integrate. It should all compile nicely into our Rust binaries/WASM. We're slightly more concerned about the user experience, complexity, maintainability burden "other non-technical aspets".

I've added a few questions in the initial issue that I can try to answer over time and can start answering them.

[max-sixty]

Great, the questions look very reasonable. I won't attempt to address them all but a couple of thoughts:

How would this interact with Jinja-like templating?

You could run the PRQL through a jinja template before passing it to the compiler.

Though if you prefer to get the SQL with the templating, PRQL should pass through Jinja templates — because we originally implemented this for dbt. It's not really used, and there have been suggestions to remove it, but we could keep it if there were demand. (I'd probably want to confirm it's still running well before depending on it, let us know)

What can we do for autocomplete and all of those niceties?

We don't have an LSP at the moment. It's probably a project after the next big project of rewriting the resolver. I'm quite keen to do this, since one of the original benefits of PRQL was that autocomplete could be much better than with SQL

Complexity of versioning systems?

I would think it's fine to just be on latest

Why this rather than Malloy or another language?

We're big fans of Malloy too! One thing to consider is that a big part of Malloy's innovation is to manage the full project, plausibly as a replacement for something like dbt. Whereas PRQL's innovation is predominantly around the relational language, and currently needs a "project manager" like dbt / quary to be run across a large project.

[eitsupi]

Though if you prefer to get the SQL with the templating, PRQL should pass through Jinja templates

IIUC, that feature has been removed.
The CLI includes minijinja now but I think text inputation is executed before compile. (PRQL/prql#1722, PRQL/prql#2104)

[max-sixty]

IIUC, that feature has been removed.

OK thanks, sounds like we did remove it. If this is important for quary, we could come up with something...