Alternative to BigQuery Access for Small Datasets (Talend & Mulesoft Use Case)

[chawki]

Hello everyone,

I currently manage multiple BigQuery projects and datasets that serve as the backend for our Power BI reporting layer. These datasets handle all data and transformations.

Now, I have a new use case: teams using Talend and Mulesoft need access to a few specific transcription tables. These tables are small in size, and I would prefer not to grant direct access to the BigQuery datasets.

I'm wondering:

• Is there an alternative way to share this data securely without exposing BigQuery?

• Would using Cloud SQL be a suitable approach for this scenario (e.g., replicating or exporting the tables there)?

• Are there other GCP-native options that could work better?

Appreciate any guidance or suggestions!

Thanks in advance,

[nmagcalengjr]

Hi @chawki,

Here's a breakdown of your options and some considerations:

1. Cloud SQL:
   • PROS: Cloud SQL is a managed relational database service (supporting MySQL, PostgreSQL, and SQL Server) that aligns well with Talend and Mulesoft’s data integration needs. It offers secure IAM-based access controls, supports native JDBC/ODBC drivers for easy connectivity, and allows full control over the exposed schema. You can replicate BigQuery tables to Cloud SQL for real-time access or schedule periodic exports to maintain consistent data snapshots, making it ideal for ETL workflows.
   • CONS: Using Cloud SQL comes with some trade-offs: you'll need to manually map your BigQuery schema to a relational format, which can add complexity. Teams unfamiliar with relational databases may face a learning curve, and continuous replication of large tables can lead to higher costs and additional ETL overhead. Compared to using BigQuery views, Cloud SQL also requires slightly more maintenance.

2. BigQuery Authorized Views: 
   • PROS: One major advantage of using BigQuery authorized views is that it's a zero-copy, fully managed, and secure solution—eliminating the need for data duplication. You can create a view that filters only the required columns or rows, authorize access at the dataset level, and use IAM controls to grant Talend or Mulesoft service accounts access to just that dataset. This setup makes access easy to audit, manage, and revoke without exposing the underlying tables directly.
   • CONS: A downside of using authorized views is that Talend and Mulesoft would still require a BigQuery connector and permission to query BigQuery directly. While access can be limited to specific datasets, this approach doesn’t fully abstract BigQuery from those tools. Additionally, any queries they run will still incur BigQuery usage costs.

3. Cloud Storage: 
   • PROS: For small, static, or infrequently updated transcription tables, exporting them as CSV or Parquet files to Cloud Storage is a simple, low-effort, and cost-effective solution. Talend and Mulesoft can easily consume these files, making this approach well-suited for batch ETL pipelines without needing direct access to BigQuery.
   • CONS: This approach lacks real-time updates and relies on file handling, which can be less seamless for applications. Talend and Mulesoft must be configured to ingest data from Cloud Storage files, which may be less convenient than querying a database—especially when joins or filtering are needed. Managing file versions and ensuring data freshness can also introduce additional complexity.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[alisha47]

Hi Chawki!

Totally get your concern; it's smart to be careful with BigQuery access. A few easy ways to share just what’s needed without opening the whole thing up.

Cloud SQL is a good start. You can move just the transcription tables over, and Talend or Mulesoft teams can grab what they need without touching your main data.

BigQuery Authorized Views are also great. They let you show only specific info, no full table access needed.

Don’t want to set up a new DB? Export the tables as CSV or Parquet to Cloud Storage and share those files securely.

Also, Windsor.ai makes the data integration process super simple. It hooks BigQuery up with tons of tools and automates everything, no coding required.

So yeah, Cloud SQL works, but views or file exports might be quicker depending on your setup. Happy to help if you need!

[shaaland]

Hi @chawki ,

Another option would be to use GCP's Application Integration service to expose just what you want from BigQuery as an API, which could then be shared with the Talend and Mulesoft platforms to call.  This allows you to use the GCP Service Account authentication from Application Integration through the BigQuery Connector, and then you could combine that with the Option 2 for BigQuery Authorized Views to lock down the service account used by the BigQuery connector.

In addition, the Application Integration layer supports fine grained RBAC controls through IAM Conditions, so you could also make sure that only the Mulesoft client and Talend client can call the API Triggers in Application Integration for those flows.

BTW: You get 2 free GCP Connection nodes in the Application Integration free tier, so this could be a pretty inexpensive option.  Pricing page here.