Skip to content
Tutorials Center
My learning

Topics

Lesson 34 of 37 ยท Pipelines and Integrations

Streaming, Dataform and Connected Tools

Stream events into BigQuery with the Storage Write API and Pub/Sub, build SQL pipelines with Dataform, and connect Looker Studio, Sheets, dbt and Airflow.

Batch loads and scheduled queries cover many pipelines, but two needs push you further. Some data must arrive within seconds, such as clickstream events or sensor readings. And once you have dozens of SQL transformations that depend on each other, a list of independent schedules becomes hard to manage. This lesson covers the streaming options, the SQL-pipeline tools Dataform and dbt, and the tools people use to see and share results.

None of these services can run on this site, so configuration and code for them are read-only. The runnable examples model two ideas you must understand to use them well: de-duplicating streamed events, and ordering transformations by their dependencies.

Streaming ingestion

Storage Write API is the recommended way to stream rows into BigQuery. It offers high throughput, lower cost than the older API, and exactly-once semantics when you use committed streams with stream offsets. Client libraries exist for Java, Go, Python and others. Rows become available for query within seconds, and data in the streaming buffer is queryable even before it is written to long-term storage.

Legacy streaming (tabledata.insertAll) is simple: you send rows as JSON, optionally with an insertId for best-effort de-duplication. It is still found in older code but costs more and offers weaker guarantees.

For most teams the easiest path involves no code at all:

  • Pub/Sub BigQuery subscription. Point a Pub/Sub topic at a table and each message becomes a row. Good for application events.
  • Dataflow templates, such as "Pub/Sub to BigQuery", add parsing, enrichment and dead-letter handling.
  • Datastream replicates database changes (CDC) from MySQL, PostgreSQL and Oracle.
  • BigQuery change data capture with _CHANGE_TYPE lets a stream apply upserts and deletes to a table.
flowchart LR
  A["Apps and devices"] --> P["Pub/Sub topic"]
  P -->|BigQuery subscription| T["Raw events table"]
  P --> D["Dataflow (optional)"]
  D --> T
  T --> V["Scheduled or Dataform SQL"]
  V --> R["Reporting tables"]

Raw events land in a table first; SQL models then turn them into clean reporting tables.

Handling duplicates and late data

Streams repeat messages (retries, at-least-once delivery) and send events late. Design the raw table to accept this, then clean it with SQL. A standard pattern keeps the latest row per event ID with ROW_NUMBER().

SQL De-duplicate a raw event stream

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery you would write the same ROW_NUMBER() (or use QUALIFY ROW_NUMBER() OVER (...) = 1) in a view or a scheduled table, and partition the raw table by received_at so the de-duplication only scans recent days.

Dataform: SQL pipelines as code

Dataform is built into BigQuery. You write each transformation as a .sqlx file; Dataform works out the order from references, compiles to SQL, runs it in dependency order and can schedule it. Files live in a Git repository, so changes are reviewed and versioned.

SQL
-- definitions/daily_revenue.sqlx
config {
  type: "incremental",
  schema: "shop_reports",
  uniqueKey: ["day", "country"],
  bigquery: { partitionBy: "day" },
  assertions: { nonNull: ["day", "country"] }
}

SELECT
  DATE(order_ts) AS day,
  country,
  SUM(total) AS revenue
FROM ${ref("orders_clean")}
${when(incremental(), `WHERE DATE(order_ts) >= CURRENT_DATE() - 3`)}
GROUP BY day, country

${ref("orders_clean")} declares a dependency and resolves to the right table name. Table types include table, view, incremental and declaration (an existing source). Assertions are data tests, like "no null keys" or "rows are unique", that fail the run when violated. dbt offers the same model-and-test approach with its own syntax and community packages, and runs against BigQuery through the dbt-bigquery adapter; teams choose between them on tooling preferences.

Both tools solve the ordering problem, which you can model directly: given each model's dependencies, find a valid run order.

Python Order models by dependency

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The sorter places sources first and the dashboard last, and rejects circular dependencies, exactly what a pipeline tool must do before it runs anything.

Connecting tools to BigQuery

Once data is clean, people need to see it:

Where results go

Looker Studio Free dashboards on tables
Connected Sheets Pivot billions of rows
Looker Governed semantic model
Notebooks Colab and BigQuery DataFrames

Dashboards re-run queries: cache and summarize.

1 / 2
  • Looker Studio connects to a table, view or custom query. Point it at small summary tables (not raw events) and set data freshness so it does not re-scan on every refresh.
  • Connected Sheets lets analysts explore a table in a spreadsheet interface; queries run in BigQuery.
  • BI Engine keeps frequently read data in memory to speed up dashboards.
  • Orchestration: Cloud Composer (managed Apache Airflow) has BigQueryInsertJobOperator; Workflows and Cloud Scheduler call the API for simpler jobs; Eventarc can start a job when a file lands in Cloud Storage.
  • Reverse ETL and exports: EXPORT DATA writes query results to Cloud Storage, and tools like Hightouch or Census sync tables back to applications.
SQL
EXPORT DATA OPTIONS (
  uri = 'gs://my-bucket/exports/daily_revenue_*.parquet',
  format = 'PARQUET',
  overwrite = TRUE
) AS
SELECT * FROM shop_reports.daily_revenue WHERE day = CURRENT_DATE() - 1;

Putting it together

A common architecture: apps publish events to Pub/Sub, a BigQuery subscription lands them in a partitioned raw table, Dataform builds clean and aggregated tables on a schedule with assertions, and Looker Studio reads the aggregates. Add budgets and maximum_bytes_billed from controlling query cost to keep it cheap.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which API is recommended for high-throughput streaming with exactly-once semantics?

    The Storage Write API offers higher throughput, lower cost and exactly-once delivery with committed streams.
  2. 2. Why de-duplicate raw streamed events with ROW_NUMBER()?

    At-least-once delivery and retries create duplicates, so keep the first row per event ID.
  3. 3. What does ref() do in a Dataform .sqlx file?

    ref() lets Dataform order models by dependency and compile the right table reference.
  4. 4. What should a Looker Studio dashboard usually query?

    Summaries keep dashboards fast and cheap since every refresh runs a query.

Summary

  • Stream with the Storage Write API, Pub/Sub BigQuery subscriptions, Dataflow or Datastream.
  • Expect duplicates and late data; keep raw tables and clean them in SQL.
  • Dataform and dbt manage SQL models, dependencies, incremental builds and data tests.
  • Looker Studio, Connected Sheets, BI Engine and Composer connect and orchestrate the rest.
  • EXPORT DATA sends results out to Cloud Storage.

Next: Project: E-commerce Analytics