Looker Studio

Looker Studio reads straight from the warehouse Weld syncs into. This guide shows where Weld puts your data, how to give Looker Studio read-only access, and how to connect it to BigQuery or Snowflake.

Before you start

• Your workspace must use your own warehouse. Weld managed BigQuery can't be connected to BI tools, so migrate to your own BigQuery first.
• Build the tables your reports need as data models in Weld, and point Looker Studio at those rather than at raw synced tables. Models keep the joins and business logic in one place.

Where your data is

Click a data source on the Data Sources page to see its Destination schema (1): the BigQuery dataset or Snowflake schema its tables land in. You chose it as the Destination schema name when you added the data source.

• A model in a subfolder becomes <folder>.<subfolder>__<model>. For a model published as a view, the Schedule tab shows the exact name as Warehouse reference. See Transformation scheduling.
• WELD_LANDING and WELD_MODELS are the default Snowflake database names. Use your own if you changed them when you set up the warehouse.
• Every synced table has a _weld_synced column with the time Weld last wrote the row. See Weld managed fields.

Create read-only access

Give Looker Studio its own identity with read access only. Don't reuse the service account or user Weld syncs with: Weld's needs write access, and sharing it makes it hard to tell Weld's queries from your reports'.

BigQuery

In the Google Cloud console, give the Google account or group that will own the reports these roles on the project Weld syncs into:

• BigQuery Data Viewer, to read tables and views.
• BigQuery Job User, to run queries.

To limit access to some datasets, grant BigQuery Data Viewer on those datasets instead of the project. Include every dataset a model reads from if the model is published as a view, because BigQuery checks access to the tables behind a view.

Snowflake

Run this as a user with the SECURITYADMIN role, replacing COMPUTE_WH with your warehouse:

create role if not exists BI_READER;
grant usage on warehouse COMPUTE_WH to role BI_READER;

grant usage on database WELD_MODELS to role BI_READER;
grant usage on all schemas in database WELD_MODELS to role BI_READER;
grant usage on future schemas in database WELD_MODELS to role BI_READER;
grant select on all tables in database WELD_MODELS to role BI_READER;
grant select on future tables in database WELD_MODELS to role BI_READER;
grant select on all views in database WELD_MODELS to role BI_READER;
grant select on future views in database WELD_MODELS to role BI_READER;

create user if not exists BI_USER default_role = BI_READER default_warehouse = COMPUTE_WH;
grant role BI_READER to user BI_USER;

Then give BI_USER a sign-in method Looker Studio supports. Repeat the database grants for WELD_LANDING if reports also need the synced tables.

Connect Looker Studio to BigQuery

1. In Looker Studio, click Create and choose Data source.
2. Choose the BigQuery connector and authorize it with the Google account that has the read-only roles.
3. Under My Projects, pick the project Weld syncs into, then the dataset and table. For a model, the dataset is the model's folder.
4. Click Connect, check the fields, and click Create report.

To combine several tables, use Custom Query in step 3 instead of picking a table, or better, build the combination as a model in Weld and connect to that.

By default the data source uses the owner's credentials, so everyone who views the report sees the data through your account. Change Data credentials on the data source if viewers should use their own access.

See Google's BigQuery connector guide for the full set of options.

Connect Looker Studio to Snowflake

Looker Studio connects to Snowflake through the Snowflake partner connector, which Snowflake maintains.

1. In Looker Studio, click Create, choose Data source, and search for Snowflake under partner connectors.
2. Authorize the connector and sign in as BI_USER.
3. Enter your Snowflake account URL, role BI_READER, your warehouse, database WELD_MODELS and the schema, which is the model's folder.
4. Optionally enter a SQL query instead of a schema and table. Don't end it with a semicolon.
5. Click Connect, then Create report.

Weld creates Snowflake names in lower case unless you turned on Create tables and columns in upper case, so quote names in custom SQL: select "id" from "analytics"."customer_growth". See identifier casing.

See Snowflake's Looker Studio connector guide for the account URL format and authentication options.

Keep reports fresh

A report is only as fresh as the slowest of three schedules:

1. How often the data source syncs in Weld.
2. How often a model published as a table is rebuilt. Views always read the latest synced data. See Transformation scheduling.
3. Looker Studio's own cache. For BigQuery, set Data freshness on the data source; it defaults to 12 hours.

To refresh models straight after their sources sync, run them together in an orchestration.

On BigQuery, every chart on a report runs a query. Publishing the models behind busy reports as tables, rather than views, keeps queries small and costs predictable.

Troubleshooting

Related

• Data models
• Transformation scheduling
• Orchestration
• BigQuery setup guide
• Snowflake destination