Support for DBT?

[walker-philips]

Does BemiDB support dbt's write operations (materializing as view/table?)

The road map indicates a need to implement write capabilities which I interpreted as Bemi only being able to support select statements at this time. If that's the case, how much of the overall Postgres functionality is also impacted (row level security, role based security, commenting, indexing, extensions, etc?)

[exAspArk]

Hi @walker-philips — thank you for submitting this issue!

That's right, BemiDB currently doesn't support write operations, which is on our roadmap. Would you be able to share your ideal solution and use case? Would you use dbt Core or dbt Cloud? Would you prefer dbt vs having automatically materialized views within BemiDB with incremental view maintenance (IVM)?

In terms of other Postgres features, we're planning to add support for role-based security (RLS is out of scope for now). Do you have any specific extensions in mind? We might add indexing in the future to enforce data constraints when writing.

[walker-philips]

Sure, our ideal solution is that we can seamlessly stream certain data from a Postgres instance where heavy ETL and business processes exist into a more analytics friendly database. We'd like to structure the analytics database with dbt and also control granular access using Postgres roles (i.e. a marketing user shouldn't be able to see financial datasets). We would be using dbt Core but I feel like dbt really operates best if you have full flexibility to materialize dbt assets as you need them. Seem's like the dbt table materialization would entirely depend on write operations being possible. As far as materialized views, I personally do not use them a lot anymore as opposed to dbt's incremental or just the table materializations since it essentially is a materialized view when its managed by dbt. I suppose though you could essentially recreate most dbt materializations in Bemi via just materialized views and views if that is an existing capability?

Just to be clear, our use case would not need actual write support in the sense that end users would be personally inserting rows or other data be loaded into the database outside of the BemiDB sync command/parquet files.

As far as extensions go, I think it tends to be use case specific but things like fdw, pg_vector, pg_trgm, postgis, etc.