Creates a new SQLite table from the provided data AND immediately inserts all rows. Infers column names and types from the first item in the data list. WARNING: Overwrites the table if it already exists.

Use this as a single step to store fetched API data — no need to call insert_data_into_table afterwards. Use insert_data_into_table only to append more rows to an already-existing table.

Args: table_input: Object containing table_name and data (list of dicts).

Returns: Dict[str, Any]: A summary with table name, columns created, and rows inserted.

IMPORTANT: The database is persistent and does NOT clean itself automatically. This tool overwrites the table if it exists, giving you a clean slate each call.

Permanently deletes (drops) a table from the SQLite database.

Use this to free up space or remove tables that are no longer needed. This action is irreversible — all data in the table will be lost.

Args: table_name_input: Object containing the table_name to drop.

Returns: Dict[str, Any]: A dictionary indicating success or an error message.

PREFERRED tool for multi-series analysis. Fetches data for multiple StatCan vector IDs in a single API call and immediately stores the results in a SQLite table — no separate create/insert steps needed.

*** USE THIS TOOL whenever you need data for multiple provinces, age groups, industries, or any other breakdown. It replaces the slow pattern of calling get_data_from_cube_pid_coord_and_latest_n_periods once per series. ***

Typical workflow:

1. search_cubes_by_title("unemployment rate") → find productId

2. get_cube_metadata(productId=...) → find vectorIds for each series you want

3. fetch_vectors_to_database( vectorIds=["v111","v222","v333"], table_name="unemployment_by_province", startRefPeriod="2023-01-01", endRefPeriod="2024-12-31" ) ← single call fetches + stores everything

4. query_database("SELECT * FROM unemployment_by_province") → analyze

Args: input_data.vectorIds: List of vector IDs to fetch (strings, e.g. ["111","222"]). input_data.table_name: SQLite table to create and populate. input_data.startRefPeriod: Optional start date (YYYY-MM-DD). input_data.endRefPeriod: Optional end date (YYYY-MM-DD).

Returns: Dict with table name, columns, rows_inserted, and a 5-row sample so you can verify the data looks right before querying.

IMPORTANT: In your final response cite the vectorIds and reference period used.

Provides a complete inventory of data tables available via the API, including dimension-level details. Disables SSL Verification. Corresponds to: GET /getAllCubesList

Results are paginated. Default returns first 100 cubes. Use offset/limit to page through. Prefer search_cubes_by_title if you know what you're looking for.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For cubes, this means including the ProductId (pid) and the Title.

Provides a complete inventory of data tables available via the API, excluding dimension or footnote information (lighter version). Disables SSL Verification. Corresponds to: GET /getAllCubesListLite

Results are paginated. Default returns first 100 cubes. Use offset/limit to page through. Prefer search_cubes_by_title if you know what you're looking for.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For cubes, this means including the ProductId (pid) and the Title.

Fetches bulk data for multiple vectors filtered by release date range (YYYY-MM-DDTHH:MM), NOT by reference period. Use this when you want data released within a specific date/time window (e.g., "all updates released yesterday").

*** IMPORTANT: release date vs reference period ***

• Use THIS tool when you want: "data released between date A and date B"

• Use get_sdmx_vector_data (startPeriod/endPeriod) when you want: "data for the time period YYYY to YYYY" get_sdmx_vector_data is more reliable and filters by reference period, not release date.

*** LARGE RESPONSE WARNING *** This tool can return hundreds of flattened data points. If the response exceeds context limits, narrow the request: use fewer vectorIds, or use offset/limit pagination to page through results in smaller chunks.

Response is pre-flattened: each element is one data point with vectorId, productId, coordinate, and all value fields injected at the top level.

Disables SSL Verification. Corresponds to: POST /getBulkVectorDataByRange

Returns: List[Dict[str, Any]]: Flat list of data point dicts, each tagged with vectorId, productId, and coordinate. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected or no vectors return SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For vector data, this means including the VectorId and Release Time.

Get the list of data tables/cubes that were updated on a specific date (YYYY-MM-DD). Disables SSL Verification. Corresponds to: GET /getChangedCubeList/{date}

Returns: List[Dict[str, Any]]: A list of dictionaries describing changed cube objects.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For changed cubes, this means including the ProductId (pid) and Title.

Retrieves changed series data (data points that have changed) using Cube ProductId and Coordinate string. Coordinates are automatically padded to 10 dimensions. Disables SSL Verification. Corresponds to: POST /getChangedSeriesDataFromCubePidCoord

Returns: Dict[str, Any]: A dictionary containing the changed series data object. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected or status is not SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For changed series data, this means including the VectorId, ProductId (pid), and Coordinate.

Get changed series data (data points that have changed) for a series identified by Vector ID. Disables SSL Verification. Corresponds to: POST /getChangedSeriesDataFromVector

Returns: Dict[str, Any]: A dictionary containing the changed series data object. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected or status is not SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For changed series data, this means including the VectorId.

Get the list of series (vectorId, productId, coordinate, releaseTime) that were updated on a specific date (YYYY-MM-DD). Disables SSL Verification. Corresponds to: GET /getChangedSeriesList/{date}

Returns: List[Dict[str, Any]]: A list of dictionaries describing changed series objects. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If date format is invalid or API response format is unexpected. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For changed series, this means including the VectorId.

Retrieves definitions for various code sets used by the API (e.g., frequency, units of measure). Corresponds to: GET /getCodeSets

Returns: Dict[str, Any]: Dictionary containing code set definitions (scalar, frequency, etc.). Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For code sets, this means specifying which code set table or definition is being used.

Retrieves detailed metadata for a specific data table/cube using its ProductId. Includes dimension info, titles, date ranges, codes, etc. Disables SSL Verification. Corresponds to: POST /getCubeMetadata

Start with summary=True (default). The summary strips noise (French translations, archive codes, footnotes) and shows only 3 sample members per dimension with _next_steps guidance. Safe for all context window sizes. Set summary=False only if you need the full raw member list or all API fields.

To browse dimension codes for get_sdmx_data key construction, use get_sdmx_structure. To resolve a coordinate to a vectorId, use get_series_info.

Returns: Dict[str, Any]: The metadata object for the specified cube on success. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected or status is not SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For cubes, this means including the ProductId (pid) and the Title.

Fetch filtered time-series observations from a StatCan table via SDMX REST.

Filtering is done server-side — only the requested slice is returned. Call get_sdmx_structure first to see dimension positions and valid codes.

Key syntax (dot-separated codes in dimension position order): "1.2.1" = Geography=1 (Canada), Gender=2 (Men+), Age=1 (All ages) ".2.1" = all geographies, Gender=2, Age=1 (wildcard — preferred for multi-geo) "1+2.2.1" = Geography 1 or 2, Gender=2, Age=1 (OR)

IMPORTANT — key position codes:

• Use member IDs from get_cube_metadata(), NOT SDMX codelist positions from get_sdmx_structure(). Member IDs and SDMX codelist codes are the same numbers.

• Wildcard (omit a position) returns a SPARSE SAMPLE for large dimensions — do NOT use wildcard for dimensions with >30 codes (e.g. NOC occupations, CMA geographies). Use explicit member IDs joined with '+' instead.

• To get all leaf IDs for a large dimension as a ready-to-use OR string, call get_sdmx_key_for_dimension(productId, dimension_position) first.

Time filtering (use one or the other, not both): lastNObservations=12 → last 12 periods (e.g. 1 year of monthly data) startPeriod="2020" → from 2020 onwards (annual); "2020-01" for monthly endPeriod="2023-12" → up to Dec 2023

LIMITATION: StatCan rejects combining lastNObservations with startPeriod/endPeriod (returns 406). NOTE: OR syntax (+) triggers a StatCan SDMX-JSON encoding bug (non-positional series keys). This is automatically corrected before rows are returned, so all OR-ed dimension labels should be present.

Output rows contain: dimension values, "period", "value", SCALAR_FACTOR, UOM, VECTOR_ID, STATUS, and other SDMX attributes.

Rows are returned inline, capped at MAX_SDMX_ROWS (500). For larger result sets, narrow the key or use startPeriod/endPeriod/lastNObservations.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. This means including the _sdmx_url, table information and productId/key in your response.

Return all leaf member IDs for a single dimension as a ready-to-use OR key string.

Use this before get_sdmx_data when a dimension has many codes (e.g. 162 NOC minor groups, hundreds of CMA geographies). Avoids the need to call get_cube_metadata and manually parse a large JSON response.

Leaf codes are codes with no children — the lowest-level members in a hierarchy. For flat (non-hierarchical) codelists every code is a leaf.

Example: get_sdmx_key_for_dimension(productId=98100452, dimension_position=6) → { "dimension_id": "Occupation_...", "dimension_name": "Occupation - Minor group - NOC 2021", "position": 6, "leaf_count": 162, "total_count": 309, "or_key": "7+11+12+13+16+18+21+23+...", "note": "Paste or_key at position 6 in your get_sdmx_data key." }

Then use the or_key directly: get_sdmx_data(productId=98100452, key="7.3.1.1.1.<or_key>.1", ...)

Fetch SDMX observations and always return rows inline — use this when you need to embed data in an artifact or widget.

Use this tool when you need rows embedded directly in an artifact or widget:

• Building a chart, table, or widget artifact that needs data at construction time

• Sorting/filtering a small result set before embedding

Same key syntax and time parameters as get_sdmx_data — see that tool's description for key construction rules and wildcard warnings.

Rows are capped at MAX_SDMX_ROWS. For large dimensions use get_sdmx_key_for_dimension to build a precise OR key before calling this.

IMPORTANT: In your final response to the user, cite the _sdmx_url, table productId, and key used.

Fetch the Data Structure Definition (DSD) for a StatCan table via SDMX REST.

Returns dimension codelists with code IDs, names, and parent hierarchy. Use this BEFORE get_sdmx_data to understand the key syntax for that table.

Each dimension entry includes:

• id: dimension identifier (e.g. "Geography")

• position: its slot in the dot-separated key string (1-based)

• codelist: the SDMX codelist ID (e.g. "CL_Geography")

• codes: list of {id, name, ?parent} — truncated to 10 for large codelists

• _code_count / _truncated: total size and truncation flag

Key construction rules:

• "1.2.1" = position-1 code 1, position-2 code 2, position-3 code 1

• ".2.1" = wildcard position 1 (all geographies), Gender=2, Age=1

• "1+2.2.1" = Geography 1 or 2 (OR syntax)

• WDS memberIds == SDMX codelist codes — no translation needed

IMPORTANT: In your final response to the user, you MUST cite the source of your data. This means including the _sdmx_url.

Fetch time-series observations for a single StatCan vector via SDMX REST.

Simpler alternative to get_sdmx_data when you already know the vectorId. Use get_series_info_from_cube_pid_coord or get_cube_metadata to find vectorIds.

Time filtering (use one or the other, not both): lastNObservations=5 → last 5 periods startPeriod="2020-01" → from Jan 2020 (monthly); "2020" for annual endPeriod="2023-12" → up to Dec 2023

LIMITATION: StatCan rejects combining lastNObservations with startPeriod/endPeriod (returns 406).

Output rows contain: dimension values, "period", "value", SCALAR_FACTOR, UOM, VECTOR_ID, STATUS, and other SDMX attributes.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. This means including the _sdmx_url,and vectorId in your response.

Resolve one or more {productId, coordinate} pairs to series metadata (vectorId, titles, frequency, UOM, etc.) in a single API call.

Use this to find vectorIds before fetching data with get_sdmx_data or get_sdmx_vector_data. Pass one item or many — same tool either way. Coordinates are automatically padded to 10 dimensions. Corresponds to: POST /getSeriesInfoFromCubePidCoord (accepts array)

NOTE: Response fields like scalarFactorCode, frequencyCode, and memberUomCode use StatCan numeric codes. Call get_code_sets() to decode them (e.g. frequencyCode 6 = "Monthly", scalarFactorCode 0 = "Units").

Returns: List of series metadata dicts, paginated with _guidance if >50 results. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If no items return SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response cite the ProductId and Coordinate for each series.

Request series metadata (productId, coordinate, titles, frequency, etc.) by Vector ID. Disables SSL Verification. Corresponds to: POST /getSeriesInfoFromVector

Returns: Dict[str, Any]: A dictionary containing the series metadata object. Raises: httpx.HTTPStatusError: If the API returns an error status code. ValueError: If the API response format is unexpected or status is not SUCCESS. Exception: For other network or unexpected errors.

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For series info, this means including the VectorId, ProductId (pid), and Coordinate.

Retrieves the schema (column names and types) for a specific table.

Args: table_name_input: Object containing the table_name.

Returns: Dict[str, Any]: Dictionary describing the schema or an error message.

Appends rows (list of dicts) into an ALREADY EXISTING SQLite table. Use this only to add more data to a table that was previously created.

For the common "fetch API data then store" workflow, use create_table_from_data or fetch_vectors_to_database instead — both create the table AND insert data in a single call, so you do NOT need to call this tool after them.

This tool is useful when:

• You want to merge data from multiple API calls into one table

• You're appending new time periods to an existing dataset

Args: table_input: Object containing table_name and data (list of dicts).

Returns: Dict[str, str]: A dictionary indicating success (with row count) or failure.

IMPORTANT: In your final response to the user, you MUST cite the source of the data you are inserting if it comes from an API call (e.g., "Data from Product ID 123456").

Lists all user-created tables in the SQLite database.

Returns: Dict[str, Any]: Dictionary containing a list of table names or an error message.

IMPORTANT: The database is persistent. Use this to check for old tables that might need cleaning.

Executes a read-only SQL query (SELECT or PRAGMA) against the database and returns the results. WARNING: Potential security risk! Avoid using this tool with untrusted input or queries that modify data (INSERT, UPDATE, DELETE). Prefer more specific tools like list_tables or get_table_schema when possible. Results may be truncated.

Args: query_input: Object containing the sql_query string.

Returns: Dict[str, Any]: Dictionary with 'columns', 'rows' (list of dicts), and optionally a 'message', or an error message.

IMPORTANT: In your final response to the user, you MUST cite the source of your data (e.g., "Query results from table 'my_analysis'").

Searches for data cubes/tables where the English or French title contains the provided search term (case-insensitive). Returns a list of matching cubes in the 'lite' format (excluding dimensions/footnotes).

Multiple keywords use AND logic (e.g., "tobacco smoking age" finds cubes containing ALL three words). Results are capped at max_results (default 25).

IMPORTANT: In your final response to the user, you MUST cite the source of your data. For cubes, this means including the ProductId (pid) and the Title.

Raises: httpx.HTTPStatusError: If the underlying API call fails. Exception: For other network or unexpected errors during the fetch.

Fetches FULL metadata for a cube and stores it into two normalized SQLite tables (_statcan_dimensions, _statcan_members) without returning the full data to the context window.

Use this when you need to browse all dimension members or look up vectorIds. The summary returned by get_cube_metadata only shows 5 members per dimension — call this tool first, then use SQL to drill into specific dimensions.

Typical workflow:

1. store_cube_metadata(productId=1234567) → stores all members + vectorIds, returns compact summary

2. query_database("SELECT * FROM _statcan_dimensions WHERE pid = 1234567") → see all dimension names and member counts

3. query_database("SELECT member_name_en, vector_id FROM _statcan_members WHERE pid = 1234567 AND dim_index = 2") → browse all members for a specific dimension

4. fetch_vectors_to_database(vectorIds=[...], ...) → fetch the data

Tables are shared across multiple pids — calling this for a new pid adds rows without affecting data for other pids already stored.

Returns a compact summary: dimension names + member counts + example SQL.

IMPORTANT: Cite the productId and cubeTitleEn in your final response.