CARTO Data Warehouse

Every CARTO organization comes provisioned with an active data warehouse connection that is created by default.

This connection will grant you access to some resources, in terms of cloud storage and cloud computing, that are provided by CARTO. This default connection will help you get started with our platform, and it will also give you access to some demo datasets so you can start using our platform from the very beginning. It will also enable you to have access to spatial datasets from CARTO’s Data Observatory without the need of connecting your own data warehouse to get access to cloud resources.

Limits

The CARTO Data Warehouse connection is limited to a certain amount of computing per day. You can find this limit in the CARTO Data Warehouse connection’s card. These limits depend on whether your organization has purchased the CARTO Data Warehouse Add-on in your subscription plan.

Structure

For simplicity, the CARTO Data Warehouse uses the same structure and naming as a Google BigQuery connection: projects, datasets and tables.

Inside the CARTO Data Warehouse you'll find two primary datasets, available to all editors:

• Organization Data (organization data): the data managed by you and your teammates.
• CARTO Demo Data (demo data): these are tables and tilesets curated by CARTO

Organization Data

For the data that users in the organization will manage, there are two available datasets: private and shared.

Private (private)

The private dataset is a unique dataset for each user, and all the tables and tilesets in this folder will only be available to that user. Private datasets have a unique qualified name that identifies the user, extracted from their email.

For example, if a user with an email [e-mail] and a table called example_data wants to use private data in a Custom Query (SQL), they will use a qualified name similar to:

carto-dw-ac-IDxxxxxx.private_johndoe_IDxxxxxx.example_data

There are some particularities when using private data:

• Maps using private data can still be shared and made public without any issue

• Maps can still be duplicated and made collaborative, but your teammates won't be able to view the data in your private dataset.

If an editor user is deleted or downgraded to viewer, their private dataset will be transferred to the admin, who will see the full qualified name in their Data Explorer (eg: private_johndoe_IDxxxxxx)

If the viewer is promoted to editor again, they will recover access to their private data but the admin will still retain privileges over that dataset. If this is an issue, re-create the user or contact our Support Team

Shared (shared)

The shared dataset is the same for every user inside the organization, and all the tables and tilesets in this folder will be available for all users.

CARTO Demo Data

The CARTO Data Warehouse connection grants access to a set of demo tables and tilesets that can be used to start creating maps and exploring the tools from the very beginning. They’re available and ready to be used in the Data Explorer.

Additionally, if your onboarding experience was customized, you may get additional demo data for Retail, CPG or Out-of-home use cases.

If your CARTO organization has been provisioned on a cloud region outside the US (e.g., Europe-West, Asia-Northeast), you will find available an additional dataset called shared-us in your CARTO Data Warehouse connection. With the tables stored in shared-us you will be able to access certain functionalities, such as creating tilesets and performing data enrichment with Data Observatory subscriptions, that are only available at the moment for data tables stored in the US multi-region. In case of questions, please contact [e-mail].

Accessing the console

Technically speaking, the CARTO Data Warehouse is a fully-managed Google BigQuery project for the organization, where users in the organization can upload and analyze geospatial data as if they were connecting their own data warehouse. To complete the experience of this managed data warehouse, we give users the possibility of directly accessing the SQL console, to run their own queries there.

In order to get access to the console of the CARTO Data Warehouse, just provide a valid Google Account:

Please note that if you use Google to login, we will use that same account and you won't need this step

Once the process is finished, the interface will change and it will display a shortcut to access the CARTO Data Warehouse console:

At this point, we have granted your Google account permissions to access the console, plus access to the Analytics Toolbox for BigQuery, so you can run our functions directly in the console. If you click on "Access console" you'll be redirected to the Google BigQuery console, where you will have the same level of access than in the CARTO Data Explorer. You will likely see:

• Your private dataset
• Your shared dataset
• Your temporal Workflows dataset
• An additional project and a dataset for your Data Observatory subscriptions.

You won't be able to create new datasets, to maintain the structure and privacy of your project.

Learning to use the BigQuery console

If this is your first time using BigQuery, we recommend the following resources to get started with the BigQuery console:

• This interactive BigQuery tutorial by Google Cloud
• This free course by the Google Cloud team

Examples of using the console

Once you're in the console, you'll be able to run any type of SQL query or use most of the functionalities available in BigQuery. Here are some examples of use cases that can be achieved through the console:

Running non-geospatial queries

The BigQuery console lets you run all types of SQL queries, including those that don't have geospatial results. For example, to understand the average of a column without having to create a map you could run:

SELECT AVG(qty) FROM carto-dw-ac-yourid.shared.sales

Another approach we recommend is using CARTO Workflows.

Running non-SELECT queries

In CARTO Builder, you can only run SELECT-type queries, but some methods in our Analytics Toolbox for BigQuery are non-SELECT procedures, for example, the Geographically Weighted Regression (GWR). Using the console you can run those procedures, store the result in a table and visualize it with Builder.

CALL `carto-un`.carto.GWR_GRID(
    'cartobq.docs.airbnb_berlin_h3_qk_qb',
    ['bedrooms', 'bathrooms'], -- [ beds feature, bathrooms feature ]
    'price', -- price (target variable)
    'h3_z6', 'h3', 3, 'gaussian', TRUE,
    '<project-id>.<dataset-id>.<table-name>'
);

Another approach we recommend is using CARTO Workflows.