fxview-parquet-consumer

FXView Tick Parquet Consumer

Read FXView tick data from Parquet files produced by the MT5 tick collection system.

Self-Evolving Skill: This skill improves through use. If instructions are wrong, parameters drifted, or a workaround was needed — fix this file immediately, don't defer. Only update for real, reproducible issues.

When to Use This Skill

Use this skill when:

• Reading or querying FXView tick Parquet files
• Building adapters or pipelines for FXView tick data
• Writing DuckDB queries against tick data
• Integrating tick data into Python or Rust pipelines
• Checking tick data schema or file layout

Authoritative Source

https://github.com/terrylica/mql5/blob/05f7c82/docs/tick_research/SCHEMA_VERIFIED.md

This skill summarizes the verified schema. For full verification details, flags distributions, and row group internals, see the source document.

Schema

All 6 columns are non-nullable. Schema is identical between EURUSD and XAUUSD files.

Column	Arrow Type	DuckDB Type	Nullable	Notes
time_msc	Int64	BIGINT	NO	Unix epoch milliseconds
bid	Float64	DOUBLE	NO	Bid price
ask	Float64	DOUBLE	NO	Ask price
last	Float64	DOUBLE	NO	Always 0.0 for FXView forex -- IGNORE
volume	Int64	BIGINT	NO	Always 0 for FXView forex -- IGNORE
flags	UInt8	UTINYINT	NO	MqlTick flags bitmask (6 values over history; mask bits, never match literals)

Parquet Footer Metadata

Key-value pairs embedded in every file footer:

Key	Example Value	Purpose
symbol	EURUSD	Bare symbol name (no FXVIEW_ prefix)
digits	5 (EURUSD) / 2 (XAUUSD)	Price decimal digits
broker	FXView	Broker name
created_at	2026-03-24T10:32:35.101090+00:00	File creation timestamp (RFC3339 UTC)

File Layout

Directory pattern: {base_path}/FXVIEW_{SYMBOL}/{YYYY}/{SYMBOL}_{YYYYMMDD}.parquet

Example: ~/.cache/opendeviationbar/ticks/FXVIEW_EURUSD/2026/EURUSD_20260323.parquet

IMPORTANT asymmetry: The directory uses the FXVIEW_ prefix (broker-qualified), but the filename and metadata use the bare symbol name. Do not mix these up.

Crash recovery files: {SYMBOL}_{YYYYMMDD}_1.parquet, _2.parquet, etc. Created when the EA restarts and finds an existing file for today. All segments are valid, complete Parquet files with footers.

XAUUSD Differences

Property	EURUSD	XAUUSD
digits	5	2
Price scale	~1.15	~4400
Ticks/day	~416K	~929K
last/volume	Always 0	Always 0
Flag distribution	53% bid+ask, 24% bid, 23% ask	98% bid+ask, ~1% each bid/ask

XAUUSD has ~2.2x higher tick density than EURUSD.

DuckDB Consumption Examples

Read a single day

SELECT * FROM read_parquet('~/.cache/opendeviationbar/ticks/FXVIEW_EURUSD/2026/EURUSD_20260323.parquet');

Read all files for a symbol (glob)

SELECT * FROM read_parquet('~/.cache/opendeviationbar/ticks/FXVIEW_EURUSD/**/*.parquet');

Convert timestamp to human-readable

SELECT make_timestamp(time_msc * 1000) AS ts, bid, ask, ask - bid AS spread
FROM read_parquet('~/.cache/opendeviationbar/ticks/FXVIEW_EURUSD/2026/EURUSD_20260323.parquet');

Spread statistics

SELECT avg(ask - bid) AS avg_spread, max(ask - bid) AS max_spread
FROM read_parquet('~/.cache/opendeviationbar/ticks/FXVIEW_EURUSD/2026/EURUSD_20260323.parquet');

Flags distribution

SELECT flags, count(*) AS cnt, round(count(*) * 100.0 / sum(count(*)) OVER (), 2) AS pct
FROM read_parquet('...')
GROUP BY flags
ORDER BY cnt DESC;

Anti-Patterns

• Do NOT use last or volume columns for FXView forex (always zero)
• Do NOT assume FXVIEW_ prefix in filenames -- it is ONLY in the directory name
• Do NOT use f64 for prices in Rust production code (use i64 fixed-point via digits metadata)
• Do NOT expect time_msc in seconds -- it is MILLIseconds
• Do NOT hardcode machine paths -- use pattern {base_path}/FXVIEW_{SYMBOL}/{YYYY}/{SYMBOL}_{YYYYMMDD}.parquet

Parquet Internals

• Compression: ZSTD level 3
• time_msc encoding: DELTA_BINARY_PACKED (optimal for monotonic timestamps, dictionary disabled)
• flags encoding: Dictionary encoding (low cardinality; six distinct values over full history, three on a typical day)
• bid, ask, last, volume: Default (PLAIN + ZSTD)
• Row groups: ~65,536 rows each (last group has remainder)
• File size: ~1.35 MB for ~416K EURUSD ticks/day

Flags Bitmask Reference

Mask the documented bit; never match a literal value. Use flags & 2 <> 0 for a bid update and flags & 4 <> 0 for an ask update. That is correct under both encodings below and across both historical transitions. WHERE flags = 130 or flags IN (130, 134) returns zero bid ticks for the whole 2026-04-23 to 2026-08-13 window.

Census of the whole lake (2018-04 to 2026-08, all symbols, measured 2026-08-29; issue #155). Over full history the column takes six values:

Event	Bit-7 form (the norm)	Bare form
bid update	130 (=128|2)	2
ask update	132 (=128|4), until 2018-06-15 only	4
bid+ask update	134 (=128|6)	6

• Through 2018-06-15 every day's alphabet is exactly [130, 132, 134]; from 2018-06-17 it is [4, 130, 134], and 132 never recurs.
• Bare 2 and 6 first appear on 2021-07-01, recur in short episodes, and run continuously from 2026-04-23 to 2026-08-13. A day whose alphabet is [2, 4, 6] (for example 2021-07-02) is not an outage or a corrupt file: every tick is present, under the bare encoding.
• Bit 7 (128) is undocumented. MQL5's MqlTick reference defines exactly six tick flags (TICK_FLAG_BID, TICK_FLAG_ASK, TICK_FLAG_LAST, TICK_FLAG_VOLUME, TICK_FLAG_BUY, TICK_FLAG_SELL), and none of them is 128. This broker sets bit 7 on most ticks, but what it means is not established; do not call it a "first tick marker" (there is no TICK_FLAG_FIRST).

Troubleshooting

Issue	Cause	Solution
No files found	Wrong base path or symbol	Check directory pattern with FXVIEW_ prefix
All last/volume are zero	Normal for FXView forex	Ignore these columns for forex pairs
Timestamp looks wrong	Using seconds not ms	time_msc is milliseconds -- multiply by 1000 for microseconds
Schema mismatch	Different broker or version	Verify against SCHEMA_VERIFIED.md permalink
Multiple files same day	Crash recovery segments	All _N suffixed files are valid, union them

Post-Execution Reflection

After this skill completes, check before closing:

1. Did the command succeed? — If not, fix the instruction or error table that caused the failure.
2. Did parameters or output change? — If the underlying tool's interface drifted, update Usage examples and Parameters table to match.
3. Was a workaround needed? — If you had to improvise (different flags, extra steps), update this SKILL.md so the next invocation doesn't need the same workaround.

Only update if the issue is real and reproducible — not speculative.