Under the hood
How data loads
What happens when a dataset loads or reloads.
A dataset either points at data where it already is, or holds a copy GOAT manages. This page covers each kind, what a load and a reload do, and how failures are handled. For the steps in the app, see Connect and upload data.
The kinds of dataset
| Kind | Where the rows live | What a load does |
|---|---|---|
| Live connect | Your BigQuery table or view. | Nothing. Every chart queries your table directly. |
| Managed copy of a BigQuery table | A table in the install's goat dataset. | Copies your table with one CREATE OR REPLACE TABLE ... AS SELECT query. A reload runs the same query, so the copy mirrors its source. |
| Cloud Storage files, loaded | A table in the goat dataset. | A BigQuery load job over every saved file location. |
| Cloud Storage files, read in place | Your bucket, through a BigQuery external table. | Registers the external table. Nothing is copied. |
| Uploaded files | The install's uploads bucket, then a table in the goat dataset. | A BigQuery load job over the uploaded files. |
| Push API | A table in the goat dataset. | Rows your application sends are gathered and added with a load job. |
A load and a reload
A reload makes the table equal to its sources again; it does not add to it.
- GOAT creates the table with its declared columns, partitioning, and clustering, if it does not exist.
- It runs one BigQuery load job over every saved location, replacing the table's contents. Column types are the declared ones; BigQuery does not guess them.
- A row that cannot be read fails the load. GOAT never skips bad rows silently.
BigQuery load jobs are free. Copies of BigQuery tables and the steps below are queries, billed by the data they read.
For CSV files whose dates need a chosen format, GOAT first loads the file as text into a temporary table, then converts it with one query. The temporary table is removed when the load ends.
Only uploaded files that finished uploading are loaded. Anything else in the uploads folder is skipped and logged.
Each load is recorded as a job on the dataset, with its state, rows, bytes, BigQuery cost, and timing. A dataset keeps its newest 100 finished jobs.
Composite tenant keys
A managed copy can take its tenant key from several columns. During the load, GOAT joins their values with :: into one text column, which becomes the tenant key. A missing value, or a value that holds :, fails the load, so two different customers can never end up with the same key.
Layout
A copy of a BigQuery table keeps its source's layout. For other managed copies, the layout is the one declared when the dataset was created; GOAT can propose a date column to partition by when the choice is clear. For pushed data only, GOAT coarsens the partition (from hour to day to month) on the first batch to stay within BigQuery's partition limits. A composite tenant key, built during the load, leads the table's clustering, so a tenant's query reads less.
Changing a column's type or layout after data has landed rebuilds the table: GOAT builds the new table beside the old one, then swaps it in. A rebuild reads the whole table and is not capped.
Failures
A load replaces the table in one BigQuery job, so a load that fails leaves the previous table as it was. A load that would make more partitions than BigQuery allows fails with TOO_MANY_PARTITIONS; the fix is a rebuild at a coarser partition, such as by day or month instead of by hour. The job records why it failed, in a typed reason the app turns into plain words, with a report of the values that could not be read.
Freshness
| Kind | How current a chart is |
|---|---|
| Live connect, read in place | As current as your table, within the answer cache's window (5 minutes by default). |
| Managed copies | As of the last load. Each load changes the dataset's data version, so cached answers from before are never served. |