Forem: Dror Atariah

Encoding an Excel File in a Power BI Report

Dror Atariah — Fri, 17 Oct 2025 13:18:33 +0000

Don't ask me why, but in my team we have a Power BI report that has an Excel file embedded in it. Kind of hard coded. Now, the time came and the Excel had to be updated. So, with the following snippet I created a new binary representation and replaced it in the right query:

let
    Source = Excel.Workbook(File.Contents("C:\Path\to\my\file.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Converted to List" = Table.ToRows(#"Promoted Headers"),
    #"Serialized to JSON" = Json.FromValue(#"Converted to List"),
    #"Compressed to Binary" = Binary.Compress(#"Serialized to JSON", Compression.Deflate),
    #"Converted to Text" = Binary.ToText(#"Compressed to Binary", BinaryEncoding.Base64)
in
    #"Converted to Text"

In turn, I could copy the generated string and replace it in the line:

Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("binaryRepresentationOfTheExcel", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Foo = _t, Bar = _t]),

Quickly Clean Up Databricks New Queries

Dror Atariah — Thu, 02 Oct 2025 06:24:00 +0000

When you create a new query in Databricks, it gets the name New Query 2025-10-02 8:25am. After a while, you will have a bunch of these queries that most likely are not needed. It seems like there's no way how to clean them easily from the web UI. But, using the CLI it is straightforward:

databricks queries list -o json \
| jq -r '.[] | select(.display_name | startswith("New Query ")) | .id' \
| while read -r query_id; do
    databricks queries delete "$query_id"
done

⚠️ WARNING ⚠️: Run this with caution! It can delete queries that you need!

Spot the Sale - My take

Dror Atariah — Wed, 24 Sep 2025 13:16:50 +0000

Here is my Python and duckdb take on the Spot the Sale challenge by Maven Analytics. Let's start with Python!

Solutions

To start, we need to load the data:

promo_df = pd.read_csv("promotions.csv", parse_dates=["start_date", "end_date"])
orders_df = pd.read_csv("orders.csv", parse_dates=["order_date"])

First Take

My approach uses a User Defined Function:

def get_promo(dt: pd.Timestamp) -> str:
    filtered_promo = promo_df.where(
        (promo_df["start_date"] <= dt) & (promo_df["end_date"] >= dt)
    ).dropna()
    if filtered_promo.empty:
        return ""
    return filtered_promo["promo_id"].values[0]

which takes a datetime and checks whether it falls within the date range of a promotion. Lastly, we use the UDF with the apply method:

res = orders_df.order_date.apply(lambda x: get_promo(x))

This produces a series of promotion IDs, which I can check as follows:

len(res[res == ""])

and get the expected number: 1916. However, this approach is very slow: about 1 second! Let's try to improve this.

Take Two using `cut`

Using the start and end dates of each promotion, we construct an IntervalIndex which is fed into the pd.cut function:

res = pd.cut(
    orders_df["order_date"],
    bins=pd.IntervalIndex.from_arrays(
        promo_df["start_date"], promo_df["end_date"], closed="both"
    ),
    labels=promo_df["promo_id"],
)

Timing this shows a significant decrease to 1.8 ms.

Take Three using `duckdb`

If we assign the following SQL query to my_q:

select
    count(*)
from (
    SELECT od.*,
    coalesce(pd.promo_id, '') as promo
    FROM
        orders_df as od
    left join
        promo_df as pd on od.order_date between pd.start_date and pd.end_date
) t
where 1=1
and promo = ''

then duckdb.sql(my_q) runs in 430 μs! This is, once again, a significant improvement.

The Solution by Maven

Lastly, the Python solution provided in the video is:

pd.merge_asof(
    orders_df.sort_values("order_date", ascending=True),
    promo_df.sort_values("start_date", ascending=True),
    left_on="order_date",
    right_on="start_date",
    direction="backward",
).query("order_date <= end_date")

This solution runs in 1.2 ms and is comparable to the cut approach but slower than duckdb approach. Moreover, merge_asof is not very popular, which negatively impact the readability.

Discussion

Which solution do you like best? Here is my summary:

Solution	Timing	Pros	Cons
Naive	1 s	Easy to read / Flexible	Very Slow
`cut`	~2 ms	Fast / Flexible	A little harder to read
`merge_asof`	~1 ms	Even faster	A little harder to read and adjust
`duckdb`	430 μs	Fastest / SQL based	Requires another dependency on top of `pandas`

Notes from PyData Berlin 2025

Dror Atariah — Wed, 10 Sep 2025 09:20:34 +0000

After a long, and probably too long, time I finally attended PyData Berlin (2025) again. And it was great! In this post, I will list the talks I attended and share some highlights. Later, I will probably write a little more about more specific things. So, let's start.

Beyond Linear Funnels: Visualizing Conditional User Journeys with Python

By Yaseen Esmaeelpour talk's page

Yaseen presented a nice project he has been working on: funnelius designed to help with visualizing non-linear, conditional user funnels. It uses pandas, Graphviz, and Streamlit and the results are useful! Getting started seems to be very simple and requires a simple data set:

user_id	action	action_start	answer
1	1st question	2025-04-10 12:04:15.00	Yes
1	2nd question	2025-04-10 12:05:17.00	No
2	2nd question	2025-04-10 12:05:17.00	Yes
2	3rd question	2025-04-10 12:08:27.00	Yes

The tool will construct the journeys based on the user_id and their timestamps.

Accessible Data Visualizations

By Maris Nieuwenhuis talk's page

With the upcoming European Accessibility Act and the related EN 301 549 standard, the issue around accessibility is going to take some attention. In this talk Maris presented a design system aimed at accessibility. I found the use of patterns alongside the colors very appealing.

During the talk a link to a color palette finder was shared.

More than DataFrames: Data Pipelines with the Swiss Army Knife DuckDB (Workshop)

By Mehdi OUAZZA Workshop's repo

This was a very interesting workshop, that would require a lot of "post-processing" on my part.

💡 DuckDB was mentioned a lot during the conference and I have been playing with it a little. However, I still don't understand how it can be used in many practical cases as described. At the end of the day the data should be loaded in the machine where duckdb is running. How does it work? The WASM support was raised --- but for that to work the whole data has to be first fetched by the client? Am I missing something? Leave some comments below!

AI-Ready Data in Action: Powering Smarter Agents (Workshop)

By Violetta Mishechkina and Chang She

This workshop covered a lot of material and two tools that I never used before: dlthub (and the git repo here) and lancedb. I am surely going to look deeper into these tools (probably starting with dlthub). The workshop followed this notebook.

Narwhals: enabling universal dataframe support (Keynote)

By Marco Gorelli

In this talk Marco:

Presented the Narwhals package, and
Shared insights about managing an open source project. The following is taken from the slides:
- Non-negotiable:
- Clear contributing guide
- Code of conduct
- Nice to have
- Low-pressure communication (like Discord)
- Open community calls
- Recognise contributors by elevating their permissions (but be careful!)
- Have a clear vision, don’t democratise decision-making too early
- Share a roadmap and priorities
- Roles:
- Merge commits: be liberal. As long as people get the spirit of the project, it’s all reversible
- Releases: be picky. Only let people who you know in person, or who's identity you’re sure of, do this

As stated in the talk, narwhals is "for builders", therefore, at the moment, it is not of high interest for me, but it is surely worth knowing about! I will come to it later. Two topics came up during the call and I will come back to them for sure:

https://github.com/ibis-project/ibis and
https://pola.rs/ - Polars was mentioned almost as often as duckdb. I have to admit that I didn't have the chance to use it so far.

The Importance and Elegance of Polars Expressions

By Jeroen Janssens - Talk's page

First, a word of disclaimer --- I have no experience with polars. You should surely watch this talk (it should become available on YouTube soon). Maybe I will discuss it more in another post, but I would mention briefly the interesting discussion I had with Jeroen. I believe we ended up agreeing that it could be a good idea to wrap expressions in functions where, for example, column names are parameterized. Doing that, makes the expressions more testable. As mentioned, more on that in the future.

Building Reactive Data Apps with Shinylive and WebAssembly

By Christoph Scheuch - Talk's page

An interesting solution to deliver data to consumers directly in the browser. It is an interesting approach that can bring both usability and maintainability to the data delivery processes. However, I suspect, as was also discussed in the talk, that the main challenge is the last mile: authentication. Specifically, securely using credentials needed to access upstream data sources is a serious challenge since the whole app is exposed in the browser. So, probably without some backend it is not possible. Here's a link to an example.

Deep Dive into the Synthetic Data SDK (Workshop)

By Tobias Hann - Talk's page

In this workshop Tobias presented the Synthetic Data SDK which helps generating synthetic data based on available data sets. The following Colab notebook was shared and it covers:

SDK Core Capabilities
Differential Privacy
Conditional Generation
Multi-Table Synthesis
Fair Synthetic Data

I believe this is a tool that can be extremely helpful for data scientists and researchers who need to generate synthetic data quickly and efficiently. The SDK provides a wide range of capabilities and even some guarantees!

Forget the Cloud: Building Lean Batch Pipelines from TCP Streams with Python and DuckDB

By Orell Garten - Checkout talk's material

I really liked Orell's question at the beginning of the talk: "WHO PROCESSES LESS THEN 100 GB OF DATA PER DAY?". In too many cases the cloud platforms used like Databricks are an insane overkill. The lean approach presented by Orell is definitely worthy of a deeper look. Maybe also connecting it to dlthub? What do you think?

Bye-Bye Query Spaghetti: Write Queries You'll Actually Understand Using Pipelined SQL Syntax

By Tobias Lampert - Talk's page

I think this was the most practical talk I attended in this conference. In case you didn't know it, just like I didn't, there's a new dialect in town: SQL Pipeline Syntax! In contrast to traditional SQL, it guarantees that the first n rows of SQL statements will always be valid! No more commenting out sections, moving things around, etc. while developing/debugging SQL code. Here's a little example taken from Databricks. The following two statements are identical:

SELECT
  c_count,
  COUNT(*) AS custdist
FROM
  (
    SELECT
      c_custkey,
      COUNT(o_orderkey) c_count
    FROM
      customer
      LEFT OUTER JOIN orders ON c_custkey = o_custkey
      AND o_comment NOT LIKE '%unusual%packages%'
    GROUP BY
      c_custkey
  ) AS c_orders
GROUP BY
  c_count
ORDER BY
  custdist DESC,
  c_count DESC;

and

FROM customer
|> LEFT OUTER JOIN orders ON c_custkey = o_custkey
                           AND o_comment NOT LIKE '%unusual%packages%'
|> AGGREGATE COUNT(o_orderkey) c_count
   GROUP BY c_custkey
|> AGGREGATE COUNT(*) AS custdist
   GROUP BY c_count
|> ORDER BY custdist DESC, c_count DESC;

So far I haven't tried it (yet), but I am definitely planning on doing so. I find it an important development of SQL. What do you think? Did you use it?

Docling: Get your documents ready for gen AI

By Christoph Auer - Talk's page

Yet another great talk that brought to my attention docling which is a lean and useful tool that extracts AI ready files (e.g., Markdown) from various document types like PDF, PPT, etc. etc. The tool is very easy to use and I will come back to it in the future and learn about its capabilities further.

Scraping urban mobility: analysis of Berlin carsharing

By Florian König - Talk's page

In this talk Florian presented his attempt at optimizing (dis-)positioning of vehicles in a carsharing fleet. I enjoyed a lot hearing about his approach. Unfortunately, he was not able to crack the secret, but I am sure that with more reliable data he could help a lot in this domain! Check it out on your own: here is a link to the slides.

JSON Schema to PySpark StructType

Dror Atariah — Wed, 27 Aug 2025 10:03:34 +0000

Assume that you get the following JSON schema specification:

// Saved in schema_def.json
{
  "title": "Customer",
  "description": "Schema for Customer data",
  "type": "object",
  "properties": {
    "name": {
      "type": "string",
      "description": "Customer name"
    },
    "age": {
      "type": "integer",
      "description": "Customer age"
    }
  },
  "required": [
    "name"
  ]
}

Naturally, when reading data that adhere to this definition, you'd like to have a programmatic way to obtain a StructType object.
Based on my research, this seems to be a little tricky and there is no way to do it nicely (i.e., with the help of a well accepted package).

So, here is a little code snippet that you can use to achieve what you need:

import json
from pyspark.sql.types import (
    StructType,
    StructField,
    StringType,
    IntegerType,
    DoubleType,
    BooleanType,
    ArrayType,
    LongType,
    NullType,
)

def json_schema_to_pyspark_struct(schema: dict) -> StructType:
    """
    Converts a JSON schema dictionary to a PySpark StructType.

    This function recursively parses the JSON schema and maps its types
    to the corresponding PySpark SQL data types.

    Args:
        schema: A dictionary representing the JSON schema.

    Returns:
        A PySpark StructType object representing the schema.

    Raises:
        ValueError: If an unsupported JSON schema type is encountered.
    """

    # Mapping from JSON schema types to PySpark data types
    type_mapping = {
        "string": StringType(),
        "number": DoubleType(),  # More general than FloatType
        "integer": LongType(),    # More general than IntegerType
        "boolean": BooleanType(),
        "null": NullType(),
    }

    fields = []

    # Get the list of required fields, if specified
    required_fields = schema.get("required", [])

    # Iterate over each property defined in the schema
    for property_name, property_details in schema.get("properties", {}).items():
        field_type_str = property_details.get("type")
        is_nullable = property_name not in required_fields
        field_type = None

        if field_type_str == "object":
            # Recursively call the function for nested objects
            field_type = json_schema_to_pyspark_struct(property_details)
        elif field_type_str == "array":
            # Handle arrays by getting the type of the items in the array
            item_schema = property_details.get("items", {})
            if not item_schema:
                 # Default to StringType if items schema is not defined
                item_type = StringType()
            elif item_schema.get("type") == "object":
                # Recursively call for arrays of objects
                item_type = json_schema_to_pyspark_struct(item_schema)
            else:
                item_type = type_mapping.get(item_schema.get("type"))
                if not item_type:
                    raise ValueError(f"Unsupported array item type: {item_schema.get('type')}")

            field_type = ArrayType(item_type, True) # Arrays can contain nulls
        else:
            # Map simple types using the dictionary
            field_type = type_mapping.get(field_type_str)
            if not field_type:
                raise ValueError(f"Unsupported type for property '{property_name}': {field_type_str}")

        # Add the constructed field to our list of fields
        fields.append(StructField(property_name, field_type, is_nullable))

    return StructType(fields)

So, you can obtain the StructType as follows:

with open("schema_def.json") as f:
    schema_def = json.load(f)
schema = json_schema_to_pyspark_struct(schema_def)

This yields: StructType([StructField('name', StringType(), False), StructField('age', LongType(), True)]).
Nice, using this schema, we can now read/create data that adhere to the schema.
For example:

spark.createDataFrame([["Alice", 10], ["Bob", 11]], schema=schema)

What do you think? How do you solve this use case?

Disclaimer: The code above is mostly a collaboration with Gemini. So be careful ;)

The Smart Way Pandas Handles Overlapping Column Names

Dror Atariah — Tue, 19 Aug 2025 11:03:29 +0000

import pandas as pd

We start with two data frames like those:

df1 = pd.DataFrame(
    {
        "id_left": [1, 2, 3],
        "first_name": ["Alice", "Bob", "Charlie"],
        "last_name": ["Smith", "Jones", "Brown"],
    }
)
df2 = pd.DataFrame(
    {
        "id_right": [1, 2, 3],
        "last_name": ["SmithX", "JonesX", "BrownX"],
        "age": [25, 30, 35],
    }
)

Specifically:

df1

	id_left	first_name	last_name
0	1	Alice	Smith
1	2	Bob	Jones
2	3	Charlie	Brown

And,

df2

	id_right	last_name	age
0	1	SmithX	25
1	2	JonesX	30
2	3	BrownX	35

Next, we want to merge these two data frames. But, note that the column last_name appears in both AND they contain different values. This can happen when the left table is obtained using one processing flow and the right table using another flow. This way or the other, the described situation leads to an ambiguity which in turn leads to a challenge. For example, in Spark you will need to be very careful when working with the resulting data frame.

Let's merge:

df1.merge(df2, left_on="id_left", right_on="id_right", how="left")

	id_left	first_name	last_name_x	id_right	last_name_y	age
0	1	Alice	Smith	1	SmithX	25
1	2	Bob	Jones	2	JonesX	30
2	3	Charlie	Brown	3	BrownX	35

Nice! The columns with the identical names were suffixed with _x and _y respectively. This is thanks to the default values of the parameter suffixes. You can find it in the documentation. Personally, I find it rather smart solution that adheres to the Python principal of "explicit is better than implicit".

What do you think?

The Undeterministic Nature of Agents

Dror Atariah — Fri, 25 Jul 2025 12:15:53 +0000

I started to play around with agno and came across the example given for exceptions. Rather quickly, I ran into an unexpected behavior of the agent. Here is a little modified version that I used:

from agno.agent import Agent
from agno.exceptions import RetryAgentRun
from agno.utils.log import logger

model =  # Set you model here

def add_item(agent: Agent, item: str) -> str:
    """Add an item to the shopping list."""
    logger.info(f"Tool is adding the item: {item}")
    if agent.session_state is None:
        raise RuntimeError("Session state is not initialized.")

    agent.session_state["shopping_list"].append(item)
    len_shopping_list = len(agent.session_state["shopping_list"])
    if len_shopping_list < 3:
        raise RetryAgentRun(
            f"Shopping list is: {agent.session_state['shopping_list']}. "
            "Minimum 3 items in the shopping list."
            + f"Add {3 - len_shopping_list} more items.",
        )

    logger.info(
        "The shopping list is now (inside the agent's call): "
        f"{agent.session_state.get('shopping_list')}"
    )
    return f"The shopping list is now: {agent.session_state.get('shopping_list')}"


agent = Agent(
    model=model,
    # Initialize the session state with empty shopping list
    session_state={"shopping_list": []},
    tools=[add_item],
    markdown=True,
)


def add_item_util(item: str) -> None:
    """Add an item to the shopping list."""
    logger.info(f"Adding item: {item}")
    logger.info(f"Session state before: {agent.session_state}")
    agent.print_response(f"Add {item}", stream=True)
    logger.info(f"Session state after: {agent.session_state}")


add_item_util("milk")
add_item_util("beer")
add_item_util("beans")

Now, you would probably expect that each call to add_item_util would add a single item to the shopping list. But, behold! That's not the case (I had to run it 2-3 times to face the glitch):

INFO Adding item: milk
INFO Session state before: {'shopping_list': []}
INFO Tool is adding the item: milk  👈🏻👈🏻👈🏻 Expected
INFO Tool is adding the item: bread 👈🏻👈🏻👈🏻 NOT Expected!
INFO Tool is adding the item: eggs  👈🏻👈🏻👈🏻 NOT Expected!
INFO The shopping list is now (inside the agent's call): ['milk', 'bread', 'eggs']
┏━ Message ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Add milk                                                              ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Tool Calls ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ • add_item(item=milk)                                                 ┃
┃ • add_item(item=bread)                                                ┃
┃ • add_item(item=eggs)                                                 ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Response (2.7s) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Your shopping list now includes:                                      ┃
┃                                                                       ┃
┃  • Milk                                                               ┃
┃  • Bread                                                              ┃
┃  • Eggs                                                               ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
INFO Session state after: {'shopping_list': ['milk', 'bread', 'eggs']}
INFO Adding item: beer
INFO Session state before: {'shopping_list': ['milk', 'bread', 'eggs']}
INFO Tool is adding the item: beer
INFO The shopping list is now (inside the agent's call): ['milk', 'bread', 'eggs', 'beer']
┏━ Message ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Add beer                                                              ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Tool Calls ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ • add_item(item=beer)                                                 ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Response (1.7s) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Beer has been added to the shopping list.                             ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
INFO Session state after: {'shopping_list': ['milk', 'bread', 'eggs', 'beer']}
INFO Adding item: beans
INFO Session state before: {'shopping_list': ['milk', 'bread', 'eggs', 'beer']}
INFO Tool is adding the item: Beans
INFO The shopping list is now (inside the agent's call): ['milk', 'bread', 'eggs', 'beer', 'Beans']
┏━ Message ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Add beans                                                             ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Tool Calls ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ • add_item(item=Beans)                                                ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
┏━ Response (1.7s) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃                                                                       ┃
┃ Beans have been added to the shopping list.                           ┃
┃                                                                       ┃
┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
INFO Session state after: {'shopping_list': ['milk', 'bread', 'eggs', 'beer', 'Beans']}

As expected, this is not deterministic and some runs work smoothly. But, this lack of determinism is a serious inherit challenge of the usage of LLMs. In this case, I was able to improve the situation by adjusting the description of the function add_item(agent: Agent, item: str) to the following:

Add an item provided by the user to the shopping list.

What do you think? How do you handle this challenges? What should I have done differently? Let's discuss in the comments.

Left Join Gotcha: Rows Duplication

Dror Atariah — Tue, 22 Jul 2025 21:10:00 +0000

I am sure you saw it a zillion times, but it can still hit you if you're being sloppy. The uniqueness of your left table is not guaranteed to hold after a left join. Assume that your left table is:

customer_id	customer_name
1	Alice
2	Bob
3	Charlie

and your right table is:

product_id	customer_id
a	1
b	1
b	2
c	2
c	3

Then, the result of the join would be:

customer_id	customer_name	product_id
1	Alice	a
1	Alice	b
2	Bob	b
2	Bob	c
3	Charlie	c

This is a very straightforward example, so you might not be surprised. But, rest assured, in more complex setups you can easily miss that and end up with duplications in your result set.

Data quality

Data quality issues can mean that your understanding of the data is misaligned with the actual nature of the data you use. Namely, you read a right table where you expect it to have unique identifiers, but in reality it has duplications. These duplications will end up in the result of the join! So the lesson learned is: ALWAYS CHECK PRE-CONDITIONS!

SQL CASE Statements: The Order Matters!

Dror Atariah — Fri, 18 Jul 2025 21:15:00 +0000

When working with SQL CASE statements for data bucketing, the order of conditions can make or break your queries. Let me show you why this matters and how to write more robust SQL.

The Setup

Let's start with a simple dataset to demonstrate the concept:

import pandas as pd
import numpy as np
from duckdb import sql

# Create sample data
N = 10
np.random.seed(42)  # For reproducibility
df = pd.DataFrame({
    'id': list(range(N)),
    'value': np.random.uniform(0, 1, N),
})

This creates a DataFrame with random float values between 0 and 1 that we want to bucket into categories.

Option 1: Order-Dependent Approach

The most common way to write a CASE statement is:

SELECT *,
    CASE
        WHEN value < 0.2 THEN 'low'
        WHEN value < 0.5 THEN 'medium'
        ELSE 'high'
    END AS value_category
FROM df

This works perfectly fine and will correctly categorize:

Values < 0.2 as 'low'
Values between 0.2 and 0.5 as 'medium'
Values ≥ 0.5 as 'high'

⚠️ The Pitfall

Here's where it gets dangerous. What if you accidentally reorder the conditions?

-- This is WRONG!
SELECT *,
    CASE
        WHEN value < 0.5 THEN 'medium'  -- This catches everything < 0.5!
        WHEN value < 0.2 THEN 'low'     -- This will never execute
        ELSE 'high'
    END AS value_category
FROM df

The problem: SQL processes CASE conditions sequentially. Once a condition is met, it stops checking the rest. In the wrong example above, any value less than 0.5 (including values that should be 'low') gets labeled as 'medium'.

Option 2: Explicit and Order-Independent

Following the Zen of Python principle that "explicit is better than implicit", here's a more robust approach:

SELECT *,
    CASE
        WHEN value < 0.2 THEN 'low'
        WHEN value < 0.5 AND value >= 0.2 THEN 'medium'
        ELSE 'high'
    END AS value_category
FROM df

Why This is Better

Order Independence: You can rearrange these conditions in any order and still get the same result
Self-Documenting: Each condition explicitly states its boundaries
Easier to Debug: When something goes wrong, it's clearer which condition is problematic
Less Error-Prone: Reduces the chance of logical errors during refactoring

Best Practices

Be Explicit: Always specify both upper and lower bounds when possible
Test Edge Cases: Make sure your conditions handle boundary values correctly
Use Comments: For complex conditions, add comments explaining the logic
Consider Order: Even with explicit conditions, think about the logical flow

Conclusion

While both approaches work, the explicit method is more maintainable and less prone to errors. It might require a bit more typing, but the added clarity and robustness are worth it.

Which approach do you prefer? Have you encountered similar pitfalls with CASE statements in your SQL work?

This post demonstrates concepts using DuckDB with Python, but the principles apply to any SQL dialect.

Filtering Out Rows Using LEFT JOIN: A Clean Alternative to NOT IN

Dror Atariah — Mon, 14 Jul 2025 19:50:00 +0000

Have you ever needed to exclude certain records from your dataset based on a blacklist or exclusion table? While many developers reach for NOT IN or NOT EXISTS, there's an elegant alternative using LEFT JOIN that's often more readable and performant. Let me show you how.

The Problem

Imagine you have a dataset and you want to exclude certain rows based on values in another table. This is a common scenario when working with:

Blacklisted users or items
Excluded categories
Records that should be filtered out based on business rules

The Solution: LEFT JOIN with NULL Check

Here's a clean approach using LEFT JOIN that I'll demonstrate with DuckDB:

Step 1: Create Sample Data

First, let's create some sample data to work with:

from duckdb import sql

# Create a main dataset
sql("""
    CREATE OR REPLACE TABLE data AS (
    SELECT
        'ID' || generate_series AS id,
        'Name_' || generate_series AS name,
        generate_series * 10 AS value
    FROM generate_series(1, 6)
    )
""")

sql("SELECT * FROM data").df()

This results in:

id	name	value
ID1	Name_1	10
ID2	Name_2	20
ID3	Name_3	30
ID4	Name_4	40
ID5	Name_5	50
ID6	Name_6	60

Step 2: Create the Blacklist

Now let's create our exclusion list:

# Create a blacklist of IDs to exclude
sql("""
    CREATE OR REPLACE TABLE black_list AS (
    SELECT 'ID2' AS ignore_id
    UNION ALL
    SELECT 'ID4' AS ignore_id
    UNION ALL
    SELECT 'ID5' AS ignore_id
    )
""")

This blacklist contains three IDs that we want to exclude from our results.

Step 3: Filter Using LEFT JOIN

Here's where the magic happens:

SELECT d.*
FROM data AS d
LEFT JOIN black_list ON d.id = black_list.ignore_id
WHERE black_list.ignore_id IS NULL

How It Works

The LEFT JOIN technique works in three steps:

LEFT JOIN: Joins every row from the data table with matching rows from black_list. If no match exists, the joined columns are filled with NULL.
NULL Check: The WHERE black_list.ignore_id IS NULL condition keeps only rows where no match was found in the blacklist.
Result: We get all rows from data except those whose IDs appear in the blacklist.

Why This Approach?

Advantages over NOT IN:

NULL Safety: NOT IN can return unexpected results when the exclusion list contains NULL values
Performance: Often more efficient, especially with proper indexing
Readability: The intent is clear - we're excluding based on a join condition

Advantages over NOT EXISTS:

Simplicity: Less nested subquery complexity
Consistency: Follows the same pattern as regular filtering joins

The Result

Running our query will return only ID1, ID3, and ID6 - effectively filtering out ID2, ID4, and ID5 as specified in our blacklist.

Real-World Applications

This pattern is incredibly useful for:

User Management: Excluding banned or inactive users
Product Catalogs: Filtering out discontinued items
Data Cleanup: Removing test or invalid records
Compliance: Excluding data based on regulatory requirements

Conclusion

The LEFT JOIN filtering technique is a powerful tool in your SQL arsenal. It's clean, performant, and handles edge cases better than alternatives like NOT IN. Next time you need to exclude rows based on another table, give this approach a try!

Of course, by replacing WHERE black_list.ignore_id IS NULL with WHERE black_list.ignore_id IS NOT NULL we get the complement! That is, whitelisting.

This example uses DuckDB, but the same pattern works across all major SQL databases including PostgreSQL, MySQL, SQL Server, and others.

I was there first! Finding the First in Every Group using SQL 🔥

Dror Atariah — Wed, 09 Jul 2025 16:30:00 +0000

This post shows how to use the FIRST_VALUE() window function in SQL to find the first occurrence in a partitioned group. We'll use a practical example with an employees table.

Setup

First, let's create and populate our employees table.

CREATE TABLE employees (emp_name VARCHAR, department VARCHAR, hire_date DATE);

INSERT INTO employees (emp_name, department, hire_date)
VALUES ('Alice', 'Sales', '2022-03-15'),
       ('Bob', 'Sales', '2023-01-20'),
       ('Charlie', 'Sales', '2022-08-01'),
       ('David', 'Engineering', '2021-07-10'),
       ('Eve', 'Engineering', '2021-05-22'),
       ('Frank', 'HR', '2024-01-05');

Finding the First Hired Employee

Now, we use FIRST_VALUE() to find the first employee hired in each department. The PARTITION BY department clause groups our data, and ORDER BY hire_date sorts it to find the "first" one.

SELECT emp_name,
       department,
       hire_date,
       FIRST_VALUE(emp_name) OVER (PARTITION BY department
                                   ORDER BY hire_date ASC) AS first_hired_in_dept
FROM employees;

Output

emp_name	department	hire_date	first_hired_in_dept
Alice	Sales	2022-03-15	Alice
Charlie	Sales	2022-08-01	Alice
Bob	Sales	2023-01-20	Alice
Eve	Engineering	2021-05-22	Eve
David	Engineering	2021-07-10	Eve
Frank	HR	2024-01-05	Frank

The first_hired_in_dept column is added to every row, showing who was hired first in that row's department.

Extracting a Unique List Per Department

To get a clean list of just the first-hired employee for each department, you can use a Common Table Expression (CTE) with DISTINCT.

WITH DepartmentFirstHires AS
  (SELECT department,
          FIRST_VALUE(emp_name) OVER (PARTITION BY department
                                      ORDER BY hire_date ASC) AS first_hired_in_dept
   FROM employees)
SELECT DISTINCT department,
                first_hired_in_dept
FROM DepartmentFirstHires;

Output

department	first_hired_in_dept
Sales	Alice
Engineering	Eve
HR	Frank

Alternatives to `FIRST_VALUE()`

Here are a couple of other common ways to get the same result.

Using `ROW_NUMBER()`

The ROW_NUMBER() window function can rank employees in each department by their hire date. You then select the one ranked first (rn = 1).

WITH RankedEmployees AS
  (SELECT emp_name,
          department,
          hire_date,
          ROW_NUMBER() OVER (PARTITION BY department
                             ORDER BY hire_date ASC) AS rn
   FROM employees)
SELECT emp_name,
       department,
       hire_date
FROM RankedEmployees
WHERE rn = 1;

Using a `JOIN` with a Subquery

Another method is to find the minimum hire date for each department in a subquery and then join the table back to itself to find the matching employee.

SELECT e.department,
       e.emp_name
FROM employees e
JOIN
  (SELECT department,
          MIN(hire_date) AS min_hire_date
   FROM employees
   GROUP BY department) AS first_hires_per_dept ON e.department = first_hires_per_dept.department
AND e.hire_date = first_hires_per_dept.min_hire_date;

Both of these are effective alternatives, though window functions like FIRST_VALUE() and ROW_NUMBER() are often more readable and performant for this type of problem.

Key Takeaways

FIRST_VALUE is a window function that requires an OVER() clause.
Use PARTITION BY to define your groups and ORDER BY to define what "first" means.
Window functions enrich every row; they don't aggregate them. Use DISTINCT or a subquery if you need a summary.
Be mindful of ties in your ORDER BY clause. Add more columns to the ORDER BY to ensure deterministic results if needed.

Which approach do you prefer? Why?
Let's discuss in the comments!

Bonus

For your convenice, here is a Python script that tests these queries using duckdb:

import duckdb
import pandas

# Connect to an in-memory DuckDB database
con = duckdb.connect(database=":memory:", read_only=False)

# 1. Setup: Create and populate the employees table
setup_sql = """
CREATE TABLE employees (emp_name VARCHAR, department VARCHAR, hire_date DATE);
INSERT INTO employees (emp_name, department, hire_date)
VALUES ('Alice', 'Sales', '2022-03-15'),
       ('Bob', 'Sales', '2023-01-20'),
       ('Charlie', 'Sales', '2022-08-01'),
       ('David', 'Engineering', '2021-07-10'),
       ('Eve', 'Engineering', '2021-05-22'),
       ('Frank', 'HR', '2024-01-05');
"""
con.execute(setup_sql)
print("--- Table created and populated. ---")
print("")

# 2. Test FIRST_VALUE() query
first_value_sql = """
SELECT emp_name,
       department,
       hire_date,
       FIRST_VALUE(emp_name) OVER (PARTITION BY department
                                   ORDER BY hire_date ASC) AS first_hired_in_dept
FROM employees;
"""
print("--- Testing FIRST_VALUE() ---")
result = con.execute(first_value_sql).fetchdf()
print(result)
print("")

# 3. Test FIRST_VALUE() with DISTINCT to get a unique list
first_value_distinct_sql = """
WITH DepartmentFirstHires AS
  (SELECT department,
          FIRST_VALUE(emp_name) OVER (PARTITION BY department
                                      ORDER BY hire_date ASC) AS first_hired_in_dept
   FROM employees)
SELECT DISTINCT department,
                first_hired_in_dept
FROM DepartmentFirstHires;
"""
print("--- Testing FIRST_VALUE() with DISTINCT ---")
result = con.execute(first_value_distinct_sql).fetchdf()
print(result)
print("")


# 4. Test ROW_NUMBER() alternative
row_number_sql = """
WITH RankedEmployees AS
  (SELECT emp_name,
          department,
          hire_date,
          ROW_NUMBER() OVER (PARTITION BY department
                             ORDER BY hire_date ASC) AS rn
   FROM employees)
SELECT emp_name,
       department,
       hire_date
FROM RankedEmployees
WHERE rn = 1;
"""
print("--- Testing ROW_NUMBER() alternative ---")
result = con.execute(row_number_sql).fetchdf()
print(result)
print("")


# 5. Test JOIN with subquery alternative
join_subquery_sql = """
SELECT e.department,
       e.emp_name
FROM employees e
JOIN
  (SELECT department,
          MIN(hire_date) AS min_hire_date
   FROM employees
   GROUP BY department) AS first_hires_per_dept ON e.department = first_hires_per_dept.department
AND e.hire_date = first_hires_per_dept.min_hire_date;
"""
print("--- Testing JOIN with subquery alternative ---")
result = con.execute(join_subquery_sql).fetchdf()
print(result)
print("")

# Close the connection
con.close()

Two Approaches to Data Anonymization with Pydantic

Dror Atariah — Tue, 08 Jul 2025 19:49:00 +0000

When working with Personally Identifiable Information (PII), you often need to anonymize sensitive information before processing or storing it. Let's explore two different approaches to implement this using Pydantic models.

The Problem

You have a User model and need to create an anonymized version for privacy compliance:

from pydantic import BaseModel, EmailStr

class User(BaseModel):
    id: int
    name: str
    email: str

Solution 1: Factory Method Approach

Create a classmethod that handles anonymization during object creation:

class AnonymizedPerson(BaseModel):
    id: int
    name: str
    email: EmailStr

    @classmethod
    def from_user(cls, user: User) -> "AnonymizedPerson":
        # Anonymize email by replacing username with "anonymized"
        # You might want to use some hashing, so analytics on
        # the data could still be supported.
        email_parts = user.email.split("@")
        anonymized_email = f"anonymized@{email_parts[1]}"

        return cls(
            id=user.id,
            name=f"anonymized_{user.name}",
            email=anonymized_email
        )

How to use:

user = User(id=1, name="Alice Brown", email="alice@company.com")
anonymized = AnonymizedPerson.from_user(user)
assert anonymized.name == "anonymized_Alice Brown"
assert anonymized.email == "anonymized@company.com"

Solution 2: Field Validator Approach

Use Pydantic's @field_validator decorator with mode='after' to transform data automatically:

from pydantic import BaseModel, EmailStr, field_validator

class AnonymizedPersonAuto(BaseModel):
    id: int
    name: str
    email: EmailStr

    @field_validator("name", mode="after")
    @classmethod
    def anonymize_name(cls, v: str) -> str:
        """Automatically anonymize the name field after validation"""
        if not v.startswith("anonymized_"):
            return f"anonymized_{v}"
        return v

    @field_validator("email", mode="after")
    @classmethod
    def anonymize_email(cls, v: str) -> str:
        """Automatically anonymize the email field after validation"""
        if not v.startswith("anonymized@"):
            email_parts = v.split("@")
            return f"anonymized@{email_parts[1]}"
        return v

    @classmethod
    def from_user(cls, user: User) -> "AnonymizedPersonAuto":
        """Create from User - name and email will be auto-anonymized"""
        return cls(id=user.id, name=user.name, email=user.email)

How to use:

user = User(id=1, name="Bob Wilson", email="bob@tech.org")

# All these methods automatically anonymize both fields:
from_factory = AnonymizedPersonAuto.from_user(user)
direct = AnonymizedPersonAuto(id=2, name="Carol", email="carol@startup.io")
from_json = AnonymizedPersonAuto.model_validate({"id": 3, "name": "Dave", "email": "dave@corp.net"})

# All result in anonymized data:
assert from_factory.name == "anonymized_Bob Wilson"
assert direct.email == "anonymized@startup.io"

Summary: When to Use Each Approach

Factory Method Approach

Pros:

Simple and explicit
Clear control over transformation logic
Easy to understand and debug
Suitable for complex multi-field transformations

Cons:

Only works when using the factory method
Other creation paths bypass anonymization
Manual process that can be forgotten

Best for: Simple use cases where you control all object creation paths

Field Validator Approach

Pros:

Guaranteed consistency across all creation methods
Self-contained transformation logic
Works with direct instantiation, JSON parsing, and factory methods
Prevents accidental non-anonymized instances

Cons:

Slightly more complex setup
Field-level transformations only
Less explicit about when transformation occurs

Best for: Production systems where data consistency and reliability are critical

Bonus: Making Factory Method Approach Bulletproof

If you want to use the factory method approach but prevent direct instantiation bypass, you can implement a private constructor pattern:

class AnonymizedPersonSecure(BaseModel):
    id: int
    name: str
    email: EmailStr

    def __init__(self, **data):
        # Private constructor - should only be called by factory methods
        if not hasattr(self, '_from_factory'):
            raise ValueError("Use AnonymizedPersonSecure.from_user() instead of direct instantiation")
        super().__init__(**data)

    @classmethod
    def from_user(cls, user: User) -> "AnonymizedPersonSecure":
        # Anonymize email by replacing username with "anonymized"
        email_parts = user.email.split("@")
        anonymized_email = f"anonymized@{email_parts[1]}"

        # Create instance through private constructor
        instance = cls.__new__(cls)
        instance._from_factory = True
        instance.__init__(
            id=user.id,
            name=f"anonymized_{user.name}",
            email=anonymized_email
        )
        return instance

How it works:

user = User(id=1, name="Alice Brown", email="alice@company.com")

# This works - using factory method
anonymized = AnonymizedPersonSecure.from_user(user)
assert anonymized.name == "anonymized_Alice Brown"

# This fails - direct instantiation blocked
try:
    direct = AnonymizedPersonSecure(id=2, name="Bob", email="bob@test.com")
except ValueError as e:
    assert str(e) == "Use AnonymizedPersonSecure.from_user() instead of direct instantiation")

This gives you the control of the factory method approach with the safety of preventing bypass routes.

Key Takeaway

Choose the factory method for simple, controlled scenarios. Choose field validators when you need bulletproof data transformation that works everywhere your model might be instantiated. Both patterns have their place in modern Python applications.

Have you used Pydantic field validators for data transformation? Share your use cases in the comments!

Forem: Dror Atariah

Encoding an Excel File in a Power BI Report

Quickly Clean Up Databricks New Queries

Spot the Sale - My take

Solutions

First Take

Take Two using cut

Take Three using duckdb

The Solution by Maven

Discussion

Notes from PyData Berlin 2025

Beyond Linear Funnels: Visualizing Conditional User Journeys with Python

Accessible Data Visualizations

More than DataFrames: Data Pipelines with the Swiss Army Knife DuckDB (Workshop)

AI-Ready Data in Action: Powering Smarter Agents (Workshop)

Narwhals: enabling universal dataframe support (Keynote)

The Importance and Elegance of Polars Expressions

Building Reactive Data Apps with Shinylive and WebAssembly

Deep Dive into the Synthetic Data SDK (Workshop)

Forget the Cloud: Building Lean Batch Pipelines from TCP Streams with Python and DuckDB

Bye-Bye Query Spaghetti: Write Queries You'll Actually Understand Using Pipelined SQL Syntax

Docling: Get your documents ready for gen AI

Scraping urban mobility: analysis of Berlin carsharing

JSON Schema to PySpark StructType

The Smart Way Pandas Handles Overlapping Column Names

The Undeterministic Nature of Agents

Left Join Gotcha: Rows Duplication

Data quality

SQL CASE Statements: The Order Matters!

The Setup

Option 1: Order-Dependent Approach

⚠️ The Pitfall

Option 2: Explicit and Order-Independent

Why This is Better

Best Practices

Conclusion

Filtering Out Rows Using LEFT JOIN: A Clean Alternative to NOT IN

The Problem

The Solution: LEFT JOIN with NULL Check

Step 1: Create Sample Data

Step 2: Create the Blacklist

Step 3: Filter Using LEFT JOIN

How It Works

Why This Approach?

Advantages over NOT IN:

Advantages over NOT EXISTS:

The Result

Real-World Applications

Conclusion

I was there first! Finding the First in Every Group using SQL 🔥

Setup

Finding the First Hired Employee

Output

Extracting a Unique List Per Department

Output

Alternatives to FIRST_VALUE()

Using ROW_NUMBER()

Using a JOIN with a Subquery

Key Takeaways

Bonus

Two Approaches to Data Anonymization with Pydantic

The Problem

Solution 1: Factory Method Approach

Solution 2: Field Validator Approach

Summary: When to Use Each Approach

Factory Method Approach

Field Validator Approach

Bonus: Making Factory Method Approach Bulletproof

Key Takeaway

Take Two using `cut`

Take Three using `duckdb`

Alternatives to `FIRST_VALUE()`

Using `ROW_NUMBER()`

Using a `JOIN` with a Subquery