dbt-elementary-sqlite

Elementary data observability support for SQLite (dbt-sqlite adapter).

Why This Package Exists

Problem: dbt Elementary is not officially supported for SQLite. When trying to configure and run Elementary with the sqlite adapter, it fails with multiple SQL syntax errors:

Database Error
  near "s": syntax error  -- ❌ escape_special_chars uses \' instead of ''

Runtime Error
  unrecognized token: ":"  -- ❌ current_timestamp::timestamp not supported in SQLite

Compilation Error
  Could not render type 'varchar(4096)'  -- ❌ SQLite uses TEXT, not varchar

Root Cause: Elementary has adapter-specific implementations for:

• ✅ snowflake, bigquery, redshift, postgres, databricks, spark, athena, trino, clickhouse, dremio
• ❌ sqlite - Not supported

Solution: This package provides sqlite__ macro implementations that make Elementary work with SQLite by translating all SQL constructs to SQLite-compatible syntax.

Installation

1. Add to your packages.yml:

packages:
  - package: elementary-data/elementary
    version: 0.22.1
  - git: https://github.com/accelerate-data/vd-dbt-elementary-sqlite.git
    revision: main

2. Configure dispatch in dbt_project.yml:

dispatch:
  - macro_namespace: elementary
    search_order:
      - elementary_sqlite  # Check our package first
      - elementary          # Then Elementary's own macros

3. Configure Elementary models:

models:
  elementary:
    +schema: elementary
    +tags: ["elementary"]

4. Install dependencies:

dbt deps

What This Package Does

Provides 34 sqlite__ macro implementations for Elementary:

Data Type Macros

• sqlite__edr_type_string() - Returns "text"
• sqlite__edr_type_long_string() - Returns "text"
• sqlite__edr_type_bool() - Returns "integer"
• sqlite__edr_type_timestamp() - Returns "text"
• sqlite__edr_type_date() - Returns "text"
• sqlite__data_type_list(data_type) - SQLite-specific type lists
• sqlite__get_normalized_data_type(exact_data_type) - Type normalization

String Functions

• sqlite__escape_special_chars(string_value) - Uses '' instead of \'
• sqlite__contains(string, string_to_search, case_sensitive) - Uses instr()
• sqlite__const_as_text(string) - Cast constant as text

Timestamp & Date Functions

• sqlite__edr_current_timestamp() - datetime('now')
• sqlite__edr_current_timestamp_in_utc() - datetime('now')
• sqlite__edr_cast_as_timestamp(field) - cast(field as text)
• sqlite__edr_cast_as_date(field) - date(field)
• sqlite__edr_date_trunc(date_part, date_expression) - Uses strftime()
• sqlite__edr_datediff(first_date, second_date, datepart) - Uses julianday()
• sqlite__edr_timeadd(date_part, number, timestamp_expression) - Uses datetime() modifiers
• sqlite__edr_to_char(column, format) - Uses strftime()

Day/Hour Expressions

• sqlite__edr_day_of_week_expression(date_expr) - Uses strftime('%w')
• sqlite__edr_hour_of_day_expression(date_expr) - Uses strftime('%H')
• sqlite__edr_hour_of_week_expression(date_expr) - Combined day + hour

Casting & Type Conversion

• sqlite__edr_safe_cast(field, type) - cast(field as type)
• sqlite__edr_cast_as_float(column) - cast(column as real)
• sqlite__edr_cast_as_numeric(column) - cast(column as numeric)

Table Operations

• sqlite__has_temp_table_support() - Returns true
• sqlite__insert_rows(...) - Handles adapter transaction state for proper commits
• sqlite__create_or_replace(temporary, relation, sql_query) - Table creation
• sqlite__replace_table_data(relation, rows) - Delete and re-insert
• sqlite__get_delete_and_insert_queries(...) - Separate DELETE/INSERT (no transaction wrapping)
• sqlite__get_relation_max_name_length() - Returns none (no limit)

Metadata & Configuration

• sqlite__target_database() - Returns target.database
• sqlite__get_columns_from_information_schema(...) - Returns empty (SQLite limitation)
• sqlite__generate_elementary_profile_args(...) - SQLite profile config

Advanced Operations

• sqlite__complete_buckets_cte(...) - Recursive CTE for time bucket generation
• sqlite__get_clean_elementary_test_tables_queries(...) - Test table cleanup

Key SQLite-Specific Fixes

1. String Escaping

SQLite uses '' for escaping single quotes, not \' (which is PostgreSQL/MySQL syntax).

2. Transaction Handling

dbt-sqlite adapter's commit() fails when transaction_open is False after the model run phase. This package opens an adapter transaction before inserts so adapter.commit() succeeds, preventing data loss in dbt_run_results and dbt_invocations.

3. Date/Time Functions

SQLite has no native timestamp type. All date/time operations use:

• datetime(), date(), strftime() for formatting
• julianday() for date difference calculations
• String modifiers like '+1 days' for date arithmetic

4. Type System

SQLite has a flexible type system: TEXT, INTEGER, REAL, NUMERIC. No varchar(n), boolean, or timestamp types.

Verified Working

Tested with:

• ✅ dbt-sqlite adapter v1.10.0
• ✅ Elementary v0.22.1
• ✅ dbt-core v1.11.6

Confirmed functionality:

• ✅ All 33 Elementary models compile and run (PASS=35, 0 errors)
• ✅ All Elementary runtime tables populated (dbt_models, dbt_tests, dbt_run_results, dbt_invocations, dbt_columns)
• ✅ On-run-end hook completes successfully
• ✅ Metadata collection on every dbt run

Limitations

• ❌ Elementary CLI: Not supported for SQLite
• ⚠️ Information Schema: get_columns_from_information_schema returns empty (SQLite limitation)
• ⚠️ No SAFE_CAST: SQLite cast() does not have a safe/try variant

Maintenance

When Elementary releases a new version:

1. Check if new dispatched macros were added
2. Add corresponding sqlite__ implementations
3. Test with your project
4. Update version in README

Contributing

This package is maintained by the AccelerateData team for internal use but is open source.

Issues and PRs welcome at: https://github.com/accelerate-data/vd-dbt-elementary-sqlite

Related

• Elementary Documentation
• dbt-sqlite adapter
• dbt-elementary-fabricspark - Similar package for Fabric Lakehouse

---

Status: ✅ Production Ready

Last Updated: 2026-02-27