Vertesia Documentation

Getting Started

This guide walks you through creating your first DataStore, importing data, running queries, and creating a dashboard.

Prerequisites

  • A Vertesia project with the Data Platform plugin installed
  • Data files (CSV, JSON, or Parquet) to import
  • Appropriate permissions to create DataStores and dashboards

Step 1: Create a DataStore

A DataStore is a DuckDB database that will hold your structured data.

Using the UI

  1. Navigate to the Data Platform section in your project
  2. Click New Project to create a data project
  3. Enter a name and description for your project
  4. The system will create both a Collection (for files) and a DataStore (for structured data)

Using the API

import { VertesiaClient } from '@vertesia/client';

const client = new VertesiaClient({ apiKey: 'your-api-key' });
const dataApi = client.data;

// Create a new DataStore
const store = await dataApi.create({
    name: 'sales-analytics',
    description: 'Sales data for analytics dashboards',
    tags: ['analytics', 'sales'],
});

console.log('Created DataStore:', store.id);

Step 2: Create Tables

Define the schema for your data by creating tables.

Using the API

// Create tables with relationships
const tables = await dataApi.createTables(store.id, {
    tables: [
        {
            name: 'customers',
            description: 'Customer information',
            columns: [
                { name: 'id', type: 'INTEGER', primary_key: true },
                { name: 'name', type: 'STRING', nullable: false },
                { name: 'email', type: 'STRING', semantic_type: 'email' },
                { name: 'created_at', type: 'TIMESTAMP' },
            ],
        },
        {
            name: 'orders',
            description: 'Customer orders',
            columns: [
                { name: 'id', type: 'INTEGER', primary_key: true },
                { name: 'customer_id', type: 'INTEGER', nullable: false },
                { name: 'amount', type: 'DECIMAL', semantic_type: 'currency' },
                { name: 'order_date', type: 'DATE' },
            ],
            foreign_keys: [
                {
                    column: 'customer_id',
                    references_table: 'customers',
                    references_column: 'id',
                    on_delete: 'CASCADE',
                },
            ],
        },
    ],
});

Using AI Schema Creation

In the Data Platform UI, you can upload data files and use AI to automatically generate an optimal schema:

  1. Upload your CSV/JSON/Parquet files to the project Collection
  2. Click Create Schema with AI
  3. The AI agent analyzes your files and proposes a schema
  4. Review and approve the suggested tables and relationships

Step 3: Import Data

Import data from various sources into your tables.

From Inline Data

const importJob = await dataApi.import(store.id, {
    tables: {
        customers: {
            source: 'inline',
            data: [
                { id: 1, name: 'Alice', email: 'alice@example.com' },
                { id: 2, name: 'Bob', email: 'bob@example.com' },
            ],
        },
    },
    mode: 'append',
    message: 'Initial customer import',
});

From CSV Files

const importJob = await dataApi.import(store.id, {
    tables: {
        orders: {
            source: 'url',
            url: 'https://example.com/data/orders.csv',
            format: 'csv',
        },
    },
    mode: 'replace',
    message: 'Import orders from CSV',
});

Import Modes

  • append: Add new rows to existing data
  • replace: Replace all existing data in the table

Step 4: Query Data

Execute SQL queries against your DataStore using DuckDB syntax.

const result = await dataApi.query(store.id, {
    sql: `
        SELECT
            c.name,
            COUNT(o.id) as order_count,
            SUM(o.amount) as total_spent
        FROM customers c
        LEFT JOIN orders o ON c.id = o.customer_id
        GROUP BY c.id, c.name
        ORDER BY total_spent DESC
        LIMIT 10
    `,
    limit: 100,
});

console.log('Columns:', result.columns);
console.log('Rows:', result.rows);
console.log('Execution time:', result.execution_time_ms, 'ms');

Query Features

DuckDB provides powerful analytics capabilities:

  • Window functions: ROW_NUMBER(), LAG(), LEAD(), RANK()
  • Common Table Expressions (CTEs)
  • QUALIFY clause for filtering window function results
  • Pivoting and unpivoting
  • JSON functions for semi-structured data

Step 5: Create a Dashboard

Create a Vega-Lite dashboard to visualize your data.

const dashboardApi = dataApi.dashboards(store.id);

const dashboard = await dashboardApi.create({
    name: 'Sales Overview',
    description: 'Key sales metrics and trends',
    query: `
        SELECT
            DATE_TRUNC('month', order_date) as month,
            SUM(amount) as revenue
        FROM orders
        GROUP BY 1
        ORDER BY 1
    `,
    spec: {
        $schema: 'https://vega.github.io/schema/vega-lite/v5.json',
        mark: 'bar',
        encoding: {
            x: { field: 'month', type: 'temporal', title: 'Month' },
            y: { field: 'revenue', type: 'quantitative', title: 'Revenue' },
        },
    },
});

console.log('Created dashboard:', dashboard.id);

Preview Before Saving

Test your visualization without saving:

// Preview returns a PNG image
const preview = await dashboardApi.preview({
    query: 'SELECT category, SUM(amount) as total FROM orders GROUP BY category',
    spec: {
        mark: 'arc',
        encoding: {
            theta: { field: 'total', type: 'quantitative' },
            color: { field: 'category', type: 'nominal' },
        },
    },
});

Next Steps

Now that you have the basics, explore more advanced features:

Was this page helpful?