Splitting a query into CTEs

[MaxHalford]

As a data analyst, when I debug a view, I spent a lot of time going through each CTE and tracing the logic from one CTE to the other. It would be cool if there a way to automatically split a query into its CTEs, and possible materialize each one. Then I could inspect them separately without having to copy/paste code all over the place. I know that Count has a demo where the splitting would be done automatically in their UI, but I can't find it anymore.

[MohamedBsh]

Good idea !
You could extend this further by adding features like showing the schema of each CTE, providing row counts, or even visualizing the relationships between CTEs.

[MohamedBsh]

Perhaps we could integrate this functionality into your existing SQLView class as a method called materialize_ctes() here

Using sqlglot :

• Parses the SQL query using sqlglot.
• Extracts each CTE and the main query.
• Materializes each CTE as a temporary table (and the main table for comparison?)
• It allows you to inspect the data in each materialized CTE and the main query.

[MaxHalford]

Yep I think that's the general idea. Each CTE table could be named schema.view__cte_name. There could be a --materialize-ctes option in the CLI to activate this feature.