Hacker News Reader

Comment by pm90 | Hacker News Reader

I hate to admit this, but I’m definitely one of the folks who isn’t a fan of the BI gang that operates in my workplace. They seem to have a very superficial understanding of how anything works. Their stuff constantly breaks on schema changes, and their response is never “hey, this seems like a broken workflow. How do we improve it?”. Its always “@here our DW may be broken but we will get it back up soon” and radio silence as they try to privately figure out how to fix something they don’t quite understand.

Not to mention that they have hardly any monitoring/alerting, which means that the data in the DW is not reliable, which can be really frustrating for users.

Overall I get the sense that these are unskilled folks who oversold what they could do but ultimately have very little clue what’s really going on.

View on HN

A more common experience with BI teams than people are willing to confess to, especially when the business the BI team is meant to be tracking and measuring is growing faster than the team can manage. In my experiences and and observations, this phenomenon where a BI teams' processes/products are unstable is usually because of a team's lack of depth in DBA skills. Not necessarily the lack of a DBA, but the lack of any engineer with any eye or attention given towards environmental stability, optimization, and the traditional "janitorial" clean up work that can tend to be neglected in the BI space.

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.

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.

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 :)

> 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

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.

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.

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.

> 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.

Until I read the second I was going to say to your first paragraph "then the company clearly doesn't value BI enough to warrant the existence of a BI team".

It's probably more a symptom of upper management valuing neither the core team nor the BI team, thinking software writes itself, and business insights come for free with the MBA. We're really in the same boat here. All the more reason for core team to make nice hooks for the BI team. They won't be appreciated by management, they probably won't be appreciated by the customers, but at least they can be appreciated by each other.

In my experience (which doesn't mean I'm disagreeing with your experience), it's not that upper mgmt doesn't recognize the value of BI - they just don't want to invest in it. They want their cake without having to pay for it. It can be hard to find a place with mgmt that really understands that investing in developer velocity and good infrastructure pays way more dividends in the long term than constant short term "promise X, deliver X*0.8 to make customer think they got what they want, and repeat to get the contract".

But yeah, we could complain on and on about all the problems of shortsighted management... it's a tale as old as time.

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.

Reply on news.ycombinator.com