Can a view on a MotherDuck lake update automatically when its table changes?

Patrick G. · 2026-10-13T14:47:56.593Z

Morning, guys. One technical doubt: I have a ducklake fully managed by motherduck - lake_gold. Ducklake have a different strucutre and format from regular dbs, since they read the parquets while dbs read the db files .duckdb, for example. Situation is, if I create a view in a db from my lake, will it always read the lake using the parquet strucuture? Is there a way to create a table that triggers update everytime a certain table is replaced or updated in my lake? I'm trying to avoid external code to this point. If user has access to that db only, but not to the lake, will it be able to query that view normally?

5 comments

· Sorted by Oldest

Tia M.
·
·
Hey Patrick! A view would work great for this use case, without external code A view doesn’t store data, only the query. So if you create a view in a regular MotherDuck database pointing at a table in lake_gold, For example
CREATE VIEW my_db.main.my_view AS SELECT * FROM lake_gold.main.my_table;
every time you query it, it reads the current state of the lake and a full CREATE OR REPLACE TABLE on the lake table shows up right away. However, MotherDuck doesn’t support triggers or materialized views, the view covers that need, since it’s always current. If the underlying lake table is dropped or renamed, or a column the view references by name is removed, you’d need to recreate the view. Additionally, If you share the database holding the view with other users, they’ll also need access to lake_gold for the view to work. If you ever need a stored copy instead, you can refresh it on a schedule with CREATE OR REPLACE TABLE ... AS SELECT * FROM lake_gold.... Let me know if this makes sense!
Patrick G.
·
·
Right I got it. It could work, but ducklake does not have schema or table level permission like the regular dbs on MD. Therefore, I can't do it, otherwise I'd allow access to all tables, even the ones that are not supposed to be shared with a specific user
Patrick G.
·
·
The best way still is to include one fixed script after all my gold lake publishes to check for changes in the metadata of specific tables and replicate them to specific dbs
Patrick G.
·
·
Triggers in duckdb 2.0 seem not to support the kind of update I do in my lake which is CREATE REPLACE TABLE rather than a simple UPDATE, otherwise it could be possible to make a trigger for each client db considering changes in specific tables in my lake_gold
Tia M.
·
·
Thanks Patrick, makes sense. We already have a feature request open for table-level security on DuckLake, and I’ve added your use case to it. Let me know if anything comes up!