SQLite is an embedded, weakly typed OLTP database that can be queried from DuckDB when analytical queries benefit from DuckDB’s engine without importing the data into persistent storage. section 5.6 section 5.0
A practical SQLite-to-DuckDB workflow is:
- Install and load the SQLite extension, then attach the database file. section 5.6
- Inspect attached tables with PRAGMA show_tables or the .tables command. section 5.6
- Resolve schema mismatches by scanning tables as VARCHAR and casting columns explicitly in views. section 5.6
Inspect and normalize a player table
Attach the SQLite file, then create a view with explicit types: cast birthday to DATE, height and weight to FLOAT, and identifier columns to BIGINT. Querying the resulting view exposes usable player records and supports joins with Player_Attributes, such as ranking players by their latest overall rating. section 5.6
USE main;
CREATE OR REPLACE VIEW Player AS
FROM sqlite_scan('database.sqlite', 'Player')
SELECT * REPLACE (
id :: BIGINT AS id,
player_api_id :: BIGINT AS player_api_id,
player_fifa_api_id :: BIGINT AS player_fifa_api_id,
birthday :: DATE AS birthday,
height :: FLOAT AS height,
weight :: FLOAT AS weight
);
Use the runnable view definition to convert SQLite’s inferred values into an analytical schema. section 5.6
