KHAYAL

A few sentences on Vasantrao Deshpande’s masterful rendition of Likh Bheji Main Patiyaan

Remembering a piece I wrote for Véronique Lerebours’ blog back in 2011, I now think about how little music I had heard, though at the time it felt like I was uncovering, discovering, and, at times, unearthing new music from the depths of the internet (I still do that, but YouTube has made it easier now). The piece was called The Romantic Raga, spelled with two a’s, as in Raaga. You’ll excuse this because that was the phase when I was writing cuz for because without any shame. Anyway.

In the piece, I write about how a few bandishes or, in fact, sets of bandishes, had captivated me because of their lyrical beauty and the renditions’ musical technicalities. I briefly mention renditions by Ajoy Chakraborty, Iqbal Ahmed Khan, Rajan & Sajan Mishra, Jasraj, and Roshan Ara Begum, but completely miss mentioning Prabha Atre and Vasantrao Deshpande, both of whom took Maru Bihag to the limits of beauty.

After many weeks, I got back to listening to Khayal today, and stumbled upon a recording of Vasantrao Deshpande’s on YouTube, where he is singing two bandishes, the first one is

उन ही से जाय कहूँ
मोरे मन की बिथा

दरस बिन कछु ना सुहावे
अँखियन नीकी प्रीत गुनवंता

And the second bandish is why I have named this piece as I have. It talks about the practice or feeling of writing letters to one’s beloved. Once upon a time, romance did take the form of letters. Pining took the form of poetic prose and, often, of poetry itself. Here, in this bandish, it’s not just poetry; it is also set to a set of notes that capture the feeling of this whole scene. It’s sort of like some languages, like German, have words that explain a whole human experience. This rendition of Maru Bihag captures the romance in a similar way.

लिख भेजी मैं पतियाँ
तुम्हरे कारन जुग सी बीतत मोरी रतियाँ

जा रे जा कगवा इतना मोरा संदेसवा लिए जा
जरत मन जोवत बाट तुम्हरी ये अँखियाँ

Who knows, maybe the heroine of this love story is singing the bandish immortalised by Prabha Atre.

जागूँ मैं सारी रैना बलमा
रसिया मन लागे ना मोरा

We are fortunate to have several recordings of Vasantrao Deshpande singing the same two bandishes at different phases of his career. No surprise that they all sound beautiful.

• Recording №1 (~51 minutes, the one that prompted me to write this blog, c. not mentioned in the recording)
• Recording №2 (~7 minutes, drut bandish only, c. 1960s)
• Recording №3 (~46 minutes, from Diwakar Tole’s archives, c. it feels like slightly late in his career, but not mentioned in the recording)
• Recording №4 (~28 minutes, c. not mentioned in the recording)
• Recording №5 (~40 minutes, probably the most famous of them all because it also has video, c. released in 1982)
• Recording №6 (~38 minutes, at a concert in Pune, c. 1974)

Listen to some of these recordings, and if you’re as captivated as I was, I’m sure you’ll be yearning for more, so I’ll leave you with some more interesting recordings/playlists of Vasantrao ji

• Farhan Amin’s beautifully curated playlist of Vasantrao ji’s recordings
• Sureshchandra Nadkarni interviewing Vasantrao Deshpande for Akashvani — look for his explanation about Bilawal Thaat ki Gunakali
• Vasantrao ji’s recordings from Diwakar Tole’s personal archives
• Two compositions in Paraj and a dadra with Zakir Hussain and Sultan Khan, c. 1983
• Vasantrao ji singing something in Marathi in Bhairavi

Happy listening!

An Epistolary Bandish in Maru Bihag was originally published in Khayaliya on Medium, where people are continuing the conversation by highlighting and responding to this story.

SNOWFLAKE

Our recommendations on when to use Openflow, Fivetran, dltHub, and others to ingest data into Snowflake

Background

Getting data into Snowflake has gotten easier over time. There is more flexibility in terms of supporting a variety of data sources, different ingestion frequencies, and also cost and performance optimization options. In addition to offering several native data ingestion methods, Snowflake also partners with and integrates with various third-party data ingestion tools, most notably Fivetran. However, there are others available on the market.

The most recent addition to this ecosystem is Snowflake Openflow, a managed version of an open-source project that automates data flow between systems. This project is called Apache NiFi, which can be run either in your own cloud account using the BYOC (Bring Your Own Compute) option or using Snowflake’s Snowpark Container Services (SPCS).

Then, there’s dlt (not to be confused with Databricks’ Delta Live Tables). dlt (from dltHub) is an open-source Python-first data ingestion tool that has many popular data source connectors. If you don’t find the connector you need, building one using Python is quite easy. Before we dive deeper into when to use which ingestion tool, here’s a quick overview of these options/categories:

• Snowflake’s native options: Snowpipe, Snowpipe Streaming, Snowflake Connectors, and now Snowflake Openflow.
• Cloud-platform-aligned ingestion tools: AWS Glue/Kinesis, Google Cloud Dataflow, and Azure Data Factory.
• Proprietary third-party ingestion tools: Fivetran, Airbyte (managed), dltHub, and Stitch, among others.
• Open-source ingestion tools: dlt (data load tool), Apache NiFi, Singer, etc.

Let’s look at when to use which of these options!

Snowflake’s Native Ingestion Tools

There are four native options to ingest data in Snowflake. We’ll look at all four. The first two assume that you already have infrastructure and tooling for landing/ingesting data into a cloud object store:

• Snowpipe — when your data from various sources is already in cloud object storage, and you need the data from the cloud storage ingested into Snowflake in batches every few minutes or less frequently.
• Snowpipe Streaming — as the name suggests, this is the same as Snowpipe but for streaming data. Use this when you need to ingest data at a rate of every few seconds.

The next two are ingestion options that help you get data directly from the data sources into Snowflake:

• Snowflake Native Application Connectors — when you want to use pre-built connectors for services like Google Analytics, SharePoint, MySQL, PostgreSQL, etc., use this option. Please note that only a limited number of connectors are available, and you cannot create your own.
• Snowflake Openflow — when you want an open-source-based connector ecosystem and you need the option to bring your own cloud compute for ingestion while staying native to Snowflake, Openflow is a good option. This project is currently under active development, with updates arriving almost every week.

Bottomline

Using Snowflake-native ingestion tool like Openflow makes sense you need an ingestion framework with a range of connectors and the possibility of creating new connectors while also giving you the freedom to run the ingestion on your own cloud compute.

The other ingestion options are good for specific use cases (ingesting from cloud storage or from a couple of services applications). Openflow is the only full-fledged direct data ingestion option that Snowflake has for a broad range of connectors.

Openflow is based on an open-source project called Apache NiFi. One key thing to remember is that Apache NiFi can have a very steep learning curve as it can impact your team’s ability to use existing connectors, or build and maintain new connectors when required.

Cloud-platform-aligned ingestion tools

If the ingestion tools aren’t Snowflake-native, they can be native to the cloud platform where Snowflake is running. The benefit of using a data ingestion service native to the cloud is that it can be easily integrated with other orchestration, observability, and testing services within that cloud. Here are the services you can use with the cloud platforms:

• AWS Glue — has 30+ built-in connectors, most of which are databases, and some of which are services like Mixpanel and Salesforce.
• Google Cloud Dataflow — is based on Apache Beam, which has 50+ I/O connectors for file systems, databases, message queues, data warehouses, etc.
• Azure Data Factory — has over 100 connectors, including those for data sources such as SAP, Salesforce, Zendesk, and Dynamics 365, among others.

Bottomline

Using these cloud-platform-aligned ingestion tools makes sense if the sources you have can be supported by these services. Most data sources that are not natively supported by these cloud-platform-aligned ingestion tools can be found in the cloud platform marketplaces.

Alternatively, you can build and maintain your own connectors, especially when dealing with very niche data sources for which you won’t find a well-maintained connector with any of the cloud ingestion tools. Having said that, it is important to keep in mind that building and maintaining connectors is a long-term investment.

Proprietary third-party ingestion tools

Following the recent merger of dbt and Fivetran, George Fraser, the CTO of Fivetran, discussed why it makes sense for dbt Core to be open-source, but not for Fivetran. He, in essence, said that nobody wants to own the painful process of building and maintaining connectors to services and applications that are ever-changing, which is why it is not a superb fit for open-source.

Fivetran has over 700 pre-built connectors, while Stitch has more than 300. These tools build and maintain connectors, so you don’t have to, providing a low-code experience for data ingestion. This comes in very handy when you don’t want to dedicate personnel solely to the task of ingestion and dealing with the day-to-day issues that come with it.

Assuming that one of the proprietary third-party tools has the connectors you want, you need to check for the following things:

• Does the tool provide an option to use your own compute for ingesting data, i.e., does it need to be temporarily or permanently stored in the tool’s cloud environment?
• Cost models can be confusing, sometimes intentionally so. Therefore, you should gain a thorough understanding of what you’ll be billed based on past volumes.

Bottomline

There’s a clear benefit of using third-party proprietary tools, especially if you want to build and keep a lean data engineering team. With these tools solving the data ingestion, data analysts and engineers can focus on what matters the most — business use cases around reporting, analysis, etc.

While there are many proprietary data ingestion tools in the market, some even with full ETL capabilties, there are only a few that have a wide variety of connectors, so when considering a tool, make sure to look at the maturity of the connectors you need for your data ingestion workflows.

Free and open-source ingestion tools

This is the option that gives you the most freedom in terms of controlling how and where your data is processed, as well as how much it costs to run. Having said that, despite my bias towards and liking for open-source ingestion tools, I recommend them to data teams with caution because the work required to develop using an open-source (or more likely, an open-core) project is often underestimated.

There are many open-source data ingestion tools, but three stand out for me:

• dlt (data load tool) — is a dev tool for data ingestion; it is not the usual connector catalog in the market. It is known for its Python-first data ingestion philosophy and is a great option for data teams that are well-versed in Python and, in addition to using dlt’s official and community-maintained connectors, are happy to build their own, too. Doing that is way easier in dlt in comparison with some of the other options.
• Apache NiFi — a mature and widely used JVM-based ingestion engine, which Snowflake has recently adopted as the underlying engine for its new managed service called Openflow. NiFi does come with a steeper learning curve than the other options in this category, especially if you aren’t familiar with the JVM ecosystem.
• Singer — is another very good option for a full-fledged ETL tool. It was open-sourced by Stitch eight years ago. Singer was also the inspiration for the creation of Meltano, which can be considered a more advanced open-source version of the same project.

Bottomline

If you want control and freedom, this is the best way to go but it comes with a cost. A better way to go about data ingestion is to use an open-source framework as the foundation but then use a managed service to run it for you. Openflow makes a good choice for managed open-source solution for Snowflake but is not fully Apache NiFi open-source needs a bit of getting used to.

If your team can write Python and you want your data team to use a developer-first ingestion framework, dlt might be a very good option. And it is fully open-source. Check out this blog post that addresses the dlt vs. Openflow question.

One thing to keep in mind is that it is more important to build your data infrastructure on open standards rather than open-source projects. This means that the choice of data ingestion tool is less important than the choice of file formats, table formats, control planes, among other things.

In other words, prefer open standards for your data infrastructure in general, and if you have the capacity and capability to run an open-source project for data ingestion, do that, otherwise use a managed version of an open-source project.

Closing Thoughts

Traditionally, ingestion, transformation, modelling, and remodelling of data have be clubbed together under the ETL umbrella. That approach which is now shifting towards separating the concerns with tooling. An ingestion tool is meant to do only that, examples of which you read about in this article. A transformation tool like dbt only does transformation and doesn’t handle ingestion. Having these variations gives you more freedom to choose the kind of architecture you want to implement for your business.

For now, I’ll leave you with a few important questions you need to ask before deciding on a data ingestion tool for Snowflake:

• What cloud platform(s) do you run Snowflake on?
• Aside from Snowflake, is all of your other data infrastructure on a particular cloud platform? In other words, do you utilize your cloud platform’s data services extensively?
• Can a combination of Snowflake’s native options for data ingestion cover all the data sources you have?
• What is the shape, size, and skillset of your data team? What are their priorities?
• Is there a bias towards low-code/no-code tooling?
• Does your team want more control and freedom in terms of choosing the compute and the process of ingestion into Snowflake?
• What control do you have over the security and privacy of your data while it is being ingested?

Answering these questions will translate into the decision points that I walked you through in the four sections that preceded this one. The situation with the data stack is that there are hundreds of options, which can be exhausting even to consider, let alone assess for use. That’s why narrowing down the good candidates is the first step. Examining the ingestion tools through these categories will hopefully help you do that more effectively.

Thanks to Paul Harmat, Kroum Klutchkov, and Adrian Brudaru for reading the draft of this blog post.

Data Ingestion Tools for Snowflake was originally published in Nexifi on Medium, where people are continuing the conversation by highlighting and responding to this story.

DATA ENGINEERING

Two days in the life of Data and AI engineers

The Versent Data & Insights attended the main data engineering event of the year in Melbourne in late July. The conference caught my interest last year when they invited Chip Huyen, author of the book AI Engineering, for a talk. When I found out our team was going to the event, I checked the schedule of the two parallel tracks and immediately decided which talks were not to be missed and which ones I could probably skip.

During the two days, I ended up attending thirteen sessions and might have listened to a couple of others in bits and pieces. In my reading, Track 1 was mostly L100-type talks, although there were some exceptions. Track 2 was where L200 and, in some cases, L300 sessions were taking place. This blog post is a brief account of the two-day event, highlighting the aspects I enjoyed most. Here we go, session by session!

Building Trino Data Pipelines with SQL or Python

I started with Lester Martin’s session, Building Trino Data Pipelines with SQL or Python, which was quite good. Lester took us through the history of Presto, how it compared to Spark, how it wasn’t a compute engine but a query engine, among other things, before he drew our attention to the complexities of a typical distributed ETL workflow, highlighting shuffles, sorts, partitions, stages, and whatnot.

Lester also highlighted how Trino’s fault-tolerant execution model, along with the support for both SQL and Python, more or less, covered the linguistic reach of the data engineering community. Then there are rebels who want to use Rust; it is becoming a thing anyway, but I’ll leave that to another day. One of the things Lester said was really funny and really true, which was that even the data engineers who primarily use Python DataFrames end up using the .sql() syntax more often than not. SQL is such a simple and beautiful language. He said:

We all like SQL or we hate SQL, but we all use SQL.

Lester focused on writing SQL, which may not be necessary with Starburst’s (Trino’s managed data lakehouse solution) Python libraries: PyStarburst and Ibis. These libraries provide PySpark-like syntax, lazy evaluation, and the Python code you write is automatically converted into SQL, which means that most of the heavy lifting is then handled by Trino itself. It is important to note, though, that PyStarburst is only supported by Starburst Galaxy and Starburst Enterprise. I won’t go into much more detail about Ibis, but a good starting point if you’re interested in learning about Trino would be the following two links:

• An Overview of the Starburst Trino Query Optimizer
• Ibis: One Library To Query Any Backend

Streaming Analytics with Apache Polaris

The next session was led by Kamesh Sampath, a Lead Developer Advocate at Snowflake, who discussed using a fully open-source stack to create a streaming analytics dashboard—the stack he used:

• Kafka — for publishing real-time events on topics
• RisingWave — the streaming data lakehouse platform
• Polaris — the catalog for registering and discovering tables
• Iceberg — the table format used for organizing Parquet files
• Streamlit — the analytics application engine

The session focused on the idea of avoiding being locked into any particular technology and utilizing open standards and open-source technologies whenever possible. Polaris is the base for Snowflake’s Open Catalog and Snowflake Horizon Catalog. The open-source version of Polaris can be run on a small, simple server anywhere. It is language-agnostic and vendor-neutral, and focuses on metadata portability.

It was a fun, fully functional and working demo, which, if you’re a data engineer, you probably know is a rarity. Find more about Kamesh’s work here.

Simplifying Data Pipelines with Tableflow

The next session was led by Confluent’s Prerna Tiwari, who took us through a Kafka-native, serverless, managed stream-to-table data pipeline service, emphasizing that managing streams is very difficult with traditional batch-based or micro-batch-based pipelines. Tableflow takes a declarative approach to data pipelines, which is where the data engineering world is going. Databricks also recently announced Lakeflow Declarative Pipelines.

Tableflow, in effect, bypasses all other data lakehouse ingestion patterns and feeds data directly into your data lakehouse, built on Iceberg or Delta Lake tables, essentially getting rid of the process of converting topics into schemas, which, as you would know, can be quite messy with evolving schemas. Tableflow was announced last year, and it is GA as of March 2025. Tableflow also integrates with Databricks, Snowflake, Trino, and other data platforms. The following image compares open table formats, stream-to-table data lakehouse solutions, general-purpose ETL solutions, and Tableflow on a range of features and capabilities.

On Creating a Data Catalog by Accident

This was probably one of the best, certainly the funniest, and most relatable sessions of the two days. May-Lyn Hu, a Data Lead at Pet Circle, presented a session with the following title: Automate Your Metadata, Deliver a Data Catalogue (by accident).

May-Lyn perfectly captured the need for a data catalog by highlighting all the unnecessary conversations, bottlenecks, and frustrations people have gone through just to get an answer to what would seem to the business a fairly simple question!

This led to the team asking the question — is this (cataloging) a solved problem? The answer was yes, but it was cost-prohibitive, or at least it was going to be significantly if not prohibitively expensive, as she cited a $1500 seat price. So, the real answer was to conceptually break down what a catalog is from a first principles approach. The team did that and determined that it was just a collection of metadata that needed to be maintained in a structured manner. The team chose YAML. This didn’t come without challenges, but the team successfully delivered a data engineer-facing catalog.

What about the business? Wasn’t the actual problem that the business doesn’t know where data assets are, what they mean, and whether they can be trusted? Yes, it was. So, the team decided to build a frontend for the business, too. This frontend gave the business a way to search data assets, access their quality and trust scores, look up ownership and stewardship metadata, among other things.

May-lyn stressed that it wasn’t an easy journey to build this. It took about a year to build the backend and the frontend, which essentially means that this is not for everyone, a point I completely agree with. However, I also think that businesses with the engineering skillset and drive to pull this off should consider doing so.

Problems That You Only Face with Scale

The next session focused on a cost-optimization project at Block following their acquisition of Afterpay. It’s a story of how the data team reduced the data egress cost for their cross-region data processing set up by over half a million USD per year. The following is from the Cash App blog. It should give you an idea of the scale:

Kafka pipelines managed by the Afterpay data team process over 9 TB of data daily and deliver data to critical business domains such as Risk Decisioning, Business Intelligence and Financial Reporting via ~200 datasets. In the legacy design, duplicate events from Kafka’s “at least once” delivery required downstream cleansing by Data Lake consumers and late-arriving records added substantial re-processing overheads.

The talk brought back some memories of dealing with scale (although nowhere close to this), reading about the journey of other teams on blogs like High Scalability. To know more about the challenge and solution at Afterpay, head over to the blog post for Project Teleport: Cost-Effective and Scalable Kafka Data Processing at Block.

How to Build an Agent

Geoffrey Huntley, who’s an AI engineer at Sourcegraph, came in with a single goal — to convince everyone that they could build their code-editing agents and it wasn’t that difficult. Because I had built something of that sort, although without using MCP, I already knew that point he was trying to make. Nevertheless, it was good to see how simple an agent can be — essentially, a self-referencing agent completion request in a while loop.

Sourcegraph has released its agentic coding tool called Amp, which is a Cursor and Windsurf alternative. There are some key distinctions between, say, Cursor and Amp:

1. With Cursor, you can choose models; with Amp, you cannot. Amp believes it chooses the best models.
2. Amp doesn’t have any limit on token usage. Your token usage decides how much you pay.
3. Amp is also collaborative — it lets you share threads with other team members and collaborate with them.

It’s an interesting approach to code editors. If you want to know more about Amp, I can point you to the following two resources:

• Owner’s Manual — Amp by Sourcegraph
• Agents for the Agent

The entire slide deck that Geoffrey used during his presentation is also available on his blog.

PromptSQL — Using LLMs for Query Optimization

And then, towards the end of Day 2, on centre stage, Versent’s own Jake Kerr shared his thoughts and experiences in using LLMs for query optimization. Jake broke down the problem of query optimization and told the audience how it’s not going to go away. Rather, he walked everyone through a step-by-step plan of optimizing queries using structured prompts created for investigating query optimization issues, highlighting those issues right within your SQL IDE, and then fixing those issues one by one.

The focus of this session was to highlight that raw prompting alone cannot get you very far with query optimization. But if you break down the problem into manageable chunks and use something like LangGraph to manage the state of the activities, you might be able to do that.

Query optimization is just another code analysis problem that can and should be broken down into multiple steps, each of which can be assigned to several agents, each with its own separate tools. Uber’s QueryGPT is a good example of this. It uses several types of agents: intent agent, table agent, column pruning agent, among other intermediate agents.

The Database Group at the University of Pennsylvania also found that LLMs are unreasonably effective with query optimization, although this wasn’t necessarily an agent-based assessment. Find more about the LLMSteer project presentation here and the associated code here on GitHub.

Jake concluded his remarks by sharing some time-tested query optimization principles and rules of thumb, which I’ll repeat here for you: join reordering based on real cardinality, predicate pushdown, set-based replacements, redundancy elimination, and data type mismatches.

All in all, these were two days well spent with the team, meeting former colleagues, ISV partners, and dabbling in the interesting tools and technologies, and hearing stories of daily struggles, architectural best practices, and challenges of scale, among other things. I think it’s an event worth attending, regardless of your level in data engineering, ML, or AI.

Epilogue

Jake’s session marked the end of the two-day data engineering, ML, and AI fun getaway for the team. I stayed back for the after-party at Melbourne Park for a while. And then left for an even cooler Friday night event at the University of Melbourne, where Dr. Danielle Holmes, who is the Women in Physics Lecturer at the Australian Institute of Physics, was presenting the Marie Curie lecture as part of the July Lectures in Physics—the topic of her presentation, Quantum Century: Unlocking the Universe’s Secrets and Shaping our Future.

Dr. Holmes talked about 100 years of quantum theory and walked us through the history of how the theory came to be. She spoke about its practical importance in explaining phenomena like why birds don’t get lost in long migrations, how stars shine, among other things. She then ventured into the future of quantum computing and how it will be critical to solving many of the world’s seemingly unsolvable problems, such as climate change, nuclear energy, molecular simulation, advanced cryptography, and machine learning. Those in Sydney can still catch the same lecture as part of the National Science Week. Details here.

Versent at DataEngBytes 2025 was originally published in Versent Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

GENERATIVE AI

Speculations about the future of my profession and why writing will be a key skill in it

The initial public release of ChatGPT is coming up on two years in November — yes, it’s only been two years, and the software world has transformed quite a lot since then. Very competitive LLMs (Large Language Models) have been open-sourced, generative AI code editors and companions have propped up, and the most important thing, in my opinion, is that the Turing test has been broken.

With these improvements in generative AI, a software engineer's job has already changed quite a bit. While visiting StackOverflow and other community forums used to be of real value a few years ago, you can now ask a well-trained coding assistant to help you with your query. Meanwhile, various well-funded startups are working on AIs that will find bugs in your codebase, search for a fix, and deploy the fix, too.

This means the change isn’t coming soon; it’s here. However, it will probably take years for most organizations to leverage generative AI for code in a real sense. There are a few reasons for that.

Despite generative AI's exponential improvements, organizations will need to navigate limitations around cost, infrastructure availability, code safety, governance, and sovereignty, among other things. These limitations will hamper organizations that are not engineering-driven. This is why, while generative AI should, by its design and philosophy, democratize coding, at least for the first few years, it might not have that effect. In fact, it might actually end up having a sort of Reverse Robinhood effect, where everyone in the software engineering world is passively working towards bettering the models for people and organizations that are already good at software engineering.

There’s also a general hesitation to adopt these new generative AI-based tools as so much of the landscape is constantly changing. While the constant change isn’t a complete shock to software engineers, the rate of change with generative AI models hasn’t been seen before with any other software technology. The fact that organizations are slowing down spending on AI learning and development doesn’t help either.

While there is a broad acceptance of AI's benefits, many software engineers revolt at the idea of AI replacing their jobs. This makes me think that we, as a society, have seen this movie before. During the Industrial Revolution, the workforce violently rallied against the automation of the mills for a prolonged period, eventually landing in jails. Hopefully, it won’t come to that. At the same time, it is important to raise social, legal, and ethical concerns around AI to promote a constructive dialogue that informs its governance framework.

Organizations will overcome these issues before the wider society does. While these things get resolved, organizations will see pockets of generative AI adoption, if not across the board. There are obvious things where it will have a tremendous boost in productivity — a tool for automation more than anything else.

Tools like Ansible did that with configuration management, Terraform with cloud and on-premises infrastructure management, and Kubernetes with deploying scalable services in a platform-agnostic world. These are all, at an elementary level, tools for automation. Generative AI is a new category of automation tools that will work with all existing software tools, including the ones mentioned above. It will help you write software configuration, infrastructure orchestration, data pipelines, and frontends, among other things.

Writing software has always been about instructions to the electrical magic underneath the silicon. These instructions were first written in FORTRAN, BASIC, and C++. More recently, they have been written in languages like Python, Go, and Rust. Writing code has been getting easier all these years with the simple goal of abstracting machine complexity. Well, now, with English (or quite likely your native language) as the ultimate layer of abstraction is going to be your first layer of instruction. Before generative AI, it never was. But it was definitely given serious thought over seven decades ago when Turing wrote his seminal paper — Computing Machinery and Intelligence.

The New Programming Skill

Now that it’s becoming increasingly clear that natural language will be the medium of instruction for writing software in the near future, the question is: What will be the key skill for a software engineer of the future?

My speculation is that though understanding the internals of hardware and all the abstractions that bring us to the natural language interface will remain important, the most important skill will be writing, that is, writing in your natural language.

To the reader who’s thinking — well, isn’t that obvious ?— I’d say it’s true that writing efficient, performant software, finding the right constructs, applying the right order of instructions, and choosing the correct level of abstraction make a good software engineer. In a similar vein, in the future, writing good natural language instructions (or prompts) and structuring them so that the underlying model responds the way you intend it to respond will make a good natural-language software engineer. Still, it seems unclear how much of the job will be just plain old writing. Probably a lot. But writing alone won’t be enough.

A software engineer's job will become an amalgamation of product and engineering, among other things. It will entail thinking about software, product, user experience, and more and putting all those ideas into words, the importance of which Paul Graham highlights in one of his essays:

Putting ideas into words doesn’t have to mean writing, of course. You can also do it the old way, by talking. But in my experience, writing is the stricter test. You have to commit to a single, optimal sequence of words. Less can go unsaid when you don’t have tone of voice to carry meaning.

This applies to software, too—the search for a single, optimal sequence of words. If you think about it, it is similar to how software is done now, but not in natural language. A software engineer's job is to find the right constructs, the right order of execution, and the right balance between CPU, database, network, and cache, among other things.

Similarly, software engineering in the future will become the art of finding the right word or sequence of words to communicate an idea to the machine or an AI agent, the essence of the Flaubertian idea of le mot juste. Who would have thought that Gustave Flaubert would also contribute, in his own little way, to the field of software engineering? Probably Alan Turing!

Thanks to Soumya, Corin Lawson, Matt Dudley, Hannah Ryan, Emerald Leung, Nishant Virmani, and Jatin Malik for reviewing the draft of this post.

Software Engineering of the Future was originally published in Versent Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

DATA ENGINEERING

A brief guide to methods for creating a global grid system for geospatial indexing and partitioning

All deterministic data processing is fundamentally based on searching and sorting. To process data efficiently, one needs to search and sort it by discarding all the data they don’t need — that’s what indexes and partitions are for. The details of how indexes and partitions are implemented are usually abstracted away from end users, but different types of indexes and partitions work better with different underlying data types.

Relational databases and data warehouse platforms support indexing types like B-tree, B+ tree, GiST, SP-GiST, and Hash. Some work better for point lookup queries on numbers and short text fields, while others work better for full-text searches, and so on. When it comes to geospatial data, one of the popular index types is the R-tree, about which the PostgreSQL documentation says the following:

R-Trees break up data into rectangles, and sub-rectangles, and sub-sub rectangles, etc. It is a self-tuning index structure that automatically handles variable data density, differing amounts of object overlap, and object size.

This has many variations in kd-tree, Quadtree, Octree, and other indexing methods that help you index geospatial data. These indexing methods are included in the database's low-level implementation, meaning that adding, removing, or changing the data can lead to expensive tree rebalancing and index rebuilding. This can both increase the computing cost and affect the performance of ongoing queries. Because of this limitation, various data processing systems have developed pluggable geospatial indexing and partitioning systems that decouple indexing from core database operations. These are based on the idea that the surface of the Earth can be represented as a grid system. From Uber’s engineering blog:

A global grid system usually requires at least two things: a map projection and a grid laid on top of the map. A map projection is needed for going from a three-dimensional location on Earth to a two dimensional point on a map. A grid is then overlaid on the map, forming a global grid system.

In this article, I will outline some of these systems and their background, focusing on Google’s S2, Uber’s H3, and Bing Maps’ Quadbin indexing methods. But before we get started, let’s take a quick look at how indexing and partitioning work in geospatial data.

Indexing vs. Partitioning in Grid Systems

In the traditional database sense, indexing means creating a pointer table based on specific columns ordered a certain way. Partitioning, in the same context, is usually the physical separation of files under the same logical construct of a table.

While many types of indexing methodologies are available in the geospatial world, which we will discuss in a while, it is important to note that they are not really indexing techniques. Instead, they’re partitioning techniques.

Why do I say this? Indexes usually help you order data in a certain way to facilitate faster retrieval, while partitions help you segregate data for the same purpose. When it comes to geospatial data, what we’re doing with popular geospatial indexing techniques like S2, H3, and Quadbin is partitioning, not indexing.

That’s why Uber H3’s documentation says that H3 is a Hexagonal Hierarchical Spatial Index, which “enables users to partition the globe into hexagons for more accurate analysis.” Let’s understand some of the grid system partitioning methods now.

Grid System Indexing and Partitioning Methods

While many indexing (read partitioning) methods exist, four stand out because of their wide adoption and support in many data processing and visualization systems. Three of these four, S2, H3, and Quadbin, came from three large companies trying to solve maps for navigation, while Geohash was created by Canonical's CTO, Gustavo Niemeyer.

Geohash

It’s the oldest of the four methods we discussed. Geohash's author made it public in 2008. It is based on the equirectangular projection, which was invented thousands of years ago. It has its shortcomings, as do the others that we will discuss. After all, we are trying to map an irregularly shaped ellipsoid (our beautiful planet) to a perfect rectangle. What could go wrong? Indulge yourself in watching this short video that explains the trouble with projections.

Nevertheless, it is one of the most popular methods for implementing proximity searches. It is a system for encoding location in strings, not numbers while creating a hierarchical quadtree grid system. It is used for place identification, but a more popular, human-readable sort of hash would be what3words, a service that converts any lat long pair to a square of area 3m² identifiable with three words like ///candy.wire.dull. That’s our Versent Melbourne office, by the way.

Geohash can go to an arbitrary number of levels. When you zoom in, you break the squares into four, forcing them to be arranged into a tree that will later be used for search and traversal. This is where a Z-order curve comes in. It orders these smaller squares, automatically enabling you to perform a breadth-first search on your whole tree.

Interestingly, Z-order curves are also used to reorganize data on disk to speed up queries. This feature is natively implemented in the Delta Lake file format. The Databricks documentation says the following:

Z-ordering is a technique to colocate related information in the same set of files. This co-locality is automatically used by Delta Lake on Databricks data-skipping algorithms. This behavior dramatically reduces the amount of data that Delta Lake on Databricks needs to read.

The Cloudera blog also talks about using Z-order curves to speed up queries, and so do Apache Hudi, AWS DynamoDB, and Aurora MySQL, among others

However, using Geohash (and Z-order curves to traverse) doesn’t come without challenges, mainly because of the choice of projection and the grid shape used. One major drawback is significant variations in squares at different latitudes. There are also limitations around inaccuracies in proximity searches closer to the boundary of the geohash square, i.e., points closer to the geohash boundary will end up having quite different hashes, hence unrecognizable.

Google’s S2

Geospatial processing can be compute-intensive. To mitigate this issue, Google’s open-source indexing library, S2, assumes Earth as a perfect mathematical sphere, making specific geospatial processing easier and less expensive.

Announcing the S2 Library: Geometry on the Sphere

S2 is the only one of the three methods we’re discussing that represents the data in a hierarchy of quadrilaterals represented on a three-dimensional sphere. Because of this, S2 shows less distortion towards the extremes of the northern and southern hemispheres.

S2 offers a wide range of shapes and sizes that you can use to partition your grid using spherical caps or discs, latitude-longitude rectangles, polylines, and polygons. This makes S2 a good choice for implementing proximity-based searches, simplifying geographies, and quick lookups.

Note that the percentage of area signifies the accuracy of a partitioning method it can successfully represent under all its partitions combined. S2 uses a function based on the continuous and space-filling Hilbert curve that visits every point in a unit square to break a sphere into smaller chunks; it’s how you traverse (and search) the grid on your map.

Spatial Index: Space-Filling Curves

This was worth mentioning because grid traversal, although with slightly different techniques, reappears when discussing H3 and Quadbin. For a deeper look, read the following:

• Grid System Spatial Analysis in BigQuery
• Google’s S2, geometry on the sphere, cells, and Hilbert curve

Google’s S2 suffers, however, from the same drawbacks as Geohash. The grid cells aren’t equidistant from all their neighbours, which makes things difficult in many use cases. At least there’s a guarantee that a data point will always land in only one grid cell, unlike Uber’s H3, which can land in multiple cells, which we’ll come to a bit later. S2, in many ways, is better than Geohash but doesn’t have the portability, interoperability, and widespread support of Geohash, which is why it might be a hard sell.

Quadbin

Bing Maps launched in the same year as Google Maps, i.e., 2005, but took a different approach to mapping the globe when it used Quadtrees (conceptually quite similar to geohashes). Bing Maps’ Quadtree goes up to 23 levels. This implementation by Bing Maps is called Quadkey.

Quadbin is a hierarchical index based on Quadkey. It stores grid cell values in a 64-bit number and goes a bit further concerning the level of detail to 27 levels, which can point to areas less than 1m². Bing Maps uses the Mercator projection, which comes with its challenges. While Bing Maps will be deprecated sometime in 2025, Azure Maps will continue to use the Spherical Mercator projection coordinate system.

Uber’s H3

While the rest of the systems we’ve discussed use rectangles and squares to create a mappable global grid, H3 uses hexagons, assuming the globe to be a regular icosahedron instead of a perfect sphere. This differs from Geohash (which uses an equirectangular projection), S2 (which uses a stereographic projection), and Quadbin (which uses a Mercator projection). The original blog post announcing the open-sourcing of H3 says the following about this choice:

This projects from Earth as a sphere to an icosahedron, a twenty-sided platonic solid. An icosahedron-based map projection results in twenty separate two-dimensional planes rather than a single plane. The icosahedron can be unfolded in many ways, producing a two-dimensional map each time. H3, however, does not unfold the icosahedron to build its grid system, and instead lays its grid out on the icosahedron faces themselves, forming a geodesic discrete global grid system.

I’ve already discussed H3 in another piece covering geospatial data ingestion, indexing, and analysis in Snowflake. I also discuss why hexagons have the edge when calculating grid distances, proximity searches, and navigation.

Getting Started with Geospatial Data in Snowflake

Ben Feifke’s blog post about these systems has a section at the end that compares these three indexing/partitioning systems and discusses when to use which one.

Support for Geohash, S2, H3, and Quadbin

These indexing and partitioning systems are available in most databases and data warehouse systems. They can be used independently as they can be decoupled from the database's core indexing implementation. For example, Foursqaure’s layer library offers H3, S2, and Hexbin layers that can be used in analytical calculations or as overlays on top of existing geospatial visualizations.

The support for different grid systems, especially H3 and Geohash, is exceptionally promising across the data ecosystem. PostgreSQL, Snowflake, Databricks, ClickHouse, and even newer databases like DuckDB have extensions or built-in libraries. This means you can use the indexing and partitioning system that suits your use case.

Conclusion

My experience working with geospatial indexing and partitioning systems has been with PostgreSQL with PostGIS (the OG, I’d say), Redshift, and MySQL, among others. Recently, though, I’ve been spending quite a bit of time exploring Snowflake for geospatial data, and it’s becoming more promising by the day. Check out the end of June 2024 update regarding new H3 functions to know more.

I hope this overview of geospatial indexing and partitioning systems has been useful to you. I’m working on my next post in this series exploring geospatial data, which will examine an actual use case using one of the database systems mentioned above.

Thanks to Kroum Kluctchkov, Dima Marr, and Haymang Ahuja for providing feedback on this blog post.

Geospatial Indexing and Partitioning in Grid Systems was originally published in Versent Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.

GEOSPATIAL

A brief overview of Snowflake’s native geospatial capabilities and how to extend them

Background

Skip this section if you understand the fundamentals of geospatial data types and spatial reference systems.

Numeric, string, boolean, and other data types have reference systems. Numeric data types follow the decimal number system. String data types adhere to a character set (computationally represented by the hexadecimal system), while boolean data types follow the binary number system, and so on. You can efficiently perform basic manipulations and operations on these data types. You can also run an analysis on top of the data held in these data types. The underlying reference system powers all these operations performed using the data type.

Although geolocation data, at its most basic level, is just a pair of numbers — latitude and longitude — which can be stored in numeric data types, it is hard to perform real-world calculations on those numbers. The decimal number system alone isn’t enough for storing information about a place on Earth. A system analysing geospatial data must know that the two points lie on a specific surface, even to calculate something as simple as the distance between two points. That’s where a spatial reference system comes into the picture.

Now, most databases use two types of reference systems to map these points to a surface. The first assumes that the planet can be represented as a flat surface on a Cartesian or Euclidian plane, giving birth to the GEOMETRY data type. The second assumes that the planet can be modelled as a perfect sphere, making GEOGRAPHY data type the other option.

Using GEOGRAPHY data type makes more sense when calculating distances across the planet's surface. On the other hand, the GEOMETRY data type is more useful when visualising data on a two-dimensional map of a geographical area. The good thing is that, if your database allows, you can convert one data type to another using standard built-in functions. That’s it on the brief background. Let’s look at how Snowflake deals with geospatial data!

Working with Geospatial Data in Snowflake

In June 2021, Snowflake’s first geospatial data type — GEOGRAPHY— went GA. This May, they’ve also made the GEOMETRY datatype GA will now help geospatial data analysts and engineers have more flexibility in consuming the data through analysis and visualisation. To understand more about geometric representations in Snowflake, read this post on the Snowflake blog.

Ingesting Geospatial Data into Snowflake

Geospatial data can come in all shapes and sizes. The files you source into Snowflake may have a structure that isn’t very different from a CSV file, with the only difference being that the source files will have latitudes and longitudes. If the data is already processed and shaped up as having a geospatial representation, you can expect it to be in a well-known text format, where you can represent a pair of latitude and longitude as a POINT and a group of latitude and longitude pairs as a MULTILINE or a POLYGON.

There are also other standards and formats that Snowflake supports for ingestion, such as GeoJSON. You can look at the complete list here. There’s a difference between WKT/WKB and GeoJSON, which is worth repeating here from the Snowflake documentation —

The WKT and WKB standards specify a format only; the semantics of WKT/WKB objects depend on the reference system — for example, a plane or a sphere.

The GeoJSON standard, on the other hand, specifies both a format and its semantics: GeoJSON points are explicitly WGS 84 coordinates, and GeoJSON line segments are supposed to be planar edges (straight lines).

This goes back to the context I gave you about reference systems. Anyway, the long and short of it is that your geospatial data can be in various formats to be fit for ingestion. For successful ingestion, you must align or transform (using reference system transform functions like ST_TRANSFORM) your Snowflake table structure and data types according to the source data format and reference system.

Geospatial Data Processing in Snowflake

You may have several use cases for processing geospatial data, i.e., calculating proximity, adjacency, and overlap between points, lines, and polygons, among other things. Geospatial data aggregation is one of the most prominent use cases. Like how you calculate total sales, cumulative sales, etc., for an e-commerce business, you may need to aggregate your geospatial data from a point to a multipoint, a linestring, a polygon, and more, for a logistics business.

Here’s a simple example that comes from a free Snowflake marketplace data share that has data about bike stations in Chicago:

CREATE TABLE ANALYTICS.PUBLIC.STATION_INFO_FLATTEN_SOS AS

WITH bike_station_locations AS
(SELECT REPLACE(station_id,'"','') station_id,
        REPLACE(name,'"','') station_name,
        ST_MAKEPOINT(lon, lat) point
   FROM CHICAGO_DIVVY_BIKE_STATION_STATUS.PUBLIC.STATION_INFO_FLATTEN
  LIMIT 5)
  
SELECT ST_COLLECT(point) point
  FROM bike_station_locations;

SELECT * FROM ANALYTICS.PUBLIC.STATION_INFO_FLATTEN_SOS;

The above piece of SQL reads the latitude and longitude values for the bike station location and converts them into a POINT data type. Once that’s done, it uses the ST_COLLECT aggregation function to convert several points to a MULTIPOINT, which sort of works like the LISTAGG function in Oracle or the GROUP_CONCAT function in MySQL. Here’s what the output will look like:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "properties": {},
      "geometry": {
        "coordinates": [
          [
            -87.673688,
            41.871262
          ],
          [
            -87.6601406209,
            41.98974251144
          ],
          [
            -87.5970051479,
            41.81409271048
          ],
          [
            -87.57632374763489,
            41.78091096424803
          ],
          [
            -87.707322,
            41.921525
          ]
        ],
        "type": "MultiPoint"
      }
    }
  ]
}

Now, if you paste this into any GeoJSON visualiser like geojson.io, you can see the points on a map, as shown in the image below:

There’s a lot more to geospatial data processing. I’ll cover that in more detail in an upcoming post.

Geospatial Search with Search Optimisation Service

Most relational databases that support geospatial data also have constructs for geospatial indexing. Snowflake, on the other hand, doesn’t support indexes. Instead, you can use Snowflake’s Search Optimization Service (SOS) to speed up your geospatial search queries. Here’s how you enable search optimisation on a column:

ALTER TABLE ANALYTICS.PUBLIC.STATION_INFO_FLATTEN_SOS 
  ADD SEARCH OPTIMIZATION ON GEO(point);

Like PostgreSQL, which supports search functionality using specific geospatially-indexed functions, Snowflake does that for some supported functions. Currently, geospatial search optimisation only works for columns with GEOGRAPHY data type. GEOMETRY data type is not supported yet.

Snowflake’s Search Optimization Service helps speed up point look-up queries that answer questions like:

• Whether a geospatial object is fully contained within another geospatial object — a state within a country, a water body within a county, etc., using the ST_WITHIN geospatial function.
• Whether two geospatial objects share any common portion — this turns out to be extremely useful and essential in mapping houses, localities, and communities using the ST_INTERSECTS geospatial function.

This service works better if the data you seek is clustered together. If you don’t already have a geographical distribution column, you can use the ST_GEOHASH function to leverage the proximity grouping method that a geohash employs.

Spatial Partitioning and Indexing with H3

While the Search Optimisation Service enables point look-up queries, many more use cases require a space partitioning system as a discrete global grid. These use cases revolve mostly around location-based aggregation of moving vehicles, IoT devices, traffic lights, and other objects.

Although H3 (Hexagonal Hierarchical Geospatial Indexing System) is more popularly known as an indexing system, it aligns more with partitioning data, i.e., H3 partitions data like a range-based or a key-based partition scheme partitions your data in a relational database. The key difference here is that your partitions are hexagonal boundaries, which have benefits you can read about in the following blog — Why We Use H3.

A quick note on Hexagons — and why they’re better to map to a sphere.

When you want to map the Earth, you want to devise the most efficient way of spatially partitioning it into blocks or sections that don’t leave any space uncovered. This can be achieved by creating a tessellation, a tiling, of polygons, which can either be triangles, squares, or hexagons. These are the only three shapes that can fill a plane or a spherical surface without leaving a gap. A hexagon is the most complex polygon that can fill a plane, which makes it the most accurate when mapping to a spherical-ish surface because of the curvature.

Read the following piece for more info on Hexagonal grids:

Fishnets and Honeycomb: Square vs. Hexagonal Spatial Grids | Matt Strimas-Mackey

The value of an H3 index is subjective; it depends on two fixed values and one variable value, i.e., latitude, longitude, and the H3 index resolution. When you pass these three things to a function that calculates the H3 value, you uniquely identify a hexagon on the discrete global grid of hexagons.

Using the values derived from the H3 spatial partitioning system, you can map grid hexagons ranging from approximately one m² to about 4.3 million km², represented with number of hexagons ranging from 569.7 trillion to 122, with resolutions ranging from 15 to 0.

Here’s the grid at resolution 10 with the highlighted hexagon where our Melbourne office is located.

Working with geospatial data, you’ll figure out that the visualisation aspect only comes at the end of the pipeline. Before that, it’s going to be, more or less, the same kind of data processing that gets done with any other form of data. Here’s a quick peek into how H3 values are created using latitude and longitude values at two different resolutions (for two different levels of aggregation):

WITH bike_station_locations AS
(SELECT REPLACE(station_id,'"','') station_id,
        REPLACE(name,'"','') station_name,
        ST_MAKEPOINT(lon, lat) point,
        H3_POINT_TO_CELL(ST_MAKEPOINT(lon, lat),12) h3_index_12,
        H3_POINT_TO_CELL(ST_MAKEPOINT(lon, lat),10) h3_index_10
   FROM CHICAGO_DIVVY_BIKE_STATION_STATUS.PUBLIC.STATION_INFO_FLATTEN
  LIMIT 10)
  
SELECT * 
  FROM bike_station_locations;

Here’s what the output of this query would look like:

The H3 spatial partitioning system not only helps address the problem of spatial data optimisation but also solves many practical problems, like identifying taxi supply and demand clusters, pinpointing accident-prone areas, and many more.

Snowflake’s geospatial capabilities are well-placed to solve any geospatial ingestion, processing, analysis, and visualisation problem that might come your way. As described in the article, Snowflake extensively supports geospatial data types, specialised indexing, partitioning systems, and more. While this was an introduction to the geospatial capabilities within Snowflake, in a couple of upcoming blogs, I’ll cover a step-by-step approach to ingesting, processing, optimising, converting, and visualising spatial data and how Snowflake handles the visualisation aspect of geospatial data with Streamlit. Meanwhile, you can check out this blog on Streamlit’s website, which discusses exactly that.

Further Reading

• Migrating Spatial Data from PostgreSQL to Snowflake
• Geospatial Functions in Snowflake
• Cell Counts and Resolutions in H3 Indexing
• H3 Indexes and PostGIS
• Snowflake Quickstart — Getting Started with Geospatial
• The Defence Context, Geospatial Standards for Data Science — Dr Paul. Cripps
• The Miraculous Space Efficiency of Honeycomb
• Hexagons are the Bestagons, and Hexagons are NotSoGreatAgons

Credits

Thanks to Matt Dudley, Sagar Kulkarni, and Emerald Leung for generously taking time out and providing feedback on this blog.

Getting Started with Geospatial Data in Snowflake was originally published in Versent Tech Blog on Medium, where people are continuing the conversation by highlighting and responding to this story.