Ask HN: How do I improve our data infrastructure?

You have 100gb, the last thing you need is anything more than the absolute minimum in infrastructure overhead. My advice is plain and simple, use Vertica Community Edition. Vertica is, in my opinion, the best possible technology in these scenarios. Vertica Community Edition is free for up to 3 nodes, and up to 1TB of data. It is the fastest columnar datastore I have used, and once you learn some of the tips and tricks it just works.

I make no money from Vertica, I am not in any way shape or form compensated by them. Dump your data into Vertica, stand up a few servers, and forget about querying infrastructure until your data grows 10x.

https://www.vertica.com/register/

I do however own some stock in Domino Data Lab. The actual challenges of coordinating a data science team are tough. Making sure there is a single project repository, one place to manage history, etc. I would consider looking into Domino Data Lab. They have a ton of experience helping teams like yours leverage a data science platform. It's good tech.

https://www.dominodatalab.com/

TL/DR - Dump your data in Vertica. Find a DS platform that helps you collaborate, Domino Data Lab can be that platform.

Moving from your existing infrastructure to a new infrastructure incurs a cost in both design and implementation. There is also a cost in training existing people on the new infra.

Without much info to go as to how the infrastructure is complex, the first thing I would suggest is to get rid of operations overhead by going with a hosted service. AWS EMR, Databricks, Qubole, etc. offer a service with S3+Hive+Spark.

The reason for using the tools might not just be volume. It could also be due to vicissitude (large number of different data sources using spark for pre processing), or scaling, or something else. Try to understand why the existing solution is used before planning a migration.

If you are determined to go with a new pipeline, build a prototype for a small subset of data science tasks you have and carefully evaluate the pro and cons vs the existing approach for that subset.

Firstly, that does sound fairly insane. Barring any unusual computational requirements, your data is several orders of magnitude smaller than the 'big data' stack it seems like you're using.

If you can quickly set up a simple version to test with any of the rest of the team that are willing, that'd be my first port of call. A simple ETL job to pull in fresh data from your S3 store and push it into Postgres. Then you can hopefully get everyone else on board with how much easier this setup will make their lives, and build a consensus that this migration would be a good idea.

A quick idea re. Postgres and 'quick enough' vs. 'fast enough' - what are your requirements around data freshness for the models. Can you take data from a read replica or even your DB backups (extra points for letting you test your backups) for your model training workloads to keep load off the main instance?

W.r.t. the 'It takes several days to explain where all the data is' - in the first instance, I'd draw up a shared spreadsheet listing all this stuff. Yes it's best case going to be eventually consistent with the actual data you hold, but it should cut down the time spent regurgitating the same information over and over and gives everyone a central point to store it. This will be much easier and faster to implement than a full blown data catalog, which you could look at doing once you've got the 'duct tape' solution going.

If you want to chat, email is in profile.

I think this problem is essentially the age old problem of refactor or add features.

Its manifestation in the data world, from an awareness perspective might benefit from similar solutions. I imagine there is plenty of advice about that. Personally I think refactoring is not a management concern - why should they care, and why should you expect them to understand? It's a technical problem and so I think you need to convince your fellow techies of the benefits to them. What might they be?

If you don't have a data engineering function, then one way to promote a better organisation is to make each data scientist an expert in one subset of the data (if that is possible). Then it becomes their responsibility to service any data requests, and they will automate and refactor when that responsibility becomes onerous, and not before, which should be a good way to self regulate your resources.

I have used standards and conventions within data platforms that I have written, but I feel that they are not as important as discovering what you need for your particular job and situation. It seems you have a handle on main factors to consider. Of the top of my head the various tradeoffs are to do with computation time, storage space, code complexity, infra costs, costs of refactoring, future proofing etc. And if in doubt, use the lean and agile approach!

Don't dump the data to Postgres.

Instead, define a data model and write an API that pulls data out of the respective places. The API will be the one place your applications get data from.

You don't have to build it all at once. Just code the parts you need as you develop new applications.

For exploring, I started Kyso for this reason, you have data in S3 - explore it using a Jupyter notebook running on ec2, and if you wish you could push your notebooks to Kyso for your team to read (we make them look like blog posts).

We act like knowledge repo for teams so you can add a wiki article explaining where the data is and how to get it, and the notebooks can be downloaded/forked, saved on github, so that your team can re-use them as data transformation scripts.

https://kyso.io/for-teams

I'm more than happy to help anyone get started at eoin [at] (company website)

The easiest thing would be EMR if you want to start moving to aws-provided stuff. You can also start doing ad-hoc queries for better explore-ability with Athena. You can do all of this without changing the data files maybe.

Have you considered putting your information into Elasticsearch?

Logstash would allow you to build out centrally (or via CM) pipelines to manage your data with much greater granularity.

100gb would fit on 3 pretty small instances and Kibana would let you sift through that information very quickly.

Disclaimer: I work for Elastic. (Feel free to reach out to me if I can help though!)

The infrastructure you described is a fairly common pattern in data based startups. As mentioned introducing a new solution has its ups and downs,best to weight out your options evaluate then implement. Since you're the first person you'll have to lay the groundwork. Just remember to take a strategic perspective as the 2nd person on the data science team may think that you're design might be insane a few years down the line.

If you have data in multiple formats in s3 you could look at Snowflake (https://www.snowflake.com/). They have built in ingestion functionality and can translate various formats into their warehouse with minimal effort. You could also look at amazon redshift spectrum which will let you run queries across data in s3.

As someone else suggested, you should provide an API access later so that users only have one way to request the data.

My experience with dealing with lot's of legacy app and infrastructure is to take a step by step a pragmatical approach.

Identify all the pain points of the current solution, tools and processes. For all those pain points take time to discuss with all the people involved to have a good understanding of the issue. Something you see as really bad might not be as problematic as you thought, some other things you might not have seen migth be worthwhile to look at. Don't do anything before having involved the impacted people otherwise, however good your idea is, you might face rejection.

Be also pragmatic. The end result needs to be more efficiency and, hopefully, people more happy when doing their job. If the main issue is the learning curve, maybe before fixing the actual model, look if the current one require better documentation.

If it is code duplication, look if there's way to share code and knowledge without the need to change the whole underlying data model.

By doing so you can gain trust with few quick but efficient and time saving changes. This will prove that you know what you're doing and that you're not asking for investment just for the sake of technical beauty. Then you'll be able to talk about bigger changes

Usually I also found that not jumping directly into the big technical overhaul and focusing on small changes ends up showing issues that were hidden in the first place or help to foresee a better solution that works for everybody. For example, by sharing code between data scientists you might end up seeing some technical requirements that you wouldn't have thought about. By writing documentation, you might end up seeing some constraints or some way to improve the way data is stored that you wouldn't have thought about.

Don't jump on a technical choice right now, make sure you have a good vision on where to go first and a good understanding on how it will fit in the company processes.

Sounds like you have both organisational and architecture problems here. A data team of ten and you're the only permanent employee? There's no data engineer or infrastructure team you can liaise with? I would discuss this with management, making the problem clear and that you're happy to help bridge the gap, but they need to hire someone with these skills.

For the software side Presto or AWS Athena will give you an SQL query layer over parquet files in S3, which should make life a lot easier. It will also hold the table_name -> s3_path relations. Lower effort than dumping to postgres, though at the current scale that is an option too.

Sounds like you are have mix of on-perm & aws? If it is all aws, check out EMR: https://aws.amazon.com/emr/. Alternatively, a relatively easier way to build pipelines on AWS is using AWS Kinesis for event streaming to S3 and ingest S3 files to Snowflake https://www.snowflake.com/ - works relatively well for smaller workloads & easy to setup.

IMO - the best way to raise awareness is to build a scrappy prototype pipeline that can be demoed & then demo/over communicate with all the stakeholders :). Having a working demo makes it easier to visualize the pros of the new proposed system compared to the existing one.

They built a pipeline that complicated for 100gb? That’s insanely over-engineered! Very typical of engineers who just want to pad their resume at the expense of unsuspecting business people. I’ve worked with single server data warehouses on SQL Server that were 10x in size and served the entire company.

I don’t know what your data looks like, whether it’s just transactional or a combination of transactional and raw server/app logs. You could ETL the raw logs into an RDBMS like Postgres but you have to worry about maintaining it though and it doesn’t sound like you have enough resources for that. To do that you need help from IT/ops to set up a replica of the live server so it can be queried without disrupting transactional operations and then write ETL code or use a service like Stitch or Panoply.

You can also use a cloud platform like Google BigQuery or AWS Redshift to dump raw data in and then create views and table extracts for all the commonly used business functions. That’s still overkill though and a simple RDBMS should suffice.

And if you want to raise awareness see this article by StichFix and the HN comments https://news.ycombinator.com/item?id=11312243

We have a similar issue.

Generally speaking, your work must fit within a value stream - that is, to support your job/function you must do something that's rewarding (someone's got to pay the bills). There are a lot of interesting agile principles at play here but ultimately they revolve around delivery which must occur regardless of how complete, incomplete, fast, slow and/or viable your data is. Delivery is valuable. Your tech leads are the ones most clued up on what must happen and which compromises are acceptable. Their role is to get some data - any data - out so that you can make a qualified success of your product. Cutting corners and managing/deferring difficult decisions is part of that job.

Over time the cadence of features and the complexities of your data will require pivots. Initially you might have gotten away with simple line-level data but with maturity and agile planning the next revenue producing feature might require aggregation and complex transformations. Once again your role is at odds with delivery - if there's a simple way to make the end result a qualified success in the shortest lead time possible, data comprises ought to prevail over your immediate happiness.

Now with that said you might be thinking "wtf this is nuts - it can't be right... I'll never succeed". That's actually quite possible. It's often a reason why most developers move on. When that happens the process becomes self-destructive - new devs start and become disillusioned and leave creating an air of futility; product knowledge is virtually non-existent...all past decisions were shit and should never have been done that way in the first place etc.

So how do you fix this? I'm going to avoid the technical side because I don't know your stack. The procedural/operational solution comes from understanding and managing your employer's approach to agile. The big word here is Trust. Is your team trusted to manage the work items that enter the sprint? Are you trusted to add work items to the backlog? As developers do you own the sprint? Have you created items that address your technical concerns with concrete examples (it might take you several sprints to fully flesh these out)? If you answer 'no' to any of these it's time to address them and start owning the process. If you've tried this or you've failed to explain from a delivery perspective why you have problems and what their risk is (use past examples rather than future commitments) and there's been no traction you should consider quitting.

I'd just write a bunch of adapters to transform all your current data into a unified set of formats in acro or parquet or whatever, compress the originals and dump into glacier just incase and then use Athena to query it.

Then you can have s3 file write triggers to ensure that every new file conforms to your new schemas

100GB is so small, it fits into RAM of a single server for very little money. So even if it's stored in some weird formats, you can read it and parse it into memory structures that are the most efficient.

Unfortunately you provide few details on the structure of data, so it's hard to advise anything particular.

Postgres should scale nicely beyond that. Stick Tableau and Alteryx on top and you’re done.

You could use a data governance tool, eg. [0], to ingest data from several different sources. I think the cover most databases (ie. postgres, mysql, mssql), S3, Hive, Snowflake, just to name a few.

[0] https://www.collibra.com

I don't know your ratio of HDF5 to Parquet files but remember for every GB of parquet you have it will equate to about 10 GB of space needed in CSV or PostgreSQL's internal format. So your data set is probably closer to 1 TB than 100 GB.

Storing that data on S3 is probably 50% the price of storing it on EBS and you won't have the durability guarantees of S3 when you're using PostgreSQL on EBS volumes.

If you're both exploring data and building models then Spark is fine. Its APIs are no more complicated that anything else out there for these tasks.

Hive is doing nothing more than offering schema on read and shouldn't be something you're thinking much about.

PostgreSQL is row-oriented and won't be able to offer features like row-group statistics that allow queries to get minimum and maximum values for every 10-15K rows of data for the columns their interested in. This gives queries a huge speed up over needing to scan over rows rather than just the statistics for the columns their interested in.

Remember that you can have a single engineer run a single query on Spark and distribute it across several servers. This allows you to scale CPU and memory bandwidth in a way you won't be able to with PostgreSQL.

It sounds like your data isn't well organised. If you moved it around and put some consistent naming conventions in place that could help. You could also look to build an atlas of the data for newcomers to get an overall picture of what data you're storing and where it lives.

It sounds like you already have an idea of what you want to do, but I think you should pause and think more deeply about what you have, vs. what you want.

What I would want in your situation is:

    - All the data in one place.
    - An easy way to explore the data. 
    - A single source of truth for transformed data.
    - Metadata to explain the data model (ie. documentation).

What you're proposing does some of those things, but it also:

    - Adds yet another maintain-forever technology to your stack.
    - Adds yet another pipeline (or set of pipelines) that does the same thing.
    - Moves from an architecture that is clustered for scale (ie. spark) to one that only scales vertically (postgres). 
    - Potentially introduces *yet more* sources of truth for some data.

> I was thinking that in a first iteration, data scientists would explore their denormalized, aggregated data and create their own feature with code.

^ Moving data into postgres doesn't make this somehow trivial, it just enables people to use a different SQL dialect. The spark API is, for anyone competent to be writing code, not meaningfully less complicated than using the postgres API.

I appreciate the naive attractiveness of having a traditional "data warehouse" in a SQL database, but there is actually a reason why people are moving away from that model:

    - it doesn't scale
    - SQL is terrible language to write transformations in (its a *query* language, not an ETL pipeline)
    - it's only vaguely better when you have many denormalised tables, vs. s3 parquet blobs
    - you have to invent data for schema changes (ie. new table schema, old data in the table) (ie. migrations are hard)

More tangibly, I know people who have done exactly what you're talking about, and regretted it. Unless you can very clearly demonstrate that what you're making is meaningfully better, it won't be adopted by the other team members and you'll have to either live forever in your silo, or eventually abandon it and go back to the old system. :/

So... I don't recommend it.

The points you're making are all valid, and for a small scale like this, if you were doing it from scratch it would be a pretty compelling option... but migrating entirely will be prohibitively expensive, and migrating partially will be a disaster.

Could you perhaps find better way to orchestrate your spark tasks, eg. with airflow or ADF or AWS Glue or whatever?

Personally I think that databricks offers a very attractive way to allow data exploration without a significant architecture change.

The architecture you're using isn't fundamentally bad, it just needs strong across the board data management... but that's something very difficult to drive from the bottom up.

Develop the proper scheme and put the data in BigQuery. From there, you can use Google Data Studio or Tableau to explore. You can stream in data with pub sub. You can use SQL on BigQuery and you’ll be able to query it all very fast somewhat regardless of size. Then you won’t need to support the old infrastructure and can buy yourself time to figure out if you want to stay with that ecosystem.

It sounds like your org needs two things,

1. A data warehouse for this data

2. Awareness of software/data best practices

That being said, while I agree code duplication is bad, data duplication isn't as long as you are maintaining data lineage. In some cases data duplication good.

I also wouldn't care too much that you have 100Gb max in a big data architecture. So what? It's not like you're going to be able to get rid of it easily. A data warehouse built from a new set of pipelines seems like the biggest bang for your buck.

100GB actually sounds like the job for command line tools on a single computer