<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Daniel Lewis on Medium]]></title>
        <description><![CDATA[Stories by Daniel Lewis on Medium]]></description>
        <link>https://medium.com/@daniel.lewis_79552?source=rss-df95d2ea6ed0------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*_G4VlcdERZ63VfEw</url>
            <title>Stories by Daniel Lewis on Medium</title>
            <link>https://medium.com/@daniel.lewis_79552?source=rss-df95d2ea6ed0------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Tue, 17 Nov 2026 14:40:44 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@daniel.lewis_79552/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[DuckLake on Apache Superset]]></title>
            <link>https://medium.com/data-analytics-at-nesta/ducklake-on-apache-superset-d1b1b00c222a?source=rss-df95d2ea6ed0------2</link>
            <guid isPermaLink="false">https://medium.com/p/d1b1b00c222a</guid>
            <category><![CDATA[duckdb]]></category>
            <category><![CDATA[superset]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Daniel Lewis]]></dc:creator>
            <pubDate>Wed, 09 Jul 2025 06:56:59 GMT</pubDate>
            <atom:updated>2025-07-09T06:56:59.459Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ySVeafXfgobUjLKgmkvBLw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@kayla_gibson?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Kayla Gibson</a> on <a href="https://unsplash.com/photos/two-mallard-ducks-on-body-of-water-K-l5Q3ifrNc?utm_content=creditCopyText&amp;utm_medium=referral&amp;utm_source=unsplash">Unsplash</a></figcaption></figure><p><em>After reading this article you’ll understand the basics of DuckLake and how to use it with Apache Superset to connect to parquet files stored on s3.</em></p><h3>Making Better Use of Data</h3><p>At <a href="https://www.nesta.org.uk/">Nesta</a>, we work with a range of data, from small human-readable spreadsheets (like <a href="https://www.gov.uk/government/collections/heat-pump-deployment-statistics">DESNZ Heat Pump Deployment Statistics</a>) to large machine-readable extracts (like <a href="https://epc.opendatacommunities.org/">Energy Performance Certificates</a>). Team members tend to work with the spreadsheets individually, while our data scientists manage our larger data using a typical workflow: python (polars), scaling (metaflow), and cloud storage (parquet on aws s3).</p><p>However, lately it felt like we weren’t using data as effectively as we could be and there was value to be gained by:</p><ul><li>Curating key data sources to provide an up-to-date, quick to access, single source of truth on our key domains.</li><li>Enabling team members to directly access meaningful summaries of larger data assets that they previously couldn’t work with directly.</li><li>Embedding data and data discovery as a more prominent and fulfilling activity for our teams.</li></ul><p>We felt that a business information tool that enabled data discovery, visualisation and dashboarding could offer these benefits.</p><h3>Our Starting Point</h3><p>From the outset, we knew we wanted to continue to work in s3. Separating storage and compute has worked really well for our data science teams, it’s a familiar and nearly ubiquitous way of working that we do effectively.</p><p>The challenge we faced was connecting to s3 from a business information tool. Generally, tools like <a href="https://www.metabase.com/">metabase</a> or <a href="https://superset.apache.org/">superset</a> like to work with database tables. We needed a connector that could read an object, like a parquet file, in s3 and expose it as a table. We tried a couple of options:</p><ul><li><strong>DuckDB</strong> — vanilla DuckDB is a really effective <a href="https://duckdb.org/docs/stable/guides/network_cloud_storage/s3_import.html">connector to parquet in s3</a>. However, it’s limited in a context like Superset as it doesn’t easily support multiple processes. This locks you into administering your data through Superset, which is not ideal.</li><li><strong>Hive Metastore and Trino</strong> — we experimented with using a <a href="https://github.com/nestauk/asf_data_connector_docker/tree/docker_setup">standalone Hive Metastore and query engine</a> (we used Trino) to connect. While this worked, it was difficult to set up and felt oversized for our use case. We weren’t confident that we understood it sufficiently to maintain it effectively.</li><li>Other approaches — we considered <strong>Iceberg</strong>,<strong> Delta Lake</strong>, and<strong> </strong><a href="https://projectnessie.org/"><strong>Nessie</strong></a>, but they all felt much too involved for our use case; we needed a simple data connector, not a fully featured data lake.</li></ul><p>And then we heard about <a href="https://ducklake.select/">DuckLake</a>.</p><h3>DuckLakes are Data Lakes without the Complexity</h3><p>DuckLake is an open, standalone way of running a data warehouse that’s refreshingly simple to use. It brings together three components:</p><ul><li>Storage for parquet files — we’re using S3 buckets on AWS, but <a href="https://ducklake.select/docs/stable/duckdb/usage/choosing_storage">others are possible</a>.</li><li>A database system to act as a data catalog — we’re using postgresql, but <a href="https://ducklake.select/docs/stable/duckdb/usage/choosing_a_catalog_database">you’re not limited to that</a>.</li><li>A query engine — we’re using DuckDB.</li></ul><p>This structure is very much like the standalone Hive Metastore approach we’d already tried. DuckLake has the benefits of multiple processes reading and writing the same data, without the challenge of complicated setup and configuration.</p><h3>Your First Duck Lake</h3><p>The architecture of our development instance of Ducklake on Superset is shown below (Figure 1). The data catalog is running on the same EC2 instance as the dockerized Superset instance for convenience. The multi-player nature of Ducklake means that an admin user can connect to the data catalog to manage the tables in the Ducklake independent of the Superset instance.</p><figure><img alt="An architecture diagram that shows an EC2 instance containing a dockerised superset instance connecting to an S3 bucket using DuckLake." src="https://cdn-images-1.medium.com/max/1024/1*PYXwYzWXTirCKMJy4ZE56Q.png" /><figcaption>Figure 1: An example development architecture for Superset and DuckLake on an AWS EC2 instance.</figcaption></figure><p>Let’s walk through an example of setting up a ducklake. Our examples have been run on AWS EC2 and Windows Subsystem for Linux (WSL2) both using ubuntu.</p><p>For clarity, let’s assume that we’re working with a data catalog called ‘<em>ducklake_catalog</em>’, a DuckLake that we’ll refer to as ‘<em>my_ducklake</em>’, an S3 bucket called ‘<em>my-bucket</em>’, a parquet file called ‘<em>my_parquet.pq</em>’ and a table called ‘<em>my_table</em>’.</p><p><strong>Create a Data Catalog</strong></p><p>We’ve used postgresql for this, which is easy to set up:</p><pre># update package list<br>sudo apt update<br># Install postgres<br>sudo apt install postgresql</pre><p>Once postgres is up, you’ll need to add a password to the postgres user and create a database for ducklake to use.</p><pre># access the postgresql psql prompt<br>sudo -u postgres psql</pre><p>Then, in the psql prompt:</p><pre>-- Set password for user: postgres<br>ALTER USER postgres with password &#39;postgres&#39;;<br>-- Create a database<br>CREATE DATABASE ducklake_catalog;<br>-- Grant some permissions (may not be required with postgres user).<br>GRANT ALL PRIVILEGES ON DATABASE ducklake_catalog TO postgres;</pre><p>This creates a database called <em>ducklake_catalog</em>, which will store the relevant metadata, and makes sure we can access that database using the default postgres user account. This is something you could change in future, but is the simplest approach for now.</p><p><strong>Setting up a Duck Lake</strong></p><p>Now we’ve got our postgres data catalog established, we can use duckdb as a query engine. First, install duckdb:</p><pre>curl https://install.duckdb.org | sh</pre><p>With duckdb installed, we can open a duckdb prompt:</p><pre># In-memory duckdb prompt<br>duckdb</pre><p>And create a DuckLake:</p><pre>INSTALL httpfs;<br>INSTALL postgres;<br>INSTALL ducklake;<br><br>LOAD httpfs;<br>LOAD postgres;<br>LOAD ducklake;<br><br>-- Create a secret with s3 credentials<br>CREATE PERSISTENT SECRET (<br>    TYPE S3,<br>    KEY_ID &#39;&#39;,<br>    SECRET &#39;&#39;,<br>    REGION &#39;&#39;<br>);<br><br>-- Now attach to a ducklake<br>ATTACH &#39;ducklake:postgres:dbname=ducklake_catalog host=localhost user=postgres password=postgres&#39; as my_ducklake (DATA_PATH &#39;s3://my-bucket/&#39;);<br><br>-- use the ducklake<br>USE my_ducklake;</pre><p>There are a few things going on here, let’s break it down:</p><ul><li><em>INSTALL/LOAD</em> — duckdb uses this syntax to <a href="https://duckdb.org/docs/stable/extensions/overview.html">explicitly install and load</a> relevant extensions. Installed extensions are stored locally, so you only need to do this once.</li><li><em>CREATE PERSISTENT SECRET</em> ¹— duckdb needs some <a href="https://duckdb.org/docs/stable/configuration/secrets_manager">credentials</a> to access the s3 bucket that stores our parquet files. By creating a ‘persistent’ secret you only have to enter it once and it persists across duckdb sessions. By default, this will write the secret (unencrypted) to the local ~/.duckdb/stored_secrets directory.</li><li><em>ATTACH</em> — This statement creates the connection to a ducklake. Note that it does not persist between duckdb sessions — more on this later.</li><li><em>USE</em> — simply tells duckdb to use the attached <em>my_ducklake</em>, without it you have to supply the database when identifying tables, e.g. <em>my_ducklake.my_table</em>, rather than just <em>my_table</em>.</li></ul><p><strong>The ATTACH statement in detail</strong></p><p>The <a href="https://ducklake.select/docs/stable/duckdb/usage/connecting">attach statement</a> is the same whether you’re connecting to an existing ducklake or creating a new one. A duck lake is automatically created if one doesn’t exist.</p><p>The first part of the statement connects to the data catalog, the standard is:</p><pre>&#39;ducklake:{metadata_storage_location}&#39;</pre><p>In our example, we’ve operationalised <em>{metadata_storage_location}</em> as:</p><pre>&#39;ducklake:postgres:dbname=ducklake_catalog host=localhost user=postgres password=postgres&#39;</pre><ul><li><em>postgres </em>is the data catalog we’re using.</li><li><em>dbname=ducklake_catalog</em> tells duckdb that we’re using the database we created previously called <em>ducklake_catalog</em>.</li><li><em>host=localhost</em> tells duckdb where to find our data catalog, in this case on localhost.</li><li><em>user=postgres password=postgres</em> tells duckdb how to access <em>ducklake_catalog</em>.</li></ul><p>For convenience, we then use as <em>my_ducklake</em> to provide a memorable alias for the duck lake.</p><p>The second part of the attach statement deals with the data storage location, and only has to be passed when first creating the DuckLake, subsequently it is stored by the data catalog. In this case I’m passing the bucket root address <em>s3://my-bucket/</em>, but I could pass a specific prefix, like <em>s3://my-bucket/silver/</em>.</p><p><strong>Using the DuckLake</strong></p><p>In the DuckLake, you can create a table and query in the ways you might expect:</p><pre>-- Create a table based on a parquet file<br>create or replace table my_ducklake.my_table as select * from &#39;s3://my-bucket/my_parquet.pq&#39;;<br><br>-- basic query<br>select * from my_ducklake.my_table limit 10;</pre><p>Now that we’ve got our DuckLake and we’ve created the tables we want to access, let’s move to integrating it with Superset.</p><h3>DuckLakes on Superset</h3><p>We have a dockerised version of superset running and our DuckLake postgres data catalog is running locally (e.g. on localhost). As such, I’ve also <a href="https://medium.com/@TimvanBaarsen/how-to-connect-to-the-docker-host-from-inside-a-docker-container-112b4c71bc66">set up the superset docker container</a> to use <em>host.docker.internal</em>, which enables access to the EC2/WSL2 localhost from inside the docker container.</p><p>Superset makes dealing with DuckLakes a little tricky, so it is important that you set up the DuckLake first — this is not straightforward to do in Superset itself. You might also want to first connect to a DuckDB :memory: instance to make sure you have the relevant extensions installed in the docker container and a persistent secret for connecting to s3.</p><p><strong>Connecting to your DuckLake in Superset</strong></p><p>Add a database connection (+ DATABASE) and input the connection via the sqlalchemy uri string highlighted below (Figure 2).</p><figure><img alt="An image showing the connect to database forms in Apache Superset with an indication on how to select the SQL Alchemy URI option needed." src="https://cdn-images-1.medium.com/max/1024/1*x442DURmm5CH0T0CFoTx0A.png" /><figcaption>Figure 2: Setting up a connection to your DuckLake in Superset.</figcaption></figure><p>The uri string is in fact the attach statement discussed above², and because the DuckLake already exists we do not need to add the data path. In my case, the uri string is:</p><pre>duckdb:///ducklake:postgres:dbname=ducklake_catalog host=host.docker.internal user=postgres password=postgres</pre><p>We use this specific string, rather than connecting to a duckdb instance and attaching the DuckLake because this approach ensures that the DuckLake connection is persisted, or at least recreated, across sessions.</p><p>You can also include additional engine configuration in the ADVANCED tab under Other → Engine Parameters as a JSON object, e.g.</p><pre>{<br>  &quot;connect_args&quot;: {<br>    &quot;config&quot;: {<br>      &quot;s3_region&quot;: &quot;eu-west-2&quot;,<br>      &quot;s3_url_style&quot;: &quot;path&quot;,<br>      &quot;s3_use_ssl&quot;: false,<br>      &quot;s3_access_key_id&quot;: &quot;my_access_key&quot;,<br>      &quot;s3_secret_access_key&quot;: &quot;my_password&quot;<br>    }<br>  }<br>}</pre><p>Finally, hitting CONNECT adds the database to your Databases.</p><p><strong>Creating a Dataset</strong></p><p>I found it easiest to create a dataset using Superset’s SQL Lab tool. This allows you to write the SQL query that returns the data you want and then to save it as a dataset for use in Superset.</p><p>This is convenient, because we can’t set an alias in our connection uri, so the actual schema name we use is a little awkward. Here, the statement we execute is:</p><pre>select * from &quot;postgres:dbname=ducklake_catalog host=host&quot;.main.my_table</pre><p>Inputting this statement into SQL Lab (Figure 3) is aided by autocomplete, hitting RUN produces a RESULTS table in SQL Lab, but you can SAVE this query to a dataset for use in Superset.</p><figure><img alt="An image showing Apache Superset’s SQL Lab with a SQL statement executed." src="https://cdn-images-1.medium.com/max/1024/1*HEkPSnHl1eJwT2hPWvltTw.png" /><figcaption>Figure 3: Superset SQL Lab dataset creation.</figcaption></figure><p>Now you can create visualisations and dashboards in exactly the way you expect, powered by data stored as parquet in an s3 bucket and connected using a DuckLake.</p><h3>Consolidation</h3><p>We’re just starting our work on enabling data discovery across our teams. We’re in development mode with DuckLake and Superset. What we’ve seen so far is really promising — an easy to use, powerful and fast data connector that meets our use case of bringing parquet to a business information tool.</p><p>We’re sharing our experience of working with DuckLake early in the hope that it inspires others to experiment with this approach and similarly share their learning. We’ve barely scratched the surface of its wider utility.</p><p>We’re looking forward to seeing what else we discover as we add in data orchestration and move from development to production. We plan to share more of what we learn along the way and are excited to see where this takes us!</p><p>[1] A persistent secret was useful for me, but they don’t have to be persistent. Duckdb also supports <a href="https://duckdb.org/docs/stable/core_extensions/aws.html">credential chains</a>.</p><p>[2] Thanks go to <a href="https://github.com/entropitor">entropitor </a>for <a href="https://github.com/Mause/duckdb_engine/issues/1293#issuecomment-3035518506">pointing this out</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1b1b00c222a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-analytics-at-nesta/ducklake-on-apache-superset-d1b1b00c222a">DuckLake on Apache Superset</a> was originally published in <a href="https://medium.com/data-analytics-at-nesta">Data science at Nesta</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>