So, I’ve been documenting my journey as I totally transition to knowledge engineering and to date, I’ve constructed two pipelines. The primary is a scheduled GitHub ETL pipeline that extracts GitHub repositories, transforms it and shops it in a SQLite database. This was easy and I discovered a variety of ideas like ETL, idempotency and schedule utilizing a cron job.
The second venture took issues a step additional and I constructed an automated RSS pipeline that extracts tech articles from varied RSS feeds and shops it in a PostgreSQL database. The automation/orchestration was dealt with by Kestra. This was my first time making an attempt an orchestration platform and I additionally obtained uncovered to Docker which I used to containerize my knowledge pipeline to be simply orchestrated by Kestra and deployed to AWS cloud platform.
Now, I’m taking over a brand new venture. This time, I need a venture that permits me to refresh my reminiscence by means of repetition of sure points of my earlier initiatives whereas permitting me to be taught some recent units of abilities. I am calling it Stage 3.
So, this is the way it’s going to go. I believed that since I am making an attempt to interrupt into knowledge engineering, I’ll want a strategy to maintain monitor of roles which might be open and traits within the trade. So I constructed a pipeline whose whole job is to observe the job marketplace for the precise roles I am making an attempt to get employed into: knowledge engineer, analytics engineer, ETL developer, and different associated jobs. Each time it runs, it’ll be checking whether or not anybody on the market needs to rent somebody just like the individual I am making an attempt to turn into.
It would come off a bit bizarre. I am not employed as an information engineer but, and right here I’m constructing production-grade infrastructure to trace the marketplace for a job I haven’t got. However that is sort of the purpose of studying in public. You construct the factor you want existed, even when the factor is partly about your individual job search.
This piece covers the buildup by means of the purpose the place I’ve two sources touchdown cleanly in a neighborhood warehouse and a working SCD Sort 2 snapshot monitoring which jobs open and shut over time. A second piece will cowl wiring all of it along with Airflow.
Choosing the stack, and hitting a wall instantly
So, the plan is to drag from two sources (Hacker Information as a simple, well-behaved one, and a job board for one with an actual narrative), land all the things in a cloud warehouse, orchestrate with a brand new device, and use dbt snapshots to trace job listings opening and shutting over time.
To resolve which orchestrator to make use of, I checked out what really exhibits up in job postings. Apache Airflow nonetheless wins by a large margin. Kestra, Dagster and Prefect are all actual and rising, however Airflow is the identify recruiters seek for, so that is what I am studying this time, shifting off Kestra.
For the warehouse, I selected BigQuery. It is because it has a free tier, no card expiry strain, and it exhibits up consistently in analytics engineering job postings. Till I really tried to set it up.
Seems Google Cloud requires a compulsory $30 prepayment to hyperlink a billing account in the event you’re in sure nations. That is one thing you need to put down earlier than BigQuery’s IAM and repair account options unlock, even on the “free” tier. I did not wish to decide to that for a studying venture, so I pivoted to DuckDB as a substitute.
Actually this felt like a downgrade at first. DuckDB does not carry the identical resume weight as BigQuery. However dbt abstracts a lot of the warehouse-specific syntax away, so the precise transformation and snapshot work I am studying transfers cleanly if I ever wish to level this at BigQuery or Snowflake later.
Hacker Information, and truly studying incremental loading
I began with Hacker Information as a result of its API offers you a clear, ever-increasing ID for each story, which makes it the simplest doable case for incremental loading. Incremental loading is mainly loading solely the brand new or modified knowledge as a substitute of loading all the things another time. This helps if you wish to keep away from duplicates in your pipeline. Earlier than this venture, each pipeline I would constructed simply pulled all the things, each time. That works high-quality at small scale and falls aside the second you care about effectivity or price limits, which is mainly each actual job.
Right here, mainly, is the thought. I needed to maintain a small file that remembers the best story ID you’ve got already processed, and solely fetch something above that.
First run pulled 500 tales. Second run, a minute later, pulled 6. So, all the things labored high-quality, which is ideal.
There’s one thing I’ve to level out although. Hacker Information returns a discipline known as by for the story’s creator. by is a reserved SQL key phrase, so DuckDB rejected my desk creation till I quoted it as “by” all over the place it confirmed up. It’s nothing to fret about, although, I simply needed to level it out.
The Remotive useless finish
That is truthfully probably the most helpful lesson in the entire piece.
Initially, I picked Remotive for job listings as a result of it has a documented, free API with search and class parameters. I wrote an extractor that queried it for six totally different function titles (knowledge engineer, analytics engineer, ETL developer, and so forth) and merged the outcomes.
Nonetheless, I discovered that each single question got here again with the very same 15 jobs. I obtained the identical titles and identical order, each time, no matter what I looked for.
My first principle was price limiting. Remotive’s docs say to not exceed about 2 requests a minute, and I used to be firing 6 in a row. I took that as an inexpensive guess, so I rebuilt the extractor to make a single request utilizing the class filter as a substitute of six separate searches.
I ran it once more and nonetheless obtained the identical 15 jobs. So I ran a direct diagnostic towards the dwell API with an specific restrict=3:
Nonetheless returned 15 jobs. The API’s personal job-count discipline claimed the full was 15. Each parameter I despatched, search, class, restrict, was being ignored. Remotive’s public API was simply serving a hard and fast, generic pattern it doesn’t matter what you requested it for.
Finally, I spotted that that is not a bug in my code. I confirmed it straight towards the dwell API from my very own machine, a number of methods, earlier than accepting it. As a substitute, it meant the second knowledge supply I would picked for this complete venture merely did not work anymore, and I needed to pivot mid-build.
Pivoting to RemoteOK
I wanted a second knowledge supply that was really working, so I gave RemoteOK a strive. It’s a well-established, free job API, however earlier than constructing something round it, I needed to ensure it was returning recent knowledge relatively than the identical previous pattern I bumped into with Remotive.
RemoteOK has a tags parameter, which helps slender issues down, nevertheless it isn’t very exact. A job can have 30 or 40 generic ability tags, so even a seek for knowledge may return one thing like a Lead Paralegal function. Not precisely what I used to be in search of.
To take care of that, I break up the extraction into two steps. First, I take advantage of tags=knowledge to get a broad set of doubtless related jobs. Then, in Python, I verify the job titles towards a stricter listing of key phrases. It’s a easy strategy to reduce down the noise with out relying solely on RemoteOK’s filtering.
Out of 100 jobs, solely 16 have been really related. That was reminder that the API’s filter wasn’t sufficient by itself. Combining its broad filter with my stricter key phrase verify in Python gave me a lot cleaner outcomes than counting on both one alone.
Watching jobs shut: dbt snapshots and SCD Sort 2
That is the half I really constructed this complete venture to be taught.
Each extractors write their present state into DuckDB on each run. For RemoteOK particularly, the loader totally replaces the desk every time, as a result of the API solely ever tells you what is open proper now. By itself, that desk can by no means let you know a job used to exist. If an inventory disappears between runs, it is simply gone, with no file it was ever there.
A dbt snapshot fixes that by evaluating the present desk towards what it noticed final time, and retaining historical past as a substitute of overwriting it. That is slowly altering dimensions, or SCD Sort 2, the factor each knowledge engineering job posting mentions and nearly no newbie tutorial really demonstrates working. To raised perceive what SCD Sort 2 is all about, right here’s a situation that would assist.
Think about a buyer named Sarah who lives in Chicago. Just a few months later, she strikes to New York.
If we merely replace her metropolis within the database, we lose the data that she used to dwell in Chicago. However what if the enterprise must analyse gross sales based mostly on the place clients lived after they made their purchases?
That is the place SCD Sort 2 is available in. As a substitute of overwriting Sarah’s current file, we maintain it and create a brand new one together with her up to date particulars. We will use dates and a current-status flag to trace when every model was legitimate.
That manner, we all know the place Sarah lives now with out dropping monitor of the place she lived earlier than. In an information warehouse, this makes it doable to analyse historic adjustments relatively than simply the newest info.
Right here’s the code I used to arrange the snapshot
The setting that issues most right here is hard_deletes='invalidate'. Usually dbt snapshots simply ignore a row that vanishes from the supply. This setting tells it the alternative: if a job ID that existed final run is lacking this run, shut out its row with a dbt_valid_to timestamp as a substitute of silently dropping it.
I examined this by hand earlier than trusting it. I ran the snapshot as soon as with two faux listings, each open. I then simulated certainly one of them closing and a brand new one showing, and ran the snapshot once more.
That is the precise conduct I needed, and dbt dealt with all of it with out me writing a line of comparability logic myself.
I additionally added assessments past dbt’s defaults. In addition to the usual not-null and uniqueness checks, there is a customized take a look at that fails loudly if the RemoteOK desk ever comes again with zero rows, which is precisely the sort of silent failure Remotive simply taught me to observe for. There are additionally freshness checks on each uncooked sources, warning if both hasn’t reloaded in 24 hours.
What’s subsequent
Proper now each sources run manually, one script at a time. The subsequent piece picks up with wrapping each extractors and the dbt instructions into an Airflow DAG, which suggests studying a second orchestrator’s psychological mannequin after a yr of solely understanding Kestra’s.
As soon as that is operating on a schedule, the extra attention-grabbing query opens up: what does the info really present? Which roles keep open longest, which titles come and go quickest? Do “knowledge engineer” and “analytics engineer” postings behave otherwise? That is the piece I am most wanting ahead to writing, as a result of it is the one the place the pipeline stops being an train and begins being a mirror pointed at my very own job search.
That is a part of my ongoing sequence documenting my transition from techniques analyst to knowledge engineer. Should you’ve been following alongside, thanks. If that is your first article within the sequence, the sooner ones are linked above.
