Preview data tables while working on formula fields

[ErikHolmberg]

Hi,

I'm trying to get into building datasets and using the formula fields. Howver, it's hard to get an overview of the datastructure and the how the properties are structured.

For instance, say I want to use [COMPANY.hs_analytics_source] as basis for a selcetion. How would I review what table this data is included in as well as what data this column can hold?

Now I feel like I need to know the datastructre before hand and have no way to explore it. I was thininkg about looking at the data model but this doesn't give me any help in understanding what's included in the data tables

Coming from an SQL bckground, in SQL i would simply do an Select TOP 10 * FROM [TableName] to see what's in the table.

In short, looking for a way to overview the data tables that are available for the datasets as well as what data they hold

[PamCotton · Accepted solution]

Hey @ErikHolmberg, thank you for posting in our Community.

Great question! When working with HubSpot’s datasets and formula fields, it can feel a bit different if you're used to SQL queries. Although HubSpot doesn't offer a direct SELECT * FROM [TableName] function, you can still preview the data and its structure as you build formulas.

In the dataset builder, you have the option to preview the dataset, which allows you to see the structure and data within the fields you're working with. If you're trying to understand where fields like [COMPANY.hs_analytics_source] come from, the data model in HubSpot is helpful for identifying the relationships between different properties and tables.

You can also filter and sort data directly within the dataset builder, which helps give you a targeted view of specific fields without needing to know the entire data structure beforehand.

To our top experts, @Anton @RatulRahman and @Shadab_Khan do you have any recommendations for @ErikHolmberg matter?

Thank you,

Pam

[ErikHolmberg]

Thanks @PamCotton, it's a bit clunky way to go about it but it kinda works. 

To access this I clicked Company under datamodel -> searched original source among properties, clicked original source and reviewd field type to see what I expect to see in the data. 

However my problem still remains, I am still left with guessing that orginal source for Companies is the same as [COMPANY.hs_analytics_source] in datasets. As these are not named the same it's not intuative to me if they actually are the same.

When I try to use DISTINCT_COUNT(IF( [COMPANY.hs_analytics_source] <> "OFFLINE", [COMPANY.hs_object_id], NULL)) in order to count how many comanies are coming from online sources I get an error highligting OFFLINE "There was a problem with your input, please check the syntax documentation for reference."

This leaves me unsure if I'm using the right conditions for  [COMPANY.hs_analytics_source] or not