Implement data virtualisation to query distributed data sources without moving data. Outputs virtual layer design, query federation strategy, performance optimisation, and governance approach.
Implement data virtualisation to query distributed data sources without moving data. Outputs virtual layer design, query federation strategy, performance optimisation, and governance approach.
argument-hint
["data sources","query latency requirements","data volume","existing data infrastructure"]
allowed-tools
Read, Write
Data Virtualization
Data virtualisation presents a unified logical view of data from multiple heterogeneous sources — without physically moving or replicating the data. Instead of ETL pipelines that copy data into a central warehouse, virtual layers query data in-place. This reduces latency for time-sensitive data and eliminates the complexity of replication pipelines.
When to Use Data Virtualization
USE VIRTUALIZATION when:
✓ Real-time or near-real-time data needed (can't wait for ETL)
✓ Data cannot be replicated (regulatory: GDPR data residency)
✓ Source system data used only occasionally (not worth ETL investment)
✓ Proof-of-concept before building full pipeline
✓ Federated query across multiple data warehouses
USE ETL/ELT instead when:
✓ Heavy transformations needed (virtualisation adds latency)
✓ Offline analytics (source systems unavailable 24/7)
✓ Historical analysis requires data retention beyond source
✓ Query performance is critical (virtualisation adds overhead)
✓ Complex ML feature computation
Query Federation with Trino
-- Trino (formerly PrestoSQL) — federated SQL across any data source
p.customer_id,
p.email,
s.total_orders,
s.lifetime_value,
h.page_view_count
postgresql.production.customers p
snowflake.analytics.customer_metrics s
p.customer_id s.customer_id
hive.raw_events.page_views h
p.customer_id h.user_id
p.created_at
s.total_orders ;
-- No data moved; queries run in-place at each source
-- Example: Join Postgres (production) with Snowflake (warehouse) and S3 (raw files)
-- dbt model that queries across sources without moving data-- Uses Trino as the execution engine-- models/customer_360.sql
{{
config(
materialized='view', -- Virtual; not materialised
schema='virtual_layer'
)
}}
WITH live_customers AS (
-- Real-time from PostgreSQL via Trino federationSELECT
customer_id,
email,
plan_type,
created_at
FROM {{ source('postgresql', 'customers') }}
WHERE created_at >=CURRENT_DATE-INTERVAL'90'DAY
),
analytics_enrichment AS (
-- Pre-aggregated from Snowflake warehouseSELECT
customer_id,
total_orders,
lifetime_value,
last_order_date
FROM {{ source('snowflake', 'customer_metrics') }}
)
SELECT
lc.customer_id,
lc.email,
lc.plan_type,
ae.total_orders,
ae.lifetime_value,
ae.last_order_date,
-- Computed at query time (not stored)CASEWHEN ae.last_order_date >CURRENT_DATE-INTERVAL'30'DAYTHEN'active'WHEN ae.last_order_date >CURRENT_DATE-INTERVAL'90'DAYTHEN'at_risk'ELSE'churned'ENDAS customer_status
FROM live_customers lc
LEFTJOIN analytics_enrichment ae USING (customer_id)
Performance Optimisation
-- Virtualisation adds latency — optimise strategically-- 1. Predicate pushdown: push filters to source systems-- Trino automatically pushes WHERE clauses to source connectors-- Verify with EXPLAIN:
EXPLAIN
SELECT*FROM postgresql.production.orders
WHERE created_at >DATE'2024-01-01'AND status ='paid';
-- Should show: ScanFilterProject[table=postgresql, filter=...]-- 2. Materialise hot virtual views-- For frequently-queried virtual tables, materialise in DWHCREATE TABLE snowflake.analytics.customer_360_snapshot ASSELECT*FROM trino_virtual_layer.customer_360
WHERE last_order_date >=CURRENT_DATE-INTERVAL'30'DAY;
-- Schedule refresh: every 4 hours via Airflow/Prefect-- 3. Columnar pruning: select only needed columns-- Never SELECT * from virtualised sourcesSELECT customer_id, email, plan_type -- Not SELECT *FROM postgresql.production.customers;
-- 4. Partition pruning: always include partition key in WHERESELECT*FROM hive.events.page_views
WHERE dt ='2024-03-15'-- dt is the S3 partition keyAND event_type ='purchase';
Anti-Patterns to Avoid
Anti-Pattern
Problem
Fix
Virtualising everything
High latency; source systems overloaded
Virtualise for real-time; ETL for historical/heavy analytics
No query limits
Expensive federated queries cause source outages
Query timeout; row limits; connection pooling
SELECT * on virtual tables
Fetches all columns from source
Always specify required columns
Joining large virtual tables
Massive cross-source data movement
Pre-aggregate at source; use virtual layer for final join
No governance on virtual layer
PII in virtual views accessible to all
Apply column-level security at virtual layer
10 Rules
Data virtualisation is for real-time access and federation — not for replacing ETL at scale.
Always push predicates (WHERE clauses) to source systems — verify with EXPLAIN.
Never SELECT * from a virtual table — specify only required columns.
Materialise frequently-queried virtual views for performance — refreshed on a schedule.
Set query timeouts and row limits — runaway federated queries can take down source systems.
Governance applies at the virtual layer — column-level security filters PII before users query.
Source systems must be designed for additional read load — virtualisation adds queries.
Trino/Presto is the most mature open-source federation engine — preferred over building custom.
Latency budgets: virtual queries take 10-100x longer than warehouse queries — plan accordingly.
Test query plans with EXPLAIN before production deployment — unexpected full scans are common.
Deep Reference Playbook
The sections below extend this skill into a complete operating playbook so it can run end-to-end inside Claude Code, CoWork, or any agentic tool without further prompting. Pull only the sections you need for a given engagement.
Inputs the skill must collect
Before producing any output, the skill confirms:
Objective — the single decision or artifact the user wants out of this session.
Context — system, team, customer, product, or domain the work sits inside.
Constraints — time, budget, headcount, regulatory, technical, political.
Definition of done — what "good" looks like and who signs it off.
Audience — who reads or consumes the output (engineer, exec, customer, regulator).
Existing artifacts — prior versions, related docs, dashboards, tickets.
Risk appetite — how reversible the decision is and how much ambiguity is acceptable.
If any of these are missing, the skill asks targeted clarifying questions before generating output. It never invents constraints the user did not state.
Operating workflow
The canonical workflow for Data Virtualization runs in five stages. Each stage has an explicit exit criterion so the skill knows when to advance.
Stage 1 — Frame. Restate the problem in one paragraph. Name the decision, the deadline, the stakeholders, and the success metric. Surface assumptions explicitly so they can be challenged.
Stage 2 — Diagnose. Inventory the current state with concrete evidence: metrics, quotes, screenshots, configs, tickets. Separate facts from interpretations. Identify the two or three root causes that explain most of the gap, not the long tail of symptoms.
Stage 3 — Design. Generate at least two viable options. For each option, capture: what changes, who owns it, what it costs, what it unblocks, what it risks, and how it could fail. Recommend one with a written rationale.
Stage 4 — Execute. Convert the chosen option into a sequenced plan: milestones, owners, dependencies, gating checks, communication cadence, and rollback triggers. Anything that cannot be assigned an owner and a date is not yet a plan.
Stage 5 — Validate. Define how success will be measured, when the measurement happens, and what action follows each possible result. Schedule the retrospective before the work starts, not after.
Outputs the skill produces
Depending on the request, the skill returns one or more of:
A one-page brief suitable for an executive reader.
A detailed working document for the delivery team.
A decision record capturing the choice, the alternatives, and the rationale.
A risk register with probability, impact, owner, and mitigation.
A sequenced action plan with named owners and explicit due dates.
A measurement plan tied to the success metric.
A communication plan for stakeholders inside and outside the team.
Every artifact uses clear headings, short paragraphs, and tables where comparison helps. No filler. No restating the prompt. No hedging language when a recommendation is warranted.
Decision logic and trade-offs
The skill applies the following heuristics when choices are not obvious:
Prefer reversible decisions taken quickly over irreversible decisions taken slowly.
Optimise for the constraint that bites first — usually time, attention, or trust, not money.
Default to the simplest design that meets the stated definition of done; add complexity only when a specific requirement forces it.
Make the cost of being wrong visible so the reader can judge whether the recommendation is proportionate.
Name the people, not the roles, when assigning ownership; ambiguous ownership produces ambiguous outcomes.
Anti-patterns the skill refuses to emit
Anti-pattern
Why it fails
What the skill does instead
Generic best-practice list with no context
Reader cannot act on it
Tailors recommendations to the stated constraints
Recommendation without trade-offs
Hides the cost of being wrong
Names the price paid for the recommendation
Plan with no owners or dates
Cannot be executed or tracked
Assigns a named owner and a date to every action
Metrics theatre
Measures activity, not outcome
Ties every metric back to the user or business outcome
Boil-the-ocean scope
Nothing ships
Cuts scope to the smallest valuable slice
Buried recommendation
Reader misses the point
Leads with the recommendation in the first paragraph
Quality bar
The skill self-checks each output against these gates before returning it:
Can a busy executive understand the recommendation from the first 150 words?
Is every claim either evidenced, labelled as an assumption, or removed?
Does every action have an owner and a date?
Are the trade-offs of the recommendation stated honestly?
Is there a measurable success criterion?
Would the author be comfortable defending this artifact in a review meeting?
If any gate fails, the skill rewrites the section before returning it.
Worked micro-example
Context: Implement data virtualisation to query distributed data sources without moving data. Outputs virtual layer design, query federation strategy, performance optimi
Frame: the team needs a defensible recommendation within five working days; the audience is a cross-functional steering group; the cost of delay is higher than the cost of being slightly wrong.
Diagnose: the dominant constraint is decision latency, not analytical depth. Existing data is sufficient for a directional call.
Design: two viable options surfaced. Option A optimises for speed and reversibility. Option B optimises for completeness but slips the deadline by two weeks.
Execute: Option A recommended. Plan sequenced into a two-week sprint with named owners, a mid-point checkpoint, and a clear rollback trigger.
Validate: success measured against a single leading indicator at day 30 and a single lagging indicator at day 90. Retrospective scheduled for day 35.
Cadence and follow-through
A one-shot artifact rarely changes outcomes. The skill recommends a lightweight cadence to keep the work alive:
Weekly: owner posts a five-line status (done, doing, blocked, risk, ask).
Fortnightly: steering group reviews leading indicators and unblocks dependencies.
Monthly: retrospective on what the data is teaching the team; adjust plan accordingly.
Quarterly: revisit the original objective and decide whether to continue, pivot, or stop.
Closing rules of thumb
Lead with the recommendation; supporting analysis follows.
Treat every output as a draft that will be challenged; pre-empt the obvious objections.
Prefer one strong recommendation over three weak options.
When the evidence is thin, say so; do not launder uncertainty as confidence.
Optimise for the next decision, not for the perfect document.
Make it easy for the reader to disagree with you in a structured way.
Ship the artifact; iterate against feedback rather than in private.
