In late 2021 we built a data pipeline for a mining operation in Australia that needed to push near-real-time production data into external Snowflake accounts via Secure Data Sharing. The ingestion layer used Fivetran for CDC from operational databases and Snowpipe for streaming telemetry. Transformation ran through dbt incremental models. The shared output landed in three external Snowflake accounts and one Reader Account, each seeing a governed subset of the same underlying tables.
On paper, the architecture was clean. In practice, every layer had a gotcha that cost us days.
Source systems
Four source systems fed the pipeline:
- SAP PM (Oracle) — maintenance work orders, equipment master data, scheduled service records
- Wenco Fleet Management (MSSQL) — haul truck GPS positions, payload weights, cycle times, updated every 10 seconds per unit
- Processing plant historian (REST API) — pour weights, mill throughput, recovery percentages, reagent consumption, polled every 2 minutes
- Safety incident tracker (CSV on SharePoint) — weekly manual upload from HR
Each had different authentication, different schemas, and different ideas about what a timestamp meant.
Architecture
┌─────────────────────────────────────────────────────────────────────┐
│ SOURCE SYSTEMS │
├──────────┬───────────┬──────────────────┬──────────────────────────┤
│ SAP PM │ Wenco │ Plant Historian │ Safety (SharePoint) │
│ (Oracle) │ (MSSQL) │ (REST API) │ (CSV) │
└────┬─────┴─────┬─────┴────────┬─────────┴────────────┬─────────────┘
│ │ │ │
▼ ▼ │ │
┌─────────────────────┐ │ ┌──────────┴──────────┐
│ FIVETRAN │ │ │ Snowflake Stage │
│ ───────────────── │ │ │ (CSV auto-ingest │
│ SAP: Oracle CDC │ │ │ via Snowpipe) │
│ Wenco: MSSQL CDC │ │ └──────────┬──────────┘
│ 5-min sync cycle │ │ │
└──────────┬──────────┘ │ │
│ ▼ │
│ ┌──────────────────┐ │
│ │ Azure Function │ │
│ │ REST → Blob │ │
│ │ every 2 min │ │
│ └────────┬─────────┘ │
│ │ │
│ ▼ │
│ ┌──────────────────┐ │
│ │ Snowpipe │ │
│ │ (auto-ingest │ │
│ │ from Blob) │ │
│ └────────┬─────────┘ │
│ │ │
▼ ▼ ▼
┌──────────────────────────────────────────────────────────────────┐
│ SNOWFLAKE — RAW DATABASE │
│ ┌───────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ raw_sap │ │ raw_wenco │ │ raw_plant │ │ raw_safety │ │
│ └───────────┘ └────────────┘ └────────────┘ └────────────┘ │
└───────────────────────────┬──────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────┐
│ dbt — TRANSFORMATION │
│ │
│ STAGING INTERMEDIATE MARTS │
│ ┌─────────────┐ ┌───────────────┐ ┌───────────────────┐ │
│ │ stg_sap_* │───▶│ int_equip_* │───▶│ fct_production │ │
│ │ stg_wenco_* │───▶│ int_haul_* │───▶│ fct_fleet_kpi │ │
│ │ stg_plant_* │───▶│ int_pour_* │───▶│ fct_safety │ │
│ │ stg_safety │───▶│ int_recovery │───▶│ dim_equipment │ │
│ └─────────────┘ └───────────────┘ │ dim_location │ │
│ │ rpt_compliance │ │
│ └───────────────────┘ │
└───────────────────────────┬──────────────────────────────────────┘
│
▼
┌──────────────────────────────────────────────────────────────────┐
│ SNOWFLAKE SECURE DATA SHARES │
│ │
│ SHARE_PRODUCTION SHARE_COMPLIANCE │
│ ┌─────────────────────┐ ┌──────────────────────────┐ │
│ │ v_production │ │ v_compliance_quarterly │ │
│ │ v_fleet_kpi │ │ (aggregated incidents, │ │
│ │ dim_equipment │ │ emissions, volumes) │ │
│ │ dim_location │ └──────────────────────────┘ │
│ │ │ │
│ │ Row access policy: │ SHARE_FLEET │
│ │ CURRENT_ACCOUNT() │ ┌──────────────────────────┐ │
│ │ → allowed_pit_ids │ │ v_fleet_kpi_filtered │ │
│ └─────────────────────┘ │ (shift-level, scoped to │ │
│ │ contracted units only) │ │
│ └──────────────────────────┘ │
└───────────┬────────────────────────┬──────────────────┬──────────┘
│ │ │
▼ ▼ ▼
External Snowflake Snowflake Reader External Snowflake
Account A Account Account B
(full account, (managed account, (full account,
imports share) provider-funded) imports share)Record-level flow: plant event to external account
Plant sensor reading (pour weight, mill throughput)
│
▼
REST API polled by Azure Function (every 2 min)
│
▼
JSON file written to Azure Blob stage
│
▼
Snowpipe auto-ingests → raw_plant.pour_events
│
▼
dbt incremental: stg_plant_pour
(parse JSON, cast types, deduplicate on event_id)
│
▼
dbt incremental: int_pour_daily
(aggregate to shift/daily grain, join dim_equipment)
│
▼
dbt mart: fct_production
(final metric calculations, business logic)
│
▼
Secure view in SHARE_PRODUCTION
(row access policy filters by CURRENT_ACCOUNT() → pit scope)
│
▼
External Snowflake account queries imported share
(governed subset, ~5–10 min end-to-end latency)Fivetran: what worked and what didn’t
Fivetran handled SAP PM well. Oracle CDC via LogMiner, minimal config, reliable five-minute syncs. Wenco worked too — MSSQL change tracking, same pattern.
What Fivetran couldn’t do was ingest from the plant historian’s REST API. No native connector existed, and the Fivetran Functions SDK in 2022 couldn’t handle the historian’s non-standard OAuth refresh that required maintaining session state between requests.
We wrote an Azure Function instead: poll every two minutes, flatten nested JSON to newline-delimited format, drop into Azure Blob Storage. Snowpipe watched the container and auto-ingested. It worked, but it was a second ingestion path — a second set of credentials to rotate, a second failure mode at 3am.
The bigger Fivetran surprise was cost. Fivetran bills on Monthly Active Rows (MAR). Wenco logged GPS positions every ten seconds for forty-odd vehicles — roughly 14 million rows per day. The MAR pricing on that volume exceeded the Snowflake compute cost of actually processing it.
We moved Wenco ingestion to the same pattern as the historian: custom extractor writing to Blob, Snowpipe ingesting. Fivetran stayed for SAP PM only, where row volumes were manageable and the Oracle CDC connector saved us from maintaining LogMiner code.
Lesson: Fivetran is excellent for SaaS sources and moderate-volume databases. For anything generating millions of rows per day — telemetry, sensor data, fleet GPS — write a custom extractor and use Snowpipe. The maintenance overhead is lower than the Fivetran bill.
Snowpipe: the near-real-time compromise
The pipeline needed production data in external accounts within 15 minutes of a plant event. Snowpipe’s SLA is “typically within one minute” of a file arriving in the stage. In practice, the stages compounded:
| Stage | Latency |
|---|---|
| Azure Function polls historian API | 0–2 min |
| Snowpipe detects and ingests file | 0–1 min |
| dbt incremental model processes new rows | 0–10 min (depends on schedule) |
| End-to-end | ~5–10 min typical |
The first two stages were reliable. The third was the problem.
dbt incremental models and late-arriving data
dbt ran on a Snowflake Task, scheduled every ten minutes. Each run processed new rows in raw tables using incremental materialisation with merge strategy and unique_key for deduplication.
The staging model:
{{ config(
materialized='incremental',
unique_key='event_id',
incremental_strategy='merge'
) }}
select
raw.event_id,
raw.pour_timestamp,
raw.weight_tonnes,
raw.recovery_pct,
raw.mill_throughput_tph,
raw.shift_id,
raw._loaded_at
from {{ source('raw_plant', 'pour_events') }} as raw
{% if is_incremental() %}
where raw._loaded_at > (select max(_loaded_at) from {{ this }})
{% endif %}The problem: _loaded_at is when Snowpipe wrote the row, not when the event happened. If the Azure Function missed a poll (transient API timeout, cold start, deployment), the next poll fetched the backlog. Those events had pour_timestamp values from minutes ago but _loaded_at of right now. The staging model picked them up correctly.
But the downstream int_pour_daily model was incremental too, keyed on shift_date. If a shift had already been processed and late-arriving events fell into that shift, the aggregate wouldn’t update because the shift row already existed. The merge on unique_key='shift_date' overwrites the row, but uses the values from the current incremental query — which only sees the new events, not the previously processed ones for that shift.
The fix was a lookback window on every intermediate and mart model:
{% if is_incremental() %}
where shift_date >= dateadd(day, -2, current_date())
{% endif %}Every run reprocessed the last two days of aggregates. Wasteful — burning compute to re-derive values that hadn’t changed — but it guaranteed late-arriving data was correctly incorporated.
The alternative was delete+insert, explicitly deleting and rebuilding any shift with new data. But dbt’s delete+insert on Snowflake in 1.3 had a gotcha: it deleted and re-inserted the entire table if the incremental predicate returned no rows. On a table being queried in real time through a Secure Share, that wasn’t acceptable — a consumer running a query during the delete window would see an empty result set.
Snowflake Secure Shares: the governance layer
Row-level security across accounts
The production share needed row-level filtering — different external accounts should see data for different pit scopes. Snowflake’s row access policies use CURRENT_ROLE(), but across a share, CURRENT_ROLE() returns the consumer’s role, not the provider’s. You can’t write a policy that references roles you don’t control.
CURRENT_ACCOUNT() works. We built a mapping table and baked the filtering into secure views:
create table governance.share_access_map (
consumer_account varchar,
share_name varchar,
allowed_pit_ids array
);
create or replace secure view marts.v_production as
select
p.shift_date,
p.pit_id,
p.weight_tonnes,
p.recovery_pct,
p.mill_throughput_tph
from marts.fct_production p
inner join governance.share_access_map m
on array_contains(p.pit_id::variant, m.allowed_pit_ids)
where m.consumer_account = current_account();The constraint we hit: both tables referenced in the secure view must be in the same database. The share is created at the database level, and views referencing objects in other databases fail at share creation. We had to move the governance mapping table into the marts database — architecturally wrong, but a hard Snowflake limitation.
Reader Account limitations
One external party didn’t have Snowflake. We created a Reader Account — a managed Snowflake account where we control the compute.
What the documentation doesn’t emphasise:
-
No policies in reader accounts. All governance must live in the provider account. The consumer can’t add their own row-level filtering or masking.
-
No data loading. Reader accounts can’t upload data. If the consumer needs to join shared data with their own reference tables, they need a full account.
-
Provider pays for compute. The reader account’s warehouse runs on your credits. An unfiltered
SELECT *on six months of production data cost us $180 before we added resource monitors. Set these before creating the share, not after. -
Maximum 20 reader accounts. Fine for a handful of external parties. Doesn’t scale to dozens.
Share latency isn’t zero
Snowflake’s documentation says shares provide “instant access to data without copying.” True from a storage perspective — no replication, no copy. But end-to-end latency is the sum of every pipeline stage: Snowpipe ingestion, dbt transformation schedule, consumer dashboard refresh. The share itself adds zero latency. Everything around it adds minutes.
We should have presented the expected end-to-end latency diagram up front instead of letting consumers discover it by comparing timestamps.
What I would change
Drop Fivetran for high-volume sources earlier. MAR pricing punishes telemetry. For anything over a few hundred thousand rows per day, Snowpipe with a custom extractor is cheaper to run and not much harder to maintain.
Use delete+insert from the start. The lookback window hack works but wastes compute on every run. Hoping a future dbt release fixes the “delete everything when no rows match” behaviour, but until then, delete+insert is cleaner.
Set resource monitors on reader accounts before sharing. Shared data feels free to the consumer and they query accordingly.
Push for full Snowflake accounts over reader accounts. If the consumer needs to do anything beyond SELECT, reader accounts are a dead end. Getting a consumer to provision a full account after they’ve been using a reader for six months is a harder conversation than doing it up front.
Draw the latency timeline before the first stakeholder meeting. Source event → ingestion → transformation → share → consumer query. Every stage adds minutes. Presenting this as a diagram prevents the “why isn’t this real-time” conversation later.
Outcome
The pipeline has been running for about a year now. So far:
- Zero data-sharing security incidents across all external accounts
- Fivetran ingested SAP PM changes reliably with no manual intervention
- The custom Azure Function for the plant historian failed twice (both token expiry edge cases)
- dbt ran 144 times per day, averaging 3 minutes per run on an XS warehouse
- Total Snowflake cost split: ~60% compute, ~25% storage, ~15% reader account compute
Two ingestion paths, a lookback-window hack on the incremental models, a governance mapping table in the wrong database. Not elegant. But it shipped, the data was accurate to within 0.1% of source, and the governance held up to an external audit. Sometimes that’s what production data pipelines in mining look like.