Forem: adrian

Weather api list & pipelines

adrian — Tue, 31 Oct 2023 09:31:24 +0000

I used the public api list to look for weather apis and qualify their pros, cons and usage.

Here is the outcome, that will easily allow you to pick one suitable for you.

Tier 1 (Highly Popular and Widely Used)
Tier 2 (Respected and Reliable)
Tier 3 (Specialized or Geographic Focus)
Tier 4 (Limited or Niche Use)

want to use them? build a pipeline in minutes using this guide and the GPT assistant
https://dlthub.com/docs/build-a-pipeline-tutorial

Tier	API Name	Description	Auth	HTTPS	CORS	Pros	Cons	API Documentation
Tier 1	OpenWeatherMap	Provides weather data for any location worldwide.	apiKey	Yes	Unknown	- Offers a wide range of weather data	- Free tier has limited requests per minute	OpenWeatherMap API
Tier 1	Weatherbit	Weather data and forecasts.	apiKey	Yes	Unknown	- Provides forecasts up to 16 days ahead	- Limited daily requests	Weatherbit API
Tier 1	AccuWeather	Weather and forecast data	apiKey	No	Unknown	- Offers detailed forecasts	- Limited free tier	AccuWeather API
Tier 1	Tomorrow	Weather API Powered by Proprietary Technology	apiKey	Yes	Unknown	- Powered by proprietary tech	- Limited free tier, rate limits	Tomorrow API
Tier 2	MetaWeather	Weather	No	Yes	No	- CORS support	- Limited features	MetaWeather API
Tier 2	US Weather	US National Weather Service	No	Yes	Yes	- Official source for US weather	- Limited to US weather data	US Weather API
Tier 2	Storm Glass	Global marine weather from multiple sources	apiKey	Yes	Yes	- Specialized in marine weather	- Limited free tier, rate limits	Storm Glass API
Tier 2	Visual Crossing	Global historical and weather forecast data	apiKey	Yes	Yes	- Historical and forecast data	- Limited free tier, rate limits	Visual Crossing API
Tier 3	AQICN	Air Quality Index Data for over 1000 cities	apiKey	Yes	Unknown	- Air quality data for cities	- Focus on air quality, not weather	AQICN API
Tier 3	ColorfulClouds	Weather	apiKey	Yes	Yes	- CORS support	- Limited to specific regions	ColorfulClouds API
Tier 3	HG Weather	Provides weather forecast data for cities in Brazil	apiKey	Yes	Yes	- Brazil-specific weather data	- Limited to Brazil	HG Weather API
Tier 3	Euskalmet	Meteorological data of the Basque Country	apiKey	Yes	Unknown	- Spain-specific weather data	- Limited geographic coverage	Euskalmet API
Tier 3	Hong Kong Observatory	Provide weather information, earthquake information, and climate data	No	Yes	Unknown	- Official source for Hong Kong	- Limited geographic focus	Hong Kong Observatory API
Tier 3	Oikolab	70+ years of global, hourly historical and forecast weather data from NOAA and ECMWF	apiKey	Yes	Yes	- Historical data and forecasts	- Limited free tier, rate limits	Oikolab API
Tier 3	Micro Weather	Real-time weather forecasts and historic data	apiKey	Yes	Unknown	- Real-time and historic data	- Limited free tier, rate limits	Micro Weather API
Tier 4	ODWeather	Weather and weather webcams	No	No	Unknown	- Offers webcams with weather	- Limited features	ODWeather API
Tier 4	OpenUV	Real-time UV Index Forecast	apiKey	Yes	Unknown	- Specialized in UV Index	- Limited to UV data	OpenUV API
Tier 4	apilayer weatherstack	Real-Time & Historical World Weather Data API	apiKey	Yes	Unknown	- Real-time and historical data	- Limited free tier, rate limits	apilayer weatherstack API
Tier 4	APIXU	Weather	apiKey	Yes	Unknown	- Provides general weather info	- Limited free tier, branding	APIXU API
Tier 4	WeatherAPI	Weather API with other stuff like Astronomy and Geolocation API	apiKey	Yes	Yes	- Additional features	- Limited free tier, branding	WeatherAPI
Tier 4	Weather-api	A RESTful free API to check the weather	No	Yes	No	- Free and open access	- Limited features	Weather-api
Tier 4	7Timer!	Weather, especially for Astroweather	No	No	Unknown	- Specialized in astro-weather	- Limited features	7Timer! API
Tier 4	Yandex.Weather	Assesses weather condition in specific locations	apiKey	Yes	No	- Assessing weather conditions	- Limited geographic coverage	Yandex.Weather API

Haunted data pipeline

adrian — Mon, 30 Oct 2023 17:29:50 +0000

Suppose in the spirit of haloween, you want to add some haunting to your DWH. How do you do that?

Create a function that returns some strange messages
Call it from your pipelines to log the strangeness for lols
Watch the logs get haunted :)

Sample code below. Happy haunting!

import requests
from bs4 import BeautifulSoup

import random


# Function to fetch spooky quotes from Goodreads
def fetch_spooky_quotes_goodreads():
    url = "https://www.goodreads.com/quotes/tag/spooky"
    response = requests.get(url)
    soup = BeautifulSoup(response.text, 'html.parser')

    quotes = soup.find_all('div', class_='quoteText')

    for quote in quotes:
        yield(quote.text)



def generate_dummy_data(num_rows=1000000):
    for i in range(1, num_rows + 1):
        data = {
            'ID': i,
            'Name': f'Name_{i}',
            'Age': random.randint(18, 70),
            'Gender': random.choice(['Male', 'Female']),
            'City': random.choice(['New York', 'Los Angeles', 'Chicago', 'Houston', 'Miami']),
            'Score': round(random.uniform(0, 100), 2)
        }
        if random.random() < 0.01: # 1% chance to print
            print(next(fetch_spooky_quotes_goodreads()))
        yield data



# view data
for row in generate_dummy_data():
    print(row)

# open connection
pipeline = dlt.pipeline(
    destination='duckdb',
    dataset_name='raw_data'
)

# Upsert/merge: Update old records, insert new
load_info = pipeline.run(
    data,
    write_disposition="merge",
    primary_key="id",
    table_name="users"
)

How to run dbt from python, with dlt's core and cloud runners

adrian — Fri, 27 Oct 2023 16:03:56 +0000

We created open source dbt runners to enable easy running of dbt from python

Here is the blog post about the use cases:
dbt runners use cases

Here are the docs to use the 2 runners:

dbt core runner

Supports creating venv on the fly and installing dbt, copying the code and running the package in it.
Supports passing credentials via dlt's credentials, which support also directly passing them in python.
Can run code from local or public online repo.

dbt cloud runner

Supports starting an existing job and waiting for it to end

Podcast: Eliminate The Overhead In Your Data Integration With The Open Source dlt Library

adrian — Fri, 27 Oct 2023 15:19:34 +0000

Resharing the episode we did on Tobias Macey's data engineering podcast.

https://www.dataengineeringpodcast.com/dlt-data-integration-library-episode-390

dlt is an open-source Python library that allows you to load data from various and often messy data sources into well-structured, live datasets.

It simplifies the process of data extraction, normalization, and loading, and can be easily integrated into your Python scripts.

It's user-friendly, scalable, and can run wherever Python runs, making it a powerful tool for both beginners and senior professionals.

You can install it with pip install dlt. For more details, check out the Introduction to dlt.

What can you expect from a hackernews launch?

adrian — Fri, 27 Oct 2023 14:56:25 +0000

So we launched our python library with a colab demo on hackernews and here's a short summary of what happened

Post went to front page in the morning at 7:30 AM https://news.ycombinator.com/item?id=37999527

Our post linked to a colab demo.
Comments and visitors started coming in. We did our best to answer as the post slowly dropped off the main page. By 6 PM our time, the post already went off the first page and onto the second.

Outcomes:

we went from 520 git stars to 670+ over 48h, and they keep climbing at a slower rate
Our github traffic was about 20x of a regular day
Our website traffic was about 3x compared to a regular day
about 10 new members joined our slack community.

6 judgment mistakes companies make when refusing data freelancers

adrian — Fri, 27 Oct 2023 13:34:39 +0000

I started dlthub as a freelancer, and I'll continue working with freelancers.

My field of work was data engineering and you can find more info about it in this data talks podcast.

However, I've noticed that some companies reject freelancers for reasons that may not be entirely justified.

Have you or your company recently turned down a talented individual simply because they were a freelancer? Did you challenge that decision, or did you base it on the notion that "we only hire full-time employees"?

Many of the companies I've spoken to are quick to dismiss freelancers as candidates for full-time positions. Often, this is due to a fundamental misunderstanding of what a freelancer represents.

In the early days, when I was unsure about how to secure projects, I tried applying for full-time roles as a freelancer. More often than not, I was met with silence, which was a stark departure from the typical <24-hour response times I was accustomed to.

I did receive callbacks from some of these companies, expressing interest in hiring me as an employee. However, they would only consider hiring me as a freelancer if they were unable to fill the role in time to meet their goals. This raised a question: why would these companies pass up the opportunity to bring in someone with precisely the skills they needed?

So whenever a company told me that they don't hire freelancers, I took it as an opportunity to learn from the interaction and inquire about the reasons behind this decision. In some cases, I received rather confrontational responses, and these responses were often based on misconceptions.

Misconception 1: Freelancers are flaky.

One common misconception is that freelancers are unreliable. Don't let stereotypes deter you; the people who believe this often lack direct experience working with freelancers.

What does it mean to be "flaky" in a work context? I once had a colleague who was a full-time employee, and after being insulted by someone in management one day, he decided to resign via email and never returned to the office. Was he being flaky? I wouldn't describe it that way. He made a principled decision, even though it may not have been the most pleasant one.

"Flaky" refers to someone who doesn't fulfill their work responsibilities. So, what motivates someone to deliver quality work?

Consider this: a freelancer has less at stake compared to an employee. Their career isn't threatened by a neglectful manager, their hours aren't dictated by an overly demanding boss, and they aren't working for you out of necessity. Freelancers don't have notice periods, and they don't lose sleep over job security.

Freelancers collaborate with you because they genuinely enjoy the work. They could likely earn similar or even better compensation elsewhere, but they choose to work with you because they are genuinely interested in seeing projects through.

If an employee were to flake out on you, would a three-month notice period have made any difference? It didn't in the example mentioned earlier, and it didn't in the case of several employees I replaced as a freelancer, all of whom had left their positions in frustration.

So, why do freelancers leave? Consider the possibility that a freelancer's departure is driven by their work environment. Could it be that an employee would have left much sooner, even with a notice period? It's essential to recognize that freelancers are less likely to be adversely affected by a challenging company culture, are more motivated to produce high-quality work, and are more likely to remain engaged. However, it's still a game of chance, as differing values and perspectives can gradually lead to parting ways.

Misconception 2: Freelancers are expensive. Market value for this work is X per hour.

Another reason companies often give for not hiring freelancers is cost. They compare freelancer rates to average salaries and conclude that freelancers are too expensive.

But just because the average salary for a role is X doesn't mean that the top talent you want to hire is willing to accept X. While some highly skilled individuals may not fully recognize their value and offer their services at the X rate, it doesn't mean you can retain top talent at that rate.

What is a freelancer's value? A freelancer with experience in the market has likely encountered various challenges and gained expertise in solving them. They are hired to deliver value for money and bill you only for the work they perform.

For example, consider a senior data employee you hired at a salary of 80,000. When you factor in company contributions to health, pension, hardware, and other benefits, this translates to a cost of about 100,000 to 110,000 to the company.

For an employee with 30 days of vacation, this works out to be roughly 60 per hour. When you factor in sick days and management and hiring overhead distributed over the employee's tenure, the cost rises to just over 70 per hour.

Now, let's look at an average senior data freelancer who charges around 80 to 100 per hour, with no additional costs beyond optional office space. Considering their short notice availability, self-management skills, lower risk to the employer, and additional experiences and skills that freelancers develop due to their work style, is +10-40% not worth it?

Why 1-to-1 comparisons are wrong:
Typically, hiring managers don't make these kinds of calculations, and they often compare their net/gross salary with the freelancer's total cost to the company, leading to the conclusion that freelancers are not worth it.

Sometimes, employers look for a good deal on salary and discount freelancers right from the start.

However, securing a "good deal" on salary can lead to situations that are detrimental to both parties. An underpaid employee may eventually leave once they realize they are undervalued. Alternatively, a junior employee may accept a low salary, which could result in a loss for the company if the cheaper but less experienced hire turns out to be five times slower. Moreover, junior employees may not have the appropriate environment to foster their professional growth.

Junior talent is not cost-effective:
Junior employees should not be seen merely as low-cost workhorses. They are, in fact, expensive professionals. Due to their limited experience, they are content with less complex tasks that provide exposure, learning opportunities, and practice without excessive pressure.

While this arrangement can be beneficial if you already have specialists, as it allows them to focus on solving complex problems, it is not suitable if you require someone who can hit the ground running. Junior employees are typically slower at handling straightforward tasks because they are still learning, which drives up the cost per unit of work.

Who would professionals prefer to hire?
If you ask any successful freelancer, they will tell you that they prefer to hire another freelancer or a well-compensated senior employee as their assistant. Freelancers tend to avoid hiring less experienced individuals due to the challenges of onboarding and managing such hires, and because no one wants to deal with an underpaid and thus demotivated employee. Such an arrangement is likely to harm client relations.

In fact, a former manager of mine, who was both a serial founder and a freelancer, shared their perspective on this matter. They always leaned toward hiring a freelancer for data-related roles because it provided higher value for their investment.

Can't I just hire multiple juniors?
You might think that hiring several junior employees could compensate for their lower productivity. However, it's important to remember that adding more team members doesn't always lead to improved overall performance. As a general rule, once a team exceeds five members, adding more members typically doesn't result in improved productivity. This is due to communication overhead, reduced accountability, and the emergence of free riders.

Of course, if you have an efficient process in place, you can expand your team size while minimizing cross-communication overhead.

So, who should you hire for the job? The answer is clear: hire the best talent you can find. A more experienced, motivated, and autonomous freelancer is often a more cost-effective choice for getting the job done. Freelancers get paid for results, not for the time spent. While hours are used for quantification, at the end of the month, hiring managers assess the output, not the hours worked.

Next time you, as an employee or hiring manager, question whether the cost-to-value ratio of freelancers is a concern, reconsider your stance.

These are just a few of the misconceptions surrounding freelancers, and there are more to explore. In the following sections, we will address additional misconceptions and provide a well-rounded understanding of the benefits freelancers can bring to your organization.

Misconception 3: 'Freelancers are only for short projects'

It's a common mistake to assume that hiring a freelancer is primarily beneficial for short-term, one-time projects. While freelancers do excel in these situations due to their availability and competence, this doesn't mean they are limited to such scenarios.

Imagine you possess a magical cannon that generates millions of dollars in value for your company annually, with a cost of only 15,000 per "cannonball" (project). Now, let's say that building a complete data warehouse with reporting requires two "cannonballs." What if assembling a team necessitates three "cannonballs"? And creating a continuous improvement framework for a CRM involves another three "cannonballs." In such cases, wouldn't you want to secure a few more "cannonballs" (i.e., hire the same freelancer repeatedly for multiple projects or an extended duration)? This approach makes sense, given the substantial value a freelancer brings.

Freelancers are professionals who thrive on learning and growth. They have a growth mindset and the ability to enhance nearly any aspect of your organization, regardless of the specific domain. They possess the capability to optimize processes and solve problems efficiently. They are the ones who might bring you those coveted 2x multipliers.

Once you recognize the return on investment (ROI) of hiring a data freelancer, you'll understand why it makes sense to work with them for longer than just short projects.

Misconception 4: 'We need full-time help'

Many companies mistakenly believe that hiring a freelancer precludes them from obtaining full-time assistance. This is a misconception; you can indeed have full-time support from a freelancer.

In Germany, a law exists to prevent companies from hiring freelancers full-time to reduce social security costs. However, it is not uncommon to see freelancers working with a single client for 18-month stretches on a full-time basis or for years on a part-time basis. This is completely legal and compliant with the law, as long as the proper criteria are met. It's a matter of doing due diligence.

In reality, full-time help from a freelancer is entirely possible. Companies that insist otherwise, despite knowing better, may simply desire control over an employee's time. A relationship driven by insecurity rarely turns out well in the long run.

A fear-driven client may not share the same values as a freelancer with a growth mindset. Both perspectives are valid, but one is more likely to propel your organization forward while the other may hinder progress. Not everyone aspires to run a large business, and there are legitimate reasons for preferring a smaller operation.

Misconception 5: 'We want someone focused on working with us, not distracted by other clients'

Many companies express concerns that a freelancer may juggle multiple clients simultaneously, which could dilute their focus and capacity.

At first glance, this concern seems reasonable. However, there are two key reasons why it may not be as significant as it appears.

Freelancers are less distracted than your employees: While working as an employee, I often interviewed with other companies. I believed this was an excellent way to stay informed about the job market, industry trends, and various approaches taken by other teams. It also allowed me to gauge my market value as an employee.

Working for startups as an employee often meant receiving lowball salary offers. Naturally, these offers were distracting and made me question if my time would be better spent elsewhere. Do I receive similar offers as a freelancer? Absolutely. However, these offers are less distracting.

As a freelancer, I was often fully booked at competitive rates. I choose the projects I work on and am 100% focused on them because it's my choice to be there. I have no desire to work elsewhere, and I don't believe the grass is greener on the other side. If it were, I would already be there.

In summary, freelancers are less susceptible to distractions compared to employees. Their focused commitment to delivering quality work is often unwavering.

You profit from a freelancer's multiple clients: Having other clients beside you can actually benefit your organization. Every experienced freelancer will tell you that there are constant synergies between projects. They learn something new on one project one day and implement it on another client's project the next.

The fact that your potential freelancer has other clients means they are in high demand as skilled professionals, and they're rapidly expanding their knowledge. Your company can leverage this wealth of experience and knowledge to its advantage.

Misconception 6: 'We want to keep the knowledge in-house'

It's a common concern for companies that hiring a freelancer might result in losing the knowledge acquired during a project. There's often a fear that a freelancer will depart, leaving the company without anyone who understands the insights gained.

However, this perspective is flawed on several fronts.

Knowledge that doesn't lead to action is often not valuable: Not all knowledge needs to be retained. Information that doesn't translate into practical applications isn't useful. It's vital to prioritize applying and integrating acquired knowledge into your operations rather than merely cataloging it.

It's not the role of freelancers or employees to store knowledge: Storing knowledge is not the responsibility of individuals; it's the role of documentation repositories, automated workflows, and efficient processes. Relying on humans to serve as repositories for knowledge reflects a lack of clarity and efficiency in your organizational processes. The goal is to use people to drive improvements and innovation.

Freelancers excel at driving improvement: Employees who remain in a single role for an extended period often become stagnant. Their exposure to new ideas and experiences diminishes, resulting in limited growth. While employees retain knowledge, they might not actively contribute to ongoing improvements.

Freelancers, on the other hand, are continuously engaged with the broader professional landscape. They are typically brought in to drive improvements and optimize processes. Their work centers on enhancing performance, which aligns with their growth mindset.

By leveraging a data freelancer for research and development, you can still access the research findings without investing time in discovery. In some cases, a freelancer might not need to engage in research and development activities because they can apply knowledge gained from previous projects.

In conclusion

Don't let these misconceptions deter you from recognizing and harnessing the incredible value that freelancers can bring to your organization. Freelancers can be valuable assets for both short-term and long-term endeavors, and they are motivated to deliver exceptional results. Furthermore, they can offer unique insights and perspectives, enriching your organization with their multifaceted experiences. The next time you hesitate to engage a freelancer, it's essential to reassess and consider the full spectrum of advantages they bring.

From local to cloud with a flip of a switch - Load Ga4->DuckDB (MDS in a box) or go to the cloud in MotherDuck

adrian — Fri, 27 Oct 2023 12:38:01 +0000

In this article, our working student Rahul is showing us how simple it is to pull Google Analytics 4 data with dlt and put the data in DuckDB.

He uses dbt to transform and visualises the data with Metabase.

Finally, good things should be shared, so he plays with the idea of going outside of the Box and onto the cloud so he is able to serve the results of the work to stakeholders.

Code and video included.

Read about it here!

Transfer SQL-> analytics 30x faster with ConnectorX + arrow + dlt

adrian — Fri, 27 Oct 2023 10:58:07 +0000

dlt is a recently released python library for data extraction and loading, the EL in ETL. At dltHub we are big fans of optimising things and integrating those optimisations into our toolkit to enable others to re-use them.

Speed boosts and schema from arrow, dlt for loading with schema evolution

In this example, we combine ConnectorX + Arrow + dlt to extract data and load it to a strongly typed environment 30x faster than classic data transfer via sqlalchemy.

Result: Much faster, but mind the memory usage

In this example we can see 30x overall speedup on extraction and normalisation with Arrow The process took 16 seconds with arrow vs 8 minutes with sqlalchemy + dlt's JSON normaliser for 10m rows.

The output in both of methods is the same (parquet files or loaded data) with schema evolution. However, in the case of arrow, we are not iterating row by row, so we cannot perform optimisations we can while streaming from sqlalchemy, such as microbatching to keep memory use low.

Read more about it + implementation docs on our blog here