3 ms·

If the BI teams stuff constantly breaks because production databases change I would say the software engineers are doing a shit job. It’s the engineers responsi

If the BI teams stuff constantly breaks because production databases change I would say the software engineers are doing a shit job. It’s the engineers responsibility to communicate such breaking changes in advance to teams that rely on their products.

pm90 5y ago
I see your point, but Engineers should not have to worry about dB schema changes breaking anything. The contract they provide externally is via APIs. The BI team slurping up dB binlogs must not create an expectation of support from engineers.
- Jweb_Guru 5y ago
  That's not true as soon as anyone else is relying on your schema (and data warehousing certainly does). At that point, you're maintaining an API contract whether you like it or not. Fortunately, databases also provide mechanisms for abstraction (like views) that can make this much easier on both the developers and the DBAs, which is an approach I've personally had good success with in companies with similar requirements. The key is to stop thinking of your database as an application-specific store and start thinking the same way you do for normal development--which tables and schemas are internal and subject to change, which ones are exposed to the outside world, what your stability guarantees are, etc. etc. Indeed, you can even guarantee backwards compatibility by versioning views you expose as APIs to other applications, and having each depending application create a view in its own schema that references the view--in Postgres, this means that only backwards-compatible migrations to the original view will go through! So when you find you can't make a change you need backwards-compatibly, you know it's time to make a new version, and you can keep the two versions of the API in sync by recomputing data in the new view to the old one until the depending applications can upgrade :)
  - Tabular-Iceberg 5y ago
    > That's not true as soon as anyone else is relying on your schema Well, don’t do that then. I think the parent comment suggested that it was an anti-pattern. Martin Fowler seems to agree: https://martinfowler.com/bliki/IntegrationDatabase.html https://martinfowler.com/bliki/IntegrationDatabase.html
    - Jweb_Guru 5y ago
      With all due respect, Martin Fowler's opinion on this matters a lot less than those of the people you're actually working with. There are many advantages to exposing your data this way, particularly performance and transactional integrity advantages, that are extremely difficult (and sometimes impossible) to recover otherwise; and like I said, there is no inherent reason why presenting an abstract contract as a view needs to make evolving your codebase any harder than presenting one as a REST API (or whatever your other preferred choice is). It "ties you to the database you're using" only in the sense that anything you don't expose through the database won't enjoy the same performance optimizations and transactional consistency, which would be the case anyway if you used a second database. The scenario presented on the website (a separate organization controlling the database schema and application developers having to negotiate with them to get what they want in) is not what I'm proposing here anyway, and seems to be deliberately conflating the use of the database as an API and some sort of organizational pattern that is mostly orthogonal to that. There is no reason applications can't have their own private schemas that are managed by the application developers, who then choose what data to expose as public (and what as private), while still sharing a database with other developers. And for applications where database operations are particularly performance sensitive or safety critical, there's also no reason why not exposing your schema to other applications implies that application developers are allowed to have full control over it. I've been in organizations at various points in time that do both of these things. Of course, there may be other restrictions that make this a bad idea (such as security restrictions that for whatever reason can't be enforced within the database), but for consumption for BI processes this is not really applicable anyway.
- ttz 5y ago
  So if engineering team uses an ORM, pushes a massive API change that renames columns and removes others, and drops FKs, and leaves it as a change item in a release list in an access controlled repo without notifying anyone, it's BI team's fault the DW ETL breaks? IMO it's not fruitful to throw blame on teams or call them unskilled. Usually, everyone is trying their best to do their job, and things that make this not happen should be seen as "how can we improve/fix this" - better communication is almost always the solution.
  - Tabular-Iceberg 5y ago
    It’s the engineering team that creates the actual value for the company. Of course they should have an easy path to clear technical debt. Database level integration is tempting at first since it’s zero effort up front, but it usually ends badly. It’s well worth it to make the BI team go through an API, even spend the effort on a well-designed one to give them a nice experience doing so.
    - ttz 5y ago
      > It’s well worth it to make the BI team go through an API, even spend the effort on a well-designed one to give them a nice experience doing so. In an ideal world yes. But sometimes, the engineering team wants nothing to do with the data itself, and so the BI team is often left to do what they can with what they have in order to do their job at all. Being a data engineer, both sides of the coin often fall on me, and so I understand the difficulties on both side. Almost always (anecdotally, so take with grain of salt), the problem lies with upper management because they aren't willing to invest the time to actually build a proper infrastructure in place; the focus is on building features to satisfy clients, without much thought on how this can affect the overall internal system, and so both sides have to rush to do things without thinking how it affects the other side. However, it likely won't change because at the end of the day, the client is happy - whether or not this is a "unfortunate reality" of business/tech, is a discussion for another day.
      - Tabular-Iceberg 5y ago
vincnetas 5y ago
Im exactly at this point now, where we have Bi which breaks from time to time. Sometimes because of infra sometimes because of source data model changes. Im now thinking how to prevent the second one, my plan is to create DB views for data that BI needs so that source system would be notified automatically notified when trying to change schema. In a sense view will become API for data. Yes and monitoring is a must have for everything.

pm90 5y ago

I see your point, but Engineers should not have to worry about dB schema changes breaking anything. The contract they provide externally is via APIs. The BI team slurping up dB binlogs must not create an expectation of support from engineers.

Jweb_Guru 5y ago
That's not true as soon as anyone else is relying on your schema (and data warehousing certainly does). At that point, you're maintaining an API contract whether you like it or not. Fortunately, databases also provide mechanisms for abstraction (like views) that can make this much easier on both the developers and the DBAs, which is an approach I've personally had good success with in companies with similar requirements. The key is to stop thinking of your database as an application-specific store and start thinking the same way you do for normal development--which tables and schemas are internal and subject to change, which ones are exposed to the outside world, what your stability guarantees are, etc. etc. Indeed, you can even guarantee backwards compatibility by versioning views you expose as APIs to other applications, and having each depending application create a view in its own schema that references the view--in Postgres, this means that only backwards-compatible migrations to the original view will go through! So when you find you can't make a change you need backwards-compatibly, you know it's time to make a new version, and you can keep the two versions of the API in sync by recomputing data in the new view to the old one until the depending applications can upgrade :)
- Tabular-Iceberg 5y ago
  > That's not true as soon as anyone else is relying on your schema Well, don’t do that then. I think the parent comment suggested that it was an anti-pattern. Martin Fowler seems to agree: https://martinfowler.com/bliki/IntegrationDatabase.html https://martinfowler.com/bliki/IntegrationDatabase.html
  - Jweb_Guru 5y ago
    With all due respect, Martin Fowler's opinion on this matters a lot less than those of the people you're actually working with. There are many advantages to exposing your data this way, particularly performance and transactional integrity advantages, that are extremely difficult (and sometimes impossible) to recover otherwise; and like I said, there is no inherent reason why presenting an abstract contract as a view needs to make evolving your codebase any harder than presenting one as a REST API (or whatever your other preferred choice is). It "ties you to the database you're using" only in the sense that anything you don't expose through the database won't enjoy the same performance optimizations and transactional consistency, which would be the case anyway if you used a second database. The scenario presented on the website (a separate organization controlling the database schema and application developers having to negotiate with them to get what they want in) is not what I'm proposing here anyway, and seems to be deliberately conflating the use of the database as an API and some sort of organizational pattern that is mostly orthogonal to that. There is no reason applications can't have their own private schemas that are managed by the application developers, who then choose what data to expose as public (and what as private), while still sharing a database with other developers. And for applications where database operations are particularly performance sensitive or safety critical, there's also no reason why not exposing your schema to other applications implies that application developers are allowed to have full control over it. I've been in organizations at various points in time that do both of these things. Of course, there may be other restrictions that make this a bad idea (such as security restrictions that for whatever reason can't be enforced within the database), but for consumption for BI processes this is not really applicable anyway.
ttz 5y ago
So if engineering team uses an ORM, pushes a massive API change that renames columns and removes others, and drops FKs, and leaves it as a change item in a release list in an access controlled repo without notifying anyone, it's BI team's fault the DW ETL breaks? IMO it's not fruitful to throw blame on teams or call them unskilled. Usually, everyone is trying their best to do their job, and things that make this not happen should be seen as "how can we improve/fix this" - better communication is almost always the solution.
- Tabular-Iceberg 5y ago
  It’s the engineering team that creates the actual value for the company. Of course they should have an easy path to clear technical debt. Database level integration is tempting at first since it’s zero effort up front, but it usually ends badly. It’s well worth it to make the BI team go through an API, even spend the effort on a well-designed one to give them a nice experience doing so.
  - ttz 5y ago
    > It’s well worth it to make the BI team go through an API, even spend the effort on a well-designed one to give them a nice experience doing so. In an ideal world yes. But sometimes, the engineering team wants nothing to do with the data itself, and so the BI team is often left to do what they can with what they have in order to do their job at all. Being a data engineer, both sides of the coin often fall on me, and so I understand the difficulties on both side. Almost always (anecdotally, so take with grain of salt), the problem lies with upper management because they aren't willing to invest the time to actually build a proper infrastructure in place; the focus is on building features to satisfy clients, without much thought on how this can affect the overall internal system, and so both sides have to rush to do things without thinking how it affects the other side. However, it likely won't change because at the end of the day, the client is happy - whether or not this is a "unfortunate reality" of business/tech, is a discussion for another day.
    - Tabular-Iceberg 5y ago

vincnetas 5y ago

Im exactly at this point now, where we have Bi which breaks from time to time. Sometimes because of infra sometimes because of source data model changes. Im now thinking how to prevent the second one, my plan is to create DB views for data that BI needs so that source system would be notified automatically notified when trying to change schema. In a sense view will become API for data. Yes and monitoring is a must have for everything.