Hacker News Reader

The tooling behind the approach has been built as a set of python package named Mara. It is available at GitHub:

And additional packages can be found at the Mara org:

And if say that if you are in the position where you can run DW workloads on your prod database you probably don't need a data warehouse in the first place.

Data warehouse workloads tend to be very IO intensive and could be highly disruptive to a production db. ETL is hard but it's a price to pay to isolate these two very different workloads.

DW is a capability, not a scale problem.

DW lets you gain insight from your available data, whether there's a lot of data, or not.

DW with postgres is probably the right choice if your data isn't petabytes level.

DW is a scale problem, what you are describing is data analytics.

DW has always described the storage solution not the workload.

From a company perspective, DW is an implementation detail for the utility it provides, which is analysis and reporting. These activities have such different lifecycles, accountabilities, and controls, to operational data stewardship, that the pattern of a BI team operating independently from the corporate IT unit may arise by governance pressures alone. Differences in storage architecture and data schema aren't considered when it comes to carving up the org chart, but they're certainly enabled by the consequences: in my experience of large-to-very-large enterprises it's common for BI teams to take practically vertical control of their entire stack, from the racks to the apps (or in public clouds, one or more separated accounts), and all the infrastructure in between, a classic sighting of Conway's law in action.

More than once I've pointed at such teams as an explainer when pitching a service-team approach to standing up a new application. (Caveat, lest it backfire if you try the same, check who you're talking to first; they're not always fans of "the BI gang")

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.

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.

This is the problem with business people being let loose to sell ideas without the technical depth.

A data warehouse is not a capability, it's a data system. Company A with a warehouse and company B without do not necessarily differ in any capabilities.

You can run the workloads on (possibly partial) replicas of the production database running on another set of machines.

Re: renaming tables - yay! It's essentially double-buffering for databases.

I've used a slight variant of this in the past: I'll have a table (e.g. my_schema_new.my_table) that gets updated by an ETL pipeline. I'll then also have a matview (e.g. my_schema.my_table) that's just SELECT * FROM my_schema_new.my_table. As long as I can give this matview a UNIQUE index, I can then REFRESH MATERIALIZED VIEW my_schema.my_table CONCURRENTLY for zero-downtime updates.

(Of course, if you're using this in a non-interactive data warehouse context, you might not care so much about zero-downtime updates; this is more for application-facing views. The REFRESH ... CONCURRENTLY pattern can be faster for incremental updates, but often struggles with larger changes to the underlying table, as it's essentially applying a diff between the two versions. Also, it only works in cases where your users can tolerate data that's as stale as the scheduled time between REFRESHes.)

> Instead of updating tables build their replacements under a different name then rename them.

This works really well, although a few minor warnings.

If you run backups, they will block DDL changes like creation and rename of a table. Now this might block all reads on your table until backup completes. Make sure you do this on a schema that isn’t being backed up or do other workarounds.

Other than that, this trick is really nice for instant replace and can also reduce disk space churn / fragmentation.

This works well until it doesn't and you're incrementally adding time to the loads. Some DBs handle it better than others. What most are looking for is performant, less constrained materialized views. If you're running ELT then MVs are a big deal. Additionally some DBs (SQL Server) don't have an option for pure "no logging" so, even with simple, there's overhead to the large writes.

I like schema swaps too for the tables rather than renames but the pattern is the same.

In the above mentioned scenario there is no need for backups as the database (or more precisely the schema) is recreated at each run.

That first point is exactly what dbt (https://www.getdbt.com/) does. It's an amazing tool, with extremely well thought out developer ergonomics.

Snowflake + dbt is great for this. Snowflake have a swap database function so instead of doing this at a table level you can create an entirely new database and do a blue/green style deployment. Not sure how this works with pgsql, but I suppose more complex.

You’ve described the ELT approach that is why dbt is taking the valley by storm.

And if you use a replica for analytics the overhead on performance is tiny!

This should be the first step every company does as it scales out. If you can no longer run analytics against your production database for whatever reason, fire up a secondary replica with a 12/24 hour delay and you're golden.

> - Instead of updating tables build their replacements under a different name then rename them. This makes updating heavy-to-compute table instant

isn’t this what materialized views are for?

I agree that a materialized view is a lot more ergonomic and probably the correct choice in most cases.

Refreshing a materialized view blocks selects from it though, rendering it inaccessible while the refresh is taking place. There is the CONCURRENTLY option but that may be more costly and requires a unique index on the view.

There's also the issue of rebuilding several dependent views. Say you have 3 dependent views that are all being shown in some dashboard: 1, 2 and 3. You refresh view 1 OK but view 2 fails for some reason. Now view 1 and view 3 are out of sync and view 2 is broken.

Encapsulating the 3 dependent views in a schema and only doing the name swap once all steps are OK prevents you from ending up in this state. If view 2 breaks you wont do the name swap so you'll be serving stale data, which is probably better than no data for 2 and out-of-sync data for 1 and 3, until you can fix it.

Thank you for the elaborate response. So I think the question is how near time your view data needs to be. Especially for reporting purposes (which is probably the most common use case of data lakes) it’s often acceptable to have data ready per T-1 (in terms of business days). For financial reports, last day’s close prices of assets is usually what’s being used. So, being able to relax requirements in this manner can reduce a lot of complexity.

Absolutely. Complexity in our own system pretty much exploded when we wanted to serve stats at near "real-time" (a couple of seconds delay). If business could just chill out and wait until next day, or even like an hour, we could have saved countless engineering hours. Such is life.

Why would developers need to learn another tool? They are all just SQL at the end.