Repository navigation
Performance improvement on persist_docs #507
Description
Describe the feature
persist_docs currently sends an individual alter table statement per column comment. This takes forever for a large project.
Describe alternatives you've considered
Don't use persist_docs and instead parse the comments from dbt model and use unity catalog rest api
Don't use persist_docs and instead parse the comments from dbt model and generate my own custom statements
Don't use dbt comments at all and instead use unity catalog rest api to AI generate comments and persist those
Most alternatives involve not using persist_docs or not using column level comments
Additional context
We have the ability to add column level comments at the create table step and through the rest api, individual alter statements are the worst alternative.
Who will this benefit?
Everyone using persist_docs that has more than a couple models with column level comments.
Are you interested in contributing this feature?
Not at this time.
Activity
benc-db commentedon Nov 16, 2023
Investigating now. Thanks for the report.
benc-db commentedon Nov 17, 2023
So I dug into this and we may be stuck for now. The syntax for declaring the comments at table creation requires knowing the list of all columns and their types. We are currently doing this with views by asking for a limit 0 of the view, but this works because the view is referencing the columns of existing tables. Once the table is created, we don't need to specify the type of a column to alter it, but there is currently no syntax for bulk updating columns after a table is created. We're looking into if we can have that added to our SQL, but it's going to be awhile, if we even get signoff for that change.
What we could do is give a config flag that basically says 'I promise to provide the correct column name and type for every column in my schema'. With this information, we can at least avoid per-column update on table creation, though we'd still have no option for incremental tables.
benc-db commentedon Nov 17, 2023
@jordandakota which api do you have in mind? I went looking for this and I don't see any way to bulk update column comments.
jordandakota commentedon Nov 17, 2023
benc-db commentedon Nov 21, 2023
I have a change I'm working on now that will filter to only changed comments on incremental table updates. While that doesn't address the core issue (no bulk update), it should dramatically reduce the time on comment updates for incrementals, as I imagine most comments stay the same between incremental runs.
shivonchain commentedon Apr 19, 2024
Hey @benc-db, any chance this is still being worked on?
benc-db commentedon Apr 19, 2024
Still pushing internally to get a bulk alter.
benc-db commentedon Apr 19, 2024
Switching to blocked. There are 2 possible changes internally (and I'm pushing for both), either of which could allow a significant improvement. I've also raised with the DBSQL team that adding column comments in general is unreasonably slow (architecturally, something causes it to scale with table size).
septponf commentedon Aug 30, 2024
@benc-db any progress in this area?
A SQL bulk alter for column descriptions would be nice, right now it is super inefficient.
I would also love to be able to incorporate the Unity Catalog AI somehow as a model setting or have an option to persist existing comments during "create or replace". Then comments can be set manually in Unity Catalog and not be cleared during table runs.
benc-db commentedon Sep 12, 2024
Apologies for delay, was out on vacation. We are still waiting on a bulk API unfortunately. The other thing that would help us out here is if we could persist the comments on table create, rather than alter, since I suspect most changes after create are marginal. The thing that is blocking that is the ability to add comments to columns without knowing column type, which is the other change I'm pushing for.
Zurbste commentedon Mar 14, 2025
Hi @benc-db,
do you have any updates on this? We started documenting our models extensively and the time to build models has increased significantly due to the individual ALTER TABLE statements.
Any feedback is appreciated :)
benc-db commentedon Mar 14, 2025
Two updates, one near term, one longer term. Near-term: in 1.10.0, there will be a behavior flag that lets you switch to a new flow for materialization that persists columns at create time, so you should only need to apply alters when you change documentation on existing relations. Long-term: a change has been merged to Databricks to allow bulk alter, but I don't think its widespread or documented yet. Once it is, I can make comments a single alter operation which hopefully further improves things. In my testing, the changes I have in 1.10.0 significantly improve performance around documentation.
benc-db commentedon Mar 14, 2025
And forgot to mention - 1.10.0 is coming near the end of this month. There will be an alpha next week.
benc-db commentedon Mar 20, 2025
Alpha out now, see #963
rapha-pereira commentedon Oct 20, 2026
Hi guys!
Any updates on this?
I understand that 1.10.0 doesn't solve the original issue since the "comment on" query is being triggered for every column in the table, even if the docs of the column didn't change.