Power Audience SQL Requirements in alli audiences

When writing custom power audience SQL queries, there are a few nuances that you have to follow because of the way the application is set up. Power Audience SQL differs from the SQL you're used to writing in the Data Warehouse or BigQuery.

Here are some things to be aware of:

1. Do not end your query with a semi-colon.
   

2. When you select columns, you must pre-pend attr_ to it. For example, if the column you want to select is called dept_description, you must select it in the SQL as attr_dept_description.
   

3. In addition to the Attributes you chose for your Power Audience, you must select the columns created_at, CURRENT_DATE() as updated_at and sort_order. The backend database expects these columns to exist, so they have to be there.
   

4. Column names are case sensitive. Additionally, If the column you want to select has any capital letters, you must put it in double quotes. For example, if the column name you want to select is named EMAIL_ADDRESS, you must enter it in the SQL as "attr_EMAIL_ADDRESS", including the quotes.

Since newer audiences don’t allow capital letters in column names you shouldn’t run into this oftern. But if you are writing SQL for an older client you will have to use aliases to get the correct column names. For example: SELECT "attr_EMAIL_ADDRESS" as attr_email_address, …

Here is an example of a properly formatted Power Audience query: