8 ms·

Build your own “data lake” for reporting purposes

143 points by Signez 6y ago

xyzzy_plugh 6y ago
This is mostly what I would do at a small to medium sized startup. Everything seems really sane. There are two potential flaws, though. One is that while using a read replica for reporting works, there be dragons. There is a reasonable amount of tuning possible here. It's important not to conflate the reporting replica with a standby replica for prod. You also might consider asynchronous replication over synchronous replication for the reporting DB. Lastly, there are (tunables) limits to how much you can get away with, but long running queries on either end can potentially cause queries to get cancelled should they hold up replication enough. In other words, it is still very valuable to have optimized, small queries on the reporting DB where possible. Second is that this works fine until your data doesn't fit on an instance. Large RDS instances are not cheap, and at some large table size it begins to make sense to look at warehouse solutions, like Spark on S3/whatever or Redshift or Snowflake, which can scale out to match your data size. I'd be concerned to find a rats-nest of reporting database instances when a proper cluster should be used.
- 0xbadcafebee 6y ago
  Do you have a link to an article on the tuning (or could you draft one)?
  - xyzzy_plugh 6y ago
    I don't, but the most important tunables are in the replication docs: https://www.postgresql.org/docs/current/runtime-config-replication.html https://www.postgresql.org/docs/current/runtime-config-repli... I'd simply suggest reading this top-to-bottom very carefully, until you understand what each option does. That should in turn help you understand how and where replication impacts the source and the sinks. The docs can be terse, but it's rare for anything to be omitted -- read with care.
greenbcj 6y ago
This seems more like “build your own data warehouse” than data lake.
- de6u99er 6y ago
  This
- andrewflnr 6y ago
  Serious question: what's the difference? I've seen both of these terms a lot but never with a concrete definition. I get the impression neither one refers to a terribly precise concept.
  - waynesonfire 6y ago
    For starters, you won't hit the front page with one.
    - 1996 6y ago
      Some people want to deploy cool technology to hit the frontpage Other people want to deploy robust, tested and tried solutions, that won't break in mysterious ways, just to make money. I side with the later.
  - contravariant 6y ago
    Most people I've met use 'datalake' to refer to a (categorised) collection of otherwise unprocessed data. A data-warehouse is typically somewhat more structured and doesn't just collect data but also combines and links data from multiple sources. Typically with the goal of creating a set of tables that you can use for reporting without needing to know all the intricate details of how the source-data is linked. A data-warehouse can be based on a datalake. You could also make a data-warehouse without first building a datalake but keeping the datalake part separate allows for better separation of concerns. You can also have datalake without building a data-warehouse on top of it, it depends on what you want to use it for.
  - texasbigdata 6y ago
deleted 6y ago
[deleted]
chatmasta 6y ago
It's really cool to see these techniques in the wild, and also feels encouraging to us as we're doing something very similar at Splitgraph [0] to implement our "Data Delivery Network" [1]. Recently we've started calling Splitgraph a "Data Mesh" [2]. As long as we have a plugin [3] for a data source, users can connect external data sources to Splitgraph and make them addressable alongside all the other data on the platform, including versioned snapshots of data called data images. [4] So you can `SELECT FROM namespace/repo:tag` where `tag` can refer to an immutable version of the data, or e.g. `live` to route to route to a live external data source via FDW. So far we have plugins for Snowflake, CSV in S3 buckets, MongoDB, ElasticSearch, Postgres, and a few others, like Socrata data portals (which we use to index 40k open public datasets). Our goal with Splitgraph is to provide a single interface to query and discover data. Our product integrates the discovery layer (a data catalog) with the query layer (a Postgres compatible proxy to data sources, aka a "data mesh" or perhaps "data lake"). This way, we improve both the catalog and the access layer in ways that would be difficult or impossible as separate products. The catalog can index live data without "drift" problems. And since the query layer is a Postgres-compatible proxy, we can apply data governance rules at query time that the user defines in the web catalog (e.g. sharing data, access control, column masking, query whitelisting, rewriting, rate limiting, auditing, firewalling, etc.). We like to use GitLab's strategy as an analogy. GitLab may not have the best CI, the best source control, the best Kubernetes deploy orchestration, but by integrating them all together in one platform, they have a multiplicative effect on the platform itself. We think the same logic can apply to the data stack. In our vision of the world, a "data mesh" integrated with a "data catalog" can augment or eventually replace various complicated ETL and warehousing workflows. P.S. We're hiring immediately for all-remote Senior Software Engineer positions, frontend and backend [5]. P.P.S. We also have a private beta program where we can deploy a full Splitgraph stack onto either self-hosted or managed infrastructure. If you want that, get in touch. We'll probably be in beta for 12-18 months. [0] https://www.splitgraph.com https://www.splitgraph.com [1] We talked about all this in depth on a podcast: https://softwareengineeringdaily.com/2020/11/06/splitgraph-d https://softwareengineeringdaily.com/2020/11/06/splitgraph-d... [2] https://martinfowler.com/articles/data-monolith-to-mesh.html https://martinfowler.com/articles/data-monolith-to-mesh.html [3] https://www.splitgraph.com/blog/foreign-data-wrappers https://www.splitgraph.com/blog/foreign-data-wrappers [4] https://www.splitgraph.com/docs/concepts/images https://www.splitgraph.com/docs/concepts/images [5] Job posting: https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421 https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421...
georgewfraser 6y ago
This is a needlessly complex solution. You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical database (Snowflake and BugQuery are both good choices). Setting up multiple Postgres database and linking them together with foreign data wrappers is interesting to blog about, but it’s an extremely roundabout way to solve this problem.
- chatmasta 6y ago
  It seems difficult to make the argument that replicating data to a warehouse is "simpler" than leaving the data in its original place (which could actually be a warehouse) and querying it through an FDW. In your scenario you need to maintain and monitor an ETL pipeline, pay storage costs for the warehouse, and likely pay bandwidth costs for moving your data into it. In the blog post scenario, the only cost is a Postgres instance and bandwidth for the data your queries return. There are many cases where replication to a warehouse makes sense, but there are also plenty where it doesn't. Our philosophy at Splitgraph, where we're building something just like this blog post, is that querying should be cheap and easy in the experimentation phase. Analysts shouldn't need to ask engineers to setup a data pipeline just so they can query some data they might not even want. So why not start with a "data lake" (or "data mesh")? You can query any data connected to the mesh without any setup or waiting time. If you eventually decide that you don't want the tradeoffs of federation, then you can selectively warehouse only the data you need to optimize certain queries. (I might go even further and suggest that the data industry has the idea of the "modern data stack" completely wrong. Replicating data to a warehouse is the ultimate act of centralization. Every other aspect of software is trending toward decentralization; it's the only real way to deal with increasing complexity and sprawl. It's only a matter of time before this paradigm shift hits the data stack too.)
  - nunie123 6y ago
    I agree with much of what you said. However, I'm disinclined to believe there will be a trend away from a centralized data warehouse (or data lake). There is inherent value in having a single source of truth for analytics. With modern cloud tools abstracting away the complexity distributed analytical databases, data warehousing is getting easier and more powerful. It's true that there is added complexity in centralizing the data. But as the author of this article suggested, you're in a bad spot if your marketing team and your sales team can't agree on last month's revenue. I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized.
gizmodo59 6y ago
For my home projects I generate parquet (columnar and very well suited for DW like queries) files with pyarrow and use Dremio (Direct SQL on data lake): https://github.com/dremio/dremio-oss https://github.com/dremio/dremio-oss (https://www.dremio.com/on-prem/ https://www.dremio.com/on-prem/) to query them (minio or just local disk or s3) and use Apache Superset for quick charts or dashboards.
- unixhero 6y ago
  What does Parquet do for your problems? I am trying to learn for when I might need it?
  - gizmodo59 6y ago
    There are several advantages compared to CSV, JSON or other non-columnar formats as far as analytics is concerned which are typically not transactional. https://blog.openbridge.com/how-to-be-a-hero-with-powerful-parquet-google-and-amazon-f2ae0f35ee04 https://blog.openbridge.com/how-to-be-a-hero-with-powerful-p... https://stackoverflow.com/a/36831549 https://stackoverflow.com/a/36831549 And with projects like Apache Iceberg you can also have ACID transactions which will make it easy to update or delete just using SQL. This really opens up the separation of compute and data and you can use any engine you want (spark, drill, hive, impala, athena, dremio, redshift spectrum etc) on top of your files.
0xbadcafebee 6y ago
> When the team was small and had to grow fast, no one in the tech team took the time to build, or even think of, a data processing platform. Good! This is much better than building something well before you need it. So when do you know you'll need it? By having each team track trends in metrics for their services/work and predicting when limits will be exceeded. (This used to be mandatory back when we had to buy gear a year or two ahead) This catches persistently increasing data sizes as well as other issues. Each team should track their metrics, write a description for what its effect means for the whole product, and forward them to a product owner (along with estimates of when limits will be exceeded). > do try to set up some streaming replication on a dedicated reporting database server for quasi-live data (or set up automated regular dump & restore process if you don't need live data) Both of these are potentially hazardous to performance, security, regulatory requirements, customer contract requirements, etc. Replication can literally halt your production database, as can ETL, and where it goes and how it's managed is just as important as for the production DB. So look at your particular use case and design the solution just as carefully as if you were giving direct access to production. As for the structure of it all, once you start adding outside data, you'll find it's easier to architect your solution to have multiple tiers of extracted data in different pipelines, and to expose them each to analysis independently. You can make all of them eventually land in a dedicated database, but allowing individual analysis allows you to later build purpose-fit solutions for just a subset of the data without having to constantly mutate one "end state" database (which may become vastly more complex than your production database). Btw, the name for this kind of work is called Business Intelligence (https://en.m.wikipedia.org/wiki/Business_intelligence https://en.m.wikipedia.org/wiki/Business_intelligence)
pupdogg 6y ago
This is an overly complex solution that we were able to resolve using a simple VPS running Clickhouse as backend and Grafana for frontend. Our production db is an Aurora MySQL instance and we keep it lean by performing daily dumps of reporting related data into a CSV with gzip compression -> push it to S3 -> convert it to parquet file format using AWS glue -> bring it into ClickHouse. Data size for these specific reports is approx 100k rows daily and is partitioned by MONTH/YEAR. Overall cost: $20/month VPS and approx. $15/month in AWS billing.
- ekianjo 6y ago
  You do not even need to use AWS. use Minio as S3 compatible system and Nifi to convert files to parquet once they land in Minio... No dependency on AWS.
  - antman 6y ago
    Minio and nifi, require lots of resources for themselves. Better off using pure python and if one wants something lighweight and visually pleasing Mara [0] or Dagster with Dagit [1] will do the job [0] https://github.com/mara/mara-pipelines https://github.com/mara/mara-pipelines [1] https://docs.dagster.io/tutorial/execute https://docs.dagster.io/tutorial/execute
    - ekianjo 6y ago
      Yes, Minio and Nifi are not lightweight but if you look for robustness they have been used in large environments and proven to scale.
- twotwotwo 6y ago
  Appreciate the info on real-world use here. A data warehouse would be sort of interesting at work but is not urgently needed (because reporting from MySQL works, without the nifty speedups data warehouses can achieve), and we're somewhere between the size you're talking about and the really-big-data use cases I tend to see blogged about more often. Am curious about ClickHouse and a lower-cost deployment might make it worthwhile when it wouldn't be otherwise.
unixhero 6y ago
Thanks for giving back to the community in the form of these meditations on Postgres, real world business scenario analytics and keaaons learned.
snidane 6y ago
It seems the article suggests piping around and querying tabular data in postgres. I don't see a need for the data lake part. Data Warehouses are used to store and process anything which looks like a structured table, or nested tables, so called semi-structured data. Data lakes are for everything else. Your SQL warehouse can't process or is not ergonomic for processing of: - purchased data, shared as a zip archive of csv files - 10 level deep nested json files coming from api calls - html files from webscrapes - contents of ftp containing 100s of various csv and other files - array data used for machine learning algorithms - pickled python ml models - yaml configs - pdf documents such as data dictionaries - materialized views over raw data Besides your DWH, you need to have a storage layer, where you store these files and raw data. This is the main reason for why companies have data lake projects. Without some centralised oversight and discipline it only results in a big mess. Note that the centralisation doesn't have to be company-wide, each team or department can maintain their own data lake standard. The more centralised you do it, the more economy of data scale you get, but the harder it gets to enforce and maintain with higher chance of turning into mess again. I think Data Mesh concept is proposing structuring your org as a bunch of data producing teams, each maintaining their own data lake, instead of having one huge ass lake mess in the middle. Tools like delta.io and Databricks are giving data lakes full capabilities of a data warehouse, so the difference between data lake and data warehouse is diminishing. These days you can get away without a dedicated DWH and just store everything in a blob store and plug in short-lived processing engines as you wish without vendor lock-ins.
throwaway346434 6y ago
I feel some of the negative comments miss the point, at least of a way of structuring reporting extracts and presenting them in an easy to maintain way for services: you are signing a contract with the data team and taking on their concerns with an approach like this. Your unit tests fail if you are about to break the contract with a change, and you discover it upfront, rather than after rolling out a new service that changes a definition unexpectedly.
revskill 6y ago
All of this over-engineering challenge is due to lacking of a good ORM model to deal with sql table/view, good cron job/webhook system to sync data in realtime/batching. I would rather spend months to build a good platform rather than working on a over-complicated and low-level setup like this.

143 points by Signez 6y ago

This is mostly what I would do at a small to medium sized startup. Everything seems really sane. There are two potential flaws, though. One is that while using a read replica for reporting works, there be dragons. There is a reasonable amount of tuning possible here. It's important not to conflate the reporting replica with a standby replica for prod. You also might consider asynchronous replication over synchronous replication for the reporting DB. Lastly, there are (tunables) limits to how much you can get away with, but long running queries on either end can potentially cause queries to get cancelled should they hold up replication enough. In other words, it is still very valuable to have optimized, small queries on the reporting DB where possible. Second is that this works fine until your data doesn't fit on an instance. Large RDS instances are not cheap, and at some large table size it begins to make sense to look at warehouse solutions, like Spark on S3/whatever or Redshift or Snowflake, which can scale out to match your data size. I'd be concerned to find a rats-nest of reporting database instances when a proper cluster should be used.

0xbadcafebee 6y ago
Do you have a link to an article on the tuning (or could you draft one)?
- xyzzy_plugh 6y ago
  I don't, but the most important tunables are in the replication docs: https://www.postgresql.org/docs/current/runtime-config-replication.html https://www.postgresql.org/docs/current/runtime-config-repli... I'd simply suggest reading this top-to-bottom very carefully, until you understand what each option does. That should in turn help you understand how and where replication impacts the source and the sinks. The docs can be terse, but it's rare for anything to be omitted -- read with care.

greenbcj 6y ago

This seems more like “build your own data warehouse” than data lake.

de6u99er 6y ago
This
andrewflnr 6y ago
Serious question: what's the difference? I've seen both of these terms a lot but never with a concrete definition. I get the impression neither one refers to a terribly precise concept.
- waynesonfire 6y ago
  For starters, you won't hit the front page with one.
  - 1996 6y ago
    Some people want to deploy cool technology to hit the frontpage Other people want to deploy robust, tested and tried solutions, that won't break in mysterious ways, just to make money. I side with the later.
- contravariant 6y ago
  Most people I've met use 'datalake' to refer to a (categorised) collection of otherwise unprocessed data. A data-warehouse is typically somewhat more structured and doesn't just collect data but also combines and links data from multiple sources. Typically with the goal of creating a set of tables that you can use for reporting without needing to know all the intricate details of how the source-data is linked. A data-warehouse can be based on a datalake. You could also make a data-warehouse without first building a datalake but keeping the datalake part separate allows for better separation of concerns. You can also have datalake without building a data-warehouse on top of it, it depends on what you want to use it for.
- texasbigdata 6y ago

deleted 6y ago

[deleted]

chatmasta 6y ago

It's really cool to see these techniques in the wild, and also feels encouraging to us as we're doing something very similar at Splitgraph [0] to implement our "Data Delivery Network" [1]. Recently we've started calling Splitgraph a "Data Mesh" [2]. As long as we have a plugin [3] for a data source, users can connect external data sources to Splitgraph and make them addressable alongside all the other data on the platform, including versioned snapshots of data called data images. [4] So you can `SELECT FROM namespace/repo:tag` where `tag` can refer to an immutable version of the data, or e.g. `live` to route to route to a live external data source via FDW. So far we have plugins for Snowflake, CSV in S3 buckets, MongoDB, ElasticSearch, Postgres, and a few others, like Socrata data portals (which we use to index 40k open public datasets). Our goal with Splitgraph is to provide a single interface to query and discover data. Our product integrates the discovery layer (a data catalog) with the query layer (a Postgres compatible proxy to data sources, aka a "data mesh" or perhaps "data lake"). This way, we improve both the catalog and the access layer in ways that would be difficult or impossible as separate products. The catalog can index live data without "drift" problems. And since the query layer is a Postgres-compatible proxy, we can apply data governance rules at query time that the user defines in the web catalog (e.g. sharing data, access control, column masking, query whitelisting, rewriting, rate limiting, auditing, firewalling, etc.). We like to use GitLab's strategy as an analogy. GitLab may not have the best CI, the best source control, the best Kubernetes deploy orchestration, but by integrating them all together in one platform, they have a multiplicative effect on the platform itself. We think the same logic can apply to the data stack. In our vision of the world, a "data mesh" integrated with a "data catalog" can augment or eventually replace various complicated ETL and warehousing workflows. P.S. We're hiring immediately for all-remote Senior Software Engineer positions, frontend and backend [5]. P.P.S. We also have a private beta program where we can deploy a full Splitgraph stack onto either self-hosted or managed infrastructure. If you want that, get in touch. We'll probably be in beta for 12-18 months. [0] https://www.splitgraph.com https://www.splitgraph.com [1] We talked about all this in depth on a podcast: https://softwareengineeringdaily.com/2020/11/06/splitgraph-d https://softwareengineeringdaily.com/2020/11/06/splitgraph-d... [2] https://martinfowler.com/articles/data-monolith-to-mesh.html https://martinfowler.com/articles/data-monolith-to-mesh.html [3] https://www.splitgraph.com/blog/foreign-data-wrappers https://www.splitgraph.com/blog/foreign-data-wrappers [4] https://www.splitgraph.com/docs/concepts/images https://www.splitgraph.com/docs/concepts/images [5] Job posting: https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421 https://www.notion.so/splitgraph/Splitgraph-is-Hiring-25b421...

georgewfraser 6y ago

This is a needlessly complex solution. You will get better performance, with simpler maintenance, by replicating everything into an appropriate analytical database (Snowflake and BugQuery are both good choices). Setting up multiple Postgres database and linking them together with foreign data wrappers is interesting to blog about, but it’s an extremely roundabout way to solve this problem.

chatmasta 6y ago
It seems difficult to make the argument that replicating data to a warehouse is "simpler" than leaving the data in its original place (which could actually be a warehouse) and querying it through an FDW. In your scenario you need to maintain and monitor an ETL pipeline, pay storage costs for the warehouse, and likely pay bandwidth costs for moving your data into it. In the blog post scenario, the only cost is a Postgres instance and bandwidth for the data your queries return. There are many cases where replication to a warehouse makes sense, but there are also plenty where it doesn't. Our philosophy at Splitgraph, where we're building something just like this blog post, is that querying should be cheap and easy in the experimentation phase. Analysts shouldn't need to ask engineers to setup a data pipeline just so they can query some data they might not even want. So why not start with a "data lake" (or "data mesh")? You can query any data connected to the mesh without any setup or waiting time. If you eventually decide that you don't want the tradeoffs of federation, then you can selectively warehouse only the data you need to optimize certain queries. (I might go even further and suggest that the data industry has the idea of the "modern data stack" completely wrong. Replicating data to a warehouse is the ultimate act of centralization. Every other aspect of software is trending toward decentralization; it's the only real way to deal with increasing complexity and sprawl. It's only a matter of time before this paradigm shift hits the data stack too.)
- nunie123 6y ago
  I agree with much of what you said. However, I'm disinclined to believe there will be a trend away from a centralized data warehouse (or data lake). There is inherent value in having a single source of truth for analytics. With modern cloud tools abstracting away the complexity distributed analytical databases, data warehousing is getting easier and more powerful. It's true that there is added complexity in centralizing the data. But as the author of this article suggested, you're in a bad spot if your marketing team and your sales team can't agree on last month's revenue. I'm not sure how you'd solve that problem in an architecture where the data isn't getting centralized.

gizmodo59 6y ago

For my home projects I generate parquet (columnar and very well suited for DW like queries) files with pyarrow and use Dremio (Direct SQL on data lake): https://github.com/dremio/dremio-oss https://github.com/dremio/dremio-oss (https://www.dremio.com/on-prem/ https://www.dremio.com/on-prem/) to query them (minio or just local disk or s3) and use Apache Superset for quick charts or dashboards.

unixhero 6y ago
What does Parquet do for your problems? I am trying to learn for when I might need it?
- gizmodo59 6y ago
  There are several advantages compared to CSV, JSON or other non-columnar formats as far as analytics is concerned which are typically not transactional. https://blog.openbridge.com/how-to-be-a-hero-with-powerful-parquet-google-and-amazon-f2ae0f35ee04 https://blog.openbridge.com/how-to-be-a-hero-with-powerful-p... https://stackoverflow.com/a/36831549 https://stackoverflow.com/a/36831549 And with projects like Apache Iceberg you can also have ACID transactions which will make it easy to update or delete just using SQL. This really opens up the separation of compute and data and you can use any engine you want (spark, drill, hive, impala, athena, dremio, redshift spectrum etc) on top of your files.

0xbadcafebee 6y ago

> When the team was small and had to grow fast, no one in the tech team took the time to build, or even think of, a data processing platform. Good! This is much better than building something well before you need it. So when do you know you'll need it? By having each team track trends in metrics for their services/work and predicting when limits will be exceeded. (This used to be mandatory back when we had to buy gear a year or two ahead) This catches persistently increasing data sizes as well as other issues. Each team should track their metrics, write a description for what its effect means for the whole product, and forward them to a product owner (along with estimates of when limits will be exceeded). > do try to set up some streaming replication on a dedicated reporting database server for quasi-live data (or set up automated regular dump & restore process if you don't need live data) Both of these are potentially hazardous to performance, security, regulatory requirements, customer contract requirements, etc. Replication can literally halt your production database, as can ETL, and where it goes and how it's managed is just as important as for the production DB. So look at your particular use case and design the solution just as carefully as if you were giving direct access to production. As for the structure of it all, once you start adding outside data, you'll find it's easier to architect your solution to have multiple tiers of extracted data in different pipelines, and to expose them each to analysis independently. You can make all of them eventually land in a dedicated database, but allowing individual analysis allows you to later build purpose-fit solutions for just a subset of the data without having to constantly mutate one "end state" database (which may become vastly more complex than your production database). Btw, the name for this kind of work is called Business Intelligence (https://en.m.wikipedia.org/wiki/Business_intelligence https://en.m.wikipedia.org/wiki/Business_intelligence)

pupdogg 6y ago

This is an overly complex solution that we were able to resolve using a simple VPS running Clickhouse as backend and Grafana for frontend. Our production db is an Aurora MySQL instance and we keep it lean by performing daily dumps of reporting related data into a CSV with gzip compression -> push it to S3 -> convert it to parquet file format using AWS glue -> bring it into ClickHouse. Data size for these specific reports is approx 100k rows daily and is partitioned by MONTH/YEAR. Overall cost: $20/month VPS and approx. $15/month in AWS billing.

ekianjo 6y ago
You do not even need to use AWS. use Minio as S3 compatible system and Nifi to convert files to parquet once they land in Minio... No dependency on AWS.
- antman 6y ago
  Minio and nifi, require lots of resources for themselves. Better off using pure python and if one wants something lighweight and visually pleasing Mara [0] or Dagster with Dagit [1] will do the job [0] https://github.com/mara/mara-pipelines https://github.com/mara/mara-pipelines [1] https://docs.dagster.io/tutorial/execute https://docs.dagster.io/tutorial/execute
  - ekianjo 6y ago
    Yes, Minio and Nifi are not lightweight but if you look for robustness they have been used in large environments and proven to scale.
twotwotwo 6y ago
Appreciate the info on real-world use here. A data warehouse would be sort of interesting at work but is not urgently needed (because reporting from MySQL works, without the nifty speedups data warehouses can achieve), and we're somewhere between the size you're talking about and the really-big-data use cases I tend to see blogged about more often. Am curious about ClickHouse and a lower-cost deployment might make it worthwhile when it wouldn't be otherwise.

unixhero 6y ago

Thanks for giving back to the community in the form of these meditations on Postgres, real world business scenario analytics and keaaons learned.

snidane 6y ago

It seems the article suggests piping around and querying tabular data in postgres. I don't see a need for the data lake part. Data Warehouses are used to store and process anything which looks like a structured table, or nested tables, so called semi-structured data. Data lakes are for everything else. Your SQL warehouse can't process or is not ergonomic for processing of: - purchased data, shared as a zip archive of csv files - 10 level deep nested json files coming from api calls - html files from webscrapes - contents of ftp containing 100s of various csv and other files - array data used for machine learning algorithms - pickled python ml models - yaml configs - pdf documents such as data dictionaries - materialized views over raw data Besides your DWH, you need to have a storage layer, where you store these files and raw data. This is the main reason for why companies have data lake projects. Without some centralised oversight and discipline it only results in a big mess. Note that the centralisation doesn't have to be company-wide, each team or department can maintain their own data lake standard. The more centralised you do it, the more economy of data scale you get, but the harder it gets to enforce and maintain with higher chance of turning into mess again. I think Data Mesh concept is proposing structuring your org as a bunch of data producing teams, each maintaining their own data lake, instead of having one huge ass lake mess in the middle. Tools like delta.io and Databricks are giving data lakes full capabilities of a data warehouse, so the difference between data lake and data warehouse is diminishing. These days you can get away without a dedicated DWH and just store everything in a blob store and plug in short-lived processing engines as you wish without vendor lock-ins.

throwaway346434 6y ago

I feel some of the negative comments miss the point, at least of a way of structuring reporting extracts and presenting them in an easy to maintain way for services: you are signing a contract with the data team and taking on their concerns with an approach like this. Your unit tests fail if you are about to break the contract with a change, and you discover it upfront, rather than after rolling out a new service that changes a definition unexpectedly.

revskill 6y ago

All of this over-engineering challenge is due to lacking of a good ORM model to deal with sql table/view, good cron job/webhook system to sync data in realtime/batching. I would rather spend months to build a good platform rather than working on a over-complicated and low-level setup like this.