{"id":"96866571-4501-44a7-ad62-dd16a3b5632d","shortId":"ZKjJyd","kind":"skill","title":"querying-lakehouse","tagline":"Writes and executes DuckDB SQL against the Altertable lakehouse. Use when analyzing data, aggregating metrics, building reports, or querying tables in connections.","description":"# Querying the Lakehouse\n\nAltertable uses the DuckDB SQL dialect. Under the hood, queries run on hosted DuckDB workers over Parquet files stored in distributed object storage.\n\n## Quick Start\n\n```sql\n-- Discover table shape\nDESCRIBE catalog.schema.table\n\n-- Statistical profile (min, max, avg, nulls, unique counts)\nSUMMARIZE catalog.schema.table\n\n-- Basic query pattern\nSELECT column1, column2\nFROM catalog.schema.table\nWHERE condition\nLIMIT 100\n```\n\nAlways qualify table names with `catalog.schema.table` format.\n\n## When to Use This Skill\n\n- User asks to query or analyze data\n- Need to explore table contents\n- Building reports or aggregations\n- Validating data quality\n- Joining data across tables\n- User mentions SQL, query, or data analysis\n\n## Core Workflow\n\n### Step 1: Understand the Data\n\nBefore writing queries:\n\n- List available connections\n- Get schema details for relevant connections\n- Check if semantic models already define needed metrics\n- When column names are unknown, discover the table shape first:\n\n```sql\nDESCRIBE catalog.schema.table\n-- or\nSELECT * FROM catalog.schema.table LIMIT 1\n```\n\n### Step 2: Validate SQL Syntax\n\nAlways validate queries before execution:\n\n- Catches syntax errors early\n- Identifies missing tables or columns\n- Saves time on large queries\n\n### Step 3: Explain Complex Queries\n\nFor complex queries, use the `explain_sql` tool to get the execution plan. This tool analyzes a DuckDB SQL query and returns execution plan information including table scan estimates and file statistics. Use it to understand query performance characteristics before execution.\n\nThe tool returns:\n\n- Table scan details (table name, estimated rows, filters applied)\n- Total files and bytes in scanned tables\n- Estimated files and bytes that will be scanned\n- Optionally, the raw EXPLAIN plan (set `include_plan: true`)\n\n### Step 4: Execute and Analyze\n\nRun the query and interpret results:\n\n- Check row counts\n- Verify data types\n- Look for unexpected nulls or values\n\n### Step 5: Render as a Chart (Optional)\n\nWhen the user wants the result visualized rather than just tabular, call `preview_insight` with the SQL kind. In MCP clients that support MCP Apps, this surfaces a built-in chart UI for the result instead of a raw table.\n\n## DuckDB SQL Dialect Patterns\n\n### Date and Time Functions\n\n```sql\n-- Current date/time\nSELECT current_date, current_timestamp\n\n-- Date arithmetic\nSELECT date_add(current_date, INTERVAL 7 DAY)\nSELECT current_date - INTERVAL '30 days'\n\n-- Date truncation\nSELECT date_trunc('month', timestamp_column)\nSELECT date_trunc('week', timestamp_column)\n\n-- Date parts\nSELECT year(date_column), month(date_column), day(date_column)\nSELECT extract(dow FROM timestamp_column)  -- day of week\n```\n\n### Aggregations\n\n```sql\n-- Basic aggregations\nSELECT\n  COUNT(*) as total_rows,\n  COUNT(DISTINCT user_id) as unique_users,\n  SUM(amount) as total_amount,\n  AVG(amount) as avg_amount,\n  MIN(created_at) as first_event,\n  MAX(created_at) as last_event\nFROM events\n\n-- Grouped aggregations\nSELECT\n  date_trunc('day', timestamp) as day,\n  COUNT(*) as events\nFROM events\nGROUP BY 1\nORDER BY 1\n```\n\n### Window Functions\n\n```sql\n-- Row numbering\nSELECT\n  *,\n  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) as event_seq\nFROM events\n\n-- Running totals\nSELECT\n  date,\n  amount,\n  SUM(amount) OVER (ORDER BY date) as running_total\nFROM daily_sales\n\n-- Lag/Lead for comparisons\nSELECT\n  date,\n  revenue,\n  LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,\n  revenue - LAG(revenue, 1) OVER (ORDER BY date) as change\nFROM daily_revenue\n```\n\n### CTEs (Common Table Expressions)\n\n```sql\nWITH daily_users AS (\n  SELECT\n    date_trunc('day', timestamp) as day,\n    COUNT(DISTINCT user_id) as users\n  FROM events\n  GROUP BY 1\n),\nweekly_avg AS (\n  SELECT AVG(users) as avg_daily_users\n  FROM daily_users\n)\nSELECT\n  d.day,\n  d.users,\n  w.avg_daily_users\nFROM daily_users d\nCROSS JOIN weekly_avg w\n```\n\n### JSON Operations\n\n```sql\n-- Extract from JSON using arrow operators\nSELECT\n  properties->>'page_url' as page_url,\n  properties->>'referrer' as referrer,\n  (properties->>'amount')::FLOAT as amount\nFROM events\n\n-- Use json_extract_string_property when properties may exist as columns or in JSON\n-- Automatically uses column if it exists, falls back to JSON extraction\nSELECT\n  json_extract_string_property(events.properties_bucketed, '$.page_url') as page_url,\n  json_extract_string_property(events.properties_bucketed, '$.referrer') as referrer\nFROM events\n\n-- Check JSON field exists\nSELECT * FROM events\nWHERE properties ? 'campaign'\n\n-- JSON array operations\nSELECT\n  json_array_length(items) as item_count\nFROM orders\n```\n\n## Query Patterns\n\n### Time-Based Analysis\n\n```sql\n-- Last 7 days\nSELECT * FROM events\nWHERE timestamp >= current_date - INTERVAL '7 days'\n\n-- Specific date range\nSELECT * FROM events\nWHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'\n\n-- Compare periods\nWITH current_period AS (\n  SELECT COUNT(*) as events\n  FROM events\n  WHERE timestamp >= current_date - INTERVAL '7 days'\n),\nprevious_period AS (\n  SELECT COUNT(*) as events\n  FROM events\n  WHERE timestamp >= current_date - INTERVAL '14 days'\n    AND timestamp < current_date - INTERVAL '7 days'\n)\nSELECT\n  c.events as current_events,\n  p.events as previous_events,\n  (c.events - p.events)::FLOAT / NULLIF(p.events, 0) * 100 as pct_change\nFROM current_period c, previous_period p\n```\n\n### Funnel Analysis\n\n```sql\nWITH funnel AS (\n  SELECT\n    user_id,\n    MAX(CASE WHEN event ='page_view' THEN 1 ELSE 0 END) as step1,\n    MAX(CASE WHEN event ='add_to_cart' THEN 1 ELSE 0 END) as step2,\n    MAX(CASE WHEN event ='checkout' THEN 1 ELSE 0 END) as step3,\n    MAX(CASE WHEN event ='purchase' THEN 1 ELSE 0 END) as step4\n  FROM events\n  WHERE timestamp >= current_date - INTERVAL '30 days'\n  GROUP BY user_id\n)\nSELECT\n  SUM(step1) as page_views,\n  SUM(step2) as add_to_cart,\n  SUM(step3) as checkout,\n  SUM(step4) as purchase\nFROM funnel\n```\n\n### Cohort Analysis\n\n```sql\nWITH user_cohorts AS (\n  SELECT\n    user_id,\n    date_trunc('month', MIN(timestamp)) as cohort_month\n  FROM events\n  GROUP BY user_id\n),\nactivity AS (\n  SELECT\n    e.user_id,\n    c.cohort_month,\n    date_trunc('month', e.timestamp) as activity_month\n  FROM events e\n  JOIN user_cohorts c ON e.user_id = c.user_id\n)\nSELECT\n  cohort_month,\n  activity_month,\n  COUNT(DISTINCT user_id) as users\nFROM activity\nGROUP BY 1, 2\nORDER BY 1, 2\n```\n\n## Common Pitfalls\n\n- **Missing table qualification**: Always use `catalog.schema.table`\n- **PostgreSQL vs DuckDB syntax**: Use DuckDB syntax; some functions differ (e.g., `DATEADD` vs `date_add`)\n- **Large result sets**: Always use `LIMIT` when exploring\n- **Not validating first**: Validate SQL before executing large queries\n- **Implicit type coercion**: Be explicit with casts (e.g., `::FLOAT`)\n- **NULL handling**: Use `COALESCE` or `NULLIF` appropriately\n\n## Reference Files\n\n- [DuckDB functions](references/duckdb-functions.md)\n- [Altertable DuckDB functions](references/altertable-duckdb-functions.md)\n- [Friendly DuckDB SQL](references/friendly-duckdb-sql.md)\n- [Query patterns](references/query-patterns.md)\n- [Optimization tips](references/optimization.md)","tags":["querying","lakehouse","skills","altertable-ai","agent-skills","ai-agents","altertable"],"capabilities":["skill","source-altertable-ai","skill-querying-lakehouse","topic-agent-skills","topic-ai-agents","topic-altertable"],"categories":["skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/altertable-ai/skills/querying-lakehouse","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add altertable-ai/skills","source_repo":"https://github.com/altertable-ai/skills","install_from":"skills.sh"}},"qualityScore":"0.453","qualityRationale":"deterministic score 0.45 from registry signals: · indexed on github topic:agent-skills · 7 github stars · SKILL.md body (7,201 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:14:20.644Z","embedding":null,"createdAt":"2026-05-18T13:21:55.379Z","updatedAt":"2026-05-18T19:14:20.644Z","lastSeenAt":"2026-05-18T19:14:20.644Z","tsv":"'-01':725,726,729 '-31':730 '0':787,817,831,843,855 '1':127,169,470,473,520,532,568,815,829,841,853,959,963 '100':81,788 '14':764 '2':171,960,964 '2024':724,728 '3':195 '30':377,866 '4':277 '5':300 '7':371,703,713,748,771 'across':115 'activ':918,930,947,956 'add':367,825,881,987 'aggreg':17,109,414,417,455 'alreadi':147 'altert':11,29,1026 'alway':82,175,970,991 'amount':431,434,436,439,499,501,618,621 'analysi':123,700,800,895 'analyz':15,99,214,280 'app':330 'appli':251 'appropri':1020 'arithmet':364 'array':683,687 'arrow':604 'ask':95 'automat':638 'avail':135 'avg':64,435,438,570,573,576,595 'back':645 'base':699 'basic':70,416 'bucket':655,666 'build':19,106 'built':335 'built-in':334 'byte':255,262 'c':795,938 'c.cohort':923 'c.events':774,782 'c.user':942 'call':317 'campaign':681 'cart':827,883 'case':809,822,836,848 'cast':1011 'catalog.schema.table':59,69,77,87,163,167,972 'catch':180 'chang':538,791 'characterist':237 'chart':304,337 'check':143,287,672 'checkout':839,887 'client':326 'coalesc':1017 'coercion':1007 'cohort':894,899,910,937,945 'column':152,188,386,392,398,401,404,410,634,640 'column1':74 'column2':75 'common':543,965 'compar':731 'comparison':514 'complex':197,200 'condit':79 'connect':25,136,142 'content':105 'core':124 'count':67,289,419,423,463,558,692,738,754,949 'creat':441,447 'cross':592 'ctes':542 'current':356,359,361,368,374,710,734,745,761,768,776,793,863 'd':591 'd.day':583 'd.users':584 'daili':510,540,548,577,580,586,589 'data':16,100,111,114,122,130,291 'date':351,360,363,366,369,375,379,382,388,393,397,400,403,457,498,505,516,524,536,552,711,716,746,762,769,864,904,925,986 'date/time':357 'dateadd':984 'day':372,378,402,411,459,462,527,554,557,704,714,749,765,772,867 'defin':148 'describ':58,162 'detail':139,245 'dialect':34,349 'differ':982 'discov':55,156 'distinct':424,559,950 'distribut':49 'dow':407 'duckdb':7,32,42,216,347,975,978,1023,1027,1031 'e':934 'e.g':983,1012 'e.timestamp':928 'e.user':921,940 'earli':183 'els':816,830,842,854 'end':818,832,844,856 'error':182 'estim':227,248,259 'event':445,451,453,465,467,491,494,565,623,671,678,707,720,740,742,756,758,777,781,811,824,838,850,860,913,933 'events.properties':654,665 'execut':6,179,210,221,239,278,1002 'exist':632,643,675 'explain':196,204,270 'explicit':1009 'explor':103,995 'express':545 'extract':406,600,626,648,651,662 'fall':644 'field':674 'file':46,229,253,260,1022 'filter':250 'first':160,444,998 'float':619,784,1013 'format':88 'friend':1030 'function':354,475,981,1024,1028 'funnel':799,803,893 'get':137,208 'group':454,468,566,868,914,957 'handl':1015 'hood':37 'host':41 'id':426,486,561,807,871,903,917,922,941,943,952 'identifi':184 'implicit':1005 'includ':224,273 'inform':223 'insight':319 'instead':342 'interpret':285 'interv':370,376,712,747,763,770,865 'item':689,691 'join':113,593,935 'json':597,602,625,637,647,650,661,673,682,686 'kind':323 'lag':518,530 'lag/lead':512 'lakehous':3,12,28 'larg':192,988,1003 'last':450,702 'length':688 'limit':80,168,993 'list':134 'look':293 'max':63,446,808,821,835,847 'may':631 'mcp':325,329 'mention':118 'metric':18,150 'min':62,440,907 'miss':185,967 'model':146 'month':384,399,906,911,924,927,931,946,948 'name':85,153,247 'need':101,149 'null':65,296,1014 'nullif':785,1019 'number':478,481 'object':50 'oper':598,605,684 'optim':1037 'option':267,305 'order':471,487,503,522,534,694,961 'p':798 'p.events':778,783,786 'page':608,611,656,659,812,876 'parquet':45 'part':394 'partit':483 'pattern':72,350,696,1035 'pct':790 'perform':236 'period':732,735,751,794,797 'pitfal':966 'plan':211,222,271,274 'postgresql':973 'prev':526 'preview':318 'previous':750,780,796 'profil':61 'properti':607,613,617,628,630,653,664,680 'purchas':851,891 'qualif':969 'qualifi':83 'qualiti':112 'queri':2,22,26,38,71,97,120,133,177,193,198,201,218,235,283,695,1004,1034 'querying-lakehous':1 'quick':52 'rang':717 'rather':313 'raw':269,345 'refer':1021 'references/altertable-duckdb-functions.md':1029 'references/duckdb-functions.md':1025 'references/friendly-duckdb-sql.md':1033 'references/optimization.md':1039 'references/query-patterns.md':1036 'referr':614,616,667,669 'relev':141 'render':301 'report':20,107 'result':286,311,341,989 'return':220,242 'revenu':517,519,528,529,531,541 'row':249,288,422,477,480 'run':39,281,495,507 'sale':511 'save':189 'scan':226,244,257,266 'schema':138 'select':73,165,358,365,373,381,387,395,405,418,456,479,497,515,551,572,582,606,649,676,685,705,718,737,753,773,805,872,901,920,944 'semant':145 'seq':492 'set':272,990 'shape':57,159 'skill':93 'skill-querying-lakehouse' 'source-altertable-ai' 'specif':715 'sql':8,33,54,119,161,173,205,217,322,348,355,415,476,546,599,701,801,896,1000,1032 'start':53 'statist':60,230 'step':126,170,194,276,299 'step1':820,874 'step2':834,879 'step3':846,885 'step4':858,889 'storag':51 'store':47 'string':627,652,663 'sum':430,500,873,878,884,888 'summar':68 'support':328 'surfac':332 'syntax':174,181,976,979 'tabl':23,56,84,104,116,158,186,225,243,246,258,346,544,968 'tabular':316 'time':190,353,698 'time-bas':697 'timestamp':362,385,391,409,460,489,555,709,722,744,760,767,862,908 'tip':1038 'tool':206,213,241 'topic-agent-skills' 'topic-ai-agents' 'topic-altertable' 'total':252,421,433,496,508 'true':275 'trunc':383,389,458,553,905,926 'truncat':380 'type':292,1006 'ui':338 'understand':128,234 'unexpect':295 'uniqu':66,428 'unknown':155 'url':609,612,657,660 'use':13,30,91,202,231,603,624,639,971,977,992,1016 'user':94,117,308,425,429,485,549,560,563,574,578,581,587,590,806,870,898,902,916,936,951,954 'valid':110,172,176,997,999 'valu':298 'verifi':290 'view':813,877 'visual':312 'vs':974,985 'w':596 'w.avg':585 'want':309 'week':390,413,569,594 'window':474 'worker':43 'workflow':125 'write':4,132 'year':396","prices":[{"id":"4342deda-dbbc-4eee-b57e-2fdfb409eb48","listingId":"96866571-4501-44a7-ad62-dd16a3b5632d","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"altertable-ai","category":"skills","install_from":"skills.sh"},"createdAt":"2026-05-18T13:21:55.379Z"}],"sources":[{"listingId":"96866571-4501-44a7-ad62-dd16a3b5632d","source":"github","sourceId":"altertable-ai/skills/querying-lakehouse","sourceUrl":"https://github.com/altertable-ai/skills/tree/main/skills/querying-lakehouse","isPrimary":false,"firstSeenAt":"2026-05-18T13:21:55.379Z","lastSeenAt":"2026-05-18T19:14:20.644Z"}],"details":{"listingId":"96866571-4501-44a7-ad62-dd16a3b5632d","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"altertable-ai","slug":"querying-lakehouse","github":{"repo":"altertable-ai/skills","stars":7,"topics":["agent-skills","ai-agents","altertable"],"license":"mit","html_url":"https://github.com/altertable-ai/skills","pushed_at":"2026-05-14T10:34:10Z","description":"Agent Skills for Altertable","skill_md_sha":"68baad1253bad1cd72bfdb0d25b19ce213326dc2","skill_md_path":"skills/querying-lakehouse/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/altertable-ai/skills/tree/main/skills/querying-lakehouse"},"layout":"multi","source":"github","category":"skills","frontmatter":{"name":"querying-lakehouse","description":"Writes and executes DuckDB SQL against the Altertable lakehouse. Use when analyzing data, aggregating metrics, building reports, or querying tables in connections.","compatibility":"Requires Altertable MCP server"},"skills_sh_url":"https://skills.sh/altertable-ai/skills/querying-lakehouse"},"updatedAt":"2026-05-18T19:14:20.644Z"}}