Iceberg and Serverless DuckDB in Google Cloud

Running Iceberg and Serverless DuckDB in Google Cloud

# Iceberg Query Engines

Many in the data space are now aware of Iceberg and its powerful features that bring database-like functionality to files stored in the likes of S3 or GCS. But Iceberg is just one piece of the puzzle when it comes to transforming files in a data lake into a Lakehouse capable of analytical and ML workloads. Along with Iceberg, which is primarily a table format, a query engine is also required to run queries against the tables and schemas managed by Iceberg. In this post we explore some of the query engines available to those looking to build a data stack around Iceberg: Snowflake, Spark, Trino, and DuckDB.

**DuckDB spoiler:** if you want to see a demo of DuckDB + Iceberg jump down to the DuckDB section. Or if you just want the code, go [here](https://github.com/steven-luabase/duckdb-iceberg-demo/blob/main/duck_iceberg_demo/duck_iceberg_demo.ipynb).

## Snowflake

Though best known for its turnkey data warehouse that combines compute and storage into one product, Snowflake started supporting Iceberg tables a few years ago. With a Snowflake + Iceberg set up, Snowflake’s compute engine can be used to query Iceberg tables external to Snowflake. By pairing Snowflake compute with Iceberg, users can harness Snowflake features such as auto-scaling resources and access control policies. Snowflake’s compute costs may be a limitation for some but its out-of-the box readiness may be worth it. Another factor to consider is [Databricks’ acquisition of core Iceberg contributor, Tabular,](https://www.definite.app/blog/databricks-tabular-acquisition) which could influence the future development of Iceberg in directions less optimized for Snowflake. Nonetheless Snowflake may be a good choice for those who already use the platform or those looking for a fully managed query engine. 

## Spark

Spark is another commonly used query engine for Iceberg. Having been designed for big data workloads, Spark works well with the large amounts of data typically found in data lakes. Spark’s parallelization optimizations along with its horizontal scalability (scales by increasing number of nodes) allow it to efficiently execute complex queries against large data sets. Furthermore, Spark supports querying Iceberg with both SparkSQL and Spark dataframes allowing developers and analysts flexibility in choosing their preferred method of data manipulation. Spark is also used in machine learning pipelines so integrating a Spark query engine for Iceberg may streamline workflows needed for ML/AI tasks. Though Spark is great for many big data use cases, it can be resource intensive and requires a decent amount of expertise to configure and optimize Spark clusters. For teams with the dedicated resources to manage Spark, it can be an excellent choice for complex big data and machine learning use cases.

## Trino

Trino is also a popular query engine for Iceberg. Similar to Spark, Trino is an open source distributed compute engine but with a focus on SQL execution. Whereas Spark has a broad range of use cases, Trino’s singular focus on query execution allow it be relatively more lightweight compared to Spark. As a result management of Trino clusters is less labor intensive than managing Spark clusters, though some set up and maintenance is still required. Trino is also highly optimized for speed thanks to in-memory processing of frequently accessed data and parallel execution of query stages. In [benchmarks](https://www.datamonad.com/post/2023-05-31-trino-spark-hive-performance-1.7/), Trino tends to outperform Spark when it comes to query speed. For teams with the necessary resources to maintain the clusters, Trino is a good choice for use cases where high performance, low latency SQL querying is the top priority.

## DuckDB

We’ve covered DuckDB in a previous post ([How We Migrated From Snowflake to Duckdb](https://www.definite.app/blog/duckdb-datawarehouse)) but it’s also shaping up to be a potential query engine for Iceberg as both projects continue developing. 

DuckDB is open source (unlike Snowflake) and lighter weight than Spark or Trino. Operations are done in memory and can be performed on a single machine. Installing DuckDB is as simple as `pip install duckdb` and is deployable without complex configurations. Despite its simplicity DuckDB is highly performant, consistently ranking near the top of benchmarks (see [**here**](https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6dHJ1ZSwiQXRoZW5hIChwYXJ0aXRpb25lZCkiOnRydWUsIkF0aGVuYSAoc2luZ2xlKSI6dHJ1ZSwiQXVyb3JhIGZvciBNeVNRTCI6dHJ1ZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjp0cnVlLCJCeUNvbml0eSI6dHJ1ZSwiQnl0ZUhvdXNlIjp0cnVlLCJjaERCIjp0cnVlLCJDaXR1cyI6dHJ1ZSwiQ2xpY2tIb3VzZSBDbG91ZCAoYXdzKSI6dHJ1ZSwiQ2xpY2tIb3VzZSBDbG91ZCAoZ2NwKSI6dHJ1ZSwiQ2xpY2tIb3VzZSAyMy4xMSAoZGF0YSBsYWtlLCBwYXJ0aXRpb25lZCkiOnRydWUsIkNsaWNrSG91c2UgMjMuMTEgKGRhdGEgbGFrZSwgc2luZ2xlKSI6dHJ1ZSwiQ2xpY2tIb3VzZSAyMy4xMSAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJDbGlja0hvdXNlIDIzLjExIChQYXJxdWV0LCBzaW5nbGUpIjp0cnVlLCJDbGlja0hvdXNlIDIzLjExICh3ZWIpIjp0cnVlLCJDbGlja0hvdXNlIjp0cnVlLCJDbGlja0hvdXNlICh0dW5lZCkiOnRydWUsIkNsaWNrSG91c2UgMjMuMTEiOnRydWUsIkNsaWNrSG91c2UgKHpzdGQpIjp0cnVlLCJDcmF0ZURCIjp0cnVlLCJEYXRhYmVuZCI6dHJ1ZSwiRGF0YUZ1c2lvbiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJEYXRhRnVzaW9uIChQYXJxdWV0LCBzaW5nbGUpIjp0cnVlLCJBcGFjaGUgRG9yaXMiOnRydWUsIkRydWlkIjp0cnVlLCJEdWNrREIgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6dHJ1ZSwiRHVja0RCIjp0cnVlLCJFbGFzdGljc2VhcmNoIjp0cnVlLCJFbGFzdGljc2VhcmNoICh0dW5lZCkiOmZhbHNlLCJHcmVlbnBsdW0iOnRydWUsIkhlYXZ5QUkiOnRydWUsIkh5ZHJhIjp0cnVlLCJJbmZvYnJpZ2h0Ijp0cnVlLCJLaW5ldGljYSI6dHJ1ZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6dHJ1ZSwiTWFyaWFEQiI6ZmFsc2UsIk1vbmV0REIiOnRydWUsIk1vbmdvREIiOnRydWUsIk15U1FMIChNeUlTQU0pIjp0cnVlLCJNeVNRTCI6dHJ1ZSwiUGlub3QiOnRydWUsIlBvc3RncmVTUUwgKHR1bmVkKSI6ZmFsc2UsIlBvc3RncmVTUUwiOnRydWUsIlF1ZXN0REIgKHBhcnRpdGlvbmVkKSI6dHJ1ZSwiUXVlc3REQiI6dHJ1ZSwiUmVkc2hpZnQiOnRydWUsIlNlbGVjdERCIjp0cnVlLCJTaW5nbGVTdG9yZSI6dHJ1ZSwiU25vd2ZsYWtlIjp0cnVlLCJTUUxpdGUiOnRydWUsIlN0YXJSb2NrcyI6dHJ1ZSwiVGltZXNjYWxlREIgKGNvbXByZXNzaW9uKSI6dHJ1ZSwiVGltZXNjYWxlREIiOnRydWV9LCJ0eXBlIjp7IkMiOnRydWUsImNvbHVtbi1vcmllbnRlZCI6dHJ1ZSwiUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJtYW5hZ2VkIjp0cnVlLCJnY3AiOnRydWUsInN0YXRlbGVzcyI6dHJ1ZSwiSmF2YSI6dHJ1ZSwiQysrIjp0cnVlLCJNeVNRTCBjb21wYXRpYmxlIjp0cnVlLCJyb3ctb3JpZW50ZWQiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6dHJ1ZSwiZW1iZWRkZWQiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsImF3cyI6dHJ1ZSwiUnVzdCI6dHJ1ZSwic2VhcmNoIjp0cnVlLCJkb2N1bWVudCI6dHJ1ZSwidGltZS1zZXJpZXMiOnRydWV9LCJtYWNoaW5lIjp7IjE2IHZDUFUgMTI4R0IiOmZhbHNlLCI4IHZDUFUgNjRHQiI6ZmFsc2UsInNlcnZlcmxlc3MiOmZhbHNlLCIxNmFjdSI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkwiOmZhbHNlLCJNIjpmYWxzZSwiUyI6ZmFsc2UsIlhTIjpmYWxzZSwiYzZhLm1ldGFsLCA1MDBnYiBncDIiOmZhbHNlLCIxOTJHQiI6ZmFsc2UsIjI0R0IiOmZhbHNlLCIzNjBHQiI6ZmFsc2UsIjQ4R0IiOmZhbHNlLCI3MjBHQiI6ZmFsc2UsIjk2R0IiOmZhbHNlLCIxNDMwR0IiOmZhbHNlLCJkZXYiOmZhbHNlLCI3MDhHQiI6ZmFsc2UsImM1bi40eGxhcmdlLCA1MDBnYiBncDIiOmZhbHNlLCJjNS40eGxhcmdlLCA1MDBnYiBncDIiOmZhbHNlLCJtNWQuMjR4bGFyZ2UiOmZhbHNlLCJtNmkuMzJ4bGFyZ2UiOmZhbHNlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6ZmFsc2UsImRjMi44eGxhcmdlIjpmYWxzZSwicmEzLjE2eGxhcmdlIjpmYWxzZSwicmEzLjR4bGFyZ2UiOmZhbHNlLCJyYTMueGxwbHVzIjpmYWxzZSwiUzIiOmZhbHNlLCJTMjQiOmZhbHNlLCIyWEwiOmZhbHNlLCIzWEwiOmZhbHNlLCI0WEwiOmZhbHNlLCJYTCI6ZmFsc2V9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTYiOnRydWUsIjMyIjp0cnVlLCI2NCI6dHJ1ZSwiMTI4Ijp0cnVlLCJzZXJ2ZXJsZXNzIjp0cnVlLCJkZWRpY2F0ZWQiOnRydWUsInVuZGVmaW5lZCI6dHJ1ZX0sIm1ldHJpYyI6ImhvdCIsInF1ZXJpZXMiOlt0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlXX0=) and [**here**](https://duckdblabs.github.io/db-benchmark/)). In a [3 way comparison](https://medium.com/datamindedbe/head-to-head-comparison-of-dbt-sql-engines-497d71535881) of using DuckDB, Spark, and Trino for dbt transformations, DuckDB was the fastest in most scenarios. However, depending on the memory constraints of the machine DuckDB is running on, it may not be a good choice for very large (multi-terabyte range) data sets. With that said given it’s strong performance on most medium to large data workloads and its easy setup, you may find that DuckDB strikes the right balance between performance and maintenance overhead. 

### DuckDB + Iceberg Example

We will be loading 12 months of [NYC yellow cab trip data](https://www.nyc.gov/site/tlc/about/tlc-trip-record-data.page) (April 2023 - April 2024) into Iceberg tables and demonstrating how DuckDB can query these tables. 

We first read the trips and zones (pickup/drop off locations) data from the NYC taxi site.
```python
import duckdb
import pyarrow as pa

# get data from April 2023 to April 2024
trips_ls = []
months = [
    '2023-04',
    '2023-05', 
    '2023-06', 
    '2023-07', 
    '2023-08', 
    '2023-09', 
    '2023-10', 
    '2023-11', 
    '2023-12', 
    '2024-01', 
    '2024-02', 
    '2024-03', 
    '2024-04'
    ]
for month in months:
    table_path = f'https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_{month}.parquet'
    # NOTE: this initial data read doesn't require Duckdb, something like pandas works as well
    table = duckdb.sql(f"SELECT * FROM '{table_path}'").arrow()
    trips_ls.append(table)

# concatenate all monthly trips data into one pyarrow table
trips = pa.concat_tables(trips_ls)

# get location zone mapping
zones = duckdb.sql("SELECT * FROM 'https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv'").arrow()
```
Here is the schema for  `trips` (41,994,806 rows):

```python
VendorID: int32
tpep_pickup_datetime: timestamp[us]
tpep_dropoff_datetime: timestamp[us]
passenger_count: int64
trip_distance: double
RatecodeID: int64
store_and_fwd_flag: string
PULocationID: int32
DOLocationID: int32
payment_type: int64
fare_amount: double
extra: double
mta_tax: double
tip_amount: double
tolls_amount: double
improvement_surcharge: double
total_amount: double
congestion_surcharge: double
Airport_fee: double
```

and here is the schema for `zones` (265 rows):

```python
LocationID: int64
Borough: string
Zone: string
service_zone: string
```

Now that we have our data let’s setup Iceberg. To do this we use [PyIceberg](https://py.iceberg.apache.org/), a Python library for interacting with Iceberg tables. The first step is to create an [Iceberg catalog](https://iceberg.apache.org/concepts/catalog/). The catalog is essentially comprised of pointers that direct the query engine on how to read the data such as where to find the data, what the schema is, how it is partitioned, and if any manipulations have been applied to it. In our demo we will use sqlite for the catalog:

```python
from pyiceberg.catalog.sql import SqlCatalog

# create iceberg catalog using sqlite
warehouse_path = "SET_YOUR_PATH"
name_space = "demo_db"
catalog = SqlCatalog(
    name_space,
    **{
        "uri": f"sqlite:///{warehouse_path}/pyiceberg_catalog.db",
        "warehouse": f"file://{warehouse_path}",
    },
)

# create a namespace for Iceberg
catalog.create_namespace(name_space)
```

Notice in the last line above we created a name space, `demo_db`, which will be where the actual data gets loaded into.

We also add two helper functions needed to ensure that every table’s catalog and metadata are properly synced. This is to address the Github issue mentioned [here](https://github.com/duckdb/duckdb_iceberg/issues/29):

```python
import sqlite3

def get_iceberg_tables(database_path, table_namespace=None, table_name=None):
    """
    Connect to the SQLite database and retrieve the list of Iceberg tables.
    Optionally filter by namespace and table name.

    Parameters:
        database_path (str): The path to the SQLite database file.
        table_namespace (str, optional): The namespace of the table to search for.
        table_name (str, optional): The name of the table to search for.

    Returns:
        list: A list of dictionaries, each representing an Iceberg table.

    Raises:
        ValueError: If only one of table_namespace or table_name is provided.
    """
    # Check if both namespace and table name are provided together
    if (table_namespace and not table_name) or (table_name and not table_namespace):
        raise ValueError("Both table_namespace and table_name must be provided together.")
    
    # Connect to the SQLite database
    con_meta = sqlite3.connect(database_path)
    con_meta.row_factory = sqlite3.Row

    # Create a cursor object to execute SQL queries
    cursor = con_meta.cursor()

    # Base query to list tables in the database
    query = 'SELECT * FROM "iceberg_tables" WHERE 1=1'
    params = []

    # Add conditions to the query based on provided namespace and table name
    if table_namespace and table_name:
        query += ' AND "table_namespace" = ? AND "table_name" = ?'
        params.append(table_namespace)
        params.append(table_name)

    # Execute the query with parameters
    cursor.execute(query, params)

    # Fetch all results
    results = cursor.fetchall()

    # Convert results to list of dictionaries
    table_list = []
    for row in results:
        row_dict = {key: row[key] for key in row.keys()}
        table_list.append(row_dict)

    # Close the connection
    con_meta.close()

    return table_list

def create_metadata_for_tables(tables):
    """
    Iterate through all tables and create metadata files.

    Parameters:
        tables (list): A list of dictionaries, each representing an Iceberg table with a 'metadata_location'.
    """
    for table in tables:
        metadata_location = table['metadata_location'].replace('file://', '')
        metadata_dir = os.path.dirname(metadata_location)
        new_metadata_file = os.path.join(metadata_dir, 'v1.metadata.json')
        version_hint_file = os.path.join(metadata_dir, 'version-hint.text')

        # Ensure the metadata directory exists
        os.makedirs(metadata_dir, exist_ok=True)

        # Copy the metadata file to v1.metadata.json
        shutil.copy(metadata_location, new_metadata_file)
        print(f"Copied metadata file to {new_metadata_file}")

        # Create the version-hint.text file with content "1"
        with open(version_hint_file, 'w') as f:
            f.write('1')
        print(f"Created {version_hint_file} with content '1'")
```

Next we register the PyArrow tables into the catalog and load the data into Iceberg tables in the `demo_db` namespace:

```python
# add tables to iceberg
for table, table_name in [
    (trips, "trips"),
    (zones, "zones"),
]:  
	# create the iceberg table
    iceberg_table = catalog.create_table(
        f"{name_space}.{table_name}",
        schema=table.schema,
    )

    # add data to iceberg table
    iceberg_table.append(table)

    # copy catalog metadata to iceberg table
    catalog_records = get_iceberg_tables(f"{warehouse_path}/pyiceberg_catalog.db", name_space, table_name)
    create_metadata_for_tables(catalog_records)
    
    print(f"Created {table_name}, {table.num_rows} rows")
```

Before hooking up DuckDB to query the data let’s take a look at what we just created. You should see a file called `pyiceberg_catalog.db`. This is a sqlite database with pointers to data and metadata of our Iceberg instance. 

You should also see a directory called `demo_db.db` , which is where the metadata and data is actually stored. Expanding the full `demo_db.db` directory should look like this:
![Iceberg Folder Structure](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1720032028/iceberg_tables_kg6y9p.png)
Each subdirectory represents an Iceberg table (eg `trips` and `zones`). The `data` folder is where the actual data is saved in parquet format. The `metadata` folder contains information used by the query engine such as which parquet file is the latest, how to partition the data, how to manipulate parquet files, etc.

Now we connect DuckDB to Iceberg. First we initiate a DuckDB connection, install the DuckDB Iceberg extension, create a schema called `taxi`, and create views of the trips and zones Iceberg tables:

```python
# initiate a duckdb connection which we will use to be the query engine for iceberg
con = duckdb.connect(database=':memory:', read_only=False)
setup_sql = '''
INSTALL iceberg;
LOAD iceberg;
'''
res = con.execute(setup_sql)

# create the schema and views of iceberg tables in duckdb
database_path = f'{warehouse_path}/demo_db.db'

create_view_sql = f'''
CREATE SCHEMA IF NOT EXISTS taxi;

CREATE VIEW taxi.trips AS
SELECT * FROM iceberg_scan('{database_path}/trips', allow_moved_paths = true);

CREATE VIEW taxi.zones AS
SELECT * FROM iceberg_scan('{database_path}/zones', allow_moved_paths = true);
'''

con.execute(create_view_sql)
```

### Querying Iceberg With Duckdb

Let’s run some queries and time it. For reference my local machine is a mid-2021 Macbook with the M1 chip and 32gb of RAM.

**Monthly Aggregated Trip Statistics**
```python
sql = f'''
select 
    date_trunc('month', tpep_pickup_datetime) as month,
    avg(passenger_count) as avg_passenger_count,
    avg(trip_distance) as avg_trip_distance,
    sum(trip_distance) as total_trip_distance,
    avg(total_amount) as avg_total_amount,
    sum(total_amount) as total_amount,
    count(*) as total_trips
from taxi.trips
-- some data present is pre and post our target date range, so we filter it out
where tpep_pickup_datetime between '2023-04-01' and '2024-05-01'
group by 1
order by 1
'''

%time res = con.execute(sql)
res.fetchdf()
```
![monthly taxi trips](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1720032169/monthly_taxi_trips_m9n1gz.png)

**Total Trips and Total Fare Amount By Borough (1 join, sorted):**
```python
sql = f'''
select 
    zones.Borough,
    count(*) as total_trips,
    sum(total_amount) as total_amount
from taxi.zones as zones
left join taxi.trips as trips
    on zones.LocationID = trips.DOLocationID
group by 1 
order by 2 desc
'''

%time res = con.execute(sql)
res.fetchdf()
```
![borough trips](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1720032291/borough_trips_cbmmbh.png)
**Total Trips By Pickup and Drop-off Borough (2 joins, sorted):**
```python
sql = f'''
select 
    starting_zone.Borough as pickup_borough,
    ending_zone.Borough as dropoff_borough,
    count(*) as trip_count
from
taxi.trips as trips
left join taxi.zones as starting_zone
    on trips.PULocationID = starting_zone.LocationID
left join taxi.zones as ending_zone
    on trips.DOLocationID = ending_zone.LocationID
group by 1, 2
order by 1 asc, 3 desc
'''

%time res = con.execute(sql)
res.fetchdf().head(20)
```
![borough pickup and dropoffs](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1720032360/borough_pickup_dropoff_zbgmsm.png)
Even on a relatively large data set (42 million rows), DuckDB and Iceberg are able to run queries in a reasonable amount of time on just a laptop. The added benefit of implementing Iceberg instead of using DuckDB alone is that we can apply CRUD operations on an otherwise static set of parquet files. Imagine we wanted to update the schema of the taxi data, update records, or append new data, Iceberg allows us to perform these actions in a more database-like fashion. Furthermore with DuckDB we can create views to define transformation logic on top of Iceberg tables. There’s still some rough edges being worked out between the two projects such as [predicate pushdowns and partitioning](https://github.com/duckdb/duckdb_iceberg/issues/2), but there’s been huge progress on both projects and we think Duck + Iceberg will be an increasingly attractive option for building and querying a Lakehouse.

All the code in the demo can be found [here](https://github.com/steven-luabase/duckdb-iceberg-demo/blob/main/duck_iceberg_demo/duck_iceberg_demo.ipynb).

Duckdb and Iceberg

Comparing Iceberg Query Engines


In case you missed it, earlier this month Databricks acquired Tabular, the company behind the open source project Iceberg, for over $1 billion. The acquisition, which was announced during Snowflake’s 2024 Summit conference and amid rumors of Snowflake’s interest in purchasing Tabular, caught many by surprise especially since Databricks already offers a competing product, Delta Lake. So, what is Iceberg, how does it compare to Delta Lake, and what does the project’s future look like post-acquisition?

## Iceberg: Bringing CRUD Operations To Your Data Lake

In a world where the volume  and variety of data collected by companies is steadily increasing, the importance of data lakes has also grown. Data lakes are centralized repositories that allow companies to store structured and unstructured data for later use such as transforming it in a data warehouse for BI or providing training data for AI models. Due to the varied format and large data size (eg think real-time streaming data from a ride-share app), the data is often stored as raw files in a data lake. 

Iceberg, which was originally developed by Netflix, is an open source table format that allows developers to efficiently run CRUD (Create, Read, Update, Delete) operations on files in data lakes, specifically parquet files. In other words, it can make a large set of parquet files feel more like using a SQL database while maintaining parquet’s distinct scaleability and file compression features. Since Iceberg’s inception in 2017, the project has grown quickly in popularity and has been adopted by many large tech companies such as Linkedin, Apple, and Stripe. Tabular, which was founded by the Netflix engineers who created Iceberg, offers a managed service version of Iceberg to customers.

## Iceberg vs Delta Lake

Delta Lake is Databrick’s existing product equivalent to Iceberg. Below we discuss how Iceberg and Delta Lake compare to each other across several domains.

![image](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1719447314/star-history-2024626_un86pa.png)

### Open Source

Both Iceberg and Delta Lake are open sourced under the Apache License and both have meaningful contributions from developers outside of their respective main-company backers (Tabular and Databricks). However despite Delta Lake’s open source status, some have pointed out that certain features available in Databrick’s own Delta Lake implementation are [not available](https://news.ycombinator.com/item?id=38813077) or are [under-optimized](https://www.reddit.com/r/dataengineering/comments/12wsatu/comment/jhija6t/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button) in the OSS version. While it is certainly possible to set up Delta Lake outside of Databricks, some developers have also noted that it can be [more complicated to do so](https://news.ycombinator.com/item?id=38811576) relative to setting up Iceberg. Still Delta Lake is used by many outside of Databricks’ platform alongside tools like [Trino](https://github.com/trinodb/trino) or [Presto](https://github.com/prestodb/presto).

Here’s a [couple](https://news.ycombinator.com/item?id=38812825) [relevant](https://news.ycombinator.com/item?id=40347786) threads from HackerNews:
![image](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1719447422/hackernews1_ilx181.png)

![image](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1719447422/hackernews2_nwo6vc.png)

### Performance

Across all the benchmarks we could find, Delta Lake is faster than Iceberg for querying, ingesting, and updating data. Several benchmarks comparing open table formats including Delta Lake and Iceberg are linked [here](https://medium.com/walmartglobaltech/lakehouse-at-fortune-1-scale-480bcb10391b) and [here](https://www.onehouse.ai/blog/apache-hudi-vs-delta-lake-vs-apache-iceberg-lakehouse-feature-comparison). While speed isn’t always the most critical factor for all use cases, it is for some and should be taken into consideration when deciding between Delta Lake vs Iceberg. Faster performance may also yield cost savings by reducing runtimes of table operations.

### Handling Schema Evolution and Drift

Just like in conventional databases, schema changes are also occasionally needed in data lakes such as altering column names, re-partitioning tables, or even rolling back historical states. Both Iceberg and Delta Lake offer the ability to perform these actions, though Iceberg may have a slight advantage. In Iceberg, most schema and partitioning changes only require changes to the metadata whereas in Delta Lake the same change may require rewriting data. 

Additionally, Iceberg also [snapshots historical states](https://iceberg.apache.org/docs/1.5.1/branching/) of a table’s metadata allowing for easy rollback of schema changes or for auditing purposes. Delta Lake offers something similar but uses a [log based approach](https://www.databricks.com/blog/2019/08/21/diving-into-delta-lake-unpacking-the-transaction-log.html) that logs each action performed on a table. While both accomplish the same goal of saving historical schema states, some may find Iceberg’s snapshot methodology easier to use and understand.

### Integrations and Ecosystem

Iceberg and Delta Lake by themselves are just an open table format; they still require storage and a query engine. Being a project born out of Databricks, Delta Lake is unsurprisingly tightly integrated with Spark and has built-in optimizations specific to Spark. Delta Lake is also compatible with other query engines such as DuckDB, Trino, Athena, and more. Similarly Iceberg can also work with most of the same query engines that Delta Lake works with including Spark and other data warehouses’ query engines. Pre-Databricks’ acquisition of Tabular, Snowflake had been heavily pushing the use of Snowflake as a query engine for Iceberg (more on that later). 

Storage wise both Iceberg and Delta Lake support any type of storage that can store parquet files such as S3 or Google Cloud Storage.

## Iceberg’s Future Post Tabular Acquisition

Given that Iceberg is a close competitor to Delta Lake, did Databricks acquire Tabular for the sole purpose of nuking its competition? As intriguing as it might sound, this scenario seems unlikely given that the Iceberg project is open sourced under the Apache Foundation and is implemented by many large tech companies who will continue to use and contribute to the project even if Tabular ceases to exist. Rather we can speculate that Databricks’ acquired Tabular for several reasons:

- **Onboarding Iceberg users to Databricks’ ecosystem:** by offering support for Iceberg, Databricks may be able to better attract users to its platform and cross-sell other products. This move could also lead to a larger user base and increased market share.
- **Influencing the development of Iceberg:** with the main contributors and creators of Iceberg now at Databricks, Databricks can better steer the evolution of Iceberg to align with its ecosystem of products.
- **Strategic move to beat out Snowflake from acquiring and building out a data lake competitor:** though Snowflake is an established player in the data warehouse space, it is a relative newcomer to the data lake game. Snowflake’s support for Snowflake compute + Iceberg along with its bid for Tabular revealed it was looking to get more serious about building its own data lake product. By acquiring Tabular first, Databricks effectively preempted Snowflake's efforts to strengthen its position in the data lake market.

A billion dollar acquisition for a 3-year old startup is certainly noteworthy and we are eager to see what the future holds for both Databricks and Iceberg. Iceberg is a key component of what we’re building here at Definite, so we’re hoping the acquisition leads to a better, open table format.

Why Databricks paid $1B for a 40 person startup (Tabular)

Everyone loves Snowflake. The ease of use when it comes to elastic scaling, data backups, and table optimizations amongst other features make Snowflake a great choice when setting up a data stack. Given the proper budget and data resources, Snowflake is undoubtedly the leading data warehouse candidate. But has your company been considering alternatives to Snowflake, whether it be for cost, vendor lock-in concerns, or other reasons? 

We’ve done this. Here at [Definite](https://www.definite.app/) we’ve been building an alternative approach that uses an open source database, [DuckDB](https://duckdb.org/),  and a self-hosted infrastructure. With this setup we were able to migrate all our analytical data and queries from Snowflake to DuckDB. Everything from internal team dashboards to ad-hoc queries (eg Stripe MRR metrics, Hubspot deal progress, etc) now runs on our self-hosted DuckDB database.

**TLDR**: it required a good bit of work but we saw a radical reduction in cost (>70%).

## What Is DuckDB?

DuckDB is a columnar database management system built for analytical workloads. It’s fast (see below), open source and has a very active community. The [Github repo](https://github.com/duckdb/duckdb/pulse/monthly) has 17.5k stars on GitHub with 34 authors have pushing 575 commits to main in the past month. Some features of DuckDB:

- It’s fast, consistently ranking near the top of performance benchmarks for analytical databases (see [here](https://benchmark.clickhouse.com/#eyJzeXN0ZW0iOnsiQWxsb3lEQiI6dHJ1ZSwiQXRoZW5hIChwYXJ0aXRpb25lZCkiOnRydWUsIkF0aGVuYSAoc2luZ2xlKSI6dHJ1ZSwiQXVyb3JhIGZvciBNeVNRTCI6dHJ1ZSwiQXVyb3JhIGZvciBQb3N0Z3JlU1FMIjp0cnVlLCJCeUNvbml0eSI6dHJ1ZSwiQnl0ZUhvdXNlIjp0cnVlLCJjaERCIjp0cnVlLCJDaXR1cyI6dHJ1ZSwiQ2xpY2tIb3VzZSBDbG91ZCAoYXdzKSI6dHJ1ZSwiQ2xpY2tIb3VzZSBDbG91ZCAoZ2NwKSI6dHJ1ZSwiQ2xpY2tIb3VzZSAyMy4xMSAoZGF0YSBsYWtlLCBwYXJ0aXRpb25lZCkiOnRydWUsIkNsaWNrSG91c2UgMjMuMTEgKGRhdGEgbGFrZSwgc2luZ2xlKSI6dHJ1ZSwiQ2xpY2tIb3VzZSAyMy4xMSAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJDbGlja0hvdXNlIDIzLjExIChQYXJxdWV0LCBzaW5nbGUpIjp0cnVlLCJDbGlja0hvdXNlIDIzLjExICh3ZWIpIjp0cnVlLCJDbGlja0hvdXNlIjp0cnVlLCJDbGlja0hvdXNlICh0dW5lZCkiOnRydWUsIkNsaWNrSG91c2UgMjMuMTEiOnRydWUsIkNsaWNrSG91c2UgKHpzdGQpIjp0cnVlLCJDcmF0ZURCIjp0cnVlLCJEYXRhYmVuZCI6dHJ1ZSwiRGF0YUZ1c2lvbiAoUGFycXVldCwgcGFydGl0aW9uZWQpIjp0cnVlLCJEYXRhRnVzaW9uIChQYXJxdWV0LCBzaW5nbGUpIjp0cnVlLCJBcGFjaGUgRG9yaXMiOnRydWUsIkRydWlkIjp0cnVlLCJEdWNrREIgKFBhcnF1ZXQsIHBhcnRpdGlvbmVkKSI6dHJ1ZSwiRHVja0RCIjp0cnVlLCJFbGFzdGljc2VhcmNoIjp0cnVlLCJFbGFzdGljc2VhcmNoICh0dW5lZCkiOmZhbHNlLCJHcmVlbnBsdW0iOnRydWUsIkhlYXZ5QUkiOnRydWUsIkh5ZHJhIjp0cnVlLCJJbmZvYnJpZ2h0Ijp0cnVlLCJLaW5ldGljYSI6dHJ1ZSwiTWFyaWFEQiBDb2x1bW5TdG9yZSI6dHJ1ZSwiTWFyaWFEQiI6ZmFsc2UsIk1vbmV0REIiOnRydWUsIk1vbmdvREIiOnRydWUsIk15U1FMIChNeUlTQU0pIjp0cnVlLCJNeVNRTCI6dHJ1ZSwiUGlub3QiOnRydWUsIlBvc3RncmVTUUwgKHR1bmVkKSI6ZmFsc2UsIlBvc3RncmVTUUwiOnRydWUsIlF1ZXN0REIgKHBhcnRpdGlvbmVkKSI6dHJ1ZSwiUXVlc3REQiI6dHJ1ZSwiUmVkc2hpZnQiOnRydWUsIlNlbGVjdERCIjp0cnVlLCJTaW5nbGVTdG9yZSI6dHJ1ZSwiU25vd2ZsYWtlIjp0cnVlLCJTUUxpdGUiOnRydWUsIlN0YXJSb2NrcyI6dHJ1ZSwiVGltZXNjYWxlREIgKGNvbXByZXNzaW9uKSI6dHJ1ZSwiVGltZXNjYWxlREIiOnRydWV9LCJ0eXBlIjp7IkMiOnRydWUsImNvbHVtbi1vcmllbnRlZCI6dHJ1ZSwiUG9zdGdyZVNRTCBjb21wYXRpYmxlIjp0cnVlLCJtYW5hZ2VkIjp0cnVlLCJnY3AiOnRydWUsInN0YXRlbGVzcyI6dHJ1ZSwiSmF2YSI6dHJ1ZSwiQysrIjp0cnVlLCJNeVNRTCBjb21wYXRpYmxlIjp0cnVlLCJyb3ctb3JpZW50ZWQiOnRydWUsIkNsaWNrSG91c2UgZGVyaXZhdGl2ZSI6dHJ1ZSwiZW1iZWRkZWQiOnRydWUsInNlcnZlcmxlc3MiOnRydWUsImF3cyI6dHJ1ZSwiUnVzdCI6dHJ1ZSwic2VhcmNoIjp0cnVlLCJkb2N1bWVudCI6dHJ1ZSwidGltZS1zZXJpZXMiOnRydWV9LCJtYWNoaW5lIjp7IjE2IHZDUFUgMTI4R0IiOmZhbHNlLCI4IHZDUFUgNjRHQiI6ZmFsc2UsInNlcnZlcmxlc3MiOmZhbHNlLCIxNmFjdSI6ZmFsc2UsImM2YS40eGxhcmdlLCA1MDBnYiBncDIiOnRydWUsIkwiOmZhbHNlLCJNIjpmYWxzZSwiUyI6ZmFsc2UsIlhTIjpmYWxzZSwiYzZhLm1ldGFsLCA1MDBnYiBncDIiOmZhbHNlLCIxOTJHQiI6ZmFsc2UsIjI0R0IiOmZhbHNlLCIzNjBHQiI6ZmFsc2UsIjQ4R0IiOmZhbHNlLCI3MjBHQiI6ZmFsc2UsIjk2R0IiOmZhbHNlLCIxNDMwR0IiOmZhbHNlLCJkZXYiOmZhbHNlLCI3MDhHQiI6ZmFsc2UsImM1bi40eGxhcmdlLCA1MDBnYiBncDIiOmZhbHNlLCJjNS40eGxhcmdlLCA1MDBnYiBncDIiOmZhbHNlLCJtNWQuMjR4bGFyZ2UiOmZhbHNlLCJtNmkuMzJ4bGFyZ2UiOmZhbHNlLCJjNmEuNHhsYXJnZSwgMTUwMGdiIGdwMiI6ZmFsc2UsImRjMi44eGxhcmdlIjpmYWxzZSwicmEzLjE2eGxhcmdlIjpmYWxzZSwicmEzLjR4bGFyZ2UiOmZhbHNlLCJyYTMueGxwbHVzIjpmYWxzZSwiUzIiOmZhbHNlLCJTMjQiOmZhbHNlLCIyWEwiOmZhbHNlLCIzWEwiOmZhbHNlLCI0WEwiOmZhbHNlLCJYTCI6ZmFsc2V9LCJjbHVzdGVyX3NpemUiOnsiMSI6dHJ1ZSwiMiI6dHJ1ZSwiNCI6dHJ1ZSwiOCI6dHJ1ZSwiMTYiOnRydWUsIjMyIjp0cnVlLCI2NCI6dHJ1ZSwiMTI4Ijp0cnVlLCJzZXJ2ZXJsZXNzIjp0cnVlLCJkZWRpY2F0ZWQiOnRydWUsInVuZGVmaW5lZCI6dHJ1ZX0sIm1ldHJpYyI6ImhvdCIsInF1ZXJpZXMiOlt0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlLHRydWUsdHJ1ZSx0cnVlXX0=) and [here](https://duckdblabs.github.io/db-benchmark/))
- It’s lightweight and can efficiently process large datasets even on a local machine. Set up is as simple as **`*pip install duckdb*`** and pointing it to your files
- Uses a SQL dialect closely matching Postgres SQL, meaning most queries from other databases are directly translatable to Duckdb SQL
- Accepts many file formats including csv, parquet, Excel, and json. This allows Duckdb to query data from wherever your data already is such as a local directory or cloud storage like S3. The added benefit is if a decision is made to move to another database, your data isn’t locked in to DuckDB

This [article](https://betterprogramming.pub/duckdb-whats-the-hype-about-5d46aaa73196) has a good overview of DuckDB and where it fits in your data stack.

## A (Very) High Level Overview of How We Implemented DuckDB As A Data Warehouse

Unlike Snowflake, which supports multiple concurrent processes, DuckDB is designed for single-user workloads and becomes locked and un-queryable during data writes or table edits. To work around this issue, we set up two DuckDB instances, a write-to database and a read-only database:

- *Write-to Database:* DuckDB installed on a VM with a persistent disk attached to it. An example of this is **[Google Compute Engine](https://cloud.google.com/products/compute?hl=en) with a [persistent disk](https://cloud.google.com/persistent-disk?hl=en) attached to it. All write operations such as `INSERT`, `CREATE`, `UPDATE`, etc are actioned on this database first.
- *Read-only Database:* Duckdb also installed on a VM but has read access to replica database files stored in cheap blob storage such as [Google Cloud Storage](https://cloud.google.com/storage?hl=en) (GCS). After edits to the write-to DuckDB instance are finished, the new/changed tables are synced to GCS which the read-only DuckDB has access to. When a user wants to query the database (`SELECT...`), the query is routed to the read-only DuckDB which in turn fetches the data from GCS and returns it.

The benefit of having write-to and read-only DuckDB instances is that the data warehouse is still queryable while edits are happening in the background. This could be a long running ETL job such as loading Stripe transaction data or a user initiated `UPDATE` statement; we want to ensure that the data is still readable even while these are running. 

Why not also use GCS as the file store for the write-to database given how much cheaper it is relative to using a persistent disk? Currently at the time of writing, DuckDB only supports read-only mode for blob storage such as [GCS](https://duckdb.org/docs/guides/network_cloud_storage/gcs_import#attaching-to-a-database) or [S3](https://duckdb.org/docs/guides/network_cloud_storage/duckdb_over_https_or_s3). 

Though the set up outlined above requires some engineering effort, it plays a crucial role in enabling data warehouse-like functionality required for analytics and BI use cases, and we are constantly working to optimize this piece of the infrastructure. 

## Cost

To run the DuckDB setup described above we need two Google Compute Engine instances, one persistent disk, and access to GCS. To estimate the daily cost of running all this we will make the following configuration and storage assumptions:

- Size of data: 1TB
- Compute Engine vCPUs: 16
- Compute Engine RAM: 64GB

Since most data warehouses are not in use 24 hours a day, we will estimate the cost under different usage scenarios. In typical BI use cases involving dashboard refreshes, data ingestion, and ad-hoc queries, compute may only be needed during peak business hours. 

We also compare the cost of different Snowflake warehouse sizes (compute) running under the different usage scenarios:

![Untitled](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1717089229/duck_vs_snow_tables_lt4gr2.png)

As seen in the table on the right above, at 12 hours of usage per day DuckDB on GCP is ~55% cheaper than the smallest Snowflake warehouse. If we compare it to the next smallest Snowflake warehouse, Small, we can see it is ~77% cheaper and savings increase as we go up the Snowflake warehouse sizes. 

The same table as before but in chart form:

![Untitled](https://res.cloudinary.com/dj7cyd5uj/image/upload/v1717089229/ducks_vs_snow_chart_ytegvw.png)

Of course there are a lot of assumptions in this estimate that could vary; GKE specs can be scaled up or down, Snowflake users may utilize multiple warehouses of different sizes, total data storage size could be larger or smaller, etc, but directionally self hosting DuckDB should yield cost savings relative to Snowflake (see bottom for GCP and Snowflake pricing pages). As mentioned in the beginning, anecdotally at Definite we’ve seen >70% savings by switching to DuckDB. 

## Interested In Trying DuckDB?

If you want to give DuckDB a try, [here](https://marclamberti.com/blog/duckdb-getting-started-for-beginners/) is an excellent guide on how to get it running on your local machine. If you want a more cloud data warehouse-like experience and have the resources to set up your own infrastructure, the DIY approach we outlined in this post is an option. Alternatively, managed solutions like [Motherduck](https://motherduck.com/) or [Definite](https://www.definite.app/) can be a pain-free way to quickly spin up your own DuckDB powered data warehouse. 

With Definite, not only do you get a managed DuckDB warehouse, we’ve also added hundreds of integrations to a wide range of other products (Salesforce, Stripe, Postgres, etc) so you can seamlessly move data in and out of DuckDB. Additionally, we also built a BI layer and AI-enabled natural language query layer on top of DuckDB, allowing anyone on your team to derive data insights. Definite’s goal is to be your data-stack in a box with ETL, data warehousing, and analytics all deployed with a few clicks. If you would like to learn more, feel free to reach out!

**Resource Pricing References:**

[Google Cloud Compute](https://cloud.google.com/compute/all-pricing)

[Google Cloud Persistent Disk](https://cloud.google.com/compute/disks-image-pricing#disk)

[Google Cloud Storage](https://cloud.google.com/storage/pricing)

[Snowflake Credit and Storage Pricing](https://www.snowflake.com/en/data-cloud/pricing-options/)

[Snowflake Compute Warehouse Credit Usage](https://docs.snowflake.com/en/user-guide/cost-understanding-compute)

How We Migrated Our Data Warehouse from Snowflake to DuckDB


Many companies dump data into S3 thinking, "we'll figure out how to use it later". They often never figure it out. It too complicated to query S3 data directly (e.g. Spark) and it's too expensive to pipe it to a data warehouse (e.g. Snowflake).

In the vast, sprawling library that is your S3 data lake, volumes of information sit gathering dust on the shelves. Each book holds valuable insights, but without a card catalog or a knowledgeable librarian, finding the right nugget of data is like searching for a needle in a haystack. You wander the aisles aimlessly, pulling out books at random, hoping to stumble upon the answers you seek. 

You need an AI librarian. One that will organize your library and guide you to insights. Enter [Definite](https://www.definite.app/): the missing card catalog for your S3 data library.



<div class="w-full flex justify-center mb-4">
<iframe class="w-full" width="560" height="400" src="https://www.youtube.com/embed/yrrCQnfKEig?si=2mqMTMP95eEpfxTk" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" referrerpolicy="strict-origin-when-cross-origin" allowfullscreen></iframe>
</div>


## From Raw Data to Actionable Insights

### 1. Seamless Connection and Direct Queries:

- No cumbersome data pipelines and intermediary databases. Definite directly connects to your S3 buckets with your access key and secret.
- Once connected, querying your data becomes as simple as writing SQL. Let's say you have taxi trip data stored in Parquet format:

```sql
SELECT passenger_count, trip_distance, total_amount
FROM read_parquet('s3://your-bucket/taxi_trips/2023-06.parquet') 
WHERE company = 'Uber'
ORDER BY trip_distance DESC
LIMIT 10;
```

### 2. Building Intuitive Data Models (Cubes):

- Definite introduces "cubes" - data models that encapsulate business logic and metrics. Define your dimensions and measures in a simple YAML format:

```yaml
cubes:
  taxi_trips:
    measures:
      trip_count:
        type: count
      average_distance:
        type: average
        sql: trip_distance
      total_revenue:
        type: sum
        sql: total_amount
    dimensions:
      company:  
        type: string
      day_of_week:
        type: string
      passenger_count:
        type: number
```

This code defines a taxi_trips cube with measures like trip count, average distance, and total revenue, along with dimensions like company, day of the week, and passenger count. This cube becomes a versatile tool for analyzing various aspects of your taxi trip data.

### 3. Fi: Your AI-Powered Data Companion:

- Definite's AI assistant, Fi, breaks down the barrier of technical expertise. Ask questions in plain English:
  - "Show me the average trip distance for Lyft on weekends."
  - "Compare total revenue for Uber and Lyft for each day of the week."  
  - "Which day of the week has the highest number of trips with more than 2 passengers?"

Fi interprets your questions, interacts with the defined cubes, and presents the answers in visualizations and tables.

### 4. Crafting and Sharing Data Stories through Dashboards:

- Definite's dashboards provide a canvas for weaving your data insights into compelling narratives.
- Combine charts and tables generated by Fi to present a holistic view of trends and patterns.
- Filter data, drill down into specifics, and customize the layout to suit your audience and message.
- Share dashboards with your team, fostering collaboration and data-driven decision making across your organization.

## Definite: Democratizing Data Analysis for the Modern Era

By eliminating technical barriers and providing an intuitive, AI-powered platform, Definite empowers everyone to become data-driven. From data analysts to business users, anyone can now explore, analyze, and extract valuable insights from their S3 data lakes. 

So if you're ready to transform your S3 data lake from a cluttered, disorganized mess into a well-indexed, easily navigable library, [Definite](https://www.definite.app/) is here for you. With [Definite](https://www.definite.app/), the answers you seek are always just a question away.


Definite: The AI-Powered Organizer for Your S3 Data

Definite: The Missing Card Catalog for Your S3 Data Library

  

The responses from LLMs (especially local / open source ones) are inconsistent—mostly mediocre but sometimes excellent. You often need to tweak and run the same prompt several times before getting something good. Can we automate the process of getting something good? 

https://res.cloudinary.com/dqlpcx9r8/image/upload/v1712669311/overkillm.webp

I went overkill on one simple task: write a good H1. The process is adaptable for tweets, emails or any other short blob of text. Here's the process:

1. **Input**: Choose authors (e.g., Stephen King).
2. **Direction**: Select topic or phrase.
3. **Generation**: Script creates variations in each author’s voice.
4. **Evaluation**: Variations compete to eliminate weak entries.
5. **Ranking**: Rank variations by wins and losses.

Runs locally (free) and only requires [ollama](https://github.com/ollama/ollama).

Note: The evaluation processes hundreds of rankings and may warm up your Mac.

Here's the code:

```python
OLLAMA_MODEL = 'knoopx/hermes-2-pro-mistral:7b-q8_0'
NUM_OF_VARIATIONS = 15
NUM_OF_MATCHES = 1000

import os
import random
import ollama
import uuid
import json

import pandas as pd

pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('display.max_colwidth', 2000)

authors = [
{
    "name": "Seth Godin",
    "description": "An author, entrepreneur, and marketer, Godin writes about marketing, the spread of ideas, and managing both customers and employees with respect."
},
{
    "name": "Paul Graham",
    "description": ""
},
{
    "name": "James Clear",
    "description": "Author of \"Atomic Habits,\" Clear writes about habits, decision-making, and continuous improvement."
},
{
    "name": "Derek Sivers",
    "description": "An entrepreneur, author, and musician, Sivers writes about creativity, life philosophy, and the lessons he's learned from founding and selling CD Baby."
},
{
    "name": "David Ogilvy",
    "description": "Often referred to as the Father of Advertising, Ogilvy was known for his emphasis on research and consumer insights. His work for brands like Rolls-Royce and Hathaway shirts has become legendary."
},
{
    "name": "Stephen King",
    "description": "A prolific author of horror, suspense, and fantasy novels, King has written over 60 books and is known for his detailed character development and storytelling."
},
]

def parse_numbered_responses(input_str, tone):
    if not input_str.strip():
        raise ValueError("Input string is empty.")
    
    lines = input_str.strip().split('\n')
    parsed_responses = []

    for line in lines:
        try:
            # Attempt to split each line at the first period followed by a space.
            number, text = line.split('. ', 1)
            number = int(number.strip())  # Convert number to integer.
            text = text.strip()  # Trim whitespace from text.
            generated_uuid = uuid.uuid4()
            parsed_responses.append({'number': number, 'text': text, 'tone': tone, 'uuid': generated_uuid})
        except ValueError:
            # Skip lines that do not conform to the expected format.
            continue

    if not parsed_responses:
        raise ValueError("No valid numbered responses found in the input.")

    return parsed_responses

def update_item_by_number(items, uuid, k):
    """Updates the text of an item in a list of dictionaries, identified by its number."""
    for item in items:
        if item['uuid'] == uuid:
            if not item.get(k):
                item[k] = 0
            item[k] = item[k] + 1
            return True
    return False

def get_one_tone(h1, context, tone, n):
    system_message = 'You are a helpful copywriting assistant. Only reply with a numbered list of variations of the text provided.'
    user_prompt = f'''Context: {context}.\nPlease generate {n} variations of the following text written in the voice of {tone}.''' 
    user_prompt += f'''Do not mention {tone} in the text:\n{h1}'''
    response = ollama.chat(model=OLLAMA_MODEL, 
        messages=[
            {
                'role': 'system',
                'content': system_message,
            },
            {
                'role': 'user',
                'content': user_prompt,
            },
        ],
        options = {
            'temperature': 1.5
        }
        )
    parsed_responses = parse_numbered_responses(response['message']['content'], tone)
    return parsed_responses

all_variations = []
n = NUM_OF_VARIATIONS
context = "I'm using this as the H1 for my website. Please write variations that are unique and engaging."
h1 = "Definite combines ETL, a data warehouse and BI in one modern platform."
for author in authors:
    print(f"Generating variations for {author['name']}...")
    tone = author['name']
    parsed_responses = get_one_tone(h1, context, tone, n)
    all_variations.extend(parsed_responses)

df = pd.DataFrame(all_variations)
print('Number of variations: ', len(df))

i = 0
while i < NUM_OF_MATCHES:
    print('i:', i)
    selected_items = random.sample(all_variations, 2)
    system_message = 'You are a helpful copywriting assistant. Only reply with "AAA" or "BBB". Do not include any other text or explaination.'
    user_prompt = f'''Please tell me which copy is more unique and engaging. Please reply in JSON format with the key "answer" and the value of your response. The only valid options for "answer" are "AAA" or "BBB". Do not include any other text or explaination.\n
    AAA: {selected_items[0]['text']}\n\n
    BBB: {selected_items[1]['text']}
    '''
    response = ollama.chat(model=OLLAMA_MODEL, 
        messages=[
            {
                'role': 'system',
                'content': system_message,
            },
            {
                'role': 'user',
                'content': user_prompt,
            },
        ],
        format='json',
        options = {
            'temperature': 0.0
        }
        )
    try:
        j = json.loads(response['message']['content'])
        if j['answer'] == 'AAA':
            update_item_by_number(all_variations, selected_items[0]['uuid'], 'wins')
            update_item_by_number(all_variations, selected_items[1]['uuid'], 'losses')
        elif j['answer'] == 'BBB':
            update_item_by_number(all_variations, selected_items[1]['uuid'], 'wins')
            update_item_by_number(all_variations, selected_items[0]['uuid'], 'losses')
        else:
            print('Invalid response:', j)
    except:
        print('Invalid response:', response)
        pass
    i += 1

df = pd.DataFrame(all_variations)
df['wins'] = df['wins'].fillna(0).astype(int)
df['losses'] = df['losses'].fillna(0).astype(int)
df['total'] = df['wins'] + df['losses']
df['win_rate'] = df['wins'] / df['total']
df = df.sort_values('win_rate', ascending=False)

winner = df.iloc[0]
print('Author win rates: ', df.groupby('tone').win_rate.mean())
print('Top 20: ', df.head(20))
print('Winner: ', winner.text)
```


OverkiLLM: Thousands of LLM calls using Ollama to write an H1


Yeah, that's a lot of things to query. I've never **needed** to query all five of these sources at once, but I did need 3. I needed to join our [Posthog](https://posthog.com/) logs (we export these to AWS as JSONL files) to our [Supabase](https://supabase.com/) data (we use Supabase as a Postgres database). I also wanted to join in some data from a Google Sheet.

https://res.cloudinary.com/definite/image/upload/v1712331160/duckdefblog_gbdf9e.png

## What's DuckDB?

DuckDB is a versatile, high-performance, in-memory analytical database that supports querying a wide range of data sources and formats. It's open-source and we use it as the data warehouse at [Definite](https://definite.app). A huge bonus with DuckDB is it's integrations and file support. It seamlessly interacts with various data sources, including PostgreSQL, Parquet, JSON, CSV, and even Google Sheets. Here's the query:

```sql
ATTACH 'postgres://your_username_password_host:5432/postgres' AS pgdb (TYPE postgres, READ_ONLY);
with interests as (
    select *
    from
    read_parquet('https://storage.googleapis.com/duck-demo-data/user_interests.parquet')
), user_preferences as (
    select *
    from read_json_auto(
        'https://storage.googleapis.com/duck-demo-data/user_preferences.jsonl',
        format = 'newline_delimited',
        records = true
        )
), user_details as (
    SELECT * FROM pgdb.user_details
), users as (
    select *
    from read_csv_auto('https://storage.googleapis.com/duck-demo-data/users.csv')
), one_more_thing as (
    SELECT *
    FROM read_csv_auto(
        'https://docs.google.com/spreadsheets/export?format=csv&id=1O-sbeSxCpzhzZj5iTRnOplZX-dIAiQJAeIO0mlh2kSU',
        normalize_names=True
        )
)
select
users.user_id,
users.name,
interests.interest,
user_preferences.theme,
user_preferences.language,
user_details.hobby,
one_more_thing.one_more_thing
from users left join
interests on users.user_id = interests.user_id left join
user_preferences on users.user_id = user_preferences.user_id left join
user_details on users.user_id = user_details.user_id left join
one_more_thing on users.user_id = one_more_thing.user_id

```

If you run this query on the DuckDB CLI, everything but Postgres will work because the GCS and Google Sheets URLs are public. Here's a version without Postgres:

```sql
with interests as (
    select *
    from
    read_parquet('https://storage.googleapis.com/duck-demo-data/user_interests.parquet')
), user_preferences as (
    select *
    from read_json_auto(
        'https://storage.googleapis.com/duck-demo-data/user_preferences.jsonl',
        format = 'newline_delimited',
        records = true
        )
), users as (
    select *
    from read_csv_auto('https://storage.googleapis.com/duck-demo-data/users.csv')
), one_more_thing as (
    SELECT *
    FROM read_csv_auto(
        'https://docs.google.com/spreadsheets/export?format=csv&id=1O-sbeSxCpzhzZj5iTRnOplZX-dIAiQJAeIO0mlh2kSU',
        normalize_names=True
        )
)
select
users.user_id,
users.name,
interests.interest,
user_preferences.theme,
user_preferences.language,
one_more_thing.one_more_thing
from users left join
interests on users.user_id = interests.user_id left join
user_preferences on users.user_id = user_preferences.user_id left join
one_more_thing on users.user_id = one_more_thing.user_id
```


Query any ducking thing: postgres, parquet, json, csv, and Google Sheets


> tl;dr - Yes, it's possible, but it's really slow (at least this way of doing it is).

## Snowflake as a vector store
Here's the steps to pull this off:
1. Add and "embeddings" column to your table in Snowflake with type `ARRAY`
2. Use the OpenAI API to generate embeddings for each row
3. Insert the embeddings into Snowflake
4. Query the embeddings using a Python UDF we'll call `vector_cosine_similarity_vectorized`



## Loading data to Snowflake
If you don't already have a dataset you want to use, [this](https://www.kaggle.com/datasets/tunguz/200000-jeopardy-questions/) one from Kaggle is good.

Create a table in Snowflake:

```sql
CREATE TABLE JEOPARDY_EMBEDDINGS (
    show_number INT,
    air_date STRING,
    round STRING,
    category STRING,
    value STRING,
    question STRING,
    answer STRING,
    embedding ARRAY
);
```

Get embeddings for each row using the OpenAI API (we're only embedding the question column, but you could easily mash the columns together):

```python
import requests
import json
import pandas as pd
import openai
openai.api_key = OPENAI_API_KEY

rows = jep_df.to_dict(orient='records')

for row in rows:
    response = openai.Embedding.create(
        model="text-embedding-ada-002",
        input=[row['question']],
    )

    row['embedding'] = response["data"][0]["embedding"]
```

Insert the embeddings into Snowflake:

```python
parquet_file = 'temp_dataframe.parquet'
load_to_snow.to_parquet(parquet_file, index=False)

conn = snowflake.connector.connect(
    account=account,
    user=user,
    password=password,
    database=database,
    schema=schema,
    warehouse=warehouse,
    role=role
)

cur = conn.cursor()

# Create or use an existing internal stage
stage_name = 'TEMP_INTERNAL_STAGE'
cur.execute(f"CREATE OR REPLACE STAGE {stage_name}")

# Upload the Parquet file to the stage
cur.execute(f"PUT file://{parquet_file} @{stage_name}")

# Bulk load the data from the stage to a Snowflake table
table_name = 'JEOPARDY_EMBEDDINGS'
cur.execute(f"COPY INTO {table_name} FROM @{stage_name} FILE_FORMAT=(TYPE='PARQUET')")

cur.close()
conn.close()
```

## Querying the embeddings

Create a Python UDF called `vector_cosine_similarity_vectorized`:

```sql
CREATE OR REPLACE FUNCTION vector_cosine_similarity_vectorized(A ARRAY, B ARRAY)
RETURNS FLOAT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
PACKAGES = ('pandas', 'numpy')
HANDLER = 'vector_cosine_similarity_vectorized'
AS $$
import pandas as pd
import numpy as np
from _snowflake import vectorized

@vectorized(input=pd.DataFrame)
def vector_cosine_similarity_vectorized(df):
    # Extract first and second columns
    A_series = df.iloc[:, 0].apply(np.array)
    B_series = df.iloc[:, 1].apply(np.array)

    # Compute cosine similarity for each pair of vectors
    dot_product = A_series.apply(lambda a: np.dot(a, B_series.loc[A_series.index[0]]))
    norm_A = A_series.apply(np.linalg.norm)
    norm_B = B_series.apply(np.linalg.norm)

    similarity = dot_product / (norm_A * norm_B)
    return similarity
$$;
```

Query the embeddings:

```python
query_sentence = '''what island had a really big monkey?'''

# create an embedding for the query sentence
response = openai.Embedding.create(
    model="text-embedding-ada-002",
    input=[query_sentence]
)
query_embedding = response["data"][0]["embedding"]

conn = snowflake.connector.connect(
    account=account,
    user=user,
    password=password,
    database=database,
    schema=schema,
    warehouse=warehouse,
    role=role
)

cur = conn.cursor()

search_vec = str(query_embedding)
search_vec = search_vec.replace('[', '').replace(']', '')

sql = f'''
SELECT 
    j.*,
    vector_cosine_similarity_vectorized(ARRAY_CONSTRUCT({search_vec}), j.EMBEDDING) as similarity
FROM 
    public.JEOPARDY_EMBEDDINGS as j
order by similarity desc
LIMIT 10;
'''
cur.execute(sql)

rows = cur.fetchall()
columns = [col[0] for col in cur.description]
df = pd.DataFrame(rows, columns=columns)
cur.close()
conn.close()
df.head()
```


Is it possible to use Snowflake as a vector store?


Can you reliably see where your sign ups are coming from? Can you segment your funnel analytics based on these acquisition sources?

These questions are key to understanding which growth channels you should scrap and which ones you should double down on.

In this post, we’ll go over a reliable way to track this data within the common scenario of where your application code (and sign up page) is within a different codebase than your marketing site (ie. Webflow, Wordpress, another code repository, etc).

## The Problem: Relying on Third-Party Tracking

The easiest way to start tracking this data is by using a web analytics tool like PostHog, Amplitude, or Google Analytics. I’m a fan of the first two.

However, the reliability of browser-based third-party tracking scripts are diminishing. When using these kinds of tracking scripts, you can expect to lose a decent amount of signups for users using an ad blocker or even browsers like Brave or Arc.

For many businesses, each sign up attribution data point is valuable. A couple of use cases I’ve experienced:

- Early stage startups need as much sign up data volume as possible when running A/B experiments.
- Companies doing referral tracking via affiliates need to make sure they get each point to drive payouts.

## The Solution: A Database Table and Some Javascript Snippets

To overcome the challenges associated with browser based tracking, implementing first party code to track visitor metadata before writing to a dedicated database table (`signup_analytics`) proves to be an effective solution.

### Here's how it works:

1. **Database Table**: Create a dedicated table in your database to record analytics data on those who’ve signed up. This table will tie each user to a signup analytics record, allowing for deeper analysis of activation rates based on acquisition referrer.

   ```sql
   create table signup_analytics (
       id serial primary key,
       user_id uuid references public.users(id) on delete set null,
       campaign text,
       campaign_date timestamp with time zone,
       source text,
       source_date timestamp with time zone,
       medium text,
       medium_date timestamp with time zone,
       term text,
       term_date timestamp with time zone,
       content text,
       content_date timestamp with time zone,
       referrer_url text,
       referrer_url_date timestamp with time zone,
       gclid text,
       gclid_date timestamp with time zone,
       created_at timestamp with time zone not null default now(),
       updated_at timestamp with time zone not null default now()
   );
   ```

2. **JavaScript Snippet for gathering visitor data:** Implement a JavaScript snippet that tracks the visitor’s traffic metadata (utm parameters, google click ids, referrer url, etc) in a cookie as users navigate from one subdomain to another. For example, when users move from your marketing site (_www.yourdomain.com_) to the application site (_app.yourdomain.com_), both sites can check the same cookie for the visitor traffic metadata. This ensures that the metadata remains intact throughout the signup process.

   - **NextJS Example**: If you’re using NextJS for your marketing site, instantiate this context on every page.

     ```jsx
     "use client";

     import {
       createContext,
       useContext,
       useMemo,
       useEffect,
       useState,
       useCallback,
     } from "react";
     import QueryString from "query-string";
     import Cookies from "js-cookie";
     import { SIGNUP_ANALYTICS_COOKIE_NAME } from "lib/config";

     const getCookieDomain = () => {
       if (typeof window !== "undefined") {
         let hostname = window.location.hostname;
         if (!hostname.includes("localhost")) {
           // Get root domain
           hostname =
             "." +
             hostname.split(".").reverse().splice(0, 2).reverse().join(".");
         }
         return hostname;
       }
     };

     const isExpired = (daysUntilExpired) => (date) => {
       const currentDate = new Date();
       const expirationDate = new Date(date);
       expirationDate.setDate(expirationDate.getDate() + daysUntilExpired);
       return currentDate > expirationDate;
     };

     export const AnalyticsContext = createContext();

     export const AnalyticsProvider = (props) => {
       const [campaign, setCampaign] = useState({ value: null, date: null });
       const [source, setSource] = useState({ value: null, date: null });
       const [medium, setMedium] = useState({ value: null, date: null });
       const [term, setTerm] = useState({ value: null, date: null });
       const [content, setContent] = useState({ value: null, date: null });
       const [gclid, setGclid] = useState({ value: null, date: null });
       const [referrerUrl, setReferrerUrl] = useState({
         value: null,
         date: null,
       });

       const initFromCookie = () => {
         const cookie = Cookies.get(SIGNUP_ANALYTICS_COOKIE_NAME);
         if (cookie) {
           const parsedCookie = JSON.parse(cookie);

           if (parsedCookie.campaign?.value) {
             setCampaign(parsedCookie.campaign);
           }

           if (parsedCookie.source?.value) {
             setSource(parsedCookie.source);
           }

           if (parsedCookie.medium?.value) {
             setMedium(parsedCookie.medium);
           }

           if (parsedCookie.term?.value) {
             setTerm(parsedCookie.term);
           }

           if (parsedCookie.content?.value) {
             setContent(parsedCookie.content);
           }

           if (parsedCookie.gclid?.value) {
             setGclid(parsedCookie.gclid);
           }

           if (parsedCookie.referrerUrl?.value) {
             setReferrerUrl(parsedCookie.referrerUrl);
           }
         }
       };

       const collectSignupAnalyticsFromUrl = () => {
         const currentDate = new Date();

         if (window.location?.search) {
           const qs = QueryString.parse(window.location.search);

           if (qs.utm_campaign !== null && qs.utm_campaign !== undefined) {
             setCampaign({ value: qs.utm_campaign, date: currentDate });
           }

           if (qs.utm_source !== null && qs.utm_source !== undefined) {
             setSource({ value: qs.utm_source, date: currentDate });
           }

           if (qs.utm_medium !== null && qs.utm_medium !== undefined) {
             setMedium({ value: qs.utm_medium, date: currentDate });
           }

           if (qs.utm_term !== null && qs.utm_term !== undefined) {
             setTerm({ value: qs.utm_term, date: currentDate });
           }

           if (qs.utm_content !== null && qs.utm_content !== undefined) {
             setContent({ value: qs.utm_content, date: currentDate });
           }

           if (qs.gclid) {
             setGclid({ value: qs.gclid, date: currentDate });
           }
         }

         let referrerUrl;
         if (document.referrer === "") {
           referrerUrl = undefined;
         } else {
           try {
             let parsedReferrer = new URL(document.referrer);
             referrerUrl =
               parsedReferrer.host.replace(/^www./, "") +
               parsedReferrer.pathname;
           } catch (e) {
             referrerUrl = document.referrer;
           }
         }

         if (referrerUrl) {
           setReferrerUrl({ value: referrerUrl, date: currentDate });
         }
       };

       const checkExpirations = useCallback(() => {
         // config expiration in days
         const isGclidExpired = isExpired(60);

         if (gclid.date && isGclidExpired(gclid.date)) {
           setGclid({ value: null, date: null });
         }
       }, [gclid.date]);

       const syncCookie = useCallback(() => {
         Cookies.set(
           SIGNUP_ANALYTICS_COOKIE_NAME,
           JSON.stringify({
             campaign,
             source,
             medium,
             term,
             content,
             gclid,
             referrerUrl,
           }),
           {
             domain: getCookieDomain(),
           }
         );
       }, [campaign, source, medium, term, content, gclid, referrerUrl]);

       const value = useMemo(
         () => ({
           campaign,
           setCampaign,
           source,
           setSource,
           medium,
           setMedium,
           term,
           setTerm,
           content,
           setContent,
           gclid,
           setGclid,
           referrerUrl,
           setReferrerUrl,
         }),
         [campaign, source, medium, term, content, gclid, referrerUrl]
       );

       // init from cross domain cookie
       useEffect(() => {
         initFromCookie();
         collectSignupAnalyticsFromUrl();
         checkExpirations();
       }, [checkExpirations]);

       useEffect(() => {
         syncCookie();
       }, [value, syncCookie]);

       return <AnalyticsContext.Provider value={value} {...props} />;
     };

     export const useAnalytics = () => {
       const context = useContext(AnalyticsContext);
       if (!context) {
         throw new Error(
           "useAnalytics must be used within a AnalyticsProvider"
         );
       }
       return context;
     };
     ```

3. **Saving cookie data to your database**: Upon signup, submit the captured cookie data to the signup analytics table. This step completes the tracking process and enables you to analyze user acquisition accurately. You’ll want to do this in the codebase that contains the sign up page.

   **Example using Supabase:** This function is called whenever the user signs up successfully.

   ```jsx
   import Cookies from "js-cookie";
   import { SIGNUP_ANALYTICS_COOKIE_NAME } from "@constants";
   import { supabase } from "@/supabase";

   export async function insertSignupAnalyticsFromCookie(userId: string) {
     let saObj = {};
     const cookie = Cookies.get(SIGNUP_ANALYTICS_COOKIE_NAME);
     if (cookie) {
       saObj = JSON.parse(cookie);
     }

     const { error } = await supabase.from("signup_analytics").insert([
       {
         user_id: userId,
         campaign: saObj?.campaign?.value,
         campaign_date: saObj?.campaign?.date,
         source: saObj?.source?.value,
         source_date: saObj?.source?.date,
         medium: saObj?.medium?.value,
         medium_date: saObj?.medium?.date,
         term: saObj?.term?.value,
         term_date: saObj?.term?.date,
         content: saObj?.content?.value,
         content_date: saObj?.content?.date,
         referrer_url: saObj?.referrerUrl?.value,
         referrer_url_date: saObj?.referrerUrl?.date,
         gclid: saObj?.gclid?.value,
         gclid_date: saObj?.gclid?.date,
       },
     ]);

     if (error) {
       console.error(error);
       return;
     }

     Cookies.remove(SIGNUP_ANALYTICS_COOKIE_NAME);
   }
   ```

4. **Analysis**: Once you have this data, you’re all set to start reporting on your top acquisition sources and where you should double down.

   **Example SQL query:**

   ```jsx
   SELECT
     t1.email AS email,
     t1.created_at,
     CASE
     WHEN t2.gclid IS NOT NULL THEN 'Google Ads'
     WHEN t2.source IS NOT NULL AND t2.source ILIKE 'facebook%' THEN 'Facebook Ads'
     WHEN t2.source IS NOT NULL AND t2.source ILIKE 'linkedin%' THEN 'LinkedIn Ads'
     WHEN t2.source IS NOT NULL AND t2.source ILIKE 'instagram%' THEN 'Instagram Ads'
     WHEN t2.source IS NOT NULL AND t2.source ILIKE 'twitter%' THEN 'Twitter Ads'
     WHEN t2.source IS NOT NULL THEN t2.source
     WHEN t2.referrer_url ILIKE 't.co%' THEN 'Twitter'
     WHEN t2.referrer_url ILIKE 'l.facebook.com%' THEN 'Facebook'
     WHEN t2.referrer_url ILIKE 'linkedin.com%' THEN 'LinkedIn'
     WHEN t2.referrer_url ILIKE 'instagram.com%' THEN 'Instagram'
     WHEN t2.referrer_url ILIKE 'pinterest.com%' THEN 'Pinterest'
     WHEN t2.referrer_url ILIKE 'tiktok.com%' THEN 'TikTok'
     WHEN t2.referrer_url ILIKE 'youtube.com%' THEN 'YouTube'
     WHEN t2.referrer_url ILIKE 'baidu.com%' THEN 'Baidu'
     WHEN t2.referrer_url ILIKE 'yahoo.com%' THEN 'Yahoo'
     WHEN t2.referrer_url ILIKE 'bing.com%' THEN 'Bing'
     WHEN t2.referrer_url IS NOT NULL AND
       SPLIT_PART(t2.referrer_url, '/', 1) IS NOT NULL
       THEN
       SPLIT_PART(t2.referrer_url, '/', 1)
     WHEN t2.referrer_url IS NOT NULL THEN 'Other Referrer'
     ELSE 'Unknown'
   END AS final_source,
     COALESCE(t2.gclid, t2.source, t2.referrer_url) AS final_source_detail,
     t2.campaign AS campaign,
     CAST(DATE_TRUNC('day', t2.campaign_date) AS DATE) AS campaign_date,
     t2.content AS content,
     CAST(DATE_TRUNC('day', t2.content_date) AS DATE) AS content_date,
     t2.gclid AS gclid,
     CAST(DATE_TRUNC('day', t2.gclid_date) AS DATE) AS gclid_date,
     t2.id AS id,
     t2.medium AS medium,
     CAST(DATE_TRUNC('day', t2.medium_date) AS DATE) AS medium_date,
     t2.referrer_url AS referrer_url,
     CAST(DATE_TRUNC('day', t2.referrer_url_date) AS DATE) AS referrer_url_date,
     t2.source AS source,
     CAST(DATE_TRUNC('day', t2.source_date) AS DATE) AS source_date,
     t2.term AS term,
     CAST(DATE_TRUNC('day', t2.term_date) AS DATE) AS term_date,
     t2.user_id AS user_id
   FROM "postgres"."public"."users" AS t1
   LEFT JOIN LATERAL (
     SELECT
       *
     FROM "postgres"."public"."signup_analytics" AS t2
     WHERE
       t1.id = t2.user_id
     ORDER BY
       t2.created_at DESC
     LIMIT 1
   ) AS t2
     ON TRUE
   ORDER BY
     t1.created_at DESC
   LIMIT 500
   ```

## Benefits of First Party Sign Up Attribution Tracking

By implementing this in your own system and database, you can enjoy several benefits:

- **Enhanced Analysis:** Having access to detailed signup referrer data allows for more accurate and precise analysis. An example is querying activation rates based on acquisition sources. What you find there would allow you to make data driven decisions on which growth channels you’d want to double down on.
- **Reliability:** Relying on your own signup analytics system ensures the reliability of the captured data. You can have confidence in the accuracy of your analytics and make informed decisions based on trustworthy insights.
- **Flexibility:** In the above implementation, you’re simply capturing raw data that represents traces of where the user came from. You’re still free to iterate on the logic of situations where a signup may have clicked a Google Ad and a partner’s affiliate link.

## Limitations

The solution above is meant to be a simple and reliable way to start tracking. It's essential to be aware of its limitations:

- **Multitouch Tracking:** The signup analytics table’s primarily focus on capturing the referrer data for the initial signup. If you require multitouch tracking to measure the impact of multiple touchpoints throughout the user journey, additional solutions/updates may be necessary.
- **Assumptions and Expirations:** The cookie tracker snippet relies on assumptions made ahead of time, such as `gclid` (Google Click Identifier) expirations. It's important to consider these assumptions and adjust them accordingly to ensure accurate data analysis.
- **Scope of analysis:** This table is only written to once the user signs up. This leads to pretty clean data. However, with just this table, you won’t be able to consider conversion rates from higher funnel steps.

## Other Solutions

In addition to storing signup referral data in your own database, another reliable way to get sign up referral tracking is by using a Product analytics tool (PostHog, Amplitude) with a proxy.

This means that the browser event will be sent to a proxy like a Cloudflare function hosted at _proxy.yourdomain.com_, then that Cloudflare function sends the event to your analytics tool.

Since the browser sees that you’re just talking to _yourdomain.com,_ it is more likely to bypass ad blockers.

See Segment’s guide on how they allow you to track data via a custom proxy: https://segment.com/docs/connections/sources/catalog/libraries/website/javascript/custom-proxy/

The fully first party solution described above will still be more reliable though. And it’s also easier for me to analyze it against other product metrics in the database. I often implement both.

## Key Takeaways

- Importance of Sign Up Attribution Analysis: Having access to detailed signup attribution data is critical and could unlock insights for growth opportunities.
- Reliability: Implementing your own signup analytics system ensures the reliability and accuracy of the captured data, providing trustworthy insights for decision-making.
- Flexibility: By capturing raw data representing user acquisition sources, you have the flexibility to iterate on the logic and adapt to different scenarios.
- Limitations: The solution focuses on capturing initial signup referrer data and may require additional solutions for multitouch tracking and adjusting assumptions and expirations.
- Other Solutions: Using a product analytics tool with a custom proxy can also provide reliable signup referral tracking, albeit with some trade-offs compared to a fully first-party solution.


Simple (and free) attribution tracking in 200 lines of JavaScript


> tl;dr - Definite is a better way to do analytics. It's like Figma for data with a healthy dose of magic (AI) sprinkled in. We’re opening up the product to more teams (sign up here).

## The Problem

I’ve been writing SQL for 17 years. It was the first programming language I learned. It felt good being one of the few people that can “pull data” on my team.

That feeling quickly wears off when you’re drowning in a flood of ad hoc data requests. Knowing SQL is a superpower, there's never enough people with the power to do all the data work your team needs.

There’s been a lot of action in the modern data stack. Tools like Fivetran have made writing custom ETL scripts a thing of the past. dbt has changed the way transformations are done. Snowflake went head to head with Google and Amazon and won.

But it still takes an inordinate amount of time to go from raw data in a source system to a final result people can use to do their jobs. Most people pull up a dashboard, can’t find the answer they’re looking for and Slack a “data person” to get it for them.

I see three core problems:

It takes way too long to set up analytics.

Once you set them up, there’s always “one more thing” needed from people that don’t know SQL.

Most analytics tools are bad at ad hoc analysis and exploration.

## Definite is the solution

You deserve a better way to leverage data and it’s definitely Definite:

Definite automates the most tedious parts of analytics with AI.

Anyone in your company can setup analytics workflows using natural language so your data engineers and data scientists can finally have time for deep work.

An ‘infinite canvas’ design space for your team to collaborate on data problems, perform ad hoc analysis and produce presentation-ready results.

It's like Figma was built for analytics and deeply understood your data warehouse.

**#1 Automation**

Connect to your warehouse and get the analytics work you need automatically generated for you with AI. How can we do this? We scan your database schema, query history and dbt models to gain a deep understanding or your data without you lifting a finger. We’re training our AI on commonly used datasets like Stripe, Salesforce, Hubspot and Shopify so we understand what the data means and can output meaningful results. We’re leveraging the countless hours of work you’ve poured into your data stack to make analytics a breeze.

You also shouldn’t **_need_** to visit a dashboard or set up Zapier to get live data from your data warehouse. Definite has Slack alerts built-in that you can toggle on with zero overhead.

**#2 ChatGPT for data**

Have you tried using ChatGPT to write SQL for you? It’s not bad. But you need to give it so much context that you’re probably better off just writing the SQL yourself. Don’t know SQL? You’re out of luck.

At Definite, we’ve become prompt whisperers. We know just what to tell to the AI to get it to do what you want. We’re using your teams query history and database schema to inform the AI of precisely what it needs to get work done. The data nerd in me is insanely excited to see where we can take this.

**#3 A drafting table for data**

Most software gives me claustrophobia. Notebooks, SQL IDE’s and BI tools are no exception. I always felt cramped when doing analytics work.

When I first used Figma, the first thing I thought was, “how the hell are they doing this in the browser?” The second thing was, “I wish this was connected to Postgres.”

Definites’ Canvas is like a drafting table for your data. See your schema, the results of that gnarly CTE, your final query and the visualization all in one surface. We use DuckDB to keep a temporary in-memory database of your current Canvas which lets you work across databases (e.g. joining Postgres data to Snowflake). You can also query parquet files on S3, CSVs or Google Sheets directly without importing them to your data warehouse.

# Get Definite

If your team wants to be data-driven, but you don’t have the time it takes to set up the analytics you need, let’s talk. I think you’ll love what we’re up to here. The team (Steven, Michael, Alex, Stefan, Kai, Jichao, Jess and Kevin) is incredible and shipping ridiculously fast.

Sign up below and find us on Twitter here.


Screaming into the void, hoping it's used by an LLM with citations

Recent Posts

Data doesn’t need to be so hard