ADR 025 — Snowflake-to-Google Sheets Sync Strategy: full_replace as Default¶

Status¶

Accepted

Context¶

The snowflake_to_gsheet pipeline type (introduced in xo-foundry) exports Snowflake views to Google Sheets for analyst consumption. Two sync strategies are available in the task implementation (gsheet_sync.py):

full_replace — runs SELECT * FROM {source}, clears the sheet tab, writes all rows in one batch via sheets.update_values().

incremental — runs SELECT * FROM {source} WHERE {date_column} = '{target_date}' for one calendar day, then reads the entire date column from the sheet to locate matching row indices, batch-deletes those rows, and appends the new rows.

The first production pipeline using this type exports rpt_wbp_agent_performance_channel_daily (~90k rows, 45 columns) to a Google Sheet daily. At that scale the full_replace run took ~2 minutes end-to-end in initial testing.

Google Sheets imposes a 10 million cell hard limit per spreadsheet. At 45 columns, the usable capacity is ~220k rows — sufficient for a multi-year rolling dataset if older data is rolled up to weekly grain.

We needed a documented position on which strategy to use by default and when each is appropriate.

Decision¶

Use full_replace as the default strategy for all Snowflake-to-Google Sheets pipelines.

Incremental mode (refresh_mode: incremental) is available for specific cases documented below, but should not be the default.

Row count management belongs in the dbt view, not in the sync strategy. When a sheet's row count would grow unboundedly (e.g., a daily-grain report accumulating over multiple years), the exp_ wrapper view feeding the pipeline should bake in a rollup — for example, weekly grain for data older than 90 days, daily grain for recent data. The DAG simply dumps whatever the view returns; it does not need to know about the rollup boundary.

Consequences¶

Easier with full_replace: - Two Google Sheets API calls per run (clear + batch write), regardless of dataset size - No sheet read step — the task never reads back data it already wrote - Binary failure mode: either the sheet has the latest data or it doesn't; no partially-updated state is possible - Retries are safe and cheap: clear always starts from a blank slate - Optimizing run time means optimizing the upstream dbt view (reducing row count via rollups), which benefits all consumers of that view, not just the sheet

Harder / given up: - Full replace is not suitable for intraday refresh on large datasets — each run rewrites the entire view. At hourly frequency, a 90k-row × 45-col view would run 24 full scans per day. - Incremental mode requires a code change to support sub-daily (datetime) granularity; the current implementation only supports date-level filtering.

Options Considered¶

Incremental as default¶

Rejected. The incremental strategy reads the entire date column from the sheet on every run to locate which row indices to delete. For a 90k-row sheet, this read scan is roughly comparable in cost to the full-replace clear call — and it grows linearly as the sheet fills. The Snowflake query savings (one filtered day vs. full scan) are real but irrelevant at daily frequency on a small warehouse. The added complexity (3+ API calls, more failure states, date targeting logic) is not justified for once-daily pipelines.

Incremental for intraday pipelines¶

Reserved for future use. If a pipeline requires sub-daily refresh (e.g., hourly metrics), full replace would be too expensive and incremental (or a datetime-windowed variant) would be the right choice. This would require extending gsheet_sync.py to support a datetime_column filter. No intraday Snowflake-to-Sheets pipeline exists today.

Managing row count via the DAG (e.g., a second purge DAG)¶

Rejected. A separate "archive old rows" DAG would add operational overhead (two DAGs to monitor, rollup logic split across DAG and dbt) and would require the Sheets API's batch delete to operate on large contiguous row ranges. Moving the rollup boundary into the exp_ view keeps all transformation logic in dbt, where it belongs.