Google Cloud BigQuery Setup

-- run_date variable declaration: DATE type with default value set to yesterday's date.
-- This date serves as the end date for data query period and the basis for file path generation.
DECLARE run_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- start_of_month variable declaration: DATE type with default value set to the first day of the month containing 'run_date' (yesterday).
-- Example: If today is September 4, 2025, run_date is September 3, and start_of_month is September 1.
DECLARE start_of_month DATE DEFAULT DATE_TRUNC(run_date, MONTH);

-- EXPORT DATA statement: Exports query results to Google Cloud Storage (GCS).
EXPORT DATA OPTIONS(
  -- uri: Specifies the GCS path and filename where data will be stored.
  -- The path is dynamically generated based on the year and month of 'run_date' (yesterday).
  uri=CONCAT(
    'gs://{GCS_BUCKET}/{PROJECT_ID}/',
    FORMAT_DATE('%Y/%m', run_date), -- 'YYYY/MM' format folder path (based on yesterday)
    '/billing_data_',
    FORMAT_DATE('%Y%m', run_date),  -- 'YYYYMM' format filename part (based on yesterday)
    '-*.parquet'
  ),
  format='PARQUET',        -- File format is Parquet
  compression='GZIP',      -- Compression method is GZIP
  overwrite=true           -- Allow overwriting existing files
) AS
-- Exports the results of the SELECT query below.
SELECT *
FROM `{DATA_SET_ID}`
WHERE
  -- Only scans data partitions from '1st of this month' to 'yesterday' based on _PARTITIONTIME.
  -- This improves query performance and reduces costs.
  DATE(_PARTITIONTIME) BETWEEN start_of_month AND run_date
  -- Filters data where invoice.month field matches the month containing 'yesterday' ('YYYYMM' format).
  -- Example: If run_date is 2025-09-03, only data with invoice.month = '202509' is selected.
  AND invoice.month = FORMAT_DATE('%Y%m', run_date);

-- run_date variable declaration: DATE type with default value set to yesterday's date.
-- CURRENT_DATE() returns today's date, and subtracting INTERVAL 1 DAY calculates yesterday.
DECLARE run_date DATE DEFAULT DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

-- prev_month_start variable declaration: DATE type with default value set to the first day of the previous month.
-- Example: If today is September 4, 2025, then (1) subtract one month from CURRENT_DATE() (August 4, 2025),
-- (2) use DATE_TRUNC function to convert to the first day of that month (August 1, 2025).
DECLARE prev_month_start DATE DEFAULT DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH);

-- EXPORT DATA statement: Exports query results to Google Cloud Storage (GCS).
EXPORT DATA OPTIONS(
  -- uri: Specifies the GCS path and filename where data will be stored.
  -- Uses CONCAT function to dynamically generate the path.
  uri=CONCAT(
    'gs://{GCS_BUCKET}/{PROJECT_ID}/',
    FORMAT_DATE('%Y/%m', prev_month_start), -- Generate 'YYYY/MM' format folder path
    '/billing_data_',
    FORMAT_DATE('%Y%m', prev_month_start),  -- Generate 'YYYYMM' format filename part
    '-*.parquet' -- Wildcard (*) indicating BigQuery can export to multiple files
  ),
  format='PARQUET',        -- File format is specified as Parquet
  compression='GZIP',      -- Compression method is specified as GZIP
  overwrite=true           -- Overwrite setting when files with the same name already exist
) AS
-- Exports the results of the SELECT query below.
SELECT * -- Selects all columns.
FROM `{DATA_SET_ID}` -- Target GCP billing data table
WHERE
  -- _PARTITIONTIME refers to the partition date of the partitioned table.
  -- Using this condition reduces the amount of data scanned, improving query performance and reducing costs.
  -- Here, it limits scanning to only data partitions from '1st of last month' to 'yesterday'.
  DATE(_PARTITIONTIME) BETWEEN prev_month_start AND run_date
  -- Filters data where invoice.month field matches 'last month'.
  -- Used together with _PARTITIONTIME filtering to extract exactly the billing data for the previous month.
  AND invoice.month = FORMAT_DATE('%Y%m', prev_month_start);