Skip to main content
Google BigQuery summary table

See when the export last ran and updated the data

Niclas Bångman avatar
Written by Niclas Bångman
Updated over a week ago

Background

To be able to get an overview of historical jobs and the latest status you can also create an 'export summary' table from your BigQuery Share in Funnel. If you have multiple Shares targeting the summary table, these entries will be appended.
The use of a summary table is enabled by checking the following option that can be found under Advanced when creating/editing the Share in Funnel.

Table Schema

Field name

Type

Mode

Description

account_id

STRING

NULLABLE

Funnel account ID

export_id

STRING

NULLABLE

Unique Funnel Share configuration

run_id

STRING

NULLABLE

Unique ID for a single sharing job

total_count

INTEGER

NULLABLE

Files shared

fail_count

INTEGER

NULLABLE

Files that failed to be shared

success_count

INTEGER

NULLABLE

Files that were shared successfully

status

STRING

NULLABLE

Status of the sharing job, 'Success' or 'Fail'

start_at

TIMESTAMP

NULLABLE

UTC timestamp of when the sharing job started

end_at

TIMESTAMP

NULLABLE

UTC timestamp of when the sharing job ended

message

STRING

NULLABLE

Status message from failed sharing job

files

RECORD

REPEATED

Files in a sharing job

files. file_name

STRING

NULLABLE

Name of the shared file

files. status

STRING

NULLABLE

Status of file sharing, 'Success' or 'Fail'

files. message

STRING

NULLABLE

Status message from failed file sharing

files. updated

BOOLEAN

NULLABLE

If the file changed, true or false

files. row_count

INTEGER

NULLABLE

The number if rows exported

files. file_size_bytes

INTEGER

NULLABLE

The number of bytes exported. Multiply it by 1024 to get exported kB.

Use case

The export summary can be used to make you data pipe more efficient. If you look at the last run in the summary you can see which files has updated=true these are the only partitions or tables (depending on if you use single table setup or not) that has changed and needs to be reloaded.

It can also be used to provide stats and setup your own alerts in your system.

Example Queries

List all files and statuses

SELECT
file.file_name,
file.status
FROM
`export_summary`,
UNNEST(files) AS file
ORDER BY
end_at DESC

Number of Failed/Successful sharing jobs per day

SELECT
DATE(end_at) as date,
SUM(total_count) as total_total,
SUM(success_count) as success_total,
SUM(fail_count) as fail_total,
FROM
`export_summary`
GROUP BY
1
ORDER BY
1 DESC
Did this answer your question?