Extract data, mask sensitive columns using DuckDB and load it to BigQuery

This blueprint runs a privacy-aware ETL pipeline that extracts raw CSV data, masks personally identifiable information (PII) with DuckDB SQL, and loads the anonymized result into Google BigQuery. It solves a common compliance problem: sensitive customer fields like names and emails should never land in your analytics warehouse in clear text. By hashing those columns in flight with DuckDB before the load step, you keep the warehouse usable for joins and aggregations while removing raw PII from downstream tables.

How it works

1. The extract task uses io.kestra.plugin.core.http.Download to fetch a CSV file of orders over HTTP and stage it in Kestra internal storage.
2. The transform task uses io.kestra.plugin.jdbc.duckdb.Queries to read the downloaded file with read_csv_auto, build an orders_pii table that applies hash(customer_name) and md5(customer_email) while passing through non-sensitive columns (order_id, product_id, price, quantity, total), and COPY the masked result back out as a CSV output file.
3. The load task uses io.kestra.plugin.gcp.bigquery.Load to ingest the masked CSV into the stage.orders_pii destination table, authenticating with a service account and using autodetect schema detection.

What you get

• PII columns hashed before they ever reach the warehouse.
• A reproducible extract, transform, load pipeline defined as declarative YAML.
• In-flight SQL transformation with DuckDB, no separate database server to manage.
• A typed BigQuery table populated with analytics-ready, anonymized data.

Who it's for

• Data engineers building compliant ingestion pipelines.
• Analytics teams that need warehouse data without raw customer PII.
• Platform teams enforcing data governance and privacy by design.

Why orchestrate this with Kestra

DuckDB and BigQuery each handle one stage, but neither coordinates the full extract, transform, load chain. Kestra ties the HTTP download, the DuckDB masking step, and the BigQuery load into one observable run with automatic passing of file outputs between tasks. You get event and schedule triggers, retries on transient failures, full execution lineage, and a single declarative YAML definition. That is the gap a database query engine's own scheduler cannot fill: cross-tool orchestration, dependency management, and end-to-end visibility.

Prerequisites

• A running Kestra instance.
• A reachable source CSV (the example points at a public Hugging Face dataset).
• A Google Cloud project with a BigQuery dataset and a service account that can load tables.

Secrets

• GCP_CREDS: the GCP service account JSON used by the load task to authenticate to BigQuery.

Quick start

1. Add the GCP_CREDS secret with your service account key.
2. Set projectId and destinationTable to your own project and dataset.
3. Optionally point the extract task at your own source file.
4. Execute the flow and confirm the masked rows appear in BigQuery.

How to extend

• Swap the extract task for a database query, an S3 download, or a custom script.
• Add more masking logic in the DuckDB SQL (for example salting hashes or redacting phone numbers).
• Point DuckDB at MotherDuck by setting a url such as jdbc:duckdb:md:my_db?motherduck_token={{ secret('MOTHERDUCK_TOKEN') }}.
• Add a schedule or event trigger and configure retries for production runs.

Links

• DuckDB plugin docs
• Google Cloud plugin docs
• DuckDB orchestration
• BigQuery orchestration