Every Fivetran account starts with rows: Postgres tables, Salesforce objects, ad-platform spend. Then someone asks the question that does not fit that shape — "can the support bot answer from our PDF contracts?", "which policy documents mention this clause?", "why is our AI assistant confidently wrong about the current pricing sheet?" — and you discover that the useful text lives in a SharePoint folder, an S3 bucket of scanned invoices, and a Zendesk ticket history nobody has ever modelled.
This tutorial covers the pipeline for that: landing unstructured and semi-structured files in the warehouse with Fivetran, then making them retrievable with warehouse-native search and embeddings, so a RAG application or an internal agent queries governed data instead of a copy someone made on a laptop.
Fivetran's own reference for the source side is the file connector documentation and, for the AI side, your destination's own vendor docs — Snowflake Cortex, Databricks Vector Search or BigQuery ML. Feature availability moves quickly here, so confirm the specifics for your plan and region before you commit an architecture.
The architecture in one paragraph
Fivetran syncs files from an object store or document system into your destination. Depending on connector and configuration you get either the file contents parsed into rows, or a metadata table plus the raw file staged where the warehouse can read it. From there the warehouse does the work: chunk the text, embed the chunks with a built-in model, store the vectors in a table, and query them with a similarity function. Nothing leaves the warehouse boundary, which is the entire point of doing it this way rather than pointing a scraper at production.
SharePoint / S3 / GDrive / Box
│ Fivetran file connector (incremental, metadata + content)
▼
raw.docs_files ──► chunking (SQL/UDF) ──► doc_chunks
│ embedding function
▼
doc_chunk_vectors ──► retrieval API
1. Choose the right source connector
Fivetran groups these differently than people expect, and picking wrong costs you a re-sync:
- Object stores — S3, Azure Blob Storage, Google Cloud Storage. Best when a system already drops files somewhere on a schedule. You control the prefix layout, so you control the sync.
- Document systems — SharePoint, OneDrive, Google Drive, Box, Dropbox. Best when humans are the source of truth and you cannot make them change where they save things. Expect messier folder structures and permission edge cases.
- Ticket and content systems with text bodies — Zendesk, Intercom, Confluence, Notion. These are ordinary connectors; the "unstructured" part is just a long text column you will chunk later.
For a first build, prefer an object store with a prefix you own. Ask the document-system owner to sync a curated folder into S3 rather than pointing Fivetran at a 400,000-file departmental drive on day one.
2. Structure the source prefix before you sync anything
Ten minutes here saves a quarter of grief:
s3://acme-docs/
contracts/ v=2026/ tenant=acme/ ...
policies/
product-sheets/
_archive/ ← excluded from the sync
Rules that hold up:
- One connection per document domain, not one per bucket. Contracts and marketing PDFs have different refresh needs, different owners and different sensitivity. Separate connections mean separate schedules, separate pause switches and — under connection-level MAR — separate, legible cost lines.
- Never sync an
_archiveortmpprefix. File connectors are enthusiastic. Excluded by pattern is cheaper than deleted later. - Immutable file names. If a process rewrites
pricing.pdfin place every night, every sync treats it as changed. Versioned names (pricing-2026-03-01.pdf) keep the sync incremental and give you a real history.
3. Configure the connection
In the connector setup you will make three decisions that matter more than the rest:
File pattern. Restrict by extension and path, e.g. contracts/.*\.pdf$. A permissive pattern is how a bucket of 2 GB log archives ends up in your document pipeline.
Content handling. For text-extractable formats, Fivetran can parse the document and land its text; for others you get metadata (path, size, modified time, hash) and read the object from the warehouse via an external stage or volume. Check which mode your connector and destination support — the two paths lead to quite different SQL downstream, and this is the single most common reason a first build gets rebuilt.
Schema change handling. Set it to block new tables/columns on document connections. You want a pull request when a new prefix appears, not a surprise sync. See our schema drift playbook for how to run that policy without breaking sync history.
If you manage Fivetran in code, this whole connection belongs in your repository — see the Terraform provider tutorial — because file patterns are exactly the kind of setting that gets loosened during an incident and never tightened again.
4. Land it, then keep raw raw
Whatever Fivetran writes, do not transform in place. Keep a raw layer and build on top:
-- raw.docs_contracts as delivered by Fivetran (illustrative columns)
select
_file as file_path,
_modified as source_modified_at,
_line as line_no,
content as raw_text,
_fivetran_synced as synced_at
from raw.docs_contracts
limit 20;
Column names differ by connector and version, so inspect the delivered schema rather than trusting a snippet. The habit that matters: raw stays byte-faithful so you can re-chunk and re-embed later without re-syncing. You will re-chunk. Everyone re-chunks.
5. Chunk deterministically
Embedding a whole 60-page contract gives you one vector that means nothing. Chunk it, and make the chunking reproducible so a re-run does not invalidate every vector.
create or replace table analytics.doc_chunks as
with docs as (
select
file_path,
source_modified_at,
md5(file_path || '|' || to_varchar(source_modified_at)) as doc_version_id,
listagg(raw_text, '\n') within group (order by line_no) as full_text
from raw.docs_contracts
group by 1, 2
)
select
doc_version_id,
file_path,
source_modified_at,
c.index as chunk_no,
c.value::string as chunk_text,
md5(doc_version_id || ':' || c.index) as chunk_id
from docs,
lateral flatten(
input => split_text_recursive_character(full_text, 'markdown', 1200, 150)
) c;
The exact splitting function is destination-specific (Snowflake has recursive text splitting in Cortex; on Databricks or BigQuery you would use a Python UDF or a notebook step). What is not destination-specific:
- Chunk size 800–1500 characters with ~10% overlap is a sane default for prose and contracts. Tables and code want smaller chunks.
- A
doc_version_idderived from path plus modified time means a re-uploaded document produces new chunks instead of silently corrupting old ones. - A deterministic
chunk_idmakes the embedding step idempotent, which is what lets you run it incrementally.
6. Embed incrementally, not nightly-from-scratch
Embedding is a per-token cost. Re-embedding unchanged text is pure waste, and at document scale it is the line item that gets your project questioned.
create table if not exists analytics.doc_chunk_vectors (
chunk_id string,
doc_version_id string,
file_path string,
chunk_text string,
embedding vector(float, 1024),
embedded_at timestamp_ltz
);
merge into analytics.doc_chunk_vectors t
using (
select c.*
from analytics.doc_chunks c
left join analytics.doc_chunk_vectors v using (chunk_id)
where v.chunk_id is null
) s
on t.chunk_id = s.chunk_id
when not matched then insert
(chunk_id, doc_version_id, file_path, chunk_text, embedding, embedded_at)
values
(s.chunk_id, s.doc_version_id, s.file_path, s.chunk_text,
ai_embed('snowflake-arctic-embed-l-v2.0', s.chunk_text),
current_timestamp());
Then clean up vectors whose document version no longer exists:
delete from analytics.doc_chunk_vectors v
where not exists (
select 1 from analytics.doc_chunks c where c.chunk_id = v.chunk_id
);
Pin the embedding model name in one place. Changing models means re-embedding everything — mixed-model vector tables return nonsense similarity scores — so treat a model change as a versioned migration into a new table, not an edit.
If you run dbt inside Fivetran, these two steps are just models with an incremental materialisation; our dbt transformations service page covers that setup, and the Fivetran + dbt merger post covers where that tooling is heading.
7. Retrieval
create or replace function analytics.search_contracts(q string, k int)
returns table (file_path string, chunk_text string, score float)
as
$$
select
file_path,
chunk_text,
vector_cosine_similarity(
embedding,
ai_embed('snowflake-arctic-embed-l-v2.0', q)
) as score
from analytics.doc_chunk_vectors
order by score desc
limit k
$$;
select * from table(analytics.search_contracts('termination notice period', 8));
Two upgrades worth doing before you call it done:
- Filter before you rank. Add
where file_path like 'contracts/%'or a tenant predicate. Pre-filtering beats post-filtering for both accuracy and cost. - Hybrid retrieval. Pure vector search misses exact identifiers — invoice numbers, clause references, SKUs. Combine keyword matching with similarity and take the union. Managed search services (Cortex Search, Databricks Vector Search) do this for you and are usually worth it over hand-rolled cosine similarity once you pass a few hundred thousand chunks.
8. Governance is the reason you did this in the warehouse
Do not lose the advantage at the last step:
- Strip PII before it becomes a vector. An embedding of a paragraph containing a national ID is still that data, and it is now in a table nobody classified. Block or hash at the source where you can — see column blocking and hashing in Fivetran — and mask in the chunking model where you cannot.
- Keep
file_pathon every chunk. Answers without citations do not survive their first review meeting, and lineage from answer to source file is what makes an internal AI tool trustworthy. - Respect source permissions. Fivetran syncs the files; it does not carry a SharePoint ACL into your warehouse. If a folder is restricted, the derived tables need equivalent row-access policies, applied deliberately. This is the failure mode auditors find first.
- Set retention. If a contract is deleted at source, decide whether its chunks and vectors disappear too, and implement it. The delete step in section 6 is that lever.
9. Costs and the honest caveats
- MAR. File connectors can count rows per parsed line or record, so a document sync can produce far more active rows than the file count suggests. Estimate on a single prefix first and read the usage before you widen the pattern. Our connection-level MAR guide explains the counting rules; cost optimization is where we do this with clients.
- Embedding and warehouse compute are separate bills from Fivetran's. Budget them separately or the finance conversation goes badly.
- OCR is not free or perfect. Scanned documents need an extraction step, and its error rate becomes your retrieval quality ceiling. Sample a hundred documents and read the extracted text before building anything on top of it.
- Feature velocity. Fivetran's unstructured/file capabilities and every warehouse's AI functions are changing release to release. Treat the SQL above as shape, not gospel, and verify function names and model identifiers against current docs.
A realistic first sprint
- Pick one document domain with a real question attached to it. Not "all company documents".
- Sync one prefix, ~1,000 files, and read the delivered schema.
- Chunk and embed, then have a subject-matter expert run twenty real queries and mark the results good or bad.
- Fix chunking and filtering based on that, not on intuition.
- Only then widen the file pattern and wire the retrieval function into an application.
Teams that skip step 3 ship a semantic search box that returns plausible garbage, and the project quietly dies. Teams that do it usually find the fix is chunk boundaries or a missing metadata filter — both cheap, both invisible without evaluation.
If you want this built on your existing account — file connectors configured, chunking and embedding models under version control, governance policies that survive an audit — our Fivetran connectors configuration and data pipeline development teams do exactly this. Get in touch with the document source and the question you are trying to answer, and we will tell you honestly whether the pipeline is the hard part.