Reference hub · Tool-belt series · Snowflake

Query it. Trace it through the engine.
Then find the row SAP has and Snowflake doesn't.

You already write working queries. This roadmap takes the next steps: Snowflake-native SQL that makes you fast, the engine and observability layer that make you credible, and — the flagship — a repeatable playbook for the question your stakeholders keep asking: "why is Snowflake missing data that's in SAP?" Cortex Code ("CoCo") runs through the whole thing as your always-on resource.

How to use this page

Same rules as the DAX Studio × Tabular Editor roadmap. Work each part top to bottom — Parts A and B run in parallel (write better SQL while learning what the engine does with it). Part C is the flagship: it turns "Snowflake is missing SAP data" from a fire drill into a 30-minute triage you can run on demand. Part D makes Cortex Code your default first stop for all of it, then closes with a capstone investigation.

Check items off as you go — sections, parts, and the top bar all track progress. Progress saves automatically in this browser on this device; it resets if you clear browser data or open the file elsewhere.

When a request comes in that this page doesn't cover, bring it back to Claude and ask for a new section — it's built to be extended like the rest of your tool belt.

Part A · Query like an engineer

From working queries to Snowflake-native SQL

Snowflake's SQL dialect has power features most people never touch — QUALIFY, VARIANT, Time Travel, streams. Learning them isn't trivia: every single one shows up again in Part C as a reconciliation weapon. This part upgrades your hands.

0 / 0

Half of all "missing data" tickets are actually context problems: wrong role, wrong database, a filtered secure view. Before debugging anyone else's data, get ruthless about knowing your own execution context at all times.

Learn this

Master the four-part context: role → warehouse → database → schema, and the USE statements plus SELECT CURRENT_ROLE(), CURRENT_WAREHOUSE(), CURRENT_DATABASE() to verify it
Understand why two people can query "the same table" and get different results: role-based access, secure views, row access policies, dynamic data masking
Get fast in Snowsight: worksheets vs Workspaces, keyboard shortcuts, running a selection, the object explorer, and chart previews on results
Use SHOW TABLES, SHOW VIEWS, DESCRIBE TABLE, and SELECT GET_DDL('view','name') to inspect objects — reading a view's DDL is often where a "missing" filter hides
Find Query History in Snowsight (Monitoring → Query History) and re-open any query you or a pipeline ran, with its profile

Go deeper

Field noteWhen someone reports missing data, your first question is now automatic: "what role were you using, and is that a table or a view?" You'll be surprised how many tickets close right there.

These are the constructs that separate "I can write queries" from "I can answer any question in one pass." QUALIFY alone will change how you write dedup and latest-record logic — which is exactly the logic CDC pipelines from SAP depend on.

Learn this

Get fluent with window functions: ROW_NUMBER, RANK, LAG/LEAD, running totals with SUM() OVER, and partition/order framing
Master QUALIFY — filter on a window function without a subquery. The latest-version-per-key pattern below is the backbone of every CDC-fed table
Use Snowflake's quality-of-life syntax: GROUP BY ALL, SELECT * EXCLUDE (...), SELECT * RENAME (...), ILIKE column selection
Learn set-based comparison tools: MINUS/EXCEPT, INTERSECT, and anti-joins (LEFT JOIN ... WHERE IS NULL) — Part C runs on these
Use CTEs to structure multi-step logic, and know when a CTE is re-evaluated vs when to materialize a temp table instead
Explore PIVOT/UNPIVOT and MATCH_RECOGNIZE so you know they exist when a request fits them

-- The CDC backbone: latest record per business key
SELECT *
FROM raw.sap_bkpf
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY bukrs, belnr, gjahr      -- company, doc no, fiscal year
    ORDER BY _loaded_at DESC
) = 1;

Go deeper

Field noteYour DAX brain transfers here: QUALIFY is roughly "CALCULATE a ranking then FILTER on it" collapsed into one clause. Anywhere you'd reach for a Power Query dedup step upstream, a QUALIFY view in Snowflake is cleaner and faster.

Many ingestion tools land source payloads as JSON in a VARIANT column before anything is flattened into typed columns. If your SAP pipeline does this, the raw truth of what arrived lives in VARIANT — and you need to be able to read it.

Learn this

Query VARIANT with dot/bracket notation (payload:header.BUKRS::string) and understand the ::type cast
Use LATERAL FLATTEN to explode nested arrays into rows
Inspect unknown payloads: TYPEOF(), OBJECT_KEYS(), and selecting a few raw rows to see the real shape
Understand the tradeoffs: when VARIANT is fine vs when columns should be typed and flattened for pruning and performance

Go deeper

Field noteIn a missing-data investigation, the VARIANT landing table is your customs checkpoint: if the row is there in raw JSON but gone downstream, the pipeline dropped it in transformation. If it never landed at all, the problem is upstream in extraction. That one check splits the search space in half.

Time Travel lets you query a table as it existed minutes, hours, or days ago. For "the data was there yesterday and now it's gone" tickets, this is not a nice-to-have — it's the difference between guessing and knowing exactly when rows disappeared.

Learn this

Query historical states with AT (TIMESTAMP => ...), AT (OFFSET => -3600), and BEFORE (STATEMENT => 'query-id')
Diff a table against its own past — the query below pinpoints exactly which rows vanished and when
Know your retention: default 1 day, up to 90 on Enterprise via DATA_RETENTION_TIME_IN_DAYS, and what Fail-safe is (and isn't)
Recover mistakes: UNDROP TABLE, and CREATE TABLE ... CLONE t AT (...) to resurrect a past state next to the current one
Use zero-copy clones as free sandboxes: clone a schema, test a fix or a destructive backfill against it, drop it — no storage cost until data diverges

-- "It was there yesterday": what disappeared in the last 24h?
SELECT belnr, gjahr, bukrs
FROM curated.fact_gl_docs AT(OFFSET => -86400)   -- 24h ago
MINUS
SELECT belnr, gjahr, bukrs
FROM curated.fact_gl_docs;                      -- now

Go deeper

Field noteThis maps to a habit you already have: it's the .vpax baseline snapshot idea, built into the database. Before any risky backfill or pipeline change, note the timestamp — that's your free rollback point and your before/after receipt.

You don't own the SAP ingestion pipeline, but you'll debug its output. That means reading its parts on sight: stages where files land, COPY INTO that loads them, streams that track changes, tasks and dynamic tables that transform on a schedule. Each one is a place rows can silently stop.

Learn this

Understand stages (internal & external) and list what's sitting in one: LIST @my_stage, plus querying staged files directly with SELECT ... FROM @stage
Read a COPY INTO statement and its sharp edges: ON_ERROR behavior (skipped rows!), file formats, and VALIDATION_MODE for dry runs
Know what Snowpipe is (continuous auto-ingest) and how its error behavior differs from manual COPY
Understand streams (CDC change tracking on a table) and the consume-on-DML semantics that make a stream "empty out"
Understand tasks (scheduled SQL, task graphs) and dynamic tables (declarative, auto-refreshing transforms) — and how each reports failures
Sketch your own environment's SAP pipeline as boxes and arrows: which of these mechanisms carries each hop? (You'll formalize this in C10)

Go deeper

Field noteON_ERROR = 'CONTINUE' is the single most common silent-row-loss setting in the wild: the load "succeeds" while quietly skipping bad rows. When you meet a COPY statement, check that option first.

Part B · Under the hood

The engine and the flight recorder

Micro-partitions, warehouses, and caching explain why queries are fast or slow. The metadata views — QUERY_HISTORY, COPY_HISTORY, TASK_HISTORY — record everything that ever happened in the account. Together they're your equivalent of DAX Studio's Server Timings: the engine's honest testimony.

0 / 0

Snowflake separates storage (immutable micro-partitions in cloud storage) from compute (virtual warehouses you spin up and down). Once this model clicks, warehouse sizing, pruning, caching, and cost all become predictable instead of mysterious.

Learn this

Understand micro-partitions: ~16MB immutable chunks with min/max metadata per column — the metadata is what makes pruning (skipping partitions) possible
Understand virtual warehouses: sizes (XS→6XL), per-second billing, auto-suspend/auto-resume, and why "bigger" helps some queries and not others
Know the three caches: result cache (24h, exact-match, free), warehouse local disk cache, and metadata cache (why COUNT(*) is instant)
Understand natural clustering and when a clustering key is (rarely) worth defining on very large tables
Grasp the honest benchmark habit: result cache makes repeat runs lie — ALTER SESSION SET USE_CACHED_RESULT = FALSE is your "Clear Cache & Run"

Go deeper

Field noteDirect translation from your VertiPaq work: micro-partition pruning is Snowflake's version of segment elimination, and high-cardinality scattered data hurts both engines the same way. Your instinct for "cardinality is the enemy" carries over intact.

Every query gets a visual execution profile. Learn to read four numbers — partitions scanned vs total, bytes spilled, join output rows, queue time — and you can diagnose 90% of slow queries in under a minute.

Learn this

Open the Query Profile on a real query and identify the most expensive operator and the data flow between nodes
Check pruning: partitions scanned vs partitions total — scanning everything means your filter isn't pruning (function on the filter column, wrong type, bad clustering)
Spot spilling (bytes spilled to local/remote storage) — the signal that the warehouse is too small for the operation, or the query needs restructuring
Spot exploding joins: join output rows » input rows means duplicate keys — in SAP data, usually a join on a non-unique key like material without plant
Diagnose queueing vs execution time, and know the fix for each (warehouse contention vs query design)

Go deeper

Field noteSame discipline as your FE/SE split: diagnose first, rewrite second. Scanning all partitions → fix the filter or the data layout. Spilling → fix the warehouse or the operation. Exploding join → fix the key. One glance at the profile decides which.

This is the section that makes Part C possible. Snowflake records every query, every load, every task run, and every DML operation into queryable views. "Did the load run? Did it fail? Who deleted those rows?" are all one SELECT away — if you know which view to ask.

Learn this

Know the two homes: INFORMATION_SCHEMA table functions (real-time, per-database, short retention) vs SNOWFLAKE.ACCOUNT_USAGE views (account-wide, 365 days, up to ~45 min latency)
Query QUERY_HISTORY: find every query that touched a table, filter by user/warehouse/status, and pull error messages from failed statements
Query COPY_HISTORY / LOAD_HISTORY: per-file load results including ROW_COUNT, ROW_PARSED, ERROR_COUNT, and first-error details — the #1 view for missing-data triage
Query TASK_HISTORY and dynamic table refresh history: did the scheduled transform run, skip, or fail — and with what error?
Use ACCESS_HISTORY (Enterprise) to see which objects a query read and wrote — lineage for "what populates this table?"
Save a personal _diagnostics.sql scratchpad of these queries, parameterized by table name — your equivalent of the DAX Studio scratchpad file

-- Did the SAP load actually run today, and did it drop rows?
SELECT file_name, last_load_time, row_count, row_parsed,
       error_count, first_error_message, status
FROM TABLE(information_schema.copy_history(
    table_name  => 'RAW.SAP_BKPF',
    start_time  => DATEADD('hour', -24, CURRENT_TIMESTAMP())))
ORDER BY last_load_time DESC;

Go deeper

Field noteThis is your DMV moment — the same trick as querying $SYSTEM.TMSCHEMA_MEASURES to make a model document itself. The account documents itself; most people just never ask. Being the person who asks is the whole job.

You won't administer the account, but understanding cost and governance makes you a better citizen and a sharper debugger — and "this recon query costs pennies to run daily" is a sentence stakeholders like hearing.

Learn this

Understand the billing model: credits per warehouse-second + storage — and check WAREHOUSE_METERING_HISTORY to see what your own work costs
Right-size by habit: XS for exploration, scale up only when the profile shows spilling; auto-suspend at 60s
Know what resource monitors and query timeouts are, so pipeline failures caused by them don't mystify you
Map the RBAC mental model: role hierarchies, ownership vs usage grants, future grants — enough to read why access broke, and to request the right grant the first time

Go deeper

Field noteThe stakeholder-simplicity principle from your Fabric work applies here too: treasury doesn't care about credits or roles — they care that answers are fast, correct, and cheap. Cost literacy is how you keep all three true without being asked.

Part C · The flagship · SAP S/4 → Snowflake reconciliation

"Why is Snowflake missing data from SAP?" — answered in 30 minutes, every time

The gap between an SAP base table and its Snowflake copy has a finite set of causes, and a fixed order to check them in. This part gives you the pipeline map, the failure catalog, the triage playbook, and the automation that makes the answer a dashboard instead of a fire drill.

0 / 0

SAP data reaches Snowflake through one of a few extraction mechanisms, then usually hops through raw → staging → curated layers inside Snowflake. Every hop is a place rows can stop. Your first mission is a one-page diagram of your company's actual pipeline — the same "map before opinions" move as your Power BI discovery phase.

Learn this

Learn the extraction mechanisms and their failure modes: SLT (trigger-based CDC, near-real-time, can lag or drop triggers), ODP/extractors & CDS views (application-layer, respects SAP logic, delta-queue based), log-based CDC tools (Fivetran, Qlik, SNP Glue, BryteFlow — each with its own delta semantics)
Find out which mechanism your pipeline uses, who owns it, and where its monitoring lives (SAP-side delta queues have their own health you can't see from Snowflake)
Understand SAP's table quirks: pool/cluster tables that need application-layer extraction, and why raw table dumps can bypass SAP business logic entirely
Learn the key SAP finance tables you'll reconcile most: BKPF/BSEG (FI documents), ACDOCA (S/4 universal journal), BSID/BSAD, LFA1/KNA1 masters — and their business keys
Map your Snowflake layers: which schema is landing (raw/untransformed), which is staged, which is curated — and what transformation runs between each (views? tasks? dbt? dynamic tables?)
Draw the one-pager: SAP table → extraction mechanism → raw table → transforms → curated table → report. Save it in the repo — it's the map every future ticket starts from

Go deeper

Field noteThe pipeline map is a stakeholder artifact, not just a debugging tool. "Here is every hop your data takes, and here's the monitoring on each" is the treasury-meeting slide that buys you trust before you've fixed anything.

Almost every "Snowflake is missing SAP data" ticket resolves to one of these ten causes. Internalize the catalog and you stop searching randomly — you check the likely suspects in order of probability and cost-to-check.

Cause 01 · Latency

The data isn't missing — it's late

User compares live SAP against a Snowflake copy refreshed hours ago, or a Power BI import refreshed yesterday. Always establish "as of when?" first. Cheapest check, most common answer.

Cause 02 · Failed / partial load

The load ran and broke — or half-ran

COPY errors, task failures, Snowpipe backlog, warehouse timeout mid-load. COPY_HISTORY and TASK_HISTORY (B8) answer this in one query. Watch for ON_ERROR='CONTINUE' quietly skipping rows.

Cause 03 · Delta / CDC gap

The incremental load missed changes

Trigger dropped during SAP maintenance, delta queue reset, CDC high-water mark skipped a window, initialization ran while transactions were posting. Symptom: a specific date range is thin; rows before and after are fine.

Cause 04 · Deletions & reversals

SAP changed history; the copy didn't

Or the inverse of missing: hard deletes and document reversals in SAP that the pipeline can't see (many extractors don't capture deletes), leaving Snowflake with phantom rows — or dropping reversals so totals disagree.

Cause 05 · Extraction filters

The pipeline never asked for that data

SLT/connector filters on company code, year, doc type, or client (MANDT!) that exclude the rows in question. Check the extraction config, not the data. Frequently the answer for "new company code has no data."

Cause 06 · Transformation drop

It landed in RAW, died on the way to CURATED

Inner joins that should be left joins, WHERE clauses, dedup logic keeping the wrong record, failed lookups nulling keys. The raw-vs-curated anti-join (C12) catches this precisely.

Cause 07 · Semantic mismatch

Both sides are right — about different questions

Posting date vs entry date, document currency vs local currency, SAP fiscal periods vs calendar months, timezone offsets, special periods (13–16). The counts differ because the definitions differ.

Cause 08 · Key mangling

The row is there under a different identity

Leading zeros stripped from ALPHA-converted fields (BELNR!), trims, case changes, composite keys missing MANDT or BUKRS. The row "missing" from a join is present — its key just doesn't match anymore.

Cause 09 · Archived / logic-hidden

SAP itself doesn't show it the same way

Archived documents gone from base tables but expected in reports; pool/cluster data extracted without application logic; CDS views applying authorization or status filters the raw table doesn't.

Cause 10 · Access, not absence

The data is there — the user can't see it

Row access policies, secure views, masking, or role grants filtering what this user's query returns. Compare counts as an elevated role before declaring anything missing.

Learn this

Memorize the catalog well enough to name the top 3 suspects from a ticket description alone
For each cause, know which tool confirms or eliminates it (COPY_HISTORY? extraction config? anti-join? GET_DDL on the view?)
Learn the SAP-specific gotchas by heart: MANDT in every key, ALPHA conversion / leading zeros, fiscal year variants, document reversals, DEC-type dates
Start a _recon_casebook.md in the repo: every resolved ticket gets an entry — symptom, cause number, fix. Six months of this is an org-specific diagnostic manual no one else has

Field noteCause 07 deserves special respect in treasury work: month-end totals that "don't match SAP" are, more often than not, posting-date-vs-entry-date or document-vs-local-currency questions. Ask "which date field, which currency?" before touching a single pipeline.

Random searching never terminates; a drill-down always does. Start at total counts, narrow to the period where they diverge, anti-join to the exact keys, then compare columns on those keys. Each step shrinks the haystack by orders of magnitude. Get an SAP-side extract (from the basis/pipeline team, or a CDS view export) as your comparison baseline.

Learn this

Step 1 — Frame it. Pin down: which SAP table/view, which Snowflake table, which filter, as of what timestamp, and what "missing" means to the reporter (rows? totals? one document?)
Step 2 — Count by period. Compare row counts grouped by posting period (and company code). The period where counts diverge is your crime scene — check COPY_HISTORY/TASK_HISTORY for that window immediately
Step 3 — Anti-join to keys. Within the divergent slice, anti-join SAP extract vs Snowflake on the full business key (including MANDT/BUKRS) to list the exact missing documents
Step 4 — Follow one row through every hop. Take one missing key and check each layer from the pipeline map: staged file? raw? staging? curated? The hop where it disappears names the cause from C11
Add checksums when counts match but totals don't: SUM(amount), HASH_AGG(*), or per-column MIN/MAX/SUM profiles to localize which column diverges
Close every investigation with a written verdict: cause number, evidence query, fix or escalation, casebook entry. The verdict doc is your breadcrumb trail — same discipline as your CLG change IDs

-- Step 2: where do the counts diverge? (SAP extract landed as a table/stage)
SELECT COALESCE(s.period, f.period) AS period,
       s.cnt AS sap_rows, f.cnt AS snowflake_rows,
       s.cnt - f.cnt AS gap
FROM (SELECT monat AS period, COUNT(*) cnt FROM recon.sap_bkpf_extract
      WHERE gjahr = 2026 GROUP BY 1) s
FULL OUTER JOIN
     (SELECT fiscal_period, COUNT(*) cnt FROM curated.fact_gl_docs
      WHERE fiscal_year = 2026 GROUP BY 1) f
  ON s.period = f.period
ORDER BY 1;

-- Step 3: exactly which documents are missing?
SELECT s.bukrs, s.belnr, s.gjahr
FROM recon.sap_bkpf_extract s
LEFT JOIN curated.fact_gl_docs f
  ON  f.company_code = s.bukrs
  AND f.doc_number   = LTRIM(s.belnr, '0')   -- cause 08 hiding in plain sight?
  AND f.fiscal_year  = s.gjahr
WHERE f.doc_number IS NULL
  AND s.monat = 4;                            -- the divergent period from step 2

Go deeper

Field noteStep 4 — following one single row through every hop — resolves more tickets than everything else combined. It converts an abstract "data is missing" into a concrete "row 4700012345 exists in RAW but not STAGING, and the staging view inner-joins to a cost-center lookup that doesn't have its KOSTL." That sentence is a fix, not a mystery.

Once the playbook works manually, productize it — the same instinct as your enforcement gate: deterministic checks, run automatically, humans only see exceptions. This is also a flagship agentic-AI deliverable: CoCo can draft every object in this section.

Learn this

Build a RECON schema with persistent views: counts-by-period per critical table (SAP-side extract vs Snowflake curated), refreshed on schedule
Add row-count and checksum snapshot tables written by a task after every load — trend lines of "gap over time" catch slow CDC drift that point checks miss
Explore Data Metric Functions (DMFs) — Snowflake's built-in data quality framework: freshness, null counts, duplicate counts, plus custom metrics, on a schedule
Wire alerts: CREATE ALERT on a gap threshold, or a task that fails loudly — so you tell the treasury team about gaps before they tell you
Surface it where stakeholders live: a small Power BI recon page (you already own that stack) showing per-table freshness, row-count gap, and last-load status

Go deeper

Field noteThe recon dashboard is your .vpax move again: receipts. "Every critical SAP table, gap tracked daily, zero unexplained variances this month" is the before-and-after story that turns a support burden into a demonstrated capability.

Part D · Cortex Code & capstone

CoCo as your always-on Snowflake resource

Cortex Code ("CoCo") is Snowflake's native agentic assistant — it replaced the old Copilot panel and, unlike a generic LLM, it's grounded in your account: schemas, roles, warehouses, query history. Since it runs inside Snowflake's governance boundary, it fits your no-external-MCP constraint. This part makes it your default first stop, then puts everything together in a capstone.

0 / 0

CoCo's edge over any external AI is context: it can see your object names, metadata, and account state, and it respects RBAC — it only works with what your role can see. Treat it like you treat Copilot agent mode in VS Code: precise charters, explicit context, you review everything before it runs.

Learn this

Get access sorted: the COPILOT_USER/CORTEX_USER roles, where the CoCo panel lives in Snowsight/Workspaces, and whether cross-region inference is enabled in your account (ask your admin — at ConocoPhillips this may be governed)
Prompt with full context: fully qualified names (db.schema.table), the business goal, and constraints — "using RAW.SAP_BKPF and CURATED.FACT_GL_DOCS, write a count-by-period reconciliation for fiscal 2026" beats "compare these tables"
Use the explain direction: paste an inherited 200-line query or view DDL and ask CoCo to explain it, find the filters, and identify why rows might be excluded — instant leverage on Cause 06
Use it as living documentation: "how do I query COPY_HISTORY for the last 24 hours?" — CoCo answers from Snowflake docs, saving the search round-trip
Have it draft your Part C automation: recon views, snapshot tasks, DMF definitions, alert conditions — you review the SQL like an agent diff, then run
Know the limits: it can't see the data values themselves (provide literal filter values), it can suggest invalid SQL — validate before trusting, exactly like your DAX-agent confidence tiers
Build your prompt library: a _coco_prompts.md in the repo with your proven recon, triage, and explain prompts — reusable charters, same as your seven-agent suite

-- A charter-style CoCo prompt for a missing-data ticket:
-- "Treasury reports document 4700012345 (company 1000, FY2026) exists in
--  SAP BKPF but not in CURATED.FACT_GL_DOCS. Write three queries:
--  1) search RAW.SAP_BKPF for that key including leading-zero variants,
--  2) check copy_history for RAW.SAP_BKPF over the last 7 days,
--  3) show the DDL of every view between RAW and CURATED for this table.
--  Do not modify anything."

Go deeper

Field noteCoCo is Snowflake's answer to the same org goal you're proving in Fabric: agentic AI as the primary method, inside governance. Being fluent in both makes you the person who can demo the pattern on either platform — that's a career asset, not just a tool skill.

Query it. Trace it through the engine.
Then find the row SAP has and Snowflake doesn't.

How to use this page

From working queries to Snowflake-native SQL

Snowsight fluency & execution context

Advanced SQL patterns

Semi-structured data & VARIANT

Time Travel & zero-copy cloning

Pipeline literacy — stages, COPY, streams, tasks

The engine and the flight recorder

Architecture — micro-partitions, warehouses, caching

Query Profile & performance tuning

The observability layer — the flight recorder

Cost awareness & governance basics

"Why is Snowflake missing data from SAP?" — answered in 30 minutes, every time

Map the pipeline first

The missing-data failure catalog

The data isn't missing — it's late

The load ran and broke — or half-ran

The incremental load missed changes

SAP changed history; the copy didn't

The pipeline never asked for that data

It landed in RAW, died on the way to CURATED

Both sides are right — about different questions

The row is there under a different identity

SAP itself doesn't show it the same way

The data is there — the user can't see it

The reconciliation playbook

Automate the reconciliation

CoCo as your always-on Snowflake resource

Working with CoCo — patterns that pay

Capstone — the full missing-data investigation