Skip to main content

Ingest Google Analytics 4 into MotherDuck with dlt as a Flight

IngestionFlightsdltingest
Build this flight plan with your AI assistant

I want scheduled GA4 reporting data in MotherDuck without hand-writing API calls or INSERTs. Help me adapt the "Ingest Google Analytics 4 into MotherDuck with dlt as a Flight" recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/flight-dlt-ga4-ingest

OpenAIOpen in ChatGPTAnthropicOpen in ClaudeView on GitHub

A single-file Flight that runs a dlt pipeline pulling Google Analytics 4 (GA4) report data into MotherDuck. It is the GA4 adaptation of flight-dlt-ingest: the demo repo_rows() source is replaced with a ga4_rows() source that calls the GA4 Data API, and the rest of the pattern — dlt-managed schema, Parquet loader files, a run ledger, and Flight scheduling — is unchanged.

Everything is driven by Flight config, so you adapt it by setting config values, not by editing flight.py. The defaults pull the last 7 days of sessions/users/pageviews by default channel group from the GA4 property you set in GA4_PROPERTY_ID, and load it into ga4_ingest.ga4.ga4_report in your own account.

Aggregated reports, not raw events​

The GA4 Data API (runReport) returns pre-aggregated report rows — metrics like sessions and totalUsers broken down by dimensions like date and sessionDefaultChannelGroup. It cannot return raw, event-level data (individual event_name rows, event params, user properties).

  • Want dashboards / KPI reporting? This template is the right fit.
  • Want raw GA4 events? Use the native GA4 → BigQuery export and ingest with the GA4 BigQuery Flight instead.

How it works​

flight.py runs a fixed sequence; the config values only change its inputs:

  1. Set HOME=/tmp (dlt writes working files under HOME, and a Flight has a writable /tmp) and point the dlt MotherDuck destination at DESTINATION_DATABASE through an environment variable, so no token is written anywhere.
  2. Connect to MotherDuck (md:) and CREATE DATABASE IF NOT EXISTS the destination, because dlt creates the dataset and tables but not the database.
  3. Build a dlt pipeline and run() the ga4_rows() source with loader_file_format="parquet". In merge mode, the date is the merge key and the configured dimension columns are the primary key.
  4. Append one row to the run ledger capturing the dlt load package summary.

ga4_rows() authenticates with a Google service account, calls the GA4 Data API runReport for the configured property, dimensions, metrics, and date range, and yields one dict per report row. It requests the GA4 maximum of 250,000 rows per page, then continues until it reads the full row_count.

Why this dlt setup​

The important default is the load format. For MotherDuck, prefer Parquet loader files over row-wise insert_values, so larger sources stay on a bulk-loading path. The Flight makes that choice explicit with loader_file_format="parquet".

The second important default is a date-partition merge over a moving lookback window. GA4 keeps revising recent days as attribution and conversion windows settle. Re-pulling 7daysAgo→yesterday replaces each returned date partition. For each returned date, that corrects changed rows and removes rows that disappear from a revised report. append would double-count, while replace would discard history.

Adapt the pattern​

  • Set GA4_PROPERTY_ID to your numeric GA4 property id.
  • Choose your grain with GA4_DIMENSIONS and GA4_METRICS. Keep date in GA4_DIMENSIONS when using the default merge mode. The dimension list is the primary key, so adding a dimension later changes the table grain.
  • Tune the lookback with GA4_START_DATE / GA4_END_DATE. GA4 accepts relative strings (7daysAgo, yesterday, today) as well as YYYY-MM-DD.
  • Use WRITE_DISPOSITION=merge with date plus the other dimension columns as PRIMARY_KEY for date-partition replacement. Use replace if you re-pull the full range each run, or append only for an immutable pull log.
  • Keep loader_file_format="parquet" unless you have measured a reason to change it. See the dlt MotherDuck destination docs.

Questions to answer​

  • Which GA4 property (numeric id), and which dimensions/metrics (the grain)?
  • What lookback window matches how late your GA4 data settles?
  • Target MotherDuck database and dataset (DESTINATION_DATABASE, DATASET_NAME); is letting the Flight create the database acceptable?
  • Load behavior: date-partition merge with dimensions as the row key (default), replace, or append?
  • Which service account token, and is the GA4 service-account key stored as a Flights secret (not config)?
  • What schedule (cron) should it run on?

Prerequisites (google side)​

  1. Enable the Google Analytics Data API in a GCP project.
  2. Create a service account and download its JSON key.
  3. In GA4 Admin → Property Access Management, add the service account's email as a Viewer on the property. (API enabled but service account not granted on the property is the most common cause of a 403.)
  4. Note the numeric GA4 Property ID (Admin → Property Settings), e.g. 123456789 — not the G-XXXXXXX measurement id.

Caveats​

  • Aggregated, not raw. See Aggregated reports, not raw events.
  • dlt does not create the database. It creates the dataset (schema) and tables, so the Flight pre-creates DESTINATION_DATABASE with CREATE DATABASE IF NOT EXISTS.
  • merge needs date and a primary key. With WRITE_DISPOSITION=merge, GA4_DIMENSIONS must include date so the Flight can replace each returned date partition. PRIMARY_KEY defaults to the dimension columns. Keep it aligned with your grain or switch to append/replace.
  • Keep source credentials out of config. The GA4 service-account key is a secret. Add a GA4_SERVICE_ACCOUNT_JSON parameter to a MotherDuck Flights secret, then attach that secret to the Flight through flight_secret_names. The Flight reads the attached, prefixed environment variable.
  • Keep the token out of config. The runtime attaches a MotherDuck token and injects it as MOTHERDUCK_TOKEN; never place a token in config.

What you'll adjust​

Every knob is a config/env value read at the top of flight.py. Set them as Flight config, not by editing code. The source itself lives in the ga4_rows() function.

Config keyDefaultPurpose
GA4_PROPERTY_ID(required)Numeric GA4 property id, e.g. 123456789. Validated as digits.
GA4_DIMENSIONSdate,sessionDefaultChannelGroupComma-separated GA4 dimension API names. Defines the grain. Must include date in merge mode.
GA4_METRICSsessions,totalUsers,screenPageViewsComma-separated GA4 metric API names. The Flight preserves integer and currency values, and loads other metric types as doubles.
GA4_START_DATE7daysAgoReport start date. Relative (NdaysAgo, yesterday, today) or YYYY-MM-DD.
GA4_END_DATEyesterdayReport end date. Same formats as GA4_START_DATE.
DESTINATION_DATABASEga4_ingestMotherDuck database dlt loads into. Created if missing. Validated as a SQL identifier.
DATASET_NAMEga4dlt dataset (schema) that holds the loaded tables.
TABLE_NAMEga4_reportdlt table name for the loaded rows.
WRITE_DISPOSITIONmergemerge (default; replaces returned date partitions), append, or replace.
PRIMARY_KEYGA4_DIMENSIONSRow key in merge mode. Defaults to the dimension columns.
PIPELINE_NAMEga4_dlt_ingestdlt pipeline name (also used for dlt state).
RUN_LEDGER_TABLEdlt_ingest_runsAudit table in the database's main schema. Validated as a SQL identifier.
GA4_SERVICE_ACCOUNT_JSON(Flights secret)Service-account key JSON. Store as a Flights secret, never in config.
MOTHERDUCK_TOKEN(Flight-injected)Auth. Select a token on the Flight; never put it in config.

Run it​

You need a MotherDuck account and access token, a GA4 property, and a service account with Viewer access on that property (see Prerequisites).

To smoke-test the pipeline locally before deploying, run the file directly against your account, supplying the GA4 key inline:

export MOTHERDUCK_TOKEN=your_token_here
export GA4_SERVICE_ACCOUNT_JSON="$(cat service-account.json)"
GA4_PROPERTY_ID=123456789 uv run --with-requirements requirements.txt flight.py

That single run creates the ga4_ingest database, loads the last 7 days of the default report into ga4.ga4_report, and writes one ledger row. Override any default inline, for example:

GA4_PROPERTY_ID=123456789 \
GA4_DIMENSIONS=date,country,deviceCategory \
GA4_METRICS=sessions,engagedSessions,conversions \
uv run --with-requirements requirements.txt flight.py

Deploy as a Flight​

Create the Flight with the MD_CREATE_FLIGHT SQL function (no deploy SQL is checked in; adapt the arguments to your situation), passing:

  • name: a Flight name, for example ga4_dlt_ingest
  • source_code: the contents of flight.py
  • requirements_txt: the contents of requirements.txt
  • flight_secret_names: ["ga4_creds"] so the GA4_SERVICE_ACCOUNT_JSON parameter is injected as ga4_creds_GA4_SERVICE_ACCOUNT_JSON
  • config: the keys from What you'll adjust you want to override (at minimum GA4_PROPERTY_ID; omit any you are keeping at default)

Before the first run, create the ga4_creds Flights secret with a GA4_SERVICE_ACCOUNT_JSON parameter. You can create it in Settings > Secrets or with CREATE SECRET ... (TYPE flights, ...). Then attach it through flight_secret_names and select a MotherDuck token. The runtime injects the token as MOTHERDUCK_TOKEN.

Create the Flight without a schedule first, trigger one manual run with MD_RUN_FLIGHT(flight_id := ...) (the id is returned by MD_CREATE_FLIGHT and listed by MD_FLIGHTS()), and confirm it succeeds and the GA4 tables and ledger row appear. Once the manual run is green, add a daily schedule (15 7 * * *, 07:15 UTC, is a reasonable default) by updating the Flight's schedule_cron with MD_UPDATE_FLIGHT. Schedule updates are metadata-only and do not create a new Flight version.

Security​

  • Identifier validation. DESTINATION_DATABASE and RUN_LEDGER_TABLE flow into CREATE/INSERT statements that cannot be parameterized, so each is checked against ^[A-Za-z_][A-Za-z0-9_]*$ before any SQL runs. GA4_PROPERTY_ID is validated as digits.
  • Parameterized data. The ledger row (pipeline name, dataset, table, and load summary) is written with bound parameters, never string-formatted into SQL.
  • Secret handling. The GA4 service-account key is read from a prefixed environment variable supplied by an attached Flights secret, or the unprefixed local value during development. It is never written to disk or placed in config.

Learn more​