How can I create a materialized view in DSS and use it in the flow ?

[UserBird]

I have few tables in postgreSQL and I want to make a materialized view for a complex join query.

[kenjil]

The simple answer to that is maybe not to use a materialized view :

• You may create a SQL query recipe containing your complex join query and its output will be a table filled with the data corresponding to that query. The output will be very similar to what you have in mind because it materializes in a table your query. Rerunning the flow will update that table.

If for some optimisation reason you still want to use a materialized view, you can do the following :

1. create your materialized view in a SQL notebook


2. create a SQL dataset based on this materialized view by manually entering its name in the table field.


3. create a SQL script recipe with your refresh statement. The inputs of this recipe are the tables used in the join query, the output dataset is the one pointing to the materialized view you created at step 2.