Warehouse recipes

These recipes take data that a cloud storage bucket, BigQuery or Google Sheets destination delivers and put it where your team queries it. They are deliberately minimal — a starting point to adapt, not a full pipeline.

Before you start

• Use Parquet for bucket destinations feeding a warehouse. Its columns are typed, so nothing needs parsing, and text arrives exactly as measured. (CSV works, but text that looks like a formula carries a leading ' — see CSV.)
• Know the layout. A bucket destination writes <folder>/<dataset>/dt=<YYYY-MM-DD>/<run_id>.parquet, and a manifest at <folder>/_manifests/<run_id>.json once every file of the run is complete — see what gets written. The examples use the folder isearchfrom and the position_tracker.results dataset, whose folder is position_tracker-results.
• Read with your own credentials. The key you gave iSearchFrom only needs to write. Give your warehouse its own read access to the bucket.
• De-duplicate on row_id. Delivery is at least once: a backfill or a retried run can deliver a row twice, always with the same row_id. Every recipe below ends with a de-duplicated view or query — build your reports on that.

Snowflake

Point an external stage at the folder, then load it with COPY INTO — by hand, on a schedule, or continuously with Snowpipe.

CREATE FILE FORMAT isf_parquet TYPE = PARQUET;

-- Amazon S3. For Google Cloud Storage use STORAGE_PROVIDER = 'GCS' and gcs:// URLs,
-- then grant the service account Snowflake creates read access to the bucket.
CREATE STORAGE INTEGRATION isf_bucket
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake-isearchfrom'
  STORAGE_ALLOWED_LOCATIONS = ('s3://your-bucket/isearchfrom/');

CREATE STAGE isf_stage
  URL = 's3://your-bucket/isearchfrom/'
  STORAGE_INTEGRATION = isf_bucket
  FILE_FORMAT = isf_parquet;

-- A table shaped like the files.
CREATE TABLE position_tracker_results USING TEMPLATE (
  SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
  FROM TABLE(INFER_SCHEMA(
    LOCATION => '@isf_stage/position_tracker-results/',
    FILE_FORMAT => 'isf_parquet',
    IGNORE_CASE => TRUE
  ))
);

-- Load. COPY INTO remembers the files it has loaded and skips them next time.
COPY INTO position_tracker_results
  FROM @isf_stage/position_tracker-results/
  FILE_FORMAT = (FORMAT_NAME = 'isf_parquet')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Load by manifest. To load only complete runs, read each new manifest and pass its files to COPY INTO. The manifest’s keys include your folder; drop it, since the stage already points there:

COPY INTO position_tracker_results
  FROM @isf_stage
  FILES = ('position_tracker-results/dt=2026-09-30/0199a7c2-5b1e-7c3a-9f10-3d2b8e6a4c11.parquet')
  FILE_FORMAT = (FORMAT_NAME = 'isf_parquet')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Or load continuously with Snowpipe. Create a pipe over the same COPY INTO, then add an S3 event notification for new objects under isearchfrom/position_tracker-results/ that targets the queue in the pipe’s notification_channel (from SHOW PIPES). Snowpipe loads every file as it lands, including files of a run that later fails — the de-duplicated view below takes care of those.

CREATE PIPE isf_position_tracker_results AUTO_INGEST = TRUE AS
  COPY INTO position_tracker_results
  FROM @isf_stage/position_tracker-results/
  FILE_FORMAT = (FORMAT_NAME = 'isf_parquet')
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

CREATE VIEW position_tracker_results_dedup AS
SELECT * FROM position_tracker_results
QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY measured_at) = 1;

Amazon Redshift

Redshift loads Parquet with COPY … FORMAT PARQUET. It matches columns by position, so the table must have the file’s columns in the file’s order — the dataset reference order, or the columns list of the manifest.

CREATE TABLE isf_position_tracker_results (
  row_id        VARCHAR(MAX) NOT NULL,
  measured_at   TIMESTAMP    NOT NULL, -- UTC
  run_id        VARCHAR(MAX),
  campaign_id   VARCHAR(MAX),
  campaign_name VARCHAR(MAX),
  project_id    VARCHAR(MAX),
  country       VARCHAR(MAX),
  language      VARCHAR(MAX),
  device        VARCHAR(MAX),
  search_engine VARCHAR(MAX),
  city          VARCHAR(MAX),
  keyword       VARCHAR(MAX),
  domain        VARCHAR(MAX),
  position      BIGINT,
  page          BIGINT,
  url           VARCHAR(MAX),
  title         VARCHAR(MAX),
  description   VARCHAR(MAX)
);
CREATE TABLE isf_position_tracker_results_staging (LIKE isf_position_tracker_results);

-- For each file listed in a new manifest:
COPY isf_position_tracker_results_staging
FROM 's3://your-bucket/isearchfrom/position_tracker-results/dt=2026-09-30/0199a7c2-5b1e-7c3a-9f10-3d2b8e6a4c11.parquet'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-isearchfrom'
FORMAT AS PARQUET;

-- Then fold the staged rows in, replacing any row already loaded.
BEGIN;
DELETE FROM isf_position_tracker_results
USING isf_position_tracker_results_staging s
WHERE isf_position_tracker_results.row_id = s.row_id;
INSERT INTO isf_position_tracker_results SELECT * FROM isf_position_tracker_results_staging;
TRUNCATE isf_position_tracker_results_staging;
COMMIT;

• List columns (string_array, e.g. in ai_visibility.mentions) load into a SUPER column when you add SERIALIZETOJSON: FORMAT AS PARQUET SERIALIZETOJSON. JSON columns arrive as text; use JSON_PARSE to turn them into SUPER.
• The bucket must be in the same AWS Region as the cluster.
• When a dataset gains a column, it is added at the end of the file. Add it to both tables with ALTER TABLE … ADD COLUMN before the next load. The same applies if you change the column pick.
• From CSV instead: FORMAT AS CSV IGNOREHEADER 1 GZIP TIMEFORMAT 'auto'.

Databricks

Auto Loader picks up new files in the folder incrementally and remembers what it has loaded. With Unity Catalog, first create an external location for the bucket.

(
    spark.readStream.format("cloudFiles")
    .option("cloudFiles.format", "parquet")
    .option("cloudFiles.schemaLocation", "/Volumes/main/isearchfrom/_meta/position_tracker_results/schema")
    .option("cloudFiles.partitionColumns", "dt")
    .load("s3://your-bucket/isearchfrom/position_tracker-results/")
    .writeStream
    .option("checkpointLocation", "/Volumes/main/isearchfrom/_meta/position_tracker_results/checkpoint")
    .trigger(availableNow=True)
    .toTable("main.isearchfrom.position_tracker_results")
)

Run it as a scheduled job after your destination’s schedule. With availableNow, each run loads what is new and stops. When a dataset gains a column, Auto Loader stops the stream once with a schema change and picks the column up when the job runs again.

CREATE OR REPLACE VIEW main.isearchfrom.position_tracker_results_dedup AS
SELECT * FROM main.isearchfrom.position_tracker_results
QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY measured_at) = 1;

For Google Cloud Storage, use the gs:// path.

DuckDB

DuckDB queries the bucket in place — no loading step. Good for analysis on a laptop and for quick checks of what was delivered.

INSTALL httpfs;
LOAD httpfs;

CREATE SECRET isf (
  TYPE s3,
  KEY_ID 'AKIA…',
  SECRET '…',
  REGION 'eu-central-1'
);

SELECT keyword, domain, position, measured_at
FROM read_parquet(
  's3://your-bucket/isearchfrom/position_tracker-results/*/*.parquet',
  hive_partitioning = true
)
WHERE dt >= '2026-09-01'
QUALIFY row_number() OVER (PARTITION BY row_id ORDER BY measured_at) = 1
ORDER BY measured_at DESC;

• Other providers: for Cloudflare R2 use TYPE r2 with ACCOUNT_ID and r2:// paths; for Google Cloud Storage TYPE gcs with an HMAC key and gs:// paths; for Wasabi, Backblaze B2, DigitalOcean Spaces and MinIO add ENDPOINT 's3.eu-central-1.wasabisys.com' (the host, without https://) and, where path-style is used, URL_STYLE 'path'.
• dt comes from the folder names, so filtering on it reads only those days.
• CSV files work the same way with read_csv('…/*/*.csv.gz').

Looker Studio

Looker Studio doesn’t read files from a bucket; connect it to a BigQuery or Google Sheets destination instead.

From BigQuery — the better choice for anything beyond a small report:

1. In Looker Studio, Add data → BigQuery, then choose your project, the export’s dataset and a table such as isf_position_tracker__checks.

2. Use measured_at as the date range dimension, so report date filters only scan those days of the day-partitioned table.

3. To drop repeated rows, choose Custom query instead of a table:

   SELECT * FROM `acme-analytics.isearchfrom.isf_position_tracker__checks`
   WHERE measured_at >= TIMESTAMP(PARSE_DATE('%Y%m%d', @DS_START_DATE))
   QUALIFY ROW_NUMBER() OVER (PARTITION BY row_id ORDER BY measured_at) = 1

   Tick Enable date range parameters so @DS_START_DATE follows the report’s date range.

From Google Sheets:

1. Add data → Google Sheets, choose the export’s spreadsheet and a tab such as position_tracker.checks, and keep Use first row as headers on.
2. Timestamps in Sheets are ISO-8601 text. If Looker Studio reads measured_at as text, add a calculated field PARSE_DATETIME("%Y-%m-%dT%H:%M:%S", LEFT(measured_at, 19)) and use it as the date.

Related docs

• Cloud storage buckets
• BigQuery
• Google Sheets
• File formats
• Dataset reference