SQL Editor

Overview

AI Squared’s SQL Editor allows you to define and manage your data models directly through SQL queries. This powerful tool supports native SQL commands compatible with your data warehouse, enabling you to seamlessly model your data.

Creating a Model with the SQL Editor

Starting with a Query

Begin by writing a SQL query to define your model. For instance, if using a typical eCommerce dataset, you might start with a query like:

SELECT * FROM sales_data.customers

Previewing Your Data

Click the Preview button to review the first 100 rows of your data. This step ensures the query fetches the expected data. After verifying, proceed by clicking Continue.

Important Note: The model cannot be saved if the query is incorrect or yields no results.

Configuring Model Details

Finalize your model by:

Naming the model descriptively.
Choosing a column as the Primary Key.

Completing the Setup

Finish your model setup by clicking the Finish button.

Unique Primary Key Requirement

Every model requires a unique primary key. If no unique column exists, consider:

Removing duplicate rows.
Creating a composite column for the primary key.

Handling Duplicate Data

To filter duplicates, use a GROUP BY clause in your SQL query. For instance:

SELECT *
FROM customer_data
GROUP BY unique_identifier_column

Composite Primary Keys

In scenarios where a unique primary key is not available, construct a composite key. Example:

SELECT customer_id, 
       email, 
       purchase_date,
       MD5(CONCAT(customer_id, '-', email)) AS composite_key
FROM sales_data

Saving a Model Without Current Results

To save a model expected to have future data:

UNION ALL
SELECT NULL, NULL, NULL

Add this to your query to include a dummy row, ensuring the model can be saved.

Excluding Rows with Null Values

To exclude rows with null values:

SELECT *
FROM your_dataset
WHERE important_column1 IS NOT NULL AND important_column2 IS NOT NULL

Replace important_column1, important_column2, etc., with your relevant column names.

Overview

Sources

Destinations

Data Modelling

Data Syncs

Overview

Creating a Model with the SQL Editor

Starting with a Query

Previewing Your Data

Configuring Model Details

Completing the Setup

Unique Primary Key Requirement

Handling Duplicate Data

Composite Primary Keys

Saving a Model Without Current Results

Excluding Rows with Null Values

​Overview

​Creating a Model with the SQL Editor

​Starting with a Query

​Previewing Your Data

​Configuring Model Details

​Completing the Setup

​Unique Primary Key Requirement

​Handling Duplicate Data

​Composite Primary Keys

​Saving a Model Without Current Results

​Excluding Rows with Null Values

Overview

Creating a Model with the SQL Editor

Starting with a Query

Previewing Your Data

Configuring Model Details

Completing the Setup

Unique Primary Key Requirement

Handling Duplicate Data

Composite Primary Keys

Saving a Model Without Current Results

Excluding Rows with Null Values