How can you automate data insertion into BigQuery using Python?

There are three ingestion paths, and choosing correctly is most of the answer.

Load jobs are the batch path and are free of insert charges. Use them for files or DataFrames:

from google.cloud import bigquery
client = bigquery.Client()
job_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_APPEND",
    schema=[
        bigquery.SchemaField("event_id", "STRING"),
        bigquery.SchemaField("amount", "NUMERIC"),
    ],
)
job = client.load_table_from_dataframe(df, "project.dataset.events", job_config=job_config)
job.result()   # block until the load completes

The Storage Write API is the modern streaming path. It is cheaper than legacy streaming inserts, supports exactly-once delivery through stream offsets, and is what new pipelines should use for real-time data.

Legacy streaming inserts via insert_rows_json() are simple but cost more per row and are best-effort on duplicates.

Picking one

Batch loads for anything scheduled — they cost nothing, handle large volumes, and are atomic per job. Streaming only when freshness genuinely matters in seconds. Loading a file once an hour is almost always the right answer, and reaching for streaming by default is a common and expensive mistake.

Automating the run

Cloud Scheduler with Cloud Run or Cloud Functions covers simple periodic jobs. Cloud Composer, which is managed Airflow, is the right choice once you have dependencies, backfills or retries. Event-driven loads triggered by a file landing in Cloud Storage suit arrival-based pipelines.

Idempotency

Automated jobs get retried, so appends must not double-count. Load into a staging table and MERGE on a business key:

MERGE dataset.events T
USING dataset.events_staging S
ON T.event_id = S.event_id
WHEN NOT MATCHED THEN INSERT ROW;

Operational details

Authenticate with a service account holding only bigquery.dataEditor on the target dataset, supplied through Workload Identity rather than a downloaded key file. Partition the destination table by ingestion date or an event timestamp so queries prune, and cluster on your most common filter column.

In the interview, also mention that WRITE_TRUNCATE makes a full-refresh load naturally idempotent without a MERGE.