Creating data sets

Data sets in Preql provide a powerful way to combine and manage your data from various sources.

This feature allows you to join or union multiple tables, creating a unified view of your data that can be used for analysis and reporting. You can also use it to connect to one table or data source at a time or use SQL to connect to the data in your warehouse.

Navigate to the "Data sets" tab in settings.

Click on "Create new" and choose your desired action:

Joins combine tables using one or more columns the tables have in common.

These mutual columns are called Join Keys. E.g. you can join your payroll data to your time tracking data on employee_id and date (the join keys) to access both payroll and time tracking columns in one place.

If you’re new to joins, we recommend speaking to your contact at Preql. You need to be careful about how you join tables together to make sure you don’t duplicate data.
​

Select how you want to connect to data and create your data set.

Choose the join type (Left, Right, Inner, or Full Outer).

Select which data models you want to join together. If you want to join together more than 2 tables, select the ‘Add a table’ button below the data previews.

Specify your join keys: These are the columns will be used to find matching rows between your tables. You can join tables on more than one key by selecting ‘Add a key’. Your join keys should exist in both of the tables you want to join.

You can use the data preview to check the keys you’re joining on before continuing by selecting ‘Next’.

Select how you’d like each column treated in Preql. You must have at least one ID, Metric, Dimension and Time fields.

Review the columns you’re importing and make any necessary changes, for example:

When you’re happy with your changes, select ‘Next’ at the bottom of your screen.

Set the name and description for your new data set and select ‘Confirm’ to create your new data set

Unions combine tables by stacking the same columns from different tables on top of each other. E.g. you can Union ad spend data from Google Ads with the same columns from Facebook Ads to access data from both sources together. Whereas the output of a Join usually has more columns (the different columns from each data source), the output of a Union has the same number of columns.

If you’re new to unions, we recommend speaking to your contact at Preql. You need to be careful about how you union tables together, to make sure you don’t duplicate data.

Select how you want to connect to data and create your data set

Choose the tables/data models you want to union. There must be at least 2, you can add more than 2 tables by selecting ‘add a table’ at the bottom of the screen.

You can add filters to these tables be selecting ‘add a filter’ under neath the data previews. This isn’t usually necessary but can be helpful. E.g. if you don’t want your output to contain data from a test or dummy account, you can filter out those Account IDs.

Once you’ve selected the tables you want to union, added any filters and reviewed the data previews, select ‘Next’.

Select how you’d like each column treated in Preql. You must have at least one ID, Metric, Dimension and Time fields.

Add columns to your data set by matching columns from both tables to union together. You’ll be selecting a column from each data set you want to union together. These columns should represent the same thing. E.g. you should match the Date column from your first source with the Date column in your other source.

Set how you want these new columns to be treated in Preql, using the ‘Create as’ dropdown.

Create a new for each column you’re creating for example if you’re combining Campaign_ID and Campaign_ID into a Metric - Count Distinct, you could name the column ‘Total Campaigns’.

When you’re happy with your columns, select ‘Next’ at the bottom of your screen.

Set the name and description for your new data set and select ‘Confirm’ to create your new data set