<?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 Victor Meyer on Medium]]></title>
        <description><![CDATA[Stories by Victor Meyer on Medium]]></description>
        <link>https://medium.com/@victor_meyer?source=rss-ef110d3e004b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*9_jMVVOFwyfIf3Nyd5l8nA.jpeg</url>
            <title>Stories by Victor Meyer on Medium</title>
            <link>https://medium.com/@victor_meyer?source=rss-ef110d3e004b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 16:31:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@victor_meyer/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[Initialize your Datalake on Azure with DBT and DuckDB]]></title>
            <link>https://medium.com/@victor_meyer/initialize-your-datalake-on-azure-with-dbt-and-duckdb-f55cdc1dd3ca?source=rss-ef110d3e004b------2</link>
            <guid isPermaLink="false">https://medium.com/p/f55cdc1dd3ca</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[azure]]></category>
            <category><![CDATA[dbt]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Victor Meyer]]></dc:creator>
            <pubDate>Wed, 18 Jun 2025 14:18:22 GMT</pubDate>
            <atom:updated>2025-06-18T14:18:22.679Z</atom:updated>
            <content:encoded><![CDATA[<p>Organizing data in a datalake has become the standard practice for medium and large enterprises. Platforms like <strong>Databricks</strong> and <strong>Snowflake</strong> currently dominate the market, offering powerful and scalable solutions. However, for startups and small businesses, these platforms can often feel oversized and cost-prohibitive.</p><p>While many proprietary solutions perform well at a small scale, they frequently hit limitations as the system grows. This can result in accumulating technical debt that becomes costly and complex to resolve later.</p><p>To address these challenges, this article explores how to build a lightweight, cost-efficient datalake on <strong>Azure</strong> using <strong>DuckDB</strong> and <strong>DBT</strong>. The architecture is designed with scalability in mind, enabling an easy migration to <strong>Databricks</strong> when your business and data needs expand.</p><h3>Case study</h3><p>We work with the Amazon Book Reviews dataset from <a href="https://www.kaggle.com/datasets/mohamedbakhet/amazon-books-reviews">Kaggle</a>, implement a <strong>Medallion</strong> Architecture where data flows through <strong>bronze</strong>, <strong>silver</strong>, and <strong>gold</strong> layers. The gold layer is structured using a star schema, as outlined below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*CA-US8BypRBakkcT" /><figcaption>gold layer star schema</figcaption></figure><h3>Infrastructure</h3><p>We use <strong>Parquet</strong>, a widely adopted format for datalakes, to store our tables in <strong>Azure Blob Storage</strong> (see <em>limitation</em> below). Our Azure <strong>resource group</strong> is structured as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*fx0ZHmMV3OmxBrax" /><figcaption>resource group architecture</figcaption></figure><p>For infrastructure details, you can refer to the <a href="https://github.com/VictorMeyer77/duckdb-dbt-azure-infra">GitHub repository</a> containing the corresponding <strong>Terraform code</strong>.</p><p>The resource group includes:</p><ul><li><strong>Blob Storage</strong> with four containers: raw, bronze, silver, and gold, corresponding to the Medallion Architecture layers.</li><li>An <strong>Azure Container Registry (ACR)</strong> and <strong>Azure Container Instance (ACI)</strong>, which host and run our application container responsible for business logic.</li></ul><p>⚠️ ACI is <strong>not deployed via Terraform</strong>; it’s handled through a separate pipeline (see <em>Deployment</em> section).</p><ul><li><strong>Azure Automation</strong> with a <strong>runbook</strong> to start and stop the ACI as needed.</li><li><strong>Azure Data Factory (ADF)</strong> to orchestrate and monitor data pipelines and workflows.</li><li><strong>Azure Log Analytics</strong> to centralize logging and diagnostics for the entire resource group.</li></ul><h3>Transformation</h3><p>All business logic is implemented using <strong>DBT</strong> and <strong>DuckDB</strong>. Since there are many excellent resources available online about these tools, I won’t go into detail here. You can find all the relevant code in the <a href="https://github.com/VictorMeyer77/duckdb_dbt_azure">GitHub repository</a>.</p><p>To connect <strong>DBT</strong> with <strong>Azure</strong> and write to <strong>Parquet</strong> files, you simply need to enable the parquet extension and provide the appropriate credentials. In our setup, we use a basic <strong>storage account key</strong> for authentication. For a more secure and production-ready configuration, refer to the <a href="https://duckdb.org/docs/stable/core_extensions/azure.html">official Azure documentation</a>.</p><p>Here’s a sample <em>profiles.yml</em> configuration:</p><pre>duckdb_dbt_azure:<br>  outputs:<br>    dev:<br>      type: duckdb<br>      path: /tmp/books.duckdb<br>      threads: 1<br>      schema: dbt<br>      extensions:<br>        - parquet<br>      filesystems:<br>        - fs: abfs<br>          anon: false<br>          account_name: &quot;{{ env_var(&#39;STORAGE_ACCOUNT_NAME&#39;) }}&quot;<br>          account_key: &quot;{{ env_var(&#39;STORAGE_ACCOUNT_KEY&#39;) }}&quot;<br><br>  target: dev</pre><p>In our <strong>DBT</strong> model files, we can define external tables written to <strong>Parquet</strong> in <strong>Azure Blob Storage</strong> like this:</p><pre>{{<br>    config(<br>        materialized=&quot;external&quot;,<br>        location=&quot;abfs://bronze@{{ env_var(&#39;STORAGE_ACCOUNT_NAME&#39;) }}.blob.core.windows.net/books/books_data.parquet&quot;,<br>        format=&quot;parquet&quot;<br>    )<br>}}</pre><p>💡 <strong>Note</strong>: The <em>dbt test</em> command operates on the <strong>DuckDB database</strong> (e.g., /tmp/books.duckdb), not directly on the Parquet files.</p><h3>Container</h3><p>For easier deployment, the entire project is <strong>containerized with Docker</strong>. The image includes all required dependencies and executes the workflow via a <a href="https://github.com/VictorMeyer77/duckdb_dbt_azure/blob/main/Makefile"><strong>Makefile</strong></a>. Here’s the relevant section:</p><pre>.PHONY: run<br>run:             ## Launch workflow.<br> @echo &quot;\nExecute and test bronze and silver ...\n&quot;<br> @dbt run --select models/bronze models/silver<br> @dbt test --select models/bronze models/silver<br> @echo &quot;\nExecute gold ...\n&quot;<br> @dbt run --select models/gold</pre><p>This pipeline executes the <strong>bronze</strong> and <strong>silver</strong> models first. If tests pass successfully, it proceeds to update the <strong>gold</strong> layer.</p><h3>Deployment</h3><p>Once the resource group is provisioned after the <em>terraform apply</em> command, we can execute the <a href="https://github.com/VictorMeyer77/duckdb_dbt_azure/blob/main/.github/workflows/cd.yml">Continuous Deployment (CD) <strong>Github</strong> action</a>.</p><p>The Continuous Deployment (CD) pipeline automates the release of the project to <strong>Azure</strong>. The deployment consists of three main steps:</p><ol><li>Build and Push Docker Image</li></ol><pre>az acr build --image book_review:v1  \<br>  --registry ${{ vars.ENV }}duckdbtazacr \<br>  --file Dockerfile .</pre><p>A Docker image of the project is built and pushed to the <strong>Azure Container Registry</strong> (ACR).</p><p>2. Deploy Azure Container Instance (ACI)</p><pre>az container create --resource-group ${{ vars.ENV }}duckdbtazrg \<br>  --name ${{ vars.ENV }}duckdbtazaci-books \<br>  --image ${{ vars.ENV }}duckdbtazacr.azurecr.io/book_review:v1 \<br>  --os-type Linux \<br>  --log-analytics-workspace ${{ vars.ENV }}duckdbtazlog \<br>  --registry-username ${{ vars.ENV }}duckdbtazacr \<br>  --registry-password ${{ secrets.ACR_PASSWORD }}  \<br>  --cpu 2 \<br>  --memory 8 \<br>  --restart-policy OnFailure \<br>  --environment-variables STORAGE_ACCOUNT_NAME=&quot;${{ vars.ENV }}duckdbtazsto&quot; \<br>  --secure-environment-variables STORAGE_ACCOUNT_KEY=&quot;${{ secrets.STORAGE_ACCOUNT_KEY }}&quot;</pre><p>An <strong>Azure Container Instance</strong> (ACI) is created using an image stored in <strong>Azure Container Registry</strong> (ACR).</p><p>We deploy a Linux-based ACI with 2 vCPUs and 8 GB of memory. Since this is a batch workload, the restart policy is set to <strong>“OnFailure”</strong> instead of <strong>“Always”</strong> to ensure the container only restarts if it exits with an error.</p><p>To enable the ACI to pull the image from ACR, authentication is required. You can retrieve the ACR credentials here</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XnTBw312mA4sA2I5eMhu-A.png" /><figcaption>Azure Container Registry (ACR) password</figcaption></figure><p>We also define environment variables for the <strong>Docker</strong> image — specifically, the <strong>Blob Storage</strong> name and the corresponding key.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KJYPwVcNOS-6da5K5ufIuA.png" /><figcaption>Storage Account access keys</figcaption></figure><p>3. Assign Permissions to Azure Automation</p><pre>az role assignment create --role Contributor \<br>  --scope /subscriptions/${{ secrets.AZURE_SUBSCRIPTION_ID }}/resourceGroups/${{ vars.ENV }}duckdbtazrg/providers/Microsoft.ContainerInstance/containerGroups/${{ vars.ENV }}duckdbtazaci-books \<br>  --assignee ${{ vars.AUTOMATION_IDENTITY_ID }}</pre><p>The <em>Contributor</em> role is assigned to the <strong>Azure Automation</strong> account on the <strong>Azure Container Instance</strong> (ACI), allowing it to trigger container executions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MRKj7OEW7DBpYqQRNQ5GMQ.png" /><figcaption>Azure Automation System Assigned idenity id</figcaption></figure><h3>Execution and supervision</h3><p>After executing the Continuous Deployment (CD) pipeline, the <strong>Docker</strong> image is successfully pushed to the <strong>Azure Container Registry</strong> (ACR) and appears in the repository.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AsB5ieu1BFrd36JWyKxpEw.png" /><figcaption>Azure Container Registry (ACR) book_review image</figcaption></figure><p>The <strong>Azure Container Instance</strong> (ACI) is successfully created and deployed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*p0DxaQr91SRQfpevDAFt3A.png" /><figcaption>resource group with ACI</figcaption></figure><p>When the <strong>Azure Container Instance</strong> (ACI) is created, the container run is automatically triggered. You can monitor logs and metrics through the ACI interface.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j2RpwW6lVwB_qJj54xLG5Q.png" /><figcaption>Azure Container Instance (ACI) Overview</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4m0yuoTocZGigO6bv5EYZA.png" /><figcaption>Azure Container Instance (ACI) container logs</figcaption></figure><p>Perfect — the Parquet files for our gold table are successfully retrieved from <strong>Blob Storage:</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*laCKe1gOCQq08Ab3FKYGRQ.png" /><figcaption>Gold container after the first run</figcaption></figure><p><strong>Note:</strong> Source files are manually uploaded to the <em>books</em> folder within the <em>raw</em> <strong>Blob Storage</strong> container.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MuXwkEQPiyVKE4APQSCv4A.png" /><figcaption>Raw container before the first run</figcaption></figure><p>However, when working with multiple pipelines, it’s more efficient to centralize workflow orchestration and log monitoring.</p><p><strong>Azure Data Factory</strong> is a widely used solution for building and monitoring ETL processes in <strong>Azure</strong>. Unfortunately, it does not natively support triggering an <strong>Azure Container Instance</strong> (ACI) directly. To work around this limitation, we integrate <strong>Azure Automation</strong> using a <strong>webhook</strong>.</p><p>As a first step, I create a <a href="https://github.com/VictorMeyer77/duckdb-dbt-azure-infra/blob/main/terraform/automation.tf"><strong>runbook</strong></a> in <strong>Azure Automation</strong> that can start or stop the ACI as needed.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vx-ew_xwpA2hi-cU3fKFow.png" /><figcaption>Azure Automation runbook</figcaption></figure><p>I also create an associated <strong>webhook</strong> to trigger the <strong>runbook</strong> and start ACI.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HckxjBOQ_nEAESDJ3PqF9A.png" /><figcaption>Associated webhook</figcaption></figure><p>Now, I can create an <strong>Azure Data Factory</strong> activity that calls our <strong>webhook</strong>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0e_73kK3VINJztVc8l03WQ.png" /><figcaption>Data Factory activity</figcaption></figure><p>Although logs can be viewed directly in ACI, managing logs across multiple projects can become complicated. To simplify this, we centralize logs by sending them to an <strong>Azure Log Analytics </strong>workspace.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7airXXs7C3rZG28jTiU3Rg.png" /><figcaption>Log Analytics ContainerInstanceLog_CL table</figcaption></figure><h3>Limitation</h3><p><strong>Parquet</strong> is a common file format used in datalakes. However, formats like <strong>Delta Lake</strong> provide additional functionality such as ACID transactions, table versioning, or time travel. These features make <strong>Delta Lake</strong> a better choice for production environments. Unfortunately, the <strong>dbt-duckdb</strong> plugin does not yet support the <strong>Delta</strong> format.</p><h3>Conclusion</h3><p>At a very low cost, we demonstrate how to initialize an <strong>Azure Data Lake</strong> using <strong>DBT</strong> and <strong>DuckDB</strong>. As your company grows and your datalake needs to become more resilient and scalable, you can quickly migrate to <strong>Databricks</strong>.</p><p>Many core components — such as <strong>Blob Storage</strong> and <strong>Data Factory</strong> — are already in place, and <strong>Parquet</strong> files can be ingested seamlessly using <strong>Databricks</strong>, making the transition smooth and efficient.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f55cdc1dd3ca" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>