I use a desktop SQL IDE which lets me export my "catalog" which is a list of SQL entries I've saved. [Catalog Export] is in JSON format with various metadata for each folder and entry. I can also import a previously exported catalog from myself or someone else using the same software. When I perform a catalog import, it will only add or change entries which are different from what I already have. The software has a capability of scheduling an automatic export of my catalog on a recurring basis. I can set the export destination to any mapped directory on my computer. I have a few colleagues who also use this software, and I'm trying to figure out the best system using off-the-shelf tools or custom scripts which will automatically grab all of our latest export files (which may have 0, 1, or many edits since a previous export), merge it into one file, with the latest versions of each entry, and then notify all of us of to perform an import when entries have changed.

Sample [Catalog Export]
{"entities":[{"fqn":"/Analytics Engineering/Certified/Customers","id":"92d50dfc-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"3e489d3b-54c5-11ed-9d7c-03f2f2a5399d","name":"Customers","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Analytics Engineering/Certified/Customers/Customers","id":"b2775a0d-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"92d50dfc-54c5-11ed-9d7c-03f2f2a5399d","name":"Customers","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"Customer sales information","content":"#+import \"Analytics Engineering/Certified/Staging\" as staging\n\n#+src sql Customer\_Orders()\n#+meta {\n :publication {\n :schema \"retail\_shop\",\n :type \"view\",\n :name \"customer\_orders\"\n }\n}\n#+begin\n select\n customer\_id,\n min(order\_date) as first\_order\_date,\n max(order\_date) as most\_recent\_order\_date,\n count(order\_id) as number\_of\_orders\n\n from {{ staging.STG\_Orders() }}\n\n group by 1\n#+end\n\n#+src sql Customers()\n#+meta {\n :publication {\n :schema \"retail\_shop\",\n :type \"view\",\n :name \"customer\_order\_summary\"\n }\n}\n#+begin\n \n select\n customer\_id,\n first\_name,\n last\_name,\n first\_order\_date,\n most\_recent\_order\_date,\n coalesce(number\_of\_orders, 0) as number\_of\_orders\n\n from {{ staging.STG\_Customers() }}\n left join {{ Customer\_Orders() }} using (customer\_id)\n#+end\n\n#+src sql CustomerOrderStatus(order\_status)\n#+meta {}\n#+begin\n \n select\n o.order\_id,\n c.first\_name,\n c.last\_name,\n o.status \n from {{ staging.STG\_Customers() }} c\n left join {{ staging.STG\_Orders() }} o using (customer\_id)\n where\n status like '%{{order\_status}}%'\n#+end\n\nselect \* from {{ CustomerOrderStatus(\"completed\") }}","platform":null}},{"fqn":"/Analytics Engineering/Certified/Publications","id":"4480f39a-54c7-11ed-9d7c-03f2f2a5399d","folder\_id":"3e489d3b-54c5-11ed-9d7c-03f2f2a5399d","name":"Publications","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Commerce Demo/Curated/Analytic","id":"6d6eb066-4a46-11ed-8051-bb1ad998f9e2","folder\_id":"b2261377-497e-11ed-9780-ab1396ae5a1e","name":"Analytic","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Commerce Demo/Curated","id":"b2261377-497e-11ed-9780-ab1396ae5a1e","folder\_id":"0c65ffb1-4a00-11ed-8051-bb1ad998f9e2","name":"Curated","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Commerce Demo/Curated/Analytic/constants","id":"9ac0407f-4a46-11ed-8051-bb1ad998f9e2","folder\_id":"6d6eb066-4a46-11ed-8051-bb1ad998f9e2","name":"constants","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"","content":"#+const\n -- Thresholds for Portfolio Management Grid\n filter\_item\_promotion = 0;\n brand\_portfolio\_growth\_rate\_thr\_hi = 5;\n brand\_portfolio\_growth\_rate\_thr\_lo = -5;\n brand\_portfolio\_profit\_thr\_lo = 30;\n brand\_portfolio\_profit\_thr\_hi = 30;\n#+end","platform":null}},{"fqn":"/Commerce Demo/Curated/Analytic/sales\_store","id":"d3201b8c-5a00-11ed-bf4e-6134e290b3ef","folder\_id":"6d6eb066-4a46-11ed-8051-bb1ad998f9e2","name":"sales\_store","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"","content":"#+import \"Commerce Demo/Curated/Commerce\" as commerce\n#+import \"Commerce Demo/Curated/Census\" as census\n\n\n#+src sql SalesByStore()\n#+begin\n\nselect\n -- groups\n st.store\_key, st.store\_name, st.address\_line\_1, st.city, st.region\_key, st.zip\n -- aggregates\n , sum(s.basket\_total) as store\_sales\n , sum(s.basket\_total)/store\_square\_ft as store\_sales\_sf\n , avg(s.basket\_total) as order\_value\_avg\n , avg(basket\_size) as basket\_size\_avg\n , avg(basket\_margin) as basket\_margin\_avg\nfrom \n pub.commerce\_dim\_location as st\n left outer join (\n select\n store\_key, order\_number\n , sum(distinct sku\_key) as uniq\_skus\n , sum(extended\_cost) as basket\_total\n , sum(quantity) as basket\_size\n , sum(margin) as basket\_margin\n , sum(discount) as basket\_discount\n from pub.commerce\_fact\_sales \n group by 1, 2\n ) as s on s.store\_key = st.store\_key\ngroup by 1, 2, 3, 4, 5, 6, ST.store\_square\_ft\n;\n\n#+end\n\n#+src sql SalesByStoreRankBy(col)\n#+begin\n select \n row\_number() over (order by {{col}} desc) as {{col}}\_rank,\n percent\_rank() over (order by {{col}}) as {{col}}\_percentile,\n \*\n from {{ SalesByStore() }}\n order by 1\n#+end","platform":null}},{"fqn":"/Analytics Engineering/Certified/Staging/STG\_Orders","id":"72d7d093-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"4c88615c-54c5-11ed-9d7c-03f2f2a5399d","name":"STG\_Orders","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"Build order staging table","content":"#+src sql STG\_Orders()\n#+meta {\n :publication {\n :schema \"retail\_shop\",\n :type \"view\",\n :name \"orders\"\n }\n}\n#+begin\n select\n id as order\_id,\n user\_id as customer\_id,\n order\_date,\n status\n\n from retail\_shop.stg\_orders\n#+end","platform":null}},{"fqn":"/Analytics Engineering/Certified","id":"3e489d3b-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"33013189-54c5-11ed-9d7c-03f2f2a5399d","name":"Certified","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Commerce Demo/Curated/Analytic/sales","id":"76888db8-4a46-11ed-8051-bb1ad998f9e2","folder\_id":"6d6eb066-4a46-11ed-8051-bb1ad998f9e2","name":"sales","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"","content":"#+import \"Commerce Demo/Curated/Commerce\" as commerce\n#+import \"Commerce Demo/Curated/Census\" as census\n\n\n#+src sql SalesByClassCategory(postcode, distance)\n#+begin\nselect \n coalesce(\_class, 'TOTAL') as class\n , coalesce(\_category,'TOTAL') as category\n , round(\"2012\_Sales\" \* DIV0(12, \"2012 months\"+0.7),0) as \"2012 Sales\"\n , round(\"2013\_Sales\" \* DIV0(12,\"2013 months\"),0) as \"2013 Sales\"\n , round(100\*(\"2013 Sales\"-\"2012 Sales\")/\"2012 Sales\",2) as \"YoY change %\"\n , iff(category='TOTAL',null,round(100 \* 2 \* DIV0(\"2013 Sales\", sum(\"2013 Sales\") over (partition by class)), 2)) as cat\_pct\_of\_class\n , iff(class='TOTAL',null,round(100 \* DIV0(max(iff(category='TOTAL',\"2013 Sales\",null)) over (partition by class), max(iff(class='TOTAL' and category='TOTAL',\"2013 Sales\",null)) over ()),2)) as class\_pct\_of\_total\nfrom (\n select \n coalesce(fs.class\_name,'Other') as \_class\n , coalesce(fs.category\_name,'Other') as \_category\n , sum(iff(year(fs.date\_value)=2012,fs.extended\_price,0)) as \"2012\_Sales\"\n , sum(iff(year(fs.date\_value)=2013,fs.extended\_price,0)) as \"2013\_Sales\"\n , count(distinct iff(year(fs.date\_value)=2012,month(fs.date\_value),null)) as \"2012 months\"\n , count(distinct iff(year(fs.date\_value)=2013,month(fs.date\_value),null)) as \"2013 months\"\n from \n #+if postcode == null && distance == null then\n {{commerce.Pub}}.{{ commerce.Fact\_Sales() }} as fs\n #+else\n {{commerce.Pub}}.{{ commerce.Fact\_Sales() }} as fs\n JOIN\n {{commerce.Pub}}.{{ commerce.Dim\_Location()}} as l on l.store\_key = fs.store\_key\n JOIN\n {{ census.Postcodes\_Near\_Postcode(postcode=postcode, distance=distance) }} as p on p.postcode = l.zip\n #+end\n group by rollup(\_class, \_category)\n) order by class='TOTAL' desc, class\_pct\_of\_total desc, category='TOTAL' desc, cat\_pct\_of\_class desc\n;\n#+end\n\n\n#+src sql MeasureByClassCategory(measure, postcode, distance)\n#+begin\nselect \n coalesce(\_class, 'TOTAL') as class\n , coalesce(\_category,'TOTAL') as category\n , round(measure\_y0 \* DIV0(12, y0\_months+0.7),0) as \"Y0 {{measure}}\"\n , round(measure\_y1 \* DIV0(12, y1\_months),0) as \"Y1 {{measure}}\"\n , round(100\*(measure\_y1 - measure\_y0)/measure\_Y0,2) as \"YoY change %\"\n , iff(category='TOTAL',null,round(100 \* 2 \* DIV0(measure\_y1, sum(measure\_y1) over (partition by class)), 2)) as cat\_pct\_of\_class\n , iff(class='TOTAL',null,round(100 \* DIV0(max(iff(category='TOTAL',measure\_y1,null)) over (partition by class), max(iff(class='TOTAL' and category='TOTAL',measure\_y1,null)) over ()),2)) as class\_pct\_of\_total\nfrom (\n select \n coalesce(fs.class\_name,'Other') as \_class\n , coalesce(fs.category\_name,'Other') as \_category\n , sum(iff(year(fs.date\_value)=2012,fs.{{measure}},0)) as measure\_y0\n , sum(iff(year(fs.date\_value)=2013,fs.{{measure}},0)) as measure\_y1\n , count(distinct iff(year(fs.date\_value)=2012,month(fs.date\_value),null)) as y0\_months\n , count(distinct iff(year(fs.date\_value)=2013,month(fs.date\_value),null)) as y1\_months\n from \n #+if postcode == null && distance == null then\n {{commerce.Pub}}.{{ commerce.Fact\_Sales() }} as fs\n #+else\n {{commerce.Pub}}.{{ commerce.Fact\_Sales() }} as fs\n JOIN\n {{commerce.Pub}}.{{ commerce.Dim\_Location()}} as l on l.store\_key = fs.store\_key\n JOIN\n {{ census.Postcodes\_Near\_Postcode(postcode=postcode, distance=distance) }} as p on p.postcode = l.zip\n #+end\n group by rollup(\_class, \_category)\n) order by class='TOTAL' desc, class\_pct\_of\_total desc, category='TOTAL' desc, cat\_pct\_of\_class desc\n;\n#+end","platform":null}},{"fqn":"/Analytics Engineering","id":"33013189-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"bb6de4af-1aad-493e-a9ee-b3e75cf58ed7","name":"Analytics Engineering","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Commerce Demo","id":"0c65ffb1-4a00-11ed-8051-bb1ad998f9e2","folder\_id":"bb6de4af-1aad-493e-a9ee-b3e75cf58ed7","name":"Commerce Demo","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Analytics Engineering/Certified/Staging/STG\_Customers","id":"5c69a07d-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"4c88615c-54c5-11ed-9d7c-03f2f2a5399d","name":"STG\_Customers","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"Build customer table","content":"#+src sql STG\_Customers()\n#+meta {\n :publication {\n :schema \"retail\_shop\",\n :type \"view\",\n :name \"customers\"\n }\n}\n#+begin\n select\n id as customer\_id,\n first\_name,\n last\_name\n\n from retail\_shop.stg\_customers\n#+end","platform":null}},{"fqn":"/Analytics Engineering/Test","id":"381e17aa-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"33013189-54c5-11ed-9d7c-03f2f2a5399d","name":"Test","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}},{"fqn":"/Analytics Engineering/Certified/Publications/customer\_publications","id":"5ca86c5b-54c7-11ed-9d7c-03f2f2a5399d","folder\_id":"4480f39a-54c7-11ed-9d7c-03f2f2a5399d","name":"customer\_publications","entity\_type":"sql\_entity","entity\_version":"A59b6ADzbNwaraUXxI5kzN0F9zJE7Od3T18L3Dpycsc=","data":{"executed\_count":0,"executed\_at":null,"asset\_type":"CSL","description":"Create views for customers, orders and customer order summary","content":"#+import \"std/publication\"\n#+import \"Analytics Engineering/Certified/Staging\" as staging\n#+import \"Analytics Engineering/Certified/Customers\" as customers\n\n{{\n publication.Run(\n packages=[staging, customers]\n )\n}}","platform":null}},{"fqn":"/Analytics Engineering/Certified/Staging","id":"4c88615c-54c5-11ed-9d7c-03f2f2a5399d","folder\_id":"3e489d3b-54c5-11ed-9d7c-03f2f2a5399d","name":"Staging","entity\_type":"folder","entity\_version":"Uw0srHEJ3TifkwXIjL1jePBCzp4+AmiT8FSbKVf0eC4=","data":{"folder\_type":"REGULAR"}}]}
{"source": "ewof/sharegpt-instruct-unfiltered-deduped", "kind": "human", "evolved_from": null}