MotherDuck connection does not work

[alvinwanyeki]

issue summary

lea's prepare and run commands fail to work properly with MotherDuck connections, but they work with standard DuckDB connections (for the same views).

environment

• lea version: 0.5.0
• Database: MotherDuck (v1.1.2)
• Python version: 3.12
• Views: Using example view from jaffle_shop

observed behavior

1. schema creation (lea prepare)

   • Commands appear successful but schema isn't created on md:

   $ lea prepare
   [13:30:35] Created schema staging                                     duckdb.py:55
   
   $ lea prepare --production
   [13:30:41] Created schema staging                                     duckdb.py:55

2. view creation (lea run)

   • Development mode fails with syntax error (same model works on DuckDB):

   $ lea run
   [13:30:46] 1 out of 1 views selected                                                  runner.py:74   
    #  view                  status   duration  cost
    1  staging.raw_payments  ERRORED        0s
              Took 0s                                                                    runner.py:74
   ┏━━━━━━━━━┳━━━━━━━┓
   ┃ status  ┃ count ┃
   ┡━━━━━━━━━╇━━━━━━━┩
   │ ERRORED │ 1     │
   └─────────┴───────┘
   staging.raw_payments
   Parser Error: syntax error at or near ":"

   • Production mode reports success but no view is created on md:

   $ lea run --production
   [13:30:55] 1 out of 1 views selected                                                  runner.py:74
    #  view                  status   duration  cost
    1  staging.raw_payments  SUCCESS        0s
              Took 0s                                                                    runner.py:74
   ┏━━━━━━━━━┳━━━━━━━┓
   ┃ status  ┃ count ┃
   ┡━━━━━━━━━╇━━━━━━━┩
   │ SUCCESS │ 1     │
   └─────────┴───────┘

Expected Behavior

• Schema and views should be created in MotherDuck as indicated by the command output

additional information

• I've set up my env as such:

LEA_USERNAME=<my_name>

LEA_WAREHOUSE=duckdb
LEA_DUCKDB_PATH=md:<my_db>
MOTHERDUCK_TOKEN=<my_token>

[MaxHalford]

Thanks for the report, I will take a look. To be honest I was waiting for someone to give it a spin, as I haven't had the time to try it out properly yet.

[alvinwanyeki]

Thanks @MaxHalford -- Happy to help with testing, looking forward to adopting lea!

[alvinwanyeki]

@MaxHalford -- i've been able to fix this issue; is there anything i need to do/know before i open a pr? thank you!

[MaxHalford]

Good job!

Well actually, it's a bit of a surprise, but I am completely rewriting the library to make it better. The interface will be the same, but the internals will be the same. I suggest that you make the pull request, and I'll take of integrating it. I hope that's ok 👍

[alvinwanyeki]

thank you; this is okay! i'll open the pr later today 👍

interface will be the same, but the internals will be the same.

on the rewrite, to confirm, did you mean the interface will be the same BUT the internals will be different? also, i'm happy to help with testing the duckdb/motherduck connectors :)

[MaxHalford]

on the rewrite, to confirm, did you mean the interface will be the same BUT the internals will be different? also, i'm happy to help with testing the duckdb/motherduck connectors :)

Yes, my bad :)

[alvinwanyeki]

@MaxHalford - just curious, on rewriting lea, will you change how 'non-root' .py views are currently identified, i noticed that the current implementation seems limited to pandas, so using something like duckdb.sql() or pl.read_database() in 'non-root' views won't work.