Ole Bause – Scalefree

Introducing Visual Data Vault: A Purpose-Built Canvas for Data Vault Modeling

Anyone who has worked on a Data Vault project knows how important a good diagram can be. Hubs, Links, Satellites, and supporting structures show which business concepts matter, how they relate to one another, and where their descriptive history belongs. A clear model can turn a complex discussion into something everyone in the room can follow.

Creating that model is often more difficult than it needs to be. General-purpose diagramming tools can draw the shapes, but they do not understand Data Vault. Colors have to be selected by hand, naming conventions must be remembered, and technical details often end up as free text inside a box. Before long, more time is spent maintaining the diagram than discussing the model itself.

This is the problem we want to solve with Visual Data Vault.

In this article:

Model with Data Vault entity types
Add the detail that makes a model useful
Save your work and export it in the format you need
A visual modeling tool with a clear scope
Create your first model

Built by Scalefree, Visual Data Vault is a lightweight, browser-based application for visually designing and documenting Data Vault models. It provides a focused workspace with familiar Data Vault entity types, practical modeling details, and simple ways to save and exchange diagrams. It is also completely free to use, so you can open the editor and start modeling without purchasing a license or subscription.

It also has a deliberately clear scope. Visual Data Vault is a visual modeling tool. It helps you design, discuss, and communicate a Data Vault model, but it does not generate transformation code, deploy database objects, run data pipelines, or build the warehouse on your data platform.

Diagram of a Data Vault model on a UI canvas, showing hubs, links, and satellites (Nation, Region, Supplier, Customer, Order, Part) connected in a network.

A Data Vault model created in a browser with purpose-built entity types and modeling controls.

Model with Data Vault entity types

When you open the editor, you do not start with a collection of generic shapes. The palette contains the Data Vault entities you need, ready to place on the canvas: Hubs, Links, Satellites, Reference Tables, PIT Tables, and Bridge Tables.

More specialized entity types are available as well. You can model Reference Hubs; Non-Historized and Hierarchical Links; and Reference, Multi-Active, Effectivity, Record-Tracking, and Non-Historized Satellites. Each one is visually recognizable, while the diagram as a whole keeps a consistent look.

Of course, real-world diagrams often need some extra context. A source table may need to appear next to the Raw Vault model, a design decision may need a short explanation, or a large subject area may need a visible boundary. For these situations, the editor also includes generic tables, notes, and groups.

The basic workflow feels natural: drag an entity onto the canvas, name it, move it into place, and connect it. From there, alignment assistance and multi-selection help keep the layout tidy. You can copy and paste parts of a model, lock finished entities in place, adjust relationship paths, and use undo or redo while trying out a different design. Local change history gives you another way to return to an earlier point.

The result is an editor that stays out of the way and lets the conversation remain focused on the model.

Entities can be placed and connected without manually recreating Data Vault notation.

Add the detail that makes a model useful

A good Data Vault diagram needs more than correctly colored entities. It should also carry enough information to support the next design discussion.

In Visual Data Vault, each entity can have a readable display name as well as a unique technical table name. Technical names can be created automatically from configurable naming conventions. When a model grows or changes, this helps keep names consistent without turning every edit into a manual cleanup task.

Select an entity and its details appear in the inspector beside the canvas. Depending on the entity type, you can define business or reference keys, review standard technical columns, and add descriptive columns with their data types. Relationships can also have labels and descriptions, making their meaning easier to understand when the diagram is shared with someone else.

Sometimes you want to discuss the architecture without seeing every column. At other times, those columns are exactly what the discussion is about. Visual Data Vault supports both situations through table-level and column-level views. The compact table-level view is well suited to workshops and high-level reviews. Switch to the column-level view when you need to look more closely at keys, metadata, and structure.

The editor also provides basic guidance when entities are connected. Clearly invalid relationships are prevented, which helps catch simple modeling mistakes as they happen. This guidance is there to support the modeler, not to replace architectural judgment or act as a complete validation framework.

Switch from an architectural overview to column-level detail without leaving the canvas and edit column names

Save your work and export it in the format you need

Many models are not finished in a single session. They develop over several workshops, review rounds, and small improvements. Visual Data Vault lets you save diagrams to your account and return to them through the diagram library.

The library makes it easy to find, rename, clone, reopen, or remove a saved diagram. Autosave helps protect changes while you work, so the editor can be used for an evolving design rather than only for a quick sketch.

Of course, a diagram rarely stays inside one application. It may need to become part of a presentation, a technical specification, or another modeling workflow. Visual Data Vault therefore includes four export options, each intended for a different purpose:

PNG creates a high-resolution image that can be added directly to presentations, documents, and architecture specifications.
PDF captures the diagram as a single-page document that is easy to review, distribute, or archive.
JSON preserves the Visual Data Vault diagram in an editable application format. It is the best option for backing up a model or moving it between Visual Data Vault sessions.
DBML exports supported tables and relationships for use in compatible external modeling tools.

JSON and DBML can also be imported, making it possible to bring an exported diagram back onto the canvas or use supported DBML structures as the starting point for a new visual model. These import and export features are available as part of the free application.

Before an imported JSON or DBML file replaces the current canvas, the application validates it and shows what can be brought into the model. For DBML, it can report incomplete or unsupported elements and offer repair options for some missing metadata.

There is one important limitation to keep in mind: DBML is an exchange format, not a complete description of Data Vault semantics. It can transfer supported tables and relationships, but it does not preserve every modeling rule, key, constraint, or implementation choice.

Continue saved models later or export them as image, document, JSON, and DBML formats.

A visual modeling tool with a clear scope

Visual Data Vault fits into the part of a project where a model needs to become visible, understandable, and ready for discussion. You might use it to sketch the first version of a Raw Vault, work through relationships during a workshop, prepare for an architecture review, or create a consistent diagram for technical documentation.

Once the visual model is agreed upon, the engineering work still remains. Your chosen implementation and automation tools are responsible for creating tables, building loading patterns, transforming data, testing pipelines, and deploying the solution to the target platform. Visual Data Vault complements those tools; it does not try to replace them.

The same clear boundary applies elsewhere. Visual Data Vault is not a full metadata or lineage platform, and it is not currently a real-time multi-user modeling environment. Its purpose is more direct: make Data Vault diagrams easier to create, enrich, maintain, and exchange.

We believe that focus is useful. Data Vault practitioners should not have to rebuild their modeling language from generic shapes every time they start a diagram. They should be able to open a canvas that already understands the work they want to do.

Create your first model

Visual Data Vault brings Data Vault entity types, technical modeling detail, diagram management, and useful export formats into one browser-based workspace, and it is completely free to use.

Open Visual Data Vault and create your first diagram at no cost. The next time your team discusses a Data Vault design, you will have a clear model everyone can see, review, and understand.

Open Visual Data Vault

Ole Bause In Data Tools,

Google BigQuery and dbt Cloud Quickstart: Full Setup Guide

Get Started with Google BigQuery and dbt Cloud

If you are looking to build a modern, scalable data transformation workflow, combining dbt Cloud with Google BigQuery is one of the most powerful stacks available today. In this guide — based on a hands-on walkthrough video — we cover everything from setting up your Google Cloud project to running your first automated deployment job. Whether you are new to analytics engineering or already familiar with SQL-based transformations, this tutorial will get you productive quickly.

In this article:

What Is dbt and Why Does It Work So Well with Google BigQuery?
Step 1: Set Up Your Google Cloud Project and BigQuery Dataset
Step 2: Create a Service Account and Generate a JSON Key
Step 3: Connect dbt Cloud to BigQuery
Step 4: Initialize Your dbt Project
Step 5: Define Your Sources
Step 6: Build Staging Models
Step 7: Build a Customer Dimension Model
Step 8: Create a Deployment Environment and Schedule a Job
What to Learn Next: Data Vault, dbt, and Scalable Analytics Engineering
Watch the Video

What Is dbt and Why Does It Work So Well with Google BigQuery?

Before diving into the setup steps, it helps to understand what each tool actually does and why they complement each other so naturally.

dbt (data build tool) is an open-source transformation framework that brings software engineering best practices into data teams. With dbt, your SQL code is version-controlled, tested, documented, and executed through scheduled jobs — the same discipline that software engineers apply to application code. You write modular SQL models, dbt compiles them, and the actual query execution happens entirely on your data platform.

Google BigQuery is a fully managed, serverless cloud data warehouse built for large-scale analytics. It handles the storage and compute, scales automatically, and integrates tightly with the broader Google Cloud ecosystem. Critically for this tutorial, BigQuery hosts publicly available datasets you can query immediately — no manual data loading required to get started.

Together, dbt and BigQuery form a clean separation of concerns: dbt manages your transformation logic, documentation, and pipeline orchestration, while BigQuery handles the heavy lifting of storing and querying your data at scale. This makes the combination especially attractive for analytics engineering teams that want speed, reliability, and maintainability.

Step 1: Set Up Your Google Cloud Project and BigQuery Dataset

Everything starts in the Google Cloud Console. If you do not already have a Google Cloud account, you can start for free — Google offers around $300 in credits with no credit card required upfront, which is more than enough for this quickstart.

Once you are in the console, navigate to the Cloud Resource Manager and create a new project. For this demo, a name like bigquery-dbt-quickstart works well. After the project is created, head to the BigQuery console from the main navigation menu.

In BigQuery, you will immediately notice that the terminology differs slightly from traditional databases. Instead of database → schema → table, BigQuery uses project → dataset → table. The concepts map directly, but knowing this terminology prevents confusion when you are configuring dbt later.

Create a new dataset inside your project — call it something like dbt_demo. During creation, you will be asked to set a data location (choose a region that makes sense for your use case, or a multi-region option like US or EU), an optional expiration policy, and an encryption setting. The defaults are fine for a quickstart.

The public tutorial data you will be using lives in a project called dbt-tutorial, under a dataset named jaffle_shop. It contains customer and order tables — exactly what you need to build a meaningful first model.

Step 2: Create a Service Account and Generate a JSON Key

For dbt Cloud to run queries in BigQuery on your behalf, it needs authenticated access. The most straightforward approach for a quickstart is a Google Cloud service account with a JSON key file.

In the Google Cloud console, use the Credentials Wizard under APIs & Services to create a new service account. Name it something like dbt-user. Assign it two roles:

BigQuery Data Editor — allows the service account to create and modify tables
BigQuery User — allows it to run jobs against the project

Once the service account is created, open it and navigate to the Keys tab. Create a new JSON key. This downloads a file to your local machine — keep it secure and do not commit it to version control. You will upload it to dbt Cloud in the next step.

Note: In a production environment, you would typically use OAuth or Workload Identity Federation rather than a JSON key file. For this quickstart, the service account key is the simplest and most transparent option.

Step 3: Connect dbt Cloud to BigQuery

With your service account key ready, log in to dbt Cloud and create a new project. When prompted to set up a connection, choose BigQuery and upload the JSON key file you just downloaded. dbt Cloud will parse the file and automatically populate your project and credential details.

Set a target name for the connection — something like dbt-demo — and save. For the repository, this demo uses dbt’s managed Git repository, which is the quickest option. In a real production project, you would connect dbt Cloud to your own GitHub, GitLab, or Azure DevOps repository and use pull requests and CI/CD pipelines to manage code changes properly.

Step 4: Initialize Your dbt Project

Open the dbt Cloud Studio (the browser-based IDE). You will see a button in the top-left to initialize your dbt project. Click it — dbt will automatically generate the standard project scaffold, including the dbt_project.yml file and a default folder structure.

The dbt_project.yml file is the configuration file that tells dbt this folder is a dbt project. It is where you set the project name, define global defaults, and configure materialization strategies per folder. Every SQL model you place inside the models/ folder from this point forward can be compiled, tested, documented, version-controlled, and executed against BigQuery automatically.

You will also notice that the initializer creates some example models. Delete the examples/ subfolder inside models/ to start with a clean structure — but be careful not to delete the models/ folder itself.

Once your project structure looks clean, commit the initial files with a message like project initialization and you are ready to start building.

Step 5: Define Your Sources

In dbt, sources are the starting point of every project. They tell dbt where your raw input data lives — which project, dataset, and table — without hardcoding those references directly into each model file. This makes your project far more maintainable: if a source location changes, you update it in one place and every model that uses it automatically picks up the change.

Create a new file in your models/ folder and name it sources.yml. The structure always starts with version: 2 (required for the current dbt YAML schema), followed by a sources: key. Under that, define a source with a name (for example, jaffle_shop), specify the BigQuery project as the database (dbt-tutorial), the dataset (jaffle_shop), and list the individual tables you want to reference — in this case, customers and orders.

After saving the file, the dbt Cloud lineage panel will update to show your newly recognized sources. They will appear as the entry points in your project’s data lineage graph.

Step 6: Build Staging Models

A core best practice in dbt is to never reference raw source tables directly in your business logic models. Instead, you create a thin staging layer — one model per source table — that lightly cleans and standardizes the data. Typical staging transformations include renaming columns to consistent naming conventions, casting data types, and filtering out clearly invalid records.

dbt Cloud makes this step even easier: the lineage view shows a link next to each detected source table that lets you auto-generate a staging model scaffold. Click it, and dbt generates a boilerplate SQL file using Common Table Expressions (CTEs) and the source() macro.

The source() macro is one of the first things that makes dbt feel different from plain SQL. Instead of writing SELECT * FROM dbt-tutorial.jaffle_shop.customers, you write SELECT * FROM {{ source('jaffle_shop', 'customers') }}. dbt compiles this at runtime into the correct fully qualified BigQuery reference, keeping your code clean and location-independent.

In the staging model for customers, rename the id column to customer_id for clarity. Save the file and use the Preview button to verify that dbt can query the data from BigQuery — this confirms your connection and credentials are working correctly.

When you are ready to persist the model, click Build. dbt will create the model in BigQuery under your personal development dataset — a sandboxed schema tied to your dbt Cloud user account. This means you can build and iterate on models freely without touching anything in your production environment.

If you switch back to the BigQuery console at this point, you will find a new dataset named after your dbt username, and inside it, a view representing your staging model. Repeat the same process for the orders source table.

Step 7: Build a Customer Dimension Model

With your staging models in place, you are ready to build a more meaningful transformation: a customer dimension that joins customer data with order history.

Create a new folder inside models/ called marts/ (a common convention for final output models), and inside it create a file called dim_customers.sql.

In this model, instead of the source() macro, you will use the ref() function — the other core dbt macro. Where source() points to raw input tables, ref() points to other dbt models you have already defined. Writing {{ ref('stg_jaffle_shop__customers') }} does two things: it generates the correct BigQuery reference at compile time, and it tells dbt that this model depends on the staging model — building the dependency graph that dbt uses to determine build order, enable lineage tracking, and support documentation.

Build out the model with a few CTEs: one that selects from your customers staging model, one that aggregates order data per customer (first order date, most recent order date, and total order count), and a final CTE that joins the two together. The lineage panel will update to show the full data flow from raw sources through staging into the dimension model — a powerful visualization, especially as your project grows.

Build the model, verify it in BigQuery, then commit your changes and merge to main.

Step 8: Create a Deployment Environment and Schedule a Job

Development work happens in personal dev environments. Production data transformations require a deployment job — a configured, scheduled run that executes your models against your production BigQuery dataset.

In dbt Cloud, navigate to Orchestration → Environments and create a production environment. You will need at minimum one development environment and one production environment. Attach a connection profile to the production environment that points to the dbt_demo dataset you created earlier — this is where production models will be materialized.

Then go to Orchestration → Jobs → Deploy Job and create a new job. Name it something like Production Daily, select your production environment, and configure the run command as dbt build (which compiles, runs, and tests all models in one step). Enable a schedule — for example, daily at 2:00 AM using either the UI scheduler or a cron expression for more precise control.

You can also trigger the job manually to verify it runs successfully. The job run view shows the status of each model — pass, fail, or skip — making it easy to spot and debug issues in production.

What to Learn Next: Data Vault, dbt, and Scalable Analytics Engineering

What you have built in this walkthrough — connecting dbt Cloud to Google BigQuery, defining sources, creating staging and dimension models, and scheduling a deployment job — represents the foundational workflow you will use in every real analytics engineering project.

But this is just the beginning. dbt supports a wide range of advanced features: custom tests, documentation generation, macros and Jinja templating, incremental models for large datasets, snapshots for slowly changing dimensions, and much more.

If you want to go deeper and learn how to combine dbt with structured modeling methodologies, Data Vault 2.0 is the industry standard for building scalable, auditable, and future-proof enterprise data warehouses. At Scalefree, we offer comprehensive Data Vault 2.1 training and certification programs that cover everything from foundational concepts to advanced implementation patterns — including how to integrate dbt into a Data Vault workflow on platforms like BigQuery.

Whether you are just starting your analytics engineering journey or looking to formalize your team’s approach to data modeling, our Data Vault 2.1 certification is the most direct path to building production-grade data platforms with confidence.

Check out our other platform-specific tutorials — including guides for Snowflake — and explore our webinars and training programs to take your data engineering skills to the next level.

Watch the Video

Ole Bause In Data Architecture

The Sovereign Data Platform: The Path to Data Ownership and Secure AI

Data sovereignty is often dismissed as a political buzzword or as a pure compliance topic, for example, in the context of the GDPR or the EU AI Act. In reality, however, it is a clear business necessity. In a time when data is no longer only visualized in dashboards but forms the foundation for automated business processes and artificial intelligence, a company’s own data infrastructure becomes a strategic bottleneck.

Companies that hand over full control to external technology providers at this stage not only lose independence. They also lose the ability to innovate on their own terms. When data is locked inside closed systems, the provider ultimately decides which tools can be connected and which AI models can be used. The path to real data sovereignty starts with the understanding that the convenient all-in-one promises of many cloud providers come at a high and often hidden price.

In simple terms, the key question is this: Do your data architecture and your AI future truly belong to you, or do they depend on the interfaces, pricing models, and product decisions of a single vendor?

In this article:

What Loss of Control Looks Like in Practice
The Data Lakehouse and Open Standards as the Way Out
No Secure AI Without a Sovereign Data Platform
Data Governance: From Rulebook to Business Enabler
How Can the Migration Succeed?
Actively Shaping Sovereignty
- Is your data ready for the future?

What Loss of Control Looks Like in Practice

To understand how companies can regain data ownership, it is important to first look at how they lose it. This loss of control rarely happens overnight. It is usually a gradual process that is deeply rooted in the architecture of traditional and modern cloud data platforms.

When a company chooses a proprietary data platform, the raw data is fully handed over to the vendor’s system.

Proprietary Formats

To deliver the promised performance, closed platforms often transform ingested data into vendor-specific, proprietary storage formats. From that moment on, the data can only be read and processed by the compute engine of that specific vendor.

Lack of Interoperability

If a company then wants to connect a new and innovative solution, such as a specialized analytics engine, third-party reporting software, or a specific open source AI model, it often hits a wall. External tools cannot read the proprietary formats natively, or the required interface is simply not available.

The Cost Trap of Egress Fees

To make the data usable for other applications, or in the worst case, to switch providers completely, the data has to be exported with significant effort. This is where egress fees, meaning costs for moving data out of a platform, can become a major issue. Large cloud providers often make data ingestion very affordable, while data export can lead to substantial costs.

Loss of Pricing Power

Once historical company data is deeply embedded in a closed system, and switching costs have been artificially increased, companies become exposed to future price increases and licensing changes from the vendor.
In short, the company still carries the full legal and business responsibility for its data, but it has lost direct physical access to it. It is essentially renting access to its own knowledge.

At this point, ask yourself honestly:

Do you know exactly in which format and on which infrastructure your core data is stored right now?

And even more importantly: How would you access your data if the vendor portal stopped working tomorrow morning, or if prices were changed unexpectedly overnight?

The Data Lakehouse and Open Standards as the Way Out

The technological way out of this dependency requires a fundamental architectural shift. The answer to proprietary data silos is the modern Data Lakehouse. This architecture combines the flexibility of a Data Lake with the structure and reliability of a traditional Data Warehouse, but with one decisive principle: the strict separation of storage and compute.
This separation allows companies to build their architecture according to a best-of-breed approach.

Company Controlled Infrastructure

Instead of loading data completely into proprietary platforms operated by external vendors and binding it to closed formats, the data remains in object storage controlled by the company, for example, Amazon S3, Azure Data Lake Storage, or a comparable storage solution. The decisive point is not that the storage must physically run in the company’s own data center. What matters is that the data is stored in open formats and is not inseparably tied to one specific compute engine or platform logic.

At first glance, this may seem paradoxical. Even in a sovereign data architecture, cloud providers can still play an important role. The difference lies in how they are used. They primarily serve as replaceable storage infrastructure for open files, not as closed all-in-one platforms. This makes a later migration to another storage provider much easier.

Open Data Formats as the Foundation

One of the most important levers for data sovereignty is the storage format. In a modern Data Lakehouse, data is stored in open standards such as Apache Iceberg, Hudi, or Delta Lake. These formats do not belong to a single software vendor and are not tied to proprietary licensing.

Interoperability: Bring Your Own Engine

Large platform vendors are increasingly recognizing this trend. Modern data platforms are moving more and more toward open Lakehouse architectures, where they do not necessarily have to own the data format, but can act as powerful compute and governance layers on top of open data. Snowflake, for example, supports architectures based on Apache Iceberg and can access data in external cloud storage through External Volumes. This makes the principle of open data storage and flexible computing increasingly relevant for established enterprise platforms as well.

Because company data is now structured and stored in open formats in controlled storage, it can be read and processed by different engines, including Snowflake, Databricks, Trino, Spark, and other specialized engines. The decisive advantage is that the data foundation does not have to be copied, exported, or remodeled every time.

The result of this architecture is real digital sovereignty. If a software vendor significantly increases prices or falls behind technologically, the compute engine can be replaced or complemented with other tools. The valuable data foundation remains untouched.

No Secure AI Without a Sovereign Data Platform

This architectural independence is not only a matter of cost control. It is also an important prerequisite for the productive and secure use of artificial intelligence. Across almost every industry, companies are under pressure to introduce AI-supported automation. At the same time, there is growing concern that sensitive business knowledge could flow into black box language models, or that incorrect AI answers could lead to critical business mistakes.

Poorly organized data foundations and closed SaaS systems systematically slow down AI initiatives. A sovereign AI approach requires a different way of working.

Querying Instead of Embedding

Many early AI initiatives fail because company data is copied without sufficient control into external AI services, vector databases, or isolated prototypes. This creates new data silos, data protection risks, and answer chains that are difficult to trace. It can also increase the risk of unreliable or misleading AI results. A large language model is primarily a language tool, not a relational database.

Agentic AI on an Open Source Foundation

The solution lies in using agentic AI in combination with open source or otherwise controlled language models that run within the company’s own cloud or enterprise environment. With the right architecture, sensitive data does not have to leave the controlled company environment.

Even more importantly, the AI is configured in a way that it does not memorize the data. Instead, it acts as an intelligent agent. It uses its understanding of context to perform targeted queries, for example, through SQL, against the open data formats of the Lakehouse whenever needed.

“Talk to Your Data” in Practice

By connecting directly to the central data platform, the system can provide hard, verifiable facts instead of relying only on statistically generated answers. This approach enables completely new business processes. Business departments without deep programming or SQL skills can interact with their data through natural language. Complex analyses and reports can be automated and queried in a reliable way.

For this dialogue between the business user, AI agent, and data platform to work smoothly, the AI must understand exactly how the data is structured and what business meaning it carries. Technology alone is not enough. This brings us to the often underestimated core of data sovereignty.

Data Governance: From Rulebook to Business Enabler

The same principle applies to data platforms as to many other areas: technology alone does not guarantee success. A modern Data Lakehouse and advanced agentic AI will not create value if the underlying data quality is poor or if the business meaning of the data is unclear. At this point, Data Governance changes from an often unpopular control mechanism into a true business enabler.

When an AI agent is expected to translate a user request into a precise database query, it needs more than access to tables. It needs context. Without a well-maintained business glossary, clear metadata, and defined data ownership, there is a high risk that the AI will generate technically valid but factually wrong results. Garbage in, garbage out is more relevant than ever in the age of artificial intelligence.

A clean governance structure solves this problem at the root.

Central Truth, Decentralized Use

Clear quality rules and defined data products create a foundation of trust. Business departments can rely on the information provided to them being correct, current, and legally compliant.

Real Democratization

Only this trust enables self-service analytics. Once governance guardrails are in place, data can be made available safely across the organization without the IT department having to manually approve every single report. AI results can also be better understood, validated, and reused with more confidence.

Compliance by Design

With strict European regulations such as the GDPR and the EU AI Act in mind, integrated governance ensures that access rights, anonymization, and traceability, including data lineage, are built into the architecture from the beginning.

Companies that take internal responsibility for their data in this way create the necessary foundation for scalability.

How Can the Migration Succeed?

The benefits of open standards and a sovereign architecture are clear. Still, many IT leaders hesitate to move away from vendor lock-in because they fear a risky, multi-year IT program. But breaking free from closed systems does not require a risky big bang.

Successful migration projects show that the move to an open and more sovereign architecture can be agile and incremental.

Use Case Driven Migration

Instead of replacing the entire historical Data Warehouse at once, the new open platform is built in parallel. Migration is based on prioritized, business-critical use cases.

Faster Return on Investment

By first migrating the data areas that create the highest immediate value, for example, to enable new use cases that were previously impossible, the transformation can often start to pay for itself during the project.

Risk Reduction

This step-by-step approach ensures that daily operations, including reporting and ongoing analyses, continue without disruption while the future-ready foundation grows in the background.

The move toward open software and vendor-independent data formats is therefore not an IT goal for its own sake. It is a planned, low-risk investment in the company’s ability to act independently.

Actively Shaping Sovereignty

A truly sovereign company is one that fully controls the architecture, quality, and location of its data and is aware of this responsibility. If you want to break free from dependency, leave expensive licensing models behind, and create a legally sound foundation for artificial intelligence, the path inevitably leads through open standards.

Take full responsibility for your data again. Turn your IT infrastructure from a pure cost factor into a decisive competitive advantage for your industry.

As experts in Big Data, Data Warehousing, and modern data platforms, Scalefree helps European companies take this path successfully. From strategic architecture consulting to technical implementation and productive data and AI solutions, including agentic AI approaches on sovereign data platforms.

Is your data ready for the future?

Let us review your current architecture in a non-binding conversation. Learn how a tailored Data Lakehouse based on open standards can secure your data sovereignty for the long term.

Book an initial consultation

Ole Bause In Data Architecture

Datensouveränität: Die souveräne Datenplattform als Weg zu unabhängigen Daten und sicherer KI

Datensouveränität wird oft als rein politisches Buzzword oder als bloße Compliance-Aufgabe, z. B. im Rahmen der DSGVO und des AI Acts, abgetan. Doch in der Realität ist sie eine harte wirtschaftliche Notwendigkeit. In einer Ära, in der Daten nicht mehr nur in Dashboards visualisiert werden, sondern als Grundlage für automatisierte Geschäftsprozesse und Künstliche Intelligenz dienen, wird die eigene Infrastruktur zum strategischen Flaschenhals.

Wer in dieser Phase die Kontrolle vollständig an externe Technologieunternehmen abtritt, verliert nicht nur Unabhängigkeit, sondern auch Innovationskraft. Sind Daten in geschlossenen Systemen gefangen, bestimmt letztlich der Anbieter, was angebunden werden darf oder welche KI-Modelle genutzt werden können. Der Weg zu echter Datensouveränität beginnt mit der Erkenntnis, dass die bequemen „All-in-One“-Versprechen vieler Cloud-Anbieter einen hohen, oft versteckten Preis haben.

Wie der Kontrollverlust in der Praxis aussieht

Um zu verstehen, wie sich die Datenhoheit zurückgewinnen lässt, muss man zunächst betrachten, wie Unternehmen sie überhaupt verlieren. Dieser Kontrollverlust geschieht selten über Nacht. Vielmehr ist es ein schleichender Prozess, der tief in der Architektur traditioneller und moderner Cloud-Datenplattformen verwurzelt ist.

Fällt die Entscheidung auf eine proprietäre Datenplattform, werden die Rohdaten vollständig an das System des Anbieters übergeben.

Proprietäre Formate

Um die versprochene Performance zu liefern, wandeln geschlossene Plattformen die eingespeisten Daten in herstellereigene, proprietäre Speicherformate um. Ab diesem Moment können diese Daten nur noch von der Compute-Engine (der Rechenleistung) genau dieses einen Anbieters gelesen und verarbeitet werden.

Fehlende Interoperabilität

Soll nun eine neue, innovative Lösung, wie bspw. eine spezialisierte Analyse-Engine oder Reporting Software eines Drittanbieters angebunden oder eine bestimmte (open-source) KI genutzt werden, stehen Unternehmen oft vor einer Wand. Externe Tools können die proprietären Formate nicht nativ lesen oder es wird gar nicht erst eine benötigte Schnittstelle bereitgestellt.

Kostenfalle (“Egress Fees”)

Um die Daten für andere Anwendungen nutzbar zu machen, oder im schlimmsten Fall den Anbieter komplett zu wechseln, müssen sie aufwändig exportiert werden. Hier schlagen die sogenannten „Egress Fees“ (Kosten für den Datenabfluss) massiv zu Buche. Große Cloud-Provider machen den Ingest (das Einspeisen der Daten) oft sehr günstig, bestrafen den Export aber mit hohen Gebühren.

Verlust der Preissetzungsmacht

Sind historische Unternehmensdaten erst einmal in einem geschlossenen System verankert und die Wechselkosten künstlich in die Höhe getrieben, sind Unternehmen künftigen Preissteigerungen und Lizenzänderungen des Anbieters ausgeliefert.

Kurzum: Das Unternehmen trägt zwar weiterhin die volle rechtliche und geschäftliche Verantwortung für seine Daten, hat aber den direkten, physischen Zugriff darauf verloren. Es mietet lediglich den Zugang zum eigenen Wissen.

Stellen Sie sich an diesem Punkt einmal ganz ehrlich die Frage:

Wissen Sie genau, in welchem Format und auf welcher Infrastruktur Ihre Kern-Daten in diesem Moment liegen?
Und noch viel wichtiger: Wie kommen Sie an Ihre Daten, wenn der Zugang über das Portal Ihres Anbieters morgen früh plötzlich nicht mehr funktioniert oder die Preise über Nacht unerwartet diktiert werden?

Das Data Lakehouse und offene Standards als Ausweg

Der technologische Ausweg aus dieser Abhängigkeit führt über eine grundlegende architektonische Neuausrichtung. Die Antwort auf proprietäre Datensilos lautet heute: Data Lakehouse. Dieser Architekturansatz vereint die Flexibilität eines Data Lakes mit der Struktur und Zuverlässigkeit eines klassischen Data Warehouses, jedoch unter einer entscheidenden Prämisse: der konsequenten Trennung von Speicher (Storage) und Rechenleistung (Compute).

Diese Trennung ermöglicht es Unternehmen, ihre Architektur nach dem “Best-of-Breed-Prinzip” aufzubauen:

Eigene Infrastruktur

Anstatt Daten in die Systeme externer Dienstleister zu laden und dort zu “verriegeln”, verbleiben sie im unternehmenseigenen Cloud-Speicher (beispielsweise Amazon S3, Azure Data Lake oder Google Cloud Storage). Das Unternehmen besitzt faktisch und rechtlich den einzigen Schlüssel zu den eigenen Daten.

Offene Datenformate als Fundament

Ein wichtiger Hebel der Datensouveränität ist das Speicherformat. In einem modernen Data Lakehouse werden Daten ausschließlich in quelloffenen Standards wie Apache Iceberg, Hudi oder Delta Lake abgelegt. Diese Formate gehören keinem einzelnen Software-Hersteller und unterliegen keiner proprietären Lizenzierung.

Interoperabilität (“Bring Your Own Engine”)

Da die Unternehmensdaten nun strukturiert und in einem offenen Format im eigenen Speicher liegen, lassen sie sich von unterschiedlichsten Verarbeitungs-Engines (wie Databricks, Trino, Spark etc.) lesen. Der entscheidende Vorteil: Die Daten müssen dafür weder kopiert noch verschoben werden.

Das Resultat dieser Architektur ist echte digitale Souveränität. Wenn ein Software-Anbieter die Preise drastisch erhöht oder technologisch zurückfällt, lässt sich die Compute-Engine austauschen oder parallel durch andere Tools ergänzen. Die wertvolle Datenbasis bleibt davon völlig unberührt.

Keine sichere KI ohne souveräne Datenplattform

Diese architektonische Unabhängigkeit ist nicht nur eine Frage der Kostenkontrolle, sondern eine wichtige Grundvoraussetzung für den produktiven und sicheren Einsatz von Künstlicher Intelligenz. Aktuell herrscht in nahezu jedem Industriesektor der Druck, KI-gestützte Automatisierungen einzuführen. Gleichzeitig wächst die berechtigte Sorge, sensible Geschäftsgeheimnisse an US-amerikanische „Black-Box“-Sprachmodelle abfließen zu lassen oder durch fehlerhafte KI-Antworten (Halluzinationen) geschäftskritische Fehlentscheidungen zu treffen.

Eine unaufgeräumte Datenbasis und geschlossene SaaS-Systeme bremsen KI-Initiativen hier systematisch aus. Ein souveräner KI-Ansatz erfordert andere Vorgehensweisen.

Abfrage statt Einbettung

Viele frühe KI-Versuche scheitern daran, dass Unternehmensdaten direkt in Sprachmodelle eingebettet werden. Dies birgt nicht nur massive Datenschutzrisiken, sondern führt unweigerlich zu gefährlichen Halluzinationen. Ein Large Language Model (LLM) ist primär ein Sprachwerkzeug, keine relationale Datenbank.

Agentic AI auf Open-Source-Basis

Die Lösung liegt im Einsatz sogenannter „Agentic AI“ in Kombination mit quelloffenen Sprachmodellen (Open-Source-LLMs), die lokal und sicher in der eigenen (Cloud-)Umgebung betrieben werden. Die Daten verlassen die unternehmenseigene Infrastruktur zu keinem Zeitpunkt. Noch wichtiger: Die KI wird so konfiguriert, dass sie die Daten nicht auswendig lernt, sondern als intelligenter Agent agiert. Sie nutzt ihr semantisches Kontextverständnis, um bei Bedarf gezielt direkte Abfragen (beispielsweise über SQL) an die offenen Datenformate des Lakehouses zu stellen.

„Talk-to-your-data“ in der Praxis

Durch die direkte Anbindung an die zentrale Datenplattform liefert das System harte, verifizierbare Fakten statt stochastisch berechneter Wahrscheinlichkeiten. Dieser Ansatz ermöglicht völlig neue Geschäftsprozesse: Fachbereiche ohne tiefe Programmier- oder SQL-Kenntnisse können künftig im direkten Dialog mit ihren Daten interagieren. Komplexe Analysen und Reportings lassen sich per natürlicher Sprache automatisieren und verlässlich abfragen.

Damit dieser reibungslose Dialog zwischen Business-User, KI-Agent und Datenplattform jedoch nicht im Chaos endet, muss die KI exakt verstehen, wie die Daten strukturiert sind und welche semantische Bedeutung sie haben. Technologie allein reicht hierfür nicht aus, womit wir beim oft unterschätzten Kernstück der Datensouveränität angelangt sind.

Data Governance: Vom Regelwerk zum strategischen Enabler

Auch bei Datenplattformen bewahrheitet sich immer wieder eine Erkenntnis, die auch in vielen anderen Bereichen eine gewisse Allgemeingültigkeit erreicht hat: Technologie allein ist kein Garant für Erfolg. Ein modernes Data Lakehouse und fortschrittliche Agentic AI laufen ins Leere, wenn die zugrunde liegende Datenqualität mangelhaft ist oder die semantische Bedeutung der Daten unklar bleibt. An diesem Punkt wandelt sich Data Governance von einem oft ungeliebten Kontrollinstrument zu einem echten strategischen Enabler.

Wenn ein KI-Agent eine Benutzereingabe in eine präzise Datenbankabfrage übersetzen soll, benötigt er mehr als nur Zugriff auf Tabellen. Er benötigt Kontext. Ohne ein gepflegtes Business Glossary, klare Metadaten und definierte Verantwortlichkeiten (Data Ownership) ist das Risiko hoch, dass die KI zwar syntaktisch korrekte, aber fachlich falsche Ergebnisse liefert. „Garbage in, garbage out“ gilt im Zeitalter der Künstlichen Intelligenz mehr denn je.

Eine saubere Governance-Struktur löst dieses Problem an der Wurzel:

Zentrale Wahrheit, dezentrale Nutzung

Durch klare Qualitätsregeln und definierte Datenprodukte entsteht ein Fundament des Vertrauens. Fachbereiche können sich darauf verlassen, dass die bereitgestellten Informationen korrekt, aktuell und rechtssicher sind.

Echte Demokratisierung

Erst dieses Vertrauen ermöglicht Self-Service-Analytics. Wenn die Leitplanken der Governance feststehen, können Daten im gesamten Unternehmen demokratisiert und sicher zur Verfügung gestellt werden, ohne dass die IT-Abteilung jeden einzelnen Report manuell freigeben muss. Auch KI-Ergebnisse können so ohne Kopfschmerzen bezüglich Halluzinationen oder rechtliche Bedenken angenommen und weiterverwendet werden.

Compliance als Standard

Mit Blick auf strenge europäische Regulierungen wie die DSGVO oder den AI Act stellt eine integrierte Governance sicher, dass Zugriffsrechte, Anonymisierung und Nachvollziehbarkeit (Data Lineage) von Beginn an in der Architektur verankert sind.

Wer die Verantwortung für seine Daten auf diese Weise intern übernimmt, schafft die zwingende Voraussetzung für Skalierbarkeit.

Wie gelingt die Migration?

Die Vorteile offener Standards und einer souveränen Architektur sind einleuchtend. Dennoch scheuen viele IT-Verantwortliche den Schritt aus dem Vendor-Lock-in, weil sie ein riskantes, jahrelanges IT-Großprojekt befürchten. Doch die Befreiung aus geschlossenen Systemen erfordert keinen riskanten „Big Bang“.

Erfolgreiche Migrationsprojekte in der Praxis beweisen, dass der Wechsel zu einer offenen souveräneren-Architektur agil und inkrementell erfolgen kann:

Use-Case-getriebene Migration

Anstatt das gesamte historische Data Warehouse auf einmal abzulösen, wird die neue, offene Plattform parallel aufgebaut. Die Migration erfolgt anhand priorisierter, geschäftskritischer Anwendungsfälle.

Schneller Return on Investment (ROI)

Indem zunächst diejenigen Datenbereiche migriert werden, die den höchsten sofortigen Mehrwert bieten, zum Beispiel zur Umsetzung neuer Use-Cases, welche zuvor unmöglich schienen, refinanziert sich der Umbau oft schon während der Projektlaufzeit.

Risikominimierung

Dieser schrittweise Ansatz stellt sicher, dass das Tagesgeschäft (Reporting und laufende Analysen) völlig ungestört weiterläuft, während im Hintergrund das zukunftssichere Fundament iterativ wächst.

Der Übergang zu offener Software und herstellerunabhängigen Datenformaten ist somit kein IT-Selbstzweck, sondern eine planbare, risikoarme Investition in die unternehmerische Handlungsfähigkeit.

Souveränität aktiv gestalten

Wahrlich souverän ist nur das Unternehmen, das die Architektur, die Qualität und den Verbleib seiner Daten vollständig kontrolliert und sich dieser Verantwortung bewusst ist. Wenn Sie sich aus der Abhängigkeit lösen, teure Lizenzmodelle hinter sich lassen und eine rechtssichere Basis für Künstliche Intelligenz schaffen wollen, führt der Weg unweigerlich über offene Standards.

Übernehmen Sie wieder die volle Verantwortung für Ihre Daten. Verwandeln Sie Ihre IT-Infrastruktur von einem reinen Kostenfaktor in den entscheidenden Wettbewerbsvorteil Ihrer Branche.

Als Experten für Big Data und die Entwicklung moderner Datenplattformen unterstützt Scalefree europäische Unternehmen dabei, diesen Weg erfolgreich zu gehen. Wir planen und realisieren End-to-End Daten- und KI-Lösungen jeder Skalierung, von der strategischen Architekturberatung bis zur Implementierung, sowie Agentic AI.

Sind Ihre Daten bereit für die Zukunft?

Lassen Sie uns in einem unverbindlichen Gespräch Ihre aktuelle Architektur beleuchten. Erfahren Sie, wie ein maßgeschneidertes Data Lakehouse auf Basis offener Standards Ihre Datensouveränität dauerhaft sichern kann.

Kostenloses Erstgespräch vereinbaren

Ole Bause In Artificial Intelligence, Beginner

Predictive Analytics on the Modern Data Platform

Predictive Analytics on the Modern Data Warehouse

From BI to AI: Operationalizing Predictive Analytics where your Data already lives

Traditional Business Intelligence and reporting are incredibly good at telling what happened yesterday. How much revenue was generated last quarter? How many users logged in this week? But while understanding the past is important, today’s businesses need to know what will happen tomorrow.

This is where Predictive Analytics comes in. At its core, predictive analytics simply uses historical data to forecast future outcomes. Instead of asking how many customers canceled last month, predictive analytics asks:

“Which specific customers are most likely to cancel next week?”

Many organizations understand this value and eagerly hire data scientists to build these models. Yet, time and time again, these predictive initiatives fail to make it out of the PoC phase and into daily business operations because of how teams and data architectures are fundamentally structured.

Predictive Analytics on the Modern Data Platform

Learn how to bridge the gap between your data platform and actionable AI by building predictive models directly where your business data lives. This webinar covers practical strategies for transforming warehouse data into features, deploying models, and automating the flow of insights back into your daily operational workflows. Learn more in our upcoming webinar on March 17th, 2026!

Watch Webinar Recording

In this article:

The Problem: The "Two Silos" of Data
The Solution: Bring the Machine Learning to the Data
Two Architecture Approaches
- Pattern 1: In-Warehouse Machine Learning (Democratizing ML)
- Pattern 2: The Data Platform as a Feature Store (The Hybrid Approach)
Example: Predicting Customer Churn
Conclusion

The Problem: The “Two Silos” of Data

In many companies, Data Engineering and Data Science exist in two entirely different worlds.

Data Engineers and Data Warehouse Developers spend their days building the Modern Data Platform. They carefully extract, clean, conform, and govern data from dozens of sources to create a “Single Source of Truth.” When a business analyst looks at a revenue dashboard, they know they can trust the numbers because the data platform enforces strict business logic.

Data Scientists, on the other hand, often work in isolated environments like standalone Jupyter notebooks. Because they need massive amounts of data to train their machine learning models, they often bypass the data platform entirely, pulling raw, unstructured data directly from a data lake.

This disconnect creates some challenges:

Duplicated Effort: Data Scientists waste up to 80% of their time cleaning and prepping raw data, work the Data Engineering team has already done in the platform
Inconsistent Metrics: Because models are built on raw data, a model’s definition of “Active Customer” might completely contradict the official definition used by the in the data platform
The “Wall of Production”: A model might look perfectly accurate on a data scientist’s laptop, but because it relies on disconnected, ungoverned data pipelines, integrating its predictions back into the daily workflows of sales or support teams becomes an IT nightmare
Exclusivity: Data analysts are often limited to classic descriptive analytics which slows time-to-insight. The optimal solution is to democratize data science, empowering analysts to implement predictive use cases directly.

The Solution: Bring the Machine Learning to the Data

The fix to this problem requires a fundamental shift in how we think about machine learning architecture. Instead of moving data out of governed systems to feed external ML models, we need to bring the ML workflows more closer to the data.

By positioning the Modern Data Platform as the foundation for predictive analytics, you ensure that every prediction is built on the same trusted, cleansed, and governed business data used for your daily reporting. The Data Platform becomes the Feature Store, a centralized hub where data is prepared once and used everywhere, whether for a BI dashboard or training a predictive model.

When the data platform serves as the single source of truth for both analysts and algorithms, magic happens. Data science teams stop wrestling with raw data pipelines, data engineering teams maintain governance, and the business gets predictions they can actually trust and operationalize.

Two Architecture Approaches

So, how do we actually bring the machine learning to the data? There isn’t a one-size-fits-all answer. Depending on the team’s skillset and the complexity of the models, there are typically one of two foundational patterns adopted:

Pattern 1: In-Warehouse Machine Learning (Democratizing ML)

Modern cloud data platforms have evolved beyond just storing and querying data, many now have machine learning engines built directly into them.

How it works: Using standard SQL, Data Analysts and Analytics Engineers can train, evaluate, and deploy models entirely inside the data platform (for example BigQuery ML, Snowflake Cortex or Databricks)
The Benefit: This radically democratizes predictive analytics. You don’t need to know Python or manage complex infrastructure to build a model. If you know SQL, you can generate predictions using the exact same tables you use for your BI dashboards
The Trade-off: While perfect for standard tasks like regression or classification, you are limited to the specific algorithms supported by the data platform

Pattern 2: The Data Platform as a Feature Store (The Hybrid Approach)

For organizations with dedicated Data Science teams building highly complex or custom models, the data platform takes on a different role: the Feature Store.

How it works: Data Scientists continue to work in their preferred external ML platforms (like Vertex AI, Databricks, or other). However, instead of pulling messy data from a data lake, they connect directly to the data platform to pull curated, business-approved data (“features”) for training
The Benefit: Data Scientists retain maximum flexibility to use advanced Python libraries and deep learning frameworks, while ensuring the models are trained on governed, accurate data
The Trade-off: It requires a bit more orchestration to manage the pipeline between the data platform and the ML platform, and ensuring predictions are accurately written back to the data platform

Example: Predicting Customer Churn

To understand the benefits of these approaches, let’s look at a classic business challenge: Customer Churn Prevention.

Imagine a SaaS company trying to figure out which customers are likely to cancel their subscriptions. In a siloed environment, predicting this is can be a messy, manual science project. But on a modern data platform, it becomes an automated operational workflow:

The Foundation (Data): Because of the Data Engineering team’s work, the data platform already contains all necessary historical information of the customer. CRM data (company size), financial records (billing history), product logs (login frequency), and Zendesk tickets (recent complaints) are all cleaned, joined, and sitting in governed tables including a full history of changes
The Prediction (Modeling): An analyst uses In-Warehouse ML (Pattern 1) to run a classification model against this historical data. The model identifies the hidden patterns of a churning customer and generates a “Churn Risk Score” between 0 and 100 for every active user
The Operationalization (Action): This is the crucial step. The predictions aren’t left in a notebook. The risk scores are written directly back into a new table in the data platform. Through reverse-ETL, these scores can be automatically synced to the CRM as well as dashboards and reports can easily be built on top of the results.

Conclusion

Predictive analytics shouldn’t be an isolated science experiment. It should be a living, breathing part of your operational reality. By treating your modern data platform as the foundation for your machine learning workflows, you eliminate data silos, empower your analysts, and ensure your predictions are built on the trusted business data that matters most.

It is time to operationalize predictive insights where your business data already lives.

Want to see how this works in practice?

Join our upcoming webinar: Predictive Analytics on the Modern Data Platform. We will explore how to build and run predictive analytics directly on top of your data platform using trusted, governed business data as the foundation. You’ll learn practical patterns for turning warehouse models into features, training and deploying predictions, and integrating results back into reporting and operational workflows. Join me on March 17th.

Ole Bause In Artificial Intelligence, Data Warehouse, Intermediate

Unlock the Intelligence Layer: LLMs in Data Warehousing and the Future of Your Data

“Stop writing complex SQL, start talking to your data?”

This provocative question highlights a growing shift in how we interact with data. For years, getting answers from a Data Warehouse meant writing SQL queries or relying on pre-built dashboards.

For many organizations, their data platforms remain underutilized because accessing insights still requires writing code or navigating complex dashboards. It’s time to go beyond static reports and unlock a true intelligence layer on top of your data warehouse. Recent advances in Large Language Models (LLMs) and Natural Language Processing (NLP) are making data warehouses smarter, faster, and easier to use for everyone. In this article, we’ll explore how LLMs can transform the way you interact with your data – from using plain English queries instead of SQL, to AI-driven discovery of hidden insights, to enriching your data pipelines – and why this shift represents the future of data analytics.

Unlock the Intelligence Layer: LLMs in Data Warehousing and the Future of your Data

Unlock your data warehouse’s full potential! This webinar reveals how Large Language Models and Natural Language Processing are transforming data interaction, empowering everyone to effortlessly translate plain language into SQL, enable AI-driven data discovery, and deliver actionable insights to every stakeholder. Register for our free webinar, August 12th, 2025!

Watch Webinar Recording

In this article:

From Complex SQL to Conversational Queries
AI-Augmented Data Discovery and Insights
LLMs in Your Data Pipeline: Enrichment and Efficiency
Conclusion

From Complex SQL to Conversational Queries

Business users often depend on data engineers or analysts to fetch answers, creating bottlenecks in decision-making. Even data professionals themselves spend considerable time writing and optimizing SQL, rather than interpreting results. What if anyone could simply ask the data warehouse a question in plain language and get the answer? This is the promise of LLMs as an “intelligence layer”, a layer that bridges complex datasets and human comprehension. Advanced LLMs can understand a user’s question or request and generate the appropriate SQL queries on the fly.

This technology (often called Text-to-SQL or Natural-Language-to-SQL or NL2SQL) has rapidly evolved and major technology players have already taken note. For example, Databricks introduced a Natural Language Query feature (LakehouseIQ) to let users ask questions of their Lakehouse, and Snowflake is also exploring LLM-driven query capabilities.

Imagine asking your data warehouse in plain English: “What were our top-selling products last quarter by region?”. This text input is passed into a LLM, often enriched by company-specific data via RAG and then the system translates that into a correct, optimized SQL query that retrieves the answer.

Of course, translating natural language to SQL at an enterprise scale isn’t trivial. Complex schemas, ambiguous user input, and security considerations mean the LLM has to be both smart and careful. Uber has built such an AI system that works on an enterprise scale level.

Uber’s QueryGPT is an NL2SQL system that uses a multi-step, RAG-based pipeline combining LLMs with retrieval and agent modules. It fetches context via similarity search over a vector database of example queries and schema information for SQL generation. To manage Uber’s vast data ecosystem, QueryGPT employs specialized agents:

an Intent Agent classifies requests by business domain
a Table Agent suggests tables for the query
a Column Prune Agent trims irrelevant columns to reduce prompt length. The LLM then produces the SQL query and an explanation.

This layered design allows QueryGPT to handle large schemas and reliably generate complex multi-table queries. It’s a hybrid architecture where multiple transformer calls specialize in sub-tasks, enabling scalable, accurate NL2SQL as a production service, saving thousands of Uber employees significant time by mid-2024.

AI-Augmented Data Discovery and Insights

Beyond simply fetching results for user queries, LLMs can augment data discovery by revealing insights that users might not have explicitly asked for. Traditional dashboards show you what is happening, but a smart LLM-based system can tell you why it’s happening and highlight patterns you might not notice. This is often called augmented analytics – using AI to automatically find important correlations, trends, outliers, and drivers in your data.

LLMs excel at interpreting data outputs and providing additional context. For example, rather than just displaying a chart or a table, an LLM can generate a written summary pointing out key trends or anomalies. They can explain which metrics are up or down and suggest potential reasons (for instance, detecting that “conversion rates dipped in July, possibly due to seasonality or inventory issues”), enabling quicker and more informed decision-making.

Another area where LLMs can significantly reduce manual effort is in the creation and maintenance of data catalogs. Documenting data models, table structures, and especially individual column descriptions is often time-consuming and easily skipped due to missing resources, despite being crucial for an effective use and accessibility of the data. LLMs can automate large parts of this process by generating descriptions based on data profiling, SQL logic, naming conventions, and metadata.

dbt Cloud has recently released their dbt Copilot AI Agent that supports the developer in various ways, for example by letting the AI analyzing the SQL code and schema metadata to automatically generate model and column descriptions.

LLMs in Your Data Pipeline: Enrichment and Efficiency

LLMs don’t just enhance how users interact with the Data Warehouse; they can also improve the data itself and the efficiency of data engineering processes. In modern ELT (Extract-Load-Transform) pipelines, a lot of time is spent cleaning, enriching, and preparing data for analysis. Here, LLMs offer new tools to automate and augment these steps.

One promising use case is the semantic enrichment of data. Large Language Models have absorbed a vast amount of world knowledge and language patterns, and they can use that to fill gaps or add context to your raw data. For example, imagine you have a dataset of customer feedback where each entry is a text comment. An LLM could automatically classify the sentiment of each comment (positive/negative), extract key themes, or even generate a summary of common issues. In this way, unstructured data becomes structured insights without manual effort. The image below illustrates how an LLM is integrated into a data pipeline: text inputs from a CustomerFeedback table are passed to an OpenAI API endpoint, where the model returns structured sentiment labels that are then stored back in the database.

Large Language Models Sentiment Analysis

In a practical case study, LLMs were used to enrich an academic dataset by inferring missing attributes (like guessing a person’s gender from their name with high accuracy), which outperformed dedicated API services. This showcases how LLMs can bring external knowledge and reasoning to enhance your data.

Another area is metadata enrichment and semantic enrichment of unstructured data. Enterprise data is often filled with cryptic column names and jargon that prevents usability. LLMs can intelligently expand abbreviations and annotate fields with business-friendly descriptions. For instance, an LLM-driven catalog might take a column labeled “CUST_ID” and annotate it as “Customer Identifier, unique ID for each customer record”.

LLMs can also assist in the coding and transformation process itself. Data engineers can leverage LLMs to generate boilerplate code or SQL for transformations, document pipeline logic in plain English, or even detect anomalies and data quality issues through pattern analysis. By automating tedious parts of data preparation and providing AI-generated suggestions, LLMs free up engineers to focus on higher-level architecture and problem-solving.

Conclusion

While the promise of an LLM-powered intelligence layer is exciting, it’s important to approach it with a clear strategy. Successful implementation requires considering a few key challenges and best practices. Data quality and governance are more crucial than ever. If your underlying data is inaccurate or poorly structured, the AI’s answers will be unreliable. As the saying goes, “garbage in, garbage out.”

Ensuring clean, well-organized data (and maintaining a robust data governance program) will help the LLM produce meaningful and correct insights. Additionally, organizations may need to fine-tune or configure their LLMs to understand industry-specific terminology or business context. This reduces the chance of the AI misinterpreting what a user asks or generating an incorrect query.

Privacy and security are another important consideration. If your data includes sensitive information, you must ensure that any AI tool accessing it complies with your security requirements. This might involve using self-hosted models or secure APIs, and setting up proper access controls.

The dream of a self-service analytics experience: “just talk to the data and get answers” is quickly becoming a reality. This evolution may redefine roles (enabling analysts and engineers alike to focus on higher-value tasks) and open up analytics to a wider audience than ever before. It’s an exciting time to be a data professional, but also one that demands staying informed and ready to adapt.

Ole Bause In Artificial Intelligence, Beginner, Data Warehouse

AI in Data Warehousing: Fundamental Principles and Applications

AI in Data Warehousing

The ability to efficiently store, manage, and analyze data has become a critical aspect for any organization. The field of Data Warehousing is an integral part of the process by providing structured, centralized storage for large volumes of data and is an important element of business intelligence.
However, the increasing volume and complexity of data is becoming increasingly challenging to manage. That said, AI is already shaping many processes in a wide variety of areas, including data warehousing.

This article explores the basic principles and applications of artificial intelligence and how AI can be integrated into an Enterprise Data Warehouse, by using it to enhance the design and operation of data warehouses, as well as enabling the development of more challenging data science applications like machine learning or predictive analytics.

AI in Data Warehousing: Fundamental Principles and Applications

This webinar delves into the essential principles of Artificial Intelligence (AI) and its transformative role in data warehousing. We explore how AI and Machine Learning (ML) not only enhance the construction and operation of data warehouses but also leverage the vast amounts of data stored within them for developing powerful applications, e.g. for predictive analytics.

You will gain an understanding of the foundational AI technologies driving these advancements and how they intersect with data science and machine learning to optimize data storage, improve decision-making, and unlock new business insights. This webinar provides valuable perspectives on integrating these technologies into your data warehousing practices.

Watch webinar recording

In this article:

Foundational Principles of AI
The Role of AI in Building Data Warehouses
- Enhancing Design and Structure
- Automating Data Integration, Cleaning, and Transformation
Using a Data Warehouse for AI Applications
- Enhancing AI Capabilities with Data Vault
Conclusion

Foundational Principles of AI

Let’s start by categorizing the term artificial intelligence, as well as other terms such as machine learning, deep learning, or generative AI, in order to clarify the context and what exactly is hidden behind these terms.

Artificial Intelligence is now more of a generic term and represents a broad field. In general, the term AI encompasses techniques that allow computers to emulate human behavior, enabling them to learn, understand language, make decisions, recognize patterns, and solve complex problems in a way that is similar to human intelligence.

Machine Learning (ML) as a Subset of AI

Machine Learning is a subset of the field of artificial intelligence and, even if less present as a term, one of the most important. Machine Learning uses advanced techniques and algorithms to recognize patterns in large amounts of data, allowing machines to learn and adapt autonomously to make inferences or predictions based on the data. Unlike traditional programming where tasks are executed based on clear and defined instructions, machine learning relies on statistical analysis based on input data to output data values that fall within an expected range. Essentially, machine learning enables computers to learn and interpret data without being explicitly programmed for each individual case.

Deep Learning (DL) – A Deeper Dive into ML

Deep Learning is a more specialized sub-area of machine learning & AI and uses deep neural networks to perform in-depth data processing tasks to recognize complex patterns.

Deep learning uses many layers to extract high-level features from raw data as input, in a sense simulating the way a human brain works.

In this respect, deep learning is particularly strong in processing very large amounts of data, using it to learn complex patterns in order to solve a wide variety of tasks. Well-known examples of this are tasks such as image or voice recognition, which every smartphone is capable of.

Generative AI – The Creative Aspect of Deep Learning

Generative AI is a term that has recently become more widely known, especially due to ChatGPT. Generative AI is a sub-area of deep learning and includes deep learning models that generate new content, such as text, images, code, or even videos, based on data on which they have been trained on. They are able to generate novel outputs without explicit instructions that do not directly replicate the training data. This opens up completely new possibilities such as generating large texts, complex images, or even music.

The Role of AI in Building Data Warehouses

AI is changing everything, including data warehousing. As such, it can help to enhance the efficiency and effectiveness of your data warehouse from the ground up. From the design and structure to the ongoing data management processes, there are many opportunities where AI can help.

AI has the power to address a data warehouse’s biggest challenges: performance, governance, and usability. This is data intelligence and will revolutionize the way you query, manage, govern, and visualize your data.

Enhancing Design and Structure

The architecture of a data warehouse is critical as it needs to support efficient data querying and scalability while maintaining performance. By analyzing usage and query patterns, AI algorithms can suggest the most effective data models and indexing strategies. This not only speeds up the retrieval of information but also ensures more agile data handling when scaling or integrating new data sources.

Automating Data Integration, Cleaning, and Transformation

By letting AI take over low-level tasks, data engineers can focus on higher-level tasks such as designing data models, training machine learning algorithms, and creating data visualizations. For example, The Coca-Cola Company uses AI-powered ETL tools to automate data integration tasks across its global supply chain to optimize sourcing and procurement processes.

But AI can also support the actual developers in their work, enabling them to work faster and more efficiently. Developers can use AI to debug problems in their code faster by using AI-based code generation and analysis, such as Github Copilot, which completes, refactors, and debugs code in real time directly in the IDE.

The automation of performance tuning of data warehouse workloads can also be improved with predictive optimizations, which can save a considerable amount of costs.

AI can also scale and automate governance using automated tagging, documentation, and natural language search across all data and assets in an organization.

Right up to the possibility that business users could use natural language to interact with the data, ask questions, and build dashboards, the possibilities are endless and we are only at the very beginning of these developments.

Using a Data Warehouse for AI Applications

The nature of a data warehouse, a structured and centralized repository that aggregates data from multiple sources within an organization, makes it a perfect foundation for building and training AI applications. AI models require large amounts of data with as much variability as possible that are well structured and have a high data quality, which a data warehouse can perfectly provide for an organization to train its own AI model.

For example, a data warehouse in a retail context might store years of customer purchase history, demographics, and product information. Data that can be used to train AI models to predict future buying trends or recommend products effectively.

Enhancing AI Capabilities with Data Vault

Data Vault 2.0 in particular sets the exact right conditions for training your own AI models by leveraging the structured, reliable data framework that Data Vault 2.0 provides.

Here’s are some examples how this integration can benefit AI-driven analytics:

Improved Data Quality: The organized and auditable structure of Data Vault 2.0 ensures higher data quality and consistency, which is important for training accurate AI models. Clean, well-structured data models reduce the time spent on data preparation and increase the reliability of AI predictions.
Enhanced Historical Analysis: The comprehensive historical data captured by Data Vault 2.0 allows AI models to perform more accurate trend analysis and forecasting. This capability is especially valuable in sectors like finance and retail, where understanding long-term trends can significantly impact strategic decisions.
Data Reliability and Lineage: Data Vault 2.0’s robust framework ensures data reliability through its unique architecture that captures data from various sources while maintaining its lineage. This means every piece of data in the system can be traced back to its origin, providing transparency and trust in the data used for AI models.

Conclusion

Integrating artificial intelligence (AI) into data warehousing significantly enhances the efficiency and effectiveness of data storage, management, and analysis. By applying AI techniques, organizations can automate data integration, cleaning, and transformation processes, leading to more accurate and timely insights. Furthermore, AI-driven optimization of data warehouse design and structure ensures improved performance and scalability. Embracing AI in data warehousing not only streamlines operations but also empowers businesses to leverage advanced analytics, such as machine learning and predictive analytics, thereby unlocking deeper insights and fostering informed decision-making.

Ole Bause In Data Warehouse, Expert

Mastering Metadata: Data Catalogs in Data Warehousing with Datahub

Mastering Metadata in Data Warehousing

In today’s data-driven world, it is essential to be able to manage and organize large amounts of data in an efficient way. Businesses across all industries are forced to contend with more data than ever before. The introduction and development of an enterprise data warehouse in a company naturally plays a central role, but does not solve a major challenge: How to effectively organize and manage the data, especially metadata, in an Enterprise Data Warehouse? This is where the concept of data catalogs comes into play and where tools like Datahub become essential.

A data catalog serves as a comprehensive inventory of data assets in an organization, providing context, annotations, and metadata to facilitate the understanding and discovery of data. It’s like a map to your data, helping users navigate the complex data landscape to find the exact data they need.

A data catalog can help users to understand where to find specific data in the data warehouse that fits their needs and to investigate where it came from, as well as how it might be connected to other data. This can greatly simplify tasks like data analysis and reporting, making the data warehouse more accessible and usable for everyone in the organization.

Mastering Metadata: Data Catalogs in Data Warehousing with DataHub

Don’t miss our upcoming webinar about data catalogs! This session will explore in detail the critical role of data catalogs in data warehousing, with an exclusive focus on the powerful tool DataHub. You’ll gain practical insights on enhancing data discovery, metadata management, data lineage, and data governance. Sign up today and transform your data management strategies into a competitive advantage.

Watch webinar recording

In this article:

Understanding Data Catalogs
- What is a Data Catalog?
- Role of a Data Catalog in Data Warehousing
Introduction to DataHub
- Key Features and Capabilities of DataHub
Conclusion

Understanding Data Catalogs

What is a Data Catalog?

In general, a data catalog is like a metadata inventory, which consists of organized and structured metadata regarding all data assets in an organization. It is a central place where all this metadata can be stored, combined, and categorized, which makes it a lot easier to discover and understand the corresponding data, for example in a data warehouse. A data catalog also has search functionalities to find specific data from the available indexed datasets. It serves like a single source of truth of your metadata, enabling users to trust the data they’re using for their analyses or business decisions.

Role of a Data Catalog in Data Warehousing

In the context of data warehousing, a data catalog brings a lot of benefits. It provides a way to explore and search all data stored in the data warehouse. Technical users, as well as Business Users, can discover relevant data, understand its context, and ensure it is up-to-date, reliable, and accurate. The following figure shows where data catalogs in data warehousing with Data Vault 2.0 take place. A Data Catalog should cover the entire Enterprise BI Solution. This also applies, for example, to a data lake, if available, and to the information delivery layer.

Now that we’ve understood what a data catalog is, let’s delve into how each component plays a part in a data catalog and explore how a tool like DataHub can assist organizations in these tasks.

Introduction to DataHub

In the world of data catalogs, DataHub stands out as an increasingly popular choice for many businesses. DataHub is a growing open-source software developed by LinkedIn to address its growing need for a more dynamic and scalable data management tool. It was created in part due to the fact that the existing tools were not sufficient with LinkedIns expanding needs.

As LinkedIn grew, so did its data volume, variety, and velocity. Recognizing the need for a more efficient way to manage its data, LinkedIn built and introduced DataHub in 2020. Open-sourcing DataHub allowed other organizations to benefit from this advanced tool, and it has since been adopted by many businesses looking for a modern, scalable data catalog solution.

DataHub supports both push-based and pull-based metadata ingestion, including a wide range of integrations for example Airflow, BigQuery, Databricks, dbt, Hive, Kafka, Looker, MSSQL, MongoDB, Oracle, S3, PowerBI, Snowflake, Spark, and much more. You can find a full list here. This gives datahub the ability to combine and show metadata of the same data from multiple sources, for example, a dbt model definition, and if the tests were running successfully, right next to the database schema and stats for all columns.

Key Features and Capabilities of DataHub

DataHub, as a metadata platform, goes beyond traditional data catalogs. DataHub offers all important features and capabilities:

1. Scalability: DataHub is designed to handle metadata from thousands of datasets, which makes it a great choice for large organizations.

2. Flexible and Extensible Data Model: The technical data model behind this tool is designed to be customizable and expandable to allow organizations to customize it to their specific business requirements

3. Powerful Search and Discovery: Leveraging Elasticsearch, DataHub offers robust search functionality that enables users to discover datasets quickly based on various attributes, such as the data’s origin, schema, and usage.

4. Rich Metadata: Unlike traditional data catalogs, DataHub captures and presents a wide variety of metadata, including data lineage, operational metadata, and business metadata. This gives users a comprehensive understanding of their data.

5. Data Lineage and Relationships: DataHub automatically captures and visualizes data lineage, showing how data flows through various systems. It also displays relationships between datasets, allowing users to understand how different data assets interact with each other.

Conclusion

Using a data catalog comes with several benefits:

Enhanced Data Discovery: With the search and categorization capabilities of a data catalog, users can quickly find the exact data they need without having to comb through large datasets.
Improved Data Understanding: The metadata in a data catalog provides users with necessary context about the data, making it easier to interpret and use correctly.
Better Compliance and Governance: A data catalog supports data governance initiatives by ensuring data is consistent, accurate, and compliant with relevant regulations.
Increased Trust in Data: By providing transparency into data lineage, a data catalog helps build trust in the data by allowing users to see its history and verify its accuracy and reliability.
Time and Resource Efficiency: By making it easier to locate and understand data, a data catalog can save the company resources, thus speeding up data-driven activities and reducing the burden on data management teams.

In conclusion, DataHub provides a flexible, feature-rich, and all-encompassing option for data catalogs in a data warehousing environment. By providing powerful features for data discovery, metadata management, data lineage, and data governance, it enables businesses to extract maximum value from their data.

If you’re interested in learning more about data catalogs, watch the recording here for free.

Ole Bause In Data Tools

Data Vault 2.0 with DBT – Part 2

Data Vault 2.0 with dbt

In the first part of this blog series we introduced you to dbt. Now let’s take a look at how you can implement Data Vault 2.0 with dbt and what advantages this offers. If you don’t know the first part yet, you can read it here.

In this article:

Dbt Models
Data Vault 2.0 and Macros
Conclusion

Dbt Models

dbt provides the ability to create models and generate and execute SQL dynamically out of these models. This allows you to write your data transformations in models using SQL and reusable macros powered by Jinja2 to run your data pipelines in a clean and efficient way. However, the most important part for the Data Vault use case is the ability to define and use those macros.

But first we should clarify how models work in dbt.

This tool handles the compilation and execution of models written using SQL and the Jinja macro language. Each model consists of exactly one SQL SELECT statement. Jinja code is translated into SQL during compilation.

This figure shows a simple model in dbt. A big advantage of Jinja is the possibility to generate sql programmatically, for example with loops and conditions. Also, through the ref() functions, dbt understands the relationships between the models and builds a dependency graph. This ensures that the models are built in the correct order and that the data lineage is documented. A lineage graph may look like this:

The materialization of models can be defined at different configuration levels. This allows fast prototyping with views and switching to materialized tables if necessary for performance reasons.

Data Vault 2.0 and Macros

But how can we implement Data Vault 2.0 with dbt? The most important part for using Data Vault 2.0 is the ability to define and use macros. Macros can be called in models and then generate into that macro additional SQL or even the entire SQL code.

For example, you could create a macro to generate a hub that gets the source/staging model as an input parameter, as well as the specification of the columns for the business key(s), load date and record source. The sql code for the hub is then generated dynamically from this. The advantage of this is that a change to the macro would directly affect each individual hub, which greatly improves maintainability.

At this point, you also gain huge benefits from the active open-source community around dbt. There are many open-source packages with which it can be extended.

There are also already some packages that are perfect for using Data Vault 2.0 with dbt.

For example, our own open-source package DataVault4dbt, developed and actively maintained here at Scalefree, provides a comprehensive set of dbt macros to translate a Data Vault model “on paper” into actual tables and views—such as Hubs, Links, Satellites, and more. The package is actively used in real-world projects and helps enforce best practices for a modern, audit-friendly Data Vault 2.0 implementation.

To explore all features and macro parameters in detail, check out the documentation.

The only thing you need in your model, for example for a hub, is just one macro call:

{%-

hub(src_pk, src_nk, src_ldts, src_source, source_model) 

-%}

With the parameters of the macro call you define the source table where the columns can be found (source_model) and the column names for the hash-key (src_pk), business key(s) (src_nk), load date (src_ldts) and the record source (src_source) column. When the model and the macro(s) defined in the model are executed, the SQL gets compiled and processed on the database system.

The metadata needed can for example be defined in variables with jinja directly in the model:

What you can also see is that this tool provides different options for the materialization. The incremental materialization for example, will load an entity as a table on an incremental basis.

When the model is executed, dbt generates the whole sql out of the macro and decides how the records are loaded. If the hub table does not exist, yet it is created and all records are loaded, if it already exists, the table is loaded incrementally.

For people who tried or managed to implement a Data Vault with “vanilla” SQL, you will realise that this is a real game-changer. The team can now focus entirely on the Data Vault design itself. Once the metadata is identified, dbt, along with your macros, can take care of the entire logic.

Openly available packages can add basic Data Vault 2.0 principles to dbt and therefore allow users to quick-dive into Data Vault implementation. Dbt’s general openness allows you to adjust all macros, for example, to your company or project specific Data Vault flavor to meet your technical and business requirements.

It is important to note that many of the currently available dbt packages for implementing Data Vault 2.0 deviate from the official standards in some details. Our own open-source package DataVault4dbt, developed and actively maintained here at Scalefree, addresses this gap by supporting all core Data Vault 2.0 entities and the latest best practices. The package is already used in real-world projects to help ensure a modern and audit-friendly implementation.

Conclusion

Integrating Data Vault 2.0 with dbt streamlines the data warehousing process by leveraging dbt’s capabilities to define models and macros, enabling efficient and dynamic SQL generation. This approach allows teams to focus on Data Vault design, with dbt handling the underlying logic.

Our open-source package DataVault4dbt brings these advantages to real-world projects, offering a reliable and standards-aligned way to build Hubs, Links, Satellites, and more.

Ole Bause In Data Tools

Data Vault 2.0 with DBT – Part 1

Data Vault 2.0 with dbt

This article focuses on the benefits of using Data Vault 2.0 with dbt, and the importance of choosing the correct implementation tools. Data is an important asset in the decision-making process. As we have previously discussed in another post, Data Vault 2.0 is the right choice when the goal of an enterprise data warehouse is to have fully historized and integrated data. Additionally, it is also better suited to instances in which data from many source systems needs to be combined. You can find the previous blog post here.

While Data Vault 2.0 focuses on the “what”, there are many options for the “how” of technically translating a Data Vault model into physical tables and views in the enterprise data warehouse, as well as for orchestrating and loading/processing the procedures. And this is where Data Vault 2.0 with dbt creates an effective solution.

In this article:

About dbt
How dbt works
How does Data Vault 2.0 with dbt work?
Conclusion

About dbt

This data build tool transforms your data directly into your data warehouse. For reference, dbt is the “T” in ELT. Therefore, dbt assumes that data is already loaded into a database from which the current database can query. In contrast, ETL extracts data then transforms it and before loading it into the target. With ELT, the data is not transformed as it moves into the data warehouse. (with transform we mean transformation by soft business rules which would change the meaning of the data. Of course, we have to make sure that the data fits into the target table (data type adjustments, etc.). Here we are talking about “hard rules”.)
Dbt is particularly compatible and useful in cloud DWH solutions such as Snowflake, Azure Synapse Analytics, BigQuery, and Redshift and performs transformations and modeling directly on the database in order to take advantage of the performance of these highly scalable platforms.

How dbt works

Models and SQL statements can be easily created, tested, and managed in dbt itself. A powerful combination of the scripting language, Jinja2, and the all-time classic SQL allows users to build models. The simple interface enables data analysts without engineering know-how to initiate appropriate transformations. Data team workflows become more efficient and cost-effective because of this. Behind this tool sits an open-source community that is constantly and passionately developing the tool. As such, dbt is available both as a free, reduced core version and as a comprehensive and flexible cloud version.

How does Data Vault 2.0 with dbt work?

Part of the Data Vault 2.0 methodology is the model that focuses on how to design the core data warehouse with a scalable solution. The core Data Vault entities are hubs, links ,and satellites. That said, using Data Vault 2.0 with dbt provides the ability to generate Data Vault models and also allows you to write your data transformations using SQL and code-reusable macros powered by Jinja2 to run your data pipelines in a clean and efficient way.

Conclusion

Dbt does not reinvent the wheel, but when it comes to building a new EDW – especially if it is in the clouds – it provides a very helpful basic framework with many important functions for Continuous Integration and Deployment already defined. dbt brings the standards of software development into the world of data transformation. This allows developers to concentrate on the core tasks of data modeling and business logic. Especially, not only for smaller projects, this tool offers a lightweight and extremely affordable alternative to other data warehouse automation solutions.

Ole Bause In Data Tools

Meltano Open Source Production Grade Data Integration – Part 2

Meltano Open Source Production Grade Data Integration

In this article, we delve into the practical application of Meltano, an open-source platform designed for building, running, and orchestrating ELT pipelines. Building upon our previous discussion of Meltano’s architecture, this installment guides you through the process of creating a data integration pipeline, from initializing a project to configuring extractors and loaders, and utilizing the Meltano UI for streamlined management.

In this article:

Meltano in action
The Meltano project folder
Adding an extractor and loader
Run a pipeline
Result
Conclusion

Meltano in action

In our last overview, we talked about Meltano and its architecture. Now, we would like to illustrate the ease in which you can use Meltano to create a data integration pipeline.
Before we start, please ensure that you have already installed Meltano on your machine. If you haven’t yet, you can follow Meltano’s official installation guide.

First we will initialize a Meltano project.
Initialize a new project in a directory of your choice by using “meltano init”. This will create a new directory with, among other things, your “meltano.yml” project file.

The Meltano project folder

The project folder is the single source of truth for all your data needs. It is a simple directory and version controllable.

The main part of your project is the meltano.yml project file. This file defines your plugins, pipelines and configuration.

The project and meltano.yml files are manageable using the Meltano CLI and are instantly containerized for Docker/Kubernetes deployment. Though, please note that there are not any defined plugins or pipeline schedules created yet. We will do this in the next step.

Adding an extractor and loader

Now, let’s initialize the pipeline’s components. The first plugin you’ll want to add is an extractor which will be responsible for pulling data out of your data source.

To find out if an extractor for your data source is supported out of the box, you can check the Extractors list on MeltanoHub or run “meltano discover”.

We will use the Tap for Gitlab in this example as we don’t need to create API credentials.

Meltano manages setup, configuration and handles invocation.

We will configure the extractor to pull the data from the repository meltano/meltano. Additionally, we will define it to only extract data as of 1 January 2021 and to include only data under the “Tags” stream.

Data selection is way easier than using just Singer! The extractor is now set up. Now, we will add a loader to store the data into a CSV file and define our destination path:

Remember that the directory needs to be previously created, as it will not be created automatically. This is what our meltano.yml looks like:

Instead of using the CLI, we can make changes directly in the YAML.

This way, it’s also possible to configure the extractors and loaders in the Meltano UI:

Start the Meltano UI web server using meltano ui. Unless configured otherwise, the UI will now be available at http://localhost:5000.

Run a pipeline

Now it’s time to run a pipeline. To run a one-time pipeline, we can just use the meltano elt command:

Result

And we are done! It took us just ten commands to create a data integration pipeline.

Conclusion

It’s clear why Meltano is a great choice for building your data platform: it’s powerful but simple to maintain and its open-source model makes it flexible, budget-friendly and reliable.

Ole Bause In Data Tools

Singer Open Source Production Grade Data Integration – Part 1

In our past blog post, we introduced an open source framework for ELT processes called Singer. This framework can be wrapped up using another open source tool which adds more interesting features to Singer including installation, setup of environments, monitoring, scheduling and orchestration. At Scalefree, we moved all of our ELT pipelines into this framework on AWS and are pleased with the results.

Please note, there are a large number of platforms for managing data integration but there is a lack of robust and easy-to-use, free open source solutions. The Meltano project aims to provide a solution to that situation. Meltano is a full-package data integration platform that challenges the most established players in the data space. Meltano is built on top of the best open source tools for data integration and infuses them with DataOps best practices.

Meltano is the easiest way to build, run and orchestrate ELT pipelines made-up of Singer taps, targets and dbt models. It is open source, self-hosted and version controlled as well as containerized.

Meltano’s open source model lets you easily adapt it to your own needs and reduces cost. Continue Reading