Modern data modeling: Start with the end?
adventofdata.com
adventofdata.com
It’s like there’s some obsession with ignoring any kind of practice in wider software engineering, or innovation in the PL theory space, in favour of…gluing sql together using string templates?
It seems like for the most part-there’s very little innovation or progress in the space. It’s just 15-variations on a theme of configure some yaml for your ninja-interpolated strings to dump ever more data into your cost-centre black hole known as a “modern data lake”.
I’m sure there’s some interesting things going on in small corners, but it’s difficult to find, and if it exists, it’s being studiously ignored by mainstream tooling.
species: str = 'acer'
structure: str = 'trees.table'
query = f"""
SELECT * FROM {structure} WHERE species = {species}
"""Is it something you've written and maintain yourself? What testing do you have on them?
I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel. It's not some godlike piece of software, just a helpful framework wrapper around plain old SQL
Underlying SQL lives in git, along with the code. Deployment happens via normal CICD pipelines.
> What tooling do you use to refresh those materialized views on a schedule?
A database that automatically rebuilds views when the underlying table changes (ClickHouse), or if I’m using postgres, a “dumb” job that simply updates the views. Periodically.
> Is it something you've written and maintain yourself? What testing do you have on them?
Written and maintain myself? Not really, it’s either Spark or Rust code that’s a very thin wrapper to drive the underlying SQL. Everything runs in Kubernetes, as either plain deployments (for ongoing/streaming workloads) or cronjobs for periodic workloads. SQL and wrapper code are tested using the normal in-language testing tools.
> I find that dbt answers all of these questions pretty simply in a way that avoids the need to reinvent the wheel.
I don’t think people not using dbt are reinventing the wheel, in my case I’m not doing anything that wouldn’t be considered exceedingly boring in architecture and usage.
Avoiding weird proprietary tools also means that the technology and approach used is almost identical to what the devs use, which means more shared knowledge and better reusability.
I didn't get it either. I thought it just looks like SQL with some complicated additions. Then I did some work with it and the flow got way better. We could work a lot more incrementally and as a larger team rather than as individuals tripping all over each other. Maybe there is a better way doing something else, but this clicked with the team.
Not sure I get your cost centre black hole comment, but for the most part storage is so cheap that it isn't a concern. ELT has made it pretty easy for us to do less analysis and work on the data gathering side, which is low value, and do more on the modeling side which is higher value. Further, analysts can play with the raw data and find what they want and if they like it they will give it to us to model in a formal way. No need to work with an ETL team and application team to bring the data over. In ye olden times, this was overthought and created friction - "If you don't know what you're going to exactly do with the data how am I going to know what to bring in and how to land it"."But how am I to know what to do with the data if I can't query it and see what opportunities there are."
On the unwieldy part, sounds like you've had a bad experience. Ours has been the opposite. Really easy to setup and use. Easy incremental step for the team to get their heads around. Integration with git and snowflake was really easy. I'm still a bit worried that something is going to get really messed up with the declarative approach that dbt has, but we've not seen any issues yet.
I've been curious as well about what more can be done to make the development lifecycle better. Most of the other "big data" approaches that I've worked with seemed to make it worse not better. A lot more complexity, not thinking in sets, layers and layers of nothing I care about that can go wrong. I can't believe I'm still working with SQL.
Interesting, what other approaches and tools are you thinking of that are worse/failed/etc.
All the promises of the power of these platforms was lost in the additional effort in managing the complexities. Developer productivity for the work that I've been doing is way more important than hw/sw costs. I can't think of anytime that this trade worked better where I had more focus on the sw/hw costs rather than developer time.
I'm convinced this entire space should be visual. We always visualize data pipelines in our minds and on whiteboards...well, visually. Two-way code-diagram syncing is needed to allow best of both worlds. We should serialize to yaml from imperative code, but allow manipulating the diagram to modify the imperative code.
Ideally what you want is also to track the dependencies of every atom of data in your org, and then have something cache and incrementally compute updates.
The biggest thorn in this vision is SQL and the relational model, and I don't think a lot of people realize it.
It favors representing data such that the query planner can optimize query execution...rather than being able to track data dependencies and visualize data flow. It wasn't designed in mind with complex ETL pipelines and many external data sources of today's world.
At my last 2 jobs I spent entirely too much time debugging Matillion jobs, which are visual. I have my doubts that it’s the panacea that it appears to be.
That said, you may find Enso particularly interesting: https://github.com/enso-org/enso
I'm also convinced by this! Currently building something like this at https://www.ilumadata.io/ We have a pipeline builder (not yet on our website) that lets you visually build out the data pipeline. At the same time it's creating the corresponding files for each node which you can switch to.
I think it is easy to lob complaints at sql. The problem is every successor seems worse. Teams I've been close to lately have taken the python pill. Pandas, they push, is so much easier to understand. This works too an extent. But what we have dodged large sql files, we now have giant python files. Worse, they are trying to engineer things in "logical" ways for code sharing.
What I'm seeing is they moved all of the complexity of most ETL engines into each python file. This feels great at the start, but is like how every Todo list project looks clean in basically any language. Ramp up the variety and size of data sources, and get ready for that clean idea to turn into the mess it thought it prevented.
For my two cents, if your ETL setup doesn't factor in Conway's law in who owns different parts of the data, it will fail in time. Trying to hide split ownership of data is a sure sign that changes will get misunderstood and data will be lost or misused.
I think the key is to not make everything visual. You want to be able to view your system as you would draw it on a whiteboard. I believe it could be done using a restricted syntax of a proglang.
> Teams I've been close to lately have taken the python pill...
Most people have a desired result and want it as fast as possible.
When imperatively using map, groupBy, filter, you can usually get the immediate result you want very quickly. This is the appeal of Pandas, Python notebooks, etc.
But when someone asks: I want to re-compute on change, which pretty much applies to every task...their quick solution is not optimal.
And then if the schema changes, and you're doing all this intermediary stuff...then refactoring is next to impossible.
Any SQL query could also be written in an imperative prog lang and its probably easier to understand and tweak and also trace. An SQL relation does not maintain any info about its source, which then makes incremental updates difficult, and also refactoring.
You can basically ask the question: for every piece of data you see in your company, can you easily see the "data lineage" from its source and all the transformations it was involved in, and how clean and efficient is this pipeline when receiving new data. Then a step further is, how easy is it to change something.
Such that I agree being able to visualize things is nice. But don't confuse the visualization with the system. A step you have to make in many visual tools.
I'm pretty much agreed on most of what you wrote, though.
I think this is where people have got it wrong in the past.
This tool is an example of something in the right direction: https://www.ilograph.com/
It's just completely disconnected from code.
And the very valid counter claim from folks that do some of this is that there needs to be some mechanism to keep things synchronized between the visualization and the execution.
Tables, their attributes, relationships etc. are easily able to be represented visually. Entity-Relationship Diagrams have standard notation (Chen, Crows-foot, UML). We can represent models conceptually, logically or physically, we can even indicate the existence of views and indexes. In terms of queries, DML is essentially multi-filtered, multi-dimensional slicing of tables and can be represented as output sets which are as modellable as the source tables.
On the wider point, DBT looks promising. It seems to be a good middle ground between the purists (like me) who wish back-end devs would just learn how to model data, and the adventurists who prefer to lock everything behind OOP principles.
When a source table changes, I want it to automatically and efficiently update anything that would change. I think pretty much every system would prefer real-time stuff like this.
If you are doing this with SQL you will start looking into "Incremental View Maintenance" which is quite complex and still quite heavy.
Then you realize that if you take control instead of handing it off to the planner, you could code your joins and transformations by hand, cache intermediary steps as needed, and have a query that is more efficient than SQL.
But for this I would argue you need a better way to visualize your data flow and the dependency graph, because people can easily write slow imperative stuff.
SQL is amazing, I’m not convinced string templates and this one tool offer enough value to be worth the hype they receive.
No I get it, land your data, apply transforms land it, repeat. I’m already following this approach, so maybe the value prop of dbt is lost on me.
My concern is that I’ve worked in places where the proverbial “reigns” of “do whatever” with the data were handed off to anyone who asked for them, and I was involved trying to fix the inevitable mess that was created. There’s zero coherency, so 8 different people create 5 different, incompatible reports for the same thing, mishandled money and date values everywhere, etc. That’s not an experience I wish to relive any time soon.
1. One source of transforms for static data 2. Easy to do plug-in or changes, even 'materializations', are Jinja. So I can have custom implementation for a specific issue 3. DBT to Metabase documentation, still super new but imagine other tools getting intake of metadata and metrics from dbt 4. I'm the data team, and managing a huge amount of workflows. If I just use a select and not DML style of writing the queries, it will 'orchestrate' it for me. 4.1 I can have a quick version controlled workflow for a new report/kpi/metric that we don't know if it is even important yet. 5. The speed trade-offs are made up for with the time saved from point 4 6. Embeddable in to the datamesh I dream of no one knowing I use DBT or any software at all, its all just [company branding] magic
This whole really just makes me want to write an alternative to dbt that does this stuff, but better. I’m convinced there must be better solutions to sql composability than string interpolations and too-many-yaml-files.
How are you going to do that when your comments betray that you barely know what dbt does in the first place?
> solutions to sql composability
dbt isn't a solution to SQL composability, but it offers something similar in spirit because of how its DAG-based processing works.
God forbid I ask questions to fill in my knowledge gaps. “Barely know” is a bit strong, I understand what it is, I don’t personally see the value proposition. The question about composability is hence a proxy- if I don’t see the value prop, I need to understand what the underlying benefit is from people who do value it. To me it just looks like some shiny bells and whistles on top of gluing a bunch of sql execution together. Other products have failed at that, so it’s got to be slightly more than that. The other thing it offers is composition via string templates, and seemingly, some level of orchestration as a direct result. Sometimes you have to ask “dumb looking” questions to make sure you don’t miss things.
> To me it just looks like some shiny bells and whistles on top of gluing a bunch of sql execution together
So is Pentaho, Matillion, Tableau Prep, and most tooling that enables this kind of work on an RDBMS. At the end of the day, they're all just generating SQL strings from (usually proprietary) metadata. dbt lets me skip the UI bullshit and focus on soup-to-nuts data modeling in pure SQL, and doesn't try to hide the fact that we're working with an RDBMS or that we're spitting out SQL strings via Jinja.
> The other thing it offers is composition via string templates
Here's an example of one of those statements. What it does do is provide the ability to generate dynamic SQL using a mustache-style template syntax via Python/Jinja, giving SQL authors the ability to further exploit that combination to aid in dynamic SQL generation on an as needed-basis. You can use as much-or-little dynamic generation as you want, it's up to the SQL author to be disciplined in how it's applied.
Most models I've seen have little-to-no-dynamic SQL, outside of incremental load predicates in the WHERE clause. The few instances I can think of top-of-mind are using things like information_schema to dynamically generate lists of columns for self-updating models.
What you're thus "composing" is a DAG of views/tables generated via dynamic SQL. It would not be accurate to say you're composing an individual SQL model, since you're really just stitching strings together. This is an important distinction, because "composition" is a terribly overloaded term in software and in this context might lead the reader to believe dbt is trying to re-invent SQL via a DSL that supports composition, which is clearly not the case.
You won't even find the word "compose" on the dbt docs that isn't in reference to Docker.
> some level of orchestration as a direct result
Each dbt model becomes either a view or table, and dbt SQL authors can build models using other models as if they were existing views/tables. Under the hood, dbt parses models pre-exec to build a DAG to determine what order views/tables need to be created. The SQL author doesn't need to do anything more than include the model via {{ ref(model_name) }} macro in a template.
dbt is about embracing the database and exploiting it to the best of its capabilities, rather than delegating to an intermediary to fill in the missing pieces. I don't need Java to split a string or concat an array of values in 2022, when most vendors now have incredibly robust built-in functions or UDF support. I don't need Airflow to define workflow order. I don't need to bug IT to stand up infra to run software they have to manage to support my workloads.
What you get is:
- Project structure and conventions for a team to collaborate on SQL-based data models
- Tooling that ensures data & transformations don't need to leave the database
- Tooling to generate SQL and execute statements in the order of dependencies, including parallelization
- Built-in support for incremental loading and snapshots
- Ability to control how models are managed (view vs. table) and optimize as needed
- Conventions for enriching metadata via YAML (such as for docs)
I currently have a small 2TB database on Snowflake with ~100 models that I can rebuild from scratch in about ~5 mins. I had to spend more time learning Snowflake syntax than I did having to learn or support dbt.
It lets me write test first sql transforms. I never thought TDD sql would be possible. My sql is so much more readable with common logic extracted into ephmeral models. I practice same method to write clear code to write sql, eg: too many mocks = refactor into separate model ( class) .
I think DBT made this possible with refs that can be swapped out with mocks. This is the awesome library I am using https://github.com/EqualExperts/dbt-unit-testing
Sounds great. My concern though with DBT is the cognitive overhead and just plain ... weirdness and clunkiness it seems to introduce as a cost to obtaining benefits like these.
For example if we look at their own documentation:
When using the view materialization, your model is rebuilt as a view on each run, via a create view as statement.
https://docs.getdbt.com/docs/build/materializations
Now, isn't your reaction, upon reading this, some version of: "Whaaat? Views are not materializations - by definition, they are essentially the opposite of materializations."
And yet here the DBT documentation is describing the results of plain old "create view" statements as materializations.
And then we're not getting into even more basic issues - like why we're supposed to believe that code like:
select
...
from {{ source('jaffle_shop', 'orders') }}
left join {{ source('jaffle_shop', 'customers') }} using (customer_id)
select
...
from raw.jaffle_shop.orders
left join raw.jaffle_shop.customers using (customer_id)
I'd never heard of dbt before today but from a quick scan of that page it's clear to me what a that 'materialization' is in dbt. It's not the same as a SQL 'materialized view'; it could be a SQL view, an SQL table, a CTE, or a table with extra machinery for incremental updating.
In my experience in software it's common for similar words to be used for different concepts in different contexts. I think the dbt documentation is well done so I don't see a big issue.
Right, but when it happens it's usually because these words get used differently in different domains.
What's happening here is that they're carelessly (in my view) mangling a well-established concept in a field (speaking of the SQL and database world) where folks are (notoriously) careful about word choice and about what things actually, in fact, mean.
It just feels weird, and on a certain gut level - koolaid-y.
They’re some products that have inexplicably captured so much s as attention for so little value and now trounce around acting like their approach is the only true way of doing things.
I went through the library you linked, but I’m really, really not convinced. This just looks like a messier, more unclear way to test some SQL. A feat you could with some spark code (for example) in a significantly less confusing and boilerplate-y manner.
I think the benefit is that dbt appears to give some cleaner way to separate and the re-compose SQL. Which is good, we do need that. However it does it in the most awkward and unwieldy way possible (string templating) and yet it’s heralded as “the true way forward”.
sql vs spark is probably a discussion for another time :0
> This just looks like a messier, more unclear way to test some SQL.
As a software engineer I've written my own share of unit tests. They are supposed to be documentation of how a unit might work but it rarely ever works in that way.
I found that to be more true for CSV tests for sql
https://github.com/EqualExperts/dbt-unit-testing#different-w...
because they are always in the same format , input csv -> output csv
I am more motivated to look at the tests to see what sql is doing. Its pretty straight forward.
Author here: yeah I ended up insisting a bit much on dbt as a basis although the method I'm describing in the article can definitely be applied to any kind of modeling framework (or lack of)
Anyway, please let me know what you think of the general method and the bottom-up VS top-down approach ;-)
> In contrast, the approach I want to outline here is primarily bottom-up, in order to:
- deliver value and insights as quickly as possible
- not pile up too much (data modeling) technical debt along the way.
> The goal will be to aim towards either: - a dimensional model/star schema - or a limited collection of "One Big Tables" (or any other top-down theory you’re most comfortable with...)
I get (and agree) that you want to start off organic and get things moving with some integrations, but I quite strongly feel that with the suggested approach (which feels to me like - just start with the dbt part, don't worry about the final design part), especially if you don't decide _up front_ what capabilities you, your team, your client have, and choose an approach based on that, you'll likely end up with something of a technical debt mess.
My suggestion would be, unless otherwise advised, to just adopt the dimensional model, put that in the hands of your users early, and start getting feedback. In the meantime you can do exactly what is described, but with a feedback loop kicked off much earlier than you would have otherwise.
edit - maybe contradictory is not correct, the suggestion stands though :)
I have nothing against dimensional modeling per-say, just the uptime effort (and initial feedback lag) that it generally brings with it in the beginning.
Another issue I've observed with teams trying to formalize their modeling "too" soon is a confusion in the models created. I find that it's sometimes difficult for teams to understand what should be in each model, how to separate the different dimensions, etc. Hence my emphasis on starting naively in the beginning and then aiming for that dimensional model when things start becoming clearer...
The outcomes are a mixed bag, but my biggest experienced failing was on a project starting naively with OBT and waiting for things to become clearer, it did indeed soon became clear that we should have taken a more methodical approach, as we had the dual problem of huge dependencies and a very brittle data model!
(I don't think there is an easy answer here btw, I am hoping for someone to describe a detailed and relatively generalisable playbook, hopefully soon!)