Ask HN: Simple, beginner friendly ETL / Data Engineering project ideas?

I think you can easily learn to walk also with Spark. There are a lot of beginners Spark tutorial online. You can see, for instance, https://community.cloud.databricks.com. They give you a Spark cluster where you can start and there are several tutorials in a notebook-style. Check it out. Of course, you could start your ETL / Data Engineering in a more "traditional" way trying to learn about relational databases and the likes. But I would suggest you to start directly with Spark. You can use it with lots of other big data tools (such as Hadoop/Hive and also S3) and you could also find some interesting Machine Learning use case.

I would recommend looking into Python-based workflow managers: Luigi[0] then Airflow[1], to get a hang of scheduling, DAGs, etc. Both are fairly simple to get started with (especially for a seasoned Python developer) and are used in production environments as well.

[0] https://github.com/spotify/luigi

[1] https://github.com/apache/incubator-airflow

You could check out https://github.com/mara/mara-example-project

The project is just a demonstrator for the Mara framework but it gives a good overview and you could take it as a starter for something you want to build.

This list has numerous pointers to resources related to data engineering: https://github.com/igorbarinov/awesome-data-engineering

an excellent survey of the field: https://dataintensive.net/

Software wise you could also have a look at dask. Which is more lightweight than Hadoop and spark.

But since you are asking for a project, why not do something local. I.e. you could scrape some data for some time (cinemas, crime) and structure it nicely. After a few months you can start analysis. Bonus points if you make you data available.

I think basic concepts of datawarehousing like creating data marts, building star or snowflake schemas, dimensional modelling, slowly changing dimensions are quite important before you jump into why hadoop, hive, hbase or spark is relevant.

Spark, etc, are great, but honestly if you're just getting started I would forget all about existing tooling that is geared towards people working at 300 person companies and I would read The Data Warehouse ETL Toolkit by Kimball:

https://www.amazon.com/gp/product/1118530802/

I learned from the second edition, but I've heard even better things about the third. As you're working through it, create a project with real data and from-scratch re-implement a data warehouse as you go. It doesn't really matter what you tackle, but I personally like ETLing either data gather from web crawling a single site[0] or push in a weekly gathered wikipedia dump. You'll learn many of the foundational reasons for all the tools the industry uses, which will make it very easy for you to get up to speed on them and to make the right choices about when to introduce them. I personally tend to favour tools that have an API or CLI so I can coordinate tasks without needing to click around, but many others like a giant GUI so they can see data flows graphically. Most good tools have at least some measure of both.

[0] Use something like Scrapy for python (or Mechanize for ruby) with CSS selectors and use the extension Inspector Gadget to quickly generate CSS selectors.

I second the Luigi recommendation. Republic Wireless uses it for all of our data warehouse ETL, and it's been fantastic to work with.

I also second the other comment that recommends starting with basic data extraction rather than diving into Hadoop or Spark immediately. Sure, at some point, you might need to process 100 billion lines of data. But in your average business, you're far more likely to be working with thousands or millions of records on customers, sales, orders, invoices, sales leads, etc. That stuff doesn't need Hadoop/Spark, it needs a Postgres database and a DBA with a good head on their shoulders keeping everything organized.

In my experience, government data sets (particularly demographics and other geographically-related data sets) are a fantastic way to get your feet wet with data processing. They're published by a bunch of different agencies, so they're not necessarily conveniently available in one place. However, they usually use standardized identifiers for geographies, which makes it easy to join the data sets together in new and interesting ways.

For instance, here at Republic, we recently used Form 477 data on wireless broadband availability from the FCC, data from Summary File 1 of the US Census, and a couple of Census geographic crosswalk files to be able to calculate the percentage of population in given zip codes and cities covered by various wireless carriers. That required reading the docs for several different data sources, automating some downloads, building database tables to hold all of the information, and then carefully crafting some SQL to pull it all together.

Of course, government data sets generally won't require a whole lot of automation (they're updated yearly or less than yearly, not daily). To build your skills on that front, I'd recommend learning to extract data from various APIs, structure it in a meaningful way, and make it available in a database. For example, if you have a website, set up a free Google Analytics account for it, then build a daily ETL that extracts some meaningful information from the Google Analytics API and stuffs it in a Postgres DB. Then see if you can build some charts or something that sit on top of that database and report on the information.

I worked in that field a number of years. My recommendation to you is to start with some form of data that you are passionate about. Baseball statistics, business metrics, investment figures, whatever.

Once you have the data, then figure what you're going to do with it. (Don't agonize over it, this should all take just a day or so.)

Then go after the toolkit. You'll find many interesting questions if you start with the end goal in mind.

Good luck, and have fun!

I wrote a simple blog post recently with links to data engineering resources I found useful to get into the field, hopefully it is helpful: https://diogoalexandrefranco.github.io/data-engineering-reso...

Convert .mat files to json,csv,xls formats

I find ETL best practices with Airflow a good start. Even if you don't go the Airflow route, you can benefit from the example implementations of Kimball and Data Vault ETLs.

https://gtoonstra.github.io/etl-with-airflow/index.html

https://github.com/gtoonstra/etl-with-airflow

If you want to make it as a career choice, I think you should start with learning Java and Scala. For better or worse, this field is tied to the JVM and learning these languages will make picking up Spark and Hadoop (which tbh is a prereq for any Data Engineering position to have on their resume) a lot easier.

Also if you are looking to stay in the Python world, PySpark is pretty intuitive for any Python developer and tons of companies are using it.

Hi I have been working in this field (and some of the previous incarnations of it) for quite a while. I would be happy to have a discussion to give some pointers and relay some experience if you want.

My email and keybase.io is in my profile so feel free to get in touch.

1) Learn to do as much in plain Python as possible, focus on lazy evaluation (itertools, yielding, ...), you'll be able to process gigabytes with a tiny memory footprint, deployment will be a breeze etc.

2) Get to know some of the basic Python data processing/science packages like pandas, numpy, scipy etc.

3) Get used to writing short shell scripts - they probably won't be a part of your pipeline, but data engineering, especially development, involves a lot of data prep that coreutils will help you with. Throw in a bit of jq and you'll handle a lot of prep work.

4) Only once you've gotten used to the above, look at Dask, PySpark, Airflow etc. It really depends on your use cases, but chances are you won't have to touch these technologies at all.

Bottom line - wait with the heavyweight tools, they might be needlessly powerful. Also, work closely with DevOps, because the deployment side of things will help you understand consequences of your actions.

Spark is not too tricky to dive into, even though you can't really take advantage unless you have a big cluster to use :)

if you want to practice data-manipulation, and a lot of the map reduce type stuff you can do with spark, I find Pandas useful for small datasets (And a lot of overlap in functionality as far as Dataframes are concerned)

For pipeline stuff, definitely take a look at Luigi, but again without a cluster it'll be less fun. Still, if you can try automating tasks with a mini luigi scheduler on your localhost, it would be good practice

http://Singer.io is an open source ETL project written in Python. The components are small, composable programs that you can run independently, so you should be able to walk before your run.

A good beginner project is to build an adapter to a new data source (known as a "tap" in Singer). Most taps pull data out of business tools like Salesforce or Marketo, but people also build them to pull characters from the Marvel API (https://www.stitchdata.com/blog/tapping-marvel-api/)

Check out the getting started guide (https://github.com/singer-io/getting-started), or jump into the Singer Slack and ask for help (linked from the guide)

SQL. Most of my ETL jobs consist of setting up the overhead pieces in pyspark then using SQL for the most important logic. It's way more portable that way.

You might want to take a look at [lnav](https://lnav.org), a sort of mini-ETL CLI powertool. Performance is fine up to a few million rows.

Edit: of course this comment (like many others below) pertains to tooling vs a particular project per se. FWIW I agree w/ others' sentiment about doing things "by hand" and working with data that holds your interest.

Hey Zabana!

I noticed you were in the Europe area. We are looking for developers with interest in going into the field.

We produce a data pipeling/analytics platform for complex data to our customers.

If you would like to know more about our company! Position is in Stockholm. https://gist.github.com/eleijonmarck/1b384480aaa3d22ab7e6ea0...

Like other I would say start with a problem which interests you.

To mimic the real world try to have extraction from variety of data sources (rdbms, NoSQL, files (different formats csv/json), APIs (salesforce)). Once you have different sources, extract the data to your datalake built on S3/GCS/HDFS. Once the data is present you need to integrate with tools which can extract value. You can use Vendor specific tools like Athena/BigQuery or open source like Presto/Impala/Hive. You can do analytics where you require filtering, cleansing, joining various datasets. You can also look at storing the results in different formats so that other tools like Tableau can use them. To orchestrate all of this you can use Azkaban or Airflow.

My suggestion is slightly biased towards Hadoop ecosystem but the good thing is that most tools here have Open source alternatives.

What are the industries that have the highest costs for ETL/Data Engineering?

What companies in these industries are interested in reducing their costs for this work?

"costs" as used above includes time expenditures as well as spending money

http://web.archive.org/web/19991023120316/http://www.dbmsmag...

I have put together a (very short) post to build a dashboard using shopify data and pulling ETL with Stich. You could try to implement the stich part in Python and would have a complete solution. Like looking at Stich/Blendo would give you some ideas of simple ETL workflows. Keep in mind that ETL changes depending on what you want to do. In theory you can do all ETL just with Python code. If you have an SQL compliant database that can hold your data ETL processes could simply be a matter of running SQL queries to transform data within the database. Then you basically load your data from any data source into your db and you run a bunch of SQL to do the transform part.

https://assemblinganalytics.com/post/airbnbapache-superset-a...