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:Previewing Your Data
Click thePreview
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 theFinish
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 aGROUP BY
clause in your SQL query. For instance:
Composite Primary Keys
In scenarios where a unique primary key is not available, construct a composite key. Example:Saving a Model Without Current Results
To save a model expected to have future data:Excluding Rows with Null Values
To exclude rows with null values:important_column1
, important_column2
, etc., with your relevant column names.