Congratulations! You just got a new job of a Data X (where X = Engineer or Analyst or Architect).

Sooner or later, you will be tasked with designing(modeling) a data pipeline or a data storage schema (like a data warehouse) for the company. Now, you had memorized a lot of abstract stuff for the job interview and actually did a pretty good job impressing your interviewers, but deep down, you start questioning if you really know how to model the data practically.

The data modeling task is:

“Can we analyze our sales by product, store, and date?”

Your goal is simple: build a dataset that helps answer this.

Fear not! And please don’t start creating the tables yet!

Data Modeling Stages

First, let’s revisit those abstract topics you memorized for the interview:

1. Conceptual modeling
2. Logical modeling
3. Physical modeling

But the knowledge of these topics alone would not be of much help to complete your task. So, let’s look how you’d go on about the actual task following these above stages.

1. Conceptual Modeling (Understanding the business)

At this stage, you’re trying to understand how the business works.

You ask questions like:

• What data does the business need?
• What data do we already have?
• Where does this data come from?
• Are these sources accessible?
• What are the high-level relationships?
• Is it worth building this pipeline?

For this example, you identify:

• Sales data exists in a sales processing system or in an Order Management System (OMS).
• Product data exists in a catalog or an excel file.
• Store data exists in another system Y.

And at a high level, you understand:

• A sale happens.
• It is tied to a product.
• It happens in a store.
• It happens at a time.

No schemas yet. Just clarity on the business and data.

2. Logical Modeling (Structuring the Data)

Now you start thinking about how to represent this data.

You define:

a. Facts and Dimensions

• Fact: Sales (a number denoting an event happening at a point in time)
• Dimensions: Product, Store, Date (attributes of the fact that make it meaningful when analyzing).

b. Grain

• You decide: One row = one sale of one product in a store at a given time

c. Relationships

• You define how sales links to Product, Store, and Date.
• For example, there could be multiple products in a single sale (many-to-one). One sale happens in exactly one store and a single date (one-to-one).

You also decide how you want to structure the relationship between the entities like Product, Store and Date. This is called data modeling technique like dimensional model or data vault model (next blog about this?).

Anyways, at this stage:

• You are looking at the data facts and dimensions, grain and relationships.
• You are designing the schema.
• You are not thinking about databases yet. We are fully tech-stack-agnostic until this step.

3. Physical Modeling (Making It Real)

Now you move to implementation.

You take the logical schema and define:

• Tables
• Columns
• Data types
• Storage decisions

For example:

Sales table is created as:

• sale_id
• product_id
• store_id
• date
• quantity
• sales_amount

Product table is created as:

• product_id
• product_name
• category

You may also think about:

• How data is stored (database, schema)
• Partitioning or clustering
• Compression
• Indexing and plethora of other implementation quirks.

This is where the model becomes actual data in a database.

Horray! You have created your first data model! (I’m not sure if you could do it within the deadline but a win is a win nevertheless!)

Putting It Together

What did you do in each step?

1. Conceptual modeling: You understood the business and data sources.

2. Logical modeling: You structured facts, dimensions, and relationships.

3. Physical modeling: You implemented the actual tables.

It would have been so easy just to jump straight into writing SQL and creating tables. But if you have a giant database and nobody actually uses it, why would the company need you at your job?

But if you spend some time on each of these stages, you will eventually build something that actually matches the business need and will be far easier to work with in the future.

This is the fourth blog of a series of blogs about the different data engineering concepts. Stick around for more nuggets like these.

Let’s get connected here or on LinkedIn.

Part 3 of the series Data Engineering Concepts

If you’ve ever spent a few minutes of your life away from the hustle and bustle of daily life (or now, away from your screens), you’ve probably been struck hard, at least once, by the realization that we don’t fully control our decisions. There are countless invisible hands shaping our values, goals, visions, and, in the day-to-day scale, each of our choices that we make to move towards them.

Honestly, I have not been spending much time away these days so I can give you only a short generic list of examples. The place you live, the people around you, your parents, the societal norms and values, your faith, your childhood, your education etc. No matter how much you try to break out of their influence, they will eventually have some say in your choices.

Wait, wait, wait! Not a journal! Not a journal.

Let’s switch gears and talk about an invisible hand that might be secretly shaping your decisions if you’re someone working in the field of software/data systems, especially your decisions related to system design and architecture.

CONWAY’S LAW

Conway’s law says that the design of a system created by an organization will likely reflect how the organization itself is structured. The exact words of Melvin Conway (according to Wikipedia) were:

“Organizations which design systems are constrained to produce designs which are copies of the communication structures of these organizations.”

In simple terms: If your whole team is a single unit, which is rare, you are more likely to create a less modular system even though you follow modularity principles. Similarly, if you have different teams, the system will likely be more modular.

In terms of communication, the teams that communicate the most will create parts of the system that have more seamless integration with each other. And the teams that don’t like meetings, will create system units that will be harder to integrate with the rest of the system.

What does it mean in data systems?

This law is starkly visible in data systems and architecture. The organization structure has a huge effect on how the data platforms are designed, built and used.

I think we can all agree that the goal of most data systems in an organization is to provide numbers (sales, revenue, customers, costs, margins etc) that show how the organization is performing. But if the organization is divided into uncommunicative departments and they meet up for a all-hands meeting once a month, they are most likely to be spending the whole meeting fighting with each other, trying to settle on what the REAL sales numbers were for that month.

Such jokes have almost become a cliché LinkedIn post material for data professionals. The term “data-silos” is often used to refer to this compartmentalization of data and facts. These jokes are first-hand proofs that this is a quite prevalent issue in many real-life data systems.

This difference in the truth is caused mainly because of two reasons that Conway’s law predicts:

a. An organization is inherently composed of several departments: sales, marketing, HR, IT etc. and not all of them use the same system for their day-to-day tasks. And when they try to use their own systems for organizing, storing and analyzing data, they might get different numbers as end result.

b. And even though, there is a centralized Data Platform, the teams that are more communicative and give more input during the design phase will get the final numbers closer to their truth than the teams that don’t like talking.

What does it mean for data professionals?

Even though simply being aware that your organization’s structure can heavily influence your data architecture may help you avoid designing flawed systems, you can also take proactive steps to prevent this from happening.

Here are a few measures I can recommend:

1. Design data platforms that break organizational boundaries.
   Break down silos and integrate all relevant data sources so that data flows freely across departments rather than stopping at team borders.
2. Involve stakeholders early and often.
   Gather feedback during both design and implementation stages to ensure everyone agrees on a single source of truth for metrics and definitions.
3. Create shared data models and governance frameworks.
   Standardize definitions for key business metrics and maintain them centrally to avoid conflicting interpretations. This ensures that even when different departments look at different data sources, they know how to communicate with other departments.
4. Inverse Conway Maneuver.
   Organize the data team so that they intermix with different business stakeholders, and create smaller overlapping groups aligned with different business domains. The goal is to create the desired architecture (the data team acting as a central hub for everyone in the org), not the existing hierarchy.
5. Continuously re-evaluate data ownership and team interfaces.
   As the organization evolves, periodically assess whether your data systems still mirror healthy communication patterns rather than legacy silos.

CONCLUSION

Conway’s law doesn’t necessarily mean that ‘structure’ in an organization is a bad thing. But it pays to be aware of what the structure can do to the systems we design and build, more so, in data related scenarios. It is only after the awareness that it can be decided whether to create systems that mirror the same structure or to put some effort into flowing against the stream.

It is just like being aware of the invisible hands that drive your decisions and goals in life. You can go with the flow if they feel right to you but you can also re-assess them and change your direction.

This is the third blog of a series of blogs about the different data engineering concepts, and is based on my notes from this Coursera course. Stick around for more nuggets like these.

Let’s get connected here or on LinkedIn.

How Conway’s Law Quietly Shapes Every Data System You Build was originally published in Towards Data Engineering on Medium, where people are continuing the conversation by highlighting and responding to this story.

“Smooth seas never made skilled sailors.” — Franklin D. Roosevelt

In the last blog, we came to know what data engineers really do. In this blog, we are going to explore how they do the things they do (or practically speaking, how they should be doing those things).

I think my last blog was clear about this: data engineering doesn’t have a cool-kid hype like training LLMs or finetuning diffusion models to make Drake wear a sari. What it does have, however, is everything you need to make all of those things work.

The cool LLMs and those ML algorithms don’t work if there’s no data or if the data is crappy. The work of data engineers is not just to dump the data from disparate sources to a target system so that it can be gobbled up by these cool applications. Yes, I admit that it involves a lot of data dumping but there are a set of principles that have to be followed so that the dumping is done securely and reliably, and so that the dumping system survives a long time.

Without further ado, let’s talk about those principles:

1. Security: The Least You Can Do

One of the most common anti-patterns in early data teams is: “Just give them admin access. We’ll clean it up later.” (Spoiler: we never do!)

In data engineering, security isn’t just a checkbox, it’s a mindset. When pipelines touch sensitive data like credit cards, healthcare info, internal emails, you can’t afford a “YOLO” mentality.

Here’s the golden rule: Least privilege, always.
Give people only the permissions they need, only for the time they need them. Just like you don’t hand over your Netflix password to someone who’s looking to watch a movie trailer, don’t give root access to people checking the column of a table.

And for the data engineers themselves:

• Use temporary credentials.
• Hide secrets and env variables.
• Learn how IAM, encryption, and network protocols work because when things go wrong, you become the incident report.

2. Data Management

“Data management” sounds like one of those overly broad corporate terms, like “strategic synergies” or “robust alignment.” But underneath that umbrella lives half of a data engineer’s job.

It includes:

• Data modeling (Should this be a dimension or a fact?)
• Data quality (Is NULL a valid value or a sad mistake?)
• Warehousing (Is this a lakehouse now?)
• Governance (Who owns this field called user_name_2_backup_new?)

3. Data Architecture: Design Like You’re Going to Rebuild It Anyway

Architecture in data engineering is not just “how the tables are joined.” It’s about designing for today with tomorrow in mind.

Good architecture is flexible. It makes reversible decisions, prepares for failures, and chooses components that work well with other teams, not just yours.

Here are some principles that you may want to tattoo on your Teams channel:

• Plan for failure. (Things will break. The goal is not “never break” but “recover gracefully.”)
• Loosely couple systems. (Let them dance, not tangle.)
• Prioritize security. (Even over speed sometimes.)
• Embrace FinOps. (Design systems that do the job without draining your AWS credits like a slot machine.)

And most importantly:

• Lead with ideas, not just tickets.
  Communicate and collaborate with all of the stakeholders and the data team while designing because architecture is a shared vision, not just a diagram in Lucidchart.

4. DataOps

Imagine if software engineers didn’t have Git, CI/CD, or Jira (okay, maybe Jira is debatable). That’s what data engineering often looked like five years ago.

Enter DataOps, a set of practices that bring agility, collaboration, and reliability to data products.

What does it actually mean?

a. Automation:

• Use orchestration tools like Airflow or Dagster.
• Build CI/CD for data pipelines.
• Automate tests for data quality and schema changes.

b. Observability:

• Know when your data looks weird.
• Monitor freshness, nulls, duplicates, unexpected spikes.
• Keep logs and create systems to audit the changes.

c. Incident Response:

• Alerts that matter.
• Create dashboards that explain if anything went wrong.
• A culture that doesn’t shoot the messenger.

In short, DataOps helps you trust your data even when you’re not staring at it.

5. Orchestration: The Invisible Conductor

You can have the best scripts and transformations, but if they don’t run in the right order with the right dependencies, you’ll spend your evenings debugging why there were no sales loaded for that day (have you checked if our products data was loaded correctly today?).

Good orchestration:

• Automates dependencies.
• Fails loudly and clearly.
• Plays well with retries, alerts, and dashboards.

Bad orchestration?
It’s why someone’s Sunday evening gets ruined.

6. Software Engineering: Because SQL Alone Won’t Save You

There was a time when data engineering was just SQL and cron jobs. Those times are over.

Modern data engineers write a lot of code: Python, Spark, dbt, APIs, tests. And that code needs to be:

• Readable
• Reusable
• Modular
• Testable

In other words, you need to know software engineering.
Version control, environment management, testing, code reviews. They’re not optional anymore.

Final Thoughts: The Part You Don’t See

Most of what makes a great data engineer is not what shows up on the analytics dashboards and reports. It’s the habits, the design decisions, the trade-offs, the duct-taped late-night hotfixes that later get replaced with clean abstractions.

So next time someone asks what data engineers do, you can say:
“We move data from A to B, yes. But we do it securely, reliably, observably, and in a way that won’t haunt our successors in 2027.”

This is the second blog of a series of blogs about the different data engineering concepts and technologies, and is based on my notes from this Coursera course. If you’ve ever wanted to see what a data engineer does instead of just reading job descriptions full of buzzwords, stick around.

Until then, may your data be clean and your DAGs never fail.

Let’s get connected here or on LinkedIn.

“Civilization advances by extending the number of important operations which we can perform without thinking about them.” — Alfred North Whitehead

If you’re anything like me, the term data engineering might’ve sounded boring at first. Like some grey-suited person automating spreadsheets in a dark corner of the internet. I mean, who gets excited by hearing the word ‘pipeline’?

Turns out, a lot of people do. Because once you look under the hood, data engineering is the quiet powerhouse that makes everything from your Spotify Wrapped, to fraud detection, to ChatGPT, actually work. It’s like plumbing. If it breaks, everything else floods.

So in this blog, I want to map out what data engineers really do, how their work shows up in the apps and decisions around us, and why it’s way more exciting than just loading CSVs into a database (though yes, sometimes we do that too).

So… What Even Is Data Engineering?

Let’s start simple.

Data engineering is about moving, cleaning, and organizing data so that others (analysts, ML models, dashboards, decision-makers, your future robot overlords) can use it to do cool stuff.

But don’t let that fool you. That deceptively short sentence hides an entire universe of work, from streaming billions of records in real time to building bulletproof pipelines that don’t collapse every time someone uploads a weird Excel file.

The Data Engineering Lifecycle

Here’s the basic lifecycle of a data engineer’s job, a journey that begins with messy chaos and ends with beautifully structured, queryable magic:

1. Ingest
Raw data resides in different sources such as APIs, databases, logs, files, sensors, toasters, etc. and we have to pull them all into a centralized repository of data. The pulls could be made in batch (intervals of few to many hours) or streaming (real-time). Some of the tools used to ingest are Airbyte, Kafka or custom Python scripts with way too many ‘try/except’s.

2. Clean & Transform
The data we get may not be in the best format. So, we need to clean it and transform it. The process consists of fixing typos, unifying formats, converting units, handling missing values, merging duplicates and anonymizing stuff. This is where bad data goes to be reborn. Some of the tools used for this process are Python, dbt, Spark and a lot of SQL sorcery.

3. Store
After cleaning data from disparate sources, we send it to a place where it can chill safely, usually a data warehouse like BigQuery or Snowflake, or a data lake like S3/GCS.

4. Serve
The data is finally ready to be mined so we have to make it accessible to other teams or apps who can do that. This could mean passing it to APIs, analytics dashboards, or machine learning models. Because if the data just sits there, did it even exist?

Wait, Isn’t That What Data Scientists Do?

Let’s clear up the confusion. These are some other data people as well but they have different priorities.

Data Engineer: “How do I move and structure this data?”

Data Scientist: “What can I predict or explain with this data?”

Data Analyst: “What’s happening in the data?”

Of course, in smaller teams, these roles blur. But trust me, nobody wants their ML model failing because the timestamp column had emojis.

Why Should I Care?

Still sounds abstract? Let me ground this.

Data engineers help companies:

• Predict demand: So stores don’t overstock purple Crocs.
• Catch fraud: Like when someone makes 10,000 transactions in 3 seconds.
• Improve UX: By personalizing app content based on behavior.
• Automate reports: So analysts can go back to analyzing, not copy-pasting SQL results.

Behind all of this is a reliable data pipeline humming quietly in the background, until it breaks and everyone panics.

This will be the first blog of a series of blogs about the different data engineering concepts and technologies. If you’ve ever wanted to see what a data engineer does instead of just reading job descriptions full of buzzwords, stick around.

Until then, may your data be clean and your DAGs never fail.

Let’s get connected here or on LinkedIn.

“Not everything that can be counted counts, and not everything that counts can be counted.” — William Bruce Cameron

The fact that numbers exist is probably how humans have been able to organize societies so well, have sent Katy Perry to the space and will probably soon create artificial copies of their brains. Metrics help us summarize a lot of information with a number, communicate the results efficiently and effectively, make decisions and take actions.

This makes it hard for us to not fall in love with our numbers. Calories on the protein bar. SAT scores. Steps on fitness trackers. Views on your YouTube videos (guilty!). Metrics give us a sense of control in a chaotic world, like tiny icebergs that we can walk over to cross an ocean.

But sometimes we tend to forget that some icebergs might be enormous, their actual body hidden under the surface. Similarly, we tend to forget or disregard what some metrics actually mean. Worse, we try to optimize those metrics even though it doesn’t improve the quality of the thing that we’re trying to improve. We become so focused on the metrics and forget that they are just some proxies that we made to measure quality.

The issue looks harmless at first but it has been messing up a lot of things if we start looking closely. I want to talk about two of the examples that I find particularly distressing.

The Report Card Menace

If you are from a third world country like me, it should have been a cliché for you but the issue is worth repeating once again here ; the importance given to exam scores is more than absurd. People have forgotten that those scores were meant to evaluate the quality (and quantity ?) of the knowledge gained.

It is rare for the students to get tested on problem solving skills, critical thinking or real world application of the materials taught (the only things that matter). If you score high on certain tests, you are automatically considered a genius. And there are a lot of other ways to score high on those tests.

Students find a way to hack the exams, be it through rote memorization, cheating or learning the patterns of questions asked. The focus is on optimizing the scores (metric) not on grasping of the gist of the subjects (quality).

The Mirage of Economic Indicators

If you try to keep up with the news, you will find a lot of fancy words being thrown around: GDP, unemployment rates, inflation, market gains etc. The people in their fancy suits try to assess the economic health of the nations using these numbers.

I admit that these metrics are hard to game (but totally possible if the politicians and corporations collude to do so) but the issue here is that they don’t represent the overall well-being of the population in anyway.

GDP, for instance, measures the total value of goods and services produced, but it doesn’t account for income inequality, environmental degradation, or labor abuse. A country might boast impressive GDP growth while a significant portion of its population struggles with poverty or lack of access to basic services (you know who).

The overreliance on these quantitative measures can lead policymakers to prioritize economic growth over human development. As a result, initiatives that enhance quality of life but don’t directly boost economic indicators, such as mental health programs, environmental conservation, or community-building efforts, may be undervalued or overlooked.

I mean, it won’t really matter how many trillions of GDP the world has if the people don’t live or enjoy living.

Other Examples

I can find numerous other examples where people have found ways to optimize for the metric rather than the thing that actually mattered:

1. There are hilarious stories of people tying their FitBits to their dogs, fans or even washing machines to get their step counts. Climbing the leaderboard makes them so high that they forget that it is their health that matters not the step count.
2. Companies try to evaluate employee performance using metrics like monthly sales, ticket resolution time or stack ranking which incentivize hurried deals, short-term gains, aggressive competition that may actually hurt the profitability or the productivity of the company.
3. IT companies trying to evaluate developers’ productivity using the Lines of Code (LoC) written per given time may force them to write lengthy, messier code just to hit the metric.

Are metrics that bad?

After all of these discouraging examples, you might be thinking that we should get rid of the metrics completely and focus on the quality instead. But like I said, it probably wouldn’t have been possible to send Katy Perry to space if we didn’t have metrics at all (Yes! Sending her to space was that important! ).

I think there are two important mantras for anyone trying to avoid this pitfall:

1. Metrics are useful when they are designed carefully so that they represent the underlying quality accurately.
2. And they are useful as long as we remember that they are just metrics and don’t forget what they were meant to be measuring.

So, next time you find yourself fighting tooth and nail to get a good score (in anything), please ask yourself what is it that you’re trying to achieve.

[ I know some of you nerds might be searching for the section with ‘Reward Hacking in AI’. The blogpost has gone too long to talk about it. Maybe next time! ]

I’d love to hear your feedbacks and what you have to say on this. If you like to read blogs like these, please follow me here on Medium or on LinkedIn: https://www.linkedin.com/in/prashant-tandan-74aa94163/.

With “ALL THE SOFTWARE ENGINEERING JOBS WILL SOON BE REPLACED BY AI!” narrative flooding the internet, it is difficult to not question if the field of your interest is going down along with the whole field.

I, personally, don’t agree that humans are ever going to go out of any work; they are just going to be working at a more abstract level. But it is always a good thing to keep an eye out for new technologies so that we can integrate them more elegantly with current workflows. (And if you’re looking for those exact jobs (like me), you should know who your competition is!)

To find out where we stand with AI agents automating data science and engineering works, I started looking at the research papers and came across a really interesting paper “Spider2-V: How Far Are Multimodal Agents From Automating Data Science and Engineering Workflows? “ which was published on NeurIPS, 2024.

Let’s see what the paper says.

What is ‘Spider2-V’?

The authors introduced Spider2-V, a benchmark designed to evaluate the capabilities of multimodal AI agents in performing complex data-related tasks that typically require human expertise.

In simple terms, they have created a collection of tasks focusing on professional data science and engineering workflows which can be used to test the AI agents designed to automate data-related workflows.

It consists of 494 real-world data-related tasks using 20 enterprise-level professional applications (like dbt, Airflow, Snowflake etc.). These tasks span across various stages of data workflows, including warehousing, ingestion, transformation, visualization, and orchestration. This collection of tasks can be used to test an AI agent’s ability to generate SQL queries, write code, and manage graphical user interfaces (GUIs) across multiple professional data software systems to do a certain task.

So, should I start looking for a new job?

If you’re a data scientist or a data engineer, I know how high you are on adrenaline right now to find out the answer (I was too when I was reading the paper!). And drum roll, pleaseeee!!!!

You are safe!!! (At least, if you’re reading this in early 2025). The AI models had a maximum success rate of just 14% on their benchmark. Phew!!!

They tested the latest LLMs and VLMs on their benchmark. The models comprised of open source models like Mixtral-8x7B and Llama-3–70B, and closed-source ones including Qwen-Max, Gemini-Pro-1.5, Claude-3-Opus and GPT families. GPT-4V was the most accurate one but with just 14% of success rate.

The tasks related to “data warehousing” and “traditional data processing”
were found to be the most challenging, both less than 10% success rates. The authors highlighted that this could be because of user authentications, pop-ups, heavy GUI usage, network waiting times etc.

Where are the AI Models better/worse?

These were some other interesting findings listed on the paper like:

1. Tasks with more action steps (>15 steps) were more difficult.
2. Tasks involving authentic user accounts were much more challenging.
3. Pure GUI tasks were much easier than CLI tasks.
4. Providing a step-by-step guideline in task instructions resulted in remarkable performance gains.

Should I just chill, then?

Umm, first, let me remind you what happens when you dangle a ‘goal’ in front of super-ambitious humans. Remember Alexander the Great? Remember how he saw “the ends of the world” on a map, and by age 30, he’d marched through THREE freaking continents trying to touch them! That’s probably where this whole affair is headed too.

Spider2-V is a really good benchmark and it will make testing of the AI agents on data workflows much more easier and quantifiable. It is obvious that AI researchers and practitioners are going to try to smash the high scores, trying to frantically climb up the leaderboards. So, you should probably embrace yourself for many more papers coming out JUST ABOUT automating your job!

(I’m too lazy to look up but there probably are already some papers doing that.)

My two cents

I am not a big fan of the Ostrich Algorithm and fully accept that it is a matter of time before things start getting real. But I think there are things that we can be optimistic about!

If you’ve ever worked with data pipelines and analysis, you know how tricky sometimes the “I’m gonna automate this shit!” can get. You keep on repeating the same tasks but you cannot automate them fully because there are small nuances to be taken care of in between. I think AI will help us move past that. That will free up time for data scientists and engineers to focus on more strategic and creative aspects of their work (you’d like that, right? RIGHT?).

Also, do you ever drive around the countryside and think that you would happily be working at one of those farms??? (I’m serious! Why do you think I started working out?)

I’d love to hear your feedbacks and what you have to say on this. If you like to read about data engineering, data science and other AI stuffs, please follow me here on Medium or on LinkedIn: https://www.linkedin.com/in/prashant-tandan-74aa94163/.

Try it live here: Looker Link.
Find the full code here: GitHub Link.

MOTIVATION

I was looking around for some cool APIs to play with and I stumbled upon the n2yo website which provides information about current satellite locations for free. I instantly got the urge to create some live visualizations using the API data but got taken aback when I realized running a data pipeline on a server is going to create some deep holes in my pocket. I could have ran things locally but it doesn’t come close to being as much fun as deploying a pipeline on the cloud. So, I started looking for free options and finalized the following pipeline.

THE PIPELINE

To create an end-to-end data pipeline that fetches and visualizes the data, I used the following components:

1. n2yo API (free)
2. Google Cloud Platform ($300 credit for new accounts)
3. Google Sheets (free)
4. Looker Studio (free)

Let’s talk about the components in detail:

1. n2yo API

This is a free API that provides different types of information about the satellites. There are different endpoints that provide interesting set of information such as a satellite’s current co-ordinates (latitude, longitude), time for which a satellite is visible on an observer’s sky, all satellites present above a certain location within some radius etc.

To use the API, we can create a free account and download the API key provided. Then, we can use the key to access the REST endpoints.

The code to call the API for visible satellites over an observer looks like this:

from locations import locations
import pandas as pd
import requests

def api_caller():
    base_url = 'https://api.n2yo.com/rest/v1/satellite/'

    with open('keys/ny2o_key.txt', 'r') as file:
        ny2o_api_key = file.read().strip()

    meta_info_columns = ['seen_from', 'category', 'transactionscount', 'satcount']
    meta_info_df = pd.DataFrame(columns=meta_info_columns)
    sat_info_columns = ['seen_from', 'satid', 'satname', 'launchDate', 'satlat', 'satlng', 'satalt']
    sat_info_df = pd.DataFrame(columns=sat_info_columns)

    for location in locations:
        name = location['name']
        lat = location['lat']
        long = location['long']
        alt=  location['alt']
        radius = location['radius']
        sat_cat = location['sat_cat']

        api_info = f'above/{lat}/{long}/{alt}/{radius}/{sat_cat}'

        full_api_url = base_url + api_info + '&apiKey=' + ny2o_api_key

        response = requests.get(full_api_url)
        if response.status_code == 200:
            content = response.json()
            loc_info_df = pd.DataFrame([content['info']])
            loc_sat_df = pd.DataFrame(content['above'])
            loc_info_df['seen_from'] = name
            loc_sat_df['seen_from'] = name
            meta_info_df = pd.concat([meta_info_df,loc_info_df ], ignore_index=True)
            sat_info_df = pd.concat([sat_info_df, loc_sat_df], ignore_index=True)
            print(f"Fetched data successfully for {name}")
        else:
            print(f"Failed to fetch data for {name}. Status code: {response.status_code}")

    return meta_info_df, sat_info_df

In the code, you can see that the location details (co-ordinates, name etc.) are stored in a file called ‘locations.py’. The API key is also stored in a file inside ‘keys’ folder for security purposes (to add it to gitignore so we don’t push the keys to GitHub). Then, we construct the full API call using the location details and the API key and make a GET request. Finally, we get the response and parse it into a Pandas dataframe.

2. GCP Composer (Airflow)

Google Cloud Platform provides a managed Airflow environment called Composer where we can create, schedule, run, monitor, and manage tasks. The best part is that we can create Directed Acyclic Graphs (DAGs) to run a bunch of tasks one after another or parallely. This helps us ensure that the dependent tasks are ran after the independent tasks are completed.

Anyone can try this awesome tool with the free $300 credit given by Google. You have to create a Google Console account and then accept to use the free credits. Before creating a Composer environment, let’s enable the Google Sheets API that will be used as our storage.

Search for ‘Google Sheets API’ in the search bar. When it opens, you can click on ‘Enable’ which will prompt you to create a service account. This account will be used to create the Composer environment and the API key will be used to update our Google Sheet. You can download the API key file and save in the ‘keys’ folder.

Now, let’s create the composer environment. Search for ‘Composer’ that opens up a window like this:

Then, you can create a new environment. Give it a name, leave the rest of the settings at default and select the service account that we created for the Google Sheets API.

Now, we add the code from the GitHub repo in the “DAGs folder”. The directory structure should look like this:

The project folder contains rest of our code in the GitHub repository but we have to keep the DAG configuration file outside the project folder i.e. directly inside ‘dags/’ folder.

The file that we create to specify the DAG is ‘airflow_dag.py.’ It looks like this:

from airflow import DAG
from airflow.operators.python import PythonOperator
from airflow.operators.dummy import DummyOperator

from datetime import datetime, timedelta
import sys
import os

# Define default_args
default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'email_on_failure': False,
    'email_on_retry': False,
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

# Define the DAG
dag = DAG(
    'update_google_sheet_with_sat_data',
    default_args=default_args,
    description='Run app.py every 15 minutes',
    schedule_interval='*/15 * * * *',
    start_date=datetime(2023, 12, 12),
    catchup=False,
)

# Define the Python function wrapper
def run_app():
    project_dir = os.path.join(os.path.dirname(__file__), 'project')
    sys.path.insert(0, project_dir)
    os.chdir(project_dir)
    from app import main  # Assuming your main function is called 'main'
    main()

# Add a PythonOperator
run_app_task = PythonOperator(
    task_id='run_app',
    python_callable=run_app,
    dag=dag,
)

start_task = DummyOperator(
    task_id='start',
    dag=dag,
)

end_task = DummyOperator(
    task_id='end',
    dag=dag,
)

start_task >> run_app_task >> end_task

In the code, you can see that our DAG is triggered every 15 minutes. We have created dummy start and end task to give the DAG a starting and ending point. The main task to run is the ‘run_app_task’ that calls the main() function inside the ‘app.py’ file.

Now, since our Airflow environment is setup, we can think about how we’re going to store the API data.

3. Google Sheets

I don’t think Google Sheets needs an introduction. We are going to store our API data here and link it as a source in the Looker environment.

Create a sheet and name it as ‘Satellite data’. My sheet looks like this.

Now, we write some code to update this sheet with the API data. In the file, ‘sheets_updater.py’:

from google.oauth2 import service_account
from googleapiclient.discovery import build

class GSheet():
    def __init__(self, spreadsheet_id) -> None:
        self.SPREADSHEET_ID = spreadsheet_id
        self.SERVICE_ACCOUNT_FILE = 'keys/molten-catalyst-443503-a5-c663cf6164b1.json'
        # Authenticate and create the Sheets API service
        self.credentials = service_account.Credentials.from_service_account_file(
            self.SERVICE_ACCOUNT_FILE, scopes=['https://www.googleapis.com/auth/spreadsheets']
        )
        self.service = build('sheets', 'v4', credentials=self.credentials)

    def update_sheets_with_data(self, sheet_range, data ):
        RANGE_NAME = sheet_range
        values = [data.columns.values.tolist()]
        values.extend(data.values.tolist())
        # Prepare the request body
        body = {
            'values': values
        }

        # Write data to the sheet
        result = self.service.spreadsheets().values().update(
            spreadsheetId=self.SPREADSHEET_ID,
            range=RANGE_NAME,
            valueInputOption='RAW',  # or 'USER_ENTERED' for formatted input
            body=body
        ).execute()

        print(f"{result.get('updatedCells')} cells updated.")

    def clear_sheet(self, sheet):
        resultClear = self.service.spreadsheets( ).values( ).clear(
                                        spreadsheetId=self.SPREADSHEET_ID, 
                                        range=sheet, 
                                        body={}).execute()

In app.py:

from ny2o_api_caller import api_caller
from sheets_updater import GSheet
from parse_api_responses import combine_sat_lat_long

spreadsheet_id = '1TUBDCIEyPqv8Hfx6pARoGfH_csbCR0Gk0ZIqLa4IpzA'

def main():
    google_sheet = GSheet(spreadsheet_id)
    meta_info_df, sat_info_df = api_caller()
    meta_sheet_range = 'meta_info'
    sat_sheet_range = 'sat_info'
    sat_cordinates_df = combine_sat_lat_long(sat_info_df)
    #clear the sheets for new update
    google_sheet.clear_sheet(meta_sheet_range)
    google_sheet.clear_sheet(sat_sheet_range)
    #update sheets with new data
    google_sheet.update_sheets_with_data(meta_sheet_range, meta_info_df)
    google_sheet.update_sheets_with_data(sat_sheet_range, sat_info_df)


if __name__=='__main__':
    main()

The spreadsheet_id can be extracted from the URL of the sheet.

For example, the spreadsheet ID in the URL https://docs.google.com/spreadsheets/d/1TUBDCIEyPqv8Hfx6pARoGfH_csbCR0Gk0ZIqLa4IpzA is “1TUBDCIEyPqv8Hfx6pARoGfH_csbCR0Gk0ZIqLa4IpzA”.

You can test the code by triggering the DAG manually and seeing if the sheet gets updated with new data or not.

4. Looker Studio

Since we have our data in the Google Sheets now, the final step left is to see what Looker can do to show the data nicely. Looker is a visualization and analytics tool provided by Google that can connect to various data sources and create dashboards, charts and reports on top of it.

Open Looker Studio (should be accessible from your gmail just like other Google Services). You’ll be prompted to choose a source. Select Google Sheets and then select the sheet where our data gets loaded. Also, be sure that you choose the sheet that has the actual data (if you have followed my format, you should choose the ‘sat_info’ sheet).

Looker will pull up some default charts with the data but we will be creating a map visualization. Delete all the default charts loaded. Then, add a Google Map bubble visualizer.

Then, you can choose the fields to show up in the map. My configuration looks like this:

You can add filters as per your likings too to drill down on the satellites you want to find.

And viola! You have successfully created an end-to-end data pipeline that pulls data from an API source, transforms and stores it, and creates live visualization.

If you get stuck or have any feedback, please feel free to comment or hit me up directly on LinkedIn. Thanks for reading. Happy coding!

In a bustling data-centric company, Mr. Data Detective (DD) was known for solving the most baffling data issues. One day, his manager approached him with a mission: rescue the data engineering team, who were drowning in frustration.

“Every day, one or two of our 400 data transformation jobs fail, while inserting to the tables from SELECT statements” the manager explained. “It’s always the same error —

NULL result in a non-nullable column

We recently migrated to Snowflake from our on-premise database. The migration to Snowflake had only made things worse. Unlike our previous database platforms, Snowflake’s error messages didn’t pinpoint the exact column causing the issue. The team has no idea which column is causing the issue by looking at the error message. ”

This was no ordinary challenge. The engineers were grappling with tables with over 75 columns. Debugging involved pulling table definitions, identifying NOT NULL constraints, and manually looking through data to locate the culprit column.

Time-consuming. Tedious. And completely unsustainable. It was eating into their SLA commitments and the clients were furious!

Mr. DD knew while Snowflake excels in performance and scalability, but unlike other databases, it doesn’t provide detailed feedback on which column causes a NULL result in a non-nullable column error.

Armed with his smartness and determination, Mr. DD headed to his drawing board.

The Master Plan

“If Snowflake won’t tell us where the error is, we’ll make it tell us,” thought Mr. DD. His idea? A procedure that would dynamically validate and insert data into a table. If a validation failed, it would flag the column causing the issue, log the offending row, and provide a clear error message. Here’s the plan he devised:

1. Dynamic Validation:

Extract all NOT NULL columns from the target table.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME
=''' ||target_table_name||'''AND IS_NULLABLE = ''NO'';

2. Row Inspection:

Check if any rows in the incoming dataset had NULL values in those columns.

LET col_list RESULTSET := (EXECUTE IMMEDIATE column_list_query);

-- Validate each column
FOR col_name IN col_list DO
  validation_query := 'SELECT COUNT(*) FROM (' || insert_query || ')
                    WHERE ' || col_name.column_name || ' IS NULL;';

3. Error Logging:

Log problematic rows and columns in a centralized error_log table.

IF (validation_result > 0) THEN
   error_log_query :=
         'INSERT INTO ERROR_LOG (ROW_ID, COLUMN_NAME, ERROR_MESSAGE)
          SELECT row_number() OVER (ORDER BY 1), ''' || col_name ||
          ''', ''NULL in NOT NULL column'' FROM (' || insert_query || ') WHERE ' ||
          col_name || ' IS NULL;';

EXECUTE IMMEDIATE error_log_query;
return 'NULL result in NOT NULL column “'|| col_name || '".Check error_log for details.';
END IF;

4. Insert If Clean:

If no issues were found, proceed with the insertion.

He coded the procedure and called it validate_and_insert. Here’s a simplified version:

The Procedure

CREATE OR REPLACE PROCEDURE validate_and_insert(
     target_table_name STRING,
     insert_query STRING
    )
RETURNS STRING
LANGUAGE SQL
AS

$$
DECLARE
 col_list RESULTSET;
 column_list_query STRING;
 validation_query STRING;
 validation_result INT;
 error_log_query STRING;

BEGIN

-- Get NOT NULL columns
column_list_query := 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME =''' ||target_table_name||'''AND IS_NULLABLE = ''NO'';';

--Get NOT NULL columns
LET col_list RESULTSET := (EXECUTE IMMEDIATE column_list_query);

-- Validate each column
FOR col_name IN col_list DO
validation_query := 'SELECT COUNT(*) FROM (' || insert_query || ')
WHERE ' || col_name.column_name || ' IS NULL;';

EXECUTE IMMEDIATE validation_query;

SELECT $1 INTO validation_result FROM TABLE(RESULT_SCAN(LAST_QUERY
_ID()));
 IF (validation_result > 0) THEN
 error_log_query :=
      'INSERT INTO ERROR_LOG (ROW_ID, COLUMN_NAME, ERROR_MESSAGE)
       SELECT row_number() OVER (ORDER BY 1), ''' || col_name ||
      ''', ''NULL in NOT NULL column'' FROM (' || insert_query || ') WHERE ' ||
      col_name || ' IS NULL;';

EXECUTE IMMEDIATE error_log_query;
 return 'NULL result in NOT NULL column “'|| col_name || '".Check error_log for details.';
 END IF;
END FOR;

-- Insert if valid
EXECUTE IMMEDIATE 'INSERT INTO ' || target_table_name || ' ' ||
insert_query;
return 'Data successfully inserted into ' || target_table_name || '.';
END;
$$
;

Testing the Waters

With the procedure ready, Mr. DD handed it over to the data engineers to test on a problematic table in their Dev environment. The engineers ran it, holding their breath. The result? A beautifully clear error message:

NULL result in NOT NULL column “customer_id”. 
Check error_log for details.

Mission Success!!!

Not only did the procedure pinpoint the problematic column, but it also logged the offending rows, giving the engineers a clear path to resolution. The team was thrilled. Gone were the days of manual debugging and missed SLAs.

Mr. DD knew the battle wasn’t over. He still needed to propose this change to his manager, secure sprint time for testing and integration, and present it to the change board for approval. But for now, he allowed himself a moment to savor the victory.

The Takeaway

Data engineering isn’t just about writing SQL or managing pipelines. It’s about solving real-world problems efficiently. Through this simple yet effective solution, Mr. DD bridged the gap between Snowflake’s limitations and the team’s operational needs.

The lesson? With creativity and determination, even the trickiest challenges can be turned into scalable solutions.

And so, the legend of Mr. Data Detective grows…

The author of the blog is Prashanna Adhikari, a data enthusiast who loves to solve people’s data issues.

The manager rushed inside Mr. DD’s office, despair brimming on his face.

Their guitar company had just added a chatbot to their website to help the customers figure out the type of guitar they want. They had chosen a top notch state-of-the-art LLM as the engine for the chatbot. But as his manager was playing with it and digging into the questions, he realized that the chatbot had no clue what it was being asked about. It talked about made up types of guitars and provided wrong details for the actual guitars, but it sounded fully confident. The manager suspected that it may as well have been a drugged person acting as a chatbot behind the scenes.

Mr. DD listened to his manager’s grievances and reminded him how any LLM they picked could not possibly act as a guitar nerd like they wanted. And, there could be a bunch of factors for this behavior, mainly:

1. Lack of domain exposure during training: Their LLM probably had not seen enough data about guitars during its training process so it doesn’t know anything about guitars yet.
2. Training for different goals: The LLM could have been trained to optimize other qualities like generalizability, fluency, confidence, creativity etc. but not factual accuracy.
3. Training cut-off time: There could have been a lot of new updates in the “guitar culture” that happened after the LLM’s training data was prepared and finalized. This caused it to miss out on that information.
4. Questioning without enough context: A simplistic reduction of a LLM’s task is to pick the next word from a list of options given the sequence of previous words. And the words are clustered around certain contexts. So, if the manager’s questions (prompts) didn’t provide enough context about the information he wanted, the LLM would not pick the word from the correct context and thus, the answer seems random.

The manager seemed quite convinced by Mr. DD’s reasons as he thought he too would have been confused if he had never read anything about guitars or if somebody asked him questions out of the blue without any context. He could sympathize with the chatbot now.

Mr. DD’s smirk hinted that he had something brewing in his mind. Without his manager insisting much, he started about the solutions that could fix their “drugged chatbot”.

1. Asking better questions:

The first solution would be to create better questions with enough context, instructions and examples to help the LLM pick the right words. This technique is often called “prompt engineering”. Mr. DD hates the fact that people have given this simple task such an intimidating name but who can fight with the crowd.

But this solution would not be enough if the LLM doesn’t know anything about guitars. So, Mr. DD moves on to the next solution.

2. Further training the LLM:

Mr. DD could gather up all of the text data related to guitars: from the internet, sites that talk about guitars, forums where the guitar nerds squabble about the best strings, and the company documents about their guitars and further train the LLM on this data. This process is called “fine tuning”.

This would provide the chatbot with enough information about the guitars and combined with the first solution, the answers could get really reliable.

This solution sounded good to the manager but he had one question. What if there is a huge cultural shift in the “guitar culture” in next three months? Realizing the stupidity of his question, he tried to compensate by asking a more realistic one: what if the company re-classified all the guitars into totally new categories. Will we have to do this fine-tuning again and again if any information changes?

Mr. DD gave a ‘yes’ nod but he had one more trick under his sleeve. He knew that this one was going to blow his manager’s mind.

3. Grounding the LLM’s response with actual truth:

Remember the collection of text data that Mr. DD was proposing to collect for fine-tuning? Wouldn’t it be awesome if the LLM could access that text data in realtime whenever a user asks it a question related to guitars?

Such a framework for looking up the ground truth to assist in the LLM’s response generation process is called Retrieval-Augmented Generation (RAG). This fancy term is quite simple if broken down into steps:

a. Using the prompt given by the user, the framework tries to retrieve the most relevant ground truth ( i.e. actual document/s or part/s of document) in the collection of data. Hence, the term “Retrieval”.

b. The relevant ground truth extracted from the collections is then augmented to the user prompt as the context to be used by the LLM for responding. Hence, the term “Augmented”.

c. The LLM then generates the response that is based on the provided ground truth. Hence, the term “Generation”.

Just like Mr. DD had guessed, the manager could not hold back his amazement with this method. This was probably the cure that their drugged chatbot needed.

The manager could also see how it removes the need to fine tune the LLM again and again if the existing data was changed or new data was added. They could just update the collections of the data and the RAG framework would pick up the ground truth from the updated data.

He could also see that looking up for the ground truth while a user is actively interacting with the LLM could delay the response time but it was a trade-off he was willing to make in order to have a reliable chatbot.

Mr. DD was just coming to a dreadful realization but his manager reached there first and asked,” Will you help us implement this RAG thing, Mr. DD?”

DD’s first reaction? “What news! Didn’t I tell you that before?”. Anyways, the request looked simple but DD knows that this could turn into a real mess real quick. The database has all of the history data from past 3 years. But DD is not someone who dwells in the past too much so he puts on his detective hat and starts doing his magic.

DD’s first idea was that he could change the existing ITEM_ID column to string using ALTER statements.

Method 1: Changing the Type of the Column In-Place

Converting a column’s data type in-place means altering the column directly without creating a new one. This kind of type conversion is supported on most database platforms, though the syntax may vary slightly depending on the platform. For DD’s PostgreSQL database, the process looks like this:

PostgreSQL

In PostgreSQL, you can use the ALTER TABLE command with the USING clause to convert a column from numeric to string directly in place.

ALTER TABLE DD_item_table ALTER COLUMN ITEM_ID TYPE VARCHAR(255) USING ITEM_ID::VARCHAR;

• The ALTER COLUMN command changes the data type to VARCHAR(255).
• The USING clause ensures that the numeric data is explicitly cast to a string.

PostgreSQL requires the USING clause to explicitly handle type conversions when changing between incompatible types.

For some other databases, the query looks like this:

MySQL

ALTER TABLE DD_item_table MODIFY COLUMN ITEM_ID VARCHAR(255);

SQL Server

ALTER TABLE DD_item_table ALTER COLUMN ITEM_ID VARCHAR(255);

“This should be easy!”, thought DD and relaxed for a while. But he knows that you can never be too careful while dealing with critical data so he starts thinking of the edge cases. And nonetheless he found one such case that would have busted his ass if he wasn’t careful enough. What if my new data type is too small to hold the old data and the history data gets truncated while converting. And other some questions came into his head: What if I want to keep the the old data intact in the same table? Or what if my database didn’t support this in-place type conversion in the first place. There should be a more reliable process!

Method 2: Creating a New Column with the Same Data

If your database system doesn’t allow in-place conversion (or you want to be cautious), creating a new column with the desired data type is a good alternative. Here’s a general process that works for almost all databases:

Step 1: Add a New Column

First, create a new column with the desired STRING (or VARCHAR) data type.

ALTER TABLE DD_item_table ADD COLUMN ITEM_ID_STR VARCHAR(255);

Step 2: Copy Data from the Old Column to the New Column

Now, copy the existing NUMERIC data from the old ITEM_ID column to the new ITEM_ID_STR column, casting or converting as necessary. For DD's PostgreSQL, the query would look like:

UPDATE DD_item_table SET ITEM_ID_STR = ITEM_ID::VARCHAR;

For other databases, the query would look like:

MySQL:

UPDATE DD_item_table SET ITEM_ID_STR = ITEM_ID;

SQL Server:

UPDATE DD_item_table SET ITEM_ID_STR = CAST(ITEM_ID AS VARCHAR(255));

Oracle:

UPDATE DD_item_table SET ITEM_ID_STR = TO_CHAR(ITEM_ID);

Step 3: Drop the Old Column (Optional)

You should verify that the data in the new column(ITEM_ID_STR) is the same as in your old column (ITEM_ID). You can do a comparison of the columns using NOT IN or EXCEPT clauses. After you’re sure that the data has been copied successfully, you can drop the old ITEM_ID column if it’s no longer needed.

ALTER TABLE DD_item_table DROP COLUMN ITEM_ID;

Step 4: Rename the New Column (Optional)

If you want the new column to have the original column’s name, rename it.

ALTER TABLE DD_item_table RENAME COLUMN ITEM_ID_STR TO ITEM_ID;

Now you have the same column ITEM_ID with a different data type and you know that you haven’t messed up anything while doing that.

“Phew, that took a while!” DD was just starting to feel his dopamine levels rising but another thought hit him like a bus. ITEM_ID is probably a primary key and has been referenced in other tables as a foreign key. If that is the case, he now needs to convert those fields too. He also needs to check the table DDLs to see if the alteration of the column has caused any existing indexes or constraints to break. He still has a lot to do before he sends out the email saying “I am ready. Bring those new ITEM_IDs in! “