Difference between VIEW and BASE TABLE in DuckDB?

[akshayjain3450]

Hi Community!

I have been working will rill for quiet sometime and I am not able to figure out when is a VIEW created and when is a BASE TABLE created inside DuckDB database.

How did I come up with this question?

I had a source as postgres from which I was reading data. A few times I saw the source table created as type BASE TABLE and other times it was getting created as a VIEW. I am not sure what I am missing here.
Appreciate for some help and light on this doubt of mine.

[AndrewRTsao]

Hey @akshayjain3450 - thanks for your question and participation in the community!

To answer your question, generally speaking, sources are created as base tables in DuckDB while models are created as views. With that being said, you can add the following syntax to the top of any individual model.sql file to materialize the model as a table:

-- @materialize: true

The upside of doing this is that this can help to significantly improve the performance of queries that run against said model, which is useful when the model is powering a dashboard (or other models). In fact, our Rill Cloud offering will automatically materialize any models by default that are backing dashboards. However, this can also lead to a slow keystroke-by-keystroke profiling experience and certain queries, such as cross joins, will actually perform worse. Therefore, we would generally recommend leaving your models as views unless they are very large (and performance is degrading) or you are using them to power a specific dashboard.

Best,
Andrew

[akshayjain3450]

Hi Andrew!

I appreciate the explaination on this. It would be very helpful if you could walk me through why do I have anomaly while working with postgres source in development mode.

I have the updated main branch and if you want I am willing to share the screenshots and the YAMLs for source and model. Also, I have not changed anything the code base for now. Does this is also related to any flag while we run the cli- start command or anything else.

[AndrewRTsao]

Could you share the screenshots / YAMLs along with any steps to reproduce or schemas of the source tables in Postgres so that we can try reproducing on our end? It would be helpful to understand the version of Postgres you are using as well.

[AndrewRTsao]

Could you also share a screenshot of your source names and your <RILL_HOME>/tmp directory? We recently implemented a change where any new project will automatically have external table storage enabled so that tables by default will be stored in /tmp/<source_name>/xxx.db (and source_name will be listed as a view in main.db). Previously, for older projects, sources will be stored as base tables in main.db and this will be the case for existing projects unless you delete the tmp directory of the project (and restart Rill - which will then use the new storage format as described).

[akshayjain3450]

Understood @AndrewRTsao. I recently deleted the tmp directory and fresh started with the newer version of rill on main branch and as you have mentioned I have the same structure now. So thank you for explaining me this. Its not a bug then just a change of now how you are storing source table.

[AndrewRTsao]

Glad to hear that we were able to get your question answered!