Loading Events into Data Warehouses

Hookbase delivers webhook events to your data warehouse using object storage as the integration point. A warehouse destination batches events and writes them as JSONL (or JSON) files to Amazon S3, Cloudflare R2, Google Cloud Storage, or Azure Blob Storage. From there, every major warehouse can load or query those files directly.

This guide shows how to get Hookbase events into Snowflake, BigQuery, and Databricks, or query them in place with Amazon Athena and Redshift Spectrum.

The pattern

Hookbase owns the extract + load half of the pipeline: it receives webhooks, applies your filters and transforms, batches the results, and writes durable files to object storage. Your warehouse owns the transform + query half. Because the hand-off is plain files in cloud storage, the same setup works with any engine that can read a bucket.

There are two ways to consume the files, and you can mix them:

• Query in place — define an external table over the bucket prefix. No copy step; the warehouse reads files on demand. Best for ad-hoc analysis and archival.
• Ingest into native tables — COPY the files into managed tables, either as a scheduled batch or continuously as new files land. Best for dashboards and joins at scale.

The record shape

Each line of a Hookbase file is the webhook payload itself. Hookbase does not wrap it in an envelope, so there are no event_id or received_at columns to load:

{"type":"payment_intent.succeeded","data":{"amount":2500,"currency":"usd"}}

The natural landing schema is therefore either a single semi-structured column holding the whole payload, or — better for querying — the payload's own top-level fields as columns, which every engine here can infer automatically.

Snowflake

Use a storage integration and an external stage, then COPY INTO a table with a VARIANT column.

-- 1. One-time: grant Snowflake access to the bucket (S3 shown; GCS/Azure analogous)
CREATE STORAGE INTEGRATION hookbase_s3
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/hookbase-snowflake'
  STORAGE_ALLOWED_LOCATIONS = ('s3://my-data-lake/webhooks/');
 
-- 2. File format + external stage
CREATE FILE FORMAT hookbase_jsonl TYPE = JSON STRIP_OUTER_ARRAY = FALSE;
 
CREATE STAGE hookbase_stage
  URL = 's3://my-data-lake/webhooks/'
  STORAGE_INTEGRATION = hookbase_s3
  FILE_FORMAT = hookbase_jsonl;
 
-- 3. Landing table: one VARIANT column holding the payload, plus a load timestamp
--    of your own (the file carries no Hookbase timestamp)
CREATE TABLE webhook_events (
  payload   VARIANT,
  loaded_at TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
);
 
-- 4. Bulk load existing files
COPY INTO webhook_events (payload)
FROM (SELECT $1 FROM @hookbase_stage)
PATTERN = '.*[.]jsonl';

For continuous loading, point a Snowpipe at the same stage so new files load automatically:

CREATE PIPE hookbase_pipe AUTO_INGEST = TRUE AS
COPY INTO webhook_events (payload)
FROM (SELECT $1 FROM @hookbase_stage);

BigQuery

Hookbase writes natively to GCS, which BigQuery loads directly. Let BigQuery infer the payload's fields as columns:

LOAD DATA INTO mydataset.webhook_events
FROM FILES (
  format = 'JSON',                       -- newline-delimited JSON
  uris = ['gs://my-gcs-bucket/webhooks/*.jsonl']
);

To keep the payload intact in a single native JSON column instead, declare it explicitly and load each line into it:

LOAD DATA INTO mydataset.webhook_events_raw (payload JSON)
FROM FILES (
  format = 'CSV',                        -- one whole JSON object per "row"
  field_delimiter = '\x1f',
  quote = '',
  uris = ['gs://my-gcs-bucket/webhooks/*.jsonl']
);

To query in place without loading, define an external table over the prefix:

CREATE EXTERNAL TABLE mydataset.webhook_events_ext
OPTIONS (
  format = 'NEWLINE_DELIMITED_JSON',
  uris = ['gs://my-gcs-bucket/webhooks/*']
);

Databricks

Create a Delta table and COPY INTO it from the bucket. Databricks infers the payload's structure, so its top-level keys become columns and nested objects land as structs.

CREATE TABLE IF NOT EXISTS webhook_events;
 
COPY INTO webhook_events
FROM 's3://my-data-lake/webhooks/'
FILEFORMAT = JSON
COPY_OPTIONS ('mergeSchema' = 'true');

For a continuously updating table, use Auto Loader, which incrementally picks up new files as Hookbase writes them:

(spark.readStream
    .format("cloudFiles")
    .option("cloudFiles.format", "json")
    .option("cloudFiles.schemaLocation", "/Volumes/main/default/hookbase/_schema")
    .load("s3://my-data-lake/webhooks/")
    .writeStream
    .option("checkpointLocation", "/Volumes/main/default/hookbase/_checkpoint")
    .toTable("webhook_events"))

Amazon Athena & Redshift Spectrum

Both query files directly in S3 with no load step. Declaring a schema by hand is where field mapping shines — map the fields you query into typed top-level columns, then declare exactly those columns:

-- Matches a destination whose fieldMapping targets are
-- event_type, amount, currency and occurred_at
CREATE EXTERNAL TABLE webhook_events (
  event_type  string,
  amount      bigint,
  currency    string,
  occurred_at timestamp
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true')
LOCATION 's3://my-data-lake/webhooks/';

Then query as usual:

SELECT event_type, COUNT(*), SUM(amount)
FROM webhook_events
WHERE occurred_at > current_timestamp - interval '7' day
GROUP BY event_type;

Without a field mapping, declare the payload's own top-level keys instead; the JSON SerDe leaves any column it cannot find as NULL.

Querying nested payload fields

If you landed the payload whole — or it has nested objects such as Stripe's data — each engine extracts from it with its own JSON syntax:

-- Snowflake (payload VARIANT)
SELECT payload:type::string AS event_type,
       payload:data.amount::number AS amount
FROM webhook_events;
 
-- BigQuery (payload JSON)
SELECT JSON_VALUE(payload, '$.type') AS event_type,
       JSON_VALUE(payload, '$.data.amount') AS amount
FROM mydataset.webhook_events_raw;
 
-- Databricks (inferred struct column)
SELECT type AS event_type,
       data.amount AS amount
FROM webhook_events;

Deduplicating rows

Hookbase retries failed uploads, so the same event can appear in more than one file. The file carries no Hookbase event id, so deduplicate on an id the provider put in the payload — and make sure that field survives into your table, either by inference or as a field-mapping target:

-- Stripe puts its own event id at the payload root
SELECT *
FROM webhook_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY payload:id::string ORDER BY loaded_at DESC) = 1;

If a provider sends no stable id, hash the row instead (MD5(TO_JSON(payload)) in Snowflake, TO_HEX(MD5(TO_JSON_STRING(payload))) in BigQuery) and dedupe on that.

Related

• Warehouse Destinations — configure S3, R2, GCS, and Azure Blob
• Field Mapping — project payload fields into typed columns
• Transforms — reshape payloads before they land in the bucket
• Filters — only warehouse the events you care about