titan_test_db = Database(
    name="TITAN_TEST_DB",
    comment="This is a database used for testing of titan[core].",
    transient=True,
    owner="SYSADMIN",
    data_retention_time_in_days=None,
    max_data_extension_time_in_days=None,
)

results in

snowflake.connector.errors.ProgrammingError: 001003: failed to execute sql, [CREATE DATABASE TITAN_TEST_DB TRANSIENT COMMENT = $$This is a database used for testing of titan[core].$$]

The correct syntax is:
CREATE TRANSIENT DATABASE TITAN_TEST_DB COMMENT = $$This is a database used for testing of titan[core].$$;