Iceberg and Serverless DuckDB in AWS

Running Iceberg and Serverless DuckDB in AWS

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/local_duck_cloud_gcp_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/definite-app/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/definite-app/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/definite-app/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/definite-app/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/local_duck_cloud_gcp_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/definite-app/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/definite-app/image/upload/v1719447422/hackernews1_ilx181.png)

![image](https://res.cloudinary.com/definite-app/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/definite-app/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/definite-app/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/definite-app/image/upload/v1722525648/overkillm_psjwl9.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-app/image/upload/v1723059984/duckdefblog_iihdqg.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


# Top 5 Snowflake Alternatives for Cost-Effective Data Warehousing in 2023

Data is the lifeblood of modern business, but managing it can be costly. [Snowflake](https://www.snowflake.com/) leads the data warehousing market with its powerful features and widespread popularity. However, its premium pricing drives many companies to seek alternatives that offer similar capabilities at a more affordable cost. Let's explore five robust alternatives to Snowflake that could save you money while enhancing your cloud data platform and strategy.

## Why Consider Snowflake Alternatives?

While Snowflake dominates the market, it may not be the perfect fit for every organization. Here’s why you might consider other options:

1. **Cost**: Snowflake's expenses can escalate as your data scales.
2. **Feature overload**: You might be paying for features you don't need.
3. **Vendor lock-in**: Diversifying your cloud infrastructure can mitigate risks.
4. **Specific needs**: Other cloud data warehouse solutions may align better with your unique use cases.

## Key Features to Look for in Data Warehouse Alternatives

When evaluating alternatives, prioritize these key factors:

- **Scalability**: Will it grow with your business?
- **Performance**: How quickly can it process your data, including unstructured data?
- **Ease of use**: How easily can your team adapt to it?
- **Integration**: Does it work seamlessly with your existing tools and cloud platform?
- **Security**: How effectively does it protect your valuable data?
- **Pricing**: Is the pricing structure transparent and predictable?

## 5 Top Snowflake Alternatives for 2023

### 1. [Definite](https://www.definite.app/): The All-in-One Data Solution

Definite isn’t just another alternative; it’s a data revolution. Here’s why:

- ETL connectors to 500+ data sources
- Built-in data warehouse
- Integrated business intelligence
- Core metrics modeling layer
- AI assistant for instant business insights

Definite’s user experience is unmatched – it’s fast, intuitive, and powerful. You get everything you need in one beautifully designed data warehousing solution.

### 2. [AWS Redshift](https://aws.amazon.com/redshift/): Amazon's Data Powerhouse

[AWS Redshift](https://aws.amazon.com/redshift/) is a solid choice, especially for AWS users. Key features include:

- Massive scalability
- Machine learning integration
- Federated querying
- Strong performance for complex queries

**Pros**:
- Deep integration with Amazon’s cloud platform and services like [Amazon S3](https://aws.amazon.com/s3/)
- Familiar SQL interface

**Cons**:
- Complex optimization required
- Pricing can be unpredictable

### 3. [Google BigQuery](https://cloud.google.com/bigquery): Serverless and Scalable

[Google’s BigQuery](https://cloud.google.com/bigquery) offers a serverless architecture ideal for scalable data warehousing:

- Serverless design
- Real-time analytics
- Machine learning capabilities
- Multi-cloud support including Google Cloud

**Pros**:
- Highly scalable
- Pay-per-query pricing model

**Cons**:
- Complex queries can become costly
- Steeper learning curve

### 4. [Azure Synapse Analytics](https://azure.microsoft.com/en-us/products/synapse-analytics): Microsoft’s Enterprise Solution

[Microsoft Azure Synapse Analytics](https://azure.microsoft.com/en-us/products/synapse-analytics) merges data warehousing with big data analytics:

- Unified experience for data preparation, management, and serving
- Built-in Apache Spark
- Integration with Azure ML and Power BI

**Pros**:
- Strong integration with the Microsoft Azure cloud platform
- Powerful for large-scale analytics

**Cons**:
- Can be complex to set up and manage
- Pricing structure may be confusing

### 5. [PostgreSQL](https://www.postgresql.org/): The Open-Source Option

[PostgreSQL](https://www.postgresql.org/) isn’t just for transactional databases – it handles analytical workloads as well:

- Free and open-source
- Extensible with numerous add-ons
- Strong community support
- ACID compliance

**Pros**:
- No licensing costs
- Highly customizable for specific needs

**Cons**:
- Requires more hands-on management
- May need additional tools for ETL and visualization, turning it into a more complex data platform

## Comparing Snowflake Alternatives

Here’s how these cloud data warehouses stack up:

| Feature       | Definite   | AWS Redshift | Google BigQuery | Azure Synapse | PostgreSQL |
|---------------|------------|--------------|-----------------|---------------|------------|
| Ease of Use   | ★★★★★      | ★★★          | ★★★★            | ★★★           | ★★         |
| Scalability   | ★★★★★      | ★★★★★        | ★★★★★           | ★★★★★         | ★★★        |
| Built-in BI   | Yes        | No           | Limited         | Yes           | No         |
| AI Assistant  | Yes        | No           | Limited         | No            | No         |
| Pricing       | Competitive| Variable     | Pay-per-query   | Complex       | Free       |

## Why Definite Stands Out

Definite isn’t just another option – it’s a paradigm shift. Here’s why:

1. **All-in-one solution**: ETL, warehouse, BI, and AI in one package.
2. **Unmatched user experience**: Beautiful interface, lightning-fast performance.
3. **Built-in intelligence**: AI assistant helps you understand your data instantly.
4. **Cost-effective**: No need to piece together multiple tools.
5. **Scalability**: Grows with your business without added complexity.

Definite delivers enterprise-level power with consumer-app simplicity. It’s the future of data management, available today.

## Choosing the Right Snowflake Alternative

Selecting a data warehouse solution isn’t a one-size-fits-all decision. Each alternative has its strengths and weaknesses. Consider your specific needs:

- Are you deeply invested in a specific cloud platform?
- Do you require advanced analytics capabilities?
- How important is ease of use for your team?
- What’s your budget?

For many, Definite offers the best balance of power, simplicity, and cost-effectiveness. But your mileage may vary.

## The Bottom Line on Snowflake Alternatives

Snowflake is a powerful cloud data platform, but it’s not the only game in town. From Definite’s all-in-one brilliance to PostgreSQL’s open-source flexibility, there’s an alternative to suit every need and budget.

Consider your requirements carefully. Think about your growth plans. Assess your team’s expertise. And if you want the best of all worlds – power, simplicity, and value – give Definite a try.

Ready to revolutionize your data strategy? [Take Definite for a spin today](https://ui.definite.app/signup) with a free trial. Your data (and your budget) will thank you.


Snowflake Alternatives: 5 Cost-Effective Data Warehouse Solutions

Top 5 Snowflake Alternatives for Cost-Effective Data Warehousing in 2023


# 9 Cost-Effective Fivetran Alternatives for Streamlined Data Integration

In today's data-driven world, efficient ETL (Extract, Transform, Load) processes are crucial. [Fivetran](https://www.fivetran.com/), a popular ETL platform, has served many businesses well. However, its high cost and certain limitations have prompted many to explore the best Fivetran alternatives. Here’s a look at nine alternatives to Fivetran that could revolutionize your data integration strategy—without breaking the bank.

## Why Look Beyond Fivetran?

Fivetran excels at simplifying data integrations, much like a Swiss Army knife for data movement. However, there's a catch: it's expensive. For startups and SMEs, the cost can be prohibitive.

Additionally, while Fivetran’s connector library is extensive, it’s not limitless. If you’re working with niche data sources or require custom connectors, you might find its options lacking. These constraints have fueled the search for the best Fivetran alternative that offers comparable functionality at a more accessible price point.

## 9 Powerful Fivetran Alternatives

### 1. [Definite](https://www.definite.app/): The Game-Changing All-in-One Data Platform

Definite isn't just another ETL tool—it's a complete data integration platform. Imagine combining your ETL, data warehouse, and business intelligence tools into one seamless platform. That’s Definite.

**Key features:**
- 500+ data connectors for comprehensive integration
- Built-in data warehouse, eliminating the need for separate solutions
- Advanced data transformation capabilities
- AI-powered assistant for data-driven insights
- Blazing-fast performance and an intuitive user interface

Definite’s standout feature? A generous free tier and startup program that democratizes advanced data analytics. You don’t need a Fortune 500 budget to harness enterprise-level data capabilities.

### 2. [Airbyte](https://airbyte.com/): Open-Source Flexibility

[Airbyte](https://airbyte.com/) is making waves with its open-source approach to ETL. It's one of the best open-source alternatives to Fivetran, offering:
- 170+ connectors
- Self-hosting option for complete control
- Community-driven development for rapid improvements
- Schema evolution support

Airbyte shines for teams with the engineering resources to customize their data workflows.

### 3. [Stitch](https://www.stitchdata.com/): User-Friendly Data Pipeline Management

[Stitch](https://www.stitchdata.com/), under Talend’s umbrella, focuses on accessibility:
- 130+ connectors
- Singer protocol integration
- Intuitive interface for easy management

Starting at $100/month, Stitch offers a more budget-friendly entry point than Fivetran, making it a strong Fivetran competitor.

### 4. [Hevo](https://hevodata.com/): No-Code Simplicity

[Hevo Data](https://hevodata.com/) champions simplicity with its no-code approach:
- 150+ connectors
- Real-time data migration capabilities
- Support for ETL, ELT, and Reverse ETL
- Advanced database replication features

At $239/month, Hevo Data balances functionality and affordability, making it one of the best-suited options for teams seeking ease of use.

### 5. [Matillion](https://www.matillion.com/matillion-etl): Enterprise-Grade Power

[Matillion](https://www.matillion.com/matillion-etl) caters to larger organizations with complex needs:
- 110+ connectors
- Advanced data transformation tools
- Both cloud and on-premises solutions

While powerful, Matillion requires a steeper learning curve, which may demand more engineering resources.

### 6. [Rivery](https://rivery.io/): Innovative Cloud-Based ELT

[Rivery](https://rivery.io/) introduces a unique "rivers" concept for data scripts:
- 200+ connectors and 15+ destinations
- 24/7 customer support
- Flexible credit-based pricing

Rivery offers a fresh perspective on data integration, but its complex pricing model might require a custom pricing discussion.

### 7. [Meltano](https://meltano.com/): For the Code-First Data Team

Born from GitLab, [Meltano](https://meltano.com/) appeals to engineering-driven organizations:
- 300+ connectors
- Singer protocol integration
- Robust version control and CI/CD testing

Meltano is ideal for teams preferring a hands-on, code-centric approach, making it a strong open-source alternative to Fivetran.

### 8. [CData](https://www.cdata.com/solutions/etl/): Universal Connectivity

[CData](https://www.cdata.com/solutions/etl/) provides a comprehensive suite of data integration tools:
- 250+ connectors
- SQL-focused approach
- Cloud and on-premises flexibility
- Supports change data capture and schema evolution

Starting at $149/month, CData offers an affordable option for SQL-proficient teams.

### 9. [Segment](https://segment.com/product/reverse-etl): Customer Data Powerhouse

While focused on customer data, [Segment](https://segment.com/product/reverse-etl) boasts robust ETL capabilities:
- 300+ connectors
- Specializes in marketing and sales data
- Advanced customer data management features, including Google Analytics integration

Segment excels for businesses prioritizing customer analytics, making it one of the best Fivetran alternatives for this use case.

## Choosing Your Fivetran Alternative: Key Considerations

When evaluating these alternatives, consider:

1. **Cost efficiency:** How does pricing scale with your data volume?
2. **User-friendliness:** Can your team easily adopt and use the tool?
3. **Connector availability:** Does it support all your crucial data sources, including custom connectors?
4. **Scalability:** Will it grow alongside your business?
5. **Support and maintenance:** What level of assistance can you expect?
6. **Change Data Capture and Schema Evolution:** How well does it handle new data structures and schema changes?

These factors determine not just the initial fit but the long-term viability of your data integration process.

## Definite: The Standout Among Alternatives

While each alternative offers unique strengths, [Definite](https://www.definite.app/) emerges as a game-changer. Here’s why:

1. **True All-in-One Solution:** Definite isn’t just ETL—it’s your entire data stack in one platform. This integration eliminates tool sprawl and simplifies your data architecture.

2. **Built-in Data Warehouse:** Unlike other solutions requiring separate data warehouse setup, Definite includes one. This integration saves time, money, and complexity.

3. **Unmatched Accessibility:** Definite’s generous free tier and startup program democratize advanced data capabilities. It’s enterprise-level power accessible to businesses of all sizes.

4. **Intuitive Yet Powerful Interface:** Definite combines ease of use with robust functionality. This balance means less time training and more time deriving valuable insights.

5. **AI-Driven Insights:** The built-in AI assistant transforms raw data into actionable business intelligence, setting Definite apart from traditional ETL tools.

## Conclusion: Revolutionize Your Data Integration

The data integration landscape is evolving rapidly, and Fivetran is no longer the only viable option. Each alternative we've explored offers unique advantages, and the best choice depends on your specific needs and constraints.

However, for businesses seeking a comprehensive, user-friendly, and scalable solution, Definite stands out as a compelling choice. Its all-in-one approach, combined with a generous free tier, makes it an attractive option for companies of all sizes looking to unlock the full potential of their data. 

Remember, effective data integration isn’t just about moving data—it’s about transforming that data into insights that drive your business forward. Choose the tool that not only meets your current needs but also empowers your future growth.

See what Definite can do for you—[try it now!](https://ui.definite.app/signup)


Top Fivetran Alternatives: Cost-Effective ETL Solutions for 2023

9 Cost-Effective Fivetran Alternatives for Streamlined Data Integration


> 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.


Announcing Definite: 10x faster analytics


# The Data Analytics Landscape in 2024

In today’s data-driven world, organizations are increasingly relying on sophisticated data analytics tools to gain a competitive edge. Whether it's improving decision-making, optimizing business processes, or predicting future trends, the right analytics tool can be a game changer. 

With so many options on the market, however, selecting the right data analytics tool can be daunting. Whether you are a startup looking for intuitive and budget-friendly solutions or an enterprises requiring advanced, scalable platforms, there is a diverse range of analytics tools available.

This year, the focus is not only on powerful analytics capabilities but also on accessibility, integration, and user experience. Companies are seeking tools that can offer deep insights while being easy to use, adaptable to various data sources, and capable of delivering results in real time.

In 2024, we see emerging trends driving the popularity of certain tools over others. The growing importance of self-service analytics is a major trend, empowering non-technical users to explore data, create reports, and derive insights without relying heavily on data science teams. Cloud-native analytics platforms, designed to handle vast amounts of data with scalability and flexibility, are also in high demand.

Here, we’ll explore some of the best data analytics tools to consider in 2024\. Dive in to discover which of these solutions can elevate your data strategy and drive your organization toward data-driven success this year and beyond.

## **Comparison of Features, Pricing, and Usability**

When comparing the leading data analytics tools, certain elements stand out.

**Features to look for:**

* **Data Integration:** How well does it fetch and process data from various sources?  
* **Visualization:** Are the visual tools robust and easy to use?  
* **AI & ML Capabilities:** Does it support advanced analytics with AI and ML?

**Pricing:**

* **Free Tiers:** Some tools offer limited free versions.  
* **Subscription Plans:** Monthly or yearly, based on users and features.  
* **Custom Pricing:** For enterprise solutions.

**Usability:**

* **User Interface:** Simple and intuitive?  
* **Learning Curve:** How steep is it?  
* **Support:** Comprehensive help and resources available?

These factors significantly influence the choice of the right tool.

## **Definite: Robust Analytics Platform**

[Visit Definite](https://www.definite.app/)

Definite makes data analytics feel like a casual conversation. Users input commands in plain English, and Definite translates these into powerful analytics operations. It’s perfect for those who aren’t too tech-savvy, allowing you to collect, store, analyze, and act on your data in one app. Even better? It’s one of the most cost-effective options on the market, offering enterprise level analytics at a startup price.

### **Key Features**

* **Natural Language:** Allows users to type queries as they speak.  
* **Real-Time Insights:** Provides instant feedback and analytics results.  
* **User-Friendly Interface:** Clean design and intuitive controls.  
* **Customizable Dashboards:** Tailor analytics views to fit unique needs.

"Show me the sales growth for Q1 2024" is all it takes to get a detailed report.

## **Tableau: User-Friendly Visualizations and Advanced Analytics**

[Visit Tableau](https://www.tableau.com/)

Tableau stands out for its user-friendly interface and powerful analytics capabilities.

### **Key Features**

* **Drag-and-Drop Interface:** Tableau allows users to create stunning visualizations easily.  
* **Advanced Analytics:** Provides in-depth functionalities like trend analysis, clustering, and forecasting.  
* **Real-Time Data:** Users can connect to live data sources and visualize changes in real-time.  
* **Integration:** Seamlessly integrates with various data sources, including SQL databases and cloud services.  
* **Community and Resources:** Boasts an active community and extensive learning resources.

Tableau is perfect for users who need powerful analytics without a steep learning curve. Its visual drag-and-drop interface makes data exploration intuitive and fun.

## **Power BI: Seamless Integration with Microsoft Ecosystem**

[Visit Power BI](https://www.microsoft.com/en-us/power-platform/products/power-bi)

Power BI offers impeccable integration within the Microsoft ecosystem. It connects fluently with popular Microsoft tools, making data handling a breeze.

### **Key Features**

* **Microsoft Excel Compatibility**: Easily imports data from Excel, preserving intricate data formats.  
* **Azure Connection**: Directly pulls data from Azure, enabling real-time analysis.  
* **Teams Integration**: Embeds interactive reports into Microsoft Teams, streamlining collaboration.

The seamless connectivity ensures easy sharing through OneDrive and embedding in Microsoft PowerPoint. With built-in AI capabilities, reports are more insightful. This integration boosts productivity within familiar Microsoft environments, making Power BI a top choice.

## **Google Data Studio: Entry-level and Free**

Google Data Studio offers a powerful, user-friendly approach to data visualization. Users can create interactive reports tailored to unique business needs. 

**Key Features**

* **Free to Use:** No cost ever, full features included.  
* **Easy Integration:** Seamlessly connects with Google Analytics, BigQuery, and other data sources.  
* **Collaborative:** Shareable with team members for real-time collaboration.  
* **Customizable:** Wide range of templates and design options.  
* **Interactive Elements:** Filters, date ranges, and dynamic options for deeper insights.

"The simplest tool for complex data visualization." Ideal for businesses of all sizes, Google Data Studio stands out as a robust and cost-effective solution.

## **Python and R: The Flexibility of Programming Languages**

Python and R shine in data analytics due to their flexibility. They each offer unique features for analysts.

**Key Features**

* **Python**:  
  * User-friendly syntax, widely recommended for newbies.  
  * Extensive libraries like Pandas, NumPy, and SciPy.  
  * Versatile for tasks beyond data analytics.  
* **R**:  
  * Built specifically for statistical analysis.  
  * Rich ecosystem of packages like ggplot2 and dplyr.

"Python is often hailed for its readability, while R's strength lies in its statistical prowess." Both languages support integration with big data tools like Apache Spark.

## **Looker: Google Cloud's Comprehensive Tool**

[Visit Looker](https://lookerstudio.google.com/navigation/reporting)

Looker is Google Cloud's powerhouse analytical tool designed for modern data environments. It's built for scalability and integration with Google's ecosystem. 

**Key Features**

* **Data Modeling:** Simplifies complex SQL queries with its LookML modeling language.  
* **Visualization:** Delivers interactive dashboards and robust visualization options.  
* **Real-Time Analysis:** Allows users to gain insights from up-to-date data.  
* **Integration:** Seamlessly integrates with Google products like BigQuery.  
* **Collaboration:** Encourages teamwork with shared reports and dashboards.  
* **Flexibility:** Supports various data sources, making it a versatile choice.

Looker empowers teams to make data-driven decisions effortlessly, all within Google Cloud's secure environment.

## **Qlik Sense: Associative Data Indexing**

[Visit Qlik Sense](https://www.qlik.com/us/products/qlik-sense)

Qlik Sense uses associative data indexing to empower users to see the whole story in their data.

**Key Features**

* Explore data without being limited to query-based constraints.  
* Instantly see relationships and inconsistencies between data points.  
* Easily drill down and pivot through different variables.

Users experience fewer roadblocks with associative indexing. They can quickly shift perspectives and uncover insights that might be hidden with traditional linear data querying. This function makes the user experience more intuitive, boosting productivity and data comprehension across various industries.

## **Apache Spark: Speed and Scalability for Big Data**

[Visit Apache Spark](https://spark.apache.org/)

Apache Spark offers unparalleled speed and reliability for big data analytics.

### **Key Features**

* **In-Memory Processing**: Allows blazing-fast computations by keeping data in memory.  
* **Rich APIs**: Supports various languages, including Scala, Java, Python, and R.  
* **Unified Engine**: Handles a variety of workloads such as batch, streaming, and machine learning.  
* **Scalability**: Can easily scale from a single server to thousands of machines.  
* **Community Support**: A constantly growing community of developers.  
* **Integrated Libraries**: Includes MLlib, GraphX, and Spark SQL for simplified data processing.

"Apache Spark: When speed and scalability are a necessity\!"

## **SAS: Comprehensive Enterprise Solutions**

SAS offers robust analytics for big businesses. Think top-notch data management, detailed reports, and advanced analytics. Industries ranging from healthcare to finance trust SAS for making data-driven decisions.

### **Key Features**

* **Data Management**: Efficient handling and cleaning of vast datasets.  
* **Advanced Analytics**: Predictive modeling, machine learning, and AI capabilities.  
* **Reporting**: Detailed and tailored reports to suit various business needs.  
* **Integration**: Seamless integration with existing enterprise systems.

Users rave about SAS’s ability to handle large-scale analytics effortlessly. Its scalability and versatility make it a preferred choice for enterprises aiming to leverage data for strategic advantage.

## **Alteryx: Powerful Data Blending and Advanced Analytics**

[Visit Alteryx](https://www.alteryx.com/es/)

Alteryx is a go-to tool for those looking to perform data blending and advanced analytics with ease. It's perfect for users who have minimal coding knowledge but need powerful analytics capabilities.

### **Key Features**

* **Drag-and-Drop Interface**: Simplifies data preparation and analytics.  
* **Data Blending**: Combines data from disparate sources seamlessly.  
* **Predictive Analytics**: Leverages built-in predictive modeling tools.  
* **Spatial Analytics**: Offers advanced capabilities for geographic data.  
* **Custom Workflows**: Build and share analytics workflows without much hassle.  
* **Connectivity**: Integrates with various data sources, making it versatile.

Alteryx shines in making complex processes straightforward for users.

## **RapidMiner: Streamlined Machine Learning Pipelines**

RapidMiner stands out with its user-friendly interface and powerful tools.

### **Key Features**

* **Drag-and-Drop Functionality:** Simplifies the creation of complex workflows.  
* **Pre-Built Models:** Offers a wide range of templates to kickstart any project.  
* **Data Prep:** Integrates robust data cleaning and transformation features.  
* **Automated ML:** Speeds up model development and fine-tuning.  
* **Collaborative Workspaces:** Enables teams to work together and share insights easily.  
* **Integration:** Seamlessly connects with various data sources and big data platforms.  
* **Visualization:** Provides clear visualizations to help understand data and model performance.

RapidMiner ensures that even non-coders can efficiently build and deploy machine learning models.

## **Future Trends in Data Analytics Tools**

Predictive analytics will become more advanced, enabling businesses to foresee market trends with greater accuracy.

* **AI Integration**: Expect increased use of artificial intelligence for real-time data processing and decision-making.  
* **Augmented Analytics**: Non-experts will harness sophisticated analytics through guided tools.  
* **Edge Analytics**: Data processed at the edge, close to the source, will enhance speed and efficiency.  
* **Data Privacy**: Stricter data privacy regulations will push for more robust compliance features.

"The future of data analytics lies in intelligent, intuitive tools accessible to all."

The horizon looks brighter with these advancements coming.

## **Conclusion: Choosing the Right Tool for Your Needs**

Selecting the right data analytics tool boils down to understanding specific needs and goals.

* **Budget:** Free tools like Google Analytics are great for cost-saving, while premium tools like Tableau offer robust features.  
* **Ease of Use:** For those new to data analytics, user-friendly interfaces in Power BI can be a big plus.  
* **Integration:** Consider tools like Apache Spark for seamless integration with other software.

Remember, the best tool is the one that meets your team's unique requirements and fits seamlessly into your workflow.  However, if you’re searching for an all-in-one solution to collect, store, analyze, and act on your data without the hassle of DIY, [Definite](https://www.definite.app/) is a choice you can trust.

Request a [free demo](https://meetings.hubspot.com/mikeritchie/hubspot-bookings) or [start your 14-day trial here](https://ui.definite.app/signup).


Best Data Analytics Tools in 2024 | Top Business Solutions

The Best Data Analytics Tools to Work With in 2024: Top Solutions Reviewed

# 9 Best Alternatives to Tableau in 2024

Startups need good tools to understand their data. Tableau is popular, but it's not always the best choice. Let's look at nine Tableau alternatives that might work better for your startup in 2024.

## Why Look Beyond Tableau?

Startups often seek Tableau alternatives (or Tableau Competitors) because:

1. Tableau (especially Tableau Server) can be expensive
2. It can be hard to learn and use
3. It has more features than many startups need
4. It doesn't always play nice with other tools startups use

Now, let's explore some options:

## 1. Definite: Your All-in-One Data Platform

**Best for:** Companies that need a complete data platform without hiring experts.

Definite is changing how startups handle data. It's an all-in-one package that includes:

- Connectors for 500+ sources
- A place to store all your data
- Tools to make dashboards and reports
- An AI assistant to answer questions about your data

What's special about Definite is how it connects everything. You can quickly link data from different places, like your sales and marketing tools. This means startups can do complex data tasks without a big team or budget.

## 2. Power BI: Microsoft's Data Tool

**Best for:** Startups already using Microsoft products.

Power BI is Microsoft's answer to Tableau. It works well with other Microsoft tools and can be cheaper than Tableau.

**Pros:**

- Fits in with Microsoft tools
- Familiar look for Microsoft users
- Often updated with new features

**Cons:**

- Might be tricky if you don't use Microsoft
- Some advanced features need coding skills

## 3. Looker: For Teams Who Love SQL

**Best for:** Startups with team members who know SQL and want to customize their data setup.

Looker is now part of Google Cloud. It uses SQL to work with data.

**Pros:**

- Great for making custom data models
- Keeps track of changes to your data setups
- Can be built into other products

**Cons:**

- Needs SQL knowledge to use well
- Can be complex to set up

## 4. Metabase: Free and Open Source

**Best for:** Startups on a tight budget who are okay with community support.

Metabase is a free, open-source data tool with basic features.

**Pros:**

- Free to use
- Easy to understand
- Works with and without SQL

**Cons:**

- Fewer advanced features than paid tools
- Support comes from other users, not a company

## 5. Domo: Cloud-Based Data Tool

**Best for:** Fast-growing startups that need a big data platform.

Domo is a cloud-based tool that focuses on real-time data and mobile access.

**Pros:**

- Connects to many data sources
- Works well on mobile devices
- Can handle lots of data as you grow

**Cons:**

- Can be expensive for small teams
- Might have more features than new startups need

## 6. Sisense: For Building Data Products

**Best for:** Startups creating products that need to show data to customers.

Sisense helps companies put data visuals into their own products.

**Pros:**

- Good for adding data visuals to your products
- Handles big, complex data well
- Flexible setup options

**Cons:**

- Can be hard to set up and use
- Might be pricey for small teams

## 7. Google Data Studio: Free Data Visuals

**Best for:** Startups with no budget for data tools, especially if they use Google products.

Google Data Studio is free and helps create data dashboards and reports.

**Pros:**

- Free to use
- Works well with Google products
- Easy for beginners

**Cons:**

- Limited compared to paid tools
- Can be slow with lots of data

## 8. Sigma: Spreadsheet-Style Data Tool

**Best for:** Startups with teams who love spreadsheets but need more powerful data analysis.

Sigma looks like a spreadsheet but connects to powerful data storage systems.

**Pros:**

- Feels like using a spreadsheet
- Connects to cloud data storage
- Good for team data exploration

**Cons:**

- Needs cloud data storage
- Might feel limited to some data experts

## 9. MicroStrategy: Big Business Data Tool

**Best for:** Startups expecting to grow into big companies with complex data needs.

MicroStrategy is a powerful data tool designed for big businesses.

**Pros:**

- Strong security features
- Can handle very large amounts of data
- Good mobile options

**Cons:**

- Can be overwhelming for small teams
- Expensive and takes time to set up

## Honorable Mentions: More Tableau Alternatives

While we've covered nine top Tableau alternatives in detail, there are many other data visualization tools worth considering. Here's a quick look at some other options:

1. **Zoho Analytics**: Easy-to-use tool with a focus on creating and sharing reports.

2. **Redash**: Open-source tool that's great for connecting to various data sources.

3. **QlikView**: Powerful tool for creating interactive data visualizations.

4. **Tableau Public**: Free version of Tableau for creating and sharing public visualizations.

5. **GoodData**: Offers embedded analytics and data monetization features.

6. **Birst**: Known for its networked analytics approach.

7. **Spotfire**: Strong in predictive analytics and statistical analysis.

8. **Pentaho**: Open-source business intelligence suite with data integration capabilities.

9. **Chartio**: Cloud-based tool that turns data into charts with a simple interface.

10. **Board**: All-in-one decision-making platform combining BI, analytics, and performance management.

11. **Geckoboard**: Focuses on creating KPI dashboards for TV displays.

12. **Klipfolio**: Offers real-time dashboards with a wide range of data source integrations.

These tools offer various features and pricing models, catering to different business needs and sizes. When choosing a Tableau alternative, consider factors like your team's technical skills, the types of data you work with, and your specific visualization needs.

## Choosing the Right Tool

![Tools](https://res.cloudinary.com/definite-app/image/upload/v1724874490/chart2-tab-alt_azg3k8.webp)

Picking a data tool is important for your startup. You need something that:

- Gives quick insights
- Fits your budget
- Can grow with your company

Definite stands out for startups because it offers a complete data solution in one tool. This solves many data problems that new companies face.

But every startup is different. Think about:

- Your team's skills
- How much data you have
- How fast you're growing
- Your budget

The right tool will help you understand your data without wasting time or money. It should let you create useful dashboards and visualizations easily, whether you're working with small or large datasets.

9 Best Tableau Alternatives in 2024

9 Best Alternatives to Tableau in 2024

# Databricks vs Snowflake: Choosing Your Data Powerhouse in 2024

In today’s data-driven world, choosing the right **data powerhouse** can make or break your data strategy. Databricks and Snowflake lead the pack, but are they the only contenders? Let’s explore how they stack up and whether they meet your team’s needs.

## The Heavyweights: Databricks and Snowflake

[Snowflake](https://www.snowflake.com/) revolutionized cloud data warehousing when it launched in 2012. Its standout feature? The separation of storage and compute, offering unmatched flexibility for **data management** and scalability. Snowflake’s architecture is built for **powerhouse data centers** handling vast amounts of **time data** and real-time analytics.

[Databricks](https://databricks.com/), launched in 2013 by the creators of Apache Spark, started as a managed Spark service and evolved into a comprehensive “lakehouse” platform. By merging the best features of data lakes and data warehouses, Databricks has become a **data powerhouse** for **data engineering**, **data analysis**, and **data science** workflows.

## Feature Showdown

### Data Storage and Processing

Snowflake’s architecture shines in the world of SQL, making it a top choice for structured and semi-structured data. Its **data integration** capabilities make loading, processing, and querying **time data** and other datasets fast and efficient. For **data governance**, Snowflake also offers robust security and compliance features, allowing businesses to manage data responsibly.

Databricks, on the other hand, casts a wider net. It supports a broad range of data types, from structured to unstructured data, and provides **data teams** with tools like Delta Lake to ensure **data governance** with ACID transactions. Databricks is built for **data engineering**, making it the go-to platform for **data teams** that need to process massive datasets and perform complex transformations.

### Analytics and Business Intelligence

Snowflake’s SQL-first approach integrates seamlessly with most **data analytics** tools, making it an excellent option for **data teams** focused on **data analysis**. Its ability to handle **time data** and structured datasets means **data management** tasks like querying, visualizing, and sharing data are a breeze.

Databricks, while SQL-capable, extends beyond it by offering support for Python, R, and Scala, empowering **data teams** to explore **data analysis** and advanced analytics workflows in a more flexible environment. With its built-in support for **data engineering**, **data analysis**, and **data science**, Databricks offers a multi-language environment that gives **data teams** greater versatility.

### Data Science and Machine Learning

Snowflake’s relatively new Snowpark allows **data teams** to work with Python, but its machine learning features are still in development. For now, Snowflake is better suited for SQL-heavy workflows in **data analytics** rather than full-scale machine learning pipelines.

Databricks, in contrast, is a playground for **data engineering** and **data science**. With native support for machine learning frameworks, **data teams** can manage the entire ML lifecycle with ease. Whether it’s **data integration**, building models, or deploying them in production, Databricks has the tools to support every stage.

### Performance and Scalability

Both platforms handle massive datasets with ease.

Snowflake’s strength lies in its ability to scale compute resources instantly—perfect for **powerhouse data centers** that need to handle fluctuating workloads and massive data streams. Whether it’s handling large amounts of **time data** or complex queries, Snowflake offers rapid scaling on demand.

Databricks, on the other hand, allows **data teams** more control over resource allocation, enabling fine-tuning to maximize performance for specific **data engineering** and analytics workloads.

### Pricing

Snowflake keeps things simple with a pay-as-you-go model. Storage and compute are billed separately, and you’re charged by the second for compute usage, making it easier for **data management** teams to control costs.

Databricks’ pricing is more complex, involving charges for both the platform and the cloud infrastructure it runs on. However, for teams dealing with heavy **data engineering** workloads, the added control can lead to cost savings in the long run.

## The Pros and Cons

### Snowflake

**Pros**:
- Extremely user-friendly for SQL-based **data teams**
- Top-tier performance for **data analysis** and BI
- Effortless **data integration** and sharing

**Cons**:
- Limited support for unstructured data
- Still maturing in ML and **data engineering**
- Costs can increase significantly at scale

### Databricks

**Pros**:
- Handles diverse data types and workloads
- Superior support for **data science**, ML, and **data engineering**
- Built on open-source tech (Apache Spark, Delta Lake)

**Cons**:
- Steeper learning curve for beginner **data teams**
- Complex pricing structure
- SQL features not as polished as Snowflake

## The Challenger: Definite

While Databricks and Snowflake dominate, [Definite](definite.app) is redefining what a **data powerhouse** should be. It’s not just another player—it’s rewriting the rules of **data integration** and **data management**.

Definite consolidates your entire data stack into one sleek platform:

1. **ETL connectors** to over 500 data sources
2. **A built-in data warehouse** for instant analysis
3. **Business intelligence tools** integrated natively for rapid **data analysis**
4. **Core metrics modeling** for fast, actionable insights
5. **AI assistant** to answer your business questions in real time

### Why Definite?

- **All-in-one solution**: No need to juggle multiple tools or add a separate data warehouse.
- **Slick user experience**: Simplifies complex **data management** tasks with blazing fast performance.
- **Affordable pricing**: Our free tier and startup program offer unbeatable value.

## Making the Right Choice

Choosing between these platforms isn’t just about ticking off features; it’s about aligning with your **data team’s** skills, business needs, and budget.

- For SQL-heavy **data teams** needing a robust data warehouse and smooth **data integration**, **Snowflake** could be the best fit.
- If your **data team** is focused on **data engineering** and advanced data science workflows, **Databricks** could be ideal.
- But if you want an all-in-one solution with simplicity, speed, and cost-effectiveness, give **Definite** a serious look. It’s the Swiss Army knife of **data platforms**, designed for modern **data teams** looking for power without the complexity.

## Final Thoughts

In 2024, the data platform landscape is richer than ever. Snowflake and Databricks are **data powerhouses** in their own right, but they aren’t the only options.

[Definite](definite.app) is shaking up the market by offering a powerful, all-in-one solution that simplifies **data management** and delivers unparalleled performance. For **data teams** looking to streamline their stack and get more done with less, Definite is worth a serious look.

The right platform is the one that empowers your **data team**, fits your budget, and scales with your business needs. Don’t just follow the crowd—choose the platform that will drive your success forward. Ready to see the difference? Start using [Definite now](https://ui.definite.app/signup)!


Databricks vs Snowflake: Which Data Powerhouse is Right for You in 2024?

Databricks vs Snowflake: Choosing Your Data Powerhouse in 2024


# 6 Powerful Databricks Alternatives for Data Lakes and Lakehouses

## Introduction

[Databricks](https://www.databricks.com/) has established itself as a leader in the data lake and lakehouse space, offering a powerful platform for big data processing and analytics. However, as the data landscape evolves, many organizations are seeking alternatives that offers simplicity, cost-effectiveness, and a tailored approach to their specific data lake needs.

In this comprehensive guide, we'll explore six **Databricks alternatives** for data lake and lakehouse solutions, with a special focus on [**Definite**](https://www.definite.app/)—an innovative all-in-one platform that's changing the game. Whether you're a growing company looking to streamline your data operations or an enterprise aiming to optimize costs, this post will help you navigate the rich landscape of **Azure Databricks** alternatives for data lakes and lakehouses.

## Why Look Beyond Databricks for Your Data Lake?

Before we dive into the alternatives, it's crucial to understand why you might be considering a switch from **Databricks** or **Azure Databricks** for your data lake architecture. Here are some common challenges that drive teams to explore **Databricks alternatives**:

1. **Cost Concerns**: Databricks’ pricing model can be a significant burden, especially for businesses with variable data processing needs.

2. **Complexity**: Managing **big data analytics** with Databricks often requires specialized expertise, making it difficult for smaller teams or those new to **data science** and large-scale **data processing tasks**.

3. **Overkill for Simpler Use Cases**: Databricks excels at handling advanced **machine learning models** and **scale data processing**, but it can be overkill for organizations with more straightforward **business intelligence**, **data visualization**, or **cloud data warehouse** needs.

4. **Data Integration Challenges**: Many teams find integrating diverse data sources into a Databricks-based **cloud data platform** cumbersome, especially when using **Azure SQL Data Warehouse**, **Amazon Web Services (AWS)**, or other external systems.

5. **Limited Flexibility**: Adapting Databricks to specific **data engineers'** workflows or integrating with specialized **data processing frameworks** like Hadoop or Apache Spark can be limiting.

If these pain points resonate with your experience, you're in the right place. Let's explore your options, starting with a game-changing alternative.

## The Game-Changer: Definite

[**Definite**]((https://www.definite.app/)) is rapidly emerging as a frontrunner among **Databricks alternatives** for data lakes and lakehouses, offering a comprehensive, user-friendly approach to data management. Here's why it's making waves in the industry:

### Key Features of Definite for Data Lakes:

1. **Unified Data Platform**: Definite provides a complete solution for building and managing data lakes, eliminating the need for multiple tools such as separate **data processing frameworks**.

2. **Extensive Data Connectors**: With over 500 pre-built connectors, Definite supports a **wide range** of data sources, making data ingestion into your lake—from **Azure SQL Data Warehouse**, databases, or **Amazon Web Services (AWS)**—seamless.

3. **Built-in Data Processing**: Definite includes built-in processing capabilities that rival other **Databricks competitors**, allowing teams to handle complex **data processing tasks** without external frameworks like Apache Spark.

4. **Integrated Analytics**: Generate reports and dashboards directly from your data lake using Definite’s built-in **business intelligence** tools, eliminating the need for separate **data visualization tools**.

5. **Data Governance and Security**: Robust governance features, including data lineage, access control, and encryption, ensure your data is secure and compliant with industry standards.

6. **AI-Powered Data Discovery**: Leverage AI to explore your data lake with natural language queries, empowering non-technical users to unlock **advanced analytics**.

7. **Schema Evolution and Data Quality**: Automatically handle schema changes and enforce data quality rules, ensuring smooth data operations as your lake grows.

### Definite vs. Databricks for Data Lakes: A Comparison

To better understand how Definite compares to Databricks for data lake and **big data analytics** solutions, let’s break down their key features:

| Feature | Definite | Databricks |
| --- | --- | --- |
| All-in-One Data Lake Solution | Yes | Partial |
| Separate Data Processing Framework Required | No | Yes (Apache Spark) |
| Free Tier | Generous | Limited |
| Startup Program | Yes | No |
| Data Source Connectors | 500+ | Limited |
| AI-Assisted Exploration | Yes | Limited |
| SQL Interface | Yes | Yes |
| User Experience | Intuitive | Complex |
| Setup Time for Data Lake | Quick | Lengthy |
| Built-in Analytics | Yes | Limited |
| Business Intelligence Tools | Yes | Limited |
| Data Governance | Comprehensive | Basic |
| Multi-Cloud Support | Yes | Limited |

Definite’s approach simplifies **cloud data platform** architecture, making it accessible to a wider range of users while still providing the flexibility and power needed for complex **scale data processing** and **machine learning** operations.

## 5 Other Databricks Alternatives for Data Lakes

While Definite offers a compelling all-in-one solution, there are other **Databricks competitors** worth considering, depending on your specific needs:

1. [**Google BigQuery**](https://cloud.google.com/bigquery/)

- Ideal for: Large-scale, SQL-based analytics on data lakes
- Key benefit: Serverless architecture with automatic scaling for **time data processing**
- Integration: Excellent with other Google Cloud services and **data processing frameworks**

2. [**Snowflake**](https://snowflake.com/)

- Ideal for: Building modern data lakes with a cloud-native architecture
- Key benefit: Separation of storage and compute with instant elasticity, making it a strong **cloud data warehouse** solution
- Best for: Enterprises looking for flexible, multi-cloud data lake solutions

3. [**Apache Spark (Self-Managed)**](https://spark.apache.org/)

- Ideal for: Teams seeking ultimate flexibility in their data lake architecture
- Key benefit: Open-source foundation with a rich ecosystem of tools for **data engineers** and **machine learning models**
- Best for: Organizations with strong in-house data engineering and **data science** capabilities

4. [**Amazon EMR (Elastic MapReduce)**](https://aws.amazon.com/emr/)

- Ideal for: AWS-centric data lake processing
- Key benefit: Deep integration with **Amazon Web Services (AWS)**, including S3 for data lakes
- Best for: Companies already invested in the AWS ecosystem

5. [**Google Cloud Dataproc**](https://cloud.google.com/dataproc)

- Ideal for: Building data lakes with Hadoop and Spark on Google Cloud
- Key benefit: Fully-managed environment with fast cluster creation for **data processing tasks**
- Best for: Organizations looking to migrate on-premises data lakes to the cloud

## Why Definite Stands Out for Data Lakes

After evaluating these **Databricks competitors**, it’s clear that Definite offers unique value for modern data lakes:

1. **Unified Data Lake Platform**: Combining **data ingestion**, processing, storage, and analytics into a single package simplifies managing a **cloud data warehouse**.

2. **SQL Interface**: Definite’s **SQL interface** ensures ease of use for teams already comfortable with SQL-based queries and reporting, making it a great fit for **data engineers** and **data science** teams alike.

3. **Rapid Implementation**: With pre-built connectors and an intuitive interface, teams can set up data lakes and derive insights from **big data analytics** in hours, not weeks.

4. **Cost-Effective**: Definite’s pricing structure, including a generous free tier and startup program, makes it a highly accessible option for **data engineers** managing data at scale.

5. **User-Friendly Data Exploration**: Definite’s sleek UI and AI assistant make **data visualization** and exploration accessible to a broader range of users, driving adoption of **business intelligence** across the organization.

## Conclusion: The Future of Data Lakes and Lakehouses

While Databricks remains a powerful solution for data lakes and lakehouses, **Databricks alternatives** like Definite are proving that power and flexibility don’t have to come at the expense of simplicity or cost-effectiveness.

Whether you're a growing company looking to build a robust data lake or an enterprise optimizing your **cloud data platform**, exploring these **Databricks competitors** could unlock new efficiencies. Platforms like Definite are democratizing access to advanced **data processing frameworks**, **data visualization tools**, and **machine learning models**, helping organizations scale their data operations with ease.

Ready to take the next step? Explore [Definite](https://ui.definite.app/signup) and other alternatives to see how they can transform your approach to data lakes and lakehouses. The future of your data strategy awaits!


6 Databricks Competitors: Best Alternatives for Data Lakes and Lakehouses

6 Powerful Databricks Alternatives for Data Lakes and Lakehouses


# **What is a Data Platform? - Use Cases and Platforms for 2024**

### **What is a Data Platform?**

In 2024, data platforms are no longer a luxury but a necessity for businesses of all sizes. As startups and enterprises alike seek to capitalize on data for innovation and growth, a modern data platform can serve as the foundation for success.

Data platforms have become indispensable in today's digital landscape, particularly for startups that aim to leverage data for business growth and innovation. Think of a data platform as the operating system for all your business's data—one that gathers, organizes, and transforms raw data into insights that fuel decisions and strategies. Designed to collect, store, and manage vast amounts of data from various sources, a data platform is a centralized solution that integrates a range of technologies to support the data lifecycle.

Whether it's a **cloud data platform**, an **enterprise data platform**, or a **data management platform**, these solutions serve several critical functions central to managing and utilizing data effectively:

1. **Data Integration**:

    - Ensures seamless acquisition of data from various sources.

    - Supports batch and real-time data processing.

    - Normalizes disparate data formats, including **relational databases** and **unstructured data**.

2. **Data Storage**:

    - Utilizes a combination of databases, **data lakes**, and **data warehouses**.

    - Provides a secure, scalable **data storage layer** for diverse data types.

3. **Data Processing**:

    - Applies data transformation, enrichment, and integration techniques.

    - Leverages technologies like ETL (Extract, Transform, Load) and ELT (Extract, Load, Transform).

4. **Data Analysis**:

    - Supports a wide range of **data analytics platforms** to derive actionable insights.

    - Provides tools for **business intelligence** (BI), data visualization, and machine learning.

At its core, the right data platform is designed to help businesses derive value from their data, supporting growth, innovation, and strategic decision-making.

### **Key Components of a Data Platform**

A robust data platform, whether a **big data platform**, **data analytics platform**, or **customer data platform**, consists of various integral components. These components work together to ensure efficient data management, storage, processing, and analysis:

- **Data Sources**:

    - Originates from applications, IoT devices, social media, and more.

- **Data Lake**:

    - Stores raw data in its native format, including both structured and unstructured data.

- **Data Warehouse**:

    - Houses processed and structured data for complex queries.

- **Data Pipelines**:

    - Manages the flow of data between sources, storage, and processing units.

- **Security Features**:

    - Includes encryption, user authentication, and access controls to protect sensitive data.

- **APIs and Connectors**:

    - APIs allow data platforms to interact with other systems.

    - Connectors enable smooth data transfer across various platforms and services.

- **Analytics Tools**:

    - Platforms like [Hadoop](https://hadoop.apache.org/), [Spark](https://spark.apache.org/), and BI tools such as [Tableau](https://www.tableau.com/) and [Microsoft Power BI](https://www.microsoft.com/en-us/power-platform/products/power-bi) are part of the analytics stack in a modern data platform.

### **Why Data Platforms Matter for Startups**

Startups, in particular, can significantly benefit from **cloud data platforms** or **data analytics platforms** because they enable data-driven decision-making, strategic planning, and operational efficiency based on accurate customer insights. Here are some key reasons why data platforms are essential for startups:

#### **Scalability and Flexibility**

Startups often experience rapid growth, requiring agile systems that can scale with their needs. Modern **enterprise data platforms** offer:

- **Elastic storage** that expands as data volumes increase, whether structured or unstructured.

- **Flexible computing resources** to handle varying workloads.

- **Seamless integration** with other tools and services for enhanced functionality.

#### **Cost Efficiency**

Budget constraints are common for startups. **Cloud platforms** and **data clouds** provide:

- **Reduced infrastructure costs** by utilizing cloud-based solutions.

- **Minimized maintenance expenses** as vendors handle updates and troubleshooting.

- **Optimized resource usage** with pay-as-you-go models, ensuring startups only pay for what they use.

#### **Enhanced Decision-Making**

Access to accurate, real-time data is pivotal for making strategic business decisions. **Data analytics platforms** provide:

- **Comprehensive analytics tools** to process and interpret data.

- **Real-time dashboards** for immediate insights.

- **Predictive analytics capabilities** to forecast trends and behaviors.

#### **Data Governance and Security**

Protecting sensitive information is essential, and modern **data platform architectures** come equipped with:

- **Robust security protocols** to safeguard data.

- **Compliance features** to adhere to industry regulations.

- **Access controls** to ensure only authorized personnel can access critical data.

#### **Collaboration and Accessibility**

Data platforms, including **customer data platforms** and **big data platforms**, facilitate better teamwork and information sharing through:

- **Unified data repositories** that store all organizational data in one place.

- **Cloud-based access** that allows team members to collaborate from anywhere.

- **User-friendly interfaces** to simplify data manipulation and analysis.

#### **Speed to Market**

Startups must be agile to outpace competitors, and **cloud data platforms** support this by:

- **Accelerating development cycles** with faster access to data.

- **Automating routine tasks**, freeing up resources for innovation.

- **Providing insights** that help in rapidly adjusting strategies.

#### **Competitive Advantage**

Having the right data platform can differentiate a startup. Whether using a **cloud data platform**, **data analytics platform**, or **big data platform**, key advantages include:

- **Better customer insights** leading to improved products and services.

- **Data-driven marketing strategies** that boost customer engagement.

- **Performance metrics** that guide continuous improvement efforts.

### **Data Ingestion: Collecting Raw Data**

Data ingestion is the first step in building a **data platform architecture**, involving the collection of raw data from various sources. This process ensures startups capture all relevant data points for analysis:

- **Data Sources**: APIs, databases, flat files, IoT devices, social media.

- **Ingestion Methods**: Batch processing, real-time streaming.

- **Tools**: [Apache Kafka](https://kafka.apache.org/), [AWS Glue](https://aws.amazon.com/glue/), [Talend](https://www.talend.com/).

- **Challenges**:

    - **Data Quality**: Ensuring data is clean and free from errors.

    - **Scalability**: Handling large data volumes efficiently.

    - **Consistency**: Maintaining uniform data formats and structures.

Effective data ingestion forms the foundation for meaningful analytics and insights.

### **Data Visualization: Turning Data into Actionable Insights**

Data visualization is crucial in making complex data easily understandable. By using visual elements like charts, graphs, and maps, startups can quickly interpret vast amounts of information. Efficient data visualization helps:

- Identify trends and patterns.

- Highlight outliers and anomalies.

- Facilitate quicker decision-making.

- Improve communication of insights.

Tools such as [Tableau](https://www.tableau.com/) and [Microsoft Power BI](https://www.microsoft.com/en-us/power-platform/products/power-bi) are integral to a modern **data analytics platform**, helping startups turn raw data into actionable insights that drive strategic decisions.

### **Security and Compliance in Data Platforms**

Security and compliance are critical for any **data platform architecture**, especially for startups that need to establish trust. A **data cloud** or **enterprise data platform** must consider:

#### **Key Security Measures**

- **Data Encryption**: Encryption ensures data is unreadable without the proper key. Platforms often employ AES-256 encryption for security.

- **Access Control**: Role-based access control (RBAC) and multi-factor authentication (MFA) limit who can access sensitive data.

- **Audit Logs**: Detailed logs help track suspicious activities and ensure compliance.

#### **Compliance Standards**

- **GDPR**: Data protection and privacy for European Union individuals.

- **HIPAA**: Data security and privacy for medical information in the United States.

- **ISO/IEC 27001**: Information security management system (ISMS) requirements.

- **CCPA**: Data privacy rights for California residents.

#### **Best Practices**

- **Regular Security Audits**: Conduct frequent audits to identify potential weaknesses.

- **Data Minimization**: Only collect necessary data to reduce breach impact.

- **Incident Response Plan**: Have a plan ready to address breaches quickly.

- **Employee Training**: Regular training on data security practices and compliance requirements.

### **Scalability: Growing with Your Startup**

Scalability in **big data platforms** ensures that startups can grow without sacrificing performance or increasing costs. Effective **cloud data platforms** should accommodate hundreds to thousands of additional users, transactions, and datasets:

- **Elasticity**: Automatic resource adjustment for usage demands.

- **Performance**: Optimized query processing for faster insights.

- **Data Partitioning**: Balancing loads by distributing data across servers.

- **Horizontal and Vertical Scaling**: Expanding capacity by adding more servers or enhancing existing ones.

- **Cost-Efficiency**: Pricing models that align with startup budgets.

- **Reliability**: High availability and fault tolerance for continuous operation.

### **Integration with Other Tools and Systems**

Modern **data platforms** integrate with a variety of tools and systems, allowing startups to adapt to an evolving landscape. Examples include:

- **CRM**: Syncing data with tools like [Salesforce](https://www.salesforce.com/) to improve customer insights.

- **Marketing Automation**: Connecting with platforms like [HubSpot](https://www.hubspot.com/) for targeted campaigns.

- **BI Tools**: Integrating with [Tableau](https://www.tableau.com/) for advanced analytics.

- **Product Management**: Connecting with tools like [Jira](https://www.atlassian.com/software/jira) for streamlined development.

- **Financial Systems**: Linking with accounting software like [QuickBooks](https://quickbooks.intuit.com/) for real-time financial tracking.

### **Best Practices for Implementing a Data Platform**

When implementing a data platform, best practices can enhance functionality and scalability:

1. **Define Clear Objectives**:

    - Set specific goals aligned with business outcomes.

2. **Incorporate Scalability**:

    - Design the platform to handle future growth.

    - Leverage **cloud data platforms** for flexible scaling.

3. **Ensure Data Quality**:

    - Implement validation and cleaning processes.

    - Use consistent data formats.

4. **Focus on Security**:

    - Encrypt sensitive data.

    - Enforce strong access controls.

5. **Leverage Automation**:

    - Automate data ingestion and processing.

    - Use automated monitoring for system performance.

6. **Promote Collaboration**:

    - Involve cross-functional teams in the design phase.

    - Use tools that facilitate data sharing.

## **Conclusion and Key Takeaways**

Understanding and implementing data platforms provide significant advantages for startups striving to gain a competitive edge in today's data-driven world. If you're considering which data platform to adopt, remember these aspects:

- **Data Integration**: Combining various data sources into a single platform.

- **Scalability**: Handling increasing data needs as the startup grows.

- **Security**: Ensuring data protection through robust security measures.

- **Cost Efficiency**: Reducing operational costs with cloud-based solutions.

- **Real-Time Analytics**: Facilitating informed decision-making with live data.

- **User Accessibility**: Providing easy access to data for all team members.

Investing in a robust data platform is essential for startups aspiring to harness the power of their data fully. It equips them with the necessary tools to grow, innovate, and stay ahead in a competitive market. Interested in exploring one today? Get in touch with the Definite team [here](https://www.definite.app/contact).


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

Recent Posts

Data doesn’t need to be so hard