Repository: gouline/dbt-metabase

Issue:
Additional metadata to understand "usability" of a card
Hi there,

I'm a big fan of `dbt-metabase`! Especially the `exposures` feature has been a complete game changer to avoid deploying model changes that would break metabase cards.

With a growing number of cards in Metabase, my team figured that it's not only important to understand by which cards our DBT models are referenced , but also whether these cards are still being used. Hence, I was thinking to add the `last_used_at` and `average_query_time` fields from the metabase card API to the exposure metadata. They strike me as a good proxy for whether a card is still usable (query time) and whether it gets used (last used timestamp)

I've already done this on a local fork (see screenshot) of the repo and would be happy to contribute it. Wdyt @gouline?

<img width="1029" alt="Screenshot 2024-10-09 at 17 49 49" src="https://github.com/user-attachments/assets/c53a5d59-4e69-4ca5-a324-5da5521bb9e8">




Discussion:

Patch:
diff --git a/dbtmetabase/_exposures.py b/dbtmetabase/_exposures.py
index 610cf16..3c5d9aa 100644
--- a/dbtmetabase/_exposures.py
+++ b/dbtmetabase/_exposures.py
@@ -112,6 +112,8 @@ class ExposuresMixin(metaclass=ABCMeta):
                 depends = set()
                 native_query = ""
                 header = ""
+                average_query_time = None
+                last_used_at = None
 
                 entity: Mapping
                 if item["model"] == "card":
@@ -129,6 +131,13 @@ class ExposuresMixin(metaclass=ABCMeta):
                     depends.update(result["depends"])
                     native_query = result["native_query"]
 
+                    average_query_time_ms = entity.get("average_query_time")
+                    if average_query_time_ms:
+                        average_query_time_s = average_query_time_ms / 1000
+                        average_query_time = f"{(average_query_time_s // 60):.0f}:{(average_query_time_s % 60):06.3f}"
+
+                    last_used_at = entity.get("last_used_at")
+
                 elif item["model"] == "dashboard":
                     dashboard_entity = self.metabase.find_dashboard(uid=item["id"])
                     if dashboard_entity is None:
@@ -190,6 +199,8 @@ class ExposuresMixin(metaclass=ABCMeta):
                             created_at=entity["created_at"],
                             creator_name=creator_name or "",
                             creator_email=creator_email or "",
+                            last_used_at=last_used_at,
+                            average_query_time=average_query_time,
                             native_query=native_query,
                             depends_on=sorted(
                                 [
@@ -310,6 +321,8 @@ class ExposuresMixin(metaclass=ABCMeta):
         created_at: str,
         creator_name: str,
         creator_email: str,
+        last_used_at: Optional[str],
+        average_query_time: Optional[str],
         native_query: Optional[str],
         depends_on: Iterable[str],
         tags: Optional[Sequence[str]],
@@ -362,6 +375,14 @@ class ExposuresMixin(metaclass=ABCMeta):
             "depends_on": list(depends_on),
         }
 
+        meta = {}
+        if average_query_time:
+            meta["average_query_time"] = average_query_time
+        if last_used_at:
+            meta["last_used_at"] = last_used_at
+        if meta:
+            exposure["meta"] = meta
+
         if tags:
             exposure["tags"] = list(tags)
 


Test patch:
diff --git a/tests/fixtures/exposure/collection/our_analytics.yml b/tests/fixtures/exposure/collection/our_analytics.yml
index 4f9f527..d253c21 100644
--- a/tests/fixtures/exposure/collection/our_analytics.yml
+++ b/tests/fixtures/exposure/collection/our_analytics.yml
@@ -46,6 +46,9 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.028'
+      last_used_at: '2024-06-20T05:56:57.295379Z'
   - name: dummy_1
     label: Dummy
     description: '### Visualization: Table
@@ -78,6 +81,9 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.024'
+      last_used_at: '2024-06-20T05:57:12.297288Z'
   - name: orders___customers
     label: Orders + Customers
     description: '### Visualization: Table
@@ -102,6 +108,9 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.107'
+      last_used_at: '2024-06-20T05:55:59.079272Z'
   - name: orders___customers__filtered_by_status_is_completed
     label: Orders + Customers, Filtered by Status is completed
     description: '### Visualization: Table
@@ -126,6 +135,9 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.264'
+      last_used_at: '2024-06-19T12:09:31.689381Z'
   - name: returned_order_count_sql
     label: Returned Order Count SQL
     description: "### Visualization: Scalar\n\nNo description provided in Metabase\n\
@@ -141,6 +153,9 @@ exposures:
     depends_on:
       - ref('stg_orders')
       - ref('stg_payments')
+    meta:
+      average_query_time: '0:00.191'
+      last_used_at: '2024-06-19T12:09:31.486489Z'
   - name: the_dashboard
     label: The Dashboard
     description: '### Dashboard Cards: 3
@@ -190,3 +205,6 @@ exposures:
       email: dbtmetabase@example.com
     depends_on:
       - ref('payments')
+    meta:
+      average_query_time: '0:00.058'
+      last_used_at: '2024-10-12T01:52:30.585371Z'
diff --git a/tests/fixtures/exposure/default/exposures.yml b/tests/fixtures/exposure/default/exposures.yml
index 622c59a..c5b3559 100644
--- a/tests/fixtures/exposure/default/exposures.yml
+++ b/tests/fixtures/exposure/default/exposures.yml
@@ -48,6 +48,9 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.028'
+      last_used_at: '2024-06-20T05:56:57.295379Z'
     tags:
       - metabase
   - name: dummy_1
@@ -82,6 +85,9 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.024'
+      last_used_at: '2024-06-20T05:57:12.297288Z'
     tags:
       - metabase
   - name: orders___customers
@@ -108,6 +114,9 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.107'
+      last_used_at: '2024-06-20T05:55:59.079272Z'
     tags:
       - metabase
   - name: orders___customers__filtered_by_status_is_completed
@@ -134,6 +143,9 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.264'
+      last_used_at: '2024-06-19T12:09:31.689381Z'
     tags:
       - metabase
   - name: returned_order_count_sql
@@ -151,6 +163,9 @@ exposures:
     depends_on:
       - ref('stg_orders')
       - ref('stg_payments')
+    meta:
+      average_query_time: '0:00.191'
+      last_used_at: '2024-06-19T12:09:31.486489Z'
     tags:
       - metabase
   - name: the_dashboard
@@ -204,5 +219,8 @@ exposures:
       email: dbtmetabase@example.com
     depends_on:
       - ref('payments')
+    meta:
+      average_query_time: '0:00.058'
+      last_used_at: '2024-10-12T01:52:30.585371Z'
     tags:
       - metabase
diff --git a/tests/fixtures/exposure/type/card/27.yml b/tests/fixtures/exposure/type/card/27.yml
index 4bb4813..25b3034 100644
--- a/tests/fixtures/exposure/type/card/27.yml
+++ b/tests/fixtures/exposure/type/card/27.yml
@@ -24,3 +24,6 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.107'
+      last_used_at: '2024-06-20T05:55:59.079272Z'
diff --git a/tests/fixtures/exposure/type/card/28.yml b/tests/fixtures/exposure/type/card/28.yml
index c4ddef3..bf2d27b 100644
--- a/tests/fixtures/exposure/type/card/28.yml
+++ b/tests/fixtures/exposure/type/card/28.yml
@@ -24,3 +24,6 @@ exposures:
     depends_on:
       - ref('customers')
       - ref('orders')
+    meta:
+      average_query_time: '0:00.264'
+      last_used_at: '2024-06-19T12:09:31.689381Z'
diff --git a/tests/fixtures/exposure/type/card/29.yml b/tests/fixtures/exposure/type/card/29.yml
index cb57633..b724a4b 100644
--- a/tests/fixtures/exposure/type/card/29.yml
+++ b/tests/fixtures/exposure/type/card/29.yml
@@ -15,3 +15,6 @@ exposures:
     depends_on:
       - ref('stg_orders')
       - ref('stg_payments')
+    meta:
+      average_query_time: '0:00.191'
+      last_used_at: '2024-06-19T12:09:31.486489Z'
diff --git a/tests/fixtures/exposure/type/card/30.yml b/tests/fixtures/exposure/type/card/30.yml
index f43d96a..3b101a8 100644
--- a/tests/fixtures/exposure/type/card/30.yml
+++ b/tests/fixtures/exposure/type/card/30.yml
@@ -32,3 +32,6 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.028'
+      last_used_at: '2024-06-20T05:56:57.295379Z'
diff --git a/tests/fixtures/exposure/type/card/31.yml b/tests/fixtures/exposure/type/card/31.yml
index df222eb..e459b00 100644
--- a/tests/fixtures/exposure/type/card/31.yml
+++ b/tests/fixtures/exposure/type/card/31.yml
@@ -32,3 +32,6 @@ exposures:
       name: dbtmetabase
       email: dbtmetabase@example.com
     depends_on: []
+    meta:
+      average_query_time: '0:00.024'
+      last_used_at: '2024-06-20T05:57:12.297288Z'
diff --git a/tests/fixtures/exposure/type/card/33.yml b/tests/fixtures/exposure/type/card/33.yml
new file mode 100644
index 0000000..be2907c
--- /dev/null
+++ b/tests/fixtures/exposure/type/card/33.yml
@@ -0,0 +1,28 @@
+version: 2
+exposures:
+  - name: transactions
+    label: Transactions
+    description: '### Visualization: Table
+
+
+      No description provided in Metabase
+
+
+      #### Metadata
+
+
+      Metabase ID: __33__
+
+
+      Created On: __2024-10-11T23:46:29.272117Z__'
+    type: analysis
+    url: http://localhost:3000/card/33
+    maturity: medium
+    owner:
+      name: dbtmetabase
+      email: dbtmetabase@example.com
+    depends_on:
+      - ref('payments')
+    meta:
+      average_query_time: '0:00.058'
+      last_used_at: '2024-10-12T01:52:30.585371Z'