virtual_table

Definition

A virtual_table is a powerful tool to define views based on existing tables by modifying, aggregating, or filtering the data in some way, all within the Veezoo Knowledge Graph.

Usage

virtual_table allows you to define a new view/table using SQL that manipulates or extends the original data. You can then reference this virtual table like any other table when creating classes or defining columns.

Note: Virtual tables should mostly be used for prototyping more complex logic. As a best practice, once the definition is finalized, it should be moved to dbt or a similar tool for long-term maintainability and better performance.

Example: Changing a Table to a Virtual Table

Suppose we have a dataset with order data, including the order date and customer ID. We want to create a virtual table that adds a column with the order number (e.g. the first order, second, etc.) for each customer, based on the order date.

To do that you will first need to switch the sidebar in Studio to show the file tree instead:

Then open the table definition file for the orders table.

We can change an existing table definition:

db.orders_db.public {
    table orders {
        identifier: "orders"      
        
        ...
    }
}

...to use virtual_table and include a virtual_table_sql instead:

import: [
    db.postgres.orders_db.public.ORDERS
]

db.orders_db.public {
    virtual_table orders {
        identifier: "orders"

        virtual_table_sql:
         """
            SELECT
                o.*,
                ROW_NUMBER() OVER (
                    PARTITION BY o."customer_id"
                    ORDER BY o."order_date" ASC
                ) as "customer_order_number"
            FROM
                "orders_db"."public"."orders" o
         """

        // Define the new column
        column customer_order_number {
            identifier: "customer_order_number"
            primitive_type: "INTEGER"
        }

        ...
    }
}

Now you can reference it in a new integer attribute Customer_Order_Number in your Order class.

kb {

    class Order {
        ...
        
        // Reference the new column
        integer Customer_Order_Number {
            name.en: "Customer Order Number"
            sql: "${orders.customer_order_number}"
        }
    }
}

Example: Joining tables

You are not limited to using the content of a single table; you can join or union several tables into a virtual table. In the following example, we want to include a payment_method in our transaction table, even though it is stored separately in the database. We can adjust the definition of the transaction table in the following fashion:

db.transaction_db.public{
    virtual_table transaction {
        identifier: "transaction"
        
        virtual_table_sql:
        """
            SELECT 
                t.*,
                p."payment_method"
            FROM 
                "transaction_db"."public"."transaction" t
            JOIN 
                "transaction_db"."public"."payment" p
            ON 
                t."transaction_id" = p."transaction_id"
        """
        
        column payment_method {
            identifier: "payment_method"
            primitive_type: "text"
        }

For another example, check our tutorial on historized tables.