I completely agree with the sentiment here. As an engineer tasked with building data driven systems and architectures, I'm well aware of the amount of buzzword / enterprise nonsense floating around in this space - and it is enormous. You can couple that with the fact that its really difficult to find any practical books and resources that you can actually apply to solve real-world problems when working in small-teams and on quick deadlines. Getting business to act in a data driven and analytic way, and building the architecture for it - is a non-trivial task. This looks like it could be one of those rare good resources in the space.
Thanks! We're truly looking for this to be community driven as well. So if you see places to contribute, or where you might disagree, or where you could share a story - do let us know or make a pull request on GitHub!
Besides the need for a new data book, we realized that it needed to be of a different format, as the space is moving so fast the expertise is very distributed.
I've recently dug into the Agile DWH Design and The DWH Toolkit books and the design tips in them all seem really compelling, for good reason. Though as I've actually started modeling, I've found that the creation of "proper" fact/dimension tables has felt at times like overkill, given the technologies we're using (BQ / Postgres / Looker).
So, perfect timing! Really looking forward to checking this out.
Rediscovering the wheel much ? You guys are starting again like 30 years ago, with simple queries and think all that was built is overkill. When you mature and get into complex analytics and joining data from 20 sources you will understand all that was built. Just you wait...
We're definitely not trying to start from scratch or throw out all the old knowledge/practices - just update them for the common data stacks used today.
In the book we use much of the old terms and recommendations. Most of the high level organizing is still totally right - but a lot of the optimizing and work done for performance and cost reasons is very different now.
ELT has been around for 15+ years. Inmon refers to it as a Persistent Staging Area (PSA) in an Enterprise Data Warehouse.
The difference now is, you have Hadoop and cloud providers that will take credit cards and give you as much space as you can pay for. The concept is not new, it was just cost was a factor back then because capacity was fixed and memory was expensive.
the only thing that has changed is the commoditization of hardware has allowed for different behaviors that would have been cost prohibitive.
Yup! And that commoditization of hardware has made it really inexpensive to have a Data Lake, where you first put all your data in raw format (so you only need to do EL - and not T all in the same step). And then, because of the way C-Store sources like Redshift are built it makes a ton of sense to just do your T step as a set of Views (materialized or not) onto of that Data Lake.
It allows you to not do E & T & L all together. It's really nice (less complex, easier to implement, less costly, and more flexible) to have that T part pulled out and done after.
C-store's 1) improve aggregation performance since values are continuous on disk/memory for the same column and 2) benefit from bitmap indices when not doing range queries. Why do you think c-stores make a difference for the T stage? Because the performance overhead makes views viable?
Yes, and also the whole process is much simpler to pull the T out. The reason T had to be done at the same time as E&L was because of those storage and performance costs. Now you don't have to - and it separates the stages and simplifies.
The T being after the L means you can do that stage more simply in just SQL (with views or materialized views - possibly with the help of DBT), as opposed to some vendor interface, or python/R/etc script.
It also means that rebuilding your warehouse is much less significant of an ordeal. When the structure of source data changes or if you want to make some migration of the schemas of the warehouse you don't need to also re-run your ETL jobs and start over from scratch.
No one does just "ETL" anymore - ELT/ETL are more or less synonymous now. I can't remember the last time I talked to a team that was throwing data away anymore because of storage constraints. It's a straw man in 2019.
C-store doesn't solve for denormalization or provide any advantages when you're copying data all over the place to avoid joins.
1. It is - and that's why I chose that example (a less controversial one) here.
And though ELT is a very common standard now - I don't know of a single book that recommends it or explains why that change has happened. Just one of the reasons for writing a new data book.
2. C-Store does largely solve pervious performance and cost issues with denormalization. We also write a bit in this book (more to come) on how to avoid doing all that copying.
Yeah exactly. Much of the "overkill" was done because of performance and cost reasons, that frankly just don't apply anymore. Now the largest expense by far is time.
There are a number of people starting to talk about Star Schemas having little gain on modern stacks. The performance and costs gains are automatically done now with C-Store warehouses. Fivetran has a great post on this https://fivetran.com/blog/obt-star-schema
The reason a Star Schema exists is because it reduces the amount of data stored and it reduces the load times (faster updates) because only the facts and measures get updated. There is also little redundancy in Master Data (dimensions).
in the world of fixed assets (on-premise data warehouses) this was a concern, I'm curious to see how this plays out in these Cloud Providers because to me it seems like they will be more than happy to rent you as much space as you want and everyone is happy until the bill comes due.
Doing materialized, flat tables everywhere is great for reporting performance but the tables will not be updated as quickly, there will be redundancy in storage and it will be difficult to sync time dependent dimensions.
This is what I mean by updates due to C-Store warehouse engines (Redshift, BigQuery, Snowflake, etc). It's not just that the cloud providers are happy to rent you more space - it's also that they're C-Store and because of that redundancy in columns is well compressed automatically.
The architecture of the C-Store warehouses often removes the benefits of materialized views. This is why for a very long time Redshift didn't even support them - they insisted they weren't needed as they didn't improve performance over regular redshift significantly.
I see Panoply is part of this effort. Panoply creates a lot of spam on hackernews, reddit, twitter, and elsewhere.
As an example, doing ETL with drag n drop tools, and not in code, is a dying skill in the industry.
[edit] Basically I'm wary of companies commenting on data standards, where those same companies also sell a product in the data industry. You're probably going to find more honesty on guidelines from open source contributors like LinkedIn, Netflix, Sitchfix, etc.
One of the experiences that stuck with me most going from doing software engineering stuff to data engineering stuff is the very difficult and sometimes complete lack of tooling around testing and debugging issues in SQL and pipelines. As software engineers, we have debuggers, unit testing, mocking frameworks, e2e tools, BDD languages like Cucumber, code quality tools, etc. But when you're working on a pipeline and you're wondering what will happen if you run it, sometimes your best bet is actually to just run it and wait 20 min for it to complete. Or run some portion of it ad hoc. Or if you want to know what a SQL query is going to do in the black box of your query engine, you might try to parse the esoteric language of a query explainer. The best tools seem to be available after deployment such as data quality tests, dashboards and alerts. I think there's a lot of opportunity to improve the ecosystem.
Occasionally I ask what the world would look like if we had a web browser that was designed from the first to be testable. Would e2e testing be simple and repeatable?
I could just as easily ask the same about databases, or dozens of other tools in our toolchain. Makes me wonder if there's space for something like SQLite and testable.
Probably Datomic. Databases are hard to test because they're big piles of global mutable state. A database designed around values, pure functions, and other FP constructs should be inherently easier to test.
There are guys who generate synthetic testing data so you can get "looks like real data" test fixtures. I used to work in CRM with PII and honestly I just put in my own details as test fixtures more often than not where you want to run final integration tests on whether the whole thing actually handles 'realish' data (you have to filter out all the junk `example.com` and whatnot emails in prod). Should've used the synthetic data in the first place because someone at our company accidentally opted out the CTO while testing out the flow and our integration tests broke.
Agreed. SQL being declarative, is primarily why there haven't been many tools to augment the development process. Same goes for machine learning, albeit SQL is not fuzzy like ML. No way to know whether a model "works" unless you train and run it.
Agreed. Outside the world of ML (but working with reasonably large data, ie a few billion rows of server log entries in varying formats) I had some success grabbing a narrow slice (single-digit millions) and experimenting w/ ways to query it using combination of regex and SQL, thanks to an amazing little powertool called lnav (https://lnav.org). It's like an intuitive, scriptable, mini-ETL on the commandline you can learn in no time. I once used it to track down the root cause of a p0 in prod, in parallel to the ops gurus and most senior devs using dedicated tools and processes; we arrived at the same conclusion around the same time, and the stuff I'd extracted helped speed the patch. Felt pretty good, and I've been singing lnav's praises ever since.
Shoot I must be getting old, rambling on a tangent. Get off my lawn! ;)
I have started using prefect.io as a workflow runner. It helps me define tasks and deeply define what should happen on failure of a task including cleaning up a cluster.
Because Tasks are just functions you could test your tasks with regular unit testing and mocking methods.
But I agree a lot of data people seem to think interactive is good enough.
What makes prefect stand out is the highly pythonic API and the powerful primitives for defining failure scenarios. Additionally, for me, integrating with dask is key as dask has become one of my main tools.
> if you want to know what a SQL query is going to do in the black box of your query engine, you might try to parse the esoteric language of a query explainer.
Possibly a side effect of nosql movement/proliferation that these basic things became esoteric. Like a vet care for dinosaurs :) The tooling around data pipelines, like debugging, profiling, lineage, etc was too a typical stuff of the ETL tools of yesteryear like Informatica for example.
Just because people don't write tests and use tooling doesn't mean it doesn't exist!
When I write pipelines I write unit tests for the business logic and integration tests for the overall process.
Whether you use SSIS, Alteryx, Spark whatever you can write tests and you can debug using known inputs, rather than running an entire pipeline to see that is works!
Great to see more information about data out there for people to learn from. BTW my colleges and I created a declarative (SQL) open source (MIT) framework for Apache Spark to make ETL and ML super easy, if you want to check it out it you can read more here: https://arc.tripl.ai . We've recently started combining this with Argo and Delta Lake which is working well for us in the source, lake to warehouse stages.
This is just lovely. I'm the lead of a company's data warehouse project, having inherited it from someone who was a DBA that read a book and then made the whole thing ...
I tend to joke that I'm a digital janitor and now I'm a big data janitor. Well, things are going well enough, but cleanup on a live system with users and zillions of reports is incredibly difficult. Part of the issue is isolation and size: too many have too much access to too much data. It's overwhelming and it also leads to a lot of high cost because queries aren't understood and they're run against massive datasets.
I've been educating myself as well as I can between fixing errors and this book is just the thing I need to calm my nerves. It totally makes sense how the stages are set and even just this blog post overview has given me ideas of how to carve some things up.
I worry that this promotes the data architecture philosophy that is currently in vogue in tech companies, but is actually a bad fit for many traditional enterprises. Most of the time, data architecture needed depends on use case.
Tech/web companies deal with massive amounts of unstructured/semi-structured data ingested at a fast clip, so the architectural thinking here works.
However I would argue that many traditional enterprises whose major sources of data are primarily highly structured (SQL databases), a lake is actually not needed.
A young data engineer working in a traditional enterprise, enamored with the idea of data lakes say, might try to ETL SQL databases into an object store (add RBAC etc.) only to rebuild it back out into a data warehouse. This will almost always turn out to be the wrong approach.
The simpler and more manageable approach is actually to federate existing databases, add cataloging etc. and not even use a data lake at all.
I think by lake they mean s3 and similar alternatives (like hdf is for hadoop). This is not crazy as processing s3 files is quite easy. What you say is true but the problem is that no analyst deals only with one db, they have to deal with an increasing number of data sources, that’s what the lake is for. I also don’t believe you need a warehouse after the lake and that one source of truth is pipe dreams, so I’m with you this architecture is not 100% what companies probably need.
> no analyst deals only with one db, they have to deal with an increasing number of data sources
Which is why I mentioned federation. Most data sources in an enterprise are SQL native or accessible and it does not make sense to dump a SQL database into S3 just to be able to combine it with other forms of data.
Federation means you can operate across multiple databases (eg do Cross database joins etc.)
50 comments
[ 4.6 ms ] story [ 113 ms ] threadGreat work by the guys from chartio!
Besides the need for a new data book, we realized that it needed to be of a different format, as the space is moving so fast the expertise is very distributed.
So, perfect timing! Really looking forward to checking this out.
In the book we use much of the old terms and recommendations. Most of the high level organizing is still totally right - but a lot of the optimizing and work done for performance and cost reasons is very different now.
For example ELT makes now much more sense than ETL for the reasons Kostas wrote about here: https://dataschool.com/data-governance/etl-vs-elt/
And many things previously done for cost and performance reasons are just not relevant anymore thanks to the big innovations in C-Store warehouses.
The difference now is, you have Hadoop and cloud providers that will take credit cards and give you as much space as you can pay for. The concept is not new, it was just cost was a factor back then because capacity was fixed and memory was expensive.
the only thing that has changed is the commoditization of hardware has allowed for different behaviors that would have been cost prohibitive.
It allows you to not do E & T & L all together. It's really nice (less complex, easier to implement, less costly, and more flexible) to have that T part pulled out and done after.
The T being after the L means you can do that stage more simply in just SQL (with views or materialized views - possibly with the help of DBT), as opposed to some vendor interface, or python/R/etc script.
It also means that rebuilding your warehouse is much less significant of an ordeal. When the structure of source data changes or if you want to make some migration of the schemas of the warehouse you don't need to also re-run your ETL jobs and start over from scratch.
C-store doesn't solve for denormalization or provide any advantages when you're copying data all over the place to avoid joins.
And though ELT is a very common standard now - I don't know of a single book that recommends it or explains why that change has happened. Just one of the reasons for writing a new data book.
2. C-Store does largely solve pervious performance and cost issues with denormalization. We also write a bit in this book (more to come) on how to avoid doing all that copying.
There are a number of people starting to talk about Star Schemas having little gain on modern stacks. The performance and costs gains are automatically done now with C-Store warehouses. Fivetran has a great post on this https://fivetran.com/blog/obt-star-schema
in the world of fixed assets (on-premise data warehouses) this was a concern, I'm curious to see how this plays out in these Cloud Providers because to me it seems like they will be more than happy to rent you as much space as you want and everyone is happy until the bill comes due.
Doing materialized, flat tables everywhere is great for reporting performance but the tables will not be updated as quickly, there will be redundancy in storage and it will be difficult to sync time dependent dimensions.
We try to explain some of that here - https://dataschool.com/data-modeling-101/row-vs-column-orien...
And Fivetran did a great benchmarking of it here - https://fivetran.com/blog/obt-star-schema
The architecture of the C-Store warehouses often removes the benefits of materialized views. This is why for a very long time Redshift didn't even support them - they insisted they weren't needed as they didn't improve performance over regular redshift significantly.
would have loved to have this book when we were building this at my previous co.
very approachable in how it explains each segment on the whole and zooms in on them individually.
would have saved our team a lot of time as we came to very similar conclusions but over the course of a few months
As an example, doing ETL with drag n drop tools, and not in code, is a dying skill in the industry.
[edit] Basically I'm wary of companies commenting on data standards, where those same companies also sell a product in the data industry. You're probably going to find more honesty on guidelines from open source contributors like LinkedIn, Netflix, Sitchfix, etc.
https://docs.getdbt.com/docs/testing
I could just as easily ask the same about databases, or dozens of other tools in our toolchain. Makes me wonder if there's space for something like SQLite and testable.
Shoot I must be getting old, rambling on a tangent. Get off my lawn! ;)
Because Tasks are just functions you could test your tasks with regular unit testing and mocking methods.
But I agree a lot of data people seem to think interactive is good enough.
Heres a rewrite of the airflow tutorial in prefect: https://docs.prefect.io/core/examples/airflow_tutorial_dag.h...
My advice is try it out and see if you like the api. I think that the airflow ui is still a killer feature in 2019 though.
Theres also this post from the creator of prefect on why not airflow: https://medium.com/the-prefect-blog/why-not-airflow-4cfa4232...
I haven’t tried dagster yet, how does it compare to airflow?
Possibly a side effect of nosql movement/proliferation that these basic things became esoteric. Like a vet care for dinosaurs :) The tooling around data pipelines, like debugging, profiling, lineage, etc was too a typical stuff of the ETL tools of yesteryear like Informatica for example.
When I write pipelines I write unit tests for the business logic and integration tests for the overall process.
Whether you use SSIS, Alteryx, Spark whatever you can write tests and you can debug using known inputs, rather than running an entire pipeline to see that is works!
These are my typical thoughts on ETL testing:
https://the.agilesql.club/2019/07/how-do-we-test-etl-pipelin...
https://the.agilesql.club/2019/08/how-do-we-prove-our-etl-pr...
https://the.agilesql.club/2019/09/how-do-test-the-upstream-d...
https://the.agilesql.club/2019/10/how-to-test-etl-processes-...
ed
I tend to joke that I'm a digital janitor and now I'm a big data janitor. Well, things are going well enough, but cleanup on a live system with users and zillions of reports is incredibly difficult. Part of the issue is isolation and size: too many have too much access to too much data. It's overwhelming and it also leads to a lot of high cost because queries aren't understood and they're run against massive datasets.
I've been educating myself as well as I can between fixing errors and this book is just the thing I need to calm my nerves. It totally makes sense how the stages are set and even just this blog post overview has given me ideas of how to carve some things up.
Kudos, cheers, and all that. Happy Halloween!
Tech/web companies deal with massive amounts of unstructured/semi-structured data ingested at a fast clip, so the architectural thinking here works.
However I would argue that many traditional enterprises whose major sources of data are primarily highly structured (SQL databases), a lake is actually not needed.
A young data engineer working in a traditional enterprise, enamored with the idea of data lakes say, might try to ETL SQL databases into an object store (add RBAC etc.) only to rebuild it back out into a data warehouse. This will almost always turn out to be the wrong approach.
The simpler and more manageable approach is actually to federate existing databases, add cataloging etc. and not even use a data lake at all.
Which is why I mentioned federation. Most data sources in an enterprise are SQL native or accessible and it does not make sense to dump a SQL database into S3 just to be able to combine it with other forms of data.
Federation means you can operate across multiple databases (eg do Cross database joins etc.)