How to run custom SQL queries

Before you begin

Ensure you have set up:

• A system of records. Currently, Cargo supports connecting to BigQuery and Snowflake.

• Existing connectors and data models for HubSpot contacts and Mixpanel events

Install SQL connector

Select data from the left sidebar menu and click the create new dataset button in the top right. Select SQL from the list.

The only options at this stage are the name and the slug. The actual query will be configured in the next step at the data model level.

Add SQL model

Once the SQL connector is installed, click the create a model button. This will bring up data model configuration options including the SQL query.

The next step depends on what database is used as the persistence system for your Cargo workspace. Go to step 3 when working with BigQuery or to step 4 when using Snowflake.

Write SQL for BigQuery

Now we can write a query that will bring records into our new data model. All columns selected by the query will be imported as record attributes.

You can query any tables within the connected BigQuery scope. You can check your scope by going to Cargo workspace settings and selecting system of records.

The naming convention for the Cargo managed databases also depends on the scope.

If you selected dataset scope:

[DATASET].datasets_[CONNECTOR_SLUG]__models_[MODEL_SLUG]

If you have chosen project scope when setting up BigQuery system of record then use following naming convention:

[PREFIX]_datasets_[CONNECTOR_SLUG].models_[MODEL_SLUG]

SELECT
  h.id as hubspot_id,
  h.email,

  COUNT(CASE WHEN m.event_name = 'Page View' THEN 1 ELSE NULL END) as page_views

FROM
  cargo_dataset.datasets_hubspot__models_contact h
LEFT JOIN
  cargo_dataset.datasets_mixpanel__models_event m
  ON h.email = m.distinct_id

WHERE
  h.email IS NOT NULL

GROUP BY
  h.id, h.email

Write SQL for Snowflake

Now we can write a query that will bring records into our new data model. All columns selected by the query will be imported as record attributes.

You can query any tables within the connected Snowflake scope. You can check your scope by going to Cargo workspace settings and selecting System of records.

The naming convention for the Cargo managed databases also depends on the scope.

If you selected database scope:

[DATABASE].datasets_[CONNECTOR_SLUG].models_[MODEL_SLUG]

If you have chosen schema scope when setting up Snowflake system of record then use the following naming convention:

[DATABASE].[SCHEMA].datasets_[CONNECTOR_SLUG]__models_[MODEL_SLUG]

SELECT
  h.id as hubspot_id,
  h.email,

  COUNT(CASE WHEN m.event_name = 'Page View' THEN 1 ELSE NULL END) as page_views

FROM
  CARGO_DB.datasets_hubspot.models_contact h
LEFT JOIN
  CARGO_DB.datasets_mixpanel.models_event m
  ON h.email = m.distinct_id

WHERE
  h.email IS NOT NULL

GROUP BY
  h.id, h.email

Note: The table naming conventions differ between BigQuery and Snowflake:

• BigQuery uses __ (double underscore) between connector and model names
• Snowflake uses . (dot) between connector and model names
• Both systems use the same pattern for the overall structure: [SCOPE].datasets_[CONNECTOR].models_[MODEL]

Configure mapping

When our SQL query is ready, we need to configure basic mapping for the new data model.

From all the columns returned by the query, we need to specify:

• Id column slug - the unique identifier of the record
• Title column slug - the title of the record
• Time column slug - the timestamp related to the record

Preview and save the query

When the query and field mapping is correct, we can run the preview on the right side to ensure the data and column naming is correct.

Once we are happy with our results, we can save the new data model.