Picking up the ball where fernandobrito left off. I have a major enhancement (one of two in the works). Lots of cleaned up code, bug fixes from the other fork, personally tested on my own production replicates and internally at my company, etc. There may be some more changes/commits I add (to readme mostly) but I just wanted to post it to get your eyes and awareness. The other PR will involve auto documenting all exposures revealed in metabase through questions / dashboards and constructing a YML that allows us to sync a DAG with metabase fully including click throughs from dbt docs exposures directly to dashboards.

Description

Reposting because some branch renaming fudged up the PR. This is still ready for review.

Parsing exposures from Metabase automatically in relation to a dbt project. This function is huge for understanding exposures within your data model. Close the loop between model composition and tangible BI exposures of those models creating an extremely valuable company asset in the dbt docs. In the nature of this project, the aim is we can use almost the same invocation parameters users set up in CI / CD for dbt-metabase export as they can with dbt-metabase exposures. This lets you run the invocation in production to essentially keep you docs in full sync with BI.

Type of change

• [x] New feature (non-breaking change which adds functionality)
• [x] This change requires a documentation update

How Has This Been Tested?

Open to ideas but ultimately I think making a mock api from metabase running on top of a database that has been seeded and ran through with the dbt jaffle shop project would be most robust. Just a lot of work.

• [x] CI test for exposure documentation

Test Configuration:

• Python version: 3.8

Checklist:

• [x] My code follows the style guidelines of this project
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [x] I have made corresponding changes to the documentation
• [x] My changes generate no new warnings
• [x] I have added tests that prove my fix is effective or that my feature works
• [x] New and existing unit tests pass locally with my changes

References #22

Providing a null metabase.semantic_type explicitly writes it to the API, omitting it leaves the existing one.

Changes behaviour in #118 and #119

Resolves #131

Description

Large refactor to use click as cli interface, improve and further abstract interface complexity, allow env vars implicitly, improve logging with global logger + rich, add config yaml with interactive setup via config command, load config via hook in CLI, split commands in sub commands, optimize help text, add implicit file / folder existence checking on file / folder args.

Sub commands broken out:

Optimized help text

Interactive config file generation:

Checking config file contents with simple dbt-metabase config --inspect and running a CLI command with just dbt-metabase models with auto config loading:

An example config:

config:
  dbt_database: reporting
  mb_user: alex@email
  ...
  mb_database: Reporting

Type of change

• [x] New feature (non-breaking change which adds functionality)
• [x] This change requires a documentation update

How Has This Been Tested?

Existing unit tests as well as production use. In addition, this time around using nox I have tested the package on Python 3.6, 3.7 and 3.8

Test Configuration:

• Python version: 3.6
• Python version: 3.7
• Python version: 3.8

Checklist:

• [x] My code follows the style guidelines of this project
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [x] I have made corresponding changes to the documentation
• [x] My changes generate no new warnings
• [x] I have added tests that prove my fix is effective or that my feature works
• [x] New and existing unit tests pass locally with my changes

Closing Notes

Env Vars taking precedence over config.yml defaults (via injection) CLI flags taking highest precedence as in a standard CLI tool (overrides)

When I run the following command, the session is established but then the process ends without any message:

dbt-metabase models \
    --dbt_manifest_path ./target/manifest.json \
    --dbt_schema dbt_prod \
    --metabase_host $url \
    --metabase_user $metabase_email \
    --metabase_password $metabase_pw \
    --metabase_database $dbname \
    --dbt_database dbt_prod

2021-09-14 09:42:07,987 - INFO - Session established successfully

Am I doing something wrong?

Description

Bring your metrics into revision control, bring them into your data model, sync it directly with Metabase.

dbt-metabase metrics \
  --dbt_database test \
  --dbt_path tests/fixtures/metric/ \
   --schema public \
   --metabase_host localhost:3000 \
   --metabase_user alex@... \
   --metabase_password "..." \
   --metabase_database unit_testing \
   --metabase_use_http --verbose

Expression syntax: https://www.metabase.com/docs/latest/users-guide/expressions.html

  - name: Number of Customers with Large Orders
    description: Customers who are big spenders should be tracked independently of total,
      any customer who orders over 20 AUD of jaffle is counted
    metric: countif([customer_lifetime_value] > 20)

We will parse: countif([customer_lifetime_value] > 20) into['count-where', ['>', ['field', 41, None], 20]] The parser should be able to handle any type of expression allowing users to use the nice excel like syntax built by metabase team directly alongside your data models. They become centralized, self contained, and gain all the advantages of dbt/jinja.

Parsing Examples

Purposefully convoluted examples showing robustness and possibilities (most metrics are simple in theory with preprocessing logic in the model)

Input: Sum(case([site_dispenser_count] + 1 > 1 or [site_dispenser_count] - 1 > 1, [site_dispenser_count] + 1)) Output: ['sum', ['case', [[['or', ['>', ['+', ['field', 1, 'site_dispenser_count'], 1], 1], ['>', ['-', ['field', 1, 'site_dispenser_count'], 1], 1]], ['+', ['field', 1, 'site_dispenser_count'], 1]]]]]

Input: Sum([table.order] + [qty] * 2 + 4 + 5 - 4 + 5) Output: ['sum', ['+', ['-', ['+', ['field', 1, 'table.order'], ['*', ['field', 1, 'qty'], 2], 4, 5], 4], 5]]

Input: SumIf([site_dispenser_count], [site_city] > "Phoenix" or [site_state] = "Arizona") Output: ['sum-where', ['field', 1, 'site_dispenser_count'], ['or', ['>', ['field', 1, 'site_city'], '"Phoenix"'], ['=', ['field', 1, 'site_state'], '"Arizona"']]]

Input: Distinct(case([site_city] = "Phoenix", [site_panel_count])) / distinct([site_dispenser_count]) Output: ['/', ['distinct', ['case', [[['=', ['field', 1, 'site_city'], '"Phoenix"'], ['field', 1, 'site_panel_count']]]]], ['distinct', ['field', 1, 'site_dispenser_count']]]

Type of change

• [x] New feature (non-breaking change which adds functionality)
• [x] This change requires a documentation update

How Has This Been Tested?

• [ ] CI test for metric propagation and synchronization

Test Configuration:

• Python version: 3.8

Checklist:

• [x] My code follows the style guidelines of this project
• [x] I have performed a self-review of my own code
• [x] I have commented my code, particularly in hard-to-understand areas
• [ ] I have made corresponding changes to the documentation
• [x] My changes generate no new warnings
• [ ] I have added tests that prove my fix is effective or that my feature works
• [x] New and existing unit tests pass locally with my changes

References #25

#17 introduced a bug by collecting models listed under DBT sources.

The problem is that sources' tables are typically not stored under the DBT schema but this script only collected Metabase models under the DB schema specified as an argument. In consequence, when a "source" model is loaded, the script won't be able to match it with a Metabase model and the script times out.

The solution implemented makes the script "schema-aware" by collecting the schema name explicitly for sources. It also indexes all models from Metabase in order to be able to find models outside the DBT Schema.

The behavior for DBT models outside sources is unchanged. They are assume to be stored under the "schema" passed explicitly as an argument.

This change is a stepping stone towards addressing #3

Hey, thanks for sharing you work on this.

I'm having some problems using the library to parse my project. Neither the the manifest parser nor the DbtFolderReader is able to parse it without failing. For my project I'm using dbt 0.20.0, is this version supported by this package? The manifest schema has slightly changed the last couple of versions and I'm wondering if you might be working with another version.

I've started testing dbt-metabase and I ran into an issue with running dbt-metabase export from the command line with the --mb_https option.

I am running Metabase locally in a docker container and was getting an SSL error. I found while troubleshooting that I can set mb_https to False when calling the export function from a Python script, but not from the command line.

I did some research and came up with a solution that adds an argument. I've tested it locally and I'm able to run the export from the command line. I've never made an open-source PR before 🙈 , so please feel free to give me feedback/suggestions! I realize that adding an argument may not be optimal.

The export_model function supports setting the display name for models from a display_name property on MetabaseModel.

However, this isn't accessible, as the meta: metabase.display_name property is not parsed and populated in the construction of the MetabaseModel in _read_model.

Previously, any values passed in a config.yml file for boolean flags (metabase_use_http, metabase_sync) were ignored.

This change will override any flags that are of type click.types.BoolParamType with values provided in a config.yml file, even if specified in a command line argument.

For example, when metabase_use_http is set to True in config.yml, running dbt-metabase models --metabase_https will still default to using HTTP.

Resolves #99

This happens when referencing other metabase questions in metabase

In the metabase exposures yml here is an extract of a problematic line

exposures:
  - name: my_viz
    description: "### Visualization: Table\n\nSee https://example.com/xx\n\
      \n#### Query\n\n```\nwith \nappointments as (\n  select * FROM `xxxx.fct_appointments`\
      \ \n),\nusers as (\n  select * from {{#161}}\n  where last_active_at_cache >\
      \ timestamp_sub(current_timestamp, interval 365 DAY)\n),\napts as (\n  SELECT\

I believe dbt tries to interpolate the metabase question references as jinja expression, thus causing problems

jinja2.exceptions.TemplateSyntaxError: unexpected char '#' at 276
  line 13
    select * from {{#161}}

This is likely to cause problems with every metabase custom variable/reference wrapped under {{}}

Is there a way to either post-process the exposures.yml to escape those characters, or find a way to disable dbt jinja interpolation for metabase exposures ?

To make code exploration and review easier, would it be possible to replicate metabase file and folder hierarchy when generating exposures ? Like reusing the slug

/metabase-exposures/question/42-my-awesome-question.yml /metabase-exposures/dashboard/84-my-super-dashboard

Or maybe just the ID if to avoid too many file changes when just renaming something ?

/metabase-exposures/cards/1.yml /metabase-exposures/cards/2.yml /metabase-exposures/dashboards/1.yml

Currently all exposures are sent to a single file and it's messy

On Metabase, it's possible to define permissions restrictions to tables for certain groups (Such as in the picture)

In the Open Source plan for Metabase, we can define if this group has either "Unrestricted" or "No self-Service" permission to the table, I am not sure if this changes for the other plans.

Is the automation for the permission system, something planned/wanted for this package?

One suggestion that could make it possible is to add this in the meta section of the table definition, and define which groups have which level of permission:

Lets consider our usual flow of

dbt compile -m my_model navigate to compiled model copy paste sql find somewhere to run it to test it OR dbt run -m my_model check in datagrip, dbeaver, dbvisualizer, vscode sql ext, etc. repeat x?... which the latter implies potentially multiple CTAS, tables being dropped and rewritten, etc. or the inability to test in production vs compile and pasting somewhere you can run it.

I think we can easily offer an ability to invoke something like:

dbt-metabase explore my_model

And on our end conceptually, we use a subprocess to invoke dbt compile -m my_model Read the compiled sql from the manifest.json or file. Construct and pass it into a json object that looks something like {dataset_query: "SELECT * FROM ..."} with your compiled model query inputted Base64 encode the json dumped string And construct a URL like this as many Metabase aficionados may be familiar with (its how metabase caches your in progress queries in the editor).

https://my.metabase/question#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiO...c2V0dGluZ3MiOnt9fQ==

This will allow, with a simple invocation, using metabase as a front end graphical dbt model explorer without the above workflow being an impedance. I have a similar goal in my project dbt-osmosis which uses a dbt-osmosis workbench invocation with streamlit as a backend for rapidly developing/prototyping and diffing the data output of model changes. I think for Metabase power users, this is a simple and effective alternative too.

We empower users to explore models, all without actually ever asking them for db creds. Interesting right. Opening this now to gauge feedback prior to implementation.

Hello,

I've got a KeyError: 'fk_target_table' when propagating data from dbt table to Metabase. This error occurs in two specific cases:

1. A column in the the schema.yml has a semantic type equal to type/FK but it has not a tests section within it.
2. A column in the the schema.yml has a semantic type equal to type/FK but its tests section contains a dbt_utils.relationships_where test within it.