Build a Python ELT pipeline to extract Google Analytics data and load it into DuckDB with dlt

Build a reproducible ELT pipeline that extracts Google Analytics 4 (GA4) reporting data with dlt (the open source data load tool) and loads it into DuckDB for fast, local SQL analytics. It replaces manual GA4 exports and brittle scripts: it authenticates to the Google Analytics Data API with a service account, runs your dimension and metric queries, and lets dlt infer schemas and create tables automatically, inside a containerized Python step managed by Kestra.

How it works

• A single dlt_pipeline task of type io.kestra.plugin.scripts.python.Script runs the whole pipeline.
• It executes on a Docker taskRunner (io.kestra.plugin.scripts.runner.docker.Docker) using the python:3.11 container image, so the runtime is isolated and reproducible.
• beforeCommands install dependencies with pip install dlt[duckdb] and bootstrap the source and destination with dlt --non-interactive init google_analytics duckdb.
• Service account credentials and the GA4 property id are injected as env variables using dlt naming conventions (for example SOURCES__GOOGLE_ANALYTICS__CREDENTIALS__CLIENT_EMAIL), sourced from Kestra secrets.
• The inline Python script defines a QUERIES list (dimensions like browser, city, dateHour and metrics like totalUsers, transactions), builds a dlt.pipeline with destination="duckdb" and dataset_name="google_analytics", then calls pipeline.run(google_analytics(queries=QUERIES)).

What you get

• Authenticated GA4 ingestion via the Google Analytics Data API.
• Multiple analytics queries (dimensions and metrics) loaded in a single run.
• Automatic schema inference, normalization, and table creation by dlt.
• A local DuckDB dataset ready for SQL exploration and reporting.

Who it's for

• Analytics engineers replacing manual GA4 exports with code.
• Data teams prototyping models before committing to a warehouse.
• Python developers who want a portable, single file ELT pipeline.

Why orchestrate this with Kestra

dlt loads data, but it does not schedule, retry, or observe your runs. Kestra adds declarative YAML pipelines, event and schedule triggers, automatic retries on transient API failures, execution logs, and lineage across runs, without bolting cron and shell glue around the dlt CLI.

Prerequisites

• A Docker task runner available to your Kestra instance.
• A Google Cloud service account with access to the Google Analytics Data API and your GA4 property.

Secrets

• GOOGLE_ANALYTICS_PROJECT_ID
• GOOGLE_ANALYTICS_CLIENT_EMAIL
• GOOGLE_ANALYTICS_PRIVATE_KEY
• GOOGLE_ANALYTICS_PROPERTY_ID

Quick start

1. Create a Google Cloud service account, grant it access to your GA4 property, and download its JSON key.
2. Add the four secrets above to your Kestra instance (project id, client email, private key, GA4 property id).
3. Adjust the QUERIES list to the dimensions and metrics you want to report on.
4. Run the flow and query the resulting tables in the google_analytics DuckDB dataset.

How to extend

• Add more entries to QUERIES to pull additional GA4 reports.
• Swap the DuckDB destination for BigQuery, Snowflake, or Postgres by changing the dlt destination.
• Add a Schedule trigger for daily incremental loads.
• Chain a dbt or SQL task after ingestion to model the raw GA4 tables.

Links

• Plugin documentation: plugin-scripts
• DuckDB orchestration with Kestra