📘 SQL Editor Guide – Defining Models with AudienceSync

🧠 Overview

The SQL Editor in AudienceSync lets you define and manage data models using native SQL queries compatible with your data warehouse. This offers a flexible, powerful way to shape data for activation and analysis.

🛠️ How to Create a Model

1. Start with a SQL Query

Begin your model by writing a SQL query.

Example:

sql
CopyEdit
SELECT * FROM sales_data.customers

2. Preview Your Data

Click Preview to load the first 100 rows.

Confirm the data returned is as expected.

Click Continue to proceed.

⚠️ Note: You cannot save the model if the query is invalid or returns no data.

3. Configure Model Details

Name your model descriptively.

Choose a Primary Key column.

4. Complete the Setup

Click Finish to save and finalize your model.

🔑 Primary Key Requirements

Every model must have a unique primary key.

If a unique column doesn’t exist:

Remove duplicate rows using GROUP BY, or

Create a composite primary key.

Example to remove duplicates:

sql
CopyEdit
SELECT *
FROM customer_data
GROUP BY unique_identifier_column

Example of a composite key:

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

📉 Saving Models Without Current Data

If your model currently has no data but will in the future, use:

sql
CopyEdit
UNION ALL
SELECT NULL, NULL, NULL

This adds a dummy row so the model can be saved.

🚫 Excluding Rows with Nulls

To exclude rows containing null values:

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

Replace important_column1, important_column2, etc., with your actual field names.

Using SQL for Advanced Models